2011年8月2日 星期二

Excel-統計借用狀況(SUMPRODUCT+陣列公式)

在 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欄中的內容,以資料剖析或其他方式轉換成上上圖的樣式才開始計算。

沒有留言:

張貼留言

好康東東