【原】9个常用Excel公式，解决工作中80%的问题！

与 30万 粉丝一起学Excel

Excel公式千变万化，不用每个都学会，只要能将最常用的这9个都学会，并能灵活运用，就足够解决80%的问题。卢子从VIP群内整理出一些实际案例进行说明。

1.提取身份证倒数第2位

身份证有18位数，也就是提取第17位。

=MID(B2,17,1)

语法：

=MID(字符串,从第几位字符开始,提取几位)

提取倒数第2位，一般是为了判断性别，奇数为男，偶数为女。

=IF(MOD(C2,2),"男","女")

2.提取科目名称

科目编码、名称混合在一起，也就是提取第1个-符号之后的内容。FIND是判断-第1次出现的位置，再加1就是科目名称开始的位置，因为不知道要提取多少位，直接写99保证全部都提取到。

=MID(A2,FIND("-",A2)+1,99)

3.返回每个企业最后一次大于0的日期。

输入公式后，将单元格设置为日期格式。

=LOOKUP(1,0/(B2:G2>0),$B$1:$G$1)

语法：

=LOOKUP(1,0/(条件区域=条件),返回区域)

4.按照单位转换表，将货品名称转换成统一单位。

LOOKUP函数的经典查找模式，可以是单条件也可以是多条件，同时每个参数都可以嵌套其他函数，非常灵活。

=LOOKUP(1,0/((FIND(A2,$F$2:$F$8))*(B2=$G$2:$G$8)),$I$2:$I$8)*C2

5.格式不同的编码查找对应的本期发生额。

A2&""是将数字格式转换成文本格式，IFERROR是让找不到的返回0。

=IFERROR(VLOOKUP(A2&"",E:F,2,0),0)

语法：

=IFERROR(VLOOKUP(查找值,查找区域,返回区域第几列,0),错误值显示值)

6.根据商品名称、客户名称，查找成本表对应的成本价。

客户名称这个单元格不确定，可以改变。

返回区域第几列，现在不确定，因此不能用固定的数字，不过可以用MATCH判断返回第几列。

=MATCH($A$1,$E$2:$I$2,0)

语法：

=MATCH(查找值,查找区域,0)

将MATCH作为VLOOKUP的第3参数就可以。

=VLOOKUP(B3,E:I,MATCH($A$1,$E$2:$I$2,0),0)

7.根据收入、支出计算余额

E1是标题，直接运算的话会出错，嵌套N可以转换成0，这样就可以正常运算。

=N(E1)+C2-D2

8.根据项目名称统计收入、支出

条件区域$B:$B绝对引用这样下拉和右拉都不变，条件$G2锁定字母下拉数字会改变，右拉字母不变，求和区域C:C不锁定右拉会自动变成D:D。

=SUMIF($B:$B,$G2,C:C)

语法：

=SUMIF(条件区域,条件,求和区域)

9.根据日期统计每月收入、支出

MONTH提取月份后才能统计，因为SUMIF不支持嵌套其他函数，这里只能退而求其次，用SUMPRODUCT。

=SUMPRODUCT((MONTH($A$2:$A$27)=$G8)*C$2:C$27)

语法：

=SUMPRODUCT((条件区域=条件)*求和区域)

这个函数不支持引用整列，因为整列会包含标题，标题*任何值都是错误值。

数据源每天都会更新，为了能动态统计，可以把区域改大点，比如A2:A9999，但不能引用整列。

=SUMPRODUCT((MONTH($A$2:$A$9999)=$G8)*C$2:C$9999)

公式不能只看一遍，而要多动手练习才能记得牢固。

请把「Excel不加班」推荐给你的朋友