「同じ数字のはずなのに、なぜか結果が合わない」——このシリーズも、今回で15回目になります。
私自身、Excelを使い始めたばかりの頃は、SUM関数の合計が微妙にズレたり、比較するとFALSEになったり、見た目は同じなのに一致しなかったりと、原因の分からないトラブルに何度も混乱させられてきました。ですが、Excelを研究していく中で気づいたのは、こうした現象の多くが、Excelの「小数点の扱い」に原因がある、ということでした。
以前、経費の按分計算をしていたとき、「この2つのセルは絶対に同じ金額のはず」と思って =A1=B1 で確認したら、結果がFALSEになって驚いたことがあります。表示上はどちらも「1,250.00」なのに、Excelは別の値だと言い張る。原因は、割り算の過程で生じた、小数点以下ずっと奥のごくわずかな誤差でした。見た目だけを信じていると、こうした落とし穴にはまってしまう、と痛感した出来事です。
これまで①〜⑭では、文字列・空白・文字コード・CSV文字列化など、さまざまな「見えない違い」を研究してきました。今回のテーマは「小数点の形式が違う」ケースです。これはかなり気づきにくいトラブルです。なぜなら、見た目ではほぼ同じに見えるのに、Excelの内部では別の数値として扱われていることがあるからです。今回は、なぜ小数点でズレるのか、内部では何が起きているのか、初心者でもできる解決方法、今後ズレを防ぐコツを、一緒に研究していきましょう。
よくある状況
まずはよくある例を見てみます。次のようなデータがあるとします。
見た目としては、ほとんど同じです。しかしこの状態で、比較・集計・条件判定・システム連携などを行うと、なぜか結果がズレることがあります。
たとえば、次のように2つのセルを比較してみます。
=A1=B1すると、「FALSE」になるケースがあります。「いや、同じ10じゃないの？」と思いますよね。でもExcelの内部では、少し違う処理が行われていることがあるのです。
原因：表示と内部データが違う
ここが、Excelで非常に重要なポイントです。Excelには、「見た目」と「内部データ」の2種類があります。
たとえば「10」と表示されていても、内部では「10.0000000001」のようになっていることがあります。逆に、「10.00」と表示されていても、内部では単純に「10」の場合もあります。つまり、表示＝実際の値ではない、ということです。
この「見た目と中身は違う」という考え方は、実はこのシリーズを通してずっと共通しているテーマです。数値の形式がバラバラで集計がズレるケースについては、以前の記事でもくわしく取り上げていますので、あわせて確認しておくと理解が深まります。数値の形式が統一されていないケースと、今回の小数点の問題は、根っこでつながっています。
なぜこんなことが起きるのか
Excelは、非常に細かく計算しています。とくに、小数計算、割り算、他システムからのデータ、CSVデータ、関数の連続計算などでは、内部で細かい誤差が発生します。
有名な例がこちらです。
0.1 + 0.2数学では当然「0.3」になります。しかしExcelでは、内部的に「0.30000000000000004」のようになることがあります。
これは、Excelのバグではありません。コンピューター全般で起きる「浮動小数点誤差」と呼ばれる現象です。コンピューターは、小数を2進数で表現しているため、0.1や0.2のような一部の小数を、完全には表現できません。そのため、ごくわずかな誤差が生じてしまうのです。人間の感覚では「たかが小数点以下の誤差」と思いがちですが、Excelは、この極小の違いも「別の値」として厳密に区別します。
Excel初心者がハマりやすい場面
研究員として、とくによく見るのは次の4つの場面です。
① IF関数で一致しない
=IF(A1=B1, "一致", "不一致") を使ったとき、見た目は同じ数字なのに「不一致」になることがあります。これは、内部の小数が違うためです。
② VLOOKUPで見つからない
=VLOOKUP(A2, 範囲, 2, FALSE) で検索しても、「#N/A」になることがあります。原因は、検索値とデータの小数点以下が微妙に違うからです。VLOOKUPで#N/Aが出るときは、空白や文字コードなど、他にもいくつか原因が考えられます。VLOOKUPで#N/Aが消えない原因については、以前の記事でくわしく取り上げていますので、あわせて確認すると、原因の切り分けがしやすくなります。
③ SUMの合計がズレる
大量データでは、「0.01」のような小さい誤差が積み重なります。すると、「9999.99」になるはずが、「10000.0000002」のようになることがあります。1件あたりの誤差はごくわずかでも、件数が多いと無視できない差になることがあります。
④ グラフや集計がおかしい
ピボットテーブルやグラフでも、内部の誤差によって、分類がおかしくなる、集計がズレる、同じ数値が別扱いになる、といったことが起こります。
見分けるポイント
次のような違和感があれば、要注意です。
① 見た目は同じなのに一致しない
かなり典型的なサインです。目で見て同じ数字なのに、関数の結果が食い違うときは、小数点の違いを疑ってみてください。
② 比較結果がFALSEになる
=A1=B1 でFALSEが出たら、内部の値が違う証拠です。この式は、小数点の違いを見つけるための、手軽で確実な方法です。
③ 集計結果が少しズレる
小数が絡む集計で、想定と数円・数銭の差が出るときは、浮動小数点誤差が積み重なっている可能性があります。
④ ROUNDしていない計算式が長い
計算式が長く連なっている場合、途中の誤差が最後まで持ち越されて増えていきます。「一度も丸めずに計算し続けている」というのは、誤差が溜まりやすい状態です。
解決方法：ROUND関数で丸める
一番おすすめなのは、ROUND関数です。
=ROUND(A2, 2)これは、小数第2位で丸める関数です。たとえば「0.300000000004」を「0.30」に整えてくれます。表示だけでなく、実際の値そのものを丸めてくれるのがポイントです。
ROUND関数の基本
桁数の指定によって、丸め方を変えられます。小数第2位までなら =ROUND(A2, 2)、整数にするなら =ROUND(A2, 0)、小数第1位までなら =ROUND(A2, 1) と書きます。2つ目の引数（桁数）を変えるだけで、どの位で丸めるかを自由に指定できます。
なお、ROUNDは四捨五入ですが、用途によっては切り捨てや切り上げが必要なこともあります。切り捨てなら =ROUNDDOWN(A2, 0)、切り上げなら =ROUNDUP(A2, 0) を使います。たとえば、消費税の計算では切り捨てを指定されることが多いなど、業務によって丸め方のルールが決まっている場合があります。「とりあえずROUND」ではなく、その計算にふさわしい丸め方を選ぶことも、正確な集計のためには大切です。
ROUNDを使うべき場面
研究員として、次の場面ではROUNDを強くおすすめします。まず、請求書や経費精算などの金額計算です。1円のズレも許されない場面では、必ず丸めておきたいところです。次に、消費税や比率などの割合計算です。割り算は誤差が出やすいため、要注意です。そして、IF関数やVLOOKUPで比較する前に丸めておくと、判定が安定します。最後に、CSVやシステム連携でも、システム間で誤差が出やすいため、ROUNDで整えておくと安心です。CSVから取り込んだデータでの数値トラブルについては、以前の記事でもくわしく取り上げています。CSV取り込みで文字列になるケースもあわせて確認しておくと、外部データを扱うときのトラブルに強くなれます。
表示形式だけではダメな理由
初心者の頃によくあるのが、「小数点を非表示にしたからOK」という勘違いです。実は、これは危険です。
なぜなら、表示を変えただけでは、内部データはそのまま残っているからです。たとえば「0.30000000004」を小数2桁表示にしても、内部ではそのままの値が残り続けます。つまり、「見えなくなっただけ」で、比較や集計をすると、やはり誤差が影響してしまいます。
だからこそ、ROUND関数で実際に値を整えることが重要です。表示形式は「見た目を変えるだけ」、ROUND関数は「中身を変える」——この違いをはっきり理解しておくと、どちらを使うべきかで迷わなくなります。計算や照合に使う値はROUNDで丸める、というのが基本の考え方です。
研究員メモ
Excel初心者の頃の私は、「同じ数字なのに、なんで違うの？」と、かなり混乱していました。でもExcelを長く使ううちに分かってきたのは、Excelは見た目ではなく内部データで判断している、ということです。
人間から見ると同じでも、Excelから見ると、小数が少し違う、桁数が違う、形式が違う、というだけで別データになります。①の文字列、②の全角数字、③のスペース、④の単位、⑤のアポストロフィ、⑥の数字と文字の混在、⑦の日付、⑧の先頭の空白、⑨の末尾の空白、⑩の見えない文字、⑪の改行、⑫の形式の不統一、⑬の文字コード、⑭のCSV文字列化、そして今回⑮の小数点。ここまで15回にわたってお伝えしてきましたが、共通しているのは「見た目ではなく、データの中身に原因がある」という一点です。だからこそ大切なのが、「数値を揃える」という考え方です。この意識を持つだけで、Excelのトラブルはかなり減ります。
まとめ
今回のテーマは「計算結果がズレる理由⑮｜小数点の形式が違う」でした。ポイントをまとめます。
- チェックポイント：比較結果がFALSE／SUMが微妙にズレる／一致検索できない／集計結果が違う
- 原因：表示ではなく、内部の小数値が違う（浮動小数点誤差）
- 解決方法：
=ROUND(セル, 桁数)で実際の値を丸めて揃える
いちばん大切なのは、Excelでは見た目と内部データは違う、ということを覚えておくことです。これを意識するだけでも、小数点まわりのトラブルにかなり強くなれます。
もし今、小数点の誤差で集計や比較がズレていても、慌てる必要はありません。ROUND関数で値を丸めて揃えれば、計算も比較も安定した状態へと整えることができます。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。次回からは新しいテーマとして、表の作り方のミス⑯「空白行を入れる」を取り上げる予定です。見やすくするために入れた空白行が、実は集計や並び替え、フィルターを壊していることがある、というお話です。
コメント