excel学习库

1.MAX或MIN+IF组合函数

MAX/MIN+IF组合函数的作用是限定条件下求目标数据的最大值或最小值。如图1所示，要分别求采购部的最低薪资和最高薪资，其公式分别为：=MIN(IF(E2=B2:B8,C2:C8))，=MAX(IF(E5=B2:B8,C2:C8))，由于涉及到数组计算，公式输入完成，最后记得同时按下Ctrl+Shift+Enter三键。

忽略逻辑值

2.INDEX+MATCH组合万能查询

INDEX+MATCH组合函数作用是查询区域样本中的目标数据，堪称查询里的万能函数，支持正向和逆向查询，灵活多变。如图2所示，要根据姓名查询对应的薪资，其公式为：=INDEX(C1:C8,MATCH(E2,A1:A8))。

MATCH函数的用法是=MATCH(查询值，查询区域），作用是返回查询值在查询区域中的位置。MATCH(E2,A1:A8)就能查询到赵六在A1:A8中所处的位置即第5个。

3.VLOOKUP+IF组合逆向查询

VLOOKUP+IF组合能进行逆向查询，单一的VLOOKUP函数只能进行顺向查询即从左往右查询，而不支持从右往左。如图3所示，要根据薪资查询对应的姓名，其公式为：=VLOOKUP(F2,IF({1,0},C1:C8,A1:A8),2,0)。

VLOOKUP函数用法是=VLOOKUP(查询值，查询区域，列数，匹配方式)，其中“列数”为函数返回值在“查询区域”中的位置即第几列，匹配方式为0或FALSE是精确匹配，为1或TRUE是模糊匹配，一般情况下都使用精确匹配。

IF函数用法为=IF(条件，真值：满足条件返回的值，假值：不满足条件返回的值），数组{1,0}为1时，返回真值即C1:C8，数组{1,0}为0时，返回假值即A1:A8，IF({1,0},C1:C8,A1:A8)返回的结果即为C1:C8和A1:A8两个相邻的区域。

4.VLOOKUP+MATCH组合查询函数

VLOOKUP函数的用法是=VLOOKUP(查询值，查询区域，查询值对应的返回值所在的列数，精确匹配或模糊匹配)，在实际工作当中，如果需要查询的数据样本量本身比较少，我们肉眼就可以判断“查询值对应的返回值所在的列数”，但是如果数据样本量比较繁杂庞大，就很难直接去判断“查询值对应的返回值所在的列数”，为了应对这种情况，VLOOKUP+MATCH组合就应用而生了。

前面已经讲过了，MATCH函数的用法是=MATCH(查询值，查询区域），作用是返回查询值在查询区域中的位置。要知道的是，“查询区域”可为纵向数据区域，也可为横向数据区域，若为纵向数据区域，即返回查询值在数据区域中所处的行数，若为横向数据区域，即返回查询值在数据区域中所处的列数。注意：这里的行数或列数指的是“查询区域”对应的行数或列数，而不是工作表所对应的行数或列数。

如图4所示，要根据姓名查询对应的薪资，其公式为：=VLOOKUP(H2,A1:F8,MATCH(I1,A1:F1,0),0)。

5.IF+MOD+MID组合函数

IF+MOD+MID函数可以根据身份证号判断男女性别，MID函数是提取字符函数，用法是=MID（要提取的字符串，从第几位开始提取，提取几个），MOD函数是求余函数，用法是=MOD(被除数，除数)，身份证倒数第二位为性别码，性别码是偶数为女，是奇数为男。当除数为2时，MOD返回的余数若为0，则表明被除数是偶数，反之则为奇数。再通过IF函数判断奇偶性得出男女性别。

如图5所示，根据身份证号求性别，其公式为：=IF(MOD(MID(B2,17,1),2)=0,"女","男")。

6.TEXT+MID组合函数

TEXT+MID组合函数可以将提取出的字符串转化为实际所需的某种格式。如图6所示，以根据身份证号提取出生日期为例，其公式为：=TEXT(MID(B2,7,8),"00-00-00")。

MID函数上面讲过了，用于从原字符串中提取自己所需的字符，MID(B2,17,1)返回身份证号中对应的出生年月，再用TEXT函数将出生年月转化为“00-00-00”的日期格式。

7.LEN+SUBSTITUTE组合函数

LEN函数返回某个字符串的长度，SUBSTITUTE函数作用是将一个字符替换为另一个字符，用法为=SUBSTITUTE(字符串，要替换的字符串，替换为新的字符串)，LEN+SUBSTITUTE组合函数用于计算一个单元格内有多少个项目。

如图7所示，求套餐内菜的个数，其公式为：=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1。

SUBSTITUTE(B2,"、","")作用就是将顿号全部替换为空值，LEN(SUBSTITUTE(B2,"、",""))就是计算去掉顿号后字符串的长度，LEN(B2)为有顿号字符串的长度，LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))是用有顿号的字符长度减去没顿号的字符长度，得出的即为顿号的个数，顿号的个数再加上个1即为菜的个数。

以上，就是小包为大家总结的关于Excel中经常使用到的函数组合，关注小包，后面继续为小伙伴们分享实用的Excel职场操作技巧。