Google Sheets条件TOROW公式与QUERY函数组合的列适配问题

问题场景 #

我需要将两个Google Sheets公式组合，把公式1中的"Text"替换为公式2，但组合后QUERY函数无法自动切换列（从I列切换到J列、K列等），当前结果不符合预期。

原始公式与数据 #

公式1（筛选A-D列数据）

=TOROW(MAP(UNIQUE(C1:C9),LAMBDA(tp, IFNA(FILTER(D1:D9, "L1"=A1:A9, "XD"=B1:B9, tp=C1:C9) &"Text"))))

对应A-D列数据：

公式2（匹配H-I列的正确答案）

=IF(QUERY(H1:I6, "SELECT I WHERE H='Correct Answer'", 0) = QUERY(H1:I6, "SELECT I WHERE H='Option1'", 0), "AG02", "") & IF(QUERY(H1:I6, "SELECT I WHERE H='Correct Answer'", 0) = QUERY(H1:I6, "SELECT I WHERE H='Option2'", 0), "AG03", "") & IF(QUERY(H1:I6, "SELECT I WHERE H='Correct Answer'", 0) = QUERY(H1:I6, "SELECT I WHERE H='Option3'", 0), "AG04", "")

对应H-J列数据：

尝试的组合公式 #

=TOROW(MAP(UNIQUE(C1:C9),LAMBDA(tp, IFNA(FILTER(D1:D9, "L1"=A1:A9, "XD"=B1:B9, tp=C1:C9) &IF(QUERY(H1:I6, "SELECT I WHERE H='Correct Answer'", 0) = QUERY(H1:I6, "SELECT I WHERE H='Option1'", 0), "AG02", "") & IF(QUERY(H1:I6, "SELECT I WHERE H='Correct Answer'", 0) = QUERY(H1:I6, "SELECT I WHERE H='Option2'", 0), "AG03", "") & IF(QUERY(H1:I6, "SELECT I WHERE H='Correct Answer'", 0) = QUERY(H1:I6, "SELECT I WHERE H='Option3'", 0), "AG04", ""))))

问题与预期结果 #

• 当前结果：E1显示q1AG02、G1显示q1AG02
• 预期结果：E1显示q1AG02、G1显示q1AG03

解决方法 #

核心问题是组合公式里的QUERY固定引用了I列，无法随公式所在列自动切换。以下是两种高效的优化方案：

方案1：用INDEX+MATCH替代QUERY，结合动态列索引 #

用LET函数封装变量减少重复计算，同时通过COUNTBLANK动态获取目标列索引，实现列自动切换：

=TOROW(MAP(UNIQUE(C1:C9),LAMBDA(tp, 
  IFNA(
    FILTER(D1:D9, "L1"=A1:A9, "XD"=B1:B9, tp=C1:C9) &
    LET(
      col_idx, COUNTBLANK($E1:E1)+2,
      correct_val, INDEX(H1:6, MATCH("Correct Answer", H1:H6, 0), col_idx),
      opt1_val, INDEX(H1:6, MATCH("Option1", H1:H6, 0), col_idx),
      opt2_val, INDEX(H1:6, MATCH("Option2", H1:H6, 0), col_idx),
      opt3_val, INDEX(H1:6, MATCH("Option3", H1:H6, 0), col_idx),
      IF(correct_val=opt1_val, "AG02", "") &
      IF(correct_val=opt2_val, "AG03", "") &
      IF(correct_val=opt3_val, "AG04", "")
    )
  )
)))

• COUNTBLANK($E1:E1)+2：根据公式所在列动态计算目标列在H-J区域的索引，E列对应I列（索引2），G列对应J列（索引3），后续列自动顺延。
• INDEX+MATCH比QUERY更高效，且更适配动态列场景。

方案2：用XLOOKUP简化匹配逻辑 #

如果需要更简洁的查找逻辑，可替换INDEX+MATCH为XLOOKUP：

=TOROW(MAP(UNIQUE(C1:C9),LAMBDA(tp, 
  IFNA(
    FILTER(D1:D9, "L1"=A1:A9, "XD"=B1:B9, tp=C1:C9) &
    LET(
      target_col, INDIRECT("H1:6")&""&CHAR(64+COLUMN()+4),
      correct_val, XLOOKUP("Correct Answer", H:H, target_col),
      opt1_val, XLOOKUP("Option1", H:H, target_col),
      opt2_val, XLOOKUP("Option2", H:H, target_col),
      opt3_val, XLOOKUP("Option3", H:H, target_col),
      IF(correct_val=opt1_val, "AG02", "") &
      IF(correct_val=opt2_val, "AG03", "") &
      IF(correct_val=opt3_val, "AG04", "")
    )
  )
)))

• INDIRECT("H1:6")&""&CHAR(64+COLUMN()+4)：动态生成目标列的引用，可根据实际列布局调整+4的偏移值。

验证结果 #

• 公式放在E1时，自动引用I列，匹配到正确答案A与Option1，输出q1AG02
• 公式放在G1时，自动引用J列，匹配到正确答案B与Option2，输出q1AG03，完全符合预期。

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