在Excel表格中,有时我们需要录入个人信息,如手机号。那如何像131****5046这样隐藏手机号,保护好隐私呢?不要一个个输入符号,巧用REPLACE函数,快速处理大批量的手机号。 EXCEL中的REPLACE函......
2021-06-25 78 REPLACE函数
应用场景
下图工作表中显示了2组人员的分数,为方便大家阅读,第2组用金黄色底纹进行了突出显示。接下来我们要分不同的组别对每个小组中的人员分数进行排名。分数越高,名次越靠前,每个小组中分数最高的为小组第1名。
操作步骤
1.选中D2单元格,输入公式:=SUMPRODUCT(($A$2:$A$9=A2)*($C$2:$C$9>C2)/COUNTIF($C$2:$C$9,$C$2:$C$9))+1,输入完毕按回车键确认公式,即可返回“贾探春”在“1组”中的排名“1”。
公式说明:
$A$2:$A$9=A2为逻辑值,即判断$A$2:$A$9单元格中的值是否等于A2;
$C$2:$C$9>C2也是逻辑值,即判断$C$2:$C$9单元格中的值是否大于C2单元格中的值;
COUNTIF($C$2:$C$9,$C$2:$C$9)用于统计$C$2:$C$9单元格区域中每一行的值所出现的次数。
SUMPRODUCT(($A$2:$A$9=A2)*($C$2:$C$9>C2)/COUNTIF($C$2:$C$9,$C$2:$C$9))的含义是:在满足$A$2:$A$9单元格中的值为“1组”的前提下,统计$C$2:$C$9对应单元格中分数大于C2单元格(98)的分数个数。
因为大于C2单元格的分数个数要加上1,才能得到C2单元格自身的排序名次,所以公式的最后还要加上“+1”。
2.将公式向下填充,即可得到所有人员在所属小组中的排名。
注意事项
如果希望得到的排名是中国式排名(重复排名不占用名次),上述公式中的“/COUNTIF($C$2:$C$9,$C$2:$C$9)”不能省略。如果省略,得到的排名将是美国式排名,中间会有名次被跳过,参见下图E列,2组有两个第“1”名,第“2”名被跳过,接下去直接排到了第“3”名。
拓展知识
如果想对所有人员的分数整体进行中国式排名(不区分组别),可将前面案例公式中的第一个逻辑值($A$2:$A$9=A2)删除,将公式修改为“=SUMPRODUCT(($C$2:$C$9>C2)/COUNTIF($C$2:$C$9,$C$2:$C$9))+1”。
标签: SUMPRODUCT函数
相关文章
在Excel表格中,有时我们需要录入个人信息,如手机号。那如何像131****5046这样隐藏手机号,保护好隐私呢?不要一个个输入符号,巧用REPLACE函数,快速处理大批量的手机号。 EXCEL中的REPLACE函......
2021-06-25 78 REPLACE函数
LN函数的功能是计算一个数的自然对数,自然对数以常数项e(2.71828182845904)为底。LOG函数的功能是,计算按所指定的底数返回一个数的对数。LOG10函数的功能是计算以10为底的对数。三个函数的......
2021-06-25 492 LN函数,计算对数
DCOUNT函数用于返回数据清单或数据库中满足指定条件的列中包含数字的单元格个数。其语法如下: DCOUNT(database,fi eld,criteria) 其中,field参数为可选项,如果省略,DCOUNT函数将返回数据库中满足......
2021-06-25 279 DCOUNT函数,excel计算单元格数量
excel性别函数怎么用? excel性别函数的使用方法 首先我们需要新建一个工作簿来进行示范。 新建之后我们先确定我们输入身份证的行列,例如身份证在C3这行,那么我们需要输入的函数应该是......
2021-06-25 148 excel性别函数
EXP函数的功能是计算e的n次幂。EXP函数的语法如下: EXP(number) 其中,number参数为应用于底数e的指数。常数e等于2.71828182845904,是自然对数的底数。下面通过实例详细讲解该函数的使用方法与技......
2021-06-25 504 EXP函数,excel计算e的n次幂