Excel 多條件變數 countif 函數 sumproduct and or - 通達人驛站
2014/11/13

Excel 多條件變數 countif 函數 sumproduct and or

最近在工作中,仍然繼續使用 Excel SUMPRODUCT 函數,發現其他 SUMPRODUCT 的進階使用,包括:在 SUMPRODUCT 內使用 and、or。所以我改寫之前廣受歡迎的《Excel多條件(變數)countif函數sumproduct》一文。

簡單地說,SUMPRODUCT 就是進階版的「2 variable countif」,所以不但能 count 超過二個以上的條件(變數),還可以使用 or、and 的條件。

另外,在這篇文章,我也同時介紹如何在 Google Spreadsheet 內使用 SUMPRODUCT。

在 SUMPRODUCT 內使用 and


以下面的例子來說:


如果要算出「E2」欄位:(Sales部門內) and (進度等於 A)的人數,就要輸入:
=SUMPRODUCT((B2:B11="Sales")*(C2:C11="A"))


SUMPRODUCT 內的「*」就是 and 的意思。

在 SUMPRODUCT 內使用 or


以前面的例子來說:(R&D 部門內) and ((進度等於 A) 或(進度等於 B)) 的人數,就可以改成輸入:
=SUMPRODUCT((B2:B11="R&D")*((C2:C11="A")+(C2:C11="B"))

SUMPRODUCT 內的「+」就是 or 的意思。

將 SUMPRODUCT True/False 陣列轉換為 1/0 陣列


在看了「火星人的天空之城」網站後,我發現 SUMPRODUCT 原來有另一種表達方式:1/0 陣列。

舉前面算出「E2」欄位:(Sales部門內) and (進度等於 A)的人數,可以改成:
=SUMPRODUCT(--(B2:B11="Sales"),--(C2:C11="A"))

再以前面的例子來說:(R&D 部門內) and ((進度等於 A) 或(進度等於 B)) 的人數,就可以改成:
=SUMPRODUCT(--(B2:B11="R&D"),--((C2:C11="A")+(C2:C11="B"))

此處的「--」是為了將 True/False 陣列轉換為 1/0 陣列。

在 Google Spreadsheet 內使用 SUMPRODUCT


如果你想要在 Google Spreadsheet 內使用 SUMPRODUCT 這個函數,必須在前面加入ARRAYFORMULA函數,就能正常使用了。例如:
=ARRAYFORMULA(SUMPRODUCT((B2:B11="Sales")*(C2:C11="A")))

注意事項


在使用 SUMPRODUCT 函數時,要注意" "內不可以有多餘的空白。如果寫成"Sales "就會得到 #VALUE! 錯誤訊息。
  1. mrexcel -《COUNTIF with two Variables?
  2. Google 網上論壇-《SUMPRODUCT won't work
  3. 火星人的天空之城-《Excel-在SUMPRODUCT函數中執行OR運算
 Related Posts with Thumbnails 

0 意見 :

張貼留言