设为首页收藏本站

EPS数据狗论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 2348|回复: 1

[函数公式] 10个Excel函数公式实用技巧解读

[复制链接]

30

主题

220

金钱

355

积分

入门用户

发表于 2019-9-3 16:52:22 | 显示全部楼层 |阅读模式

一、标识重复内容。
1.gif
方法:
在目标单元格中输入公式:=IF(COUNTIF(E:E,E3)>1,"重复","")。

解读:
1、如果一个值重复,则其出现的次数肯定大于1,所以用Countif函数统计该值在指定范围内出现的次数,然后用IF函数判断其次数,如果>1,则返回“重复”,否则返回空值。
2、Countif函数为单条件计数函数,其语法结构为:=Countif(范围,计数的值)。


二、指定值第二次出现时提示。
2.gif
方法:
在目标单元格中输入公式:=IF(COUNTIF(E$3:E3,E3)>1,"重复","")。

解读:
1、 “指定值第二次出现时提示”,意思就是说,第一次出现时不予提示。此时,通过控制Countif函数的参数范围来实现,既然第一次出现不提示,参数范围就为此单元格本身,但第二次及以后出现提示,则此单元格也在计数范围之内,故需采用绝对引用和相对引用的方式进行。暨E$3:E3。引用方式的变化也是理解本公式的关键哦!


三、有重复则提示。
3.gif
方法:
在目标单元格中输入公式:=IF(COUNTIF(E$3:E$9,E3)>1,"重复","")。

解读:
1、“有重复则提示”意思就是说只要指定的值的次数>1,则进行提示。所以我们要在整个范围内查找指定的值,所以采用绝对引用的方式,暨E$3:E$9。
2、此公式的结果和=IF(COUNTIF(E:E,E3)>1,"重复","")的结果是相同的,但思路不仅相同,大家要注意理解。


四、根据身份证号或出生年月计算年龄。
4.gif
方法:
在目标单元格中输入公式:=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")。

解读:
1、年龄就是出生年和当前年的差值,所以用系统隐藏函数Datedif来实现,而给出的身份证号中包含了出生年月,所以用Mid函数提取出来,并用Text函数将其设置为时间(00-00-00)格式。
2、代码“y”的意思就是按年统计指定的两个值之间的差值。


五、根据身份证号码计算性别。
5.gif
方法:
在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。

解读:
1、身份证号的第17位值代表性别,如果能被2整除,则为女性,否则为男性。
2、所以利用Mid函数提取第17位置,然后用Mod函数求余,如果余数为1,则返回“男”,否则返回“女”。


六、匹配查询。
6.gif
方法:
在目标单元格中输入公式:=VLOOKUP(H3,B3:E9,4,0)。

解读:
Vlookup函数是常见的匹配查询函数,其语法结构为:=Vlookup(查询值,查询值和返回值所在范围,返回第X列的值,精准匹配还是模式匹配)。其中:精准匹配为0,模糊匹配为1。


七、美式排名。
7.gif
方法:
在目标单元格中输入公式:=RANK(E3,E$3:E$9,0)。

解读:
Rank函数的作用为:计算指定值在指定范围内的相对位置。其语法结构为:=Rank(排序值,数值范围,排序方式),排序方式有0和1两种,0为降序,1为升序。


八、中国式排名。
8.gif
方法:
在目标单元格中输入公式:=SUMPRODUCT(($E$3E$9>E3)/COUNTIF($E$3:$E$9,$E$3:$E$9))+1。


九、统计指定范围内的数量。
9.gif
方法:
在目标单元格中输入公式:=COUNTIF(E3:E9,">=40")。


十、统计指定范围内的和值。
10.gif
方法:
在目标单元格中输入公式:=SUMIF(E3:E9,">=40")-SUMIF(E3:E9,">=50")。

解读:
1、大于等于40小于50的销量和:就是大于等于40的销量和减去大于等于50的销量和。
2、除了用Sumif函数实现外,还可以用Sumifs函数来实现。

2

主题

2万

金钱

3万

积分

专家用户

发表于 2019-9-10 21:56:27 | 显示全部楼层
很好的技巧,感谢分享!
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /1 下一条

客服中心
关闭
在线时间:
周一~周五
8:30-17:30
QQ群:
653541906
联系电话:
010-85786021-8017
在线咨询
客服中心

意见反馈|网站地图|手机版|小黑屋|EPS数据狗论坛 ( 京ICP备09019565号-3 )   

Powered by BFIT! X3.4

© 2008-2028 BFIT Inc.

快速回复 返回顶部 返回列表