如何获取每个唯一值的行数（基于两列的组合）？

I have the following excel spreadsheet (in Excel 2010):

我有以下excel电子表格(在Excel 2010中):

State Sport
TX Football
TX Football
TX Basketball
CA Basketball
CA Basketball
CA Football

I'd like to get a count for each unique State-Sport combination in the spreadsheet. So the result would look like this:

我想在电子表格中计算每个独特的State-Sport组合。所以结果看起来像这样:

TX Football 2
TX Basketball 1
CA Basketball 2
CA Football 1

How can I achieve this?

我怎样才能做到这一点?

2 个解决方案

#1

Built-in function COUNTIFS could make this job

内置功能COUNTIFS可以完成这项工作

#2

Or you could use a pivot table:

或者您可以使用数据透视表:

This pivot table has State and Sports in Row labels and Count of Sport in Values.

此数据透视表具有行标签中的状态和运动以及值中的运动计数。

The layout is "Tabular Form" and I opted for "Do not show Subtotals"

布局是“表格形式”,我选择“不要显示小计”

You can easily copy/paste the table as values (to remove the pivot table and enable you to edit the table) and fill down the states to fill in the blank cells below them later using something like:

您可以轻松地将表复制/粘贴为值(以删除数据透视表并使您能够编辑表)并填写状态以便稍后使用以下内容填充其下方的空白单元格:

1. Select the column with 'State' as from cell A5, hit Ctrl + G.

从单元格A5中选择“State”列,按Ctrl + G.

2. Hit Special... and select Blanks.

点击特别...并选择空白。

3. Insert = and press the Up key, then Ctrl + Enter.

插入=并按向上键,然后按Ctrl + Enter。