在电子表格中计算不同值 [英] count distinct values in spreadsheet
问题描述
我有一个Google电子表格,其中的列如下所示:
城市
---- b $ b伦敦
巴黎
伦敦
柏林
罗马
巴黎
我想计算每个不同城市的外观(所以我需要城市名称和出现次数)。
City |计数
------- + ------
London | 2
Paris | 2
Berlin | 1
Rome | 1
我如何做?
感谢
其他可能有用的链接
I have a Google spreadsheet with a column that looks like this:
City
----
London
Paris
London
Berlin
Rome
Paris
I want to count the appearances of each distinct city (so I need the city name and the number of appearances).
City | Count
-------+------
London | 2
Paris | 2
Berlin | 1
Rome | 1
How do I do that?
Thanks
Solution 0
This can be accompished using pivot tables.
Solution 1
Use the unique
formula to get all the distinct values. Then use countif
to get the count of each value. See the working example link at the top to see exactly how this is implemented.
Unique Values Count
=UNIQUE(A3:A8) =COUNTIF(A3:A8,B3)
=COUNTIF(A3:A8,B4)
...
Solution 2
If you setup your data as such:
City
----
London 1
Paris 1
London 1
Berlin 1
Rome 1
Paris 1
Then the following will produce the desired result.
=sort(transpose(query(A3:B8,"Select sum(B) pivot (A)")),2,FALSE)
I'm sure there is a way to get rid of the second column since all values will be 1. Not an ideal solution in my opinion.
via http://googledocsforlife.blogspot.com/2011/12/counting-unique-values-of-data-set.html
Other Possibly Helpful Links
这篇关于在电子表格中计算不同值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!