「見た目は同じなのに、なぜか一致しない」——このシリーズも、今回で10回目になります。
私自身、Excelを使い始めたばかりの頃は、VLOOKUPで検索してもヒットしなかったり、関数は正しいのに結果がおかしかったりと、原因の分からないトラブルに何度も悩まされてきました。ですが、Excelを研究していく中で気づいたのは、トラブルの多くは関数ではなく「データの中身」に原因があるということです。
以前、社内の管理画面に表示されていた一覧をコピーしてExcelに貼り付け、別のマスタと照合しようとしたとき、なぜか半分近くのデータが一致せず、頭を抱えたことがあります。空白かと思ってTRIM関数を試しても直らない。文字数を数えてみると、見た目の文字数より1つ多い。結局、コピー時に紛れ込んでいた改行コードが原因だったのですが、そこにたどり着くまで、ずいぶん試行錯誤を重ねました。
これまで⑧⑨では、先頭の空白や末尾の空白について研究してきました。今回のテーマは「コピー時に文字が混入する」ケースです。これはかなり多く、そして厄介なことに、自分ではまったく気づきにくいという特徴があります。空白と違って、見た目ではほぼ判別できないのに、Excelはしっかりと「違い」として認識しているのです。では、一緒に研究していきましょう。
よくある状況
まずはよくある流れを見てみます。Webサイトや社内システムからデータをコピーして、Excelに貼り付けたとします。
見た目には、まったく問題がなさそうです。そして、このデータを使って別の表と照合するために、VLOOKUP関数を使います。
=VLOOKUP(A2, 範囲, 列番号, FALSE)通常であれば「りんご」は一致するはずですが、なぜか一致しない、という現象が起きることがあります。
「ちゃんとコピーしたのに、なぜ？」——ここで、つい関数を疑ってしまう方が多いのですが、実際には別の原因が潜んでいます。それが、今回のテーマです。
原因：見えない文字が入っている
コピーしたデータは、「りんご」に見えても、実際には「りんご＋見えない文字」となっているケースがあります。
具体的には、次のようなものが紛れ込みます。
- 改行コード（改行の情報）
- 特殊スペース（全角スペースやノーブレークスペースなど）
- 制御文字（システム特有の、印刷できない情報）
これらは画面上ではまったく見えません。しかしExcelは、これらをデータとしてしっかり認識しています。そのため、Excelの内部では次のように扱われます。
人間の目には同じ「りんご」に見えても、Excelにとっては完全に別物です。この違いが原因で、VLOOKUPで一致しない、COUNTIFでカウントできない、検索してもヒットしない、といった問題が起こります。
原因の根っこは、これまで①〜⑨で取り上げてきたトラブルとまったく同じです。「見た目は同じでも、中身が違う」——この一点に尽きます。空白も見えない文字の一種でしたが、今回はさらに見つけにくい、改行コードや制御文字といった、より正体のつかみにくい文字が相手になります。
なぜ起きるのか
研究員として、実務でよく見る原因は主に次の3つです。
① Webページからコピー
Webページの文字は、HTMLという構造の中に配置されています。そのため、コピーすると、文字だけでなく、HTMLの構造に含まれる情報（改行や特殊なスペースなど）が一緒に貼り付けられることがあります。ニュースサイトや社内ポータル、Webの管理画面などからデータを持ってくるときに、とくに起こりやすいです。
② PDFからコピー
PDFの文字情報は、そのままの文字列ではなく、特殊な形式で保持されていることがあります。そのため、PDFからコピーして貼り付けると、見た目は同じでも、改行や余計な区切りが紛れ込むことがあります。請求書や報告書など、PDFからデータを転記する場面では、注意が必要です。
③ 社内システムのデータ
社内の基幹システムから出力したデータには、そのシステム独自の仕様による文字や制御コードが含まれていることがあります。これは外からは判断しにくく、「なぜかこのシステムのデータだけ、いつも照合に失敗する」といった形で表面化します。
とくに多いのが、Webサイトから直接Excelにコピーする流れです。便利な一方で、もっともトラブルが発生しやすいポイントでもあります。
見分けるポイント
見えない文字は、目で直接確認することができません。しかし、次のような「違和感」がヒントになります。
① 見た目は同じなのに一致しない
もっとも典型的なサインです。完全一致で検索しているのに見つからない、というときは、見えない文字を疑ってみてください。
② 検索しても見つからない
「Ctrl + F」で検索しても、あるはずのデータが見つからない場合も、見えない文字が紛れ込んでいる可能性があります。
③ 並び替えの位置がおかしい／一部だけ結果がズレる
改行コードや制御文字が入っていると、並び順に違和感が出たり、一部の行だけ計算結果がおかしくなったりすることがあります。
④ LEN関数で文字数を数える
確実に判断したい場合は、LEN関数で文字数を数える方法が役立ちます。
=LEN(A2)「りんご」は本来3文字ですが、見えない文字が1つ混ざっていると4文字と表示されます。想定より文字数が多い場合は、目に見えない何かが紛れ込んでいる、と判断できます。空白や制御文字は目視できないため、このLEN関数での確認が、原因の切り分けにとても役立ちます。
こうした違和感があったら、まず「コピー元のデータを疑う」ことが重要です。
解決方法
まずは基本のTRIM関数
まずは、これまでの回でも登場したTRIM関数です。
=TRIM(A2)TRIM関数は、前後の余計な空白を削除する関数です。ただし今回のケースでは、空白ではなく改行コードや制御文字が原因になっていることが多いため、TRIMだけでは直らない場合があります。
より強力なCLEAN関数
そこで登場するのが、CLEAN関数です。
=CLEAN(A2)CLEAN関数は、印刷できない制御文字（改行コードなど）を削除してくれる関数です。TRIMが「空白」を担当するのに対して、CLEANは「見えない制御文字」を担当する、と考えると分かりやすいと思います。
最強の組み合わせ：TRIM＋CLEAN
そして、この2つを組み合わせると、もっとも効果的です。
=TRIM(CLEAN(A2))このように書くと、まずCLEANで見えない制御文字を取り除き、次にTRIMで前後の空白を削除する、という2段階の処理が一度にできます。コピー由来のトラブルの多くは、この組み合わせで解決できます。迷ったら、まずこの =TRIM(CLEAN(A2)) を試す、と覚えておくとよいでしょう。
それでも消えない場合
TRIMとCLEANを組み合わせても、なぜか直らない、というケースもあります。これは、全角スペースや、ノーブレークスペースと呼ばれる特殊な空白文字が原因の場合です。CLEAN関数は制御文字は削除できますが、これらの「文字扱いの空白」までは取り除けないことがあります。その場合は、置換機能（Ctrl + H）で、全角スペースや該当する文字を直接削除する必要があります。「TRIMとCLEANでも消えない」という場面に出会ったら、置換機能の出番だと覚えておいてください。
実務での使い方
データが多い場合は、次の手順で一括処理するのがおすすめです。
まず、別の列に =TRIM(CLEAN(A2)) を入力し、それを下までコピーします。次に、その列全体をコピーし、「形式を選択して貼り付け」から「値」を選んで貼り付けます。こうすることで、関数の結果を「ただの文字データ」として確定でき、元の見えない文字が入ったデータを置き換えることができます。関数のままだと、元の列を削除したときに結果も消えてしまうため、最後に「値として貼り付ける」という一手間が大切です。
この手順を覚えておけば、Webやシステムから取り込んだ大量のデータでも、まとめてクリーンな状態に整えることができます。外部データを扱う機会が多い方は、ぜひ習慣にしておきたい処理です。
研究員メモ
今回のポイントは、とても重要です。Excelでは、「見た目」と「中身」は別物である、という原則が、ここでも当てはまります。
とくにコピペは、時短になる便利な操作であると同時に、トラブルの原因にもなる、という両面を持っています。①の文字列、②の全角数字、③のスペース、④の単位、⑤のアポストロフィ、⑥の数字と文字の混在、⑦の日付、⑧の先頭の空白、⑨の末尾の空白、そして今回⑩の見えない文字。ここまで10回にわたってお伝えしてきましたが、そのうちのかなりの割合が、実は「コピペ」をきっかけに発生しています。
だからこそ、違和感があったときは、「空白」と「見えない文字」——この2つを疑うだけで、解決できるケースが一気に増えます。そして、その多くは =TRIM(CLEAN(セル)) の一手で片付きます。なお、半角と全角の違いなど、文字コードのレベルで一致しなくなるケースもあります。文字コードの違いで一致しないケースについても、あわせて確認しておくと、コピペ由来のトラブルにより強くなれます。
まとめ
今回のテーマは「関数がうまく動かない理由⑩｜コピー時に文字が混入する」でした。ポイントをまとめます。
- チェックポイント：コピペしたデータである／一致しない／検索できない
- 原因：改行コードや制御文字など、見えない文字が混入している
- 解決方法：
=TRIM(CLEAN(セル))で空白と制御文字をまとめて削除する
いちばん大切なのは、コピペしたデータで違和感があったら、見えない文字を疑う、という視点です。目には見えなくても、Excelはしっかり認識しています。「同じはずなのに一致しない」と感じたら、まずはLEN関数で文字数を確認し、=TRIM(CLEAN(セル)) で整える、という流れを覚えておくと安心です。
もし今、コピペで見えない文字が入ったデータがあっても、慌てる必要はありません。TRIMとCLEANを組み合わせれば、見た目はそのままに、関数がきちんと動くデータへと整えることができます。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。次回は、セルの中に改行が含まれてしまう「改行が入っている」ケースを取り上げる予定です。
コメント