2010年3月9日 星期二

Excel-SUMPRODUCT函數應用

假設取得一個學生成績資料表(共300筆資料),其為學生夜間讀書方式和模擬考名次的記錄表(以下資料為模擬產生)。

如果想要轉換成以下的統計報表,該如何處理呢?

首先,在儲存格F2輸入:=(ROW(F2)-2)*50+1 & "-" & (ROW(F2)-1)*50

(如果想要改變成績間距,只要改變公式中的50即可)

再將儲存格F2複製到F3:F7。

在儲存格G2輸入:=SUMPRODUCT(($C$2:$C$301>=VALUE(LEFT($F2,SEARCH("-",$F2)-1)))*($C$2:$C$301<=VALUE(RIGHT($F2,LEN($F2)-SEARCH("-",$F2))))*($B$2:$B$301=G$1))

再將儲存格G2複製到G2:K7。

 

其中:

SEARCH("-",$F2)-1)) 表示:找尋 -符號在儲存格F2中的第幾個字元

LEFT($F2,SEARCH("-",$F2)-1)) 表示:取出 -符號左邊的字元

VALUE(LEFT($F2,SEARCH("-",$F2)-1))) 表示:將取得的文字轉換為數字

接著:

RIGHT($F2,LEN($F2)-SEARCH("-",$F2))) 表示:取出 -符號右邊的字元

VALUE(RIGHT($F2,LEN($F2)-SEARCH("-",$F2)))) 表示:將取得的文字轉換為數字

最後:

利用SUMPRODUCT函數

=SUMPRODUCT(模考名次>=成績區間)*(模考名次<=成績區間)*(夜間讀書方式=標題)

沒有留言:

張貼留言

檢視其他文章

好康東東