想要Excel水平超过90%的同事，只需学会这20个公式就够了！（内含836个表格模板）
想要Excel水平超过90%的同事，只需学会这20个公式就够了！（内含836个表格模板）
Excelbujiaban
30万+读者支持的《Excel效率手册 早做完，不加班》系列丛书作者，坚持每天发布原创Excel教程，伴你学习Excel每一天！同时进行Excel疑难解答，让你早做完不加班！
点击上方蓝字 「Excel不加班」 关注 ， 看下一篇
这次好人做到底，将所有文章使用的表格全分享出来，合计836个。个别表格可能没有，毕竟这么多年，有些不小心删掉。如果能好好结合文章学习，超过90%的同事，那简直是小菜一碟。
怎么领取呢？
很简单，文末给卢子点个赞，顺便留个言就行，代表你有诚意。然后私聊卢子 微 信 chenxilu2019 领取。
下面卢子分享一些经常会用到的公式。
1. 对金额进行合计
=SUM(F2:F17)
SUM函数这个估计大家都很熟悉，就是对区域的数据进行求和。需要注意的是，如果区域存在文本，将自动被忽略。如现在将求和区域变成F1:F17，包含了标题金额，直接用SUM求和不会有任何影响。
=SUM(F1:F17)
学会了SUM函数，就间接学会了MAX（最大值）、MIN（最小值）、AVERAGE（平均值）、COUNT（数字个数）。
=MAX(F2:F17)
=MIN(F2:F17)
=AVERAGE(F2:F17)
=COUNT(F2:F17)
2. 对每个商品的金额进行合计
=SUMIF(B:B,H2,F:F)
SUMIF→SUM+IF，IF就是如果的意思，也就是如果满足条件就对区域中的数据进行求和。
语法：
=SUMIF(条件区域,条件,求和区域)
同理，如果对商品进行计数，就是COUNTIF函数。
=COUNTIF(B:B,H2)
商品的平均金额，就AVERAGEIF函数。
=AVERAGEIF(B:B,H2,F:F)
学习函数就是这样，将同一系列的函数放在一起，这样就能批量记住。
另外，可以再自学SUMIFS、COUNTIFS函数。
=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,条件区域n,条件n) =COUNTIFS (条件区域1,条件1,条件区域2,条件2,条件区域n,条件n)
3. 对每个商品每个月的金额进行合计
=SUMPRODUCT(($B$2:$B$17=$H2)*(TEXT($C$2:$C$17,"m月")=I$1)*$F$2:$F$17)
语法：
=SUMPRODUCT((条件区域1=条件1)*(条件区域2=条件2)*求和区域)
每个参数都可以嵌套其他函数，TEXT就是将销售日期转换成月份。
如果数据有跨年的，要统计每一年的，就将TEXT第2参数改成e年，e代表4位数的年份，等同于yyyy。
=SUMPRODUCT(($B$2:$B$17=$H8)*(TEXT($C$2:$C$17,"e年")=I$7)*$F$2:$F$17)
求和、计数大概就这几个常用函数。接下来就是查找引用对应值。
4. 查找每个商品的单价
=VLOOKUP(B2,H:I,2,0)
语法：
=VLOOKUP(查找值,查找区域,返回区域第几列,0)
当然，这里用LOOKUP函数也行。
=LOOKUP(1,0/($H$2:$H$4=B2),$I$2:$I$4)
语法：
=LOOKUP(1,0/(查找区域=查找值),返回区域)
VLOOKUP家族其实有三兄弟，VLOOKUP是老大，LOOKUP是老二，老三是被人遗忘了的HLOOKUP。我们都喜欢高高瘦瘦的美女，而不喜欢胖胖矮矮的丑女。做表格其实也一样，因为审美观的原因，导致了很少有几行无数列的表格，也就使HLOOKUP英雄无用武之地。VLOOKUP函数是垂直查询，HLOOKUP函数是水平查询，除了方向不同，其他用法一样。
如现在将价格对应表进行转置。
=HLOOKUP(B2,$I$1:$K$2,2,0)
查找就VLOOKUP家族用得最多，针对特殊的数据源，还有用到其他函数。
4. 查找每个商品的单价（不同时间段价格不一样）
=VLOOKUP(B2,H:K,MATCH(C2,$H$1:$K$1),0)
语法：
=MATCH(查找值,某一行或者某一列,查找模式)
查找模式为0就是精确查找，省略不写就是按区间查找，就是查找小于或者等于它的最大值。
比如2021/10/28，返回2，也就是查找到2021/10/26的对应位置。
如果写上查找模式为0，没有一样的日期就返回错误值。
MATCH单独没啥作用，都是跟其他函数配合，除了配合VLOOKUP，还有INDEX。
=INDEX(H:K,MATCH(B2,H:H,0),MATCH(C2,$H$1:$K$1))
语法：
=INDEX(区域,第几行,第几列)
第几行，第几列经常用MATCH判断，因此语法变成：
=INDEX(区域,MATCH,MATCH)
顺便说下OFFSET，语法跟INDEX有点类似。
=OFFSET(起点,向下几行,向右几列)
套用进去就是：
=OFFSET($H$1,MATCH(B2,H:H,0)-1,MATCH(C2,$H$1:$K$1)-1)
5. 每月工资的综合案例
上面的都是理论用法，接下来看VIP学员的真实案例。将上面2年的数据整理成下面的效果，并制作图表。
2021年的数据，在第2列、第4列……也就是偶数列。
下拉生成数字可以用ROW，右拉生成数字可以用COLUMN，现在是下拉，用2*ROW就可以得到偶数。
=2*ROW(A1)
ROW跟INDEX组合就得到了2021年的工资。
=INDEX($2:$2,2*ROW(A1))
2020年的工资在右边一列，也就是再加1就出来。
=INDEX($2:$2,2*ROW(A1)+1)
增长率正常用2021年÷2020年-1就行，不过在做图表的时候，2021年的工资是0，也就是还没发，用错误值NA()代替会好点。输入公式后，将单元格设置为百分比。
=IF(B7=0,NA(),B7/C7-1)
数据转换后，插入推荐的图表，直接选第一个就行了。
以上这些全会了，就已经比大多数人都厉害了。
恭喜这3位粉丝： 兔too、墨云轩、hu li ，获得书籍《 Excel函数跟卢子一起学 早做完，不加班 》，加卢子微信 chenxilu2019
报名后加卢子微信 chenxilu2019 ，发送报名截图邀请进群。
推荐： 7个好用到强烈推荐的Excel神奇函数，你值得拥有！
上篇： 总有人问我怎么才能学好Excel函数，现在统一回复
这几天整理一下电脑，将那些有用的资料分享给你。
其实，我不怕你超越我，长江后浪推前浪。只怕你原地踏步。。。
作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）
长按二维码，识别关注
请把「Excel不加班」推荐给你的朋友和同事
别忘了点赞支持卢子哦↓↓↓