編按：
說(shuō)到數(shù)據(jù)的多條件查詢，我們更多地想到的是Vlookup函數(shù)的應(yīng)用。可是，在名企任職的大神們不會(huì)固守這一觀點(diǎn)，他們會(huì)用更簡(jiǎn)單的思路，選擇當(dāng)下更實(shí)用的查詢公式，去輕松實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)提醒和動(dòng)態(tài)查詢！例如，在貨品庫(kù)存中心，查詢待補(bǔ)貨的店鋪和補(bǔ)貨數(shù)量時(shí)，大神們都會(huì)這樣做……

正文：

小伙伴們，大家好！今天繼續(xù)向大家介紹一個(gè)多維引用的實(shí)例，希望大家可以盡快掌握這個(gè)技巧。

下面是國(guó)際著名公司麥必德某大區(qū)的門店配送中心的庫(kù)存報(bào)表。

該圖顯示了每天門店的庫(kù)存情況（空白則表示該門店沒(méi)有此款產(chǎn)品）。物流人員會(huì)根據(jù)每天各門店的庫(kù)存來(lái)安排實(shí)物配送。安排配送的條件如下：

1.   以“包裝數(shù)量”為參照標(biāo)準(zhǔn)，將“配送中心”庫(kù)存的商品自動(dòng)分配到現(xiàn)庫(kù)存數(shù)小于“包裝數(shù)量”標(biāo)準(zhǔn)的門店。

2.   庫(kù)存小于等于1/2個(gè)“包裝數(shù)量”的標(biāo)準(zhǔn)時(shí)，配送2個(gè)的標(biāo)準(zhǔn)“包裝數(shù)量”的量；庫(kù)存大于1/2個(gè)“包裝數(shù)量”的標(biāo)準(zhǔn)，同時(shí)又小于1個(gè)“包裝數(shù)量”的標(biāo)準(zhǔn)時(shí)，配送1個(gè)標(biāo)準(zhǔn)“包裝數(shù)量”的量。

3.   顏色標(biāo)記需要配貨的門店。

第一步：標(biāo)記顏色

這個(gè)非常簡(jiǎn)單，用條件格式就可以完成。在條件格式中輸入公式“=(E7<$C7:$C10)*(E7<>"")”，這里不再贅述了，最后效果如下。

第二步：提取門店清單

首先使用“數(shù)據(jù)驗(yàn)證”功能在單元格A13中創(chuàng)建商品代碼的下拉清單。

然后在單元格B13中輸入公式“=IFERROR(INDEX($E$6:$N$6,SMALL(IF(($A$7:$A$10=$A$13)*($E$7:$N$10<$C$7:$C$10)*($E$7:$N$10<>""),COLUMN($E$7:$N$10)-4),ROW(A1))),"")”，按三鍵“Ctrl+shift+回車”并向下拖曳。

本質(zhì)上講，這也是一個(gè)一對(duì)多的查詢應(yīng)用。

函數(shù)解析：

1.   IF(($A$7:$A$10=$A$13)*($E$7:$N$10<$C$7:$C$10)*($E$7:$N$10<>""),COLUMN($E$7:$N$10)-4)部分，對(duì)于同時(shí)滿足條件的單元格（對(duì)應(yīng)的商品代碼、小于包裝數(shù)量和非空值）則返回它們所對(duì)應(yīng)的列號(hào)。

2.   利用SMALL函數(shù)依次提取上面的列號(hào)。

3.   利用INDEX函數(shù)返回對(duì)應(yīng)的門店名稱。

第三步：計(jì)算配送數(shù)量

在單元格C13中輸入公式“=IFERROR(IF(N(INDIRECT(TEXT(RIGHT(SMALL(IF(($E$7:$N$10<>"")*($A$7:$A$10=$A$13)*($E$7:$N$10<$C$7:$C$10),ROW($E$7:$N$10)/1%+COLUMN(E:N)*10001),ROW(A1)),4),"r0c00"),))>INDEX($C$7:$C$10,MATCH($A$13,$A$7:$A$10,))/2,INDEX($C$7:$C$10,MATCH($A$13,$A$7:$A$10,)),INDEX($C$7:$C$10,MATCH($A$13,$A$7:$A$10,))*2),"")”，按三鍵“Ctrl+shift+回車”并向下拖曳即可。

計(jì)算時(shí)，大家也可以使用更簡(jiǎn)單的INDEX+MATCH組合來(lái)完成。這里向小伙伴們介紹的多維引用的思路和技巧，雖然公式看起來(lái)比較長(zhǎng)，但其中的邏輯思路是比較簡(jiǎn)單的，掌握以后將來(lái)能更容易地處理各種不同的問(wèn)題。

函數(shù)解析：

1.   IF(($E$7:$N$10<>"")*($A$7:$A$10=$A$13)*($E$7:$N$10<$C$7:$C$10),ROW($E$7:$N$10)/1%+COLUMN(E:N)*10001)部分，對(duì)于那些滿足條件的單元格（對(duì)應(yīng)的商品代碼、小于包裝數(shù)量和非空值），把它們對(duì)應(yīng)的行號(hào)擴(kuò)大100倍，列號(hào)擴(kuò)大10001倍，并將這兩部分相加。其結(jié)果為{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;51005,FALSE,FALSE,81008,FALSE,FALSE,111011,FALSE,FALSE,FALSE}。

2.   SMALL(IF())部分利用SMALL函數(shù)特性可依次從小到大提取上面的數(shù)值。

3.   利用RIGHT函數(shù)提取4位字符。請(qǐng)注意，這里是關(guān)鍵的一步。上面的步驟中已經(jīng)將對(duì)應(yīng)數(shù)據(jù)的行號(hào)擴(kuò)大了100倍，列號(hào)擴(kuò)大了10001倍。所以，在兩個(gè)數(shù)據(jù)相加后，從右側(cè)開(kāi)始第1位和第2位是列信息，第3位和第4位是行信息。用RIGHT函數(shù)即可提取到行列號(hào)的信息，其結(jié)果為{"1005"}，表示第10行第5列。

4.   用TEXT函數(shù)將其轉(zhuǎn)換為R1C1的格式，返回值為{"r10c05"}。

5.   用INDIRECT函數(shù)提取目標(biāo)值，即為{75}。

6.   接下來(lái)還要做一個(gè)判斷，即根據(jù)補(bǔ)貨規(guī)則做一個(gè)判斷。用三個(gè)INDEX（）函數(shù)來(lái)判斷當(dāng)前物料的數(shù)量，其滿足大于1/2包裝數(shù)量且小于1個(gè)包裝數(shù)量的條件時(shí)，補(bǔ)貨一個(gè)整包數(shù)量；不滿足條件則補(bǔ)貨2個(gè)整包數(shù)量。

7.   下面另外一個(gè)重點(diǎn)內(nèi)容：在運(yùn)用的多維方法中，INDIRECT函數(shù)的結(jié)果是不能直接和INDEX($C$7:$C$10,MATCH($A$13,$A$7:$A$10,))/2來(lái)做比較的。在比較前，大家還需要用N函數(shù)來(lái)降維處理后才能比較。

這樣，所有的公式都已經(jīng)輸入完畢了。錄入不同的商品代碼后，一方面，EXCEL會(huì)用顏色標(biāo)識(shí)出需要補(bǔ)貨的門店信息，另一方面，它也列出了具體的清單。

今天的分享就是這些。

多維引用的公式看似復(fù)雜，其實(shí)是很簡(jiǎn)單的思路應(yīng)用。學(xué)習(xí)過(guò)程中，可能不會(huì)一帆風(fēng)順，但是多多練習(xí)掌握以后，小伙伴們?cè)谖磥?lái)即使面對(duì)不同的問(wèn)題，也可以輕松應(yīng)對(duì)。所以還是那句話，多多練習(xí)才是快速掌握公式的捷徑哦~

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

IF函數(shù)的新用法，早會(huì)早下班！

Excel數(shù)字提取技巧:從無(wú)規(guī)律文本中提取手機(jī)號(hào)的5種方法

5種最實(shí)用的提取文件名稱的妙招！

Excel數(shù)字提取技巧:用簡(jiǎn)單公式從混合文本中提取數(shù)字的3種情景

版權(quán)申明：

本文作者EXCEL應(yīng)用之家；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。