admin 管理员组文章数量: 1184232
I have the following list
Jon
James
Peter
Liz
Sabrina
James
Maria
Jon
Liz
Liz
Sarah
...
I then want a list that shows me how many times each name occurs. Then it should give me a rank for each name, if a rank has ties, I want to re-number them alphabetically.
So the list should look like this:
| (A) Name | (B) How often | (C) Rank # |
|---|---|---|
| Jon | 2 | 3 |
| James | 2 | 2 |
| Peter | 1 | 5 |
| Liz | 3 | 1 |
| Sabrina | 1 | 6 |
| Maria | 1 | 4 |
| Sarah | 1 | 7 |
I have the following list
Jon
James
Peter
Liz
Sabrina
James
Maria
Jon
Liz
Liz
Sarah
...
I then want a list that shows me how many times each name occurs. Then it should give me a rank for each name, if a rank has ties, I want to re-number them alphabetically.
So the list should look like this:
| (A) Name | (B) How often | (C) Rank # |
|---|---|---|
| Jon | 2 | 3 |
| James | 2 | 2 |
| Peter | 1 | 5 |
| Liz | 3 | 1 |
| Sabrina | 1 | 6 |
| Maria | 1 | 4 |
| Sarah | 1 | 7 |
And then in a seperate list I only want to show the top 5, sorted by their rank.
When I rank them from column B, I allways get duplicated ranks, for example with
=ARRAYFORMULA(IF(B2:B<>""; RANK(B2:B; B2:B; 0); ""))
3 Answers
Reset to default 1You may use the following formulas-
=XMATCH(C2,SORT($C$2:$C$8,1,FALSE))+COUNTIFS($C$2:C2,C2)-1
=RANK(C2,$C$2:$C$8,0)+(SUM(INDEX(--($C$2:C2=C2))))-1
Or all in one formula in cell G2. Adjust the formula as per need.
=LET(x,QUERY(A2:A,"select A, count(A) where A is not null group by A label count(A) ''"),
y,CHOOSECOLS(x,2),
z,MAP(y,SEQUENCE(ROWS(y)),LAMBDA(r,rw,RANK(r,y,0)+(SUM(INDEX(--(QUERY(y,"limit " &rw)=r))))-1)),HSTACK(x,z))
And to keep only top 5 values (from lowest to highest rank). Use QUERY() or SORTN() function.
=SORTN(LET(x,QUERY(A2:A,"select A, count(A) where A is not null group by A label count(A) ''"),
y,CHOOSECOLS(x,2),
z,MAP(y,SEQUENCE(ROWS(y)),LAMBDA(r,rw,RANK(r,y,0)+(SUM(INDEX(--(QUERY(y,"limit " &rw)=r))))-1)),HSTACK(x,z)),5,,3,1)
Output from above formula.
| Names | How Often | Rank |
|---|---|---|
| Liz | 3 | 1 |
| James | 2 | 2 |
| Jon | 2 | 3 |
| Maria | 1 | 4 |
| Peter | 1 | 5 |
Here's another solution:
={
QUERY(A2:A,
"SELECT Col1, COUNT(Col1)
WHERE Col1 IS NOT NULL
GROUP BY Col1
ORDER BY COUNT(Col1) DESC
LABEL COUNT(Col1)''"),
SEQUENCE(COUNTUNIQUE(A2:A))
}
To get just the top 5, you can use:
=QUERY(
QUERY(A2:A,
"SELECT Col1, COUNT(Col1)
WHERE Col1 IS NOT NULL
GROUP BY Col1
ORDER BY COUNT(Col1) DESC
LABEL COUNT(Col1)''"
),
"SELECT Col1
LIMIT 5"
)
Try this alternative formula using Filter, Unique, CountIf
=ARRAYFORMULA(Sort({FILTER(UNIQUE(A2:A), COUNTIF(A2:A, UNIQUE(A2:A)) <> 0, COUNTIF(A2:A, UNIQUE(A2:A)) <> 8),FILTER(COUNTIF(A2:A, UNIQUE(A2:A)), COUNTIF(A2:A, UNIQUE(A2:A)) <> 0, COUNTIF(A2:A, UNIQUE(A2:A)) <> 8),FILTER(RANK(COUNTIF(A2:A, UNIQUE(A2:A)), COUNTIF(A2:A, UNIQUE(A2:A)), 0) + COUNTIFS(COUNTIF(A2:A, UNIQUE(A2:A)), COUNTIF(A2:A, UNIQUE(A2:A)), UNIQUE(A2:A), "<"&UNIQUE(A2:A)), COUNTIF(A2:A, UNIQUE(A2:A)) <> 0, COUNTIF(A2:A, UNIQUE(A2:A)) <> 8)}, 3, TRUE))
Sample Output
| (A) Name | (B) How often | (C) Rank # |
|---|---|---|
| James | 1 | 1 |
| Jon | 1 | 2 |
| Liz | 1 | 3 |
| Maria | 1 | 4 |
| Peter | 1 | 5 |
| Sabrina | 1 | 6 |
| Sarah | 1 | 7 |
本文标签：
版权声明：本文标题：google sheets - Count names in list, then count how many, rank, then show top 5 - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/p/1738717688a1998633.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
=RANK(B2,$B$2:$B$8,0)+(SUM(INDEX(--($B$2:B8=B2))))-1. – Harun24hr Commented Jan 20 at 4:23