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

話不多說，直接講了

IF類函數(shù)

1.1 IF函數(shù)

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

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

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

1.1.1 單條件判斷

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

E3單元格輸入以下公式：

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

1.1.2 多重條件判斷

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

F18單元格輸入以下公式：

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

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

1.2 SUMIF和SUMIFS函數(shù)

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

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

1.2.1 SUM函數(shù)

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

=SUM(F42:F54)

1.2.2 SUMIF函數(shù)

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

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

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

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

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

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

1.2.3 SUMIFS函數(shù)

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

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

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

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

公式為：

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

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

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

1.3 IFERROR函數(shù)

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

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

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

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

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

VLOOKUP函數(shù)

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

函數(shù)的語法為：

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

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

2.1 常規(guī)查詢

如圖，需要從74:83的單元格區(qū)域中，根據H74單元格的編號查詢對應的職務：

公式為：

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

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

2.2 帶通配符查詢

如下圖，假如我們需要通過記錄的部分SIM卡號，來獲取到對應的手機號，這時直接使用

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

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

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

2.3 近似查詢

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

如下圖，我們要對學生的成績做評級。

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

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

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

2.4 逆向查詢

我們使用VLOOKUP進行查詢的時候，通常是從左到右進行查詢，但是當查詢的結果在查詢條件的左邊時，單純的使用VLOOKUP函數(shù)是沒有辦法完成的。可以借助CHOOSE函數(shù)組合使用來解決。

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

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

SUMPRODUCT函數(shù)

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

方法1：

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

方法2：

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

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

3.1 常規(guī)乘積求和

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

=SUMPRODUCT(C5:C8,D5:D8)

它的運算過程是：C5:C8和D5:D8兩個區(qū)域數(shù)組內的元素對應相乘。

3.2 多條件計數(shù)

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

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

3.3 多條件求和

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

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

3.4 二維區(qū)域求和

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

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

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

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

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

MATCH函數(shù)組合

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

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

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

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

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

=MATCH(100,148:151,1)

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

=MATCH(80,159:162,1)

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

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

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

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

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

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

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

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

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

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

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

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

如下圖，首先 第一個MATCH()用來確定編號的位置，第二個MATCH()用來確定產品類型的位置。

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

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

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

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

可實現(xiàn)動態(tài)查詢

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

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

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

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

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

=INDEX(11:15,3,4)

4.3.1 正向查詢

如下圖所示，根據D24單元格中的員工編號，在B列查詢對應的職務。

公式為：

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

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

4.3.2 逆向查詢

如下圖所示，根據D39單元格中的職務，在A列查詢對應的員工編號。

公式為：

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

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

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

4.3.3 多條件查詢

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

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

公式為：

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

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

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

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

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

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