我有一个 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 在公式周围放置了花括号 {}(但不要尝试自己手动插入这些括号)。