【数式・表示形式】SUM関数の合計欄にゼロを表示したくない

Excel Application Tips Excel関数の実践
こんなときどうする?

SUM関数で合計欄を作成。数値を何も入力していないとき、合計欄にはゼロを表示せずに空欄にしておきたい。何か数値が入力されたら、合計値を表示したい。

▼実現イメージ

数値が入力されなければ、合計欄は空白
数値が入力されたら、合計欄に合計値を表示

この記事では、3つの方法を紹介しています。

  1. COUNTBLANK関数を使った数式
  2. ISBLANK関数を使った数式
  3. セルの書式設定で表示形式を設定

有効な解決方法は「COUNTBLANK関数を使った数式」。これ一択です。

他の方法には制約がありますので、なぜ実現できないかについて解説しています。

要件:SUM関数を使った合計欄にゼロを表示したくない

最初に要件を箇条書きでまとめておきます。

  1. 合計欄にはSUM関数を使う。
  2. 数値を何も入力していない場合、合計欄はゼロを表示せず空欄にしたい。
  3. 数値が何か入力されたら、合計欄には合計値を表示したい。

どんな場面で使えそうかというと。

  • 未入力のまっさらな請求書・領収書・採点表で、合計欄の「0円」や「0点」を表示しない。
  • 「入会金 0円」などゼロの表示が必要な場合は、合計欄にゼロを表示させたい。

SUM関数って何?については、こちらをご覧ください。

【関数リファレンス】SUM関数

それでは解決方法を紹介します!

【解決】COUNTBLANK関数とSUM関数を組み合わせる

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

【関数リファレンス】COUNTBLANK関数

たとえば、金額の入力欄が5つあるとします。空欄が5つであれば、まだ金額が入力されていないまっさらな状態とみなすことができます。

実際の数式にすると、こんな感じ。

数式の例

合計欄の数式 =IF(COUNTBLANK(B2:B6)=5,””,SUM(B2:B6))

COUNTBLANK関数で合計ゼロを表示しない
COUNTBLANK関数の実装例

数式の解説

COUNTBLANK関数で空白の数をカウントし、IF関数で処理を分岐させています。

  1. COUNTBLANK関数で、金額を入力する範囲に空白がいくつあるか数える。
  2. 入力欄の数と空白の数が一致する場合は、空白を表示する。
  3. 入力欄の数と空白の数が一致しない場合は、SUM関数を実行する。

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

VLOOKUP関数で数値を参照する場合も有効

COUNTBLANK関数が最有力である理由はこれです!

VLOOLUP関数を使用して、他の表から金額を参照している場合にも使えます。

COUNTBLANK関数 数式が含まれるセル
VLOOKUP関数で他の表から金額を参照
COUNTBLANK関数 数式を含む場合
VLOOKUP関数を使用していても未入力時には空白が表示される

さて・・・空白を扱う関数には、COUNTBLANK関数の他に、ISBLANK関数も存在します。

ISBLANK関数を使用するとどうなるでしょう?

【制約あり】ISBLANK関数とSUM関数を組み合わせる

ISBLANK関数は、セルに値や数式が入力されているか/入力されていないかを判定する関数です。

【関数リファレンス】ISBLANK関数

結論からいうと、COUNTBLANK関数に比べて使い勝手が良くありません。

  • ISBLANK関数は複数セルの範囲指定ができない。
  • そのため数式が煩雑になりがち。
  • VLOOKUP関数で金額を参照するような表には対応できない。

では、実際に数式を入力してみましょう。

数式の例

合計欄の数式 =IF(AND(ISBLANK(B2),ISBLANK(B3),ISBLANK(B4),ISBLANK(B5),ISBLANK(B6)),””,SUM(B2:B6))

ISBLANK関数で合計値を非表示
ISBLANK関数で合計値(ゼロ)を表示

数式の解説

ISBLANK関数で1つ1つの入力欄が空白かどうかを判定し、IF関数で処理を分岐させています。

  1. ISBLANK関数で、各セルが空白かどうかを判定する。
  2. 全て空白(TRUE)の場合は、空白を表示する。
  3. いずれか1つでも空白でない場合は、SUM関数を実行する。

ISBLANK関数で指定できるセル範囲は1つのみ!

そういう事情で、AND関数が登場。ISBLANK関数をセルの数だけ並べておき、AND関数でまとめて判定しています。

【関数リファレンス】AND関数

入力欄が5つ程度であっても、数式をつなげるのが面倒。今後もっと入力欄が増えたりすると、数式の修正作業も大変です。

VLOOKUP関数で数値を参照する場合は使えない

これがISBLANK関数では要件を満たせない理由です。

VLOOLUP関数で他の表から金額を参照すると、項目も金額は入力していないのに、合計欄にはゼロが表示されてしまいます。

ISBLANK関数 ゼロ非表示にならない例
VLOOKUP関数を含むセルをISBLANK関数で参照すると…。

原因は金額欄に入力されている数式です。

ISBLANK関数 対象に数式が含まれる場合
数式が入力されているので「空白でない」判定となる。

VLOOKUP関数で「空白」を表示させていますが、ISBLANK関数は「数式が入っているので、セルは空白でない」という判定をします。

したがって、VLOOKUP関数など数式を含んだセルを扱う場合、ISBLANK関数でこの要件を満たすことはできません。

【制約あり】セルの書式設定で表示形式を設定する場合

最後に紹介するのは、セルの書式設定でなんとかしてみようというケースです。

結論からいうと、ゼロを表示させないことだけが可能。要件の全てを満たすことができません。

「入会金 0円」のように、あえてゼロを表示したい場面では使えない。

では、実際にセルの書式設定で表示形式を設定してみましょう。

最適屋
最適屋

こんなやり方もあるんだなぁ…という理解で十分です。

セルの書式設定の表示形式を変更

手順は次のとおりです。

  1. セルを選択して、ショートカット[Ctrl+1]
  2. 「セルの書式設定」から[表示形式]→[ユーザー定義]を選択
  3. 種類(T)の欄に「G/標準;G/標準;」を入力
  4. [OK]をクリック
セルの書式設定で表示形式を変更
セルの書式設定で「表示形式」を変更

セルの書式設定ではあらゆるゼロが表示されない

未入力のとき、合計値のゼロは表示されません。

しかし、「入会金 0円」のような表示しておきたいゼロも表示されません。

セルの書式設定でゼロを非表示に
セルの書式設定でゼロを非表示に
「入会金 0円」のゼロも表示されない
「入会金 0円」のゼロも表示されない

要件を満たせないので、セルの書式設定では解決できませんね。

まとめ

SUM関数を使った合計欄にゼロを表示したくない場合は、COUNTBLANK関数を使いましょう

以上、ゼロを表示しないための方法に、COUNTBLANK関数・ISBLANK関数・セルの書式設定などを検討してみました。

Excel関数の実践Microsoft Excel 基本
\ お友だちにも教えてあげましょう/
プロフィール
最適屋

Excel VBAとAccessを独学で習得。2011年から現在まで株式会社アントレ(旧: 株式会社リクルート アントレユニット)と業務委託契約を結ぶ。Excel/Accessで様々な効果集計ツールや営業支援ツールを開発・提供。
過去、株式会社リクルートスタッフィング アウトソーシング契約社員時代には、 SV、業務フローの設計・改善に従事。
他、WordPressによるWeb制作やSEO対策も。

Follow Me!
川良最適化事務所
タイトルとURLをコピーしました