小ネタ・便利技

数式の計算結果で0を表示しない方法

投稿日:2016.05.06 更新日:

VLOOKUP関数やINDEX関数、あるいは「=A1」などセルを参照する数式で、その計算結果が空白セルを参照する場合、Excel は空白(“”)ではなく0(ゼロ)を返します。今回はこの0を非表示にする方法をご紹介します。

VLOOKUP関数が0を返すケース

あるキーでテーブルなどを検索するとき、お手軽でよく使われるのがVLOOKUP関数かと思います。このVLOOKUP関数はとっても便利なのですが、検索結果のセルが空白だったときは空白(“”)ではなく 0 (ゼロ)を返します。

以下の例では「《Players Table》($A$3:$C$6)」からA列の[Name]をキーにC列の[No]を取得する例です。

hide-returned-zero_1

値の取得にVLOOKUP関数を使用していますが、4行目の「K.Bryant」さんの[No]には値が設定されていないため、0 が返されてしまいます(セルE4)。

一方、6行目の「R.Westbrook」さんの[Name]をキーにしたVLOOKUP関数も0を返していますが、「R.Westbrook」さんの[No]は「0」ですのでこちらは正しい結果が返されています(セルE6)。

一律0を非表示にする方法もあるけど…

Excel のオプションやセルの書式設定で0を非表示にすることもできますが、「R.Westbrook」さんの[No]は「0」ですので、一律0を非表示にしてしまうと値が入力されてなくて0が返されてるものだけでなく、実際の検索結果が0のものも非表示になってしまうので不都合が生じますよね。更には検索元の表にある0も非表示になってしまい、これでは何が何だかわからなくなってしまいます。

hide-returned-zero_2

hide-returned-zero_3

数式をちょこっと変更して解決!

そこで以下のように数式を変更するわけですよ。

=VLOOKUP(A4,$A$3:$C$6,3,0)

↑これを↓こうする。

=VLOOKUP(A4,$A$3:$C$6,3,0)&””

hide-returned-zero_4

変わったのは末尾の「&””」の部分です。こんだけです。

これにより数式の結果を「文字列」として Excel に認識させることでき、0が返されなくなるんです。

実際の検索結果が0となる「R.Westbrook」さんの[No]もちゃんと0として取得できています(セルE6)。

検索結果を文字列として認識させられればいいわけで、例えばTRIM関数やCONCATENATE関数のような文字列を返す関数の引数として指定してあげてもいいんです、↓こんな感じに。

=CONCATENATE(VLOOKUP(A4,$A$3:$C$6,3,0))

ただし、上述のとおり、実際の検索結果が”数値”の場合でも”文字列”として返されます

つまり、SUM関数や算術演算子などを用いての計算には使用できませんのでご注意ください。

#まぁ計算で使用するなら
#値が入力されてないセルが0を返しても
#さほど問題ないよねっていう(^^;

まとめ。

今回はVLOOKUP関数を例に説明しましたが、このテクニックはVLOOKUP関数だけでなくセル参照するすべての数式に当てはまります。

なので、極端な話セルA1に「=B1」と入力してもセルB1が空白であれば0が返されます。

その場合も今回紹介したテクニックで解消することが可能です。

ご不明点等ございましたらお気軽にお問い合わせくださいまし:)

HAYs

このエントリーはこちらのサイトを参考にさせて頂きました。ありがとうございます:)

Office TANAKA | VLOOKUPで0を返さない

-小ネタ・便利技
-, ,


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

VBAで一番省略されているキーワード

VBA にはプロパティやステートメントなど様々なキーワードがあります。そして中には記述を省略できるものも少なくありません。 では一番省略されているキーワードっていったいなんなのでしょうか?

alphabet

文字列Aに含まれる文字列Bの数をカウントする方法

以前(というかかなり昔)、とある方から 『例えば「ABCABCBC」っていう文字列に「A」がいくつあるか数えるエクセルの関数ってある?』 って問い合わせがあったんですが、皆様にもその方法を連携しますね …

caluculation_process

Excelで数式の計算過程を表示する方法

Excelで作業しているとやたら長ったらしい数式ってたまにありますよね。 『この数式は関数がいっぱい書いてあるけど何でこの値を返すんだろう?』 とか 『あれ?数式がエラーになっちゃったけど原因はどこだ …

book

大量シートの中から目的のシートを簡単に選択する方法

ブック内にシートがたくさんある時って、目的のシートを探して選択するのって面倒じゃないですか?面倒ですよね?面倒なんですか?そうですか。そんなあなたに目的のシートを簡単に選択する方法をご紹介します。  

【Excel】ユーザー設定リストを活用してオートフィルと並べ替えをカスタマイズ

Excel には「ユーザー設定リスト」という順序に関する情報を保存している機能があります。順序に関する情報なので、使いこなせばオートフィルや並べ替えの機能をより使いやすくすることも可能です。今回はこの …