Excel:查找范围内最频繁出现的值吗? [英] Excel: Find most frequent occurring value in a range?
问题描述
我在工作表1上有一列,如下所示:
I have a column on sheet 1 like so:
Column D
Dog
Dog
Dog
Dog
Cat
Cat
Cat
Hamster
Frog
Frog
在工作表2上,我想按时间顺序列出前10个最频繁出现的单词
On sheet 2, i want to list the top 10 most frequent occurring words in chronological order
Dog . <---Most Frequent
Cat . <---Second Frequent
Frog . <--Third
etc.
我正在尝试像这样使用索引,模式和匹配项:
I am trying to use index, mode and match like so:
=INDEX(Sheet1!D:D,MODE(MATCH(Sheet1!D:D,Sheet1!D:D,0)))
这会产生N/A错误
但是,如果我将范围添加到我的列引用中,就像这样:
But if i add ranges to my column reference like so:
=INDEX(Sheet1!D1:D10,MODE(MATCH(Sheet1!D1:D10,Sheet1!D1:D10,0)))
然后它起作用.
问题是数据要添加到列D中,我需要它来查找整个列D.
Problem is data is going to be added to columnn D and i need it to look up the entire column D.
请有人可以告诉我我要去哪里了吗?
Please can someone show me where i am going wrong?
推荐答案
如果使用帮助器列,则可以完全消除对数组公式的需要.在 E1 中输入1
,在 E2 中输入:
If we use a helper column we can completely eliminate the need for array formulas. In E1 enter 1
and in E2 enter:
=IF(COUNTIF($D$1:D1,D2)>0,"",1+MAX($E$1:E1))
,然后向下复制到 D 列. (随着项目添加到列 D 中,也将列 E 公式也下拉)
and copy down as far as column D. (as items are added to column D, pull the column E formula down as well)
最后在 F1 中输入:
=IFERROR(INDEX(D:D,MATCH(ROWS($1:1),E:E,0)),"")
然后抄下来.在 G1 中输入:
and copy down. In G1 enter:
=COUNTIF(D:D,F1)
并复制下来:
注意:
F 和 G 列中的小频率表是可排序的".
The little frequency table in columns F and G is "sort-able".
这篇关于Excel:查找范围内最频繁出现的值吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!