2011年4月20日 星期三

Excel-SUM中的AND和OR運算

再次來練習在SUM函數中執行AND和OR運算,參考下圖。

設定名稱A:儲存格B2:B11、名稱B:儲存格C2:C11、名稱S:儲存格D2:D11。

以下是陣列公式者,輸入完成應按 Ctrl+Shift+Enter 鍵。

(1) 計算A>5,求B的和

儲存格H2:=SUMIF(A,">5",B)

(2) 計算A>5 and A<10,求A的和

儲存格H3:{=SUM((A>5)*(A<10)*A)}

={0,1,0,0,1,1,0,0,0,0}*{1,1,1,1,0,1,1,1,1,1}*A

={0,1,0,0,0,1,0,0,0,0}*A

=6+9

=15

你還可以有以下的不同的寫法:

儲存格H3:=SUMPRODUCT((A>5)*(A<10)*A)

儲存格H3:=SUMPRODUCT(--(A>5),--(A<10),A)

儲存格H3:={SUM(IF(A>5,IF(A<10,A,0)))}

(3) 計算A>5 and A<10,求B的和

儲存格H4:{=SUM(IF(A>5,IF(B<10,B,0)))}

IF(條件一,IF(條件二,XX,XX))相當於條件一AND條件二。兩個條件均成立者輸出B,否則輸出0。

(4) 計算A>5 or A<10,求S的和

儲存格H5:{=SUM(IF((A>5)+(B<10)>=1,1,0)*S)}

={0,1,0,0,1,1,0,0,0,0}+{0,0,0,1,1,0,1,0,1,1}*A

={0,1,0,1,2,1,1,0,1,1}*A

=>{0,1,0,1,1,1,1,0,1,1}*A

(5) 計算計算A>5 and A<10,求個數

儲存格H8:{=SUM((A>5)*(A<10)*1)}

試試不同寫法:

儲存格H8:=SUMPRODUCT((A>5)*(A<10)*1)

儲存格H8:{=SUM(IF(A>5,IF(A<10,1,0)))}

(6) 計算A>5 and A<10,求個數

儲存格H9:{=SUM(IF(A>5,IF(B<10,1,0)))}

試試不同寫法:

儲存格H9:=SUMPRODUCT((A>5)*(B<10)*1)

儲存格H9:{=SUM(IF(A>5,IF(B<10,1,0)))}

(6) 計算A>5 or A<10,求個數

儲存格H10:{=SUM(IF((A>5)+(B<10)>=1,1,0)*1)}

沒有留言:

張貼留言

好康東東