2013年10月3日 星期四

Excel-SUMPRODUCT練習(COUNTIF,SUMIF,陣列公式)

最近在學校的 Excel 程式單元中,教完 COUNTIF 和 SUMIF 函數後,接著教 SUMPRODUCT 函數,以下是學生的練習題,你也可以來試試:

【準備工作】

選取儲存格A1:D26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、人員、性別、數值。

image

【輸入公式】

(1) 性別:男/女,求「人員」個數和其數值總和

儲存格G2:=SUMPRODUCT(--(性別=F2))

儲存格H2:=SUMPRODUCT(--(性別=F2),數值)

其中「--」的用意是要將 True/False 陣列經由運算後變成 1/0 陣列,所以以下的做法都可以達到相同的結果:

  • =SUMPRODUCT((性別=F2)+0)
  • =SUMPRODUCT((性別=F2)*1)
  • =SUMPRODUCT((性別=F2)/1)
  • =SUMPRODUCT((性別=F2)^1)

複製儲存格G2:H2,貼至儲存格G2:H3

接著,改以 COUNTIF 函數和 SUMIF 函數來練習:

儲存格G2:=COUNTIF(性別,F2)

儲存格H2:=SUMIF(性別,F2,數值)

 

(2) 人員:甲/乙/丙/丁/戊/己,求「奇數」個數和其數值總和

儲存格G6:=SUMPRODUCT((人員=F6)*(MOD(數值,2)=1))

儲存格H6:=SUMPRODUCT((人員=F6)*(MOD(數值,2)=1)*數值)

其中使用 MOD 函數來求得數值除以 2 的餘數,若是餘 1,則為奇數。(反之為偶數)

複製儲存格G6:H6,貼至儲存格G6:H11

【進階】若改為陣列公式:(輸入公式後要按 Ctrl+Shift+Enter 鍵)

儲存格G6:{=SUM(IF((人員=F6)*(MOD(數值,2)=1),1,FALSE))}

儲存格H6:{=SUM(IF((人員=F6)*(MOD(數值,2)=1),數值,FALSE))}

 

(3) 日期:一月/二月/三月/四月,求「女生」個數和其數值總和

儲存格G14:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(性別="女"))

儲存格H14:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(性別="女"),數值)

透過 MONTH 函數取出日期陣列中的月份成為月份陣列,因為ROW(1:1)=1,往下複製時可以產生 ROW(2:2)=2、ROW(3:3)=3、…。

複製儲存格G14:H14,貼至儲存格G14:H17

【進階】若改為陣列公式:(輸入公式後要按 Ctrl+Shift+Enter 鍵)

儲存格G14:{=SUM(IF((MONTH(日期)=ROW(1:1))*(性別="女"),1,FALSE))}

儲存格H14:{=SUM(IF((MONTH(日期)=ROW(1:1))*(性別="女"),數值,FALSE))}

 

(4) 日期:星期日/星期一/星期二/星期三/星期四/星期五/星期六,求「人員」個數和其數值總和

儲存格G20:=SUMPRODUCT(--(WEEKDAY(日期,1)=ROW(1:1)))

儲存格H20:=SUMPRODUCT((WEEKDAY(日期,1)=ROW(1:1))*數值)

透過 WEEKDAY 函數取出日期陣列中的星期成為星期陣列,其中參數 1,對照星期日為 1,…,星期六為 7。

複製儲存格G20:H20,貼至儲存格G20:H26

【進階】若改為陣列公式:(輸入公式後要按 Ctrl+Shift+Enter 鍵)

儲存格G20:{=SUM(IF(--(WEEKDAY(日期,1)=ROW(1:1)),1,FALSE))}

儲存格H20:{=SUM(IF(--(WEEKDAY(日期,1)=ROW(1:1)),數值,FALSE))}

沒有留言:

張貼留言

檢視其他文章

好康東東