|
美輪美奐的深圳人才公園 在《Excel中反向查找方法大全》中，講解了實現(xiàn)單一條件查找的四種方法，遇到多條件查找需求怎么辦？本文就主要來講解多條件查找的十種實現(xiàn)方法。 如下B:D列是產(chǎn)品不同型號的單價信息，要求根據(jù)B13和C13兩個單元格對應的產(chǎn)品和型號，在D13單元格查找對應的單價。 實現(xiàn)非常之多，逐一介紹如下： 【1】VLookup函數(shù)： D13單元格公式： {=VLOOKUP(B13&C13,IF({1,0},B2:B9&C2:C9,D2:D9),2,FALSE)} B13&C13 ： 多條件構成的組合查詢條件 B2:B9&C2:C9 ：數(shù)組運算返回組合的查詢區(qū)域 IF({1,0},B2:B9&C2:C9,D2:D9)：返回組合的查詢區(qū)域和結果區(qū)域 VLOOKUP按常規(guī)正向查詢返回結果 特別提醒： 因有數(shù)組運算，必須按照數(shù)組公式輸入，即同時按Ctrl Shift Enter完成數(shù)組公式輸入。 【2】Lookup函數(shù)： D13單元格公式： =LOOKUP(1,0/((B2:B9=B13)*(C2:C9=C13)),D2:D9) ((B2:B9=B13)*(C2:C9=C13)) 符合多條件的返回1，否則返回0 0/((B2:B9=B13)*(C2:C9=C13))符合多條件的返回0，否則返回0/0的錯誤值 =LOOKUP(1,0/((B2:B9=B13)*(C2:C9=C13))，Lookup函數(shù)在查找的時候是忽略錯誤的，所以返回小于1的最大值位置，即符合條件的0值位置，從而返回D2:D9對應位置的單價。 理解0/((B2:B9=B13)*(C2:C9=C13))的目的就是將正確結果用0表示，其他的變成錯誤值，利用Lookup函數(shù)查找忽略錯誤這個特點完成查找。 【3】Index Match函數(shù)： D13單元格公式： {=INDEX(D2:D9,MATCH(B13&C13,B2:B9&C2:C9,0))} B13&C13 ： 多條件構成的組合查詢條件 B2:B9&C2:C9 ：數(shù)組運算返回組合的查詢區(qū)域 MATCH(B13&C13,B2:B9&C2:C9,0)：返回組合查詢條件滿足的行序號 INDEX(D2:D9，行序號）返回D2:D9對應行序號的單價 特別提醒： 因有數(shù)組運算，必須按照數(shù)組公式輸入，即同時按Ctrl Shift Enter完成數(shù)組公式輸入。 【4】Offset Match函數(shù)： D13單元格公式： {=Offset(D1,Match(B13&C13,B2:B9&C2:C9,0),)} B13&C13 ： 多條件構成的組合查詢條件 B2:B9&C2:C9 ：數(shù)組運算返回組合的查詢區(qū)域 MATCH(B13&C13,B2:B9&C2:C9,0)：返回組合查詢條件滿足的行序號 OFFSET(D1，行序號, ）返回對應行序號的單價 特別提醒： 因有數(shù)組運算，必須按照數(shù)組公式輸入，即同時按Ctrl Shift Enter完成數(shù)組公式輸入。 【5】Indirect Match函數(shù)： D13單元格公式： {=INDIRECT('D' & (MATCH(B13&C13,B2:B9&C2:C9,0) 1))} B13&C13 ： 多條件構成的組合查詢條件 B2:B9&C2:C9 ：數(shù)組運算返回組合的查詢區(qū)域 MATCH(B13&C13,B2:B9&C2:C9,0)：返回組合查詢條件滿足的區(qū)域行序號 MATCH(B13&C13,B2:B9&C2:C9,0) 1：返回組合查詢條件滿足的D列行號 INDIRECT函數(shù)作用：根據(jù)字符串拼接得到的單元格引用返回單元格值。 特別提醒： 因有數(shù)組運算，必須按照數(shù)組公式輸入，即同時按Ctrl Shift Enter完成數(shù)組公式輸入。 【6】SUM函數(shù)： D13單元格公式： {=SUM((B2:B9=B13)*(C2:C9=C13)*D2:D9)} (B2:B9=B13)*(C2:C9=C13)：滿足組合查詢條件返回1 (B2:B9=B13)*(C2:C9=C13)*D2:D9：滿足組合查詢條件返回單價，否則返回0，滿足條件的只有一條數(shù)據(jù)，求和即是單價 特別提醒： 因有數(shù)組運算，必須按照數(shù)組公式輸入，即同時按Ctrl Shift Enter完成數(shù)組公式輸入。 【7】SUMPRODUCT函數(shù)： D13單元格公式： =SUMPRODUCT((B2:B9=B13)*(C2:C9=C13)*D2:D9) (B2:B9=B13)*(C2:C9=C13)：滿足組合查詢條件返回1 (B2:B9=B13)*(C2:C9=C13)*D2:D9：滿足組合查詢條件返回單價，否則返回0，滿足條件的只有一條數(shù)據(jù)，求和即是單價 【8】SUMIFS函數(shù)： D13單元格公式： =SUMIFS(D2:D9,B2:B9,B13,C2:C9,C13) 滿足多條件的只有一條數(shù)據(jù)，求和即是單價 【9】MAX函數(shù)： D13單元格公式： {=MAX((B2:B9=B13)*(C2:C9=C13)*D2:D9)} (B2:B9=B13)*(C2:C9=C13)*D2:D9：滿足組合查詢條件返回單價，否則返回0，滿足條件的只有一條數(shù)據(jù)，求最大值即是單價 【10】MIN IF函數(shù)： D13單元格公式： {=MIN(IF((B2:B9=B13)*(C2:C9=C13),D2:D9))} IF((B2:B9=B13)*(C2:C9=C13),D2:D9)：滿足組合查詢條件返回單價，否則返回FALSE MIN忽略邏輯值FALSE，滿足多條件的單價只有一條數(shù)據(jù)，求最小值即是單價。 其實實現(xiàn)多條件查找的還遠不止這十種方法，寫到此，深感Excel函數(shù)功能還真是豐富和強大。
分享Excel實用技術，助力辦公效率提升 歡迎加入Excel表哥之家
|
|
|
