在 Excel 中計算範圍內包含錯誤值的儲存格數量

本教學將說明如何在 Excel 的指定區域中，計算包含錯誤值（例如 #N/A、#VALUE！ 或 #DIV/0! 等各類錯誤）的儲存格數量。

使用 SUMPRODUCT 函數計算包含錯誤值的儲存格數量
使用 SUM 函數計算包含錯誤值的儲存格數量

使用 SUMPRODUCT 函數計算包含錯誤值的儲存格數量

如下圖所示，若要計算 B3:B9 範圍中包含錯誤值的儲存格數量，可搭配使用 SUMPRODUCT 函數與 ISERR 函數。

通用公式

=SUMPRODUCT(--ISERROR(range))

引數

如何使用這個公式？

1. 在此範例中，請選取一個空白儲存格，例如 D3.

2. 在該儲存格中輸入下列公式，然後按下 ENTER 鍵即可取得結果。

=SUMPRODUCT(--ISERROR(B3:B9))

這些公式是如何運作的？

=SUMPRODUCT(--ISERROR(B3:B9))

• 1. ISERROR(B3:B9)：ISERROR 函數會在範圍 B3:B9 中的儲存格包含錯誤時傳回 TRUE，否則傳回 FALSE。最終傳回陣列 {FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}，表示該範圍中僅第二個與第五個儲存格含有錯誤值。
• 2. --{FALSE;TURE;FALSE;FALSE;TURE;FALSE;FALSE}：這兩個負號會將「TRUE」轉換為 1，並將「FALSE」轉換為 0，產生一個新陣列：{0;1;0;0;1;0;0}。
• 3. SUMPRODUCT{0;1;0;0;1;0;0}SUMPRODUCT 函數會將陣列中的所有數字相乘後加總，並傳回最終結果 2.

使用 SUM 函數計算包含錯誤值的儲存格數量

SUM 函數也能協助計算範圍內錯誤儲存格的數量，操作方式如下。

通用公式

{=SUM(--ISERROR(range))}

引數

注意：此公式必須以陣列公式的方式輸入。套用後，若公式前後出現大括號，即表示已成功建立陣列公式。

如何使用這個公式？

1. 在此範例中，請選取一個空白儲存格，例如 D3.

2. 在該儲存格中輸入下方公式，然後同時按下 Ctrl+Shift+Enter 鍵，立即取得結果！

=SUM(--ISERROR(B3:B9))

這些公式是如何運作的？

=SUM(--ISERROR(B3:B9))

• 1. ISERROR(B3:B9)：ISERROR 函數在範圍 B3:B9 中的儲存格包含錯誤時傳回 TRUE，否則傳回 FALSE。最終會傳回陣列 {FALSE;TURE;FALSE;FALSE;TURE;FALSE;FALSE}，表示該範圍中僅第二個與第五個儲存格包含錯誤值。
• 2. --{FALSE;TURE;FALSE;FALSE;TURE;FALSE;FALSE}：這兩個負號將「TRUE」轉換為 1，並將「FALSE」轉換為 0. 在此您會得到一個新陣列：{0;1;0;0;1;0;0}。
• 3. SUM{0;1;0;0;1;0;0}SUM 函數會對陣列中的所有數字進行加總，並傳回最終結果 2.

相關函數

相關公式