如何计算Excel中每个类别的唯一值 [英] How to count unique values per category in Excel

问题描述

我已经在这个网站和网上查看了这个问题的答案，但我还没有成功，所以我会问一个新的线程。

I have looked around on this site and online for an answer to this question, but I have not been sucessful yet so I will ask in a new thread.

我需要能够使用公式（或多个公式）来计算Excel中每个类别的特定文本的数量。最好这可以在不使用VBA的情况下完成，但是如果使用VBA命令，我将尝试编写VBA命令。

I need to be able to count the number of ocurrences of a particular text per category in Excel using a formula (or a number of formulas). Preferably this can be done without the use of VBA, but if it is nessesary I will attempt to write a VBA command.

这是我正在使用的工具： p>

Here is what I am working with:

Cat.    Word
A       doo
A       foo
A       foo
A       loo
B       loo
B       goo
B       loo
A       roo
A       lol
B       rar
C       goo
...      ...

我想使用一个公式来计算每个类别每个单词的唯一出现次数。这些类别是已知和有限的，并且包含在我的excel表格中的列表中，但是这些单词可以是任何数量并且不知道。每个类别都可以有其他类别的单词，最终结果应该如下所示：

I would like to have a formula that counts the number of unique occurrences of each word per category. The categories are known and finite, and are included in a list within my excel sheet, but the words can be any amount and are not known. Each category can have words that other categories would have as well the end result should look something like this:

Cat.    Count of distinct ocurrences per category
A       5
B       3
C       1

最后，我知道我可以重复数据删除列表，但是我想保留列表的完整档案目的，所以不需要重复数据删除的计数将是理想的。

Finally, I know that I can deduplicate the list, but I would like to keep the list intact for archival purposes, so a count that does not require deduping would be ideal.

这是我试图做的，但最终没有工作：

Here is what I tried to do, but did not work in the end:

=IF(A:A=C1,SUM(IF(FREQUENCY(MATCH(B1:B11,B1:B11,0),MATCH(B1:B11,B1:B11,0))>0,1)))

非常感谢您的帮助！

推荐答案

个人而言，我更喜欢使用数据透视表。对于任何大于偶尔的电子表格大小的事件，Microsoft Access或其他数据库中的SQL查询将赢得游戏。

Personally, I prefer to do that using Pivot Tables. For anything larger than an occasional spreadsheet, SQL queries in Microsoft Access or other databases win the game.

这篇关于如何计算Excel中每个类别的唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！