「合計が0になる」「一部しか足されない」——そんな経験はありませんか。
数式は間違っていないはずなのに、SUM関数がうまく動かない。私自身、Excelを使い始めたばかりの頃、何度も関数を書き直してはうまくいかず、頭を抱えていました。ある月、経費精算のデータを他部署から受け取り、SUMで合計を出したところ、明らかに実際の金額より少ない数字が返ってきたことがあります。1件1件、電卓で確認し直しても、データ自体は正しく入力されているように見える。原因が分かるまで、半日近く時間を使ってしまいました。
ですが、あとになって分かったのは、原因の多くは関数ではなく、データそのものにあるということです。
今回は、SUM関数が計算されないもっとも多い原因である「数字が文字列になっている」というケースについて、見分け方と直し方をあわせて整理していきますSUM関数の基本的な使い方や注意点については以前の記事でも取り上げましたが、今回はその中でもとくに実務で頻度の高い「文字列扱い」に焦点を絞って解説します。
よくある状況
次のような表を作ったとします。
この合計を出すために、次の関数を入力します。
=SUM(B2:B4)通常なら結果は600になるはずです。しかし、実際の業務ではこのようなことが起こります。
- 合計が0になる
- 一部しか計算されない
- SUM関数が動いていないように見える
こういう状況です。初心者の頃の私は「関数の書き方が間違っているんだ」と思って、何度も関数を書き直していました。ですが実際の原因は、関数そのものではないことが多いのです。範囲の指定も、括弧の対応も、すべて合っているのに結果だけがおかしい。そんなときこそ、視点をデータそのものに切り替えるタイミングです。
原因：数字が「文字列」になっている
Excelでは、データは大きく分けて「数字」と「文字列」の2種類で扱われます。ここで重要なのは、見た目が同じでも、Excelの中では別物として扱われるという点です。
例えば「100」と表示されていても、実際には「”100″」という文字列データになっていることがあります。この場合、Excelはそれを数値として計算してくれません。つまり =SUM(B2:B4) と入力しても、文字は計算対象にならないため、合計がおかしくなるのです。
エラーメッセージが出るわけではないので、余計に厄介です。式は正しく入力されているのに、結果だけが違う。「自分が何か間違えたのでは」と不安になりながら式を何度も見直す時間ほど、もったいないものはありません。これは、以前ご紹介したSUMIFSで数字が合わないというトラブルとも、根っこはまったく同じです。関数の種類が違っても、「数値のはずが文字列になっている」という原因は、集計まわりのトラブルで繰り返し登場します。
なぜ文字列データになるのか
では、なぜ数字が文字列になってしまうのでしょうか。よくある原因は次の3つです。
①Webサイトやシステムからコピペした
これは非常によくあります。Webサイト、PDF、社内システムなどからコピーしたデータは、文字列データとして貼り付けられることが多いです。見た目は数字でも、Excelの中では文字列扱いになっています。
とくに、社内の基幹システムから出力したCSVをそのまま取り込んだ場合や、他部署から共有された一覧をコピーしてきた場合は、ほぼ確実に一度疑ってみたほうがいい原因です。検索値は数値、参照先は文字列というズレは、VLOOKUPの#N/Aエラーの原因としても紹介しましたが、これも今回と同じ「文字列と数値の混在」が根本原因です。
面白いのは、同じシステムから出力したデータでも、出力形式の設定によって数値になったり文字列になったりする、という点です。「先週までは普通に計算できていたのに、今週から急に合計が合わない」という場合は、データの出力元やコピー元が変わっていないか、あわせて確認してみることをおすすめします。
②セルの表示形式が文字列になっている
セルの書式設定が「文字列」になっていると、入力した数字はすべて文字列として扱われます。テンプレートを使い回している場合や、他の人が作った表をベースにしている場合、意図せずセルの書式が文字列のまま残っていることがあります。
厄介なのは、一度「文字列」の書式が設定されたセルは、あとから書式だけを「数値」に変更しても、すでに入力済みのデータは自動的には数値に変わらないという点です。書式を変更したあとに、そのセルを一度ダブルクリックしてEnterキーを押し直す、あるいは前述の変換操作を行う必要があります。「書式は数値に直したのに、まだ合計されない」という場合は、この二段階の手順を踏み忘れていないか確認してみてください。
③先頭に「’」（シングルクォーテーション）がついている
Excelでは '100 のように、シングルクォーテーションがついていると、数字でも文字列として扱われます。これは、Excelが「先頭が0から始まる数字（郵便番号や社員番号など）」を数値として扱ってしまい、桁が消えてしまうのを防ぐための機能なのですが、意図せずこの状態になっているケースも少なくありません。
見分けるポイント
数字が文字列になっているかどうかは、慣れないうちは見た目だけでは判断しにくいものです。次の4つを確認してみてください。
①セル左上の緑の三角
Excelでは、エラーの可能性があるセルに緑の三角が表示されることがあります。これが出ている場合、「数値が文字列として保存されています」という警告の可能性があります。セルをクリックすると表示される「！」マークからも、同じ内容を確認できます。
②数字なのに左寄せになっている
Excelには、数字は右寄せ、文字列は左寄せで表示される、というルールがあります。つまり、数字なのに左寄せになっている場合は、文字列データの可能性があります。これはかなり分かりやすいサインなので、まず表全体をざっと見て、左寄せになっている数値がないかを確認する習慣をつけておくと安心です。
③コピペしたデータかどうか
もしWeb、PDF、システム画面などからコピーしてきた場合は、文字列データを疑うのがおすすめです。Excelでは、コピペデータが原因のトラブルが本当に多いです。
④ISNUMBER関数で確認する
見た目だけでは判断がつきにくい場合は、別のセルに次の関数を入力して確認する方法もあります。
=ISNUMBER(B2)対象のセルが数値であればTRUE、文字列であればFALSEが返ってきます。表全体をまとめて確認したいときは、この式を対象範囲の隣の列にコピーしておけば、どのセルが文字列扱いになっているかを一目で洗い出すことができます。目視での確認に自信が持てないときは、この方法が一番確実です。
解決方法
一番簡単な方法：エラーインジケーターから変換する
初心者の方におすすめの、一番簡単な解決方法を紹介します。
- セルをクリックします。
- 表示される「！」マークをクリックします。
- 「数値に変換」を選択します。
これだけで、文字列から数値に変換されます。すると =SUM(B2:B4) も正しく計算されるようになります。とても簡単ですが、知らないとずっとハマるポイントです。
対象範囲が広い場合：区切り位置ウィザードを使う
エラーインジケーターは、1件ずつ確認するには便利ですが、範囲が広い場合は少し手間がかかります。範囲全体をまとめて変換したい場合は、対象のセルを選択したうえで「データ」タブの「区切り位置」を開き、そのまま「完了」を押すだけで、選択範囲の文字列がまとめて数値に変換されることがあります。
VALUE関数で変換する
既存のデータを直接書き換えたくない場合は、別のセルにVALUE関数を使って変換する方法もあります。
=VALUE(B2)こうしておけば、元の文字列データを残したまま、数値として扱える列を新たに作ることができます。
掛け算やコピー＆形式を選択して貼り付けを使う裏技
もう一つ、実務でよく使われる方法があります。空いているセルに「1」を入力してコピーし、変換したい範囲を選択したうえで、「形式を選択して貼り付け」から「乗算」を選ぶという方法です。文字列として入力された数字に1を掛け算することで、Excelが自動的に数値として計算し直してくれます。この方法であれば、区切り位置ウィザードが使いにくい表の形でも、まとめて変換できることがあります。範囲が広い場合や、何度も同じ作業が発生する場合は、覚えておくと重宝するテクニックです。
研究員メモ
Excel初心者の頃の私は、関数が動かないと「関数の書き方が悪い」と思っていました。ですが、実際の業務では、関数よりもデータの問題の方が多いです。特に多いのが、文字列になっている、全角数字が混ざっている、空白が入っている、などです。
Excelでトラブルが起きたときは、関数よりデータを疑う。この考え方を持つだけで、解決スピードがかなり変わります。これは、Excelを使う上での小さなコツですが、覚えておくと長く役に立つ視点です。
もう一つ付け加えておくと、こうしたデータの問題は、自分の入力ミスだけが原因とは限りません。他部署から受け取ったデータ、外部システムから出力されたデータなど、自分では手を加えていない部分にも原因があることが多いです。「自分の作業が悪かったのでは」と自分を責める前に、まずはデータの出どころから疑ってみる、という順番を意識しておくと、無駄に落ち込まずに済みます。
まとめ
今回のテーマは「SUM関数が計算されない理由｜数字が文字列になっている」でした。チェックポイントはこちらです。
- セル左上に緑の三角が出ていないか
- 数字なのに左寄せになっていないか
- コピペしたデータではないか
この3つを、まず確認してみてください。SUM関数が動かない原因の、かなりの割合がこれです。エラーが出ないぶん気づきにくいポイントですが、一度この見分け方を覚えてしまえば、次に同じ症状が出たときも、迷わず対処できるようになります。
数字が文字列になっているという原因は、SUM関数だけでなく、SUMIFSやVLOOKUP、COUNTIFなど、条件や数値を扱うほとんどの関数で同じように影響します。1つの関数の対処法として覚えるのではなく、「Excelでは数字と文字列は別物として扱われる」という基本のルールとして押さえておくと、これから先どんな関数を使うときにも役立ちます。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数についても実務目線で整理していますので、あわせてご覧ください。
コメント