2010年11月25日 星期四

Excel-用公式取代樞紐分析(一)

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

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

(一)使用SUMIF函式

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

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

(二)使用SUMPRODUCT函式

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

使用SUMPRODUCT函式:

SUMPRODUCT(--(合於某一學校的陣列),--(合於某一年級的陣列),加總範圍)

=SUMPRODUCT(--(True/False陣列),--(True/False陣列),加總範圍)

=SUMPRODUCT(1/0陣列,1/0陣列,加總範圍)

=總和結果

(三)使用IF+SUM+陣列公式

儲存格F3:{=SUM(IF($A$2:$A$395=$E3,IF($B$2:$B$395=F$2,$C$2:$C$395,0)))}

此為陣列公式,輸入完成時按 Ctrl+Alt+Enter 鍵。

{=SUM(IF(合於某一學校的陣列,IF(合於某一年級的陣列,加總範圍,0)))}

其中和樞紐分析不一樣的地方是,如果完全沒有任一筆資料的部分,在樞紐分析表中會以空白顯示,而利用以上三種公式的運算結果會以0表示。

沒有留言:

張貼留言

好康東東