google-sheets - 转置多列的唯一值

Question

我正在尝试生成唯一值，D1:F但我的公式不起作用。例如，第 1 行有

A       B       C
milk    milk    44

输出应该是：

D      E
milk   44

这是我的公式和我的表格

=ARRAYFORMULA(unique(A1:C))

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

Answer 1

UNIQUE仅适用于一维（行或列）

=QUERY(UNIQUE({A:A;B:B;C:C}), "where Col1 is not null", 0)

---

=ARRAYFORMULA(SPLIT(REGEXREPLACE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE({QUERY(QUERY(
 UNIQUE(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(LEN(A2:A), "♠"&A2:A&"♦"&B2:D, )),,999^99)),,999^99)), "♠")), "♦")),
 "select Col1, count(Col1) where Col1 is not null group by Col1 pivot Col2", 0),
 "select Col1 offset 1",0),
 IF(QUERY(QUERY(UNIQUE(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(LEN(A2:A), "♠"&A2:A&"♦"&B2:D, )),,999^99)),,999^99)), "♠")), "♦")),
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2", 0), "offset 1",0)<>"", 
 QUERY(QUERY(UNIQUE(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(LEN(A2:A), "♠"&A2:A&"♦♀"&B2:D&",", )),,999^99)),,999^99)), "♠")), "♦")),
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2", 0), "limit 0",1),)})
 ,,999^99))), ", ♀", ", "), ",$", ), "♀"))