Excel:当A列匹配时，计算B列中的唯一文本 [英] Excel: Count unique text in column B when column A matches

问题描述

当A列匹配时，我试图计算B列中的唯一文本值.

I'm trying to count unique text values in column B when column A matches.

假设我有

A 1    
A 1    
B 1    
B 2    
C 2    
C 2    
C 2

我希望它退回

A has one unique text in column B    
B has two unique texts in column B    
C has one unique texts in column B

我将使用哪个Excel公式?

Which Excel formula would I use?

推荐答案

使用公式，您将需要几个帮助器列:

With formula you are going to need a couple of helper columns:

首先，我们将此数组公式放在 SECOND 行中:

In the first we put this array formula in the SECOND row:

=INDEX($A$1:INDEX(A:A,MATCH("zzz",A:A)),MATCH(0,COUNTIF($D$1:D1,$A$1:INDEX(A:A,MATCH("zzz",A:A))),0))

然后在其旁边的列中显示此数组公式:

Then in the column next to it this array formula:

=SUM(IF($A$1:INDEX(A:A,MATCH("zzz",A:A))=D2,1/COUNTIFS($A$1:INDEX(A:A,MATCH("zzz",A:A)),D2,$B$1:INDEX(B:B,MATCH("zzz",A:A)),$B$1:INDEX(B:B,MATCH("zzz",A:A)))))

都是数组公式.作为数组公式，退出编辑模式时必须使用Ctrl-Shift-Enter而不是Enter进行确认.如果操作正确，则Excel会将 {} 放在公式周围.

Both are array formulas. Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

然后在第三列中，我们可以输入文本:

Then in the third column we can put the text:

=D2 & " has " & CHOOSE(E2,"one","two","three") & " unique " & IF(E2>1,"texts","text")& " in column B"

如果唯一性大于三个，则需要输入等效文本.

If you have greater than three unique you will need to put the text equivalents.

这篇关于Excel:当A列匹配时，计算B列中的唯一文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！