Office 365中如何高效将指定工作表转换为目标列表？

原数据结构 #

目标数据结构 #

高效公式解决方案 #

在目标表格的首个数据单元格（比如F2）输入以下公式，Office 365会自动溢出填充所有结果：

=LET(
    names, B1:D1,
    fruits, A2:A5,
    flags, B2:D5,
    valid_fruits, FILTER(fruits, BYROW(flags, LAMBDA(r, SUM(r)>0))),
    valid_flags, FILTER(flags, BYROW(flags, LAMBDA(r, SUM(r)>0))),
    fruit_repeats, TOCOL(IF(valid_flags, valid_fruits, ""), 2),
    name_matches, TOCOL(IF(valid_flags, names, ""), 2),
    HSTACK(fruit_repeats, name_matches)
)

公式说明 #

1. names：提取第一行的名字区域（MARY、PETER、SAM）
2. fruits：提取Column A的水果列表
3. flags：提取下方的1/空值标记区域
4. valid_fruits：过滤出至少带有一个1标记的水果（排除空值行和BANANA行）
5. valid_flags：对应筛选出有效水果的标记区域
6. fruit_repeats：根据标记生成重复的水果名（比如APPLE对应两个1，就生成两次APPLE）
7. name_matches：根据标记匹配对应的名字
8. HSTACK：将水果列和名字列横向合并，得到目标结构

如果需要更精简的写法，也可以用嵌套公式：

=HSTACK(
    TOCOL(IF(FILTER(B2:D5,BYROW(B2:D5,LAMBDA(r,SUM(r)>0)),""),FILTER(A2:A5,BYROW(B2:D5,LAMBDA(r,SUM(r)>0)),""),""),2),
    TOCOL(IF(FILTER(B2:D5,BYROW(B2:D5,LAMBDA(r,SUM(r)>0)),""),B1:D1,""),2)
)

原公式不符合预期的原因 #

=FILTER(A2:D5,B2:B5=1) 仅能筛选出Column B为1的行（APPLE和PLUM行），但无法实现水果与对应列名字的一一配对，只是对原行的截取，因此和目标结构不符。

内容的提问来源于stack exchange，提问作者kwany