點擊上方
藍色
文字 關(guān)注我們吧！
送人玫瑰，手有余香，請將文章分享給更多朋友
動手操作是熟練掌握EXCEL的最快捷途徑！
在實際的經(jīng)濟生活中，我們經(jīng)常會遇到給客戶報價的情形。一份設(shè)計合理的報價單通常能夠準確地向客戶提供產(chǎn)品價格信息。下圖就是一個很好的例子。
上述表格是一個二維表格。有時候我們也想得到一份根據(jù)一定條件從這個二維表格中提取出來的一維表格，即符合某種條件的產(chǎn)品價格清單。
方法有很多種，這里我們只向大家介紹一下如何通過函數(shù)公式來完成。
首先我們給出條件。這里加上了最低價和最高價這兩個限定條件。我們希望當給出最低價和最高價的限定條件后，在給定區(qū)間內(nèi)的所有產(chǎn)品的價格都用黃色標示出來。如下圖。
方法很簡單，利用條件格式就可以完成。選定單元格區(qū)域B2:F11，在條件格式中輸入“=AND(B2>=$I$2,B2<=$I$3)”，完成設(shè)定就可以了。
下面我們重點來介紹一下如何利用函數(shù)公式導出符合條件的所有產(chǎn)品的價格信息。
在單元格K2中輸入“=IFERROR(INDEX($A$2:$A$11,SMALL(IF(($B$2:$F$11>=$I$2)*($B$2:$F$11<=$I$3),ROW($A$2:$A$11)),ROW(A1))-1),"")”，三鍵回車并向下拖曳即可。
這是一個經(jīng)典的萬金油公式的應用。關(guān)于萬金油公式我們之前已經(jīng)介紹很多期了，這里不做詳細介紹了。如有興趣的小伙伴們可以在我的公眾號中搜索相關(guān)內(nèi)容。
在單元格L2中輸入“=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF((($B$2:$F$11>=$I$2)*($B$2:$F$11<=$I$3)*($A$2:$A$11=K2)-COUNTIF($L$1:L1,$B$2:$F$11))>0,ROW($2:$11)/1%+COLUMN(B:F)*10001),ROW($A$1)),4),"r0c00"),),"")”，三鍵回車并向下拖曳即可。
在單元格M2中輸入“=IFERROR(INDEX($A$1:$F$1,SUMPRODUCT(($A$2:$A$11=K2)*($B$2:$F$11=L2)*(COLUMN(B:F)))),"")”并向下拖曳即可。
由于這部分公式也比較簡單，我們也不做過多的介紹了。
下面我們著重對L列的公式進行解析分析。
思路：
IF((($B$2:$F$11>=$I$2)*($B$2:$F$11<=$I$3)*($A$2:$A$11=K2)-COUNTIF($L$1:L1,$B$2:$F$11))>0,ROW($2:$11)/1%+COLUMN(B:F)*10001)部分，這是一個IF條件判斷。我們先說說后半部分當條件為真實返回的結(jié)果ROW($2:$11)/1%+COLUMN(B:F)*10001。當條件為真時，將數(shù)據(jù)區(qū)域?qū)男刑枖U大100倍，對應列號擴大10001倍，并將兩部分的結(jié)果相加
IF函數(shù)的條件部分，($B$2:$F$11>=$I$2)*($B$2:$F$11<=$I$3)*($A$2:$A$11=K2)給出需要滿足的條件。COUNTIF($L$1:L1,$B$2:$F$11)部分的作用是用來剔除已經(jīng)在L列出先的數(shù)據(jù)。最后對整個條件部分做大于零的判斷
利用SMALL函數(shù)來提取最小值。前面講行號擴大100倍，列號擴大10001倍，這樣做后從小到大順序是先縱向方向（列），再橫向方向（行）。當限定了產(chǎn)品這個條件后，從小到大的提取順序就是每行中從左向右依次提取
利用RIGHT函數(shù)提取右側(cè)4為字符，截取的內(nèi)容實際上就是行號和列號的信息。這時可以返回頭去想一下列號擴大10001倍的妙處
利用TEXT函數(shù)將上述4位字符轉(zhuǎn)換為R1C1的形式
利用INDIRECT函數(shù)提取對應位置下的數(shù)據(jù)
小伙伴們，你們理解了嗎？
-END-
長按下方二維碼關(guān)注EXCEL應用之家
面對EXCEL操作問題時不再迷茫無助
推薦閱讀：
一對多查詢經(jīng)典函數(shù)組合拓展應用--多對多查詢
遇到不規(guī)范的數(shù)據(jù)錄入，你該怎么辦？
戳原文，更有料！免費模板文檔！
