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

一、動(dòng)態(tài)多列查詢：VLOOKUP+MATCH。

目的：查詢銷售員的相關(guān)信息并高亮度顯示。

方法：

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

2、選定數(shù)據(jù)源區(qū)域，【條件格式】-【新建規(guī)則】-【使用公式確定要設(shè)置格式的單元格】。

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

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

解讀：

1、利用公式：=VLOOKUP($H$3,$B$3:$E$9,MATCH(I$2,$B$2:$E$2,0),0)查詢相關(guān)數(shù)據(jù)。利用MATCH(I$2,$B$2:$E$2,0)定位當(dāng)前查詢的數(shù)據(jù)在范圍$B$3:$E$9中的相對(duì)列數(shù)。

2、利用公式：=$b3=$h$3高亮度顯示設(shè)置，如果B列的值和H3單元格的值相等，則對(duì)整列進(jìn)行填充“黃色”。

二、動(dòng)態(tài)多列查詢：INDEX+MATCH。

目的：查詢銷售員的相關(guān)信息并高亮度顯示。

方法：

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

2、選定數(shù)據(jù)源區(qū)域，【條件格式】-【新建規(guī)則】-【使用公式確定要設(shè)置格式的單元格】。

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

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

解讀：

1、INDEX函數(shù)的作用是篩選出范圍（$B$2:$E$9）中的行（MATCH($H$3,$B$2:$B$9,0)）和列（MATCH(I$2,$B$2:$E$2,0)）交匯處的值并予以顯示。利用MATCH函數(shù)對(duì)當(dāng)前值所在的行和列進(jìn)行定位。

2、利用公式：=$b3=$h$3高亮度顯示設(shè)置，如果B列的值和H3單元格的值相等，則對(duì)整列進(jìn)行填充“黃色”。

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

目的：按“地區(qū)”統(tǒng)計(jì)銷售總額。

方法：

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

2、Ctrl+Shift+Enter填充。

3、選定數(shù)據(jù)源區(qū)域，【條件格式】-【新建規(guī)則】-【使用公式確定要設(shè)置格式的單元格】。

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

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

解讀：

1、公式SUMIF(E3:E9,H3,C3:C9)求出符合條件的一條記錄，如果有多條符合條件的記錄，必需將其進(jìn)行多次計(jì)算，故用Ctrl+Shift+Enter將多條符合條件的記錄存儲(chǔ)在數(shù)組中，然后利用SUM函數(shù)對(duì)齊求和。從而實(shí)現(xiàn)按“地區(qū)”統(tǒng)計(jì)的目的。

2、利用公式：=$e3=$h$3高亮度顯示設(shè)置，如果E列的值和H3單元格的值相等，則對(duì)整列進(jìn)行填充“黃色”。

四、一對(duì)多查詢：INDEX+SMALL+IF。

目的：篩選出產(chǎn)品的銷售顧客信息。

方法：

1、選定目標(biāo)單元格。

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

3、Ctrl+Shift+Enter填充。

4、拖動(dòng)填充柄填充。

解讀：

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

2、然后用SMALL函數(shù)將等于H$4單元格的值的行號(hào)記錄在數(shù)組中，形成了INDEX函數(shù)的第二個(gè)參數(shù)。然后將其返回。