SUM関数で合計欄を作成。数値を何も入力していないとき、合計欄にはゼロを表示せずに空欄にしておきたい。何か数値が入力されたら、合計値を表示したい。
▼実現イメージ
この記事では、3つの方法を紹介しています。
- COUNTBLANK関数を使った数式
- ISBLANK関数を使った数式
- セルの書式設定で表示形式を設定
有効な解決方法は「COUNTBLANK関数を使った数式」。これ一択です。
他の方法には制約がありますので、なぜ実現できないかについて解説しています。
【PCSTORE】要件：SUM関数を使った合計欄にゼロを表示したくない
最初に要件を箇条書きでまとめておきます。
- 合計欄にはSUM関数を使う。
- 数値を何も入力していない場合、合計欄はゼロを表示せず空欄にしたい。
- 数値が何か入力されたら、合計欄には合計値を表示したい。
どんな場面で使えそうかというと。
- 未入力のまっさらな請求書・領収書・採点表で、合計欄の「0円」や「0点」を表示しない。
- 「入会金 0円」などゼロの表示が必要な場合は、合計欄にゼロを表示させたい。
SUM関数って何？については、こちらをご覧ください。
それでは解決方法を紹介します！
【解決】COUNTBLANK関数とSUM関数を組み合わせる
COUNTBLANK関数は、指定した範囲に含まれる空白セルを数える関数です。
たとえば、金額の入力欄が５つあるとします。空欄が5つであれば、まだ金額が入力されていないまっさらな状態とみなすことができます。
実際の数式にすると、こんな感じ。
数式の例
合計欄の数式 =IF(COUNTBLANK(B2:B6)=5,””,SUM(B2:B6))
数式の解説
COUNTBLANK関数で空白の数をカウントし、IF関数で処理を分岐させています。
- COUNTBLANK関数で、金額を入力する範囲に空白がいくつあるか数える。
- 入力欄の数と空白の数が一致する場合は、空白を表示する。
- 入力欄の数と空白の数が一致しない場合は、SUM関数を実行する。
例は入力欄が５行の集計表ですので、空白であるべき数を「５」としています。応用する場合は、入力欄の数に応じて変更してください。
VLOOKUP関数で数値を参照する場合も有効
COUNTBLANK関数が最有力である理由はこれです！
VLOOLUP関数を使用して、他の表から金額を参照している場合にも使えます。
さて・・・空白を扱う関数には、COUNTBLANK関数の他に、ISBLANK関数も存在します。
ISBLANK関数を使用するとどうなるでしょう？
【制約あり】ISBLANK関数とSUM関数を組み合わせる
ISBLANK関数は、セルに値や数式が入力されているか/入力されていないかを判定する関数です。
結論からいうと、COUNTBLANK関数に比べて使い勝手が良くありません。
- ISBLANK関数は複数セルの範囲指定ができない。
- そのため数式が煩雑になりがち。
- VLOOKUP関数で金額を参照するような表には対応できない。
では、実際に数式を入力してみましょう。
数式の例
合計欄の数式 =IF(AND(ISBLANK(B2),ISBLANK(B3),ISBLANK(B4),ISBLANK(B5),ISBLANK(B6)),””,SUM(B2:B6))
数式の解説
ISBLANK関数で１つ１つの入力欄が空白かどうかを判定し、IF関数で処理を分岐させています。
- ISBLANK関数で、各セルが空白かどうかを判定する。
- 全て空白（TRUE）の場合は、空白を表示する。
- いずれか１つでも空白でない場合は、SUM関数を実行する。
ISBLANK関数で指定できるセル範囲は1つのみ！
そういう事情で、AND関数が登場。ISBLANK関数をセルの数だけ並べておき、AND関数でまとめて判定しています。
入力欄が５つ程度であっても、数式をつなげるのが面倒。今後もっと入力欄が増えたりすると、数式の修正作業も大変です。
VLOOKUP関数で数値を参照する場合は使えない
これがISBLANK関数では要件を満たせない理由です。
VLOOLUP関数で他の表から金額を参照すると、項目も金額は入力していないのに、合計欄にはゼロが表示されてしまいます。
原因は金額欄に入力されている数式です。
VLOOKUP関数で「空白」を表示させていますが、ISBLANK関数は「数式が入っているので、セルは空白でない」という判定をします。
したがって、VLOOKUP関数など数式を含んだセルを扱う場合、ISBLANK関数でこの要件を満たすことはできません。
【制約あり】セルの書式設定で表示形式を設定する場合
最後に紹介するのは、セルの書式設定でなんとかしてみようというケースです。
結論からいうと、ゼロを表示させないことだけが可能。要件の全てを満たすことができません。
「入会金 0円」のように、あえてゼロを表示したい場面では使えない。
では、実際にセルの書式設定で表示形式を設定してみましょう。
こんなやり方もあるんだなぁ…という理解で十分です。
セルの書式設定の表示形式を変更
手順は次のとおりです。
- セルを選択して、ショートカット[Ctrl+1]
- 「セルの書式設定」から[表示形式]→[ユーザー定義]を選択
- 種類(T)の欄に「G/標準;G/標準;」を入力
- [OK]をクリック
セルの書式設定ではあらゆるゼロが表示されない
未入力のとき、合計値のゼロは表示されません。
しかし、「入会金 0円」のような表示しておきたいゼロも表示されません。
要件を満たせないので、セルの書式設定では解決できませんね。
まとめ
SUM関数を使った合計欄にゼロを表示したくない場合は、COUNTBLANK関数を使いましょう！
以上、ゼロを表示しないための方法に、COUNTBLANK関数・ISBLANK関数・セルの書式設定などを検討してみました。
