2014年6月25日 星期三

Excel-計算平均時不包含空白儲存格(SUMPRODUCT,ISBLANK)

有網友問到如下圖左的 Excel 資料清單中,如果要將編號為奇數/偶數的項目,分別取出計算其平均,該如何處理?其中有部分儲存格的內容為空白。

計算平均時,如果儲存格的內容為空白,理應不併入計算。而 AVERAGE 函數,也是會將儲存格為空白儲存格者不列入平均。

先選取儲存格A1:B20,按一下 Ctrl+Shfit+F3 鍵,定義名稱:編號、數值。

【錯誤結果】

儲存格E2:=SUMPRODUCT((MOD(編號,2)=1)*數值)/SUMPRODUCT(--(MOD(編號,2)=1))

MOD(編號,2)=1:判斷編號除以 2 的餘數是否為 1 (該數為奇數),得到一個 TRUE/FALSE 的陣列。

SUMPRODUCT((MOD(編號,2)=1)*數值):計算編號為奇數者的數值總和。

SUMPRODUCT(--(MOD(編號,2)=1)):藉由「--」運算,將計算結果為 TRUE/FALSE 的陣列轉換為 1/0 的陣列。

儲存格E3:=SUMPRODUCT((MOD(編號,2)=0)*數值)/SUMPRODUCT(--(MOD(編號,2)=0))

MOD(編號,2)=0:判斷編號除以 2 的餘數是否為 0 (該數為偶數),得到一個 TRUE/FALSE 的陣列。

但是以上的公式,會將儲存格B4和儲存格B13這二個空白儲存格的內容視為 0,在計算平均值時會產生錯誤。

 

【正確結果】

為了將空白儲存格不在計算平均時併入計算,將公式稍做修改:

儲存格E4:=SUMPRODUCT((MOD(編號,2)=1)*數值)/SUMPRODUCT((NOT(ISBLANK(數值)))*(MOD(編號,2)=1))

NOT(ISBLANK(數值)):透過 ISBLANK 函數來判斷數值陣列中是否為空白儲存格,得到一個 TRUE/FALSE 陣列,再藉由 NOT 函數將結果轉換為 FALSE/TRUE 陣列。

儲存格E5:=SUMPRODUCT((MOD(編號,2)=0)*數值)/SUMPRODUCT((NOT(ISBLANK(數值)))*(MOD(編號,2)=0))

沒有留言:

張貼留言

檢視其他文章

好康東東