【Excel Tips】SUM関数の結果にゼロを表示したくない〜数式で解決編〜

シェアする

Excel Application Tips

こんなときどうする?

続・SUM関数の結果にゼロを表示したくない

セルの書式設定によりゼロを表示しないことは可能でした。しかし、「入会金 0円」など、あえてゼロを表示したい場面では有効な方法ではありません。

こんなときどうする? SUM関数の結果にゼロを表示したくない 請求書・領収書・採点表など、まだ何も数値を入力していない集計表の合計欄には...

合計値のゼロを表示させたい場面

ゼロを表示したり/表示させなかったり、関数を組み合わせて解決できないでしょうか?

要件を整理する

解決に取り掛かる前に要件を整理しておきます。

「要件」とは簡単に言うと「○○したい」ということです。どのような条件下で「ゼロを表示したい/ゼロを表示したくない」のか、実現したいことを箇条書きにしておきましょう。

  1. 項目を何も入力していない場合、合計欄は空欄にしたい。
  2. 項目を合計した結果がゼロになる場合は、合計欄にはゼロを表示したい。

解決方法の検討

「何も入力していない」状態、つまり空白を扱う関数といえばISBLANK関数とCOUNTBLANK関数があります。それぞれの関数をどのように使うべきでしょう。

ISBLANK関数を組み合わせる

ISBLANK関数は、指定したセルに何か入力されているか/何も入力されていないかを判定する関数です。

画像の例ではセルB2〜セルB6の範囲が空白かどうかを判定して、すべてのセルが空白の場合は空白を表示し、いずれか1つでも空白でない場合はSUM関数の合計を表示しています。

ISBLANK関数で未入力はゼロを表示しない

▼数式
=IF(AND(ISBLANK(B2),ISBLANK(B3),ISBLANK(B4),ISBLANK(B5),ISBLANK(B6)),””,SUM(B2:B6))

▼数式の解説
IF関数で2つの処理に分岐させています。
 └指定した範囲が空白である場合→空白「””」を表示
 └指定した範囲が空白でない場合→「SUM(B2:B6)」の結果を表示。

空白の判定にはISBLANK関数を使用します。ここで注意したいことが1つ。

ISBLANK関数に指定できるセルは1つのみ。

したがって、AND関数を使うことで複数のセル範囲を判定しています。

AND(ISBLANK(B2),ISBLANK(B3),ISBLANK(B4),ISBLANK(B5),ISBLANK(B6))

こうすることで、どのセルにも何も入力されていなければ、AND関数はTRUEを返します。IF関数により「””」が実行されます。

いずれかのセルに数値が入力された場合、AND関数はFALSEを返します。IF関数により「SUM(B2:B6)」が実行されます。

ISBLANK関数で合計ゼロを表示

関数リファレンス

関数の機能 ISBLANK関数は、指定した単一セルが空白であるか/空白でないかを判定します。 関数の使い方 書式 =ISBLAN...

COUNTBLANK関数を組み合わせる

COUNTBLANK関数は、指定した範囲に含まれた空白セルを数える関数です。

画像の例ではセルB2〜セルB6の空白セルを数えて、空白であるべき数と一致する場合は空白を表示し、空白であるべき数と一致しない場合にSUM関数の合計を表示しています。

COUNTBLANK関数で合計ゼロを表示しない

▼数式
=IF(COUNTBLANK(B2:B6)=5,””,SUM(B2:B6))

▼数式の解説
IF関数で2つの処理に分岐させています。
 └指定した範囲の空白セルが5つである場合→空白を表示。
 └指定した範囲の空白セルが5つでない場合→SUM関数の結果を表示。

COUNTBLANK(B2:B6)=5

例は入力欄が5行の集計表ですので、空白であるべき数を「5」としています。応用する場合は、表の大きさに応じて変える必要があります。

関数リファレンス

関数の機能 COUNTBLANK関数は、指定した範囲に含まれる空白セルを数えます。 関数の使い方 書式 =COUNTBLANK(...

ISBLANK関数とCOUNTBLANK関数の違いを検証

ISBLANK関数とCOUNTBLANK関数の実装例をあげてみました。数式の内容は異なりますが、どちらも同じ用途には使えそうです。

しかし…

汎用性が高いのはCOUNTBLANK関数です

ISBLANK関数が使用できない例

ISBLANK関数を使用した次の例を見てみましょう。

ISBLANK関数 ゼロ非表示にならない例

表には何も入力されていないにも関わらず、合計欄にはゼロが表示されています。

原因は何かというと…

ISBLANK関数 対象に数式が含まれる場合

セルB2は空白セルに見えますが、実は数式が含まれています。

数式の詳細な説明は省きますが、VLOOKUP関数により、項目名を入力すると別の表から金額が自動入力され、項目名を入力していない場合は空白が表示される仕組みです。

この場合、ISBLANK関数は「数式が入っているので、セルは空白でない」という判定をします。したがって、合計欄にはSUM関数の結果が表示されます。

汎用性が高いのはCOUNTBLANK関数

それでは、COUNTBLANK関数を使用した例を見てみましょう。

COUNTBLANK関数 数式を含む場合

同じ表の合計欄の数式のみ、COUNTBLANK関数を使用したものに変更しました。

表に何も入力されていない状態で、合計欄にゼロは表示されません。

ちなみに、項目名を入力すると…

COUNTBLANK関数 数式が含まれる場合の合計

合計欄にはゼロが表示されます。

ISBLANK関数が「有るか/無しか」のガチガチの判定をするのに対し、COUNTBLANK関数の場合は、セルに数式が入っていればその結果を評価した上で、空白としてカウントします。

以上、ゼロを表示しないために、ISBLANK関数とCOUNTBLANK関数の使い方を検討しました。

シェアする

フォローする