「同じデータを入力しているはずなのに、なぜか一致しない」——このシリーズも、今回で8回目になります。
私自身、Excelを使い始めたばかりの頃は、VLOOKUPで検索しているのにヒットしなかったり、同じ文字のはずなのに一致しなかったりと、原因の分からないトラブルに何度も悩まされてきました。ですが、Excelを研究していく中で気づいたのは、トラブルの多くは関数ではなく「データの中身」に原因があるということです。
以前、取引先から届いた商品リストと、自社のマスタをVLOOKUPで突き合わせようとしたとき、いくつかの商品だけがどうしても一致せず、原因を探すのに苦労したことがあります。画面上ではまったく同じ商品名に見えるのに、一部だけ#N/Aになる。何度も見比べて、ようやく気づいたのが、その商品名の先頭に入っていた、たった1つの空白でした。目には見えないだけに、原因にたどり着くまで、ずいぶん遠回りをしてしまいました。
これまで①〜⑦では、数字や日付が文字列になっているケースを中心に研究してきました。今回のテーマは「先頭に空白がある」ケースです。これは非常に見つけにくく、初心者がとくにつまずきやすいポイントです。なぜなら、見た目ではほぼ分からないからです。この「見えない空白」が原因で、関数がうまく動かなくなることがあります。では、一緒に研究していきましょう。
よくある状況
まずはよくある例を見てみます。次のような商品名のデータがあるとします。
ここで、別の表と照合するためにVLOOKUP関数を使います。
=VLOOKUP(A2,範囲,列番号,FALSE)通常であれば、「りんご」は一致して、正しく値が返ってきます。しかし実際には、一致しない、#N/Aエラーになる、検索できない、といった問題が起きることがあります。
「同じ”りんご”なのに、なぜ？」——その原因が、今回のテーマです。
原因：見えない空白が入っている
見た目では「りんご」と表示されていても、実際のデータは「（空白）りんご」と、先頭にスペースが入った状態になっていることがあります。
Excelはこの違いを、非常に厳密に判断します。
人間の目には同じ「りんご」に見えても、Excelにとってはまったく別のデータです。そのため、VLOOKUPで一致しない、COUNTIFで数えられない、IF関数で判定できない、といった問題が起きます。
原因の根っこは、①〜⑦で取り上げてきた「見た目は同じでも、中身が違う」というトラブルとまったく同じです。数字が文字列になっているのと同じように、文字の先頭に空白が付いているだけで、Excelはそれを別のデータとして扱ってしまうのです。
なぜこのミスが起きるのか
研究員としてよく見る原因は、主に次の3つです。
① コピペしたデータ
他のシステムやWebサイトからデータをコピーした場合、余計な空白が含まれていることがあります。とくに、CSVやシステムから出力したデータは要注意です。桁や見た目を揃えるために、先頭に空白を入れて出力する仕様のシステムもあり、それがそのままコピーされてしまうのです。
コピー時には、空白だけでなく、目に見えない特殊な文字が紛れ込むこともあります。コピー時に見えない文字が混入するケースについては、以前の記事でCLEAN関数を使った対処法を詳しく取り上げていますので、TRIM関数を使っても直らないときは、あわせて確認してみてください。
② 手入力でスペースを入れてしまう
入力作業のとき、無意識にスペースキーを押してしまい、先頭に空白が入ってしまうことがあります。とくに、急いで入力しているときや、変換の操作に慣れていないときに起こりやすいミスです。自分ではまったく意識していないため、後から見返しても原因に気づきにくいのが厄介なところです。
③ 見た目を揃えるためにスペースを入れる
表をきれいに見せようとして、文字の位置をスペースで調整してしまうケースです。人間の目には整って見えるのですが、Excelにとっては「空白＋文字」という別のデータになってしまうため、これはNGな操作です。見た目を揃えたい場合は、スペースではなく、セルの配置（インデントや中央揃え）で調整するのが正解です。
見分けるポイント
先頭の空白は見えませんが、次のような違和感がヒントになります。
① VLOOKUPで一致しない
もっとも多いパターンです。完全一致（FALSE）で検索しているのに見つからない場合は、先頭の空白を疑ってみてください。
② COUNTIFで数えられない
同じデータのはずなのにカウントされない、という場合も、空白が原因の可能性があります。「1件あるはずなのに0と表示される」ようなときは、要注意です。
③ 並び替えの順番が変
空白があると、並び順に違和感が出ることがあります。空白は文字コードの上では「文字よりも前」に来るため、空白付きのデータだけが、リストの先頭に固まってしまう、といった現象が起きます。
④ LEN関数で文字数を数える
確実に判断したい場合は、LEN関数で文字数を数える方法があります。
=LEN(A2)「りんご」は本来3文字ですが、先頭に空白が1つ入っていると4文字と表示されます。想定より文字数が多い場合は、空白などの余計な文字が紛れ込んでいる、と判断できます。どこに空白があるか目視では分からないときに、とても役立つ方法です。
解決方法
もっとも簡単で強力な方法は、TRIM関数を使うことです。
=TRIM(A2)TRIM関数は、余計な空白を削除する関数です。具体的には、先頭の空白、末尾の空白、単語間の余分なスペースを、自動でまとめて削除してくれます。
使用例
「（空白）りんご」というデータにTRIM関数を使うと、「りんご」に整えられます。これだけで、VLOOKUPやCOUNTIFなどの関数が、正しく動くようになります。
まとめて修正する方法
データが大量にある場合は、次の手順で一括修正できます。
まず、別の列にTRIM関数を入力し、それを下までコピーします。次に、TRIMの結果が入った列全体をコピーし、「形式を選択して貼り付け」から「値」を選んで、元の位置（または新しい列）に貼り付けます。こうすることで、関数の結果を「ただの文字データ」として確定でき、元の空白付きデータを置き換えることができます。関数のままだと、元の列を消したときに結果も消えてしまうため、最後に「値として貼り付ける」という一手間が大切です。
TRIMで消えない空白もある
TRIM関数を使っても、なぜか空白が消えない、というケースもあります。これは、通常の半角スペースではなく、全角スペースや、特殊な空白文字（Webページ由来のものなど）が入っている場合に起こります。この場合は、CLEAN関数を組み合わせたり、置換機能で全角スペースを直接削除したりする必要があります。「TRIMで消えない空白」に出会ったら、通常の半角スペース以外の可能性を疑ってみてください。
そもそも空白を入れないための予防策
トラブルが起きてから直すのも大切ですが、そもそも空白が混ざらないように予防できれば、余計な手間を減らせます。実務で使える予防策をいくつか紹介します。
一つ目は、外部からデータをコピーするときに、そのまま貼り付けるのではなく、いったんメモ帳などのテキストエディタに貼り付けてから、あらためてExcelにコピーし直す方法です。これで、書式や特殊な空白が持ち込まれにくくなります。二つ目は、貼り付けた直後に、対象範囲へ一括でTRIM関数を通してから値として貼り直す、という手順を習慣にしておくことです。外部からデータを取り込む機会が多い方ほど、この一手間が効いてきます。
三つ目として、そもそも人が手入力する列については、入力ルールを共有しておくことも大切です。「前後にスペースを入れない」「見た目を揃えたいときはスペースではなくセルの配置機能を使う」といったルールを、複数人で作業する場合はあらかじめ決めておくと、後から空白の除去に追われることが少なくなります。
Excelで重要な考え方：見えない違いも区別される
Excelを使う上で、とても重要なポイントがあります。それは、見えない違いも区別される、ということです。
人間にとっては「同じ文字」に見えても、Excelにとっては、完全に一致していなければ別物です。「りんご」と「（空白）りんご」は、Excelの中ではまったく別のデータとして扱われます。たった1つの空白でも、Excelにとっては明確な違いなのです。
この考え方を理解すると、Excelのトラブルの多くが解決できるようになります。「同じはずなのに一致しない」というときは、目に見えている文字そのものだけでなく、その前後に見えない何かが付いていないか、と考える習慣を持っておくとよいでしょう。
研究員メモ
Excelのトラブル対応で一番大切なのは、違和感に気づくことです。一致しない、計算できない、結果がおかしい。こうしたときは、関数を疑うのではなく、データの中身を疑う。これがとても重要です。
①の文字列、②の全角数字、③のスペース、④の単位、⑤のアポストロフィ、⑥の数字と文字の混在、⑦の日付、そして今回⑧の先頭の空白。ここまで8回にわたってお伝えしてきましたが、共通しているのは「見た目ではなく、データの中身に原因がある」という一点です。空白が原因のケースは、その中でもとくに多く、そして一度気づけるようになると、すぐに見抜けるようになります。
なお、空白は先頭だけでなく、末尾に入っていることもあります。末尾の空白も、先頭の空白とまったく同じように、関数の一致を妨げます。末尾に空白があるケースについても、あわせて確認しておくと、空白まわりのトラブルにより強くなれます。
まとめ
今回のテーマは「関数がうまく動かない理由⑧｜先頭に空白がある」でした。ポイントをまとめます。
- チェックポイント：VLOOKUPで一致しない／COUNTIFで数えられない／並び順がおかしい
- 原因：先頭に空白が入っている
- 解決方法：
=TRIM(セル)で余計な空白を削除する
いちばん大切なのは、違和感があったら空白を疑う、という視点です。「同じはずなのに一致しない」と感じたら、まずはTRIM関数を試してみてください。この一手が、原因究明の時間を大きく短縮してくれます。
もし今、先頭に空白が入ったデータがあっても、慌てる必要はありません。TRIM関数を使えば、見た目はそのままに、関数がきちんと動くデータへと整えることができます。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。次回は、先頭の空白と対をなす「末尾に空白がある」ケースを取り上げる予定です。
コメント