DGET、SUMIFS、SUMPRODUCT、LOOKUP、OFFSET、VLOOKUP多條件查詢
問題來源
多條件查詢一直是困擾EXCEL使用者的難題之一,今天韓老師就把經常用於多條件查找的DGET、SUMIFS、SUMPRODUCT、LOOKUP、OFFSET、VLOOKUP六個函數綜合講解。
示例數據:
查詢倉庫二鍵盤的銷量。
韓老師視頻講解
關鍵步驟提示
第一種:DGET函數
在G2單元格輸入公式:「=DGET(A1:C13,C1,E1:F2)」
DGET(構成列表或資料庫的單元格區域, 結果數據的列標籤, 指定條件的單元格區域);
在本題中的解釋:
=DGET(資料庫,銷量列標籤,條件區域)。
第二種:SUMIFS函數
在G5單元格輸入公式:「=SUMIFS(C2:C13,A2:A13,E5,B2:B13,F5)」
第三種:SUMPRODUCT函數
在G8單元格輸入公式:「=SUMPRODUCT((A2:A13=E8)*(B2:B13=F8)*C2:C13)」
其中,各個數組返回值:
三個數組對應位置數據乘積求和。
注意:SUMPRODUCT函數只能用於查詢「數值」單元格。
第四種:LOOKUP函數
在G11單元格輸入公式:「=LOOKUP(1,0/((A2:A13=E11)*(B2:B13=F11)),C2:C13)」
注意要點:
LOOKUP函數用「二分法」進行查找。
返回小於等於lookup_value(查找值)的最大值。
Lookup_vector(查找區域)中如果有「錯誤值「,那麼LOOKUP函數在查找時將會忽略錯誤值。
「=LOOKUP(1,0/((A2:A13=E11)*(B2:B13=F11)),C2:C13)」
在 {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}里查找1,忽略錯誤值,結果返回0對應位置的C2:C13中的數據。
第五種:OFFSET函數
在G14單元格輸入公式:「=OFFSET(C1,MATCH(E14&F14,A2:A13&B2:B13,0),)」
本公式的含義是:以C1為基準,公式向下偏移MATCH(E14&F14,A2:A13&B2:B13,0)行。
其中E14&F14和A2:A13&B2:B13分別對應的結果:
公式結束時需按「CTRL+SHIFT+ENTER」組合鍵。
第六種:VLOOKUP函數
在G14單元格輸入公式:「=VLOOKUP(E17&F17,IF(,A2:A13&B2:B13,C2:C13),2,0)」
其中「IF(,A2:A13&B2:B13,C2:C13)」是生成一新的數據區域:
「=VLOOKUP(E17&F17,IF(,A2:A13&B2:B13,C2:C13),2,0)」是指在新的區域中精確匹配第2列的數值。
公式結束時需按「CTRL+SHIFT+ENTER」組合鍵。
最終結果:
本示例練習:
鏈接:http://pan.baidu.com/s/1eRBJomm
密碼:qe1f
點擊展開全文
![](https://pic.pimg.tw/zzuyanan/1488615166-1259157397.png)
![](https://pic.pimg.tw/zzuyanan/1482887990-2595557020.jpg)
※浙江省二級AOA系列之WORD單項操作(一)——郵件合併
※SUMPRODUCT函數用法之二:單條件、多條件、模糊條件計數
※浙江省二級AOA系列之WORD長文檔排版(二)——分節符的使用與頁碼生成
※SUMPRODUCT函數用法之一:單條件、多條件、模糊條件求和
※浙江省二級AOA系列之WORD長文檔排版(一)——自動生成目錄和圖表索引
TAG:韓老師講Office |