google-sheets - 将多行转换为 Google 工作表中的列

Question

我想在这个数据中使用转置公式

我希望欲望输出看起来像这样

标签： google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query

Answer 1

尝试：

=QUERY(A2:B, "select max(B) where A is not null group by B pivot A")

---

或者：

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(QUERY(
 {SUBSTITUTE(A2:A, " ", "♥"), B2:B}, 
 "select max(Col2)
  where Col1 is not null
  group by Col2
  pivot Col1"),,999^99)), " ")), "♥", " "))