问题描述
计算电子表格列中出现次数的好方法是什么?这可以用一个数组公式完成吗?
示例(列 A
是 input ,列 B
和 C
自动生成 em>):
| A | B | C |
+ ------- + ------- + ------- +
|名称|名称| Count |
+ ------- + ------- + ------- +
|乔|乔| 2 |
|丽莎|丽莎| 3 |
|珍妮|珍妮| 2 |
|丽莎| | |
|丽莎| | |
|乔| | |
|珍妮| | |
试试:
$ (COL1),计数(Col2)其中Col1!=''按Col1标签计数(Col2)组合)'Count'; 1))
22/07/2014 Some time in上个月,Sheets已经开始支持使用嵌入式数组的更灵活的数组串联。所以解决方案可能会略微缩短为:
= QUERY({A:A,A:A},选择Col1,count(Col2 )其中Col1!=''组由Col1标签计数(Col2)'Count',1)
What would be a good approach to calculate the number of occurrences in a spreadsheet column? Can this be done with a single array formula?
Example (column A
is input, columns B
and C
are to be auto-generated):
| A | B | C |
+-------+-------+-------+
| Name | Name | Count |
+-------+-------+-------+
| Joe | Joe | 2 |
| Lisa | Lisa | 3 |
| Jenny | Jenny | 2 |
| Lisa | | |
| Lisa | | |
| Joe | | |
| Jenny | | |
Try:
=ArrayFormula(QUERY(A:A&{"",""};"select Col1, count(Col2) where Col1 != '' group by Col1 label count(Col2) 'Count'";1))
22/07/2014 Some time in the last month, Sheets has started supporting more flexible concatenation of arrays, using an embedded array. So the solution may be shortened slightly to:
=QUERY({A:A,A:A},"select Col1, count(Col2) where Col1 != '' group by Col1 label count(Col2) 'Count'",1)
这篇关于计算列中出现的次数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!