月末の締め作業で、販売管理システムから書き出した取引先一覧と、経理で使っているExcelの台帳を突き合わせていたときのことです。取引先名でVLOOKUPをかけたら、120件ほどのうち40件近くが#N/A。スペルミスかと思って1件ずつ見比べても、画面の上ではどう見ても同じ「アップル商店」でした。
半ばやけになって、台帳側のセルを消して手で打ち直してみたところ、そこだけすっと一致しました。打ち直したことで何が変わったのか。答えは、システムから出てきた「ｱｯﾌﾟﾙ」が半角カナで、私が打ったのが全角カナだった、というだけのことです。見比べたつもりでいたのに、文字の幅のわずかな差にはまったく目がいっていませんでした。
この記事では、そうした「文字の種類の違い」で一致しないときに、どう確かめて、どう直すかをまとめます。直し方は1つではありません。「照合だけ通したいのか」「元データそのものを整えたいのか」で選ぶべき手が変わるので、そこも分けて書いていきます。
最初に30秒だけ：本当に「文字の違い」が原因か確かめる
関数の式をいじり始める前に、次の3つを順に試してみてください。どれも空いているセルに数式を1つ入れるだけです。
テスト1 「=」で2つのセルを直接比べる
一致しないはずの2つのセル（たとえばA2とD5）を、=A2=D5 で比べます。結果がFALSEなら、見た目がどうであれ、Excelはこの2つを別の値として扱っています。
逆にここでTRUEが返ってくるなら、文字の中身は同じです。原因はVLOOKUPの範囲指定や列番号、検索方法の指定など、式の側にある可能性が高いので、そちらを見直したほうが早く片付きます。
テスト2 LENで文字数を数える
=LEN(A2) と =LEN(D5) を並べてみます。半角カナは濁点や半濁点が独立した1文字として数えられるので、「ｱｯﾌﾟﾙ」は5文字、「アップル」は4文字になります。見た目は同じなのに文字数がずれていたら、ほぼ当たりと考えてよいでしょう。
日本語版のExcelなら、=LENB(A2)-LEN(A2) もあわせて入れておくと便利です。LENBは全角を2、半角を1として数えるので、LENとの差がそのまま「全角の文字がいくつ入っているか」になります。結果が0なら、そのセルはすべて半角で書かれています。
テスト3 UNICODEで1文字ずつの番号を見る
それでも判断がつかないときは、文字ごとの番号を出してしまいます。=UNICODE(MID(A2,1,1)) の2つ目の「1」を2、3……と変えていけば、何文字目がどんな文字なのかを数字で確認できます。
全角の「ア」は12450、半角の「ｱ」は65393。人間の目には似て見えても、Excelが見ている番号はまるで別物です。この番号の違いこそが、「文字コード違い」と呼ばれているものの正体です。
「文字コード違い」でよく混ざる4つの組み合わせ
実務で出会うのは、だいたい次の4種類に絞られます。UNICODEの番号も載せておくので、テスト3の結果と照らし合わせてみてください。
| 種類 | 見た目の例 | UNICODEの番号 | ASC・JISで揃うか |
|---|---|---|---|
| カナの半角・全角 | ｱ ／ ア | 65393 ／ 12450 | 揃う |
| 英数字の半角・全角 | A ／ Ａ | 65 ／ 65313 | 揃う |
| スペースの半角・全角 | 半角スペース ／ 全角スペース | 32 ／ 12288 | 揃う |
| 形の似た横棒の記号 | – ／ ー ／ ― ／ − | 45 ／ 12540 ／ 8213 ／ 8722 | 一部しか揃わない |
英数字のうち、数字が全角になっているケースは、照合だけでなく合計の計算にも響いてきます。
数字の側で困っている場合は、全角数字でSUMが計算されないときの見分け方と直し方を先に読んでおくと、話がつながりやすいと思います。
スペースの混在は、文字の種類というより「余計な空白が入り込んでいる」問題として表に出てくることも多いです。
名前やコードの頭に空白が紛れ込んでいる疑いがあるなら、セルの先頭に空白が入って関数が動かないときの直し方もあわせて確認してみてください。
4つの中でいちばん手ごわいのは、最後の横棒です。ハイフン、長音、ダッシュ、マイナス記号は、どれもただの「一本線」に見えますが、番号はばらばら。しかも後で触れるように、ASC関数やJIS関数だけでは全部をきれいに揃えられません。電話番号や型番の列で一致しないときは、まずこの横棒を疑ってみるのが近道です。
「文字コード」と「文字化け」は別の話
少し脇道にそれますが、ここで言葉を整理しておきます。
「文字コード」と聞いて、CSVファイルを開いたら意味の読めない漢字や記号がずらっと並んだ、という経験を思い出す人もいるかもしれません。あれはShift_JISやUTF-8といった「ファイル全体の保存形式」が、開く側の想定と食い違ったときに起きる文字化けで、この記事の話とは原因が違います。
ここで扱っている「文字コード違い」は、ファイルは問題なく開けていて、どの文字も普通に読める状態です。それなのに、ある行は半角カナ、別の行は全角カナというように、1文字ずつの番号がずれている。見た目が崩れないぶん、文字化けよりずっと気づきにくいのが困ったところです。
ちなみにCSVを取り込んだときは、文字化けとも半角・全角とも別に、数字が文字列として入ってしまうトラブルもよく起こります。
システムから出したCSVで合計が合わないなら、CSV取り込みで数字が文字列になるときの見分け方と直し方も一度のぞいてみてください。
直し方は「何をしたいか」で選ぶ
ここからが本題です。文字の違いが原因だとわかったら、次の3つのケースのうち、自分の状況に近いものを選んでください。
ケース1 元データには手を付けず、照合だけ通したい
システムから出力したデータを直接書き換えると、あとで「どこを変えたのか」が追えなくなります。監査や引き継ぎの都合で、元の列はそのまま残しておきたい、という場面は意外と多いものです。
この場合は、照合用の作業列を1本足すのがいちばん確実です。検索する側の表の空いている列に =ASC(A2) を入れて最終行までコピーし、検索される側の表にも同じように作業列を作ります。あとは作業列同士でVLOOKUPをかければ、元データを一文字も変えずに照合できます。
1つだけ気をつけたいのが、作業列を置く位置です。VLOOKUPは指定した範囲のいちばん左の列を探しに行くので、検索される側の作業列は範囲の左端に来るように挿入してください。表の右端に作ってしまうと、範囲の取り方が急にややこしくなります。
Microsoft 365やExcel 2021以降を使っているなら、作業列を作らずに済ませる書き方もあります。
=XLOOKUP(ASC(A2),ASC($D$2:$D$200),$E$2:$E$200)検索値と検索範囲の両方を、数式の中でその場だけ半角に揃えてから探す、という書き方です。手軽ではありますが、ほかの人があとから見たときに「なぜASCが入っているのか」が伝わりにくいのが難点です。誰かに引き継ぐ予定のファイルなら、作業列を見える形で残しておくほうが親切だと思います。
ケース2 元データそのものを整えてしまいたい
その表を今後も使い続けるなら、いっそ中身を揃えてしまったほうが後々楽です。手順は次のとおりです。
- 直したい列の隣に空の列を挿入し、
=ASC(A2)（全角に揃えたいなら=JIS(A2)）を入れて最終行までコピーする - 作った列をまるごとコピーし、同じ場所で右クリック→「形式を選択して貼り付け」→「値」を選んで貼り付ける
- セルをクリックして、数式ではなく文字だけが入っていることを確かめてから、元の列を削除する
2の「値で貼り付け」を飛ばすと、元の列を消した瞬間に、揃えたはずの結果がすべて#REF!に変わります。私はこれを一度やってしまい、300行分をまるごとやり直す羽目になりました。3の確認は、面倒でも挟んでおいたほうがいい一手間です。
空白の入り方も怪しいときは、=TRIM(ASC(A2)) のようにTRIM関数と重ねておくと、半角への統一と前後の空白の削除を1回で済ませられます。ただ、空白の中にはTRIMをかけても消えないものがあります。
TRIMのあとでもまだ一致しない行が残るなら、末尾の空白でVLOOKUPが一致しないときの見分け方と直し方で、消えない空白への対処を確認してみてください。
ケース3 横棒の記号が混ざっている
電話番号、郵便番号、型番といった列で一致しないときは、関数より置換機能のほうが話が早いことが多いです。
まず、直したい列だけをマウスで選択してから Ctrl + H を押して「検索と置換」を開きます。「オプション」を押すと「半角と全角を区別する」というチェック欄が出てくるので、ここにチェックを入れておくと、狙った記号だけを確実に置き換えられます。あとは「検索する文字列」に長音の「ー」、「置換後の文字列」に半角ハイフンの「-」を入れて「すべて置換」。ダッシュの「―」やマイナス記号の「−」も、同じように1つずつ置き換えていきます。
ここで列を選択し忘れると、シート全体が置換の対象になってしまいます。「コーヒー」「データ」のような普通のカタカナまで「コ-ヒ-」「デ-タ」に化けるので、置換の前に範囲を選ぶことだけは忘れないでください。
置換を使わず、関数で揃えたい場合はSUBSTITUTE関数を重ねる方法もあります。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"ー","-"),"―","-"),"−","-")こちらも、カタカナの商品名が入っている列には使わないのが鉄則です。なお、全角のハイフン「－」はASC関数で半角の「-」に変わりますが、長音の「ー」は半角カナの「ｰ」になるだけで、ハイフンにはなりません。ダッシュやマイナス記号にいたっては、ASCをかけても何も変わりません。横棒の混在だけは、置換かSUBSTITUTEで片付ける、と覚えておくと迷わずに済みます。
ASCとJIS、どちらに揃えるか迷ったら
「結局どっちに揃えればいいの？」という質問はよく受けます。私は列の中身で決めています。
| 列の中身 | 揃える向き | 理由 |
|---|---|---|
| 商品コード・型番・社員番号など英数字が中心 | ASC（半角） | ほかのシステムに取り込む値は半角が前提になっていることが多い |
| 顧客名・取引先名などカナが入る | JIS（全角） | 半角カナは社外に出す資料やメールで読みにくく、扱えないソフトもある |
| 照合のためだけに一時的に揃える | どちらでもよい | 最終的に使わない作業列なので、両方の表で同じ関数を使っていれば問題ない |
ASCを使うときに知っておきたいのが、濁点の扱いです。全角の「ガ」にASCをかけると、半角の「ｶ」と「ﾞ」の2文字に分かれます。つまり、揃えただけで文字数が増えることがあるわけです。文字数に上限のあるシステムの入力欄へそのまま貼ると、途中で切れてしまうこともあるので注意してください。反対に、半角の「ｶﾞ」にJISをかけると、全角の「ガ」1文字に戻ります。
また、ASCやJISで変わるのはカナ・英数字・スペース・一部の記号だけで、ひらがなと漢字はそのまま残ります。「アップル商店」にASCをかけると「ｱｯﾌﾟﾙ商店」になり、漢字の「商店」は全角のままです。
私が実際にやってしまった失敗3つ
手順がわかっていても、実務だとつまずくポイントは別のところにあります。どれも私自身がやってしまったものです。
1つ目は、片方の表だけ揃えて満足してしまったことです。システムから出したデータは半角カナばかりだろうと思い込み、そちらだけJISで全角に揃えました。ところが、手入力の台帳側にも、急いで打った半角カナがところどころ混ざっていたのです。照合するなら、検索する側とされる側、両方に同じ関数をかける。これは例外なしのルールだと思っています。
2つ目は、ケース2のやり方で一度きれいにしたのに、翌月また同じ不一致が出たことです。原因は単純で、システムから毎月出てくるデータは、当然ながら毎回半角カナのまま。月次で繰り返す作業なら、元データを直すより、ケース1の作業列を表に組み込んでおくほうが結果的に手間が減ります。
3つ目は、1つのセルに取引先名とコードが一緒に入っていた列に、JISをかけてしまったことです。「ｱｯﾌﾟﾙ商店 A-001」が「アップル商店 Ａ－００１」になり、カナはきれいになったものの、コード部分まで全角に。これを別のシステムに取り込もうとしてエラーが出ました。1つの列に種類の違う情報が入っているときは、先に列を分けてから、それぞれに合った向きで揃えるのが安全です。
次から混ざらないようにする2つの仕掛け
直すのと同じくらい大事なのが、そもそも混ざらない表にしておくことです。
1つは条件付き書式で「混ざったセルに色を付ける」方法です。対象の列を選んで「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選び、次の数式を入れます。
- 半角で統一したい列なら
=A2<>ASC(A2) - 全角で統一したい列なら
=A2<>JIS(A2)
ASCをかけたら変わってしまうセル、つまり全角の文字が混ざっているセルだけに色が付く、という仕組みです。数式の「A2」は、選択した範囲の先頭のセルに合わせて書き換えてください。全角に揃える列で2つ目の式を使うと、半角の数字や英字もまとめて色が付くので、そこは中身を見て判断します。
もう1つは、入力規則で日本語入力のモードを固定する方法です。「データ」→「データの入力規則」を開き、「日本語入力」タブで、コードの列なら「オフ（英語モード）」、カナ名の列なら「全角カタカナ」などを選んでおくと、そのセルを選んだときに入力モードが自動で切り替わります。
ただし、どちらも手入力に対しての仕掛けで、コピーして貼り付けたデータは素通りしてしまいます。貼り付けで持ち込まれる混在は、条件付き書式の色で気づいて直す、という二段構えにしておくと安心です。
よくある質問
Q. ASCで揃えたのに、まだ一致しない行が残ります。
A. まず横棒の記号を疑ってください。ASCでは揃わない記号が残っている可能性があります。それでもだめなら、テスト3のUNICODEで1文字ずつ番号を確認すると、160番（ノーブレークスペース）など、目に見えない特殊な文字が見つかることがあります。また、片方が数値、もう片方が文字列の「100」になっているような、形式の違いが原因のこともあります。
Q. 大文字と小文字の違いでも一致しなくなりますか？
A. VLOOKUPやXLOOKUP、「=」での比較は大文字と小文字を区別しないので、「apple」と「APPLE」は一致したものとして扱われます。区別して比べたいときは =EXACT(A2,D5) を使います。なお、ASCとJISは大文字・小文字を変えないので、揃えたい場合はUPPER関数やLOWER関数を使ってください。
Q. 数式を使わず、目で見て半角カナを見分けるコツはありますか？
A. 列のフォントを一時的に「MS ゴシック」のような等幅のフォントに変えてみてください。半角の文字はちょうど全角の半分の幅で表示されるので、混ざっている行が並びの凸凹で浮き上がって見えます。確認が終わったら、フォントは元に戻して大丈夫です。
現場では、半角・全角の違いだけが単独で起きることはむしろ少なく、空白や数値の形式の不統一が同時に重なっていることがよくあります。もう一歩踏み込んで、外部データを取り込んだあとに不一致が重なるときの片付け順をnoteにまとめました。複数の原因がからんで、どこから手を付ければいいかわからないときの参考にしてください。
研究員のひとこと
冒頭の40件の#N/Aは、作業列を2本足してVLOOKUPをかけ直したら、ものの5分で片付きました。それまで1時間近く、式が間違っているのではと画面をにらんでいたのが嘘のようです。
一致しないときに式を疑うのは自然なことですが、先に =A2=D5 とLENで中身を確かめるだけで、遠回りはかなり減らせます。見た目が同じでも、Excelが見ているのは番号のほう。この感覚は、数字が合わないときにもそのまま使えます。
文字ではなく数値の側で同じようなことが起きている場合は、見た目は同じ「100」なのに合計が合わないときの見分け方と直し方で、形式がばらばらな数字の揃え方を詳しく書いています。
コメント