「関数は合っているのに、なぜか合計が合わない」——前回に続き、今回もそんなトラブルの正体を掘り下げていきます。
私自身、Excelを使い始めたばかりの頃は、計算が合わないたびに「関数の書き方が間違っているのかな」と思い込み、何度も関数を入力し直していました。ですが、Excelに慣れてくると気づくのは、トラブルの多くは関数ではなく「データ」に原因があるということです。
以前、複数人で入力を分担した名簿の集計を任されたとき、どうしても人数の合計が合わず、原因を探すのに苦労したことがあります。よくよく確認すると、一部の担当者だけが数字を全角で入力していたのです。見た目ではまったく気づけなかったため、原因が分かったときには思わず脱力してしまいました。それくらい、全角数字の混在は見つけにくいトラブルです。
前回は「数字が文字列になっている」ケースを取り上げました。数字が文字列になっているときの見分け方は、今回のテーマとも深くつながっているので、あわせて読んでおくと理解が早くなります。今回の研究テーマは、SUM関数が計算されない原因の中でもとくに気づきにくい「全角数字が混ざっている」ケースです。
よくある状況
まずは、よくある例を見てみましょう。次のような売上表を作ったとします。
この表を見ると、特に問題はないように見えます。そして合計を出すために、次の関数を入力します。
=SUM(B2:B4)通常であれば結果は600になるはずです。しかし実際には、正しい合計にならないという現象が起きることがあります。例えば、合計が400になる、一部だけ計算される、計算されないセルがある、などです。
「関数は合っているのに、なぜ？」——このときに起きていることが、今回の研究テーマです。
厄介なのは、エラーメッセージが一切出ないことです。もし数式の書き方が間違っていれば、Excelは「#NAME?」などのエラーを表示して教えてくれます。ところが全角数字の場合、Excelは「文字列は無視して、数値だけを合計した」という、Excelなりに正しい処理をしているだけなので、警告は何も出ません。だからこそ、合計欄の数字がおかしいことに、自分で気づくしかないのです。
原因：全角数字が混ざっている
Excelでは、数字には半角と全角の2種類があります。
- 半角数字：
100 - 全角数字：
１００
人間の目には、ほとんど同じに見えます。しかしExcelの中では、まったく別のデータとして扱われることがあります。全角で入力された数字は、多くの場合そのまま「文字列」として認識されるため、数値としての計算対象から外れてしまうのです。
先ほどの表でいえば、Bの「２００」だけが全角で入力されていた場合、この行だけが計算から漏れて、合計が400になってしまう、というわけです。つまり、見た目は同じでも、Excelの中身は違う。これがExcel初心者がよくハマるポイントです。
原因の根っこは、前回取り上げた「文字列になっている」問題と同じです。全角数字は、文字列扱いになる数値の一種、と捉えておくと分かりやすいと思います。見た目が数字であることと、Excelが数値として扱ってくれることは、まったくの別問題なのだということを、あらためて意識しておきたいところです。
なぜ全角数字が混ざるのか
では、なぜ全角数字が混ざってしまうのでしょうか。主な原因は次の3つです。
① 手入力
日本語入力がオンの状態で数字を入力すると、全角数字になることがあります。例えば「１００」のように入力されてしまうケースです。特に、日本語入力がオンのまま、テンキーを使わずに入力している場合に起きやすくなります。本人は半角で打ったつもりでも、変換のタイミングで全角のまま確定してしまっている、ということも少なくありません。
② WebサイトやPDFからのコピペ
WebサイトやPDFなどからデータをコピーすると、全角数字が混ざることがあります。見た目では分からないため、そのままExcelに貼り付けてしまうケースが多いです。とくに、複数の人が入力に関わっている資料や、外部の資料から数字を引用してくる場面では、全角と半角が入り混じった状態になりやすくなります。
③ 社内システムから出力したデータ
会社のシステムからCSVやExcelデータを出力すると、全角数字が混ざっていることがあります。これは意外と多い原因です。SUMIFSで数字が合わないというトラブルでも、条件に使う数値の形式がズレていることが原因になりますが、その「形式のズレ」の中には、この全角・半角の違いも含まれます。SUM関数だけでなく、条件付き集計でも同じ落とし穴があると覚えておくと安心です。
見分けるポイント
全角数字は見た目が似ているため、気づきにくいのが特徴です。しかし、よく見ると次のような違いがあります。
① 横幅が少し広い
半角の「100」と全角の「１００」を並べてみると、全角の方が横幅が少し広いのが特徴です。慣れてくると、この違和感に気づけるようになります。1つのセルの中に半角と全角が混在していると、文字の並びが不自然に見えることもあるので、そうした違和感も見分けのヒントになります。
② 数字なのに左寄せになる
Excelには、数字は右寄せ、文字列は左寄せで表示される、というルールがあります。全角数字は文字列扱いになることがあるため、数字なのに左寄せになることがあります。これは分かりやすいサインなので、表全体をざっと見て、他のセルと揃っていない数値がないかを確認してみてください。
③ ISNUMBER関数で確認する
見た目での判断に自信が持てない場合は、別のセルに次の関数を入力して確認する方法が確実です。
=ISNUMBER(B3)対象のセルが数値であればTRUE、全角数字などで文字列扱いになっていればFALSEが返ってきます。対象範囲の隣の列にこの式をコピーしておけば、どのセルが計算対象から漏れているかを一目で洗い出せます。
解決方法
いちばん簡単な方法：入力し直す
対象が数件であれば、一番シンプルなのは入力し直すことです。
- セルをダブルクリックします。
- 全角数字を削除します。
- 半角で入力し直します。
とてもシンプルですが、確実に直すことができます。入力し直す際は、日本語入力をオフにしてから打ち直すと、再び全角になってしまうのを防げます。
データが多い場合：置換機能を使う
データが大量にある場合、1つずつ修正するのは大変です。その場合は、置換機能を使う方法があります。
- 「Ctrl + H」を押して置換画面を開きます。
- 「検索する文字列」に全角の数字、「置換後の文字列」に半角の数字を入力します。
- 「すべて置換」を押します。
「０」から「９」までを1つずつ置換していく必要はありますが、一括で修正できるため、手作業よりずっと早く終わります。業務でExcelを使う場合は、この方法も覚えておくと便利です。なお、置換を行うときは、対象の範囲をあらかじめ選択してから実行すると、意図しない他のセルまで変換してしまうのを防げます。金額や数量の列だけを選択してから置換する、というひと手間を加えておくと、より安全です。
ASC関数でまとめて半角にする
もう一つ、関数を使った方法もあります。ASC関数は、全角の文字を半角に変換する関数です。
=ASC(B3)こうすると、全角で入力された数字を半角に変換した結果を、別のセルに表示できます。ただし、ASC関数の結果はそのままだと文字列になることがあるため、確実に数値として扱いたい場合は、VALUE関数と組み合わせて次のように書くと安心です。
=VALUE(ASC(B3))これで、全角数字を半角の数値に変換した値が得られます。元のデータを残したまま、計算に使える列を新しく作りたい場合に便利な方法です。
そもそも全角数字を混ぜないための予防策
トラブルが起きてから直すのも大切ですが、そもそも全角数字が混ざらないように予防しておければ、余計な手間を減らせます。実務で使える予防策をいくつか紹介します。
一つ目は、数値を入力する列に「入力規則」を設定しておく方法です。入力規則で「整数」や「小数」だけを許可するように設定しておくと、全角数字を入力しようとしたときにエラーが出て、そもそも入力できなくなります。複数人でファイルを共有していて、入力する人によって形式がバラバラになりがちな場合に、とくに効果的です。
二つ目は、入力するときに日本語入力を自動的にオフにする設定です。入力規則の「日本語入力」タブで「オフ（英語モード）」を選んでおくと、そのセルを選択したときに自動的に半角入力に切り替わるため、うっかり全角で入力してしまうこと自体を防げます。
こうした予防策は、一度設定してしまえば、その後は意識しなくても全角混入を防いでくれます。同じトラブルを何度も繰り返している場合は、対処法だけでなく、こうした仕組みでの予防も検討してみてください。
研究員メモ
Excelの面白いところは、「見た目が同じでも中身が違う」という点です。今回の「100」と「１００」も、その一例です。人間の目には同じでも、Excelにとっては違うデータ。だからこそ、「なんか変だな」と思ったときは、関数ではなくデータを疑う、という視点がとても大切です。
前回の「文字列になっている」も、今回の「全角数字」も、そして次にご紹介する予定の「スペースが入っている」も、すべて根っこは同じで、「数値のはずのデータが、実は数値として扱われていない」という問題です。1つ1つを別々のトラブルとして丸暗記するのではなく、「Excelは見た目ではなくデータの種類で計算する」という原則として理解しておくと、初めて出会う症状にも応用が利きます。
まとめ
今回のテーマは「SUM関数が計算されない理由②｜全角数字が混ざっている」でした。チェックポイントはこちらです。
- 数字の横幅が少し広くないか
- 手入力したデータではないか
- コピペや社内システムから持ってきたデータではないか
この3つを、まず確認してみてください。Excelでは、見た目よりデータの種類が重要です。この視点を持つと、トラブル解決がかなり早くなります。全角・半角の違いは、一度気づけるようになると、以降は「あ、これ怪しいな」と直感的に見抜けるようになります。焦らず、少しずつ目を慣らしていきましょう。
次回は、これもまた見た目では気づきにくい「スペースが入っている」ケースを取り上げる予定です。全角数字と同じように、見えないところに原因が潜んでいるという点で、今回の内容と地続きのテーマです。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。
コメント