「100円」と入力した表は合計できない|Excelで単位を付けたまま計算する方法

アイキャッチ:「100円」と入力した表は合計できない。NGは単価に円まで入力して合計0、OKは表示形式で円を付けて合計2,530円 初心者シリーズ

新人が備品の購入リストを作って上司に提出する場面を例にします。単価の列に「1,200円」「850円」と、単位まできちんと打ち込む。本人は、誰が見ても分かる親切な表を作ったつもりです。

ところが、合計欄に入れたSUM関数の結果が「0」。式を消して入れ直しても0、範囲を選び直しても0。提出時間が迫っていれば、電卓で合計を出して手入力し、そのまま出してしまうかもしれません。あとで原因が分かると、かなり脱力するはずです。丁寧にやったつもりの作業が、そのまま原因だからです。

単価の列に「850円」「1,200円」のように単位まで入力され、すべて左寄せになってSUMの合計が0になっている備品リスト
赤枠の合計が0のままです

Excelの計算トラブルの相談を受けていると、この「良かれと思ってやったことが原因」というパターンは本当に多いと感じます。数字が文字列になっている、全角が混ざっている、余計なスペースが入っている——こうしたケースは、たいてい「気づかないうちに混入していた」ものです。ところが単位の問題だけは、入力した本人が意図してやっている点が違います。だから、原因として疑うのが一番遅くなるのです。

この記事では、単位付きの数字がなぜ計算されないのかという理屈よりも先に、今その表が原因かどうかを判定する手順と、単位の見た目を残したまま計算できる状態に戻す具体的な作業を順番に説明していきます。

ここで扱うのは、「円」「個」などの単位を数字と一緒に打ち込んだ列です。単位以外の原因も疑わしいときは、合計のずれ方から原因を探す方法で先に当たりを付けられます。

先に結論だけ言うと

急いでいる方のために、要点を3つだけ先に置いておきます。

  • セルに 100円 と入力すると、Excelはそれを数値ではなく文字列として保存する。だからSUM関数の合計に入らない
  • セルには 100 だけを入力し、「円」は表示形式(ユーザー定義書式)で見せる。これで見た目も計算も両立できる
  • すでに単位を打ち込んでしまった表は、置換で単位を消し、数値に戻してから表示形式を設定し直せば元通りになる

ここから先は、この3つをそれぞれ「どう確かめるか」「どう操作するか」に分けて掘り下げていきます。

「30個」「5箱」のように単位が何種類も混ざっている列、「20A」のような型番が入り込んだ列、合計ではなく平均だけがおかしい表については、記事の後半でそれぞれ扱っています。

まず、本当に単位が原因かを確かめる

合計が合わないとき、いきなり修正に入るのはおすすめしません。原因が単位ではなく別のところにあった場合、置換や再入力の作業がすべて無駄になるからです。順番としては、次の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円」のような単位付きの入力では出ないことも多いのですが、文字列として保存された数字には、セルの左上に小さな緑の三角マークが表示される場合があります。マークが出ているセルを選ぶと、警告アイコンから「数値に変換する」を選べることがあります。これで直るならそれが一番早いので、まずマークの有無を確認しておく価値はあります。

なお、①〜④を試しても数値として認識されない理由が分からないときは、単位以外の原因が混ざっている可能性があります。とくに多いのが、半角と全角の混在と、目に見えないスペースです。

数字そのものが全角で入力されている場合も、Excelは同じように文字列として扱います。

また、単位を消したのに計算されないという場合は、見えないスペースが入っているときの探し方も確認してみてください。単位と一緒にスペースが打たれているケースは珍しくありません。

なぜ「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"円" を入れたダイアログ。サンプルは1,200円
赤枠の2か所を選ぶと「円」付きで表示されます
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. どこか空いているセルに 1 と入力し、そのセルをコピーする
  2. 直したい範囲を選択して右クリックし、「形式を選択して貼り付け」を開く
  3. 演算の欄で「乗算」を選んでOKを押す

範囲内のすべての値に1を掛けることになるので、値そのものは変わらないまま、Excelが数値として再認識してくれます。文字列の数字をまとめて数値化する定番の方法です。作業が終わったら、最初に入力した 1 のセルは削除しておきます。

なお、置換ではなく数式で処理したい場合は、隣の列に次の式を入れる方法もあります。

=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"円",""),",",""))

SUBSTITUTEで「円」とカンマを取り除き、VALUEで数値に変換する式です。元データを残したまま変換結果を作れるので、あとで照合したいときはこちらが安全です。変換後、値だけをコピーして貼り付ければ完成です。

単位が何種類も混ざっているとき

ここまでは「円」や「個」が1種類だけ付いている列を前提にしてきました。他部署から受け取った表だと、「30個」「5箱」「12本」のように、1つの列に何種類もの単位が並んでいることがあります。

置換で消すこと自体は、単位の種類だけ繰り返せばできます。ただ、手を動かす前に一度考えてほしいのは、「30個と5箱を足した35に意味があるか」です。たいていの場合、意味はありません。単位がばらばらということは、そもそも同じ列で合計してはいけないデータが並んでいる、ということでもあります。

この場合は単位を消すのではなく、単位を別の列に移します。数量の列には数字だけ、隣に「単位」の列を作って「個」「箱」「本」を入れる。前半の方法4と同じ形です。こうしておけば、SUMIFSで「単位が箱のものだけ合計」といった集計ができるようになります。

元の列を残したまま、作業列で数字だけを取り出したいときは、SUBSTITUTEを重ねます。

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"個",""),"箱",""),"本",""))

内側から順に「個」「箱」「本」を消して、最後にVALUEで数値にしています。消し忘れた単位が残っているセルは#VALUE!になるので、エラーが出た行を見れば、想定していなかった単位(「コ」「ケース」など)がすぐ見つかります。SUBSTITUTEが4つ、5つと増えていくようなら、式で頑張るより列を分けたほうが、あとで表を触る人にとっても楽です。

単位の種類は同じでも、「円」と「千円」のように桁が違う単位が混ざっている場合は、数字を取り出すだけでは直りません。そのケースは円と千円が混ざった列の見つけ方と直し方で扱っています。

「20A」のような型番が混ざっているとき

数量の列に「20A」「20B」「S-100」のような値が入っていることもあります。入力した人の頭の中では「Aタイプが20個」なので数字のつもりですが、Excelにとってはアルファベットが1文字でも入っていれば文字です。

数量の列に「20A」「20B」が混ざり、その2行が足されずSUMの合計が12・8・30だけの50になっている表
赤枠の2つは文字として扱われ、合計から外れています

ここで「A」や「B」を置換で消してしまうと、数字は合計できるようになる代わりに、AとBの区別が消えます。これは修正ではなく、データを壊しているのと同じです。

型番が混ざっているときは、列を2つに分けます。「数量」と「タイプ」です。末尾の1文字だけがタイプを表しているなら、作業列で次のように切り出せます。

数量:=VALUE(LEFT(B2,LEN(B2)-1))
タイプ:=RIGHT(B2,1)

桁数やタイプの文字数がばらばらな場合は、式で無理に分けるより、データタブの「フラッシュフィル」を使うほうが早いことがあります。最初の2〜3行だけ手で「20」「A」と入れて「Ctrl」+「E」を押すと、残りの行を同じパターンで埋めてくれます。終わったら、埋まった値が本当に正しいか、何行か元の値と見比べてください。

1つのセルに2つの情報を入れない、という考え方は、表の作り方全体にかかわる話です。表の構造から見直したいときは、1行1データの表に作り直す手順も参考になります。

合計は合っているのに、平均だけおかしいとき

単位付きのセルが混ざっていても、そのセルの値がたまたま小さかったり、別の行で相殺されたりして、合計の違和感に気づかないことがあります。そういう表で先に崩れるのが、平均です。

10店舗の客単価を並べた列で、2店舗分だけ「850円」「920円」と単位付きで入っていたらどうなるか、説明用の数字で見てみます。AVERAGEはこの2つを0として扱うのではなく、最初から存在しないものとして飛ばします。つまり10で割るつもりが、8で割っているわけです。分母が変わるので、平均は実際より高く出ます。0として足されるなら低く出るはずなので、「平均がやけに高い」というのは、文字のセルを疑う手がかりになります。

10店舗の客単価のうちB店「850円」とD店「920円」が文字のため平均から外れ、AVERAGEが1,134、抜けている件数が2と表示された表
赤枠の2店が分母から抜け、平均が高く出ています

AVERAGEの結果がおかしいと感じたら、合計と同じように件数から確かめます。

=COUNTA(B2:B11)-COUNT(B2:B11)

ここが0でなければ、その数だけ平均の計算から抜けているセルがあります。平均単価や達成率のような割り算の指標は、会議の資料にそのまま載ることが多いので、合計より先にこちらを点検しておくと安心です。

直したはずなのに、まだ合計されないとき

上の手順を踏んでも数値にならない場合、単位以外のものが混ざっています。確認する順番はこうです。

まず、セルをダブルクリックして編集状態にし、カーソルキーで文字の前後を移動してみてください。数字の手前や後ろで余計に1回カーソルが動くなら、そこにスペースが入っています。次に、数字が半角かどうか。全角の 100 は見た目が似ていますが、Excelは別物として扱います。

それでも解決しない場合は、数字の先頭にアポストロフィ(’)が入っている可能性があります。数式バーには表示されるのにセルには見えない、やっかいな存在です。全角数字とあわせて、見分け方と直し方は上で紹介した記事の後半で扱っています。

また、Webサイトや基幹システムからコピーしてきたデータの場合は、改行コードや制御文字が一緒に入り込んでいることがあります。この場合はコピー時に見えない文字が混入している場合の手順で、CLEAN関数を使った処理が必要になります。

二度とやらないための予防策

直したあとに大事なのは、同じことが起きない形にしておくことです。とくに複数人で触る表では、自分が気をつけるだけでは足りません。

一つ目は、入力用のシートを最初から作っておくことです。データを入れる列にはあらかじめ表示形式を設定しておき、見出しにも単位を書いておく。入力する人が「単位を書かなきゃ」と思わない状態を先に作ってしまうのが、結局いちばん効きます。

二つ目は、データの入力規則を使う方法です。データタブの「データの入力規則」で、入力値の種類を「整数」や「小数点数」に設定しておくと、単位付きの文字列を入力しようとした時点でエラーメッセージが出ます。エラーメッセージの文言も自分で設定できるので、「数字だけを入力してください(単位は自動で表示されます)」と書いておくと親切です。

三つ目は、外部から取り込んだデータを一度チェックする習慣です。他部署からもらったファイルや、システムから出力したCSVは、単位付きになっていることが本当に多いです。集計に使う前に、COUNT関数で行数と一致するか見るだけでも、事故はかなり減ります。

人の都合とExcelの都合をすり合わせる

このシリーズを続けていて思うのは、Excelのトラブルの多くが「人間の都合とExcelの都合がすれ違ったところ」で起きているということです。

人間は、100円という表記をひとかたまりの情報として読みます。数字と単位を分けて考えたりしません。一方でExcelは、計算できるものとできないものを厳密に区別します。この差が、そのままトラブルの正体になっています。

だから、覚えるべきことは「単位を付けてはいけない」という禁止事項ではないと思っています。セルの中身と、画面に見えているものは別物である——この一点を理解すれば十分です。この考え方が身につくと、日付が計算できないときも、パーセントの数字がおかしいときも、「では中身は何になっているのか」と発想が向かうようになります。

同じ「中身と見た目のズレ」という観点では、列の意味が途中で変わっている表の直し方も一緒に読んでおくと、表を作るときの判断基準が固まりやすいと思います。

単位は入力せず、見せ方で付ける

最後に、今日の作業をチェックリストの形で置いておきます。

  • 合計が合わないとき、まずCOUNT関数で「数値として認識されている個数」を数える
  • 個数が足りなければ、セルの中身に数字以外の文字が入っていないか疑う
  • 単位は表示形式(ユーザー定義書式)か、列の見出しで表現する
  • 入力済みの単位は、範囲を選択したうえで置換して削除する
  • 置換後は必ずCOUNT関数で再確認し、数値になっていなければ「1を掛ける」操作で変換する
  • 行ごとに単位が違うなら、数値と単位を別の列に分ける

単位付きで入力してしまった表があっても、慌てる必要はありません。単位を外して数値に戻し、表示形式で付け直せば、見た目はほとんど変わらないまま、計算できる表に作り変えられます。一度この形にしておけば、集計のたびに悩まされることもなくなります。

単価を数値で入力し表示形式で「円」を付けた表。数式バーには1200と表示され、合計が5,250円になっている
緑枠のとおり、円付きのまま合計できます

なお、ここで扱ったのは「単位が1種類の列」を直すところまでです。実務では、単位付きの列が複数あって置換の順番を間違えると壊れる表や、書式だけ整っていて中身が文字列のまま引き継がれたファイルなど、もう一段階ややこしいケースにも出会います。そうした原因が重なった表で、何が起きているかを3分ほどで絞り込む手順は、SUM関数が合わない原因の診断フロー(note)にまとめています。もう一歩踏み込んで整理しておきたい方はのぞいてみてください。

タイトルとURLをコピーしました