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