「関数は合っているのに、なぜか合計が合わない」——このシリーズも、今回で6回目になります。
私自身、Excelを使い始めたばかりの頃は、計算が合わないたびに「関数の書き方を間違えたのかな」と思い込み、何度も関数を打ち直していました。ですが、Excelを使い続けているうちに気づいたのは、トラブルの多くは関数ではなく「データの入力」に原因があるということです。
在庫の数量表を作っていたとき、ほとんどのセルは数字だけで入力していたのに、1つだけ「30個」と単位を付けてしまっていたことがありました。合計が合わないので原因を探したところ、その1セルだけが計算から外れていたのです。たった1つ、うっかり単位を付けたセルのせいで、表全体の合計が狂う。見た目ではまったく区別がつかないだけに、こうしたミスは本当に気づきにくいものです。
これまで、数字に単位を付けているケースや、アポストロフィが付いているケースなど、SUM関数が計算されない原因を1つずつ研究してきました。
今回のテーマは「数字と文字列が混ざっている」ケースです。前回の④で取り上げた「単位を付けている」問題と近い部分もありますが、今回はもう少し広く、「数字と文字が同じセルに同居している」状態を扱います。見た目では数字に見えても、Excelの中では文字列データとして扱われている——そんなよくあるミスを、一緒に研究していきましょう。
よくある状況
まずはよくある例を見てみます。次のような数量の表を作ったとします。
見た目としては、それほど問題がなさそうに見えます。むしろ「30個」と単位まで書いてあって、親切な表に見えるかもしれません。そして合計を出すために、次の関数を入力します。
=SUM(B2:B4)普通なら結果は60になります。しかし実際には、正しい合計にならない、計算結果が合わない、一部の数字だけ計算されない、といったことが起きることがあります。
関数も範囲も合っているのに、なぜ計算されないのか。その原因が、今回のテーマです。
原因：数字の中に文字列が混ざっている
Excelでは 10 20 30 はすべて数値データです。しかし 30個 と入力すると、Excelはこれを文字列データとして扱います。
つまりExcelの中では、次のように判断されています。
Excelの基本ルールは、これまでの回でもお伝えしてきた通り、数値は計算できる、文字列は計算できない、というものです。そのため、=SUM(B2:B4) と入力しても、「30個」のセルは計算対象から外れてしまい、合計が正しく出なくなるのです。
原因の根っこは、これまで取り上げてきた「文字列になっている」「全角数字」「スペース」「単位付き」「アポストロフィ」と、まったく同じです。数字のように見えても、Excelが数値として扱ってくれなければ計算されない。この原則が、ここでもそのまま当てはまります。
よくある入力パターン
このミスは意外と多く、実務でもよく見かけます。研究員としてよく見るのは、次のような例です。
30個
100円
20A
10台
50％これらはすべて「数字＋文字」のデータで、Excelの中ではすべて文字列として扱われます。つまりExcelは「30個」を、数字ではなく一つの文章のように認識しているのです。そのため、SUM関数などの計算に使えないことがあります。
とくに「20A」のような、商品コードや型番として使われがちな組み合わせは、本人が「これは数字のつもり」で入力していることが多く、後になって「なぜ計算できないのか」と戸惑いやすいパターンです。数字が含まれていても、アルファベットや記号が一文字でも混ざっていれば、Excelにとってはもう文字列なのだ、と考えておくと分かりやすいと思います。
なぜこの入力をしてしまうのか
これは、とても自然なことです。人間が見る場合は「30個」「100円」と書いてあったほうが、何のデータなのかがひと目で分かって親切です。だからこそ、Excelでも同じ感覚で入力してしまいます。
しかしExcelは、あくまで計算するためのソフトです。そのため、データの種類をとても重要視します。Excelにとって 30 と 30個 は、見た目が似ていてもまったく別のデータなのです。「人間にとって分かりやすい入力」と「Excelが計算できる入力」は、必ずしも一致しない。この感覚のズレが、今回のトラブルの根っこにあります。
正しい入力方法
Excelでデータを入力するときは、数字は数字だけで入力するのが基本です。例えば数量の表なら、次のように入力します。
これなら =SUM(B2:B4) で正しく60と計算されます。
では、「個」などの単位はどうすればいいのでしょうか。おすすめは、列の見出しで表現する方法です。
このように「数量（個）」と見出しに単位を書いておけば、セルの中身は数字だけで済みますし、表を見る人にも意味がきちんと伝わります。これは、Excelで非常によく使われる方法です。
もう一つ、④の記事でも紹介した「表示形式」を使う方法もあります。セルには 30 と入力しておき、表示形式のユーザー定義で 0"個" と設定すると、画面上は「30個」と表示されるのに、中身は数値のまま、という状態を作れます。「見た目に単位を出したい」という場合は、この方法を使えば、計算できる状態を保ったまま、単位も表示できます。
見分けるポイント
SUM関数がうまく計算されないときは、次のポイントを確認してみてください。
① 数字なのに左寄せになっている
Excelには、数値は右寄せ、文字列は左寄せで表示される、というルールがあります。数字なのに左寄せになっている場合は、文字列データの可能性が高いです。表全体をざっと見て、他の数値と揃っていないセルがないかを確認してみてください。
② 単位や文字が混ざっていないか
30個 100円 20台 のように、数字のうしろ（または前）に文字が付いていないかを確認します。1文字でも文字が混ざっていれば、そのセルは文字列扱いになっている、と考えてよいでしょう。
③ ISNUMBER関数で確認する
見た目での判断に自信が持てない場合は、別のセルに次の関数を入力して確認する方法が確実です。
=ISNUMBER(B2)対象のセルが数値であればTRUE、文字が混ざって文字列扱いになっていればFALSEが返ってきます。範囲が広い場合は、隣の列にこの式を並べておくと、どのセルが計算対象から漏れているかを一目で洗い出せます。
解決方法
対象が少ない場合：入力し直す
対象が数件であれば、一番シンプルなのは、余計な文字を消して数字だけにすることです。セルをダブルクリックして「個」や「円」などの文字を削除し、数字だけの状態にすれば、数値データとして扱われるようになります。
データが多い場合：置換機能を使う
データが大量にある場合は、置換機能が便利です。対象の範囲を選択したうえで「Ctrl + H」で置換画面を開き、「検索する文字列」に「個」と入力し、「置換後の文字列」は空欄のままにして「すべて置換」を押します。これで、範囲内の「個」がまとめて削除され、数字だけが残ります。「円」「台」など、他の単位も同じ手順で処理できます。
なお、置換を行う前には、必ず対象範囲を選択しておくことをおすすめします。範囲を選ばずに実行すると、見出しや備考欄の文章まで変わってしまう可能性があるためです。念のため、作業前にファイルを別名で保存しておくと、より安心です。
数字だけを抜き出したい場合
数字と文字が複雑に混ざっていて、単純な置換では対応しきれない場合もあります。そうしたときは、関数を使って数字部分だけを抜き出す方法もありますが、初心者のうちは、まず「入力の段階で数字と文字を分ける」ことを意識するほうが、結果的にトラブルは少なくなります。入力ルールをシンプルに保つことが、いちばんの予防策です。
研究員メモ
Excelのトラブルを研究していると、よく感じることがあります。それは、見た目と中身は違う、ということです。今回の「30」と「30個」も、その一例です。人間には同じ数字に見えても、Excelの中ではまったく別のデータです。
だからこそ、「関数は合っているのにおかしい」と思ったときは、関数ではなくデータの種類を疑う。この習慣を持つことが、Excelトラブル解決の一番の近道です。①の文字列、②の全角数字、③のスペース、④の単位、⑤のアポストロフィ、そして今回⑥の数字と文字の混在。ここまで6回にわたってお伝えしてきましたが、原因はどれも「数値であるべきデータが、文字列になっている」という一点に集約されます。症状の名前を1つずつ暗記するのではなく、この共通のルールとして理解しておけば、初めて出会うトラブルにも落ち着いて対応できます。
なお、似たトラブルとして、半角と全角の違いによって文字が一致しなくなるケースもあります。VLOOKUPやCOUNTIFで「一致しない」という症状が出ている場合は、文字コードの違いで一致しないケースもあわせて確認してみると、原因の切り分けがしやすくなります。
まとめ
今回のテーマは「SUM関数が動かない理由⑥｜数字と文字列が混ざっている」でした。ポイントをまとめます。
- NG入力：
30個100円20A - OK入力：
3010020 - 単位は「列の見出し」または「表示形式」で表す
この3つを覚えておくと、Excelの計算トラブルをかなり防ぐことができます。Excelでは、見た目ではなくデータの種類が重要です。この考え方さえ身につけてしまえば、SUM関数に限らず、あらゆる集計トラブルに落ち着いて対応できるようになります。
もし今、数字と文字が混ざった表があっても、慌てる必要はありません。余計な文字を取り除いて数字だけにし、単位は見出しや表示形式で付け直せば、見た目の分かりやすさを保ったまま、計算できる表へと作り変えることができます。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。次回は、見た目は日付なのにExcelの中では文字列になっている、という「日付が文字列になっている」ケースを取り上げる予定です。
コメント