「同じ数字のはずなのに、なぜか結果が合わない」——このシリーズも、今回で12回目になります。
私自身、Excelを使い始めたばかりの頃は、COUNTIFの結果がなぜかズレたり、並び替えをすると順番がおかしくなったりと、原因の分からないトラブルに何度も悩まされてきました。初心者のころは「関数の使い方を間違えているのかも」と思い、何度も式を書き直していましたが、実際には、原因の多くは関数ではなくデータの中身にありました。
以前、複数の担当者が入力したアンケート結果を集計したとき、「満足度5」の件数をCOUNTIFで数えたところ、明らかに実際より少ない数字になってしまったことがあります。原因を探ると、一部の担当者だけが数字を全角で入力していて、Excelがそれを別のデータとして扱っていたのです。見た目ではまったく区別がつかなかったので、原因にたどり着くまで、かなり遠回りをしてしまいました。
これまで①〜⑪では、文字列・空白・改行など「見えない違い」によるトラブルを研究してきました。今回のテーマは「数値の形式が統一されていない」ケースです。これは一見すると気づきにくいのですが、実務では非常によく起きるトラブルです。なぜなら、見た目が同じでも中身が違うからです。では、一緒に研究していきましょう。
よくある状況
まずはよくある例を見てみます。次のような数値のデータがあるとします。
見た目としては、すべて「100」に見えます。この状態で、たとえば次のようにCOUNTIF関数を使って、「100」がいくつあるかを数えてみます。
=COUNTIF(A2:A4, 100)本来であれば「3」とカウントされそうですが、思った通りの結果にならないことがあります。
「全部100なのに、なぜ？」——ここで起きているのが、今回の問題です。
原因：Excelの中では別のデータになっている
先ほどの3つの「100」を、Excelがどう見ているかを整理してみます。
とくに注意したいのが、全角の「１００」です。これはExcelでは文字データとして扱われます。つまりこの表は、実際には「数値・数値・文字」が混ざっている状態なのです。
もう一つ見落としやすいのが、「100」と「100.0」の違いです。この2つはどちらも数値なので、SUMなどの計算では問題なく足し算できます。ところが、COUNTIFで「100と完全に一致するもの」を数えようとすると、環境や設定によっては、100.0が別物として扱われることがあります。見た目が「100」でも、小数点以下の情報を持っているかどうかで、判定結果が変わってしまうことがあるのです。
Excelはこの違いを厳密に判断します。そのため、COUNTIFで正しく数えられない、SUMで集計がおかしくなる、並び替えの順序が崩れる、といった問題が発生します。
全角数字が文字扱いになる、という点については、以前の記事でもくわしく取り上げました。全角数字が混ざっているケースと、今回の「形式の不統一」は、根っこでつながっている問題です。
なぜ起きるのか
研究員として、現場でよく見る原因は主に次の3つです。
① 手入力による混在
複数の人が入力に関わっていると、半角の「100」と全角の「１００」が混ざってしまうことがあります。とくに、日本語入力をオンにしたまま数字を打つと、うっかり全角になってしまうため、入力する人によって形式がバラバラになりがちです。
② コピペデータ
WebサイトやPDFからデータをコピーすると、全角の数字や、文字扱いのデータが混ざることがあります。もとのデータの形式がそのまま持ち込まれるため、自分の表の中で形式がバラバラになってしまうのです。
③ 表示形式の違い
見た目は同じ「100」でも、セルの設定が「数値」になっているものと「文字列」になっているものが混在しているケースです。とくに、数字と文字が同じセルに入っていた名残で、文字列扱いのままになっていることもあります。数字と文字が混ざっているケースも、この「形式の不統一」を引き起こす原因の一つになります。
見分けるポイント
数値の形式がバラバラな場合、次のような違和感が出ます。
① COUNTIFの結果が合わない／SUMの合計がズレる
「3件あるはずなのに2件」「合計が想定より少ない」といったズレは、形式の不統一を疑う代表的なサインです。
② 並び替えの順番が変
数値と文字が混ざっていると、並び替えの順序がおかしくなります。数値は数値どうし、文字は文字どうしで並ぶため、意図した順番にならないのです。
③ 数字なのに左寄せになっている
これがもっとも重要なサインです。Excelでは、数値は右寄せ、文字は左寄せで表示されます。数字なのに左寄せになっているセルは、「文字扱い」になっている証拠です。表全体をざっと見て、他のセルと揃っていない数字がないかを確認してみてください。
④ ISNUMBER関数で確認する
確実に判断したい場合は、別のセルに =ISNUMBER(A2) と入力してみてください。数値であればTRUE、文字扱いであればFALSEが返ってきます。範囲が広い場合は、隣の列にこの式を並べておくと、どのセルが文字扱いになっているかを一目で把握できます。
解決方法
いちばんシンプルな方法：VALUE関数
もっともシンプルなのは、VALUE関数です。
=VALUE(A2)VALUE関数は、文字を数値に変換する関数です。これにより、文字扱いだった「１００」を、計算に使える数値の「100」に変換できます。
実務でよく使う：ダブルマイナス
もう一つ、実務でよく使われるのが「ダブルマイナス」と呼ばれる方法です。
=--A2マイナスを2つ重ねると、内部的に数値への変換が行われ、VALUE関数と同じ効果が得られます。式が短く済むため、慣れると手早く処理できる方法です。ただし、初心者のうちは、意味が分かりやすいVALUE関数を使うのがおすすめです。
全角が原因の場合：ASC関数と組み合わせる
全角数字が原因の場合は、ASC関数（全角を半角に変換する関数）と組み合わせると、より確実です。
=VALUE(ASC(A2))こうすると、全角を半角に直したうえで数値に変換できます。「VALUE関数だけでは、なぜかエラーになる」というときは、この組み合わせを試してみてください。
まとめて修正する方法
データが多い場合は、次の流れがおすすめです。まず、新しい列に変換式（VALUEなど）を入力し、下までコピーします。次に、その列全体をコピーし、「形式を選択して貼り付け」から「値」を選んで貼り付けます。最後に、元データの列を削除すれば、数値の形式を一括で統一できます。関数のままだと、元の列を消したときに結果も消えてしまうため、「値として貼り付ける」という一手間が大切です。
このとき、変換した結果が本当に数値になっているかを、念のため確認しておくと安心です。変換後の列を選択したときに、画面右下のステータスバーに「合計」や「平均」が表示されれば、正しく数値として認識できている証拠です。もし「データの個数」しか表示されない場合は、まだ文字扱いのままである可能性があるため、変換方法を見直してみてください。
さらに一歩：再発を防ぐ考え方
ここで、一つ重要なポイントをお伝えします。それは、Excelでは見た目ではなく中身がすべてである、ということです。
人間は「100」と見れば、どれも同じだと判断します。しかしExcelは、数値なのか、文字なのか、整数なのか小数なのか、といった「形式」を厳密に区別します。この違いを理解しておくだけで、形式の不統一によるトラブルの多くは、未然に防げるようになります。
再発を防ぐには、入力の段階でルールを決めておくのが効果的です。たとえば、数値を入力する列には「入力規則」で「整数」や「小数」だけを許可するように設定しておけば、全角数字や文字が混ざること自体を防げます。複数人で入力するファイルであれば、こうした仕組みでの予防が、あとから形式を統一する手間を大きく減らしてくれます。
研究員メモ
ここまでのシリーズで共通しているのは、ズレの原因は「見えない違い」にある、という点です。今回のケースでは、全角と半角、数値と文字、小数と整数という、複数の「形式の違い」が混在していました。
初心者のうちは「関数が難しい」と感じてしまいがちですが、実際にはデータの整え方が重要です。①の文字列、②の全角数字、③のスペース、④の単位、⑤のアポストロフィ、⑥の数字と文字の混在、⑦の日付、⑧の先頭の空白、⑨の末尾の空白、⑩の見えない文字、⑪の改行、そして今回⑫の形式の不統一。ここまで12回にわたってお伝えしてきましたが、原因はどれも「見た目ではなく、データの中身にある」という一点に集約されます。この意識を持つだけで、Excelの理解は一段レベルアップします。
なお、見た目が同じ文字なのに、文字コードのレベルで別物として扱われるケースもあります。文字コードの違いで一致しないケースについては、次回くわしく取り上げる予定ですので、あわせて確認しておくと、形式まわりのトラブルにより強くなれます。
まとめ
今回のテーマは「計算や集計がズレる理由⑫｜数値の形式が統一されていない」でした。ポイントをまとめます。
- チェックポイント：全角と半角が混ざっている／数値と文字が混在している／数字なのに左寄せ
- 原因：数値の形式（半角・全角・数値・文字・小数・整数）がバラバラになっている
- 解決方法：
=VALUE(セル)または=--セルで数値に変換して統一する
いちばん大切なのは、集計や並び替えがおかしいときは、数値の形式を疑う、という視点です。とくに「数字なのに左寄せ」を見つけたら、文字扱いのサインだと考えて、VALUE関数などで整えてみてください。
もし今、形式がバラバラのデータがあっても、慌てる必要はありません。VALUE関数やダブルマイナスを使えば、見た目はそのままに、正しく集計できる状態へと整えることができます。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。次回は、見た目は同じ文字なのに内部では別物として扱われる「文字コード違いで文字になる」ケースを取り上げる予定です。
コメント