必看!最好用的28个Excel公式( 二 ) _知识经验

15.获取总金额，也就是数量*单价后再求和。

16.正确显示文本+日期的组合。

=A4&TEXT(B4,”!_yyyy-m-d”)
=A4&TEXT(B4,”!_e-m-d”)
&的作用就是将两个内容合并起来，不过遇到日期，合并后日期就变成数字。有日期存在的情况下要借助TEXT函数，显示年月日的形式用yyyy-m-d，4位数的年份也可以用e代替。这里添加_是为了防止以后有需要处理，可以借助这个分隔符号分开，因为是特殊字符前面加!强制显示。
17.计算收入大于3万的人的累计收入总和。

=SUMIF(C:C,”>30000″, C:C)
SUMIF函数语法：
=SUMIF(条件区域,条件,求和区域)
对区域进行条件求和。
18.序列号为102开头的累计收入总和。

=SUMIF(A:A,”102*”,C:C)
通配符号有2个，一个是*代表全部，102开头就是102*，如果是包含102用*102* 。另一个通配符是?代表一个字符，比如现在有3个字符，就用??? 。
说明：通配符只能针对文本格式进行处理，数字格式的序列号不可以用。
19.统计每一种水果的购买次数和统计每一种水果运费大于20元的次数。

=COUNTIF(B:B,G5)
=COUNTIFS(B:B,G14,E:E,”>20″)
COUNTIF函数语法：
=COUNTIF(条件区域,条件)
COUNTIFS函数语法：
=COUNTIFS(条件区域1,条件1,条件区域2,条件2……)
COUNTIF(COUNTIFS)对区域进行条件计数，有S可以多条件计数。
20.宝贝标题包括耳钉，就返回首饰，否则为其他。

=IF(COUNTIF(A4,”*耳钉*”),”首饰”,”其他”)
=IF(ISERROR(FIND(“耳钉”,A4)),”其他”,”首饰”)
根据SUMIF函数支持通配符的特点，COUNTIF函数也支持，包含就用*耳钉* 。
当然也能借助FIND函数判断，如果有出现就返回数字，否则返回错误值，而ISERROR函数就是判断是否为错误值。
21.根据身份证号码，获取性别、生日、周岁。

性别：从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 。
22.把歌曲和作者合并到一个单元格。

=A4&”-“&B4
&就是将字符连接起来，叫连字符。
23.将字符串合并成一个单元格。

必看!最好用的28个Excel公式( 二 )

猜你喜欢