Excel函數公式：含金量超高的4組函數組合實用技巧，必須掌握

06-20

函數公式在xcel中是最普通的話題了，每個函數或公式都有其特定的作用，但是如果將兩個函數嵌套組合使用，將會產生1+1>2的神奇效果。

一、動態多列查詢：VLOOKUP+MATCH。

目的：查詢銷售員的相關信息並高亮度顯示。

方法：

1、選定目標單元格，輸入公式：=VLOOKUP($H$3,$B$3:$E$9,MATCH(I$2,$B$2:$E$2,0),0)。

2、選定數據源區域，【條件格式】-【新建規則】-【使用公式確定要設置格式的單元格】。

3、在【為符合公式的值設置格式】中輸入公式：=$b3=$h$3。

4、【格式】-【填充】-選取填充色（例如黃色），【確定】-【確定】。

解讀：

1、利用公式：=VLOOKUP($H$3,$B$3:$E$9,MATCH(I$2,$B$2:$E$2,0),0)查詢相關數據。利用MATCH(I$2,$B$2:$E$2,0)定位當前查詢的數據在範圍$B$3:$E$9中的相對列數。

2、利用公式：=$b3=$h$3高亮度顯示設置，如果B列的值和H3單元格的值相等，則對整列進行填充「黃色」。

二、動態多列查詢：INDEX+MATCH。

目的：查詢銷售員的相關信息並高亮度顯示。

方法：

1、選定目標單元格，輸入公式：=INDEX($B$2:$E$9,MATCH($H$3,$B$2:$B$9,0),MATCH(I$2,$B$2:$E$2,0))。

2、選定數據源區域，【條件格式】-【新建規則】-【使用公式確定要設置格式的單元格】。

3、在【為符合公式的值設置格式】中輸入公式：=$b3=$h$3。

4、【格式】-【填充】-選取填充色（例如黃色），【確定】-【確定】。

解讀：

1、INDEX函數的作用是篩選出範圍（$B$2:$E$9）中的行（MATCH($H$3,$B$2:$B$9,0)）和列（MATCH(I$2,$B$2:$E$2,0)）交匯處的值並予以顯示。利用MATCH函數對當前值所在的行和列進行定位。

2、利用公式：=$b3=$h$3高亮度顯示設置，如果B列的值和H3單元格的值相等，則對整列進行填充「黃色」。

三、單條件匯總查詢：SUM+SUMIF。

目的：按「地區」統計銷售總額。

方法：

1、在目標單元格中輸入公式：=SUM(SUMIF(E3:E9,H3,C3:C9))。

2、Ctrl+Shift+Enter填充。

3、選定數據源區域，【條件格式】-【新建規則】-【使用公式確定要設置格式的單元格】。

4、在【為符合公式的值設置格式】中輸入公式：=$e3=$h$3。

5、【格式】-【填充】-選取填充色（例如黃色），【確定】-【確定】。

解讀：

1、公式SUMIF(E3:E9,H3,C3:C9)求出符合條件的一條記錄，如果有多條符合條件的記錄，必需將其進行多次計算，故用Ctrl+Shift+Enter將多條符合條件的記錄存儲在數組中，然後利用SUM函數對齊求和。從而實現按「地區」統計的目的。

2、利用公式：=$e3=$h$3高亮度顯示設置，如果E列的值和H3單元格的值相等，則對整列進行填充「黃色」。

四、一對多查詢：INDEX+SMALL+IF。

目的：篩選出產品的銷售顧客信息。

方法：

1、選定目標單元格。

2、在單元格中輸入公式：=INDEX(C:C,SMALL(IF(B$4:B$13=H$4,ROW($4:$13),4^8),ROW(B1)))&""。

3、Ctrl+Shift+Enter填充。

4、拖動填充柄填充。

解讀：

1、SMALL公式的主要作用是返回區域中指定序列上的最小值。公式：SMALL(IF(B$4:B$13=H$4,ROW($4:$13),4^8),ROW(B1))中，首先用IF函數判斷H4單元格的產品是否與銷售流水中產品一致，如果一致，則返回產品所在的行數（ROW($4:$13），否則返回4^8（4的8次方，當前Excel中的最大行數）。

2、然後用SMALL函數將等於H$4單元格的值的行號記錄在數組中，形成了INDEX函數的第二個參數。然後將其返回。