是的,我阅读:Excel:矩阵公式 - 为什么不返回矩阵(向量)?但这并不一样。
基本上我无法弄清楚为什么公式是这样的:{=MATCH({1,2,3},{1,2,3},0)}
通常返回矩阵(向量){1,2,3}
返回单个值。
考虑这个例子:
注意:介绍很长 - 有趣的部分从粗体文字开始:“到目前为止,一切都按预期进行。”
有一个带有Name
和 修订 的表格columns
。
- 我希望每个唯一的名称都有一个唯一的整数
GroupNo
。 - 然后根据修正值添加
GroupNo
小数部分( )RevPart
""=0, "a"=0.01, ...
GroupNo+RevPart
叫做FullGroupNo
(FGN
)- 我想知道
FGN
每个名称组的最大值:(MaxInGroup
)MIG
使用表中的多个列可以轻松完成此操作(Table1
):
GroupNo: =MATCH([@Name],[Name],0)
RevPart: =INDEX({0,1},MATCH([@Revision],{"","a"},0))/100
FullGroupNo: =[@GroupNo]+[@RevPart]
MaxInGroup: {=MAX(([@GroupNo]=[GroupNo])*[FullGroupNo])}
Table1
+------+----------+---------+---------+-------------+------------+
| Name | Revision | GroupNo | RevPart | FullGroupNo | MaxInGroup |
+------+----------+---------+---------+-------------+------------+
| A | ="" | 1 | 0 | 1 | 1,01 |
| A | a | 1 | 0,01 | 1,01 | 1,01 |
| B | ="" | 3 | 0 | 3 | 3 |
| C | ="" | 4 | 0 | 4 | 4,01 |
| C | a | 4 | 0,01 | 4,01 | 4,01 |
+------+----------+---------+---------+-------------+------------+
Note: ="" means there is a blank string in the cell (formula ="")
仅引用和列即可在一个公式中获得(FullGroupNo
)。FGN
Name
Revision
FGN_2: {=(INDEX({0,1},MATCH([@Revision],{"","a"},0))/100+MATCH([@Name],[Name],0))}
FGN_2 is a new column in the table (Table1) above. It's matrix formula returns single (not-matrix) value.
Area under FGN (Matrix) is an ordinary worksheet range. This whole area contains return (matrix) value.
FGN (Matrix): {=INDEX({0,1},MATCH(Table1[Revision],"","a"},0))/100+MATCH(Table1[Name],Table1[Name],0))}
Table1 Just range
+-------+ +--------------+
| FGN_2 | | FGN (Matrix) |
+-------+ +--------------+
| 1 | | 1 |
| 1,01 | | 1,01 |
| 3 | | 3 |
| 4 | | 4 |
| 4,01 | | 4,01 |
+-------+ +--------------+
现在我尝试MaxInGroup
用一个矩阵公式来合并。
Original MaxInGroup formula:
MaxInGroup: {=MAX(([@GroupNo]=[GroupNo])*[FullGroupNo])}
Step1
- replace[@GroupNo] with matrix formula with reference only to Name and Revision columns
MIG_1: {=MAX((MATCH([@Name],[Name],0)=[GroupNo])*[FullGroupNo])}
Step2
- Replace (matrix) [GroupNo] with matrix formula with reference to Name and revision columns
MIG_2: {=MAX((MATCH([@Name],[Name],0)=MATCH([Name],[Name],0))*[FullGroupNo])}
到目前为止一切都按预期进行。
Step3
- Replace (matrix) [FullGroupNo] in MIG_2 with matrix formula with reference to Name and Revidion columns
(so, basicaly I need formula from FGN (Matrix) used before
MIG_3: {=MAX((MATCH([@Name],[Name],0)=MATCH([Name],[Name],0))*(INDEX({0,1},MATCH([Revision],{"","a"},0))/100+MATCH([Name],[Name],0)))}
Note: MIG_3 is part of the Table1 so column names only are enough
So now we could have same values in MaxInGroup, MIG_1-3 columns. But no. We haven't.
The RevPart in MIG_3 depends only on the firs Revision value.
+------+----------+------------+-------+-------+-------+
| Name | Revision | MaxInGroup | MIG_1 | MIG_2 | MIG_3 |
+------+----------+------------+-------+-------+-------+
| A | ="" | 1,01 | 1,01 | 1,01 | 1 |
| A | a | 1,01 | 1,01 | 1,01 | 1 |
| B | ="" | 3 | 3 | 3 | 3 |
| C | ="" | 4,01 | 4,01 | 4,01 | 4 |
| C | a | 4,01 | 4,01 | 4,01 | 4 |
+------+----------+------------+-------+-------+-------+
Versus
+------+----------+------------+-------+-------+-------+
| Name | Revision | MaxInGroup | MIG_1 | MIG_2 | MIG_3 |
+------+----------+------------+-------+-------+-------+
| A | a | 1,01 | 1,01 | 1,01 | 1,01 |
| A | a | 1,01 | 1,01 | 1,01 | 1,01 |
| B | ="" | 3 | 3 | 3 | 3,01 |
| C | ="" | 4,01 | 4,01 | 4,01 | 4,01 |
| C | a | 4,01 | 4,01 | 4,01 | 4,01 |
+------+----------+------------+-------+-------+-------+
Note: ="" means there is a blank string in the cell (formula ="")