【原】想要Excel水平超过90%的同事，只需学会这20个公式就够了！（内含836个表格模板）

这次好人做到底，将所有文章使用的表格全分享出来，合计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)

数据转换后，插入推荐的图表，直接选第一个就行了。

以上这些全会了，就已经比大多数人都厉害了。

推荐：7个好用到强烈推荐的Excel神奇函数，你值得拥有！

上篇：总有人问我怎么才能学好Excel函数，现在统一回复

这几天整理一下电脑，将那些有用的资料分享给你。

其实，我不怕你超越我，长江后浪推前浪。只怕你原地踏步。。。

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）