arrays - 给定一组数据，是否有一种简单的方法可以找到多个标题列？

Question

我有一张看起来像这样的表:

我想要一个公式来查询 A2:E7 中的数据并从 A1:E1 中提取相应的列标题。诀窍是值可能会出现多次。
示例结果如下所示:

示例表:https://docs.google.com/spreadsheets/d/1Zr_q8nwYPixUjxWYdT-WlQLiGIUzqoK_cSQXSp19nJg

Answer 1

用:

=ARRAYFORMULA(IFERROR(TRIM(SPLIT(IFNA(VLOOKUP(G1:G, 
 SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(UNIQUE(SPLIT(FLATTEN(
 IF(A2:E="",,A2:E&"♂×"&A1:E1&"♀×"&A1:E1)), "×")), 
 "select Col1,max(Col2) 
  where Col2 is not null 
  group by Col1
  pivot Col3"), 
 "offset 1", 0)),,9^9)), "♂"), 2, 0)), "♀"))))

更新:

=ARRAYFORMULA(ARRAY_CONSTRAIN(IFERROR(TRIM(SPLIT(IFNA(VLOOKUP(G1:G, 
 SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(UNIQUE(SPLIT(FLATTEN(
 IF(A2:E="",,A2:E&"♂×"&A1:E1&"♀×"&A1:E1)), "×")), 
 "select Col1,max(Col2) 
  where Col2 is not null 
  group by Col1
  pivot Col3"), 
 "offset 1", 0)),,9^9)), "♂"), 2, 0)), "♀"))), 9^9, 2))