2015年5月5日 星期二

Excel-使用SUMPRODUCT取代多個COUNTIF運算(使用陣列)

有網友問到:如下圖中的 Excel 資料表,職稱欄位裡有多個不同的職稱,如何取出想要的職稱計算項目個數?

假設職稱的資料範圍在:儲存格B2:B123。例如:使用以下公式:

儲存格D2:=COUNTIF(B2:B123,"會員")+COUNTIF(B2:B123,"總教練")+
COUNTIF(B2:B123,"理事")+COUNTIF(B2:B123,"總幹事")+COUNTIF(B2:B123,
"第七八屆理事長")+COUNTIF(B2:B123,"副理事長")+COUNTIF(B2:B123,"監事")
+COUNTIF(B2:B123,"後備理事")+COUNTIF(B2:B123,"常務理事")+
COUNTIF(B2:B123,"常務監事")

看來,公式太長且修改不易,該如何處理會較方便維護呢?

是的,在設計公式時得考量到公式是否易讀和易維護,建議公式如下修正。

先選取儲存格B1:B123,按 Ctrl+shift+F3 鍵,勾選「頂端列」,定義名稱:職稱。

修改公式:

儲存格D2:=SUMPRODUCT(--(職稱={"會員","總教練","理事","總幹事",
"第七八屆理事長","副理事長","監事","後備理事","常務理事","常務監事"}))

其中 {"會員","總教練","理事","總幹事","第七八屆理事長","副理事長","監事",
"後備理事","常務理事","常務監事"} 是將想要計算的職稱以「常數陣列」型式來處理,爾後只要修改陣列的項目,即可改變要計算個數的項目。

公式中的「--」,是為了將 SUMPRODUCT 函數中「職稱={"會員","總教練",…」的判斷結果 TRUE/FALSE,藉由運算轉換為 1/0。

總結:藉由定義名稱和陣列的使用,可以簡化公式,使其易讀和易維護。

沒有留言:

張貼留言

檢視其他文章

好康東東