用QUERY/ARRAYFORMULA批量获取每行最小分数对应列标题

问题背景 #

现有如下表格（实际约2000行）：

需要在另一列批量生成排除空白后最小分数对应的列标题，要求：

• 自动批量计算，无需逐行下拉公式
• 处理重复最小分数的情况（取最先出现的列标题，如Sue的分数A和分数C都是9，返回分数A）

当前使用的公式需要逐行下拉，在大表格中新增数据时效率极低：

=INDEX($B$1:$D$1,MATCH(MIN(B2:D2),B2:D2,0))

预期结果如下：

解决方案 #

可以使用ARRAYFORMULA结合BYROW实现批量计算，公式如下：

=ARRAYFORMULA(IF(A2:A="","",BYROW(B2:D,LAMBDA(row,INDEX($B$1:$D$1,MATCH(MIN(FILTER(row,row<>"")),row,0))))))

公式解释 #

1. ARRAYFORMULA：触发数组计算，无需下拉公式
2. IF(A2:A="","",...)：当姓名列为空时返回空值，避免无效计算
3. BYROW(B2:D,LAMBDA(row,...))：逐行处理分数区域B2:D
4. FILTER(row,row<>"")：过滤当前行中的空白单元格，只保留有效分数
5. MIN(...)：获取过滤后的最小分数
6. MATCH(...,row,0)：找到该最小分数在当前行中首次出现的位置
7. INDEX($B$1:$D$1,...)：根据位置返回对应的列标题

替代方案（使用QUERY） #

如果偏好QUERY，可以用以下公式（逻辑类似，通过构造数组实现）：

=ARRAYFORMULA(IF(A2:A="","",INDEX($B$1:$D$1,MATCH(MMULT(N(B2:D=BYROW(B2:D,LAMBDA(r,MIN(FILTER(r,r<>""))))),SEQUENCE(COLUMNS(B2:D),1,1,0)),SEQUENCE(COLUMNS(B2:D)),0))))

不过BYROW的版本更直观易读，推荐优先使用。

注意事项 #

• 公式中的区域A2:A、B2:D、$B$1:$D$1需根据实际表格范围调整
• 当某行所有分数都是空白时，公式会返回错误值，可在IF中添加错误处理，比如改为：

  =ARRAYFORMULA(IF(A2:A="","",IFERROR(BYROW(B2:D,LAMBDA(row,INDEX($B$1:$D$1,MATCH(MIN(FILTER(row,row<>"")),row,0)))),"")))
  

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