12個Excel常用函數，工作效率翻3倍 「建議收藏」

函數是 excel 中最重要的分析工具，但是單個函數的功能都是比較單一的，我們在解決實際問題時，往往需要多個函數組合使用，今天小編整理了12組常用Excel函數組合，都放在這里了，趕快拿去提升工作效率吧~

功能：計算指定條件下的最小值

例：要計算人力部的最低分數。

G3 單元格公式：

=MIN(IF(A2:A9=F3,D2:D9))

說明：先用IF函數判斷A列的部門是否等于F3指定的部門，如果條件成立，則返回D列對應的分數，否則返回邏輯值FALSE；

接下來再使用MIN函數計算出其中的最小值。MIN函數有一個特性，就是可以自動忽略邏輯值，所以只會對數值部分計算，最終得到指定部門的最低分數。

注：執行多項計算，在輸入公式時要按 Shift+ctrl+Enter 鍵

功能：并列多條件判斷

例：在C列設置公式，如果A列值小于500且B列值為未到期，則返回”補款“，否則顯示為空。

C4 單元格公式：

=IF(AND(A4<500,B2='否'),'補款','')

注：兩個條件同時成立用 AND,任一個成立用 OR 函數。

功能：根據條件查詢

例：根據月份和費用項目，查找金額。

C10 單元格公式：

=INDEX(B2:G6,MATCH(B10,$A$2:$A$6,0),MATCH(A10,$B$1:$G$1,0))

說明：先用MATCH函數查找3月在第一行中的位置

=MATCH(B10,$A$2:$A$6,0)

再用MATCH函數查找費用項目在A列的位置

=MATCH(A10,$B$1:$G$1,0)

最后用INDEX根據行數和列數提取數值

=INDEX(區域,行數,列數）

功能：用于不確定列數的數據查詢

例：根據B13單元格的姓名，在數據表中查詢對應的項目。

C13 單元格公式：

=VLOOKUP(B13,A1:E9,MATCH(C12,1:1,),0)

功能：當 VLOOKUP 查找不到時屏蔽查錯誤值

例：根據產品名稱在上表中查找單價，如果產品不存在則顯示為空白。

B9 單元格公式：

=IFERROR(VLOOKUP(A8,$A$1:$D$5,3,0),'')

功能：用于日期字符串的提取和轉換

例：根據B列身份證號碼提取出生年月。

C3 單元格公式為：

=TEXT(MID(B2,7,8),'0-00-00')

說明：MID 函數用于從字符串的指定位置開始，提取特定數目的字符串。

MID(B2,7,8)就是從 B2 單元格的第 7 位開始，提取 8 位數字，結果為：19881109。

再使用 TEXT 函數，將這個字符串變成'0-00-00'的樣式，結果為'1988-11-09'。

功能：根據條件截取字符串

例：在個人信里截取出年齡

B2 單元格公式：

=MID(A2,FIND(' ',A2)+1,9)

注：find 查找分隔符的位置，MID 負責截取字符

功能：計算一個單元格內有幾個項目

例：計算每個部門的人數。

C2 單元格公式：

=(LEN(B2)-LEN(SUBSTITUTE(B2,'、',))+1)*(B2<>'')

注：加上一個判斷B2單元格是否為空格的判定，否則如果為空格就會返回錯誤的結果1

說明：先用LEN函數計算出 B 列單元格的字符長度，然后再用 SUBSTITUTE 函數將頓號全部替換掉之后，計算替換后的字符長度。

用字符長度減去替換后的字符長度，就是單元格內頓號的個數。

功能：分離漢字、數字和字母

例：

B5 單元格公式：

=LEFT(A2,LENB(A2)-LEN(A2))

注：帶B的函數是按字節計數，而一個漢字占2個字節，數字和字母則占1個。所以用 LENB(A2)-LEN(A2) 可以倒推出漢字的個數，然后用 left 或 mid 函數截取。

功能：計算不重復值個數

例：統計B列的客戶數量

D2 單元格公式：

=SUMPRODUCT(1/COUNTIF(B2:B19,B2:B19))

注：Countif 函數統計出每個客戶的出現次數，Sumprodcut 對 1/出現次數進行求和。每個客戶無論出現多少次，求和的結果都是1，求和后正好是不重復個數。

功能：最后N天求和

例：在D2單元格返回B列最近7天的銷量

D2 單元格公式：

=SUM(OFFSET(B1,COUNTA(B:B)-7,0,7,1))

注：Counta 負責統計 B 列非空值個數，offset 負責生成動態最后 N 天區域，SUM 負責求和

功能：一對多查找

例：在F列查找“張麗”的個人消費記錄 

F2 單元格公式：

{=INDEX(C:C,SMALL(IF(B$2:B$10=F$1,ROW($2:$10)),ROW(A1)))}

說明：

• IF(B$2:B$10=F$1,ROW($2:$10))：如果B列的姓名和F1的姓名相同，就返回它的行號。不相同的返回FALSE

•  Row(a1)：是返回A1的行號1，如果向下復制會變為 Row(a2)，返回2，其實用它的目的是當公式向下復制時可以生成序號：1，2，3...然后取符合條件的第1個行號，第2個行號...

• SMALL(): 從符合條件的行號中從小到大，逐個提取符合條件的行

• INDEX() :根據取得的行號從C列提取值

• { }：數組公式（含有逐一運算的公式）需要按 ctrl+shift+enter 輸入大括號（一定要是自動生成的，不能手輸入大括號）。

End.