英文:
How to return the top 10 more frequent column values in excel?
问题
我有一个包含三列的Excel表格，在“id”列中有一些数字并且有一些重复。如何在Excel中将“id”列中出现频率最高的前10个值获取到一个列表中？
到目前为止，我编写了以下代码：
=INDEX(B1:B10;MATCH(LARGE(FREQUENCY(B1:B10;B1:B10);2);FREQUENCY(B1:B10;B1:B10);0))
范围：B1:B10
但是这只会逐个给出第1、2、...最频繁的值，我希望得到一个包含前10个最频繁值的列表。
附注：我使用的是MacOS。
英文:
I have an excel with three columns and in the column "id" there are some numbers with some repetitions. How can I get in a list the top 10 more frequent column values ("id" column) in excel?
So far I made this code:
=INDEX(B1:B10;MATCH(LARGE(FREQUENCY(B1:B10;B1:B10);2);FREQUENCY(B1:B10;B1:B10);0))
range: B1:B10
but it gives only one by one the 1th, 2th, ... more frequent value, what I'd like is to have an only list with the top 10 more frequent values.
PS: I have a MacOS
答案1
得分: 1
以下是翻译好的部分：
以下的内容仍然适用，即使有多个具有相同频率的 ID：
=LET(x, A2:A12, top, 3, cnts, COUNTIFS(x, x),
TAKE(SORT(UNIQUE(HSTACK(x, cnts)), 2, -1), top, 1))
如@JosWoolley在评论中指出的，您可以使用 SORTBY 替代 SORT，这将生成一个排序公式：
=LET(x, A2:A12, top, 3, cnts, COUNTIFS(x, x), TAKE(UNIQUE(SORTBY(x, cnts, -1)), top))
您还可以使用您的方法，但您忘记在 LARGE 的第二个输入参数中使用 SEQUENCE，但它无法选择作为顶部一部分的重复频率：
=LET(A, A2:A12, top, 3, freq, FREQUENCY(A, A),
INDEX(A, MATCH(LARGE(freq, SEQUENCE(top)), freq, 0)))
这是输出：
上述公式假定使用 Office 365，对于较旧的版本，您可以进行以下替换：
TAKE(x, top, [y])->INDEX(x, SEQUENCE(top), [y])HSTACK(x, y)->CHOOSE({1,2}, x, y)
英文:
The following works even, there are several ids with the same frequency:
=LET(x, A2:A12,top,3, cnts,COUNTIFS(x,x),
TAKE(SORT(UNIQUE(HSTACK(x,cnts)),2,-1),top,1))
As @JosWoolley pointed out in the comment section, you can use SORTBY instead of SORT which produces a sorter formula:
=LET(x,A2:A12, top,3, cnts,COUNTIFS(x,x),TAKE(UNIQUE(SORTBY(x,cnts,-1)),top))
You can also use your approach, you missed using SEQUENCE as the second input argument for LARGE, but it doesn't work for repeated frequencies being selected as part of the top:
=LET(A,A2:A12, top,3, freq,FREQUENCY(A,A),
INDEX(A,MATCH(LARGE(freq,SEQUENCE(top)),freq,0)))
Here is the output:
The above formulas assume O365, for older versions, you can make the following substitutions:
TAKE(x,top,[y])->INDEX(x, SEQUENCE(top),[y])HSTACK(x,y)->CHOOSE({1,2},x,y)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论