如何查找与excel中的单词相关联的唯一值的数量 [英] How to find how many unique values are associated with a word in excel
问题描述
假设我有一个名为colors的列,其中填充了1000个单元格。一些单元格中有字 blue
。在另一列中,我有与颜色列对应的唯一标识符。例如 Blue
可以具有与其相关联的值01,02,04或05.出现 blue
我的名字列中有20次。什么是一种方式,我可以找到有多少唯一标识符与单词 blue
?
上面的答案应该返回4。
我使用的当前方法是通过使用枢轴表。我过滤掉名称列中不包括单词 blue
的任何值。
编辑:注意 blue
如何出现8次, ,2,3和4。
假设您的数据位于 A1:B21
请尝试:
在 D1:F1
输入这些 ArrayFormulas
CTRL + SHIFT + ENTER ,您将看到
{$ c $
p> In
D2
- 返回 Colors
值
: = IFERROR(
INDEX $ 2:$ A $ 21& $ B $ 2:$ B $ 21,
MATCH(0,COUNTIF($ D $ 1:$ D1,$ A $ 2:$ A $ 21& $ B $ 2:$ B $ 21) 0)* 1),)
在 E2
- 返回 Colors
:
= IFERROR(
INDEX($ A $ 2:$ A $ 21,
MATCH COUNTIF($ E $ 1:$ E1,$ A $ 2:$ A $ 21),0)* 1),)
b $ b
在 F2
- 退货 Colors
& 每个
的价值:
code> = COUNTIF($ D $ 2:$ D $ 21,$ E2&*)
然后复制 ArrayFormulas
$ c> D2:F2 到最后一行数据(即第21行)
列 / code>可以隐藏,如果需要...
Say I have one column called colors with 1000 cells populated with values. Some of the cells have the word blue
in it. In another column I have unique identifiers that correspond with the colors column. For example Blue
can have a value associated to it of 01, 02, 04, or 05. The word blue
appears 20 times within my name column. What is one way I can find how many unique identifiers are associated with the word blue
?
In the example listed above the answer should return 4.
The current method I am using to accomplish this is by using a pivot table. I filter out any value in the name column that doesnt include the word blue
. Then I count all the unique identifiers that appear in my pivot table.
EDIT: Notice how blue
appears 8 times, but it only has the values 1, 2, 3, and 4 associated with it. How can I create a function that finds out how many values are associated with blue?
Assuming your data is located at A1:B21
try this:
Enter the following headers at D1:F1
Enter these ArrayFormulas
FormulaArrays
are entered pressing CTRL+SHIFT+ENTER simultaneously, you shall see {
and }
around the formula if entered correctly
In D2
- Returns an unique combined list of Colors
& Values
:
=IFERROR(
INDEX($A$2:$A$21&$B$2:$B$21,
MATCH(0,COUNTIF($D$1:$D1,$A$2:$A$21&$B$2:$B$21),0)*1),"")
In E2
- Returns an unique list of Colors
:
=IFERROR(
INDEX($A$2:$A$21,
MATCH(0,COUNTIF($E$1:$E1,$A$2:$A$21),0)*1),"")
In F2
- Returns the count of combined Colors
& Values
for each Color
:
=COUNTIF($D$2:$D$21,$E2&"*")
Then copy the ArrayFormulas
in D2:F2
till last row of data (i.e. row 21)
Column D
could be hidden if required...
这篇关于如何查找与excel中的单词相关联的唯一值的数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!