在前幾篇文章中提及了 SUMPROCUT 函數的使用,你有了解了嗎?

Excel-常用指令SUMPRODUCT函數

Excel-互通陣列公式、SUM+IF、SUMIF、SUMIFS、SUMPRODUCT

SUMPRODUCT 函數對於處理陣列資料的功能性很強,本篇再來讓初學者有一些練習,以增強理解與應用。

 

【使用 SUMPRODUCT 函數】

選取A2:B25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:A組、B組。

Excel-使用SUMPRODUCT函數執行陣列資料運算

(1) 計算 A>B 個數

公式:=SUMPRODUCT((A組>B組)*1)

A組和B組是兩組陣列資料,而(A組>B組)是一個判斷式,在 SUMPRODUCT 函數中要判斷陣列中的每一個 A>B 的結果,會傳回 TRUE/FALSE 陣列,在經過「*1」運算後,會轉換為 1/0 陣列。再經由 SUMPRODUCT 函數執行加總。

(2) 計算 A,B 皆偶數個數

公式:=SUMPRODUCT((MOD(A組,2)+MOD(B組,2)=0)*1)

MOD 函數用於兩數相除求得餘數,若是除以 2 的餘數為 0,表示偶數;若是除以 2 的餘數為 1,表示奇數。

MOD(A組,2)+MOD(B組,2)=0 用以判斷 A 和 B 均為偶數,即 MOD(A組,2)=0 AND MOD(B組,2)=0。

(3) 計算 A,B 1奇1偶個數

公式:=SUMPRODUCT((MOD(A組,2)+MOD(B組,2)=1)*1)

MOD(A組,2)+MOD(B組,2)=1 用以判斷 A 和 B均為1奇1偶,即 MOD(A組,2)=0、MOD(B組,2)=1 或 MOD(A組,2)=1、MOD(B組,2)=0。

(4) 計算 A,B 皆奇數個數

公式:=SUMPRODUCT((MOD(A組,2)+MOD(B組,2)=2)*1)

MOD(A組,2)+MOD(B組,2)=2 用以判斷 A 和 B 均為奇數,即 MOD(A組,2)=1 AND MOD(B組,2)=1。

這幾個公式藉由在 SUMPRODUCT 函數使用 MOD 函數的巧妙安排,可以運算各種狀況的數值。

Excel-使用SUMPRODUCT函數執行陣列資料運算

 

【使用陣列公式】

Excel-使用SUMPRODUCT函數執行陣列資料運算

(1) 計算 A>B 個數

公式:{=SUM((A組>B組)*1)}

陣列公式在輸入完成後,要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

(2) 計算 A,B 皆偶數個數

公式:{=SUM((MOD(A組,2)+MOD(B組,2)=0)*1)}

(3) 計算 A,B 1奇1偶個數

公式:{=SUM((MOD(A組,2)+MOD(B組,2)=1)*1)}

(4) 計算 A,B 皆奇數個數

公式:{=SUM((MOD(A組,2)+MOD(B組,2)=2)*1)}

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel SUMPRODUCT
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(0) 人氣()