excel - 如何在 Excel 中计算每个类别的唯一值

Question

我已经在这个网站和网上四处寻找这个问题的答案，但我还没有成功，所以我会在一个新的线程中提问。

我需要能够使用一个公式(或多个公式)计算 Excel 中每个类别的特定文本出现的次数。最好不使用 VBA 即可完成此操作，但如果有必要，我将尝试编写 VBA 命令。

这是我正在处理的内容:

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 工作表内的列表中，但单词可以是任意数量且未知。每个类别可以包含其他类别也可以包含的单词，最终结果应如下所示:

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

最后，我知道我可以对列表进行重复数据删除，但我希望出于存档目的保持列表完整，因此不需要重复数据删除的计数是理想的选择。

这是我尝试做的，但最终没有成功:

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

非常感谢您的帮助!

Answer 1

试试这个“数组公式”

=SUM(IF(FREQUENCY(IF(A$1:A$11=C1,IF(B$1:B$11<>"",MATCH(B$1:B$11,B$1:B$11,0))),ROW(B$1:B$11)-ROW(B$1)+1),1))

通过 CTRL+SHIFT+ENTER 确认

其中 C1 是第一个重要的类别。复制所有类别的公式

说明

该公式的基本思想是第一个IF是一个“过滤器”，以确保您仅在 A 值 = C1 时查看 B 值，第二个 IF过滤掉 B 中的空白，否则MATCH给出该值在 B 中的第一个匹配的位置(从而为相同的值提供相同的数字)

IFs的结果是 FREQUENCY 的第一部分功能。 FREQUENCY 中的“垃圾箱”由 ROW-ROW+1 部分组成，该部分为您提供 1 到 n 的数字范围，其中包含 MATCH 的整个值范围。可能会给。

现在我们只需要统计IF返回的不同数字的数量。 s(因为这等于您的标准中 B 中不同值的数量)，因此公式中的最后一个 1 被分配给 FREQUENCY 中的每个非零值。并对结果求和。

详细说明您的示例，其中 C1 = A 这部分

=IF(A$1:A$11=C1,IF(B$1:B$11<>"",MATCH(B$1:B$11,B$1:B$11,0)))

此数组中的结果

{1;2;2;4;FALSE;FALSE;FALSE;8;9;FALSE;FALSE}

FALSE 值，其中 col A <> "A"，否则编号为 MATCH给出每个值的第一个匹配的位置 - 注意 2 出现两次，因为 foo 出现两次，首先在第 2 行

和ROW(B$1:B$11)-ROW(B$1)+1部分给你这个数组

{1;2;3;4;5;6;7;8;9;10;11}

所以现在公式看起来像这样

=SUM(IF(FREQUENCY({1;2;2;4;FALSE;FALSE;FALSE;8;9;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11}),1))

所以FREQUENCY将第一个数组中的值放入相关的“bins”中，公式现在解析为此

=SUM(IF({1;2;0;1;0;0;0;1;1;0;0;0},1))

请注意 FREQUENCY 返回的数组总和为 6(值为“A”的六行)，但只有 5 个非零数字，表示标准“A”的五个不同值。

现在，最后一个 IF 为每个非零值返回 1，并且 SUM函数将这些相加得到 5