Excel中12個常用函數組合助你輕鬆提高工作效率 | 推薦收藏
函數是 excel 中最重要的分析工具，但是單個函數的功能都是比較單一的，我們在解決實際問題時，往往需要多個函數組合使用，今天小編整理了 12 組常用Excel 函數組合，都放在這裡了，趕快拿去提升工作效率吧~
1、MIN+IF 組合
功能：計算指定條件下的最小值
例：要計算人力部的最低分數。
G3 單元格公式：
=MIN(IF(A2:A9=F3,D2:D9))
說明：先用IF函數判斷A列的部門是否等於F3指定的部門，如果條件成立，則返回D列對應的分數，否則返回邏輯值FALSE；
接下來再使用MIN函數計算出其中的最小值。MIN函數有一個特性，就是可以自動忽略邏輯值，所以只會對數值部分計算，最終得到指定部門的最低分數。
註：執行多項計算，在輸入公式時要按 Shift+ctrl+Enter 鍵
2、IF+AND 組合
功能：並列多條件判斷
例：在C列設置公式，如果A列值小於500且B列值為未到期，則返回」補款「，否則顯示為空。
C4 單元格公式：
=IF(AND(A4<500,B2="否"),"補款","")
註：兩個條件同時成立用 AND,任一個成立用 OR 函數。
3、INDEX+MATCH 組合
功能：根據條件查詢
例：根據月份和費用項目，查找金額。
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(區域,行數,列數）
4、VLOOKUP+MATCH 組合
功能：用於不確定列數的數據查詢
例：根據B13單元格的姓名，在數據表中查詢對應的項目。
C13 單元格公式：
=VLOOKUP(B13,A1:E9,MATCH(C12,1:1,),0)
5、IFERROR+VLOOKUP 組合
功能：當 VLOOKUP 查找不到時屏蔽查錯誤值
例：根據產品名稱在上表中查找單價，如果產品不存在則顯示為空白。
B9 單元格公式：
=IFERROR(VLOOKUP(A8,$A$1:$D$5,3,0),"")
6、TEXT+MID 組合
功能：用於日期字元串的提取和轉換
例：根據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"。
7、MID+FIND 組合
功能：根據條件截取字元串
例：在個人信里截取出年齡
B2 單元格公式：
=MID(A2,FIND(" ",A2)+1,9)
註：find 查找分隔符的位置，MID 負責截取字元
8、LEN+SUBSTITUTE 組合
功能：計算一個單元格內有幾個項目
例：計算每個部門的人數。
C2 單元格公式：
=(LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1)*(B2<>"")
註：加上一個判斷B2單元格是否為空格的判定，否則如果為空格就會返回錯誤的結果1
說明：先用LEN函數計算出 B 列單元格的字元長度，然後再用 SUBSTITUTE 函數將頓號全部替換掉之後，計算替換後的字元長度。
用字元長度減去替換後的字元長度，就是單元格內頓號的個數。
9、LEFT+LENB+LEN 組合
功能：分離漢字、數字和字母
例：
B5 單元格公式：
=LEFT(A2,LENB(A2)-LEN(A2))
註：帶B的函數是按位元組計數，而一個漢字佔2個位元組，數字和字母則佔1個。所以用 LENB(A2)-LEN(A2) 可以倒推出漢字的個數，然後用 left 或 mid 函數截取。
10、SUMPRODUCT+COUNTIF 組合
功能：計算不重複值個數
例：統計B列的客戶數量
D2 單元格公式：
=SUMPRODUCT(1/COUNTIF(B2:B19,B2:B19))
註：Countif 函數統計出每個客戶的出現次數，Sumprodcut 對 1/出現次數進行求和。每個客戶無論出現多少次，求和的結果都是1，求和後正好是不重複個數。
11、SUM+OFFSET+COUNT 組合
功能：最後N天求和
例：在D2單元格返回B列最近7天的銷量
D2 單元格公式：
=SUM(OFFSET(B1,COUNTA(B:B)-7,0,7,1))
註：Counta 負責統計 B 列非空值個數，offset 負責生成動態最後 N 天區域，SUM 負責求和
12、INDEX+SMALL+ROW 組合
功能：一對多查找
例：在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 輸入大括弧（一定要是自動生成的，不能手輸入大括弧）。
歡迎關注公眾號：數獵天下DataHunter
－數據分析展示就用 DataHunter－
手把手教你免費使用數據可視化軟體
手把手教你免費使用數據可視化軟體推薦閱讀：
TAG:MicrosoftExcel | Excel公式 | Excel函數 |