☰

Excel：识别具有特定值的列

tag-icon • tag-icon microsoft-excel
Excel：识别具有特定值的列

我有一个 excel 文件，其中有以下格式的表格，大约有 50 列

A B C D E id
1 0 0 0 0  1
0 1 0 0 0  2
0 0 0 0 1  3

保证一行恰好有 1 个值，即 '1'，其他值全为 0

我如何将上述内容转换为以下格式：

val  id
 A    1
 B    2
 E    3

答案1

假设您提供的表格位于 A1:F4（标题位于第 1 行），首先在 H1 中输入以下公式：

=SUM(A2:E4)

然后在I1中输入这个**数组公式****：

=IF(ROWS($1:1)>$H$1,"",INDEX($A$1:$E$1,SMALL(IF($A$2:$E$4,COLUMN($A$2:$E$4)-MIN(COLUMN($A$2:$E$4))+1),ROWS($1:1))))

将此公式复制下来（但不是 H1 中的公式 - 这是一次性的）直到开始得到结果空白。

初始 IF 子句的设计使得，在复制公式的行中，如果超出预期的返回数（由 H1 中的公式给出），则返回空白。这是可行的，因为 ROWS($1:1)（等于 1）在向下复制时会依次变为 ROWS($1:2) (=2)、ROWS($1:3) (=3) 等。

SMALL 函数中的 IF 结构会生成一个值数组，其中包含所有列中某处为 1 的情况的相对列号。SMALL 函数的 k 参数为上述 ROWS($1:1) 结构，它会在连续的行上返回第一个这样的值，然后是第二个这样的值，等等。

然后将这些列索引传递给 INDEX 以从标题中提供相应的条目。

然后在 J1 中输入此（非数组）公式：

=IF(I1="","",INDEX($F$2:$F$4,MATCH(1,INDEX($A$2:$E$4,,MATCH(I1,$A$1:$E$1,0)),0)))

按要求抄下来。

问候

**数组公式的输入方式与“标准”公式不同。您不必直接按 ENTER，而是先按住 CTRL 和 SHIFT，然后按 ENTER。如果您操作正确，您会注意到 Excel 在公式周围放置了花括号 {}（但不要尝试自己手动插入这些括号）。

相关内容