SUMが合わない・VLOOKUPが一致しない原因は「見えない文字」|空白・改行・制御文字の見つけ方と消し方

アイキャッチ:SUMが合わない原因は見えない文字。NGは末尾スペースの3800が外れて合計22,100、OKはTRIMとVALUEで数値に戻して25,900 初心者シリーズ

説明のために、こんな場面を用意しました。金曜の夕方、取引先から届いた請求データをExcelに貼り付けて合計を出したところ、先方の明細の総額と、自分で出した合計が3,800円だけ合わない。

関数は =SUM(B2:B40) の一行だけ。範囲もきちんと選べている。それでも合わない。電卓で叩き直しても、自分の合計のほうが少ない。こういうときに関数や範囲ばかり見直していると、あっという間に1時間近く経ってしまいます。この例の犯人は、38行目の金額の後ろに入っていた、たった1個のスペースです。

画面上は「3800」と表示されているのに、Excelから見ればそれは数字ではありません。この記事では、遠回りになりがちな手順を省いて、最短で犯人を捕まえる方法からお伝えします。

なお、合計が合わないトラブルには「数字がそもそも文字列として入力されている」という、もっと頻度の高い原因もあります。まだ確認していない方は、数字が文字列になっているときの見分け方と直し方を先に読んでいただくと、切り分けが早くなります。

同じ見えない文字は、商品名や取引先名に入るとVLOOKUPやCOUNTIFを一致させなくします。文字の列で困っている方は、後半のVLOOKUPやCOUNTIFが一致しないときから読んでも大丈夫です。先頭・末尾の空白の違い、スペースではない制御文字、セル内の改行の消し方も、あわせてそこにまとめています。

扱うのは、数字や文字の前後・途中に入り込んだ見えない空白と改行です。合計のずれが空白のせいかどうかまだわからない段階なら、差額の形で原因を見分ける手順から入ってください。

まず30秒|合計が合わない原因を数字で炙り出すテスト

原因を推測する前に、事実を数字で確認します。私はこの2つを最初にやります。

テスト1:SUMとCOUNTの件数を突き合わせる

空いているセルに、この2つを並べて入力してください。

=COUNT(B2:B40)
=COUNTA(B2:B40)

COUNT関数は「数値として認識できるセル」だけを数えます。COUNTA関数は「空白でないセル」を数えます。データが39件入っているのにCOUNTが38、COUNTAが39と表示されたら、その差の1件が計算から漏れている犯人です。合計が合うかどうかを目で追うより、この2つの数の差を見るほうが圧倒的に速いです。

差が0なら、スペースの線は消えます。別の原因を探しに行きましょう。差が出たら、次のテストに進みます。

テスト2:LEN関数を隣の列に流し込む

犯人が何行目にいるのかを特定します。データの隣の空き列に、次の式を入れて下までコピーします。

=LEN(B2)

LEN関数は文字数を返します。「3800」なら4、「12000」なら5になるはずです。ここで1つだけ5や6といった大きい値が並んでいたら、そのセルには余計な文字が紛れ込んでいます。

LEN関数の列で、ほかの行が4文字なのにB38の3800だけ5文字と表示されている表
1つだけ多い5が、スペース入りのセルです

私はこの列に条件付き書式で色を付けてしまうこともあります。「桁数が周りより多いセルだけ塗る」という設定にしておくと、40行でも200行でも、犯人が一瞬で光ります。データを1行ずつ目で追う必要はありません。

なぜスペース1個で計算されなくなるのか

ここで、Excelの中で何が起きているのかを整理しておきます。

Excelはセルの中身を「数値」と「文字列」に分けて管理しています。3800 と入力すれば数値、3800円 と入力すれば文字列です。この判定は入力が確定した瞬間に行われ、判定結果はセルの見た目にはほとんど現れません。

そして、3800␣(後ろにスペース)や ␣3800(前にスペース)は、環境や入力の経緯によって文字列として扱われることがあります。文字列になってしまえば、SUM関数はそのセルを無視します。無視されるだけで、エラーは出ません。

ここが厄介なところです。#VALUE! のような赤いエラーが出てくれれば、誰でも気づけます。ところがSUM関数は「文字列は飛ばして、数値だけ足す」という、Excelとしては完全に正しい仕事をしています。正しい仕事をしているので、警告を出す理由がないのです。

黙って間違った答えを返してくる。これがスペース混入の一番怖い性質です。

見分けるヒントとして、セルの配置も使えます。Excelは初期設定で、数値を右寄せ、文字列を左寄せで表示します。同じ列の中で1つだけ左に寄っている数字があったら、それは高確率で文字列です。ただし、誰かが列全体に「中央揃え」を設定していると、この手がかりは使えなくなります。だからこそ、先ほどのCOUNTとLENのほうが確実です。

伝票No1037の金額3800だけ左寄せで末尾にスペースがあり、SUMの合計が191,100で明細の総額194,900より3,800円少ない表
赤枠の3800だけ左に寄っています

スペースにも種類がある|TRIMで消えるもの・消えないもの

「TRIM関数を使ったのに直らない」という相談を受けることがあります。原因はほぼこれです。私たちが「スペース」と呼んでいるものは、実は1種類ではありません。

種類文字コードよくある出どころTRIM関数で消えるか
半角スペース32手入力、システムのCSV出力消える
全角スペース12288手入力、日本語の資料からのコピペ消えない
改行10セル内でAlt+Enterした跡、メール本文からのコピペ消えない
ノーブレークスペース160Webページ、HTMLからのコピペ消えない
タブ9テキストファイル、システムのログ出力消えない

TRIM関数が処理してくれるのは、原則として半角スペースだけです。Webサイトの表をそのままコピーして貼り付けたデータには、文字コード160番のノーブレークスペースが混ざっていることがよくあります。見た目は半角スペースと区別がつきませんが、TRIMは一切反応しません。

「TRIMを通したのに、まだCOUNTの数が合わない」という状況になったら、半角スペース以外の空白を疑ってください。

なお、全角のスペースが混ざるということは、そのデータには全角の数字も混ざっている可能性があります。金額の一部が全角で入力されていた場合も、同じように合計から漏れます。心当たりのある方は、全角数字が混ざっているときの見分け方と直し方もあわせて確認しておくと安心です。

犯人の正体を1発で特定する2つの式

種類が5つあると分かったところで、「では自分のデータに入っているのはどれなのか」を確定させます。ここまで来れば、あとは作業です。

末尾の文字が何番なのかを見る

=CODE(RIGHT(B2,1))

RIGHT関数でセルの一番右の1文字を取り出し、CODE関数でその文字コードを表示させます。結果が32なら半角スペース、10なら改行、160ならノーブレークスペースです。数字の0〜9であれば48〜57が返るので、その場合は末尾は正常ということになります。

先頭を調べたいときは =CODE(LEFT(B2,1)) に変えるだけです。

スペースが何個入っているのかを数える

=LEN(B2)-LEN(SUBSTITUTE(B2," ",""))

少し長く見えますが、やっていることは単純です。「元の文字数」から「半角スペースを全部消したあとの文字数」を引く。差がそのまま半角スペースの個数になります。1と出れば1個、2と出れば2個です。

" " の部分を全角スペースに書き換えれば、全角スペースの個数も同じ方法で数えられます。「1個だと思っていたら3個入っていた」ということも実際にあるので、直す前に個数を把握しておくと、直したあとの確認が楽になります。

直し方は「件数」と「元データを残すかどうか」で決める

直す方法は複数ありますが、いつも同じやり方を選ぶ必要はありません。私は次の基準で使い分けています。

状況選ぶ方法元データ
数件だけセルを直接手直し上書き
件数が多く、上書きしてよい置換(Ctrl+H)上書き
元データを残したいTRIM+VALUE別列に作る
特殊な空白が混ざっているSUBSTITUTEで狙い撃ち別列に作る
列ごとまとめて数値化したい区切り位置上書き

方法1:数件なら手直しが一番速い

対象が2〜3件だと分かっているなら、小細工は不要です。セルをダブルクリックし、末尾でBackspaceを押し、Enterで確定する。それだけです。

数式を組む時間より手が動くほうが速い場面は、実務では意外と多くあります。LEN関数で犯人の行番号が分かっているなら、その行だけ直せば終わりです。

方法2:件数が多いなら置換で一気に消す

対象範囲を選択した状態で Ctrl+H を押し、置換ダイアログを開きます。

  1. 「検索する文字列」にスペースを1つ入力する
  2. 「置換後の文字列」は空欄のままにする
  3. 「すべて置換」を押す

これで範囲内の半角スペースが消えます。続けて、検索欄に全角スペースを入れてもう一度実行してください。半角と全角は別の文字なので、1回では両方消えません。ここを忘れると「置換したのに直らない」という状態になります。

置換は範囲選択を忘れるとシート全体に効いてしまう点だけ注意してください。商品名の中の意味のあるスペースまで消えてしまうと、後から戻すのが大変です。必ず対象列だけを選んでから実行します。

方法3:元データを残すならTRIM+VALUE

送られてきたファイルを直接書き換えたくない場面もあります。その場合は、隣の列に作業列を用意して、次の式を入れます。

=VALUE(TRIM(B2))

TRIM関数が前後の半角スペースを取り除き、VALUE関数がその結果を数値に変換します。TRIM関数だけだと結果が文字列のまま残ることがあるため、この2つはセットで使うのが安全です。

作業列ができたら、その列をコピーして、同じ場所に「値として貼り付け」で貼り直します。式のままにしておくと、元の列を消したときに一緒に壊れてしまうためです。

VALUE(TRIM())の作業列で3800が数値に戻り、合計が194,900になって明細との差額が0になった表
緑枠の合計が明細の総額と一致しました

方法4:特殊な空白はSUBSTITUTEで狙い撃つ

TRIMで消えないタイプが混ざっていた場合は、文字コードを指定して個別に潰します。

=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),CHAR(160),""),CHAR(10),""))

内側から順に、TRIMで半角スペースを処理し、CHAR(160)のノーブレークスペースを消し、CHAR(10)の改行を消し、最後にVALUEで数値化しています。全角スペースも消したい場合は、SUBSTITUTEをもう一段重ねて、全角スペースを指定してください。

長い式に見えますが、CODE関数で正体が分かっていれば、必要な分だけ書けば済みます。全部盛りにする必要はありません。

方法5:区切り位置で列ごと数値に戻す

数式を使わず、機能だけで解決する方法もあります。

  1. 対象の列を1列まるごと選択する
  2. 「データ」タブの「区切り位置」をクリックする
  3. そのまま「完了」を押す

区切り位置は、本来は1つのセルの中身を複数列に分けるための機能ですが、実行するとExcelがセルの中身をもう一度読み直します。この読み直しの過程で、前後のスペースが落ちて数値として認識し直されることがあります。

作業が一瞬で終わるのが利点ですが、列全体に効くため、隣の列にデータがあるときは押し出されないか確認してから使ってください。

Before / After で確認する

直した結果が正しいかどうかは、最初のテストをもう一度回して確かめます。

確認項目直す前直した後
セルの表示38003800
表示位置左寄せ右寄せ
=LEN(B38)54
=COUNT(B2:B40)3839
=SUM(B2:B40)実際より少ない明細と一致

見た目は直す前後で変わりません。だからこそ、LENとCOUNTの数値が動いたかどうかで判断します。ここまで確認して初めて「直った」と言えます。

直したはずなのに合計が変わらないときは

スペースは消したのに合計が動かない、というときに私が順番に見ているのは次の3つです。

計算方法が「手動」になっている。 「数式」タブの「計算方法の設定」が手動だと、中身を直しても再計算されません。F9キーを押すか、設定を自動に戻してください。他人から受け取ったファイルでは、たまにこの設定のまま届きます。

セルの表示形式が「文字列」のままになっている。 表示形式が文字列のセルは、中身を数字だけにしても数値として扱われません。表示形式を「標準」に変えたうえで、そのセルをダブルクリックしてEnterを押し、入力を確定し直す必要があります。表示形式を変えただけでは切り替わらない点が、引っかかりやすいところです。

そもそもスペース以外の文字が入っている。 「3,800円」のように単位や記号が付いている場合も、まったく同じ現象が起きます。数字と文字が同じセルに同居しているケースについては、数字に単位(円・個)を付けているときの見分け方と直し方で、置換と表示形式を使った直し方をまとめています。

VLOOKUPやCOUNTIFが一致しないときも、犯人は同じ

ここまでは「数字の後ろにスペースが付いて、SUMが黙って計算から外す」話でした。同じ見えない文字が、商品名や取引先名のような文字の列に入ると、今度はVLOOKUPやCOUNTIFが一致しなくなります。合計から漏れるか、#N/Aが返るか。症状が違うだけで、起きていることは同じです。

文字の列で疑ったときも、最初の一手は数字で確かめることです。照合できなかったセルがA2にあるとして、空いているセルに次の2本を入れます。

=LEN(A2)
=LEN(TRIM(CLEAN(A2)))

2つの数字が同じなら、空白や制御文字は入っていません。下のほうが小さければ、見えない文字が混ざっています。もう一押し確定させたいときは、照合先のマスタに対してCOUNTIFを2回打ちます。

=COUNTIF(マスタの範囲,A2)
=COUNTIF(マスタの範囲,TRIM(CLEAN(A2)))

上が0で下が1以上なら、原因は関数の書き方でもマスタの不備でもなく、貼り付けたデータの中の余計な文字です。この切り分けに30秒かけるだけで、数式を何度も書き直す遠回りをしなくて済みます。

空白の位置で、症状の出方が変わる

同じスペース1個でも、文字の前に付くか後ろに付くかで、気づきやすさがまるで違います。

先頭の空白:並べ替えで上に固まる

文字の前に空白があると、その行は並べ替えのたびにリストの先頭(降順なら末尾)に集まります。スペースは文字コードの上で普通の文字より前に位置づけられるためです。五十音順に並べたのに一部だけ妙な位置にいる、というときは、まず先頭の数行をダブルクリックして、カーソルを左端まで動かしてみてください。空白があれば、矢印キーを1回余分に押さないと文字の左端に届きません。

先頭の空白は、手入力のときに変換キーやスペースキーを1回多く押したり、項目名の長さを揃えようとしてスペースで字下げしたりして入ることがほとんどです。見た目を揃えたいだけなら、スペースではなくセルの書式設定にある「インデント」や「均等割り付け」を使えば、データの中身を変えずに表示だけを整えられます。

末尾の空白:見た目には何も起きない

後ろに付いた空白は、文字の位置もずれず、並べ替えにもほとんど影響しません。だから気づくきっかけがなく、VLOOKUPの#N/Aで初めて表に出てきます。末尾の1文字が何者かは、次の式で番号として確かめられます。

=UNICODE(RIGHT(A2,1))

32なら半角スペース、12288なら全角スペース、160ならWebページ由来の特殊な空白です。式が苦手な人向けには、中身をカギ括弧で挟んで見せる方法もあります。

="["&A2&"]"

[りんご ]のように右側に隙間が見えれば、末尾に何か付いています。報告資料に「ここに空白がありました」と貼って見せるときにも使えます。

名前の間の全角スペースは残し、末尾だけ消したいとき

名簿では「山田 太郎」のように、氏名の間に全角スペースを入れていることがよくあります。TRIMは全角スペースを消さないことがあり、置換で全角スペースを一括削除すると、今度は氏名の間まで詰まって「山田太郎」になってしまいます。末尾の1文字だけを狙い撃ちするなら、次の式です。

=IF(RIGHT(A2,1)=" ",LEFT(A2,LEN(A2)-1),A2)

式の中の「 」は全角スペース1文字です。末尾が全角スペースなら最後の1文字を除いた部分を返し、そうでなければそのまま返します。末尾に2つ以上並んでいそうなら、結果をもう一度同じ式に通してください。

スペースではない「見えない文字」がいるとき

TRIMをかけても文字数が減らない。それは、犯人がスペースではないサインです。Webの管理画面やPDF、基幹システムからコピーしたデータには、改行やタブのような制御文字や、特殊な空白が紛れ込みます。実務で出会うものは、ほぼ次の7種類に絞られます。

番号正体よく来る場所TRIMで消えるCLEANで消える
10改行(LF)Web、メール、セル内改行×○
13復帰(CR)基幹システム、テキストファイル×○
9タブテキストファイル、PDF×○
32半角スペースあらゆる場所○×
12288全角スペース日本語の入力フォーム環境による×
160ノーブレークスペースWeb、Word、PDF××
8203ゼロ幅スペースWeb、翻訳ツール経由××

大事なのは右の2列です。TRIMの担当はスペース、CLEANの担当は制御文字で、受け持ちが分かれています。片方だけでは取りこぼすので、迷ったら次の形で両方かけます。

=TRIM(CLEAN(A2))

CLEANが内側です。先に制御文字を落としてから、残ったスペースをTRIMで整える順番にしておかないと、制御文字を消したあとにできた余分なスペースが残ります。コピー由来の不一致は、多くがこの1本で片付きます。

それでも直らないときの相手は、160番か8203番です。この2つはTRIMでもCLEANでも消えないので、SUBSTITUTEで名指しします。

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(160)," "),UNICHAR(8203),"")))

内側から、160番を普通の半角スペースに置き換え、8203番を削除し、最後にCLEANとTRIMで仕上げています。外部データを毎月扱う部署なら、この式を手順書の1ページ目に貼っておく価値があります。

文字列の途中のどこに犯人がいるかまで知りたいときは、1文字ずつ番号に分解します。B列から右へ次の式をコピーすると、1文字目、2文字目……の番号が横に並び、10や160が混ざっている位置が侵入地点です。

=UNICODE(MID($A$2,COLUMN()-1,1))

毎回同じシステムのデータだけおかしい、というときに一度だけ調べておくと、翌月からは最初からその文字を消す式を挟めるようになります。

セル内の改行は、2種類あると思っておく

見えない文字の中でも、改行は特に気づきにくい相手です。誰かが見やすくしようとAlt+Enterで入れた改行は、列幅や折り返しの設定によってはセルの上でまったく見えません。改行があるかどうかは、次の式で白黒つきます。

=IF(ISNUMBER(FIND(CHAR(10),A2)),"改行あり","改行なし")

数百行をまとめて調べるなら、改行を含むセルの件数を1行で出せます。

=SUMPRODUCT(--(LEN(A2:A200)<>LEN(SUBSTITUTE(A2:A200,CHAR(10),""))))

数式を入れる前でも、手がかりはあります。セルをクリックしたときに数式バーが縦に伸びる、短い文字しかないのに「折り返して全体を表示する」だけがオンになっている、その行だけ高さが不自然。どれもAlt+Enterの跡であることが多いサインです。

注文の商品名「ぶどう」にセル内改行が入って2行に折り返され、VLOOKUPが#N/Aになっている表
赤枠の#N/Aは、改行入りの「ぶどう」を探した結果です

CHAR(10)だけ消しても直らないことがある

Alt+Enterで入れた改行はCHAR(10)です。ところが、CSVやメール本文からコピーしたデータには、CHAR(13)が単独で潜んでいることがあります。CHAR(10)を消す式を入れたのにまだ一致しない、というときはこれです。出どころが分からないデータは、最初から両方を消す形で組んでおくと取りこぼしません。

=SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),""),CHAR(13),"")

消すか、置き換えるか、分けるか

改行は、何でも消せばいいわけではありません。そのデータを次に何に使うかで、扱い方を変えます。

  • 照合のキーに使う列:上の式で完全に消す
  • 住所や氏名など人が読む列:空文字ではなく半角スペースに置き換える(=SUBSTITUTE(A2,CHAR(10)," "))。消すと「千代田区1-2-3ビル5階」のように区切りが分からなくなります
  • 「商品名(改行)規格」のように2つの情報が入っている列:消さずに分ける。Microsoft 365なら=TEXTSPLIT(A2,CHAR(10))、それ以外は「区切り位置」で「その他」を選び、入力欄でCtrl+Jを押すと改行が区切り文字になります

関数を使わずにその場で消すなら、Ctrl+Hの置換で「検索する文字列」の欄にCtrl+Jを押す方法があります。画面上は空欄に見えますが、改行コードが入っています。ただし、この改行コードは次に置換ダイアログを開いたときも残っていることがあり、別の置換で「なぜか1件も置換されない」原因になります。作業が終わったら、検索欄をDeleteで空にしておいてください。

もうひとつ注意したいのが、CLEANも改行を消すことです。備考欄や住所欄のように、人が読むために意図して改行している列までまとめて処理すると、レイアウトが崩れます。処理をかけるのは照合に使う列だけ、と決めておけば事故は起きません。

空白も改行も消したのに、まだ一致しないとき

見えない文字を全部落としたのに#N/Aのまま、というときは、原因が別のところにあります。次の表で切り分けてください。

症状疑うべき原因確認する式
TRIMしても文字数が減らない半角スペース以外の文字=UNICODE(RIGHT(A2,1))
見た目は同じなのに一致しない半角カナと全角カナの混在=EXACT(A2,D2)
数字なのに左寄せで表示される数値が文字列になっている=ISTEXT(A2)
検索値を直したのに一致しない参照先の表にも同じ文字が入っている参照先にも=LEN()

EXACTは、2つのセルが1文字残らず同じかどうかをTRUEかFALSEで返します。FALSEなら、目には同じでも別データです。半角カナの「アップル」と全角カナの「アップル」のように、文字の幅だけが違うケースは半角カナと全角カナの違いで一致しないときの直し方で扱っています。

見落としやすいのが4行目です。検索する側だけきれいにしても、参照先のマスタに同じ空白や改行が入っていれば一致しません。照合するなら、両側に同じ処理をかけるのが鉄則です。VLOOKUPの#N/Aそのものの原因をひととおり確認したい場合は、VLOOKUPで#N/Aが消えないときの原因の探し方もあわせてどうぞ。

入れない仕組みを作っておく

毎回あとから直すより、入口で止めるほうが結局は早く済みます。手間の少ない順に3つ挙げます。

1つ目は、入力規則で弾くこと。複数人で入力する表なら、対象範囲に「データの入力規則」→「ユーザー設定」で次の式を入れておきます。前後に空白があるとTRIMした結果と一致しないので、確定する前にエラーで止まります。エラーメッセージに「前後にスペースを入れないでください」と書いておけば、口頭でルールを伝えるより確実です。

=A1=TRIM(A1)

2つ目は、条件付き書式で光らせること。すでにデータが入っている表なら、範囲を選んで「条件付き書式」→「数式を使用して、書式設定するセルを決定」に=A1<>TRIM(A1)を入れて色を付けます。20列あっても1回の設定で、空白付きのセルだけが浮かび上がります。改行を探すなら、式を=ISNUMBER(FIND(CHAR(10),A1))に変えるだけです。

3つ目は、受け入れ用の作業列をテンプレートにしておくこと。毎月同じシステムからデータを取り込むなら、A列に貼り付け、B列に=TRIM(CLEAN(A2))が最初から入っている形にしておきます。貼った瞬間に整形が終わるので、照合の段階でつまずくことがなくなります。CSVをダブルクリックで開いたときに数字が文字扱いになる問題とあわせて対策したい場合は、CSVを開いたら合計が0になるときの取り込み手順も参考にしてください。

現場でよく聞かれること

Q. スペースが入っていることを、事前に防ぐ設定はありませんか。

入力規則を使えば、ある程度は防げます。対象範囲に「データの入力規則」で「整数」や「小数点数」を指定しておくと、スペース付きの値を入力したときにエラーが表示され、確定できなくなります。自分たちで運用する台帳には有効です。ただし、外部から貼り付けたデータには入力規則が効かない場合があるため、外部データについては後から検査するほうが現実的です。

Q. 商品名の途中にあるスペースまで消えてしまわないか心配です。

TRIM関数は、単語と単語の間のスペースは1つだけ残す仕様なので、「エクセル 研究所」のような表記が「エクセル研究所」になってしまうことはありません。一方で、Ctrl+Hの置換はすべてのスペースを消します。金額の列だけを選択してから置換する、という手順を守れば問題ありません。

Q. 合計は直りましたが、同じデータでVLOOKUPも一致しません。

原因は同じです。検索値の側か、参照先の表の側に見えないスペースが残っています。SUM関数では「合計から漏れる」という形で現れ、VLOOKUPでは「#N/Aが返る」という形で現れているだけで、起きていることは同一です。詳しい確認手順はVLOOKUPで#N/Aが消えない原因と対処法にまとめていますので、あわせてご覧ください。

Q. VLOOKUPの式の中でTRIMを使えば、元データを直さなくても済みますか。

=VLOOKUP(TRIM(A2),範囲,2,FALSE)のように書けば、検索値の側の空白は無視して照合できます。ただし、消えるのは検索する側の空白だけです。参照先の表に空白が入っていれば一致しません。急ぎの応急処置としては有効ですが、根本的には元データを直しておくほうが、あとで同じ表を使う人が困りません。

Q. TRIMとCLEANを両方かけても直りません。

160番(ノーブレークスペース)か8203番(ゼロ幅スペース)の可能性が高いです。スペースではない「見えない文字」がいるときで紹介したSUBSTITUTEの式を試してください。それでも直らなければ、空白ではなく半角カナと全角カナの違いなど、文字そのものの違いを疑います。

Q. 改行を含むデータをCSVで保存して開き直したら、1件のデータが2行に割れました。

セルの中の改行が、CSVでは行の区切りとして読まれてしまうためです。外部のシステムに渡すファイルを作るときは、書き出す前にセル内の改行を消しておくのが安全です。

静かな間違いに気づくために

Excelのトラブルで難しいのは、間違いが「静か」なことです。

計算式を間違えれば、たいていはエラーが出ます。エラーが出れば直せます。ところがスペースの混入は、エラーを出さず、それらしい数字を返してきます。合計が0になってくれたほうが、まだ気づけたのに、と思うことすらあります。

冒頭の例で1時間近くかかってしまうのも、関数のほうを何度も見直してしまうからです。数式バーの =SUM(B2:B40) を10回見直しても、答えはそこにはありません。答えはB38の中にあります。

「関数は合っているはずなのに、結果がおかしい」と感じたとき、疑う先を関数からデータに切り替える。この習慣を持てるようになれば、原因の特定にかかる時間はぐっと短くなります。

貼り付けた直後に3手順だけやっておく

外部データを扱う機会が多い方は、直す技術よりも、持ち込まない習慣のほうが結果的に効きます。私がCSVや取引先のファイルを開いたときにやっているのは、次の3つです。

  1. 貼り付けるときは「値のみ貼り付け」を選ぶ。書式やリンクを持ち込まないだけで、後のトラブルがかなり減ります。
  2. 貼り付けた直後に、金額列に対してCOUNTとCOUNTAを1回だけ実行する。数が合っていれば、その日はもう気にしなくて構いません。
  3. 合わなければ、その場でLEN関数を流して犯人の行を特定する。作業が終わってからではなく、貼り付けた直後にやるのがコツです。

慣れれば30秒もかかりません。集計が終わって、資料を作って、上司に提出した後で「合計が違う」と言われるのに比べれば、はるかに安い30秒です。

ここまでの手順は、1本の記事で扱える範囲の「基本形」です。実務では、スペースと全角数字と単位が同じ列に同時に混ざっていたり、毎月届くCSVの列構成が微妙に変わっていたりと、もう一段複雑な状況にぶつかることがあります。そうした複合的なケースで原因を絞り込む手順と、そのまま貼って使えるチェック用の数式は、noteで扱っています。もう一歩踏み込みたい方はこちらをご覧ください。

→ SUM関数が合わない6つの原因を絞り込む診断フロー(note)

まとめ|合計が合わないときの確認順

最後に、今日から使える順番でまとめます。

  • COUNTとCOUNTAの数を比べ、差があるかを確認する
  • 差があったら、LEN関数を隣の列に流して犯人の行を特定する
  • CODE関数で、末尾または先頭の文字コードを確認する
  • 半角スペースならTRIMまたは置換、それ以外ならSUBSTITUTEで消す
  • 直したあと、COUNTの数とセルの寄せ方向が変わったかを確認する

スペースは見えません。見えないので、目で探そうとすると必ず遠回りになります。目ではなく関数に探させる。この切り替えができれば、合計が合わない夜は、確実に減ります。

コメント

タイトルとURLをコピーしました