2010年11月21日 星期日

Excel-計算指定名次內的小計

在 Excel 的工作表中有一份如下圖的數據,如果想要計算指定名次內的小計,該如何處理?

(一)計算累計的總和和平均

你可以使用以下三種方式來計算總和:

1.儲存格G2:=SUMIF($C$2:$C$26,"<="&E2*5,$B$2:$B$26)

2.儲存格G2:=SUMPRODUCT(--($C$2:$C$26<=E2*5)*$B$2:$B$26)

公式中的「--」運算,可以將 $C$2:$C$26<=E2*5 的結果 True/False 轉換為 1/0。

3.儲存格G2:{=SUM(IF($C$2:$C$26<=E2*5,$B$2:$B$26))}  [這是陣列公式]

計算平均:

儲存格H2:=AVERAGEIF($C$2:$C$26,"<="&E2*5,$B$2:$B$26)

複製儲存格G2:H2到儲存格G2:H5。

為什麼不直接以總和除5計算平均,是因為可能有同名次的狀況。

(二)計算區間的總和和平均

你可以使用以下三種方式來計算總和:

1.儲存格G9:=SUMIFS($B$2:$B$26,$C$2:$C$26,"<="&E9*5,$C$2:$C$26,">"&(E9-1)*5)

2.儲存格G9:=SUMPRODUCT(--($C$2:$C$26<=E9*5)*($C$2:$C$26>(E9-1)*5)*$B$2:$B$26)

3.儲存格G9:{=SUM(IF($C$2:$C$26<=E9*5,IF($C$2:$C$26>(E9-1)*5,$B$2:$B$26)))}

[這是陣列公式]

計算平均:

儲存格H9:=AVERAGEIFS($B$2:$B$26,$C$2:$C$26,"<="&E9*5,$C$2:$C$26,">"&(E9-1)*5)

複製儲存格G9:H9到儲存格G13:H13。

關於SUMIF、SUMIFS、AVERAGEIF、AVERAGEIFS、SUMPRODUCT函數的說明,請參考微軟網站:

SUMIF:http://office.microsoft.com/zh-tw/excel-help/HP010342932.aspx

SUMIFS:http://office.microsoft.com/zh-tw/excel-help/HA010342933.aspx

AVERAGEIF:http://office.microsoft.com/zh-tw/excel-help/HA010342193.aspx

AVERAGEIFS:http://office.microsoft.com/zh-tw/excel-help/HA010342194.aspx

SUMPRODUCT:http://office.microsoft.com/zh-tw/excel-help/HP010342802.aspx

沒有留言:

張貼留言

好康東東