【Excel関数】未入力時に「0」や「#Value!」を表示させない方法

Windows PC

こんにちは、ふみです。みなさんは表計算ソフト「Microsoft Excel(エクセル)」で作成された書類を印刷に不必要な「0」や「#Value!」を手入力で削除してから印刷したりしていませんか?
私は次回提出する見込みがないような Excelファイルの書類については、余分なエラー値等を手入力で削除してから印刷しています。

しかし、次回も使う可能性のある Excelファイルの書類は毎回、手入力で削除するのは面倒です。
Excel で未入力時に「0」や「#Value!」を表示させないように Excel関数を設定する方法について紹介します。

IF関数を使って未入力時に「0」を非表示にする

会社名等、何度も同じ値を流用するときは「=(セルのアドレス)」を使って表示させることができます。
例えば、セルA1の値を表示させるときは、データを表示させたいセルに

=A1

と入力するとセルA1の値を表示させることができます。

しかし、この方法ではセルA1に何も入力されていないとき「0」が表示されてしまいます。
「0」が表示されていると不自然になる書類は多いです。

そこで、未入力時に「0」を表示させないようにするには「IF関数」を使用します。
セルA1にあるデータを IF関数を使って表示させるときは

=IF(A1="","",A1)

と入力するとセルA1にデータが入力されているときはセルA1の値が表示され、未入力時は何も表示されなくなります。

ちなみに、ここで使われている「”」はダブルクォーテーションといいます。
「”」を2つ並べると文字がない(空白)という意味になります。

IF関数を使った値を合計するときに「#Value!」を表示させない

空白処理のセル値を含めて合計する

Excelにおいて未入力の空白セルは空白の文字列、または数値の「0」と認識されます。
しかし、上記で紹介したIF関数で非表示にしたセルは空白の文字列と認識されますが、数値の「0」としては認識されません。

例えば、IF関数を使って空白処理をしたセルB1と、別のセルB2との合計をセルB3に表示させようします。
セルB3に

=B1+B2

と入力すると、セルB3にはセルB1とセルB2の合計値が表示されますが、セルB1が空白表示の時はセルB3に「#Value!」のエラー値が表示されてしまいます。
エラー値が表示される原因はIF関数で空白表示されているB1のセルが数値として認識されなくなるからです。

空白処理をしたセルを含めて合計するときは「SUM関数」を使用します。
「+」を使った計算で文字列を含めると「#Value!」が表示されますが、「SUM関数」は数値として認識できない文字列を無視して合計するので「#Value!」が表示されなくなります
セルB3に

=SUM(B1,B2)

と入力すると、セルB1が空白表示の時でもセルB3はエラー値が表示されなくなります。

合計のセルを空白処理にする

月次処理の書類では来月以降の未入力欄をすべて空白処理にしたい時があります。
来月以降の空白セルがすべて「0」が表示されている書類は見栄えが良くないものです。

こういった書類の場合、来月になるまでは入力されず、月次処理の時に必ず入力されるセルを押さえます。

例えば、我が社の賃金台帳の場合、月次処理で社員の労働日数を必ず入力するので、労働日数のセルをIF関数のターゲットにします。
労働日数の入力箇所がセルL14として、合計したい箇所範囲がセルL40~L45の場合は

=IF(L$14="","",SUM(L$40:L$45))

と入力すると、セルL40~L45の合計値が計算されますが、セルL14が未入力の時は非表示になります。

全ての合計欄をこのようにIF関数で設定すると、来月以降の空白セルがすべて「0」が表示されている見栄えの悪い書類になるのを回避することができます。

ターゲットのセルL14に絶対参照を表す「$」を使っているのは、ドラッグ&ドロップ等で数式をコピーした時、労働日数が記載されている14行目のターゲットがずれないようにする為です。
また、縦の合計L40~L45にも「$」が使われているのは別の行にコピーしたときに合計値がずれないようにする為です。
反対に列方向にはドラッグ&ドロップ等でコピーして使うので、Lの前に「$」を使わない相対参照にしています。

この絶対参照の設定は特に複数の人が編集する可能性のあるフォーマットにおいて、後の編集で誤りを防ぐのに非常に有効です。

セルの入力が2か所以上必要なときの空白処理

例えば、セルA4とセルB4の両方とも入力されていないとエラーが出てしまうケースがあったとします。
そんな時、IF関数で空白処理をすると、次のようになります。

=IF($A4="","",IF($B4="","","(数式)"))

これでセルA4とB4の両方が入力されないと「(数式)」が表示されなくなります。

しかし、空白処理の対象のセルの数が多くなると、IF関数とカッコの数が多くなって数式が分かりにくくなります。
そこで、セルの入力が2か所以上必要なときの空白処理はCOUNTA関数を使います

=IF(COUNTA($A4,$B4)<2,"","(数式)")

このように記述すると、数式をすっきりさせることができます。
COUNTA関数は空白でないセルを数える関数です。ただし、空白処理されたセルの「""」は空白でないセルとして数えるので、空白処理のセルを参照するときは注意が必要です。
「COUNTA($A4,$B4)<2」はセルA4とセルB4の両方とも入力されていない時、2より小さくなるのでセルが空白で表示されます。
COUNTA関数を使った空白処理はセルの入力が2か所以上必要なときに使います。
アドレスの範囲指定は「:」(コロン)と「,」(カンマ)のどちらでも指定できますが、空白処理でセルを指定するときは上記のように「,」でアドレスを区切ります。

ちなみに、こちらはおすすめできませんが、COUNTBLANK関数でも同じように機能します。記述は次のとおりです。

=IF(COUNTBLANK($A4:$B4)>=1,"","(数式)")

「COUNTABLANK($A4:$B4)>=1」はセルA4とセルB4の両方とも入力されていない時、セルの空白が1以上になるのでセルが空白で表示されます。
しかし、COUNTBLANK関数は範囲をカンマで区切ることができない為、A列とB列の間に列を挿入されたときに数式が崩れる可能性があります。また、2つのセルが離れている時に対応できない等、COUNTA関数に比べて汎用性(はんようせい)は低いですが、空白処理されたセルの「""」は空白としてカウントされる利点を持っています。

まとめ

  • セルの空白処理は IF関数を使う
  • 空白処理したセルの合計は SUM関数を使う
  • 合計セルを空白処理にするときは使用時に必ず入力されるセルを IF関数のターゲットにする
  • IF関数のターゲットのセルアドレスは必要に応じて絶対参照「$」を設定する
  • 入力が2か所以上必要なときの空白処理はCOUNTA関数を使う

ありがとうございました。

コメント

タイトルとURLをコピーしました