「今月も先月と同じ関数を入れただけなのに、合計が合わない」
月末、売上データを集計して本社に提出する前日にこれが起きると、正直かなり焦ります。Excelを使い始めたばかりの頃は、何度もこの状況に陥りがちです。関数の書き方は先月とまったく同じ。範囲もずれていない。それなのに、SUM関数の結果だけがおかしい。
先に結論からお伝えすると、この現象のもっとも多い原因は関数の書き方ではなく、「合計したいセルの数字が、実は文字列として保存されている」ことです。今回は、SUM関数が計算されない原因の中でも実務でとくに頻度の高い「数字の文字列化」について、見分け方と直し方をまとめて整理します。SUM関数そのものの基本的な使い方や注意点は以前の記事でも取り上げているので、あわせて確認してみてください。
SUM関数の基本の使い方と、合計が合わないときの対処法 SUM関数は「簡単だから安心」とは限りません。基本の使い方に加え、文字列や空白が混ざったときの落とし穴、複数シート集計、引き算への応用まで、実務目線でまとめて解説します。
文字列になる原因のうち、全角の数字と、先頭に付く見えないアポストロフィは、とくに気づきにくい2つです。この記事の後半で、全角数字の見分け方と直し方、アポストロフィの見つけ方と直し方も、それぞれまとめています。同じ列に文字の数字が数行だけ混ざっている表の調べ方と、数値なのに合わない隠れ小数の見つけ方もあわせて載せました。
まず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が消えないときの原因の探し方 VLOOKUPで#N/Aが消えない原因を実務目線で解説。スペース混入・TRUE設定・範囲ズレなど、会議前に焦らないための確認ポイントを具体例付きで紹介します。
②セルの表示形式が「文字列」になっている
セルの書式設定が「文字列」になっていると、そこに入力した数字はすべて文字列として扱われます。テンプレートを使い回している場合や、他の人が作った表をベースに作業している場合、意図せず書式が「文字列」のまま残っていることがあります。
厄介なのは、一度「文字列」の書式が設定されたセルは、あとから書式だけを「数値」に変更しても、すでに入力済みのデータが自動的に数値へ変わるわけではないという点です。書式を変更したあとに、そのセルを一度ダブルクリックしてEnterキーを押し直すか、後述の変換操作を行う必要があります。「書式は数値に直したのに、まだ合計されない」という場合は、この二段階目の手順を踏み忘れていないか確認してみてください。
③先頭に「’」（アポストロフィ）が付いている
Excelでは '100 のように、数字の前にアポストロフィを付けると、その数字は文字列として扱われます。これは、Excelが「先頭が0から始まる数字（郵便番号や社員コードなど）」を数値として扱うと桁が消えてしまうのを防ぐための仕様なのですが、意図せずこの状態になっているケースも少なくありません。
4つの方法で「文字列かどうか」を見分ける
数字が文字列になっているかどうかは、慣れないうちは見た目だけでは判断しにくいものです。次の4つを確認してみてください。
①セル左上の緑の三角
Excelでは、エラーの可能性があるセルの左上に緑の三角マークが表示されることがあります。これが出ている場合、「数値が文字列として保存されています」という警告の可能性が高いです。セルをクリックすると出てくる「！」マークをクリックすると、同じ内容を確認できます。
②数字なのに左寄せになっている
Excelには「数値は右寄せ、文字列は左寄せ」という初期設定のルールがあります。数字が入っているのに左寄せになっている場合は、文字列データの可能性が高いです。表全体をざっと見て、左寄せの数値がないか確認する習慣をつけておくと、単純ですがかなり見つけやすくなります。
③そのデータがコピペ由来かどうか
Web、PDF、システム画面などからコピーしてきたデータであれば、まず文字列データを疑うのがおすすめです。実務でのExcelトラブルは、コピペが原因になっているケースが本当に多いです。
④ISNUMBER関数で確認する
見た目だけでは判断がつきにくい場合は、別のセルに次の関数を入力して確認する方法もあります。
=ISNUMBER(B2)
対象のセルが数値であればTRUE、文字列であればFALSEが返ってきます。表全体をまとめて確認したいときは、この式を対象範囲の隣の列にコピーしておくと、どのセルが文字列扱いになっているかを一目で洗い出せます。目視での確認に自信が持てないときは、この方法がもっとも確実です。
見分け方を表にまとめると、次のようになります。
| 確認方法 | 手間 | 確実さ | こんな時におすすめ |
|---|---|---|---|
| 緑の三角マーク | ほぼゼロ | 表示されない場合もある | まず最初にざっと見るとき |
| 左寄せ/右寄せ | ほぼゼロ | 目視なのでやや主観的 | 表全体をパッと見渡すとき |
| コピペ由来かの確認 | ほぼゼロ | 状況証拠レベル | 原因の当たりをつけるとき |
| ISNUMBER関数 | 少しかかる | 確実 | 範囲が広い、または正確に判定したいとき |
状況別の直し方
原因が分かったところで、実際の直し方を4パターン紹介します。表の大きさや、元データを残したいかどうかで、向いている方法が変わってきます。
もっとも簡単な方法：エラーインジケーターから変換する
対象のセルが少ない場合は、この方法がもっとも簡単です。
- 対象のセルをクリックします
- 表示される「！」マークをクリックします
- 「数値に変換」を選択します
これだけで文字列から数値に変換され、=SUM(B2:B4) も正しく計算されるようになります。知っていればすぐ終わる作業ですが、知らないとずっとハマるポイントです。
対象範囲が広い場合：区切り位置ウィザードを使う
エラーインジケーターは1件ずつ確認するには便利ですが、範囲が広いとさすがに手間がかかります。範囲全体をまとめて変換したい場合は、対象のセルを選択したうえで「データ」タブの「区切り位置」を開き、そのまま「完了」を押すだけで、選択範囲の文字列をまとめて数値に変換できることがあります。
元データを残したい場合：VALUE関数で変換する
既存のデータをそのまま書き換えたくない場合は、別のセルにVALUE関数を使う方法もあります。
=VALUE(B2)
こうしておけば、元の文字列データを残したまま、数値として扱える列を新たに作ることができます。他部署から受け取ったデータのように、原本を直接いじりたくない場面で重宝します。
繰り返し作業になる場合：掛け算＋形式を選択して貼り付け
実務でよく使われる裏技です。空いているセルに「1」を入力してコピーし、変換したい範囲を選択したうえで「形式を選択して貼り付け」から「乗算」を選びます。文字列として入力された数字に1を掛け算することで、Excelが自動的に数値として計算し直してくれます。区切り位置ウィザードが使いにくい形の表でも、まとめて変換できることがあり、何度も同じ作業が発生する場合は覚えておくと重宝します。
4つの方法をどう使い分ければいいか、目安を表にまとめておきます。
| 方法 | 向いている場面 | 注意点 |
|---|---|---|
| エラーインジケーター | セルの数が少ない | 緑の三角が出ていないと使えない |
| 区切り位置ウィザード | 範囲がまとまって広い | 列の途中に文字列以外のデータがあると崩れることがある |
| VALUE関数 | 元データを残したい | 変換用の列が追加で必要になる |
| 掛け算＋乗算貼り付け | 何度も同じ作業が発生する | 手順を忘れやすいのでメモしておくと安心 |
再発させないための予防策
一度直しても、また同じことが起きては意味がありません。ここでは、表そのものの作り方や運用面でできる予防策を紹介します。
外部データを取り込む機会が多い場合は、コピペではなく「データ」タブの「テキストまたはCSVから」（Power Query）を使うと、取り込み時点でデータ型を指定できるため、あとから文字列化に悩まされにくくなります。少し慣れが必要な機能ですが、毎月同じ形式のデータを取り込むような業務であれば、覚える価値は十分にあります。
また、自分たちで数字を手入力する表であれば、あらかじめ「データの入力規則」で対象セルに数値のみを許可する設定をしておくと、そもそも文字列が紛れ込むこと自体を防げます。テンプレート化して配布する表がある場合は、この設定を最初から組み込んでおくと、あとから毎回同じ確認作業をする手間が減ります。
もう一つ付け加えておくと、こうしたデータの問題は、自分の入力ミスだけが原因とは限りません。他部署から受け取ったデータや、外部システムから出力されたデータなど、自分では手を加えていない部分に原因があることも多いです。「自分の作業が悪かったのでは」と自分を責める前に、まずはデータの出どころから疑ってみる、という順番を意識しておくと、無駄に落ち込まずに済みます。
全角の数字が混ざっているとき
文字列になる理由のうち、見た目でいちばん気づきにくいのが全角数字です。半角の「100」と全角の「１００」は、並べればわずかに横幅が違う程度で、ほとんど同じに見えます。けれどもExcelの中では、全角で入った数字の多くが文字列として扱われ、SUMの合計から静かに外れます。
たとえば、A商品100、B商品２００、C商品300の3行を=SUM(B2:B4)で合計すると、600ではなく400が返ります。エラーは何も出ません。B商品の「２００」だけが全角で、計算の対象から外れているからです。
全角数字が入り込む経路は、だいたい次の3つです。
- 日本語入力がオンのまま、キーボード上段の数字キーで打って、そのまま確定してしまう
- Webページやメール本文、PDFから金額をコピーして貼り付ける
- 社内システムやCSVの出力で、項目によって全角のまま出てくる
見分けるときは、数字なのに左寄せになっていないか、ほかのセルよりわずかに横幅が広くないかを見ます。確実に判定するなら、隣の列に=ISNUMBER(B2)を入れてFALSEのセルを探してください。直すときは、件数が少なければ日本語入力をオフにして打ち直すのが早く、多ければ元の列を残したまま次の式で半角の数値に変換します。
=VALUE(ASC(B2))ASCは全角の文字を半角に変える関数ですが、それだけだと結果が文字列のままになることがあるので、外側のVALUEで数値に戻しています。同じ表に何度も入力が続くなら、対象範囲の「データの入力規則」で、「日本語入力」タブを「オフ（英語モード）」にしておくと、セルを選んだ瞬間に半角入力へ切り替わり、そもそも全角が入らなくなります。
先頭にアポストロフィが付いているとき
セルには「128000」と表示されているのに、クリックして数式バーを見ると「’128000」になっている。これが先頭のアポストロフィです。セルの表示と数式バーの表示が食い違っていたら、まずこれを疑ってください。
アポストロフィは、Excelに「このあとの内容は文字として保存して」と伝える指示です。データそのものではないので、セルには表示されず、=LEN(B2)で文字数を数えても1文字として数えられません。目で探しても見つからないのはそのためです。
自分で打った覚えがなくても付いていることがあります。システムの画面からコピーして貼り付けたとき、CSVを開いたときにExcelが文字として読んだとき、そして「0012」のような先頭に0が付く番号を入力したときです。最後のケースは、先頭の0を消さないためのExcelの配慮です。
件数が少なければ、セルをダブルクリックして「’」を消し、Enterを押すだけで右寄せに変わります。まとまった量なら、前の「状況別の直し方」で紹介した区切り位置や、1を掛ける方法で一括変換できます。同じシステムから毎月CSVを受け取っているなら、ダブルクリックで開くのをやめて、「データ」タブの「テキストまたはCSVから」で読み込み、金額の列を最初から数値の型に指定しておくと、毎月の手直しそのものがなくなります。取り込みの手順はCSVを開いたら合計が0になるときの取り込み方で詳しく扱っています。
文字の数字が「ぽつぽつ」混ざっているとき
列がまるごと文字になっていれば、左寄せの列がすぐ目に入ります。困るのは、何百行のうち数行だけが文字になっているケースです。
3つの課から届いた経費の表、およそ180行を1枚にまとめた場面で考えます（数字は説明用の作例です）。SUMの合計は486,200円、各課の小計を電卓で足すと498,200円。差は12,000円で、原因は「4500」「7500」の2行だけが文字として入っていたことでした。以前ほかの用途で使っていたセルに、表示形式の「文字列」が残ったまま金額を打ち込んでいたのです。画面ではほかの行とまったく見分けがつかず、範囲の選び直しや非表示行の確認で30分ほど消えていきます。
こういうときは、1セルずつ目で追う前に「何件混ざっているか」を数えます。
=COUNTA(B2:B181)-COUNT(B2:B181)COUNTAは空白以外のセルを、COUNTは数値のセルだけを数えるので、差がそのまま文字のセルの件数になります。0なら混ざっていません。冒頭の例なら「2」と出て、その時点で見当がつきます。ステータスバーを右クリックして「数値の個数」にチェックを入れておけば、列を選ぶだけで「データの個数」と並べて見比べることもできます。
場所を探すときは、列を選んで「Ctrl」＋「G」→「セル選択」→「定数」を選び、下のチェックを「文字」だけ残して「OK」を押します。文字のセルだけがまとめて選択されるので、そのまま塗りつぶしの色を付けておくと、直したあとの確認にも使えます。
関数によって、ズレ方が違う
文字の数字が混ざった表がやっかいなのは、関数ごとに狂い方が違い、しかもどれもエラーを出さないことです。半角の数値100、文字の「100」、全角の「１００」の3つが並んでいる列で比べると、次のようになります。
- SUM：文字の2つを飛ばして、100を返す
- COUNT：数値の1つだけを数えて、1を返す
- 「=A2+A3」のような足し算：文字の「100」を数値に読み替えるので、200になる。1行ずつ足していった累計とSUMの合計が食い違うのは、このため
- COUNTIF：条件を数値の100にしても、半角の文字「100」は数える。件数は合っているのに金額だけ合わない、という症状になりやすい
- VLOOKUP・XLOOKUP：数値の1001と文字の「1001」は別物として扱われ、#N/Aになる
- ピボットテーブル：同じ100が、別々の2行に分かれて表示される
件数が合っているからデータは正常、とは言い切れないわけです。
商品コードや社員番号のように、文字のままにしておきたい列を検索するときは、表を直すのではなく、検索する値のほうを文字に合わせる書き方もあります。
=VLOOKUP(E2&"",$A$2:$C$500,3,FALSE)E2に「&””」（空の文字）をつなげると、E2に数値が入っていても文字として検索されます。
数値なのに合わない「隠れ小数」
もう一つ、ここまでの方法をすべてすり抜けるパターンがあります。画面には100と出ているのに、中身が99.996の数値になっているセルです。単価×数量の結果や、税込金額から税抜金額を逆算した結果をそのまま使うと、割り切れない小数が残ります。表示形式で小数点以下を0桁にしていれば、画面上はきれいな100に見えます。
中身は数値なので、ISNUMBERはTRUEを返し、緑の三角も出ません。見つけるには、列を選んで「小数点以下の表示桁数を増やす」ボタンを何回か押すのが早道です。式で探すなら、=B2<>ROUND(B2,0) と入れると、整数でないセルがTRUEになります。円単位の金額のように、小数が入るはずのない列で使うと効きます。
直すときは、作業列で =ROUND(B2,0) として丸めてから、値として貼り付け直します。Excelのオプションにある「表示桁数で計算する」は、ブック全体の値を表示どおりに丸めてしまい、元の値に戻せないので避けたほうが無難です。端数の扱いそのものは、小数点の見えないズレをROUNDで直す方法で詳しく扱っています。
数値に戻していい列、文字のまま残すべき列
全角もアポストロフィも、「計算に使う列なら直す」が原則です。逆に、計算に使わない列は文字のまま残しておかないと壊れることがあります。
| データの種類 | 計算する？ | 扱い |
|---|---|---|
| 売上金額・数量・単価 | する | 数値に戻す |
| 社員番号（0011など） | しない | 文字のまま残す。数値にすると「11」になる |
| 郵便番号・電話番号 | しない | 文字のまま残す |
| 品番・型番（0045-Aなど） | しない | 文字のまま残す |
社員番号の「0011」を数値に変えると、Excelは11として保存します。名簿の照合やVLOOKUPの検索値に使っている列なら、その瞬間に突き合わせが全部失敗します。区切り位置や乗算を「シート全体」にかけるのは避け、計算に使う列だけを選んでから実行してください。作業前にファイルを別名で保存しておけば、範囲を広く取りすぎたときにも戻れます。
直したはずなのに、まだ合計が合わないとき
アポストロフィを消した、全角を直した、それでも左寄せのまま。そんなときは、次の順に疑います。
- セルの表示形式が「文字列」になっている：この設定のままだと、入れ直した数字もまた文字として保存されます。表示形式を「標準」に変えてから、あらためて区切り位置か乗算を実行します。順番を逆にすると効きません
- 計算方法が「手動」になっている：「数式」タブの「計算方法の設定」が手動だと、データが直っても合計欄が古いままです。F9を押すか、「自動」に戻します
- スペースや単位が一緒に入っている：「1,280 円」のように余計な文字が残っていれば、文字列のままです。単位なら数字に単位を付けて入力した表の直し方、見えない空白なら見えない空白や改行の見つけ方と消し方を参考にしてください
原因は1つとは限りません。全角とアポストロフィとスペースが同じ列に同居していることも、実務ではよくあります。迷ったら、隣の列に=ISNUMBER()を並べ、FALSEのセルが1つも残っていないことを最後に確かめてください。
よくある質問
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では数字と文字列は別物として扱われる」という基本ルールとして押さえておくと、これから先どんな関数を使うときにも役立ちます。
似たような「数字と文字列の混在」で困っている場合は、こちらの記事もあわせて参考にしてみてください。
数字と文字が混ざったセルの見分け方と直し方 1つのセルの中に数字と文字列が混在しているケースについて、見分け方と直し方を解説しています。
初心者シリーズでは、他にも表の作り方やコピペが原因のトラブルを扱っています。あわせてチェックしてみてください。
なお、文字列化に全角数字やスペースが重なっているなど、原因が1つとは限らない表もあります。6つの原因をまとめて絞り込む手順は、もう一歩踏み込みたい方向けにSUM関数が合わない原因を3分で絞り込む診断フロー（note）にまとめています。
