ルックアップ系の関数（VLOOKUP、XLOOKUPなど）や関数FILTERは、「空白セル」のデータを取得したときに「0」（ゼロ）が表示される仕様になっている。とはいえ、この0を消去したい場合もあるだろう。そこで今回は、空白を示す0を表示しないようにするテクニックを紹介していこう。色々な方法があるので、好きなものを選択するとよい。

  • ルックアップなどで取得した「空白データ」を0と表示しない方法

「空白セル」は0（ゼロ）と表示される・・・

VLOOKUPやXLOOKUPといったルックアップ系の関数で「空白セル」のデータを取得すると、そのデータは数値の0（ゼロ）と表示される仕様になっている。これは、関数FILTERで「空白セル」を取得した場合も同様だ。

まずは、「空白データ」が0と表示される例を紹介しておこう。以下の図は、関数XLOOKUPで「タニモト ルナ」さんのデータを取得した例だ。取得元の表では「住所2」のデータが「空白セル」になっているため、このデータは数値の0として取得されてしまう。

  • XLOOKUPで取得した「空白データ」が0（ゼロ）と表示された例

関数FILTERでデータを取得した場合も同様だ。この場合も「空白セル」が数値の0として取得される仕様になっている。

  • FILTERで取得した「空白データ」が0（ゼロ）と表示された例

とはいえ、「0を表示しないで空白のままにしておきたい」というケースもあるだろう。そこで、0を表示しないテクニックを5種類ほど紹介していこう。

（1）手軽な反面、少し問題が残る「&""」の追加

最も簡単なのは、関数XLOOKUPなどの後に「&""」を追加する方法だ。「&""」の記述は、「取得したデータの後に空文字（""）を追加する」という処理になる。

  • 関数の後に「&""」を追加

このように処理すると、データが「文字列データ」として扱われるようになるため、「空白セル」を0ではなく、空文字として取得することが可能となる。

  • データの取得結果（生年月日の表示にトラブルあり）

ただし、取得したデータがすべて「文字列データ」に変換されてしまうことに注意しなければならない。その結果、「日付や時刻が正しく表示されない・・・」などのトラブルが発生する。

このような現象が生じるのは、数値データをはじめ、日時を示すシリアル値も「文字列データに変換される」ことが原因だ。このため、取得したデータをもとに計算を行うことも不可能になってしまう。

よって、このテクニックが使えるのは「取得するデータが文字列しかない場合」に限定される。手軽である反面、使える場面が制限される、と覚えておく必要がある。

（2）関数IFを使った正攻法

続いて紹介するテクニックは、「関数IF」を使った正攻法ともいえる手法だ。その仕組みは簡単で、「取得されたデータ0であった場合は、空文字に置き換えて出力する」という処理になる。

  • 関数IFで0を空文字に変更

念のため、関数IFの記述を補足しておこう。上図の例では、関数IFで以下のような処理を行っている。

　　条件：関数XLOOKUPで取得したデータが0に等しい場合は・・・、
真の場合：空文字（""）に置き換える
偽の場合：もういちど関数XLOOKUPでデータを取得しなおす

このように処理することで、「空白セル」を空白のまま取得できるようになる。

  • データの取得結果

このテクニックの難点は、XLOOKUPなどの関数を2回も記述しなければならないことだ。関数の入力が面倒になるし、見通しが悪くなるため記述ミスも起こりやすくなる。

（3）関数LETを活用した方法

続いては、先ほど紹介した（2）のテクニックをもう少し短く記述する方法を紹介していこう。

この場合は「関数LET」を活用する。関数LETは、簡単に説明すると、「数式や関数の結果を変数に保管して再利用できる関数」となる。ちなみに、関数LETは比較的新しい関数になるため、Microsoft 365またはExcel 2021でのみ使用できる関数となる。

具体的な例を紹介していこう。以下の図に示した例では、aという変数を用意し、この変数aにXLOOKUPで取得したデータを代入している。さらに、「変数aが0の場合は空文字に置き換える、そうでない場合は変数aをそのまま出力」という処理を関数IFで実行している。

  • 関数LETを利用して「同じ記述の繰り返し」を回避

関数LETの引数に対応した形で説明すると、

　第1引数：変数aを用意する
　第2引数：関数XLOOKUPでデータを取得し、その値を変数aに代入する
　第3引数：変数aが0の場合は空文字に置き換える

という流れになる。

このように関数LETを活用すると、XLOOKUPなどの関数を2回記述しなくても、（2）のテクニックと同様の処理を実現できる。

  • データの取得結果

なお、（2）や（3）で紹介したテクニックを利用するときは、数値データの0（ゼロ）を正しく取得できなくなることに注意しなければならない。

たとえば、取得元の表に「数値データの0」があり、このデータを（2）や（3）のテクニックで取得すると、その結果は空文字（表示なし）に置き換えられてしまう。つまり、数値の0を、そのまま0として取得できなくなってしまう訳だ。この点に十分注意しなければならない。

（4）取得範囲を関数IFで記述する方法

続いて紹介するのは、取得範囲を「関数IF」で記述する方法だ。この方法が最もトラブルが少なく、多くの場面で利用できるテクニックになると考えられる。

まずは関数XLOOKUPを使用する場合を例にして、具体的な記述例を紹介しておこう。関数XLOOKUPは、「データを取得するセル範囲」を第3引数に指定する仕様になっている。この第3引数を関数IFで記述する。

  • XLOOKUPの取得範囲（第3引数）を関数IFで指定

上図に示した例の場合、「A11:H510」のセル範囲について「セルが空白か？」を判定し、空白であった場合は「空文字」に置き換える、そうでなかった場合は「A11:H510」のまま変更しない、という処理を行っている。

つまり、A11:H510のセル範囲内にある「空白セル」を空文字（""）に置き換えている訳だ。関数XLOOKUPは、この架空のデータ表（配列）をもとにデータを取得していくことになる。この場合、「空白セル」が「空文字のデータ」に置き換えられているため、“データなし”を示す0は表示されなくなる。

  • データの取得結果

このテクニックを関数FILTERで使用した例も紹介しておこう。関数FILTERは、「データを取得するセル範囲」を第1引数に指定する仕様になっている。よって、第1引数を先ほど同様に関数IFで記述する。

  • FILTERの取得範囲（第1引数）を関数IFで指定

  • データの取得結果

前回の連載で紹介した、「部分一致」で検索できる関数FILTERにも応用できる。こちらも第1引数を関数IFで記述すればよい。

  • 「部分一致」でも検索可能FILTERの場合

  • データの取得結果

このテクニックを利用した場合は、数値データの0を、そのまま0として取得することが可能だ。記述がそれほど複雑にならず、あらゆる場面で使いやすい対策法といえるだろう。

（5）条件付き書式で0（ゼロ）を見えなくする方法

最後に紹介するのは、0を消去するのではなく、見えなくする方法だ。このテクニックを利用するときは、XLOOKUPやFILTERなどの関数を普通に記述すればよい。その代わりに「条件付き書式」を使って、取得されたデータの見た目を変更する。

具体的な手順を紹介しておこう。取得されたデータが表示されるセル範囲を選択し、「条件付き書式」から「新しいルール」を選択する。

  • 「新しいルール」の追加

続いて、「条件付き書式」のルールを指定していく。まずは、条件を指定する。「指定の値を含むセルだけを書式指定」を選択し、「セルの値が0に等しい」という条件を指定する。その後、「書式」ボタンをクリックする。

  • 条件に「0に等しい」を指定

あとは、文字の色に「白」を指定するだけ。これで、0のデータだけを見えなくする（文字を白色にする）ことが可能となる。

  • 文字の色に「白」を指定

単に「0が見えなくなればよい」という場合は、この方法が最も簡単かもしれない。取得したデータを印刷する場合にも活用できるだろう。

  • データの取得結果

関数の記述を書き換える必要がないため、Excel関数に不慣れな初心者の方も利用しやすいテクニックといえる。ただし、以下の2点に注意しておく必要がある。

　・0のデータが消去された訳ではない（見えないだけ）
　・「数値データの0」を取得した場合も見えなくなってしまう

このため、取得元の表に「数値データの0」が含まれている場合は、トラブルの原因になりかねない。

今回の連載では、ルックアップ関数などで「空白セル」を取得したときに表示される「0」を消去する（見えなくする）テクニックを5つ紹介した。各自の状況や知識量にあわせて、使いやすいテクニックを採用するとよいだろう。各テクニックの注意点も頭にいれながら、対処法の参考にして頂ければ幸いだ。