多條件查詢求和,實際中很常見,基於此,胖斯基將重用方法進行整理,看看你會幾種?
方法1. Vlookup方法【涉及到數據區域的組合】
vlookup方式
公式1:=VLOOKUP(E2&F2,IF({1,0},A2:A10&B2:B10,C2:C10),2,0) 數組公式
由於是多條件,所以要把多條件進行組合,條件1 & 條件2 & 條件n,本例子中是E2&F2。
條件進行了組合,那對應被查詢的區域範圍也要進行組合,即對應的A2:A10與B2:B10進行組合,故A2:A10&B2:B10。
由於利用的是Vlookup,所以其查詢區域要構建出來,故用到if函數,結合{1,0}數組進行了判斷,其結果區域就為A2&B10到C10這個範圍,然後Vlookup即可得出結果。
注意:如果源數據中多條件判斷的是唯一值,其結果正確;要是非唯一值,則僅僅只取第1次匹配上的結果(這是所謂的一見鍾情?)
方法2. Lookup方法
Lookup方法
公式2:=LOOKUP(1,0/((A2:A10=E2)*(B2:B10=F2)),C2:C10)
說明:
1.(A2:A10=E2)*(B2:B10=F2) 即條件1*條件2,條件滿足的話結果為1,不滿足的話結果為0,所以這麼計算出來的結果是一串數組{0;0;0;0;0;0;1;0;0};
2.用0/(A2:A10=E2)*(B2:B10=F2) ,其結果很明顯:僅有第7項有意義,其他項無意義(分母為0)
3. 然後用LOOKUP查詢,定位到第7項,而C2:C10的第7項,即為結果
套路:LOOKUP(1,0/((條件1)*( 條件2) *( 條件n)),結果區域)
注意:如果源數據中多條件判斷的是唯一值,其結果正確;要是非唯一值,則僅僅只取最後1次匹配上的結果(這可不是一見鍾情!)
方法3. Index+Match
公式3: =INDEX(C2:C10,MATCH(E2&F2,A2:A10&B2:B10,0)) 數組公式
說明:
1.區域數據合併,採用&方式,已經說明過了
2.在合併的區域內,採用Match方式,定位其在目標區域中的第幾行(7)
3.利用Index,檢索C2:C10中第7個數據,得到結果
套路:INDEX( 結果區域,MATCH (條件1 & 條件2 & 條件n,條件區域1 &條件區域2 & 條件區域n,0))
注意:如果源數據中多條件判斷的是唯一值,其結果正確;要是非唯一值,則僅僅只取第1次匹配上的結果(一見鍾情)
方法4. Index+Min+If(經典組合)
公式4:=INDEX(C2:C10,MIN(IF((A2:A10=E2)*(B2:B10=F2),ROW(A2:A10)-1,4^8)))
說明:
1.利用if進行判斷,如果條件1*條件2滿足,則返回其對應的行號(位置),反之則範圍一個較大的數(4^8即65536,也可以改為其他的,比範圍行數大的數即可)
2.通過Min函數,範圍其最小值(此時是7,其他的是4^8)
3.通過Index函數,檢索C2:C10中第7個數據,得到結果
套路:INDEX(目標範圍,MIN(IF(條件1 & 條件2 & 條件n , 返回行數 ,4^8)))
注意:如果源數據中多條件判斷的是唯一值,其結果正確;要是非唯一值,則僅僅只取第1次匹配上的結果(一見鍾情)
方法5 . Sum(數組公式)
公式5:=SUM((A2:A10=E2)*(B2:B10=F2)*C2:C10) 數組公式
說明:條件1*條件呢2*求和區域 再進行求和,比較簡單
套路:SUM(條件1*條件呢2*條件呢n*求和區域)
注意:無論源數據裡面是否唯一,其結果均正確(因為是求和)
方法6. SUMPRODUCT
公式6:=SUMPRODUCT((A2:A10=E2)*(B2:B10=F2)*C2:C10)
說明:標準的SUMPRODUCT的基本應用,無需解釋
注意:無論源數據裡面是否唯一,其結果均正確(因為是求和)
方法7. DSUM
公式7 =DSUM(A1:C10,3,E1:F2)
說明:
1.DSUM數據庫求和函數,也是標準應用
2.DSUM(數據庫區域 , 返回結果的列數 , 條件區域) 要求:數據庫區域與條件區域的列名字,需要一樣,比如數據庫區域叫【手機】,條件區域也需要叫【手機】而不是【phone】;返回結果的列數這裡手工數一數就可以了,這裡要返回庫存數,而庫存數在這個數據庫區域的第3列,所以填寫3
注意:無論源數據裡面是否唯一,其結果均正確
方法8. DGET
公式8:=DGET(A1:C10,3,E1:F2)
說明:同DSUM函數一樣思路,需要注意的是,DGET在數據唯一值時管用,要是重複值,則會錯誤
方法9. Offset+Match
公式:OFFSET(C1,MATCH(E2&F2,A2:A10&B2:B10,0),,,) 數組公式
說明:原因不解釋了(一見鍾情),需要考慮下OFFSET函數的應用,後期會專題講,你可以思考下。
方法10. Indirect+Macth
公式10:=INDIRECT("C"&MATCH(E2&F2,A2:A10&B2:B10,0)+1)
說明:原因不解釋了(一見鍾情),需要考慮下INDIRECT函數的應用,後期會專題講,你可以思考下。
最終:整理歸納如下:
胖斯基說:
多條件查詢求和,在實際過程中經常遇到也經常使用,還沒有明白的,趕緊收藏,以後備用!
如果胖斯基的文章到你有幫助,請幫忙轉發和關注,感謝!
如果需要源文件,請私信胖斯基或者留言區留下郵箱,稍後呈上!
閱讀更多 Excel老斯基 的文章