2011年6月29日 星期三

Excel-跨工作表使用COUNTIF

在 Excel 中如果要計算以下各月的業績中超過40000的次數,只要使用COUNTIF函數即可輕易達到。

儲存格L2:=COUNTIF(F2:K2,$L$1)

複製儲存格L2,往下各列貼即可。

但是,如果1月到6月是分別置於命名為1月、2月、…、6月的工作表中,當使用COUNTIF函數會發生錯誤:

儲存格B2:=COUNTIF('1月:6月'!B2,$B$1)

試圖要將每個月的工作表(1月到6月)中的儲存格B2取出來計算,因為在COUNTIF中無法使用跨工作表的表示法,所以顯示錯誤訊息。

所以要改用不同的作法,例如:

儲存格B2:=SUMPRODUCT(COUNTIF(INDIRECT(ROW($1:$6)&"月!B"&ROW(2:2)),$B$1))

複製儲存格B2,往下各列貼上。

SUMPRODUCT函數可以將ROW($1:$6)&"月!B"&ROW(2:2)公式在往下各列貼上時,產生以下的陣列:

1月!B2、1月!B3、1月!B4、1月!B5、1月!B6、1月!B7、1月!B8、1月!B9、1月!B10、1月!B11

COUNTIF用以計算符合條件的格式。

因此,如果要計算符合條件的總和,可以將公式改為:

儲存格C2:=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$6)&"月!B"&ROW(2:2)),$C$1))

沒有留言:

張貼留言

好康東東