切换值和标题

Question

我有一个带有表格的Google表格，

card1 | card2 | card3 | card4 | card5 | card6 | card7 
------+-------+-------+-------+-------+-------+------
set3  | set1  | set1  | set2  | set2  | set4  | set1 
set4  | set2  | set3  | set3  | set4  |       | set2
      | set4  |       |       |       |       | set3
      |       |       |       |       |       | set4

如何切换表中的值以及标题中的值以产生这样的表：

 set1 |  set2 |  set3 |  set4 
------+-------+-------+-------
card2 | card2 | card1 | card1 
card5 | card4 | card3 | card2
      | card5 | card4 | card5  
      | card7 | card7 | card6 
      |       |       | card7

Answer 1

使用：

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(
 IF(A2:G30="",,A2:G30&"¤×"&A1:G1&"¤")), "×"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,9^9)), "¤"))))