计算电子表格中不同的值

我有一个谷歌电子表格，其中一栏是这样的:

City
----
London
Paris
London
Berlin
Rome
Paris

我想计算每个不同城市的外观(所以我需要城市名称和外观的数量)。

City   | Count
-------+------
London |  2
Paris  |  2
Berlin |  1
Rome   |  1

我该怎么做？

162439 次浏览
小开
最佳答案

Link to Working Examples

Solution 0

This can be accompished using pivot tables.

Pivot table Example - Count rows by value

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

小开

=iferror(counta(unique(A1:A100))) counts number of unique cells from A1 to A100

小开

This is similar to Solution 1 from @JSuar...

Assume your original city data is a named range called dataCity. In a new sheet, enter the following:

    A                 | B
----------------------------------------------------------
1 | =UNIQUE(dataCity) | Count
2 |                   | =DCOUNTA(dataCity,"City",{"City";$A2})
3 |                   | [copy down the formula above]
4 |                   | ...
5 |                   | ...
小开

Not exactly what the user asked, but an easy way to just count unique values:

Google introduced a new function to count unique values in just one step, and you can use this as an input for other formulas:

=COUNTUNIQUE(A1:B10)

小开

You can use the query function, so if your data were in col A where the first row was the column title...

=query(A2:A,"select A, count(A) where A != '' group by A order by count(A) desc label A 'City'", 0)

yields

City    count
London  2
Paris   2
Berlin  1
Rome    1

Link to working Google Sheet.

https://docs.google.com/spreadsheets/d/1N5xw8-YP2GEPYOaRkX8iRA6DoeRXI86OkfuYxwXUCbc/edit#gid=0

小开

=UNIQUE({filter(Core!L8:L27,isblank(Core!L8:L27)=false),query(ArrayFormula(countif(Core!L8:L27,Core!L8:L27)),"select Col1 where Col1 <> 0")})

Where Core!L8:L27 is the list in the question.

小开

This works if you just want the count of unique values in e.g. the following range

=counta(unique(B4:B21))

提交答案