跳到主要内容

如何在Excel中唯一地对范围号进行排名而不重复？

在Microsoft Excel中，普通等级函数将重复的数字赋予相同的等级。 例如，如果数字100在所选范围内出现两次，并且第一个数字100的排名为1，则最后一个数字100的排名也为1，这将跳过某些数字。 但是，有时，您需要按照以下屏幕快照对这些值进行唯一排序。 有关唯一排名的更多详细信息，请按照以下教程逐步进行。

以唯一的降序排列范围编号

以唯一的升序排列范围编号

以唯一的降序排列范围编号

在本节中，我们将向您展示如何以降序唯一地对范围编号进行排名。

以下面的截图数据为例，可以看到A2：A11范围内有多个重复的数字。

1.选择B2，复制并粘贴公式 =RANK(A2,$A$2:$A$11,0)+COUNTIF($A$2:A2,A2)-1 到 配方栏，然后按 输入 键。 看截图：

2.然后，排名编号显示在单元格B2中。 选择单元格B2并将光标放在其右下角，当显示一个小的黑色十字时，将其向下拖动到单元格B11。 然后，唯一排名成功。 看截图：

以唯一的升序排列范围编号

如果要按升序唯一地对范围编号进行排名，请执行以下操作。

1.选择单元格B2，复制并粘贴公式 =RANK(A2,$A$2:$A$11,1)+COUNTIF($A$2:A2,A2)-1 到 配方栏，然后按 输入 键。 然后，第一个排名编号显示在单元格B2中。

2.选择单元格B2，将填充手柄向下拖动到单元格B11，然后完成唯一排名。

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (27)
No ratings yet. Be the first to rate!
iben
iben
This comment was minimized by the moderator on the site
Hi every one, i have this formula
=RIJEN($H$2:$H$7)-SOMPRODUCT(--(H2-E2/10000>$H$2:$H$7-$E$2:$E$7/10000))

it determines the rank of a value in a column. if there is an equal value, the rank is determined by a 2nd value in another column. Now the problem is when negative numbers occur in the 2nd column, the rank is determined inversely -1 gets a lower rank than 0

can anyone adjust this formula or create a new one for excel 2007 dutch version
Reply
0
0
Iben
Iben
This comment was minimized by the moderator on the site
Hi every one, i have this formula
=RIJEN($H$2:$H$7)-SOMPRODUCT(--(H2-E2/10000>$H$2:$H$7-$E$2:$E$7/10000))

it determines the rank of a value in a column. if there is an equal value, the rank is determined by a 2nd value in another column. Now the problem is when negative numbers occur in the 2nd column, the rank is determined inversely -1 gets a lower rank than 0

can anyone adjust this formula or create a new one for excel 2007 dutch version

thanks
Reply
0
0
JustZ52
JustZ52
This comment was minimized by the moderator on the site
Hey so I just worked on this formula for the past 45 minutes. The above formula is wrong, the output does provide duplicates but that is only because the countif range is not accounting for the entire range.

Above the descending formula is: =RANK(A2,$A$2:$A$11,0)+COUNTIF($A$2:A2,A2)-1

The bold/underlined A2 cell should be equal to the ending of the range which is A11. Which would make correct forumla stance:

=round(RANK(A2,$A$2:$A$11,0)+COUNTIF($A$2:A7,A2)-1

For visuals, on my sheet I created a leader tracking board with the following formula and I got no duplicates see below 2 images with duplicate numbers but different ranking levels:


My Descending formula: =round(RANK(D7,$D$7:$BY$7)+countif($D$7:$BY$7,D7)-1)
Key Notes:
- My range is locked
- D7 is the start of my range and BY7 is the end of my range
- I have added the round formula to account for any decimals
- the (-1) will automatically subtract from the previous ranking.
View Attachments (2 / 5)
Reply
Report
0
0
crystal
crystal     JustZ52
This comment was minimized by the moderator on the site
Hi Zoe,
Thank you for your feedback. I will check the formula and make the changes.
Reply
Report
0
0
Rofijul Islam
Rofijul Islam
This comment was minimized by the moderator on the site
It's very helpful content. Thank You so much.
Reply
0
0
Sofi
Sofi
This comment was minimized by the moderator on the site
Thank you for the solution! Works perfectly!
Reply
0
0
Rick
Rick
This comment was minimized by the moderator on the site
This tip was PERFECT in solving my sorting issue! Thanks very much!!!
Reply
0
0
Brett
Brett
This comment was minimized by the moderator on the site
I still have duplicate rankings. Before I used COUNTIF the rankings were 4 for both, now they are both ranked 5 with COUNTIF BUT in my chart, it will only list one of the items twice instead of listing both items. Here is my formula: =RANK(V3,V$3:V$30, 1)+COUNTIF(V$3:V$30:V3,V3)-1
In the ranking chart it displays rank 4 as team 1 and rank 5 as team 1 since team 1 and team 2 have the same rank, team 2 is not listed. Here is the formula for those cells:=INDEX($L$3:$L$30,MATCH(SMALL($V$3:$V$30,Y3),$V$3:$V$30,0))
How do I make sure teams that are tied do not get left off list?


Reply
0
0
Ron
Ron
This comment was minimized by the moderator on the site
Formulas for unique ascending and descending are identical. Ascending should be: =RANK(A2,$A$2:$A$11,1)+COUNTIF(A2:$A$2,A2)-1.
Reply
0
0
vikas
vikas
This comment was minimized by the moderator on the site
if you see the below ranking, where the duplicate numbers found, number is missing.
Exmples
After 23 it goes to 25, after 29 it goes to 30, Formula miss the next number like - 24 & 29

Score Rank

4661 21

4650 22

4648 23

4645 25

4645 25

4644 26

4629 27

4626 28

4595 30

4595 30

4578 31

4438 32
Reply
0
0
Sifa
Sifa     vikas
This comment was minimized by the moderator on the site
What's the formula for this ranking where it combines same values into one ranking???
Reply
0
0
Musawir
Musawir
This comment was minimized by the moderator on the site
Hi, This formula just ranks all numbers in ascending/descending order and doesn't combine the duplicates into one rank. How do I combine the ranking for duplicates, for example two duplicate 100s to be combined and both ranked as 1, 3 duplicate 90s to be combined and ranked as 2, 2 duplicate 80s ranked as 3, etc.? In your example of total 10 students I want a formula that will rank the two 100s as 1, rank the two 90s as 2, rank the two 80s as 3, etc. Please help. Thanks
Reply
0
0
Alex
Alex     Musawir
This comment was minimized by the moderator on the site
if you do want this kind of ranking, what I would do is copy the whole list somewhere else, then use remove duplicates in the Data menu to leave only the individual values. rank these individual scores using a standard RANK formula and then use a simple VLOOKUP formula to return the value against the score in the full list.

this will show the values the way you want above.
Reply
0
0
Alex
Alex     Musawir
This comment was minimized by the moderator on the site
Why do you need to do this? a standard RANK formula will rank these examples as 1, 1, 3, 3, 5, 5. this is a much more usual way of ranking as if there are 2 first places the next in line would usually logically be 3rd not 2nd.
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations