google-sheets - 按事件排列数据

Question

给定一个包含多个项目的范围，如果可能，我想计算它们的出现次数并显示结果，如此Google 电子表格示例中所示，仅使用一个公式。

这是我得到的最接近的：

=ARRAYFORMULA(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},"select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''",0),"select Col1, Count(Col1) group by Col1 pivot Col2",0))

任何想法或想法将不胜感激，谢谢！

标签： google-sheetsrangegoogle-sheets-formulaarray-formulasgoogle-query-language

Answer 1

尝试：

=ARRAYFORMULA(TRANSPOSE(LEN(SUBSTITUTE(FLATTEN(QUERY(QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
 "select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''", ),
 "select max(Col1) group by Col1 pivot Col2", ), "offset 1", ),,9^9)), " ", ))))

和：

=ARRAYFORMULA(TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
 "select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''", ),
 "select max(Col1) group by Col1 pivot Col2", ),,9^9)), " ")))

---

或联合：

=ARRAYFORMULA({TRANSPOSE(LEN(SUBSTITUTE(FLATTEN(QUERY(QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
 "select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''", ),
 "select max(Col1) group by Col1 pivot Col2", ), "offset 1", ),,9^9)), " ", )));
 TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
 "select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''", ),
 "select max(Col1) group by Col1 pivot Col2", ),,9^9)), " "))})

---

更新：

=ARRAYFORMULA({TRANSPOSE(LEN(SUBSTITUTE(FLATTEN(QUERY(IF(""=QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
 "select Col1, count(Col2) where Col1 is not null group by Col1 label count(Col2) ''", ),
 "select max(Col1) group by Col1 pivot Col2", ), "offset 1", ),,"×"),,9^9)), " ", )));
 REGEXREPLACE(""&TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(QUERY(FLATTEN( 
 IF(ISNUMBER(IFERROR(1/(1/(1*B3:U11)))), "!"&TEXT(B3:U11, "000000000#"), B3:U11))&{"",""},
 "select Col1, count(Col2) where Col1 is not null group by Col1 label count(Col2) ''", ),
 "select max(Col1) group by Col1 pivot Col2", ),,9^9)), " ")), "^!0{1,9}", )})