close

334

Excel中實現多條件查找的15種方法

示範檔

範例檔

334.XLSX

結果檔

如下圖所示,根據第9列的產品名稱和型號,從上面表中查找「銷售數量」,結果如C9儲存格所示。

image

n  1SUM函數

C9儲存格輸入公式:「=SUM((A2:A6=A9)*(B2:B6=B9)*C2:C6)」後,再按 Ctrl + Shift +Enter鍵,結果如下圖所示。

image

公式簡介:使用(條件)*(條件)因為每欄符合條件的為0,不符合的為1,所以只有條件都符合的為非零數字。所以SUM求和後就是多條件查找的結果。

n  2SUMPRODUCT函數

C9儲存格輸入公式:「=SUMPRODUCT((A2:A6=A9)*(B2:B6=B9)*C2:C6)」後,再按 Enter鍵,結果如下圖所示。

image

公式簡介:和SUM函數用法差不多,只是SUMPRODUCT函數不需要陣列運算

n  3MAX函數

C9儲存格輸入公式:「=MAX((A2:A6=A9)*(B2:B6=B9)*C2:C6)」後,再按Ctrl + Shift +Enter鍵,結果如下圖所示。

image

SUM是通過求和把符合條件的提出來,這裡是使用MAX提取出最大值來完成符合條件的值提取。

n  4lookup函數

C9儲存格輸入公式:「=LOOKUP(A9&B9,A2:A6&B2:B6,C2:C6)」後,再按Enter鍵,結果如下圖所示。

image

公式簡介:LOOKUP函數可以直接進行陣列運算。查找的連接起來,被查找範圍也連接起來。

C9儲存格輸入公式:「=LOOKUP(1,0/((A2:A6=A9)*(B2:B6=B9)),C2:C6) 」後,再按Enter鍵,結果如下圖所示。

image

C9儲存格輸入公式:「=LOOKUP(1,0/((A2:A6&B2:B6)=(A9&B9)),C2:C6)」後,再按Enter鍵,結果如下圖所示。

image

C9儲存格輸入公式:「=LOOKUP(1,1/(((A2:A6=A9)+(B2:B6=B9))=2),C2:C6)」後,再按Enter鍵,結果如下圖所示。

image

n  5MIN+IF函數

C9儲存格輸入公式:「=MIN(IF((A2:A6=A9)*(B2:B6=B9),C2:C6))」後,再按Ctrl + Shift +Enter鍵,結果如下圖所示。

image

n  6SUM+IF函數

C9儲存格輸入公式:「=SUM(IF(A2:A6=A9,IF(B2:B6=B9,C2:C6,0),0))」後,再按Ctrl + Shift +Enter鍵,結果如下圖所示。

image

n  7INDEX+MATCH函數組合

C9儲存格輸入公式:「=INDEX(C2:C6,MATCH(A9&B9,A2:A6&B2:B6,0))」後,再按Ctrl + Shift +Enter鍵,結果如下圖所示。

image

C9儲存格輸入公式:「=INDEX(C2:C6,MATCH(1,(A9=A2:A6)*(B9=B2:B6),0))」後,再按 Ctrl + Shift +Enter鍵,結果如下圖所示。

image

n  8OFFSET+MATCH函數

C9儲存格輸入公式:「=OFFSET(C1,MATCH(A9&B9,A2:A6&B2:B6,0),)」後,再按Ctrl + Shift +Enter鍵,結果如下圖所示。

image

n  9INDIRECT+MATCH函數

C9儲存格輸入公式:「=INDIRECT("C"&MATCH(A9&B9,A1:A6&B1:B6,0))」後,再按Ctrl + Shift +Enter鍵,結果如下圖所示。

image

n  10VLOOKUP+CHOOSE函數

C9儲存格輸入公式:「=VLOOKUP(A9&B9,CHOOSE({1,2},A2:A6&B2:B6,B2:C6),2,0)」後,再按Ctrl + Shift +Enter鍵,結果如下圖所示。

image

n  11HLOOKUP+TRANSPOSE+CHOOSE函數

C9儲存格輸入公式:「=HLOOKUP(A9&B9,TRANSPOSE(CHOOSE ({1,2},A2:A6&B2:B6, B2:C6 )) ,2,0)」後,再按 Ctrl + Shift +Enter鍵,結果如下圖所示。

image

n  12VLOOKUP+IF函數

C9儲存格輸入公式:「=VLOOKUP(A9&B9,IF({1,0},A2:A6&B2:B6,B2:C6),2,0)」後,再按 Ctrl + Shift +Enter鍵,結果如下圖所示。

image

n  13SUMIFS函數

Excel 2007開始提供的函數SUMIFS

C9儲存格輸入公式:「=SUMIFS(C1:C6,A1:A6,A9,B1:B6,B9)」後,再按Enter鍵,結果如下圖所示。

image

n  14、資料庫函數

C9儲存格輸入公式:「=DSUM(A1:C6,3,A8:B9)」後,再按Enter鍵,結果如下圖所示。

image

C9儲存格輸入公式:「=DGET(A1:C6,3,A8:B9) 」後,再按Enter鍵,結果如下圖所示。

image

C9儲存格輸入公式:「=DAVERAGE(A1:C6,3,A8:B9) 」後,再按Enter鍵,結果如下圖所示。

image

C9儲存格輸入公式:「=DMAX(A1:C6,3,A8:B9)」後,再按Enter鍵,結果如下圖所示。

image

C9儲存格輸入公式:「=DMIN(A1:C6,3,A8:B9)」後,再按Enter鍵,結果如下圖所示。

image

C9儲存格輸入公式:「=DPRODUCT(A1:C6,3,A8:B9)」後,再按Enter鍵,結果如下圖所示。

image


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(1) 人氣()