在Excel中使用Rank函数 [英] Using the Rank Function In Excel

问题描述

对不起，如果这已经得到回答，我觉得可能有，但我很努力找到一个帮助我成功的答案。

我有一个基本电子表格，供时间试用。电子表格适用于男性和女性。基本上，积分在整个竞争对手中以最快的时间以30秒的时间间隔被罚款（Cloumn N）...（我已经管理了）

我的问题是 - 除此之外，排名第七的男子获得额外奖金积分和前3名（只因为通常少于男性参加比赛的女性），女性也获得额外奖励积分。

我添加了一个列来指定M或F（列C），同时使用RANK

  = IF（G7 = 0,0，RANK（G7，$ G $ 6：$ G $ 36,1）-COUNTIF（$ G $ 6：$ G $ 36,0））
  

在时间 - 列K

但我真的很挣扎如何使用公式来提取前7名男子和前3名女性，并获得积分。也就是第一名男子 - 第七名，也是第一名，女子第三名。所以在本质上我有什么办法可以从相应的列中识别F和M来提取两组排名。

目前我只能得到基本排名和使用IF（AND）语句我可以返回结果以应用奖励积分，如果条件匹配，但这不能帮助确定排名根据男性（第1至第7）或女性（第1 - 3） p>

你也可以在我的屏幕转储中看到，虽然我没有添加分配女性积分的公式，因为满足条件我没有获得第五名的奖励积分，因为我设置性别F，我希望有人也可以帮助我与

对于华夫饼抱歉，我现在已经3天了，我刚刚进入圈子

真的感谢任何回复

解决方案

只需使用COUNTIFS：

  = IF（G6 = 0,0，COUNTIFS（C：C，C6，G：G，& G6，G：G， -  0）+ 1）
  

喜欢C中的条目，从而给出两个一，一男一女。

要添加俱乐部，只需添加另一个条件：

  = IF（G6 = 0,0，COUNTIFS（C：C，C6，G：G，& G6，G：G ;> 0，B：B，B6）+1）
  

Sorry if this has been answered and I feel it may have but I am struggling to find an answer that helps me to the point of success.

I have a basic spreadsheet for time trial results. The spreadsheet is for both men and women. Basically, points are awarded for the quickest times throughout the entire competitors on 30 second intervals which is fine (Cloumn N)...(I have managed this)

My question is - On top of this the top 7 men in ranked position is awarded additional bonus points and the top 3 (only because there is normally less women attending the events than men) women are also additional awarded bonus points.

I have set up a column to specify M or F (Column C) when a competitor is added, and also using RANK

=IF(G7=0,0,RANK(G7,$G$6:$G$36,1)-COUNTIF($G$6:$G$36,0))

on the times - Column K

But I am really struggling with how to use a formula to extract the top 7 men and top 3 women and award the points. Ie there will be a 1st place man - 7th but also a 1st place woman - 3rd. So in essence is there any way I can extract the two sets of rankings from the identification of F and M from the appropriate column.

At the moment I can only get the a basic ranking and using an IF(AND) statement I can return results to apply the bonus points if the conditions are matched but this doesnt help with identifying the rankings according to Male (1st-7th) or Female (1st-3rd)

You can also see on my screen dump that although I havent added the formula for assigning the female points that because of the conditions been met I dont have bonus points awarded for 5th place because I set sex to F which I was hoping someone could also help me with

Sorry for waffling but I have been toiling with this 3 days now and I am just going in circles

Really appreciate any reply

解决方案

Just use COUNTIFS:

=IF(G6=0,0,COUNTIFS(C:C,C6,G:G,"<" & G6,G:G,"<>0")+1)

This will rank the like entries in C, thus giving two 1st, one male and one female.

To add for the Club just add another condition:

=IF(G6=0,0,COUNTIFS(C:C,C6,G:G,"<" & G6,G:G,"<>0",B:B,B6)+1)

这篇关于在Excel中使用Rank函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！