続・SUM関数の結果にゼロを表示したくない
セルの書式設定によって、SUM関数で算出したゼロを表示させないことは可能でした。しかし「入会金 0円」のように、あえてゼロを表示したい場面では有効な方法ではありません。
今回は、ゼロを表示したり/表示させなかったり、エクセルの関数を組み合わせて制御してみましょう。
解決方法の検討
どのような条件下で「ゼロを表示したい/ゼロを表示したくない」のか、実現したいことを箇条書きにしておきましょう。いわゆる、要件定義です。
- 項目を何も入力していない場合、合計欄は空欄にしたい。
- 項目を合計した結果がゼロになる場合は、合計欄にはゼロを表示したい。
「何も入力していない」状態、つまり空白を扱う関数といえばISBLANK関数とCOUNTBLANK関数があります。それぞれの関数をどのように使うべきでしょう。
ISBLANK関数を組み合わせる
ISBLANK関数は、セルに値や数式が入力されているか/入力されていないかを判定する関数です。
画像の例ではセルB2〜セルB6の範囲が空白かどうかを判定して、すべてのセルが空白の場合は空白を表示し、いずれか1つでも空白でない場合はSUM関数の合計を表示しています。
▼数式
=IF(AND(ISBLANK(B2),ISBLANK(B3),ISBLANK(B4),ISBLANK(B5),ISBLANK(B6)),””,SUM(B2:B6))
▼数式の解説
IF関数で2つの処理に分岐させています。
└指定した範囲が空白である場合→空白「””」を表示
└指定した範囲が空白でない場合→「SUM(B2:B6)」の結果を表示。
空白の判定にはISBLANK関数を使用します。ISBLANK関数で指定できるセル範囲は1つのみなので、AND関数を組み合わせて複数のセル範囲を判定しています。
AND(ISBLANK(B2),ISBLANK(B3),ISBLANK(B4),ISBLANK(B5),ISBLANK(B6))
いずれのセルにも値や数式が入力されていなければ、AND関数はTRUEを返し、IF関数により「””」が実行されます。合計欄は空白になります。
いずれかのセルに値や数値が入力された場合、AND関数はFALSEを返し、IF関数により「SUM(B2:B6)」が実行されます。合計欄にはSUM関数で算出された数値が表示されます。
関数リファレンス
COUNTBLANK関数を組み合わせる
COUNTBLANK関数は、指定した範囲に含まれた空白セルを数える関数です。
画像の例ではセルB2〜セルB6の空白セルを数えて、空白であるべき数と一致する場合は空白を表示し、空白であるべき数と一致しない場合にSUM関数の合計を表示しています。
▼数式
=IF(COUNTBLANK(B2:B6)=5,””,SUM(B2:B6))
▼数式の解説
IF関数で2つの処理に分岐させています。
└指定した範囲の空白セルが5つである場合→空白を表示。
└指定した範囲の空白セルが5つでない場合→SUM関数の結果を表示。
COUNTBLANK(B2:B6)=5
例は入力欄が5行の集計表ですので、空白であるべき数を「5」としています。応用する場合は、表の大きさに応じて変える必要があります。
関数リファレンス
ISBLANK関数とCOUNTBLANK関数の違いを検証
ISBLANK関数とCOUNTBLANK関数の実装例をあげてみました。数式の内容は異なりますが、どちらも同じ用途には使えそうです。
しかし…
ISBLANK関数が使用できない例
ISBLANK関数を使用した次の例を見てみましょう。
表には何も入力されていないにも関わらず、合計欄にはゼロが表示されています。
原因は何かというと…
セルB2は空白セルに見えますが、実は数式が含まれています。
数式の詳細な説明は省きますが、VLOOKUP関数により、項目名を入力すると別の表から金額が自動入力され、項目名を入力していない場合は空白が表示される仕組みです。
この場合、ISBLANK関数は「数式が入っているので、セルは空白でない」という判定をします。したがって、合計欄にはSUM関数の結果が表示されます。
汎用性が高いのはCOUNTBLANK関数
それでは、COUNTBLANK関数を使用した例を見てみましょう。
同じ表の合計欄の数式のみ、COUNTBLANK関数を使用したものに変更しました。
表に何も入力されていない状態で、合計欄にゼロは表示されません。
ちなみに、項目名を入力すると…
合計欄にはゼロが表示されます。
ISBLANK関数の場合は、セルに値や数式が入力されているかどうかの判定をするのに対し、COUNTBLANK関数の場合は、セルに数式が入っていればその結果を評価した上で、空白をカウントします。
以上、ゼロを表示しないために、ISBLANK関数とCOUNTBLANK関数の使い方を検討しました。
