20个Excel函数公式的经典案例,助你加职升薪
傻⼦才去记Excel的函数,聪明⼈都⽤这招
上回这篇⽂章⾮常受欢迎,但其实并⾮完整版,现在全部补上。内含⼤量⼲货,建议收藏起来
慢慢学习!
1、根据成绩的⽐重,获取学期成绩。
=C8*$C$5+D8*$D$5+E8*$E$5
引⽤⽅式有绝对引⽤、混合引⽤、相对引⽤,可以借助F4键快速切换。
如果学了SUMPRODUCT函数,也可以换种⽅式。
=SUMPRODUCT(C8:E8,$C$5:$E$5)
2、根据成绩的区间判断,获取等级。
=IF(B5>=90,"优秀",IF(B5>=80,"良","及格"))
IF函数语法:
=IF(条件,条件为真返回值,条件为假返回值)
IF函数图解
3、重量±5以内为合格,否则不合格。
=IF(AND(A4>=-5,A4<=5),"合格","不合格")
=IF(ABS(A4)<=5,"合格","不合格")
AND函数当所有条件都满⾜的时候返回TRUE,否则返回FALSE。
ABS是返回数字的绝对值。
4、根据对应表,查询2⽉销量。
=VLOOKUP(A4,F:G,2,0)
VLOOKUP函数语法:
=VLOOKUP(查值,在哪个区域查,返回区域第⼏列,精确或模糊匹配)
第4参数为0时为精确匹配,1时为模糊匹配。
VLOOKUP函数图解
5、根据查询品名和型号。
=VLOOKUP($A4,$E:$G,2,0)
=VLOOKUP($A4,$E:$G,3,0)
正常情况下可以⽤VLOOKUP函数,然后将参数3分别设置为2和3,不过考虑到列数可能⽐较
多,也就是通⽤的情况下,所以⽤COLUMN函数作为第3参数。这个函数是获取列号,B1的列
号就是2,C1的列号就是3,依次类推。
=VLOOKUP($A4,$E:$G,COLUMN(B1),0)
6、正确显⽰⽂本+⽇期的组合。
=A4&TEXT(B4,"!_yyyy-m-d")
=A4&TEXT(B4,"!_e-m-d")
&的作⽤就是将两个内容合并起来,不过遇到⽇期,合并后⽇期就变成数字。有⽇期存在的情况下要借助TEXT函数,显⽰年⽉⽇的形式⽤yyyy-m-d,4位数的年份也可以⽤e代替。这⾥添加_是为了防⽌以后有需要处理,可以借助这个分隔符号分开,因为是特殊字符前⾯加!强制显⽰。
7、计算收⼊⼤于3万的⼈的累计收⼊总和。
=SUMIF(C:C,">30000", C:C)
SUMIF函数语法:
=SUMIF(条件区域,条件,求和区域)
对区域进⾏条件求和。
8、序列号为102开头的累计收⼊总和。
=SUMIF(A:A,"102*",C:C)
通配符号有2个,⼀个是*代表全部,102开头就是102*,如果是包含102⽤*102*。另⼀个通配符是?代表⼀个字符,⽐如现在有3个字符,就⽤???。
说明:通配符只能针对⽂本格式进⾏处理,数字格式的序列号不可以⽤。
9、统计每⼀种⽔果的购买次数。
10、统计每⼀种⽔果运费⼤于20元的次数。
=COUNTIF(B:B,G5)
=COUNTIFS(B:B,G14,E:E,">20")
COUNTIF函数语法:
=COUNTIF(条件区域,条件)
COUNTIFS函数语法:
=COUNTIFS(条件区域1,条件1,条件区域2,条件2……)
COUNTIF(COUNTIFS)对区域进⾏条件计数,有S可以多条件计数。
11、宝贝标题包括⽿钉,就返回⾸饰,否则为其他。
=IF(COUNTIF(A4,"*⽿钉*"),"⾸饰","其他")
=IF(ISERROR(FIND("⽿钉",A4)),"其他","⾸饰")
根据SUMIF函数⽀持通配符的特点,COUNTIF函数也⽀持,包含就⽤*⽿钉*。
当然也能借助FIND函数判断,如果有出现就返回数字,否则返回错误值,⽽ISERROR函数就是判断是否为错误值。
12、根据⾝份证号码,获取性别、⽣⽇、周岁。
性别:从15位提取3位,如果奇数就是男,偶数就是⼥。
=IF(MOD(MID(A4,15,3),2),"男","⼥")
MOD函数就是取余数的意思,奇数除以2的余数就是1,偶数除以2的余数就是0。1在这⾥相当于TRUE也就是返回男,0就是FALSE返回⼥。
⾼版本中⽤ISODD函数判断是不是奇数,⽤ISEVEN函数判断是不是偶数,所有也可以将公式改成⾼
版本的。
=IF(ISODD(MID(A2,15,3)),"男","⼥")
⽣⽇:从第7位提取8位,设置公式后将单元格设置为⽇期格式。
=--TEXT(MID(A4,7,8),"0-00-00")
周岁:
=DATEDIF(D4,TODAY(),"y")
TODAY也可以换成NOW。
13、把歌曲和作者合并到⼀个单元格。
=A4&"-"&B4
&就是将字符连接起来,叫连字符。
sumif函数的使用方法绝对引用14、将字符串合并成⼀个单元格。
=PHONETIC(A4:K4)
PHONETIC这是⼀个很神奇的⽂本合并函数,可以轻松将内容合并起来,不过只针对⽂本,切记!
如果PHONETIC解决不定,下⾯这篇⽂章就可以解决。
前14个运⽤属于基础运⽤有详细解释,后6个属于进阶需要靠⾃⼰动脑思考。
15、根据产品名称和城市查询销售额
=VLOOKUP(G4,$A$3:$E$9,MATCH(H4,$A$3:$E$3,0),0)
=SUMPRODUCT(($A$4:$A$9=G4)*($B$3:$E$3=H4)*$B$4:$E$9)
16、品牌⽉度销售额查询
=SUMIFS(C:C,A:A,E4,B:B,F4)
17、分别提取产品和编码
=LEFT(A4,LENB(A4)-LEN(A4))
=RIGHT(A4,2*LEN(A4)-LENB(A4))
18、从起始时间提取⽇期和时间
=--LEFT(A4,FIND(" ",A4)-1)
=--RIGHT(A4,LEN(A4)-FIND(" ",A4))
19、将省份(区)和城市分离出来
=LEFT(A4,FIND(IF(ISNUMBER(FIND("区",A4)),"区","省"),A4))
=RIGHT(A4,LEN(A4)-LEN(B4))
20、知道某⽇期,获取下个⽉第⼀天
作者:卢⼦,清华畅销书作者,《Excel效率⼿册早做完,不加班》系列丛书创始⼈,个⼈:Excel不加班(ID:Excelbujiaban)

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。