2010年11月28日 星期日

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

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

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

(一)使用COUNTIFS函數

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

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

儲存格M3:=COUNTIFS($C$2:$C$141,M$1,$D$2:$D$141,"<="&ROW(2:2)*"5")-SUM(M$2:M2)

複製儲存格M3到儲存格M3:O7。

公式中的ROW(2:2)在向下複製時會變為ROW(3:3)、ROW(4:4)…,會產生2,3,4…的數字。

(二)使用陣列公式

儲存格M2:{=SUM(IF($C$2:$C$141=M$1,IF($D$2:$D$141<=5,1,0)))}

陣列公式,輸入完成後要按 Ctrl+Alt+Enter 鍵。複製儲存格M2到儲存格M2:O2。

儲存格M3:

{=SUM(IF($C$2:$C$141=M$1,IF($D$2:$D$141<=ROW(2:2)*5,1,0)))-SUM(M$2:M2)}

陣列公式,輸入完成後要按 Ctrl+Alt+Enter 鍵。複製儲存格M3到儲存格M3:O7。

沒有留言:

張貼留言

檢視其他文章

好康東東