转置多列中的唯一值 [英] Transpose Unique Values from Multiple Columns
本文介绍了转置多列中的唯一值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试在 D1:F
中生成唯一值,但我的公式不起作用.例如,第 1 行有
A B C牛奶 牛奶 44
输出应该是:
D E牛奶 44
这是我的公式和我的
<小时>=ARRAYFORMULA(SPLIT(REGEXREPLACE(SUBSTITUTE(TRIM(TRANSPOSE(TRANSPOSE(QUERY(TRANSPOSE({QUERY(QUERY(唯一(拆分(转置(拆分(修剪(查询(转置(查询(转置(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),"选择 Col1 偏移 1",0),如果(查询(查询(唯一(拆分(转置(拆分(拆分(TRIM(查询(转置(查询(转置(转置))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)<>"",查询(查询(查询(唯一(拆分(转置(转置(拆分(修剪(查询(转置(查询(转置(转置))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))),",♀",","),",$",),"♀"))
I'm trying to generate unique values in D1:F
but my formula is not working. For example, row 1 has
A B C
milk milk 44
the output should be:
D E
milk 44
here is my formula and my sheet
=ARRAYFORMULA(unique(A1:C))
解决方案
UNIQUE
works only in one dimension (row or column)
=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))), ", ♀", ", "), ",$", ), "♀"))
这篇关于转置多列中的唯一值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文