2014年3月3日 星期一

Excel-兩組數字判斷奇偶個數(MOD,SUMPRODUCT,NOT)

在 Excel 的資料表中含有 A 組和 B 組資料,如果想要判斷各組的奇/偶數個數,及二者和之奇/偶數個數,該如何處理。

【準備工作】

選取儲存格A1:B22,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:A組、B組。

【基礎知識】

(1) MOD 函數用以餘數,所以可以使用 MOD 函數來找出一個數除以 2 的餘數,用以判斷該數為奇數或是偶數。例如:

MOD(A2,2) = 1,表示儲存格A2的內容為奇數。

MOD(A2,2) = 0,表示儲存格A2的內容為偶數。

(2) MOD(A2,2)+MOD(B2,2) 會有三種可能:

MOD(A2,2)+MOD(B2,2) = 0 :表示 儲存格A2、儲存格B2 皆為偶數。

MOD(A2,2)+MOD(B2,2) = 2 :表示 儲存格A2、儲存格B2 皆為奇數。

MOD(A2,2)+MOD(B2,2) = 1 :表示 儲存格A2、儲存格B2 一奇一偶。

(3) 在公式數如果取 TRUE/FALSE 來運算,則 TRUE 視為 1,FALSE 視為 0。

(4) NOT 邏輯運算為「相反」,所以:

NOT(TRUE) = FALSE,NOT(FALSE) = TRUE。

NOT(1) = FALSE = 0,NOT(0) = TRUE = 1。

(5) 使用 SUMPRODUCT 函數來計算陣列中符合條件的乘積和。其公式中常會使用「--」運算來將 TRUE/FALSE 陣列轉換為 1/0 陣列,其和「*1」和「+0」等是相同意思的。

 

【輸入公式】

(1) A組和B組有2個偶數的個數

a.儲存格C2:=IF(MOD(A2,2)+MOD(B2,2)=0,"V","")

b.儲存格C2:=IF(MOD(A2,2)=0,IF(MOD(B2,2)=0,"V",""),"")

該巢狀 IF 函數結構,表示符合 MOD(A2,2)=0 且符合 MOD(B2,2)=0 者為二者皆為偶數。

(2) A組和B組有2個奇數的個數

a.儲存格D2:=IF(MOD(A2,2)+MOD(B2,2)=0,"V","")

b.儲存格D2:=IF(MOD(A2,2)=1,IF(MOD(B2,2)=1,"V",""),"")

該巢狀 IF 函數結構,表示符合 MOD(A2,2)=1 且符合 MOD(B2,2)=1 者為二者皆為奇數。

(3) A組和B組有1偶1奇的個數

儲存格E2:=IF(MOD(A2,2)+MOD(B2,2)=0,"V","")

(4) A組和B組者皆偶數的總數

a.儲存格H2:=SUMPRODUCT(--((MOD(A組,2)+MOD(B組,2))=0))

b.儲存格H2:=SUMPRODUCT(NOT(MOD(A組,2))*NOT(MOD(B組,2)))

c.儲存格H2:=SUMPRODUCT((MOD(A組,2)-1)*(MOD(B組,2)-1))

比較以上三式,其結果相同。

(5) A組和B組二者皆奇數的總數

a.儲存格H3:=SUMPRODUCT(--((MOD(A組,2)+MOD(B組,2))=2))

b.儲存格H3:=SUMPRODUCT(MOD(A組,2)*MOD(B組,2))

比較以上二式,其結果相同。

(6) A組和B組二者1偶1奇的總數

儲存格H4:=SUMPRODUCT(--((MOD(A組,2)+MOD(B組,2))=1))

(7) A組和B組二者和為奇數的總數

儲存格H5:=SUMPRODUCT(--NOT(MOD(A組+B組,2)))

(8) A組和B組二者和為偶數的總數

儲存格H6:=SUMPRODUCT(--MOD(A組+B組,2))

試著比較(7)和(8)二式。

沒有留言:

張貼留言

好康東東