google-sheets - 自动更新基于另一张工作表数据的表格

Question

如果我在一张纸上有这样的数据

value | key
============
apple |  1
pear  |  1
orange|  2
cat   |  2
dog   |  3

我想用它在另一个中生成单独的表，以这样的形式呈现这些数据

|  1   |
========
|apple | 
|pear  | 

|  2   | 
========
|orange| 
|cat   |

|  3   | 
========
|dog   |

如果我更新了数据（例如 cat : 2 -> cat : 3），表格将自动更新

标签： google-sheetstransposegoogle-sheets-formulaarray-formulasgoogle-sheets-query

Answer 1

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE({"♥"&QUERY(QUERY(A2:B, 
 "select B,count(B) where B is not null group by B pivot A", 0), 
 "select Col1 offset 1", 0), IF(QUERY(QUERY(A2:B, 
 "select count(B) where B is not null group by B pivot A", 0), 
 "offset 1", 0)<>"", "♥"&QUERY(A2:B, 
 "select count(B) where B is not null group by B pivot A limit 0", 0), )})
 ,,999^99))&"♥♦",,999^99), "♥♦"))))