Excel-计算组中的唯一记录 [英] Excel - Counting unique records in a group

问题描述

我很难统计文件中具有唯一ID的记录并列出与该特定ID相关联的行数.

I'm having difficulty counting records in a file that have a unique ID and listing the number of rows associated with that specific ID.

对于此文件，唯一ID代表特定的族(列A).家庭中的每个成员都在具有相同ID的不同行中.我想计算每个独特家庭的家庭成员(行)数.我可以有几千行，所以自动化它会很棒.谢谢您的帮助！

For this file, the unique ID represents a specific family (column A). Each member of the family is in a different row with the same ID. I would like to count the number of family members(rows) in each unique family. I can have a few thousand rows so automating this would be wonderful. Thanks for any help!!

推荐答案

您现在可以使用Excel 2013自动执行此操作.

You can do this now automatically with Excel 2013.

如果您使用的是该版本，则选择数据以创建数据透视表，并在创建表时，确保选中了将数据添加到数据模型"复选框(请参见下文).

If you have that version, then select your data to create a pivot table, and when you create your table, make sure the option 'Add this data to the Data Model' tickbox is check (see below).

然后，在打开数据透视表时，正常创建行，列和值.然后单击要计算其非重复计数的字段，然后编辑字段值设置":

Then, when your pivot table opens, create your rows, columns and values normally. Then click the field you want to calculate the distinct count of and edit the Field Value Settings:

最后，向下滚动到最后一个选项，然后选择非重复计数".

Finally, scroll down to the very last option and choose 'Distinct Count.'

这应该更新您的数据透视表值，以显示您要查找的数据.

This should update your pivot table values to show the data you're looking for.

这篇关于Excel-计算组中的唯一记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！