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

正文：

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

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

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

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

2.   庫存小于等于1/2個“包裝數(shù)量”的標準時，配送2個的標準“包裝數(shù)量”的量；庫存大于1/2個“包裝數(shù)量”的標準，同時又小于1個“包裝數(shù)量”的標準時，配送1個標準“包裝數(shù)量”的量。

3.   顏色標記需要配貨的門店。

第一步：標記顏色

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

第二步：提取門店清單

首先使用“數(shù)據(jù)驗證”功能在單元格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ì)上講，這也是一個一對多的查詢應用。

函數(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)部分，對于同時滿足條件的單元格（對應的商品代碼、小于包裝數(shù)量和非空值）則返回它們所對應的列號。

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

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

第三步：計算配送數(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+回車”并向下拖曳即可。

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

函數(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)部分，對于那些滿足條件的單元格（對應的商品代碼、小于包裝數(shù)量和非空值），把它們對應的行號擴大100倍，列號擴大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位字符。請注意，這里是關(guān)鍵的一步。上面的步驟中已經(jīng)將對應數(shù)據(jù)的行號擴大了100倍，列號擴大了10001倍。所以，在兩個數(shù)據(jù)相加后，從右側(cè)開始第1位和第2位是列信息，第3位和第4位是行信息。用RIGHT函數(shù)即可提取到行列號的信息，其結(jié)果為{"1005"}，表示第10行第5列。

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

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

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

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

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

今天的分享就是這些。

多維引用的公式看似復雜，其實是很簡單的思路應用。學習過程中，可能不會一帆風順，但是多多練習掌握以后，小伙伴們在未來即使面對不同的問題，也可以輕松應對。所以還是那句話，多多練習才是快速掌握公式的捷徑哦~

本文配套的練習課件請加入QQ群：902294808下載。

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

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

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

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

5種最實用的提取文件名稱的妙招！

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

版權(quán)申明：

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