「関数は合っているのに、なぜか合計が合わない」——このシリーズでは、そんなトラブルの正体を1つずつ掘り下げてきました。今回はその第3回です。
私自身、Excelを使い始めたばかりの頃は、計算が合わないたびに「関数の書き方が間違っているのかな」と思い込み、何度も関数を書き直していました。ですが、Excelに慣れてくると気づくのは、トラブルの多くは関数ではなく「データ」に原因があるということです。
以前、取引先から送られてきた請求データをExcelに貼り付けて合計を出したとき、どうしても金額が合わず、頭を抱えたことがあります。何度計算し直しても数字が合わない。最終的に原因が判明したのですが、それは数字の後ろに入っていた、たった1つの見えないスペースでした。見た目ではまったく分からなかったので、原因にたどり着くまでに、ずいぶん遠回りをしてしまいました。
これまで、数字が文字列になっているケース、全角数字が混ざっているケースを取り上げてきました。今回のテーマは、その中でもとくに見つけにくい「スペースが入っている」ケースです。スペースは見えないことが多いため、Excelトラブルの中でもかなり厄介な原因です。
よくある状況
まずはよくある例を見てみます。次のような売上表を作ったとします。
この表の合計を出すために、次の関数を入力します。
=SUM(B2:B4)普通なら結果は600になるはずです。しかし実際の業務では、合計が正しく表示されない、一部のセルだけ計算されない、SUM関数がうまく動かない、といったことが起きることがあります。
関数も範囲も間違っていないのに、なぜ計算されないのか。そんなときに起きていることが、今回の研究テーマです。
やはり今回も、エラーメッセージは出ません。Excelは「文字列は無視して、数値だけを合計した」というExcelなりに正しい処理をしているだけなので、警告は何も表示されないのです。式が真っ赤なエラーになってくれれば、まだ気づきようがあるのですが、静かに間違った結果を返してくるからこそ、原因の切り分けが難しくなります。
原因：数字の前後にスペースがある
Excelでは、セルの中にスペースが入っていると、文字列データとして扱われることがあります。
例えば、本来は 100 と入力したつもりでも、実際には 100␣（後ろにスペース）になっていることがあります。あるいは ␣100（前にスペース）の場合もあります。この場合、Excelはそのセルを数値ではなく文字列として扱うことがあり、=SUM(B2:B4) と入力しても、そのセルだけ計算から漏れてしまうのです。
つまり、見た目は同じでも、データの中身が違う。これは、これまで取り上げてきた「文字列になっている」「全角数字が混ざっている」とまったく同じ構造の問題です。数字のように見えても、Excelが数値として扱ってくれなければ、合計には含まれない。この原則が、ここでもそのまま当てはまります。
ちなみに、このスペースの問題はSUM関数だけでなく、VLOOKUPなどの検索でも同じように起こります。VLOOKUPで#N/Aが消えない原因としても、見えないスペースは代表的なものとして紹介しています。「見た目は同じなのに一致しない」「見た目は数字なのに合計されない」——これらはすべて、根っこでつながっているトラブルです。
なぜスペースが入るのか
研究員としてExcelトラブルを見ていると、スペースが入る原因にはいくつかパターンがあります。特によくあるのは次の3つです。
① コピペしたデータ
WebサイトやPDFからデータをコピーしてExcelに貼り付けると、見えないスペースが入ることがあります。特に、Webページ、社内システム、メールなどからコピーしたデータは、スペースが入りやすいです。もとのデータで、見た目を整えるために数字の前後に空白が入れられていることがあり、それがそのままコピーされてしまうのです。
② 手入力のときにスペースが入った
入力作業のときに、数字を打ったあと、うっかりスペースキーを押してからEnterを押してしまうことがあります。この場合、セルの中には「数字＋スペース」が入ってしまいます。急いで入力しているときや、キーボードに慣れていないときに起こりやすいミスです。自分ではまったく意識していないため、後から見返しても「なぜこのセルだけ計算されないのか」がなかなか分からず、原因究明に時間がかかりがちです。
③ 社内システムから出力したデータ
社内システムから出力されたCSVデータなどにも、スペースが含まれていることがあります。桁を揃えるために、数字の前に空白を入れて出力する仕様のシステムもあり、これは業務でExcelを使う人がよく遭遇するケースです。
スペースを見つけるヒント
スペースは見えないので、非常に気づきにくいです。そんなときは、次の方法を試してみてください。
① 数式バーを見る
セルをクリックすると、画面上部に数式バーが表示されます。ここを見ると、100␣ のように、数字の後ろに空白があることが分かる場合があります。カーソルを数字の末尾に移動させたとき、数字のすぐ後ろではなく、少し離れた位置にカーソルが止まるようであれば、スペースが入っている可能性が高いです。
② 数字なのに左寄せになっている
Excelには、数字は右寄せ、文字列は左寄せで表示される、というルールがあります。スペースが入っていて文字列扱いになっている場合、数字なのに左寄せになることがあります。これも重要なヒントです。
③ LEN関数で文字数を数える
見た目では判断がつかない場合は、LEN関数で文字数を確認する方法が確実です。
=LEN(B2)例えば「100」なら本来は3文字のはずですが、後ろにスペースが1つ入っていると4文字と表示されます。想定より文字数が多い場合は、スペースなどの余計な文字が紛れ込んでいる、と判断できます。どこにスペースがあるか目視では分からないときに、とても役立つ方法です。対象範囲の隣の列にLEN関数を並べておけば、どの行だけ文字数が多いのかを、ひと目で見比べることもできます。
解決方法
いちばん簡単な方法：手動で削除する
対象が数件であれば、一番シンプルなのは手動で削除することです。
- セルをダブルクリックします。
- スペースを削除します。
- Enterキーを押します。
とても簡単ですが、確実に問題を解決できます。
データが多い場合：TRIM関数を使う
データが大量にある場合、手動で削除するのは大変です。その場合は、TRIM関数を使う方法があります。TRIM関数は、余分なスペースを削除する関数です。
=TRIM(B2)この関数を使うと、文字列の前後のスペースを取り除くことができます。単語と単語の間にあるスペースは1つだけ残し、それ以外の余計なスペースをまとめて削除してくれるので、数字の前後に入り込んだ空白の除去にはうってつけです。元のデータを直接書き換えるのではなく、別のセルにTRIMの結果を出しておけば、万が一のときにも元データを見比べられるので安心です。
ただし、TRIM関数の結果は文字列になることがあります。確実に数値として合計に含めたい場合は、前々回にご紹介したVALUE関数と組み合わせて、次のように書くと安心です。
=VALUE(TRIM(B2))これで、前後のスペースを取り除いたうえで、数値として扱える値が得られます。
見えないスペースが消えない場合
TRIM関数を使っても、なぜかスペースが消えない、というケースもあります。これは、通常の半角スペースではなく、「全角スペース」や「改行コード」「特殊な空白文字」が入り込んでいる場合に起こります。とくに、Webページからコピーしたデータでは、通常のスペースとは異なる特殊な空白文字が混ざっていることがあります。この場合は、CLEAN関数（印刷できない文字を削除する関数）を組み合わせたり、置換機能で全角スペースを直接削除したりする必要があります。「TRIMで消えないスペース」に出会ったら、通常の半角スペース以外の可能性を疑ってみてください。
コピペのときに予防する
そもそもスペースを持ち込まないための予防策も知っておくと便利です。Webサイトや他のファイルからデータをコピーする際に、そのまま貼り付けるのではなく、「形式を選択して貼り付け」から「値のみ」を選ぶと、余計な書式が持ち込まれにくくなります。また、貼り付けた直後に、対象範囲に対して一度TRIM関数やCLEAN関数を通してから、値として貼り直しておく、という一手間を習慣にしておくと、後から「なぜか合計が合わない」というトラブルに悩まされる回数がぐっと減ります。外部からデータを取り込む機会が多い方ほど、この予防策は効果的です。
研究員メモ
Excelで面白いのは、見えないものが原因になることです。今回のスペースも、その一つです。人間には見えないけれど、Excelはちゃんと認識しています。
だからこそ、「関数は合っているのに変だな」と思ったときは、関数ではなくデータを見る。この習慣を持つことがとても大切です。①の文字列、②の全角数字、そして③のスペース。この3回を通してお伝えしてきたのは、結局のところ「見た目ではなく、データの中身を疑う」という、たった一つの視点です。この考え方さえ身につけてしまえば、SUM関数に限らず、あらゆる集計トラブルに落ち着いて対応できるようになります。
まとめ
今回のテーマは「SUM関数が動かない理由③｜スペースが入っている」でした。チェックポイントはこちらです。
- コピペしたデータではないか
- 数字の前後に空白が入っていないか
- 数式バーやLEN関数で確認したか
この3つを、まず確認してみてください。Excelでは、見た目ではなくデータの中身が重要です。この視点を持つと、トラブル解決がかなり早くなります。
スペースは見えないぶん、最初は原因として気づきにくいものですが、一度「合計が合わないときは、まずスペースを疑う」という引き出しを持っておけば、次からは慌てずに対処できます。焦らず、一つずつ確認していきましょう。次回は、数字に「円」や「個」といった単位を付けてしまうケースを取り上げる予定です。これもまた、見た目は数字なのに計算されない、というExcelあるあるの一つです。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。
コメント