「関数は合っているのに、なぜか合計が合わない」——このシリーズも、今回で4回目になります。
私自身、Excelを使い始めたばかりの頃は、計算が合わないたびに「関数の書き方を間違えたのかな」と思い込み、何度も関数を入れ直していました。ですが、Excelを使い続けているうちに気づいたのは、トラブルの多くは関数ではなく「入力方法」に原因があるということです。
新人の頃、備品の購入リストを作って上司に提出したことがあります。「単価」の列に「1,200円」「850円」と丁寧に単位まで入力し、我ながら分かりやすい表ができたと思っていました。ところが、合計欄に入れたSUM関数の結果は「0」。何度式を入れ直しても変わらず、結局その場では電卓で合計を出して提出することになりました。良かれと思ってやった入力が、実はトラブルの原因になっていた——今回のテーマは、そんな種類のミスです。
これまで、数字が文字列になっているケース、全角数字が混ざっているケース、スペースが入っているケースを取り上げてきました。
今回のテーマは「数字に単位（円・個）を付けている」ケースです。①〜③は「気づかないうちに混入していた」トラブルでしたが、今回は少し性質が違います。入力した本人が、良かれと思ってやっていることが原因になる、という点が今回の特徴です。
よくある状況
まずは、よくある例を見てみます。次のような売上表を作ったとします。
見た目としては、とても分かりやすい表です。「円」が付いているので、この数字が金額であることがひと目で分かります。そして合計を出すために、次の関数を入力します。
=SUM(B2:B4)普通に考えると、結果は600円になりそうです。しかし実際のExcelでは、計算されない、合計が0になる、正しく合計されない、といった問題が起きることがあります。
「関数は合っているはずなのに…」——このような場合、原因は関数ではなく、入力しているデータの種類にあります。
見分け方はとても簡単です。セルを選択して、右寄せになっているか左寄せになっているかを確認してみてください。Excelでは、数値は右寄せ、文字列は左寄せで表示されます。「100円」のように単位を付けたセルは、ほぼ確実に左寄せになっているはずです。他の数値セルと見比べたときに、明らかに揃っていない列があれば、それが原因の可能性が高いと判断できます。
原因：数字ではなく「文字列」になっている
Excelでは、セルに入力されたデータは大きく2種類に分かれます。
一見同じように見えますが、Excelの中ではまったく別のデータとして扱われています。Excelの基本ルールは、数字は計算できる、文字列は計算できない、というものです。
つまり 100 200 300 のように入力されていればSUM関数で計算できますが、100円 200円 300円 と入力すると、Excelはこれを文字列データとして扱います。その結果、=SUM(B2:B4) と入力しても、正しく計算できなくなるのです。
ここまでの流れは、①〜③でご紹介してきたトラブルとまったく同じ構造です。数字のように見えても、Excelが数値として扱ってくれなければ、合計には含まれない。「見た目は数字、中身は文字列」という点で、これらはすべて同じ仲間のトラブルだと言えます。
なぜ単位を付けてしまうのか
これは、とても自然なことです。普段の生活では、100円、10個、5kg、3時間のように「数字＋単位」で表現します。そのため、Excelでも同じ感覚で入力してしまうのです。
実際、初心者の頃の私も 100円 10個 3kg 20人 といった入力をよくしていました。見た目は分かりやすいのですが、Excelでは計算できないデータになってしまいます。
しかも厄介なのが、この入力方法は「丁寧に仕事をしている」ように見えることです。単位まできちんと書いてある表は、一見すると親切な資料に見えます。だからこそ、周りから指摘されることも少なく、本人も問題に気づかないまま、そのやり方を続けてしまいがちです。合計を出そうとした段階で初めて、「あれ、計算できない」と気づく、というパターンが非常に多いのです。
正しい入力方法
ではどう入力すればいいのでしょうか。おすすめは、セルには数字だけ入力するという方法です。
このように数字だけ入力します。これなら =SUM(B2:B4) で600と正しく計算されます。
とはいえ、「これだと円なのか個数なのか分からない」と思われるかもしれません。そこで使うのが、Excelの表示形式という機能です。
単位は「表示形式」で付ける
Excelには表示形式という機能があります。これは、データの中身を変えずに、見た目だけを変える機能です。
つまり 100 という数字を、¥100 のように表示できます。データは数字のままなので、SUM関数も平均も、すべての計算が問題なく使えます。「計算できること」と「見やすいこと」を、両立できるわけです。
表示形式の設定方法
設定方法はとても簡単です。
- 金額のセルを選択します。
- 「ホーム」タブをクリックします。
- 「数値」の設定を開きます。
- 「通貨」または「会計」を選びます。
これだけで、100 だった表示が ¥100 のように変わります。ここで重要なのは、Excelの中では数字のままという点です。見た目は「円」ですが、Excelはちゃんと数値として認識してくれています。
「円」や「個」を表示したい場合
「¥100」ではなく「100円」と表示したい、あるいは「10個」と表示したい、という場合は、ユーザー定義の表示形式を使います。セルの書式設定から「ユーザー定義」を選び、種類の欄に次のように入力します。
#,##0"円"こうすると、セルには 100 と入力されているのに、画面上は「100円」と表示されます。個数であれば 0"個"、重さであれば 0"kg" のように、ダブルクォーテーションで囲んだ文字が単位として表示されます。
この方法なら、見た目の分かりやすさを保ちながら、計算もきちんとできる状態を維持できます。「単位を付けたい」という気持ちを我慢する必要はなく、付け方を変えればいい、というだけの話なのです。
円以外の単位も同じ考え方
この考え方は、円だけではありません。例えば次のような単位も同じです。
基本ルールは、数字は数字だけ入力する、ということです。単位は、表示形式、別の列、あるいは見出しなどで表現します。
とくに実務でおすすめなのが、見出しに単位を書いておく方法です。「売上」ではなく「売上（円）」、「数量」ではなく「数量（個）」というように、列の見出しに単位を明記しておけば、セルの中身は数字だけで済みますし、表を見る人にも意味がきちんと伝わります。表示形式の設定が面倒に感じる場合は、まずこの方法から試してみるのも良いと思います。
すでに単位付きで入力してしまった表の直し方
「もう何百行も単位付きで入力してしまった」という場合、1つずつ手で直すのは現実的ではありません。そんなときに使えるのが、置換機能です。
対象の範囲を選択したうえで「Ctrl + H」で置換画面を開き、「検索する文字列」に「円」と入力し、「置換後の文字列」は空欄のままにして「すべて置換」を押します。これで、範囲内の「円」という文字がまとめて削除され、数字だけが残ります。あとは表示形式で単位を付け直せば完成です。
なお、置換を行う前には、必ず対象範囲を選択しておくことをおすすめします。範囲を選択せずに実行すると、シート全体の「円」という文字が削除されてしまい、見出しや備考欄の文章まで変わってしまう可能性があるためです。念のため、実行前にファイルを別名で保存しておくと、より安心して作業できます。
研究員メモ
Excelのトラブルを研究していると、ある共通点が見えてきます。それは、見た目を優先するとExcelが困る、ということです。
人間にとって分かりやすい入力が、Excelにとっては計算できないデータになることがあります。Excelでは、データの種類がとても重要です。だからこそ、数字は数字、文字列は文字列、というルールを意識して入力することが大切になります。
①の文字列、②の全角数字、③のスペース、そして今回の④の単位。この4つは、症状こそ違いますが、原因は「数値として扱われるべきデータが、文字列になっている」という一点に集約されます。1つ1つを別々に丸暗記するのではなく、この共通のルールとして理解しておくと、これから先どんなトラブルに出会っても、落ち着いて原因を切り分けられるようになります。
まとめ
今回のテーマは「SUM関数が動かない理由④｜数字に単位を付けている」でした。ポイントをまとめます。
- NG入力：
100円10個5kg - OK入力：
100105 - 単位は表示形式、または列の見出しで付ける
これを覚えておくと、Excelの計算トラブルを防ぐことができます。Excelでは、見た目よりもデータの種類が重要です。この考え方を覚えておくと、トラブル解決がとても早くなります。
もし今、単位付きで入力してしまっている表があっても、慌てる必要はありません。単位を削除して数字だけにし、あらためて表示形式で単位を付け直せば、見た目はそのままに、計算できる表へと作り変えることができます。一度この形に整えてしまえば、以降は集計のたびに悩まされることもなくなります。
次回は、知らないうちに入り込んでいることがある「アポストロフィ（’）」を取り上げる予定です。これもまた、SUM関数が動かなくなる原因の一つです。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。
コメント