金曜の夕方、取引先から届いた請求データをExcelに貼り付けて合計を出したときのことです。先方の明細に書かれた総額と、私が出した合計が、どうしても3,800円だけ合いませんでした。
関数は =SUM(B2:B40) の一行だけ。範囲もきちんと選べている。それでも合わない。電卓で叩き直しても、私の合計のほうが少ない。結局、原因にたどり着いたのは1時間近く経ってからでした。犯人は、38行目の金額の後ろに入っていた、たった1個のスペースです。
画面上は「3800」と表示されているのに、Excelから見ればそれは数字ではありませんでした。この記事では、そのときに私が遠回りした手順を全部省いて、最短で犯人を捕まえる方法からお伝えします。
なお、合計が合わないトラブルには「数字がそもそも文字列として入力されている」という、もっと頻度の高い原因もあります。まだ確認していない方は、SUM関数が計算されない一番多い原因は「数字の文字列化」を先に読んでいただくと、切り分けが早くなります。
まず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といった大きい値が並んでいたら、そのセルには余計な文字が紛れ込んでいます。
私はこの列に条件付き書式で色を付けてしまうこともあります。「桁数が周りより多いセルだけ塗る」という設定にしておくと、40行でも200行でも、犯人が一瞬で光ります。データを1行ずつ目で追う必要はありません。
なぜスペース1個で計算されなくなるのか
ここで、Excelの中で何が起きているのかを整理しておきます。
Excelはセルの中身を「数値」と「文字列」に分けて管理しています。3800 と入力すれば数値、3800円 と入力すれば文字列です。この判定は入力が確定した瞬間に行われ、判定結果はセルの見た目にはほとんど現れません。
そして、3800␣（後ろにスペース）や ␣3800（前にスペース）は、環境や入力の経緯によって文字列として扱われることがあります。文字列になってしまえば、SUM関数はそのセルを無視します。無視されるだけで、エラーは出ません。
ここが厄介なところです。#VALUE! のような赤いエラーが出てくれれば、誰でも気づけます。ところがSUM関数は「文字列は飛ばして、数値だけ足す」という、Excelとしては完全に正しい仕事をしています。正しい仕事をしているので、警告を出す理由がないのです。
黙って間違った答えを返してくる。これがスペース混入の一番怖い性質です。
見分けるヒントとして、セルの配置も使えます。Excelは初期設定で、数値を右寄せ、文字列を左寄せで表示します。同じ列の中で1つだけ左に寄っている数字があったら、それは高確率で文字列です。ただし、誰かが列全体に「中央揃え」を設定していると、この手がかりは使えなくなります。だからこそ、先ほどのCOUNTとLENのほうが確実です。
スペースにも種類がある｜TRIMで消えるもの・消えないもの
「TRIM関数を使ったのに直らない」という相談を受けることがあります。原因はほぼこれです。私たちが「スペース」と呼んでいるものは、実は1種類ではありません。
| 種類 | 文字コード | よくある出どころ | TRIM関数で消えるか |
|---|---|---|---|
| 半角スペース | 32 | 手入力、システムのCSV出力 | 消える |
| 全角スペース | 12288 | 手入力、日本語の資料からのコピペ | 消えない |
| 改行 | 10 | セル内でAlt+Enterした跡、メール本文からのコピペ | 消えない |
| ノーブレークスペース | 160 | Webページ、HTMLからのコピペ | 消えない |
| タブ | 9 | テキストファイル、システムのログ出力 | 消えない |
TRIM関数が処理してくれるのは、原則として半角スペースだけです。Webサイトの表をそのままコピーして貼り付けたデータには、文字コード160番のノーブレークスペースが混ざっていることがよくあります。見た目は半角スペースと区別がつきませんが、TRIMは一切反応しません。
「TRIMを通したのに、まだCOUNTの数が合わない」という状況になったら、半角スペース以外の空白を疑ってください。
なお、全角のスペースが混ざるということは、そのデータには全角の数字も混ざっている可能性があります。金額の一部が全角で入力されていた場合も、同じように合計から漏れます。心当たりのある方は、全角数字が原因でSUM関数が計算されない時の見分け方と直し方もあわせて確認しておくと安心です。
犯人の正体を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回では両方消えません。ここを忘れると「置換したのに直らない」という状態になります。
置換は範囲選択を忘れるとシート全体に効いてしまう点だけ注意してください。商品名の中の意味のあるスペースまで消えてしまうと、後から戻すのが大変です。必ず対象列だけを選んでから実行します。
方法3：元データを残すならTRIM＋VALUE
送られてきたファイルを直接書き換えたくない場面もあります。その場合は、隣の列に作業列を用意して、次の式を入れます。
=VALUE(TRIM(B2))TRIM関数が前後の半角スペースを取り除き、VALUE関数がその結果を数値に変換します。TRIM関数だけだと結果が文字列のまま残ることがあるため、この2つはセットで使うのが安全です。
作業列ができたら、その列をコピーして、同じ場所に「値として貼り付け」で貼り直します。式のままにしておくと、元の列を消したときに一緒に壊れてしまうためです。
方法4：特殊な空白はSUBSTITUTEで狙い撃つ
TRIMで消えないタイプが混ざっていた場合は、文字コードを指定して個別に潰します。
=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),CHAR(160),""),CHAR(10),""))内側から順に、TRIMで半角スペースを処理し、CHAR(160)のノーブレークスペースを消し、CHAR(10)の改行を消し、最後にVALUEで数値化しています。全角スペースも消したい場合は、SUBSTITUTEをもう一段重ねて、全角スペースを指定してください。
長い式に見えますが、CODE関数で正体が分かっていれば、必要な分だけ書けば済みます。全部盛りにする必要はありません。
方法5：区切り位置で列ごと数値に戻す
数式を使わず、機能だけで解決する方法もあります。
- 対象の列を1列まるごと選択する
- 「データ」タブの「区切り位置」をクリックする
- そのまま「完了」を押す
区切り位置は、本来は1つのセルの中身を複数列に分けるための機能ですが、実行するとExcelがセルの中身をもう一度読み直します。この読み直しの過程で、前後のスペースが落ちて数値として認識し直されることがあります。
作業が一瞬で終わるのが利点ですが、列全体に効くため、隣の列にデータがあるときは押し出されないか確認してから使ってください。
Before / After で確認する
直した結果が正しいかどうかは、最初のテストをもう一度回して確かめます。
| 確認項目 | 直す前 | 直した後 |
|---|---|---|
| セルの表示 | 3800 | 3800 |
| 表示位置 | 左寄せ | 右寄せ |
| =LEN(B38) | 5 | 4 |
| =COUNT(B2:B40) | 38 | 39 |
| =SUM(B2:B40) | 実際より少ない | 明細と一致 |
見た目は直す前後で変わりません。だからこそ、LENとCOUNTの数値が動いたかどうかで判断します。ここまで確認して初めて「直った」と言えます。
直したはずなのに合計が変わらないときは
スペースは消したのに合計が動かない、というときに私が順番に見ているのは次の3つです。
計算方法が「手動」になっている。 「数式」タブの「計算方法の設定」が手動だと、中身を直しても再計算されません。F9キーを押すか、設定を自動に戻してください。他人から受け取ったファイルでは、たまにこの設定のまま届きます。
セルの表示形式が「文字列」のままになっている。 表示形式が文字列のセルは、中身を数字だけにしても数値として扱われません。表示形式を「標準」に変えたうえで、そのセルをダブルクリックしてEnterを押し、入力を確定し直す必要があります。表示形式を変えただけでは切り替わらない点が、引っかかりやすいところです。
そもそもスペース以外の文字が入っている。 「3,800円」のように単位や記号が付いている場合も、まったく同じ現象が起きます。数字と文字が同じセルに同居しているケースについては、数字に単位（円・個）を付けているときの見分け方と直し方で、置換と表示形式を使った直し方をまとめています。
現場でよく聞かれること
Q. スペースが入っていることを、事前に防ぐ設定はありませんか。
入力規則を使えば、ある程度は防げます。対象範囲に「データの入力規則」で「整数」や「小数点数」を指定しておくと、スペース付きの値を入力したときにエラーが表示され、確定できなくなります。自分たちで運用する台帳には有効です。ただし、外部から貼り付けたデータには入力規則が効かない場合があるため、外部データについては後から検査するほうが現実的です。
Q. 商品名の途中にあるスペースまで消えてしまわないか心配です。
TRIM関数は、単語と単語の間のスペースは1つだけ残す仕様なので、「エクセル 研究所」のような表記が「エクセル研究所」になってしまうことはありません。一方で、Ctrl+Hの置換はすべてのスペースを消します。金額の列だけを選択してから置換する、という手順を守れば問題ありません。
Q. 合計は直りましたが、同じデータでVLOOKUPも一致しません。
原因は同じです。検索値の側か、参照先の表の側に見えないスペースが残っています。SUM関数では「合計から漏れる」という形で現れ、VLOOKUPでは「#N/Aが返る」という形で現れているだけで、起きていることは同一です。詳しい確認手順はVLOOKUPで#N/Aが消えない原因と対処法にまとめていますので、あわせてご覧ください。
研究員メモ
Excelのトラブルで難しいのは、間違いが「静か」なことです。
計算式を間違えれば、たいていはエラーが出ます。エラーが出れば直せます。ところがスペースの混入は、エラーを出さず、それらしい数字を返してきます。合計が0になってくれたほうが、まだ気づけたのに、と思うことすらあります。
私が請求データで1時間溶かしたのも、関数のほうを何度も見直していたからでした。数式バーの =SUM(B2:B40) を10回見直しても、答えはそこにはありません。答えはB38の中にありました。
「関数は合っているはずなのに、結果がおかしい」と感じたとき、疑う先を関数からデータに切り替える。この習慣を持てるようになってから、原因の特定にかかる時間は、体感で10分の1くらいになりました。
貼り付けた直後に3手順だけやっておく
外部データを扱う機会が多い方は、直す技術よりも、持ち込まない習慣のほうが結果的に効きます。私がCSVや取引先のファイルを開いたときにやっているのは、次の3つです。
- 貼り付けるときは「値のみ貼り付け」を選ぶ。書式やリンクを持ち込まないだけで、後のトラブルがかなり減ります。
- 貼り付けた直後に、金額列に対してCOUNTとCOUNTAを1回だけ実行する。数が合っていれば、その日はもう気にしなくて構いません。
- 合わなければ、その場でLEN関数を流して犯人の行を特定する。作業が終わってからではなく、貼り付けた直後にやるのがコツです。
慣れれば30秒もかかりません。集計が終わって、資料を作って、上司に提出した後で「合計が違う」と言われるのに比べれば、はるかに安い30秒です。
ここまでの手順は、1本の記事で扱える範囲の「基本形」です。実務では、スペースと全角数字と単位が同じ列に同時に混ざっていたり、毎月届くCSVの列構成が微妙に変わっていたりと、もう一段複雑な状況にぶつかることがあります。そうした複合的なケースへの対処や、そのまま貼って使えるチェック用の数式テンプレートについては、note会員限定のコンテンツで扱っています。もう一歩踏み込みたい方はこちらをご覧ください。
→ もっと実務寄りの話を知りたい方へ｜note会員限定コンテンツのご案内
まとめ｜合計が合わないときの確認順
最後に、今日から使える順番でまとめます。
- COUNTとCOUNTAの数を比べ、差があるかを確認する
- 差があったら、LEN関数を隣の列に流して犯人の行を特定する
- CODE関数で、末尾または先頭の文字コードを確認する
- 半角スペースならTRIMまたは置換、それ以外ならSUBSTITUTEで消す
- 直したあと、COUNTの数とセルの寄せ方向が変わったかを確認する
スペースは見えません。見えないので、目で探そうとすると必ず遠回りになります。目ではなく関数に探させる。この切り替えができれば、合計が合わない夜は、確実に減ります。
コメント