學不完．教不停．用不盡

在 Excel 中取得一個球類的借用狀況報表(如下圖左)，試著來統計分析借用狀況(如下圖右)。

先選儲存格A1:D25，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義「日期、班級、球類、數量」等名稱。

儲存格G2：=SUMPRODUCT(--(球類=G1),數量)

其中「--」是為將 True/False 陣列轉換為 1/0 陣列。

複製儲存格G2到儲存格G2:I2。

儲存格G5：=SUMPRODUCT(--(班級=$F5),--(球類=G$4),數量)

複製儲存格G5到儲存格G5:I16。

如果你取得像下圖左的報表，試著來練習以公式計算出相同的統計分析報表(如下圖右)。

先選儲存格L1:L25，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義「借用狀況」名稱。

儲存格O2：{=SUM(IF(MID(借用狀況,7,2)=O$1,VALUE(SUBSTITUTE(MID(借用狀況,9,LEN(借用狀況)-8),"顆","")),0))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

觀察L欄中的資料，在前8個字的格式是一樣的，只有在第8個字以後才有差異。

MID(借用狀況,9,LEN(借用狀況)-8)：算出借了「幾顆」球。

SUBSTITUTE(MID(借用狀況,9,LEN(借用狀況)-8),"顆","")：「幾顆」球的「顆」換成空白。

VALUE(SUBSTITUTE(MID(借用狀況,9,LEN(借用狀況)-8),"顆",""))：將顆數轉成數值。

MID(借用狀況,7,2)=O$1：判斷是借用狀況的「X球」。

如果符合條件者的陣列才將其加總。

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

同理：

儲存格O5：{=SUM(IF(VALUE(MID(借用狀況,1,3))=$N5,IF(MID(借用狀況,7,2)=O$4,VALUE(SUBSTITUTE(MID(借用狀況,9,LEN(借用狀況)-8),"顆","")),0)),0)}

在公式中使用兩個條件的判斷，這兩個條件相當於使用 AND 邏輯運算。即兩者都符合者，才會將數量加總。

複製儲存格O5到儲存格O5:Q16。

註：以上的公式是純練習用，一般你會將 L欄中的內容，以資料剖析或其他方式轉換成上上圖的樣式才開始計算。