新人のころ、備品の購入リストを作って上司に提出したことがあります。単価の列に「1,200円」「850円」と、単位まできちんと打ち込みました。自分では、誰が見ても分かる親切な表を作ったつもりでいました。
ところが、合計欄に入れたSUM関数の結果が「0」。式を消して入れ直しても0、範囲を選び直しても0。提出時間が迫っていたので、その日は結局、電卓で合計を出して手入力し、そのまま出しました。あとから同僚に見てもらって原因が分かったとき、正直かなり脱力しました。丁寧にやったつもりの作業が、そのまま原因だったからです。
Excelの計算トラブルの相談を受けていると、この「良かれと思ってやったことが原因」というパターンは本当に多いと感じます。数字が文字列になっている、全角が混ざっている、余計なスペースが入っている——こうしたケースは、たいてい「気づかないうちに混入していた」ものです。ところが単位の問題だけは、入力した本人が意図してやっている点が違います。だから、原因として疑うのが一番遅くなるのです。
この記事では、単位付きの数字がなぜ計算されないのかという理屈よりも先に、今その表が原因かどうかを判定する手順と、単位の見た目を残したまま計算できる状態に戻す具体的な作業を順番に説明していきます。
先に結論だけ言うと
急いでいる方のために、要点を3つだけ先に置いておきます。
- セルに
100円と入力すると、Excelはそれを数値ではなく文字列として保存する。だからSUM関数の合計に入らない - セルには
100だけを入力し、「円」は表示形式（ユーザー定義書式）で見せる。これで見た目も計算も両立できる - すでに単位を打ち込んでしまった表は、置換で単位を消し、数値に戻してから表示形式を設定し直せば元通りになる
ここから先は、この3つをそれぞれ「どう確かめるか」「どう操作するか」に分けて掘り下げていきます。
まず、本当に単位が原因かを確かめる
合計が合わないとき、いきなり修正に入るのはおすすめしません。原因が単位ではなく別のところにあった場合、置換や再入力の作業がすべて無駄になるからです。順番としては、次の4つを上から試していくのが早いです。
① 右寄せか左寄せかを見る
一番手軽な方法です。何も書式を触っていないセルであれば、Excelは数値を自動的に右寄せ、文字列を自動的に左寄せで表示します。金額の列を上から眺めてみて、他のセルと文字の位置が揃っていない行があれば、そこが怪しいということになります。
ただし、この方法には落とし穴があります。誰かが見た目を整えるために「中央揃え」や「右揃え」をあらかじめ設定していた場合、文字列でも右寄せで表示されてしまい、見分けがつきません。共有ファイルや引き継いだファイルでは、この判定はあまり当てにならないと考えてください。
② COUNT関数で数えてみる
より確実なのがこの方法です。空いているセルに次のように入力します。
=COUNT(B2:B20)COUNT関数は、指定した範囲の中で数値として認識されているセルの個数だけを数えます。B2からB20までに19行分のデータが入っているのに、この結果が「0」や「5」など明らかに少ない数字を返したら、残りのセルは数値として扱われていないことになります。
さらに、次の式も併せて入れてみてください。
=COUNTA(B2:B20)COUNTAは空白以外のセルをすべて数えます。COUNTAが19、COUNTが0であれば、「データは19個入っているが、そのうち数値は1つもない」という意味です。ここまで確認できれば、原因はほぼ確定と言っていいでしょう。
③ ISNUMBER関数で1セルずつ判定する
範囲全体ではなく、どの行が犯人かを特定したいときは、隣の空き列に次の式を入れて下までコピーします。
=ISNUMBER(B2)数値なら「TRUE」、文字列なら「FALSE」が返ります。行数が多い表では、この列を作ってからフィルターでFALSEだけを抽出すると、修正すべき行が一目で並びます。作業が終わったら、この判定用の列は削除して構いません。
④ セルの左上の緑の三角を見る
「100円」のような単位付きの入力では出ないことも多いのですが、文字列として保存された数字には、セルの左上に小さな緑の三角マークが表示される場合があります。マークが出ているセルを選ぶと、警告アイコンから「数値に変換する」を選べることがあります。これで直るならそれが一番早いので、まずマークの有無を確認しておく価値はあります。
なお、①〜④を試しても数値として認識されない理由が分からないときは、単位以外の原因が混ざっている可能性があります。とくに多いのが、半角と全角の混在と、目に見えないスペースです。
数字そのものが全角で入力されている場合の判定方法は、全角数字が混ざっているケースの見分け方で詳しく整理しています。
また、単位を消したのに計算されないという場合は、見えないスペースが入っているときの探し方も確認してみてください。単位と一緒にスペースが打たれているケースは珍しくありません。
なぜ「100円」は計算できないのか
ここで、仕組みの話を少しだけしておきます。理屈が分かっていると、応用が利くようになるからです。
Excelがセルに保存するデータには種類があります。ざっくり分けると、計算に使える「数値」と、計算に使えない「文字列」です。日付や時刻も内部的には数値ですし、TRUE/FALSEのような論理値もありますが、初心者のうちは数値と文字列の2つを押さえておけば十分です。
Excelは、セルに入力された内容を見て、この種類を自動で判定しています。100 と打てば数値、りんご と打てば文字列。ここまでは直感どおりです。問題は 100円 のような混ざった入力です。Excelには「数字と単位が並んでいる」という発想がないため、数字以外の文字が1つでも含まれていた時点で、その内容全体を文字列として保存します。
SUM関数は、指定された範囲の中から数値だけを拾って足し算する関数です。文字列は「足せないもの」として、エラーも出さずに黙って飛ばします。つまりSUMは間違った動作をしているわけではなく、拾える数値が1つもなかったから0を返している、というだけなのです。ここが分かると、「関数が壊れている」という疑いが晴れて、データの方を見に行けるようになります。
この構造は、シリーズで扱ってきた他のトラブルとまったく同じです。
「見た目は数字なのに中身は文字列」という状態がなぜ生まれるのかについては、数字が文字列になる仕組みの基本にまとめてあります。単位の問題も、突き詰めればこの一種です。
よくある勘違いを3つ潰しておく
相談を受けているとき、修正に入る前によく出てくる質問があります。ここで先にまとめて答えておきます。
「セルの幅が狭いからでは？」——違います。列幅は表示上の都合でしかなく、Excelの中のデータには一切影響しません。幅が足りないときは #### と表示されるので、そもそも症状が異なります。
「SUMではなく別の関数なら計算できる？」——できません。SUMIFでもAVERAGEでも、数値を扱う関数はすべて同じルールで動きます。文字列として保存されている限り、どの関数を使っても結果は変わりません。ただしCOUNTAのように「文字列も数える」関数は反応するので、それを見て「データはちゃんと入っているのに」と混乱しがちです。
「オートSUMのボタンを使えば大丈夫では？」——オートSUMは、SUM関数を自動で入力してくれるだけの機能です。中身は手入力のSUMとまったく同じなので、結果も同じになります。むしろオートSUMは、上の行が文字列だと合計範囲を正しく認識できず、範囲そのものがずれることがあります。合計欄に入った式の範囲が想定どおりか、一度確認してみてください。
単位の「付け方」は4つある
ここからが本題です。単位を付けたいという気持ちを我慢する必要はありません。付け方を変えるだけで、見た目と計算を両立できます。方法は大きく4つあり、それぞれ向き不向きがあります。
方法1：通貨・会計の表示形式を使う
金額であれば、これが一番手軽です。対象のセルを選択して「ホーム」タブの数値グループを開き、「通貨」または「会計」を選びます。100 と入力されたセルが ¥100 と表示されるようになります。
セルの中身はあくまで 100 のままなので、SUMもAVERAGEも問題なく動きます。通貨と会計の違いは記号の位置で、会計形式は通貨記号を列の左端に揃えて表示するため、金額が縦に並ぶ表では見た目がきれいに揃います。請求書や経費精算のような書類では会計形式のほうが向いています。
方法2：ユーザー定義書式で好きな単位を付ける
「¥100ではなく100円と表示したい」「個数に個を付けたい」という場合は、ユーザー定義書式を使います。対象セルを選んで「Ctrl + 1」でセルの書式設定を開き、「表示形式」タブから「ユーザー定義」を選択、種類の欄に書式を入力します。
#,##0"円"これで、セルの中身は 100 のまま、画面上は「100円」と表示されます。ダブルクォーテーションで囲んだ文字が、そのまま単位として表示される仕組みです。よく使うものを挙げておきます。
0"個" → 10個
0.0"kg" → 5.2kg
#,##0"人" → 1,200人
#,##0"円";[赤]-#,##0"円" → マイナスを赤字で表示
#,##0,"千円" → 1,200,000 を 1,200千円と表示最後の書式は少しトリッキーですが、覚えておくと役に立ちます。#,##0 の後ろにカンマを1つ足すと、表示だけを1,000分の1にできます。データは元の金額のまま保持されるので、集計は正確なまま、資料上は「千円単位」で見せることができます。予算表や年次の売上表で桁が多くなりすぎるときに便利です。
方法3：列の見出しに単位を書く
書式設定が面倒に感じるなら、この方法から始めるのが現実的です。「売上」ではなく「売上（円）」、「数量」ではなく「数量（個）」というように、見出しに単位を書いておきます。
地味ですが、実務ではこれが一番トラブルが少ない方法だと感じています。書式に依存しないのでコピーしても崩れませんし、CSVで書き出したときにも意味が残ります。他の人がその表を触ることを考えると、見出しに書いてある情報は最も伝わりやすいのです。
方法4：単位を別の列に分ける
数量と単位がバラバラの表——たとえば「3箱」「12本」「5kg」のように、行ごとに単位が違う場合は、数値の列と単位の列を分けます。B列に数量、C列に単位という形です。
この形にしておくと、あとからSUMIFで「kgのものだけ合計する」といった集計もできるようになります。単位が混在する表を1列にまとめてしまうのは、あとから必ず困る形です。
なお、単位そのものは付けていなくても、同じ列の中に「円」と「万円」が混ざっているケースも要注意です。こちらは同じ列に円と万円が混ざっている場合の対処法で扱っています。数字だけを見ていると気づけない、桁違いの集計ミスにつながります。
どれを選ぶか
迷ったときの目安をまとめておきます。単位が列全体で共通していて、金額を扱うなら方法1か2。社内で回覧する資料で、書式が崩れる心配をしたくないなら方法3。行ごとに単位が違うなら方法4。この判断を最初にしておくと、あとから表を組み直す手間が減ります。
すでに単位付きで入力してしまった表の直し方
「もう300行分入力してしまった」という場合の手順です。3ステップで進めます。
ステップ1：ファイルを別名で保存する
置換は一括で内容を書き換える操作なので、実行前に必ずバックアップを取ってください。「名前を付けて保存」で日付入りのファイル名にしておけば十分です。この一手間を省いて泣いた経験が私自身にあるので、しつこいようですがおすすめします。
ステップ2：範囲を選択してから置換する
修正したい列（またはセル範囲）をドラッグで選択します。ここで範囲を選ばずに置換を実行すると、シート全体が対象になります。 見出しの「円」や、備考欄に書いた「〜円以内で」といった文章まで書き換わってしまうので、必ず先に範囲を選んでください。
範囲を選んだ状態で「Ctrl + H」を押し、「検索する文字列」に 円 と入力します。「置換後の文字列」は空欄のままにして、「すべて置換」をクリックします。これで範囲内の「円」だけが削除され、数字が残ります。
カンマ付きで 1,200円 のように入力していた場合は、カンマも一緒に消しておくと確実です。同じ手順で「検索する文字列」に , を指定してもう一度置換してください。
ステップ3：数値に戻ったか確認する
ここが抜けやすい工程です。置換で文字を消しても、セルの表示形式が「文字列」に設定されたままだと、数字だけになっても文字列として扱われ続けることがあります。
先ほどのCOUNT関数をもう一度使って、数値として認識されているか確かめてください。まだ0のままなら、次の操作で強制的に数値に変換できます。
- どこか空いているセルに
1と入力し、そのセルをコピーする - 直したい範囲を選択して右クリックし、「形式を選択して貼り付け」を開く
- 演算の欄で「乗算」を選んでOKを押す
範囲内のすべての値に1を掛けることになるので、値そのものは変わらないまま、Excelが数値として再認識してくれます。文字列の数字をまとめて数値化する定番の方法です。作業が終わったら、最初に入力した 1 のセルは削除しておきます。
なお、置換ではなく数式で処理したい場合は、隣の列に次の式を入れる方法もあります。
=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"円",""),",",""))SUBSTITUTEで「円」とカンマを取り除き、VALUEで数値に変換する式です。元データを残したまま変換結果を作れるので、あとで照合したいときはこちらが安全です。変換後、値だけをコピーして貼り付ければ完成です。
直したはずなのに、まだ合計されないとき
上の手順を踏んでも数値にならない場合、単位以外のものが混ざっています。確認する順番はこうです。
まず、セルをダブルクリックして編集状態にし、カーソルキーで文字の前後を移動してみてください。数字の手前や後ろで余計に1回カーソルが動くなら、そこにスペースが入っています。次に、数字が半角かどうか。全角の １００ は見た目が似ていますが、Excelは別物として扱います。
それでも解決しない場合は、数字の先頭にアポストロフィ（’）が入っている可能性があります。数式バーには表示されるのにセルには見えない、やっかいな存在です。詳しくはアポストロフィが付いているときの見分け方で解説しています。
また、Webサイトや基幹システムからコピーしてきたデータの場合は、改行コードや制御文字が一緒に入り込んでいることがあります。この場合はコピー時に見えない文字が混入している場合の手順で、CLEAN関数を使った処理が必要になります。
二度とやらないための予防策
直したあとに大事なのは、同じことが起きない形にしておくことです。とくに複数人で触る表では、自分が気をつけるだけでは足りません。
一つ目は、入力用のシートを最初から作っておくことです。データを入れる列にはあらかじめ表示形式を設定しておき、見出しにも単位を書いておく。入力する人が「単位を書かなきゃ」と思わない状態を先に作ってしまうのが、結局いちばん効きます。
二つ目は、データの入力規則を使う方法です。データタブの「データの入力規則」で、入力値の種類を「整数」や「小数点数」に設定しておくと、単位付きの文字列を入力しようとした時点でエラーメッセージが出ます。エラーメッセージの文言も自分で設定できるので、「数字だけを入力してください（単位は自動で表示されます）」と書いておくと親切です。
三つ目は、外部から取り込んだデータを一度チェックする習慣です。他部署からもらったファイルや、システムから出力したCSVは、単位付きになっていることが本当に多いです。集計に使う前に、COUNT関数で行数と一致するか見るだけでも、事故はかなり減ります。
研究員メモ
このシリーズを続けていて思うのは、Excelのトラブルの多くが「人間の都合とExcelの都合がすれ違ったところ」で起きているということです。
人間は、100円という表記をひとかたまりの情報として読みます。数字と単位を分けて考えたりしません。一方でExcelは、計算できるものとできないものを厳密に区別します。この差が、そのままトラブルの正体になっています。
だから、覚えるべきことは「単位を付けてはいけない」という禁止事項ではないと思っています。セルの中身と、画面に見えているものは別物である——この一点を理解すれば十分です。この考え方が身につくと、日付が計算できないときも、パーセントの数字がおかしいときも、「では中身は何になっているのか」と発想が向かうようになります。
同じ「中身と見た目のズレ」という観点では、列の意味が途中で変わっている表の直し方も一緒に読んでおくと、表を作るときの判断基準が固まりやすいと思います。
まとめ
最後に、今日の作業をチェックリストの形で置いておきます。
- 合計が合わないとき、まずCOUNT関数で「数値として認識されている個数」を数える
- 個数が足りなければ、セルの中身に数字以外の文字が入っていないか疑う
- 単位は表示形式（ユーザー定義書式）か、列の見出しで表現する
- 入力済みの単位は、範囲を選択したうえで置換して削除する
- 置換後は必ずCOUNT関数で再確認し、数値になっていなければ「1を掛ける」操作で変換する
- 行ごとに単位が違うなら、数値と単位を別の列に分ける
単位付きで入力してしまった表があっても、慌てる必要はありません。単位を外して数値に戻し、表示形式で付け直せば、見た目はほとんど変わらないまま、計算できる表に作り変えられます。一度この形にしておけば、集計のたびに悩まされることもなくなります。
なお、ここで扱ったのは「単位が1種類の列」を直すところまでです。実務では、単位付きの列が複数あって置換の順番を間違えると壊れる表や、書式だけ整っていて中身が文字列のまま引き継がれたファイルなど、もう一段階ややこしいケースにも出会います。そうした複合的なパターンへの対処や、表示形式のカスタム書式の応用例は、note会員限定コンテンツのご案内にまとめています。もう一歩踏み込んで整理しておきたい方はのぞいてみてください。
コメント