2010年11月28日 星期日

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

在 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。

公式的意義是:

=SUMPRODUCT(--(合於部門的儲存格陣列),--(合於職務的儲存格陣列),--(大於或等於區間起始值的儲存格陣列),--(小於或等於區間終止值的儲存格陣列))

=SUMPRODUCT(--(True/False陣列),--(True/False陣列),--(True/False陣列),--(True/False陣列))

=SUMPRODUCT(1/0陣列,1/0陣列,1/0陣列,1/0陣列)

公式中的INT(ROW(3:3)/3)*5-4和INT(ROW(3:3)/3)*5,可以在向下複製時自動配合年資區間,參考下圖的說明:

沒有留言:

張貼留言

檢視其他文章

好康東東