こんにちは。

本日は

Excelの「IF関数で０の場合空欄にする」を使用せず、ユーザー定義で0の場合は非表示にする方法

です。

以前ブログで『Excelで1,000,000円のように「カンマ区切り＋円」の表示する方法を記載しました。』

このブログの中に、0円だった場合は「円」ではなく「0円」と表示する方法も記載しています。

今回は、0+単位（0円）だった場合には、非表示とする方法です。

 

下表はとある会社の営業成績表です。
こちらの表を使用して、0＋単位（0件）だった場合非表示とする方法を説明致します。

この表にある0件を全て非表示にします。

 

0＋単位を非表示にする方法はこの方法

#,##0″件”;;

をユーザー定義で設定してあげます。

コピー＆ペーストで使用できるのでよかったら使ってみてください。
※使用する際はカンマなど不要でしたら自身で調整して使用して下さい。

 

Step１：ユーザー定義を設定したいセルを選択します。

わかりづらいかもしれませんが、数値が入っている部分全て選択しています。

Step２：表上で右クリック後、「セルの書式設定」を選択します。

 

Step３：ユーザー定義を「#,##0″件”;;」に書き換えます。

Step４：書き換えたら、OKボタンをクリックします。

これで完了です。

画面が切り替わりましたら、下図のように０件だったところが全て空欄になっているはずです。

このように見ますと、一発逆転タイプのCさん、コンスタントにお客様を獲得しているDさん、Eさんとわかりますね。

 

なぜ、「#,##0″件”;;」と設定すると０は表示されなくなるのか。

今回の注目すべき点は

「；」セミコロンです。

下記リンクはマイクロソフトのサポートページにある「Excel のセルの表示形式で [ユーザー定義] に使用できる書式記号について」です

https://support.microsoft.com/ja-jp/help/883199

このページの中に表示形式の書式として下記の記載があります。

ユーザー定義の表示形式は、一つの表示形式で 4 つの書式を作成することができます。
それぞれの書式は、「;」セミコロンで区切ります。左から 3 つまでの数値の書式と、文字列用に 4 つ目の書式を持つことができます。
また、以下のように対応します。

正の数の書式;負の数の書式;ゼロの書式;文字列の書式

セミコロンで正の数、負の数、０そして文字列の書式が分けて設定できるようになっています。

この数式を先程の「#,##0″件”;;」に当てはめてみます。

このように、ゼロの書式共に、書式に何も入っていない（空欄）となっているため、

0件となるものが全て非表示となります。

※この書式ですと負の数も非表示となります。

 

IF関数で非表示にするやり方もあるが…使い方に注意が必要

今回のポイントの一つですが、
ExcelではIF(A1=0,””,B1)というように、
IF関数を使って、「0だったら、空欄を表示する」
という方法がありますが、この方法には注意が必要です。

下図に合計値をもとに1週間当たりの平均を出す表を追加しました。
この平均の表ですが、数値として間違っている箇所が2か所あります。

正解は…

BとCの平均値が正しい値で算出されていません。

合計表のセルには、それぞれのセルに合計値を比較し、0だったら空欄にするという式を入れています。
Bの2週目を参考にしますと、下記のような式を入れています。

=IF(SUM(E10:E14)=0,””,SUM(E10:E14))

Bの2週目の合計件数は0件ですので、空欄となります。

表示上確かに空欄となるのですが、
平均値を出す際、間違った値となってしまいます。

これは“”(空欄)とする事で文字列扱いになり、
Averageの計算が正しくできなくなります。

このようにIF関数を使った空欄にする方法は、

間違った計算となる場合もありますので、注意が必要です。

ユーザー定義は数値として扱うよう（セルの値が0の時は非表示）に定義しているので、
正しく計算をしてくれます。

 

 

おわりに

いかがでしたでしょうか。

ユーザー設定はあまり使われないかもしれませんが、
細かいところに手が届くような機能ですので、是非活用してみてください。