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