想要Excel水平超過90%的同事，只需學(xué)會這20個(gè)公式就夠了?。▋?nèi)含836個(gè)表格模板）
想要Excel水平超過90%的同事，只需學(xué)會這20個(gè)公式就夠了?。▋?nèi)含836個(gè)表格模板）
Excelbujiaban
30萬+讀者支持的《Excel效率手冊 早做完，不加班》系列叢書作者，堅(jiān)持每天發(fā)布原創(chuàng)Excel教程，伴你學(xué)習(xí)Excel每一天！同時(shí)進(jìn)行Excel疑難解答，讓你早做完不加班！
點(diǎn)擊上方藍(lán)字 「Excel不加班」 關(guān)注 ， 看下一篇
這次好人做到底，將所有文章使用的表格全分享出來，合計(jì)836個(gè)。個(gè)別表格可能沒有，畢竟這么多年，有些不小心刪掉。如果能好好結(jié)合文章學(xué)習(xí)，超過90%的同事，那簡直是小菜一碟。
怎么領(lǐng)取呢？
很簡單，文末給盧子點(diǎn)個(gè)贊，順便留個(gè)言就行，代表你有誠意。然后私聊盧子 微 信 chenxilu2019 領(lǐng)取。
下面盧子分享一些經(jīng)常會用到的公式。
1. 對金額進(jìn)行合計(jì)
=SUM(F2:F17)
SUM函數(shù)這個(gè)估計(jì)大家都很熟悉，就是對區(qū)域的數(shù)據(jù)進(jìn)行求和。需要注意的是，如果區(qū)域存在文本，將自動被忽略。如現(xiàn)在將求和區(qū)域變成F1:F17，包含了標(biāo)題金額，直接用SUM求和不會有任何影響。
=SUM(F1:F17)
學(xué)會了SUM函數(shù)，就間接學(xué)會了MAX（最大值）、MIN（最小值）、AVERAGE（平均值）、COUNT（數(shù)字個(gè)數(shù)）。
=MAX(F2:F17)
=MIN(F2:F17)
=AVERAGE(F2:F17)
=COUNT(F2:F17)
2. 對每個(gè)商品的金額進(jìn)行合計(jì)
=SUMIF(B:B,H2,F:F)
SUMIF→SUM+IF，IF就是如果的意思，也就是如果滿足條件就對區(qū)域中的數(shù)據(jù)進(jìn)行求和。
語法：
=SUMIF(條件區(qū)域,條件,求和區(qū)域)
同理，如果對商品進(jìn)行計(jì)數(shù)，就是COUNTIF函數(shù)。
=COUNTIF(B:B,H2)
商品的平均金額，就AVERAGEIF函數(shù)。
=AVERAGEIF(B:B,H2,F:F)
學(xué)習(xí)函數(shù)就是這樣，將同一系列的函數(shù)放在一起，這樣就能批量記住。
另外，可以再自學(xué)SUMIFS、COUNTIFS函數(shù)。
=SUMIFS(求和區(qū)域,條件區(qū)域1,條件1,條件區(qū)域2,條件2,條件區(qū)域n,條件n) =COUNTIFS (條件區(qū)域1,條件1,條件區(qū)域2,條件2,條件區(qū)域n,條件n)
3. 對每個(gè)商品每個(gè)月的金額進(jìn)行合計(jì)
=SUMPRODUCT(($B$2:$B$17=$H2)*(TEXT($C$2:$C$17,"m月")=I$1)*$F$2:$F$17)
語法：
=SUMPRODUCT((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)*求和區(qū)域)
每個(gè)參數(shù)都可以嵌套其他函數(shù)，TEXT就是將銷售日期轉(zhuǎn)換成月份。
如果數(shù)據(jù)有跨年的，要統(tǒng)計(jì)每一年的，就將TEXT第2參數(shù)改成e年，e代表4位數(shù)的年份，等同于yyyy。
=SUMPRODUCT(($B$2:$B$17=$H8)*(TEXT($C$2:$C$17,"e年")=I$7)*$F$2:$F$17)
求和、計(jì)數(shù)大概就這幾個(gè)常用函數(shù)。接下來就是查找引用對應(yīng)值。
4. 查找每個(gè)商品的單價(jià)
=VLOOKUP(B2,H:I,2,0)
語法：
=VLOOKUP(查找值,查找區(qū)域,返回區(qū)域第幾列,0)
當(dāng)然，這里用LOOKUP函數(shù)也行。
=LOOKUP(1,0/($H$2:$H$4=B2),$I$2:$I$4)
語法：
=LOOKUP(1,0/(查找區(qū)域=查找值),返回區(qū)域)
VLOOKUP家族其實(shí)有三兄弟，VLOOKUP是老大，LOOKUP是老二，老三是被人遺忘了的HLOOKUP。我們都喜歡高高瘦瘦的美女，而不喜歡胖胖矮矮的丑女。做表格其實(shí)也一樣，因?yàn)閷徝烙^的原因，導(dǎo)致了很少有幾行無數(shù)列的表格，也就使HLOOKUP英雄無用武之地。VLOOKUP函數(shù)是垂直查詢，HLOOKUP函數(shù)是水平查詢，除了方向不同，其他用法一樣。
如現(xiàn)在將價(jià)格對應(yīng)表進(jìn)行轉(zhuǎn)置。
=HLOOKUP(B2,$I$1:$K$2,2,0)
查找就VLOOKUP家族用得最多，針對特殊的數(shù)據(jù)源，還有用到其他函數(shù)。
4. 查找每個(gè)商品的單價(jià)（不同時(shí)間段價(jià)格不一樣）
=VLOOKUP(B2,H:K,MATCH(C2,$H$1:$K$1),0)
語法：
=MATCH(查找值,某一行或者某一列,查找模式)
查找模式為0就是精確查找，省略不寫就是按區(qū)間查找，就是查找小于或者等于它的最大值。
比如2021/10/28，返回2，也就是查找到2021/10/26的對應(yīng)位置。
如果寫上查找模式為0，沒有一樣的日期就返回錯(cuò)誤值。
MATCH單獨(dú)沒啥作用，都是跟其他函數(shù)配合，除了配合VLOOKUP，還有INDEX。
=INDEX(H:K,MATCH(B2,H:H,0),MATCH(C2,$H$1:$K$1))
語法：
=INDEX(區(qū)域,第幾行,第幾列)
第幾行，第幾列經(jīng)常用MATCH判斷，因此語法變成：
=INDEX(區(qū)域,MATCH,MATCH)
順便說下OFFSET，語法跟INDEX有點(diǎn)類似。
=OFFSET(起點(diǎn),向下幾行,向右?guī)琢?
套用進(jìn)去就是：
=OFFSET($H$1,MATCH(B2,H:H,0)-1,MATCH(C2,$H$1:$K$1)-1)
5. 每月工資的綜合案例
上面的都是理論用法，接下來看VIP學(xué)員的真實(shí)案例。將上面2年的數(shù)據(jù)整理成下面的效果，并制作圖表。
2021年的數(shù)據(jù)，在第2列、第4列……也就是偶數(shù)列。
下拉生成數(shù)字可以用ROW，右拉生成數(shù)字可以用COLUMN，現(xiàn)在是下拉，用2*ROW就可以得到偶數(shù)。
=2*ROW(A1)
ROW跟INDEX組合就得到了2021年的工資。
=INDEX($2:$2,2*ROW(A1))
2020年的工資在右邊一列，也就是再加1就出來。
=INDEX($2:$2,2*ROW(A1)+1)
增長率正常用2021年÷2020年-1就行，不過在做圖表的時(shí)候，2021年的工資是0，也就是還沒發(fā)，用錯(cuò)誤值NA()代替會好點(diǎn)。輸入公式后，將單元格設(shè)置為百分比。
=IF(B7=0,NA(),B7/C7-1)
數(shù)據(jù)轉(zhuǎn)換后，插入推薦的圖表，直接選第一個(gè)就行了。
以上這些全會了，就已經(jīng)比大多數(shù)人都厲害了。
恭喜這3位粉絲： 兔too、墨云軒、hu li ，獲得書籍《 Excel函數(shù)跟盧子一起學(xué) 早做完，不加班 》，加盧子微信 chenxilu2019
報(bào)名后加盧子微信 chenxilu2019 ，發(fā)送報(bào)名截圖邀請進(jìn)群。
推薦： 7個(gè)好用到強(qiáng)烈推薦的Excel神奇函數(shù)，你值得擁有！
上篇： 總有人問我怎么才能學(xué)好Excel函數(shù)，現(xiàn)在統(tǒng)一回復(fù)
這幾天整理一下電腦，將那些有用的資料分享給你。
其實(shí)，我不怕你超越我，長江后浪推前浪。只怕你原地踏步。。。
作者：盧子，清華暢銷書作者，《Excel效率手冊 早做完，不加班》系列叢書創(chuàng)始人，個(gè)人公眾號：Excel不加班（ID：Excelbujiaban）
長按二維碼，識別關(guān)注
請把「Excel不加班」推薦給你的朋友和同事
別忘了點(diǎn)贊支持盧子哦↓↓↓