學不完．教不停．用不盡

當你在 Excel 中取得如下圖的工作表(資料範圍為儲存格A1:M79)，分別列出各年級得分的人數，如何將各年級7-10分和1-6分的人數各為多少，分別計算出來？

(表中人數為亂數產生，無特定意義。)

假設要產生如下圖的統計表，分別輸入以下公式：

儲存格P3：{=SUM(IF($A$2:$A$79=O3,IF($B$2:$B$79=P$1,$C$2:$F$79)))}

(意義：=SUM(IF(符合學校名稱,IF(符合年級,7-10分儲存格範圍)))

在 Excel 的工作表中有一個各個分數區間(2~10)的統計表，如果想要以勾選方式求得各等第的人數和比例應該如何操作？使用SUMPRODUCT函數很容易可以做到。

儲存格K3：=SUMPRODUCT((B3:J3="V")*$B$1:$J$1)

儲存格L3：=K3/$K$1

複製儲存格K3:L3到儲存格K3:L7。

其中SUMPRODUCT函數中的參數：

(B3:J3="V")*$B$1:$J$1

在 Excel 的工作表中，數值通常包含整數和小數部分，如何利用數值格式設定來讓數字之間相互對齊呢？

在數值格式設定中可以使用「.、#、0和 ?」 來設定，參考以下說明：

在 Excel 中取得一個資料表(如下圖左)(資料範圍：儲存格A2:D141)，以樞紐分析方式得到各種年資的各種職務人數分析(如下圖右)。如果想要以年資區間和區別各處室來分析可能不太容易，如何以公式完成這個要求呢？

參考下圖左，年資以每5年一個區間，依不同處室和不同職務來計算符合的人數。這次要以SUMPRODUCT函數來運算。

儲存格N2：=SUMPRODUCT(--($B$2:$B$141=$M2),--($C$2:$C$141=N$1),--($D$2:$D$141>=INT(ROW(3:3)/3)*5-4),--($D$2:$D$141<=INT(ROW(3:3)/3)*5))

將儲存格N2複製到儲存格N2:P19。

公式的意義是：

在 Excel 中取得一個資料表(如下圖左)(資料範圍：儲存格A2:D141)，以樞紐分析方式得到各種年資的各種職務人數分析(如下圖右)。如果想要以年資區間來分析可能不太容易，如何以公式完成這個要求呢？

參考下圖左，年資以每5年一個區間，依不同職務來計算符合的人數。

(一)使用COUNTIFS函數

儲存格M2：=COUNTIFS($C$2:$C$141,M$1,$D$2:$D$141,"<=5")

複製儲存格M2到儲存格M2:O2。

在 Excel 中取得一個資料表(如下圖左)(資料範圍：儲存格A2:C141)，以樞紐分析方式得到各年級的各種請假時數分析(如下圖右)。如果想要以月份來分析可能不太容易，如何以公式完成這個要求呢？

現在要以公式來建立分析表(如下圖左)，依年級、月份和假別分別來分析。你可以使用SUMPRODUCT函數、SUMIFS函數和陣列公式等三種方式來做，以下以SUMPRODUCT為例：

儲存格M3：=SUMPRODUCT(--(MONTH($A$2:$A$141)=ROW(1:1)),--($B$2:$B$141=$M$3),--($D$2:$D$141=O$2),$E$2:$E$141)

將儲存格M3複製到儲存格O3:Q8。

儲存格M9：=SUMPRODUCT(--(MONTH($A$2:$A$141)=ROW(1:1)),--($B$2:$B$141=$M$9),--($D$2:$D$141=O$2),$E$2:$E$141)

在 Excel 中取得一個資料表(如下圖左)(資料範圍：儲存格A2:C395)，以樞紐分析方式得到各年級的數量分析(如下圖右)。如果你想以公式完成相同動作該如何處理？

你可以透過以下三種方式完成和樞紐分析表相同的工作：

(一)使用SUMIF函式

儲存格F3：=SUMIFS($C$2:$C$395,$A$2:$A$395,$E3,$B$2:$B$395,F$2)

使用SUMIF函式：SUMIFS(加總範圍，學校範圍，指定某一學校，年級範圍，指定某一年級)

(二)使用SUMPRODUCT函式

在 Excel 中執行樞紐分析時，對於產生的樞紐分析表，如果其中有儲存格未填入任何資料，則在計算平均時將會產生錯誤。

例如下圖左(資料範圍：儲存格A1:A395)，有些儲存格沒有任何資料，所以：

得到第一個平均為100+200+300+400/4=250(如下圖中)。

但是如果要將空白存格列入平均(如下圖右)，則可以輸入公式：

儲存格I2：{=AVERAGE(IF($A$2:$A$395=H2,$C$2:$C$395))}

陣列公式，輸入完成後要按一下 Ctrl+Alt+Enter 鍵。複製儲存格I2，在儲存格I2:I27貼上。

在 Excel 中取得如下圖左的資料表，根據這個資料表，找出各種可能的運算(個數、總和、平均)。

(一) 計算個數

在 Excel 中常會用到各種亂數，RAND 和 RANDBETWEEN 兩個函數可以產生各種亂數值。但是如果想要產生某個範圍內的亂數，則需進一步改造公式。試著練習以下各種公式：