Excel10年老用戶了，做數(shù)據(jù)分析的前幾年全靠Excel打天下，給大家復(fù)盤一下Excel的高頻使用函數(shù)，供初入職場(chǎng)或者想要提高工作效率的朋友做參考，如果你能夠吸收完這一篇，可以抵上一年以上的工作經(jīng)驗(yàn)了。

話不多說，直接講了

IF類函數(shù)

1.1 IF函數(shù)

IF函數(shù)是日常工作中使用Excel時(shí)最常用的函數(shù)之一，IF函數(shù)承載著'如果......那么......否則......'這組關(guān)聯(lián)詞的作用。IF函數(shù)共有3個(gè)參數(shù)，每個(gè)參數(shù)扮演不同的角色，只有參數(shù)設(shè)置正確，Excel才會(huì)明白你的意圖。

=IF（判斷條件，條件ture返回值，條件false返回值）

函數(shù)中有3個(gè)參數(shù)，均寫在括號(hào)中，參數(shù)間用逗號(hào)分割。第1個(gè)參數(shù)為判斷條件，當(dāng)返回TRUE時(shí)，則返回值1，否則返回值2

1.1.1 單條件判斷

如下圖所示，要根據(jù)D列的學(xué)生分?jǐn)?shù)判斷該學(xué)生某學(xué)科的分?jǐn)?shù)是否及格。

E3單元格輸入以下公式：

=IF（D3<60,'不及格','及格'）

1.1.2 多重條件判斷

如下圖所示，如果我們的條件為'物流'部門的'車輛管理員'才可能領(lǐng)取交通補(bǔ)貼，那么該如何篩選出符合條件的員工呢？這里就需要用到if的多重條件判斷。

F18單元格輸入以下公式：

=IF(D18='物流',IF(E18='車輛管理員','有','無'),'無')

以上函數(shù)還實(shí)現(xiàn)了IF多層嵌套的邏輯。

1.2 SUMIF和SUMIFS函數(shù)

SUM是最常用的求和函數(shù)，當(dāng)需要對(duì)報(bào)表范圍中符合指定條件的值求和時(shí)，需要用到SUMIF和SUMIFS，它們兩者的區(qū)別是：

• SUM是直接求和，沒有任何條件
• SUMIF是單條件求和，相當(dāng)于條件篩選一次后求和
• SUMIFS是多條件求和，相當(dāng)于條件篩選多次后求和

1.2.1 SUM函數(shù)

如下圖所示，是某單位食堂的采購記錄表，使用以下公式，即可計(jì)算所有采購物品的總數(shù)量。

=SUM(F42:F54)

1.2.2 SUMIF函數(shù)

如果要按指定條件求和，那就要請(qǐng)出SUMIF函數(shù)了。

這個(gè)函數(shù)的用法是：

=SUMIF(條件區(qū)域，指定的條件，求和區(qū)域)

如下圖所示，要計(jì)算職工食堂的物資采購總數(shù)量，公式為：

=SUMIF(42:54,I42,42:54)

公式的意思是，如果D39:D51單元格區(qū)域中等于I39指定的部門'職工食堂'，就對(duì)F39:F51單元格區(qū)域?qū)τ诘臄?shù)值進(jìn)行求和。

1.2.3 SUMIFS函數(shù)

SUMIFS函數(shù)的作用是多條件求和，這個(gè)函數(shù)的用法是：

=SUMIFS(求和區(qū)域,條件區(qū)域1，指定條件1，條件區(qū)域2，指定的條件2,......)

第一個(gè)參數(shù)指定的是求和區(qū)域，后面是一一對(duì)應(yīng)的條件區(qū)域和指定條件，多個(gè)條件之間是同時(shí)符合的意思。

如下圖所示，要計(jì)算部門是職工食堂，單價(jià)在1元以下的物資采購總量。

公式為：

=SUMIFS(61:73,61:73,61,61:73,61)

公式的意思是，如果D39:D51單元格區(qū)域中等于I39指定的部門'職工食堂'，并且G39:G51單元格區(qū)域中等于指定的條件'<1'，就對(duì)F39:F51單元格區(qū)域中對(duì)應(yīng)的數(shù)值求和。

同樣的，類似于SUMIF和SUMIFS函數(shù)，AVERGE/AVERGEIF/AVERAGEIFS函數(shù)是用來求算術(shù)平均值函數(shù)和有條件的求平均值函數(shù)。而COUT/COUNTIF/COUNTIFS函數(shù)是用來統(tǒng)計(jì)單元格區(qū)域內(nèi)的數(shù)量和有條件的統(tǒng)計(jì)單元格數(shù)量。

1.3 IFERROR函數(shù)

函數(shù)公式為：IFERROR(value, value_if_error)，表示判斷value的正確性，如果value正確則返回正確結(jié)果，否則返回value_if_error。作用是用來將錯(cuò)誤值修改為特定值，常見value的錯(cuò)誤格式有#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL等。

通常我們使用時(shí)，都是在IFERROR中嵌套了其他函數(shù)，例如：

=IFERROR(VLOOKUP(......),0)

上面的函數(shù)意思是說，當(dāng)VLOOKUP()出現(xiàn)錯(cuò)誤值時(shí)，單元格顯示為0。

如下兩幅圖，上圖在單獨(dú)使用VLOOKUP函數(shù)時(shí)，出現(xiàn)錯(cuò)誤值#N/A，假如人工進(jìn)行二次去刪除錯(cuò)誤值，數(shù)據(jù)量較大時(shí)會(huì)影響工作效率，此時(shí)配套IFERROR函數(shù)，可以事半功倍，如第二幅圖。

VLOOKUP函數(shù)

VLOOKUP函數(shù)是Excel中的一個(gè)縱向查找函數(shù)，它與LOOKUP函數(shù)和HLOOKUP函數(shù)屬于一類函數(shù)，在工作中都有廣泛應(yīng)用。比如，當(dāng)有多張表時(shí)，如何將一個(gè)excel表格的數(shù)據(jù)匹配到另外一個(gè)表中？這時(shí)候就需要使用VLOOKUP函數(shù)。

函數(shù)的語法為：

=VLOOKUP(要找誰，在哪兒找，返回第幾列的內(nèi)容，精確找還是近似找)

• 第一個(gè)參數(shù)是要查詢的值。
• 第二個(gè)參數(shù)是需要查詢的單元格區(qū)域，這個(gè)區(qū)域中的首列必須是要包含查詢值，否則公式將會(huì)返回錯(cuò)誤值。如果查詢區(qū)域中包含多個(gè)符合條件的查詢，VLOOKUP函數(shù)只能返回第一個(gè)查找到的結(jié)果。
• 第三個(gè)參數(shù)用戶指定返回查詢區(qū)域中的第幾列的值。
• 第四個(gè)參數(shù)是決定函數(shù)的查找方式，如果為0，則是精確匹配方式。如果為1，則是近似匹配方式。

2.1 常規(guī)查詢

如圖，需要從74:83的單元格區(qū)域中，根據(jù)H74單元格的編號(hào)查詢對(duì)應(yīng)的職務(wù)：

公式為：

=VLOOKUP(H96,96:105,3,0)

提示：VLOOKUP函數(shù)第三個(gè)參數(shù)中的列號(hào)，不能理解為工作表中的實(shí)際的列號(hào)，而是指定要返回查詢區(qū)域中的第幾列的值。

2.2 帶通配符查詢

如下圖，假如我們需要通過記錄的部分SIM卡號(hào)，來獲取到對(duì)應(yīng)的手機(jī)號(hào)，這時(shí)直接使用

=VLOOKUP(F111,111:114,2,0)

是無法正常獲取的，此時(shí)就需要利用通配符來進(jìn)行補(bǔ)充F89，并用'&'符號(hào)連接。第一個(gè)'*'補(bǔ)充的是F89前方數(shù)據(jù)，后邊'*'補(bǔ)充的是SIM卡號(hào)后邊的數(shù)據(jù)。

=VLOOKUP('*'&F111&'*',111:114,2,0)

2.3 近似查詢

在實(shí)際應(yīng)用中，我們往往用到的是FALSE精確匹配，無須顧慮表格是否為升序排列（Truth近似匹配容易受此影響），萬一沒有查詢到目標(biāo)，也能迅速查找原因。那參數(shù)TRUE近似匹配有什么用武之地？

如下圖，我們要對(duì)學(xué)生的成績(jī)做評(píng)級(jí)。

0-60分，為不合格；60-80分，為合格；80-90分，為良好；90分以上，為優(yōu)秀。

=VLOOKUP(E120,121:124,2,1)

提示：VLOOKUP函數(shù)第四參數(shù)為TRUE時(shí)，在近似匹配模式下返回查詢之的精確匹配值或者近似匹配值。如果找不到精確匹配值，則返回小于查詢值的最大值。使用近似匹配時(shí)，查詢區(qū)域的首列必須按升序排序，否則無法得到正確的結(jié)果。

2.4 逆向查詢

我們使用VLOOKUP進(jìn)行查詢的時(shí)候，通常是從左到右進(jìn)行查詢，但是當(dāng)查詢的結(jié)果在查詢條件的左邊時(shí)，單純的使用VLOOKUP函數(shù)是沒有辦法完成的?？梢越柚鶦HOOSE函數(shù)組合使用來解決。

如下圖，假如想查詢部門為'銷售'的員工編號(hào)。

=VLOOKUP(H132,CHOOSE({1,2},E132:E141,D132:D141),2,0)

SUMPRODUCT函數(shù)

SUMPRODUCT函數(shù)是用于在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。

方法1：

=SUMPRODUCT(數(shù)組1：數(shù)組2：數(shù)組3)

方法2：

=SUMPRODUCT(數(shù)組1*數(shù)組2)

兩種方法區(qū)別在于，SUMPRODUCT函數(shù)的兩個(gè)參數(shù)之間的連接符號(hào)不同，方法1用逗號(hào)連接，方法2用乘號(hào)連接。假如兩個(gè)數(shù)組全部是數(shù)值，兩種方法返回的結(jié)果是一致的，當(dāng)有包含文本數(shù)據(jù)時(shí)，第一種方法可以返回正確結(jié)果，第二種方法會(huì)返回錯(cuò)誤值#VALUE。

3.1 常規(guī)乘積求和

如下圖，這便是一個(gè)簡(jiǎn)單的SUMPRODUCT函數(shù)，公式如下：

=SUMPRODUCT(C5:C8,D5:D8)

它的運(yùn)算過程是：C5:C8和D5:D8兩個(gè)區(qū)域數(shù)組內(nèi)的元素對(duì)應(yīng)相乘。

3.2 多條件計(jì)數(shù)

25歲及以下女性的人數(shù)：

=SUMPRODUCT((16:22<=25)*(16:22='女'))

3.3 多條件求和

25歲及以下女性的業(yè)績(jī)：

=SUMPRODUCT((16:22<=25)*(16:22='女'),29:35)

3.4 二維區(qū)域求和

銷售1部的所有業(yè)績(jī)：

=SUMPRODUCT((42:46='銷售1部')*42:46)

3.5 二維區(qū)域多條件求和

銷售1部3月的業(yè)績(jī)：

=SUMPRODUCT((42:46='銷售1部')*(53:53='3月'),54:58)

MATCH函數(shù)組合

MACTH函數(shù)是EXCEL中使用較為廣泛的一個(gè)函數(shù)，MATCH函數(shù)的功能就是在指定區(qū)域內(nèi)搜索特定內(nèi)容，然后返回這個(gè)內(nèi)容在指定區(qū)域里面的相對(duì)位置。通俗的來講，就是返回指定值在數(shù)值的位置，如果在數(shù)組中沒有找到該值則返回#N/A。

=MATCH(查找的內(nèi)容，查找的區(qū)域，匹配類型)

其中匹配類型包含1，0，-1

• 1或省略，查找小于或等于指定內(nèi)容的最大值，而且指定區(qū)域必須按升序排序
• 0，查找等于指定內(nèi)容的第一個(gè)數(shù)值
• -1，查找大于或等于指定內(nèi)容的最小值，而且指定區(qū)域必須按降序排序

如下圖，想要在126:129區(qū)域內(nèi)找出一個(gè)等于'100'的數(shù)值為第幾個(gè)，按順序找到D128在126:129區(qū)域內(nèi)排第3，所以結(jié)果顯示3。

=MATCH(100,148:151,1)

如下圖，想要在137:140區(qū)域內(nèi)查找出小于或者等于80的數(shù)值，按順序找到E137和E138單元格的數(shù)值都小于'80'，選擇其中最大的數(shù)值，即E138的數(shù)值，區(qū)域內(nèi)排第2，所以結(jié)果顯示2。

=MATCH(80,159:162,1)

如下圖，想要在,148:151區(qū)域內(nèi)查找出大于或等于'90'的數(shù)值，按順序找到E148:E151單元格的數(shù)值都大于等于'90'，選擇其中最小的數(shù)值，即E149的數(shù)值，區(qū)域內(nèi)排第2，所以結(jié)果顯示4。

=MATCH(90,170:173,-1)

以上就是MACTH函數(shù)的一些基本用法，假如只掌握MATCH函數(shù)，可能并不會(huì)覺得它有什么威力，若把它跟其他函數(shù)結(jié)合起來使用，就可以解決很多問題。

4.1 MATCH與OFFSET函數(shù)組合

說組合函數(shù)之前，先嘮嘮OFFSET這個(gè)函數(shù)，剛接觸這個(gè)函數(shù)的時(shí)候，只知道這貨是根據(jù)參考值進(jìn)行偏移的函數(shù)，而且感覺沒多大用處。但凡EXCEL玩的比較深的同學(xué)，都知道這貨和其他函數(shù)匹配起來，特別好用。最常見的就是OFFSET+MATCH這個(gè)經(jīng)典組合了。

OFFSET的作用是以指定的引用為參照系，通過給定偏移量得到新的引用。

=OFFSET(指定參照單元格，偏移行，偏移列)

• 偏移行，正數(shù)為向下偏移，負(fù)數(shù)為向上偏移
• 偏移列，正數(shù)向右偏移，負(fù)數(shù)向左偏移

如下圖，這是OFFSET的一個(gè)最基本的使用方法，以A1為參考系，向下移動(dòng)3行，向右移動(dòng)3列，則得到D4的值。

說完OFFSET函數(shù)，就可以聊聊OFFSET+MATCH的組合。MATCH函數(shù)的用法作用為返回指定數(shù)值在指定數(shù)組區(qū)域中的位置。

=OFFSET(參照值，MATCH(),MATCH())

• 第一個(gè)MATCH(),用來確定偏移的行數(shù)
• 第二個(gè)MATCH(),用來確定偏移的列數(shù)

如下圖，首先 第一個(gè)MATCH()用來確定編號(hào)的位置，第二個(gè)MATCH()用來確定產(chǎn)品類型的位置。

=OFFSET(181,MATCH(J183,182:193,0),MATCH(K183,181:181,0))

4.2 MATCH與VLOOKUP函數(shù)組合

如何根據(jù)姓名和月份查找相應(yīng)的銷售量？利用VLOOKUP函數(shù)查找姓名，返回的列數(shù)為指定的月份所在的值；由于月份是變化的，所以想用一條公式就可以解決，就必須用其他公式確定月份的位置，這里就用到MATCH函數(shù)。

=VLOOKUP(I2,1:11,MATCH(J2,1:1,0),0)

可實(shí)現(xiàn)動(dòng)態(tài)查詢

4.3 MATCH與INDEX函數(shù)組合

先來說說INDEX函數(shù)的作用：

INDEX函數(shù)用于在一個(gè)區(qū)域中，根據(jù)指定的行和列號(hào)來返回內(nèi)容。

=INDEX(單元格區(qū)域，指定的行數(shù)，指定的列數(shù))

例如，以下公式，用于返回11:15單元格區(qū)域第3行和第4列交叉處的單元格值，即D13單元格。

=INDEX(11:15,3,4)

4.3.1 正向查詢

如下圖所示，根據(jù)D24單元格中的員工編號(hào)，在B列查詢對(duì)應(yīng)的職務(wù)。

公式為：

=INDEX(24:33,MATCH(D24,24:33,0))

先用MATCH函數(shù)，查找D24單元格的'M10004'在A列中所處的位置，得到結(jié)果為4，然后使用INDEX函數(shù)，在B列中返回第8個(gè)元素的內(nèi)容，結(jié)果就是'秘書'

4.3.2 逆向查詢

如下圖所示，根據(jù)D39單元格中的職務(wù)，在A列查詢對(duì)應(yīng)的員工編號(hào)。

公式為：

=INDEX(39:48,MATCH(D39,39:48,0))

先用MATCH函數(shù)，查找D39單元格的'秘書'在B列中所處的位置，得到結(jié)果為4，然后使用INDEX函數(shù)，在A列中返回第8個(gè)元素的內(nèi)容，結(jié)果就是'M10004'

兩個(gè)不同方向的查詢，使用的公式套路完全一樣，如果有興趣，你可以試試上下方向的查找公式怎么寫。

4.3.3 多條件查詢

除了常規(guī)的單條件查找，這兩個(gè)搭檔還可以完成多條件的查詢。

如下圖所示，需要根據(jù)F55（職務(wù)）和G55（年齡）信息，在A-C列單元格區(qū)域中，查找職務(wù)為'秘書'，年齡為'56'所對(duì)應(yīng)的員工編號(hào)。

公式為：

=INDEX(39:48,MATCH(F55&G55,55:64&55:64,0))

注意這里是一個(gè)數(shù)組公式，輸入完成后需要將光標(biāo)放到編輯框中，按照Ctrl+Shift不放，再按回車完成。

先使用連接符&，將F55和G55的職務(wù)和年齡合并成一個(gè)新的條件，再使用連接符將B列和C列的信息合并成一個(gè)新的查詢區(qū)域。然后使用MATCH函數(shù)，查詢出職務(wù)&年齡再查詢區(qū)域中所處的位置為8。

最后用INDEX函數(shù)，得到A列第8個(gè)元素的內(nèi)容，最終完成兩個(gè)條件的數(shù)據(jù)查詢。

以上，就是我這復(fù)盤的Excel函數(shù)，希望對(duì)大家有所幫助。

文源：一個(gè)數(shù)據(jù)人的自留地