「見た目は同じなのに、なぜか一致しない」——このシリーズも、今回で11回目になります。
私自身、Excelを使い始めたばかりの頃は、VLOOKUPで検索してもヒットしなかったり、関数は正しいのに結果がおかしかったりと、原因の分からないトラブルに何度も悩まされてきました。ですが、Excelを研究していく中で気づいたのは、トラブルの多くは関数ではなく「データの中身」に原因がある、それも「見えないデータの違い」であることがほとんどだ、ということです。
以前、住所録を作っていたとき、セルの中を見やすくしようと、住所の途中で「Alt + Enter」を使って改行していたことがありました。見た目はとてもきれいに整ったのですが、いざその住所録を別のリストと突き合わせようとすると、改行を入れたセルだけがまったく一致しない。よかれと思ってやった見た目の工夫が、照合の妨げになっていたのです。自分で入れた改行なのに、それが原因だと気づくまで、しばらく時間がかかりました。
前回⑩では「コピー時に文字が混入する」ケースを研究しました。今回のテーマは、その中でもとくに厄介な「改行が入っている」ケースです。コピー時に見えない文字が混入するケースの続きとも言える内容です。改行が厄介なのは、見えている場合と、見えていない場合があるという点です。場合によっては、違和感すら覚えないまま、エラーが発生してしまいます。では、一緒に研究していきましょう。
よくある状況
まずはよくある例を見てみます。次のような商品名のデータがあるとします。
このデータを使って、別の表と照合するためにVLOOKUP関数を使います。
=VLOOKUP(A2, 範囲, 列番号, FALSE)通常であれば「りんご」は一致するはずですが、なぜか一致しない、という現象が起きることがあります。
「同じ文字なのに、なぜ？」——このとき、多くの人は「関数が間違っているのでは」「範囲がおかしいのでは」と考えます。ですが、実際の原因は、もっと見えにくいところに潜んでいます。それが、今回のテーマです。
原因：セルの中に改行が含まれている
見た目では「りんご」と表示されていても、実際の中身は「りんご＋改行」となっているケースがあります。また、場合によっては、セルの中で「りんご」と「（次の行）」のように、実際に改行された状態になっていることもあります。
この「改行」は、Excelの内部では CHAR(10) という文字コードのデータとして扱われます。つまり、Excelにとっては次のように別物として認識されます。
人間の目には同じ「りんご」に見えても、Excelにとっては完全に別のデータです。この違いが原因で、VLOOKUPで一致しない、COUNTIFでカウントできない、検索してもヒットしない、といった問題が起こります。
原因の根っこは、これまで①〜⑩で取り上げてきたトラブルとまったく同じで、「見た目は同じでも、中身が違う」という一点に尽きます。改行は、その中でもとくに、見えるときと見えないときがあるぶん、判断が難しいケースだと言えます。
なぜ起きるのか
研究員として、実務でよく見る原因は主に次の3つです。
① Webページからコピー
Web上のテキストには、改行コードが含まれていることがあります。段落の区切りや、見た目を整えるための改行が、そのままコピーされてしまうのです。ニュースサイトや社内ポータルなどからデータを持ってくるときに、とくに起こりやすいです。
② PDFからコピー
PDFは「見た目」を再現することを重視したファイル形式です。そのため、元の紙面のレイアウトを再現しようとして、内部的に改行が挿入されていることがあります。それをコピーすると、意図しない位置に改行が入り込んでしまいます。
③ Alt + Enterで手動改行
Excel上で、セルの中を見やすくしようとして「Alt + Enter」で手動改行した場合も、その改行コードがセルの中に残ります。自分で入力したデータであっても、この操作をしていると、あとで照合や検索がうまくいかなくなることがあります。
とくに多いのが、コピペによる混入です。前回⑩の「見えない文字」と同じく、コピー操作は便利な一方で、データの純度を下げる要因にもなります。
見分けるポイント
改行は見える場合もありますが、見えないことも多いです。そのため、次のような「違和感」に注目します。
① セルの高さが微妙に高い／行の途中で折り返されている
改行が入っていると、そのセルだけ高さが少し高くなったり、文字が途中で折り返されて表示されたりすることがあります。他のセルと見比べて、なんとなく高さが違うと感じたら、改行を疑うサインです。
② 数式バーで見ると改行されている
セルをクリックして数式バーを確認すると、セルの中の表示では1行に見えていた文字が、数式バーでは2行に分かれて表示されることがあります。これは、改行が入っている決定的な証拠です。
③ 一致・検索がうまくいかない
VLOOKUPで一致しない、検索してもヒットしない、といった症状も、改行が原因のことがあります。
④ LEN関数で文字数を数える
確実に判断したい場合は、LEN関数で文字数を数える方法が役立ちます。
=LEN(A2)「りんご」は本来3文字ですが、改行が1つ含まれていると4文字と表示されます。想定より文字数が多い場合は、改行などの見えない文字が紛れ込んでいる、と判断できます。改行は目視では気づきにくいため、このLEN関数での確認が、とくに効果を発揮します。
こうした違和感があったら、「改行が入っている可能性」を疑うことが重要です。
解決方法
改行だけを狙って消す：SUBSTITUTE関数
改行を確実に削除したい場合は、SUBSTITUTE関数が便利です。
=SUBSTITUTE(A2, CHAR(10), "")これは、「セルの中の改行（CHAR(10)）を、空文字（何もなし）に置き換える」という処理です。つまり、セル内の改行だけを狙って削除できます。改行が原因だとはっきり分かっている場合に、ピンポイントで効く方法です。
なお、CHAR(10)は「セル内の改行」を表しますが、環境によってはCHAR(13)（キャリッジリターン）が混ざっていることもあります。その場合は、SUBSTITUTEを二重にして =SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),""),CHAR(13),"") のように書くと、より確実に削除できます。
まとめて消す：TRIM＋CLEAN
改行だけでなく、空白や他の見えない文字もまとめて処理したい場合は、前回⑩でも紹介したTRIMとCLEANの組み合わせがおすすめです。
=TRIM(CLEAN(A2))それぞれの役割はこうです。TRIMは前後の空白を削除し、CLEANは改行を含む制御文字を削除します。この組み合わせにより、空白＋見えない文字＋改行を、まとめて一度に処理できます。「改行かもしれないし、他の文字かもしれない」というときは、こちらの方が確実です。迷ったら、まずこの =TRIM(CLEAN(A2)) を試すとよいでしょう。
実務での使い方
データが多い場合は、次の手順が効率的です。
まず、別の列に =TRIM(CLEAN(A2)) を入力し、それを下までコピーします。次に、その列全体をコピーし、「形式を選択して貼り付け」から「値」を選んで貼り付けます。こうすることで、関数の結果を「ただの文字データ」として確定でき、元の改行入りデータを置き換えることができます。関数のままだと、元の列を削除したときに結果も消えてしまうため、最後に「値として貼り付ける」という一手間が大切です。
この手順を覚えておけば、Webやシステム、PDFから取り込んだ大量のデータでも、改行をまとめて取り除いてクリーンな状態に整えることができます。
なお、注意点が一つあります。それは、「改行を消していいセル」と「改行が必要なセル」を区別することです。たとえば、備考欄や住所欄のように、あえて改行を入れて読みやすくしているセルもあります。照合や計算に使う列の改行は削除すべきですが、人が読むための説明文の改行まで一律に消してしまうと、かえって見づらくなることもあります。改行を消す前に、その列が「計算・照合に使う列」なのか「人が読むための列」なのかを一度確認しておくと、必要な改行まで消してしまう失敗を防げます。
研究員メモ
ここまでの研究で見えてくるのは、Excelトラブルの多くは「見えない要素」が原因である、ということです。具体的には、空白、見えない文字、そして今回の改行。この3つが、非常に多い原因です。
初心者のうちは「関数が難しい」と感じてしまいがちですが、実際にはデータの中身の問題であるケースがほとんどです。①の文字列、②の全角数字、③のスペース、④の単位、⑤のアポストロフィ、⑥の数字と文字の混在、⑦の日付、⑧の先頭の空白、⑨の末尾の空白、⑩の見えない文字、そして今回⑪の改行。ここまで11回にわたってお伝えしてきましたが、共通しているのは「見た目ではなく、データの中身に原因がある」という一点です。
だからこそ大切なのは、違和感があったら中身を疑う、という視点です。これが身につくと、Excelのトラブル対応力は一気に上がります。なお、半角と全角の違いなど、文字コードのレベルで一致しなくなるケースもあります。文字コードの違いで一致しないケースについても、あわせて確認しておくと、照合トラブルにより強くなれます。
まとめ
今回のテーマは「関数がうまく動かない理由⑪｜改行が入っている」でした。ポイントをまとめます。
- チェックポイント：セルの高さが違う／数式バーで改行されている／一致しない
- 原因：セルの中に改行（CHAR(10)）が含まれている
- 解決方法：
=SUBSTITUTE(セル, CHAR(10), "")または=TRIM(CLEAN(セル))
いちばん大切なのは、一致しないときは、見えない改行も疑う、という視点です。とくに、コピペしたデータや、Alt + Enterで整えたデータは要注意です。「同じはずなのに一致しない」と感じたら、まずは数式バーやLEN関数で確認し、SUBSTITUTEやTRIM・CLEANで整える、という流れを覚えておくと安心です。
もし今、改行が入ったデータがあっても、慌てる必要はありません。SUBSTITUTE関数やTRIM・CLEAN関数を使えば、見た目はそのままに、関数がきちんと動くデータへと整えることができます。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。次回は、数値の形式がバラバラになってしまう「数値の形式が統一されていない」ケースを取り上げる予定です。
コメント