文:老祝
今天和大家分享一组常用的函数套路,大家遇到类似问题可以直接拿来套用即可。
1、常规查询
(资料图片仅供参考)
如下图所示,要根据G2单元格姓名,在A~E数据区域中查询对应的年龄。
经典套路:
=VLOOKUP(G2,B1:E6,4,0)
套路指南:
第三参数,指定要返回查询区域中第几列的内容,不是整个工作表的第几列。
2、逆向查询
如下图所示,要根据G2单元格姓名,在A~E数据区域中查询对应的工号。
经典套路:
=LOOKUP(1,0/(G2=B2:B6),A2:A6)
套路指南:
=LOOKUP(1,0/(条件区域=指定条件),要返回的区域)
可以根据需要,将公式中的 0/(条件区域=指定条件),写成:
0/((条件区域1=指定条件1)*(条件区域2=指定条件2)*……)
从而实现任意角度的多条件查询。
3、查询万金油
如下图所示,要根据H2单元格姓名,查询所在的部门。
经典套路:
=INDEX(B1:F1,MATCH(H2,B2:F2,))
套路指南:
由MATCH函数找到查询值的精确位置,然后由INDEX函数返回指定区域中,对应位置的内容。可以实现上下左右任意方向的查询。
4、年龄计算
如下图所示,要根据C列的出生年月计算年龄。
经典套路:
=DATEDIF(C2,TODAY(),"y")
套路指南:
虽是隐藏函数,却早已是众人皆知的秘密。
第一参数是开始日期,第二参数是结束日期,第三参数是返回的数据类型。
使用Y,表示返回整年数。
使用M,则表示返回整月数。
5、身份证计算
如下图所示,要根据C列的身份证号码计算出生年月。
经典套路:
=--TEXT(MID(C2,7,8),"0-00-00")
套路指南:
先使用MID函数,从C2单元格提取出表示出生年月的8位数字,再用TEXT函数将其转换为日期样式的文本。
最后使用两个负号进行运算,变成真正的日期序列值。
6、个人所得税计算
如下图所示,要根据E列工资额计算个税。
经典套路:
=ROUND(MAX((E2-3500)*0.1*{0.3,1,2,2.5,3,3.5,4.5}-5*{0,21,111,201,551,1101,2701},0),2)
套路指南:
只要把公式中的E2换成实际的单元格引用即可,其他不用管它。
7、根据个税倒推税前工资
如下图所示,要根据E列个税金额计算税前工资。
经典套路:
=ROUND(MIN(((E2 5*{0,21,111,201,551,1101,2701})/({0.3,1,2,2.5,3,3.5,4.5}/10)) 3500),2)
套路指南:
注意个税额不能为0。
工资还不够纳税起点的,蓝瘦不?
8、中国式排名
如下图所示,要对E列的成绩进行中国式排名,也就是相同成绩不占用名次。
经典套路:
=SUMPRODUCT((E$2:E$6>E2)/COUNTIF(E$2:E$6,E$2:E$6)) 1
套路指南:
运算过程比较复杂,三言两语说不清了。
使用的时候,只要把公式中的单元格地址换成实际的数据区域即可。
[责任编辑:linlin]
个税反推税前工资公式(一组常用函数套路,Excel达人都在用)-天天
歌手赵英俊(赵英俊离世三天,绯闻女友袁姗姗发文悼念,心碎表情
晶钢门板(晶钢门板差距在哪里?可别买了劣质晶钢门)-快播
个性网名大全最新版的(慢热文静有深意的微信昵称个性气质)-环球
进口牛奶品牌(大喝三天,十二款新疆奶真实测评)
泾川(泾川:致富路上“牛”为伴)
金华赶集网(双峰:合演“售卖二手摩托车”骗局 夫妻双双获刑)-
金溪教育网(金溪县学前教育联盟骨干教师下乡听课评课(图))-焦
好听的个性网名大全(个性男生网名大全好听帅气-可爱点)-环球要闻
今年鬼节是几号(我国传统节日七月半鬼节的由来)
红烧鱼块怎么做最好吃(#福气年夜菜#爸爸的味道之红烧鱼块)-环球
洁面摩丝(这些洗面奶深层清洁让肌肤感觉清爽、柔嫩、润泽,如丝