棚卸しの数量表を印刷する直前に、合計欄を見て手が止まったことがあります。現物を数えた紙の集計は「1,240個」。ところが画面のExcelは「1,210個」。差はちょうど30個でした。
数え間違いを疑って倉庫に戻り、もう一度同じ棚を数え直しました。やはり紙のほうが正しい。ではExcelの関数が壊れているのかと思って数式バーを確認しても、=SUM(B2:B41) と、なんの変哲もない式が入っているだけです。範囲も合っています。
原因が分かったのは、印刷プレビューを閉じてから15分ほど経ったあとでした。40行あるうちの1行だけ、数量欄が「30個」と入力されていたのです。他の39行はすべて「30」「25」「18」といった数字だけ。たった1セル、うっかり単位を付けてしまったせいで、そのセルだけが合計から抜け落ちていました。
Excelを使い慣れていない頃の私は、こういうとき決まって関数を疑いました。書き方を間違えたのだろうと思って、SUMを打ち直し、範囲を選び直し、それでも直らないので閉じて開き直す。けれど何度やっても結果は同じです。当たり前で、原因は関数ではなくデータのほうにあったからです。
この記事では、「数字と文字が同じセルに入っている」というExcel初心者がとてもよくハマるトラブルについて、症状の見分け方から、データの形に応じた直し方、そして再発させないための入力ルールまでを順番に整理していきます。
この記事の結論を先に
長くなるので、先に要点だけ書いておきます。急いでいる方はここだけ読んで、必要な章に飛んでください。
- Excelは「30」を数値、「30個」を文字列として扱う。見た目が数字でも、文字が1つでも混ざっていれば計算対象から外れる
- SUM関数はエラーを出さずに、文字列のセルを黙って無視する。だから気づきにくい
- 犯人を探すときは目視ではなく、COUNT関数とISNUMBER関数で機械的に洗い出す
- 直し方はデータの形によって変わる。単位が1種類なら置換、複数混在なら置換の繰り返し、型番のような混在なら列を分ける
- 直したあとは必ず件数を検算する。そして単位は「セルの中」ではなく「列の見出し」か「表示形式」で表す
ここから先は、この5点をそれぞれ具体的に見ていきます。
まず症状を確認する：あなたの表はどのタイプか
同じ「数字と文字の混在」でも、画面に現れる症状はいくつかのパターンに分かれます。どのタイプかが分かると、原因の絞り込みがぐっと速くなります。
タイプA：合計が「少し」足りない
もっとも多いのがこのパターンです。合計は出ているのに、手計算や別資料と突き合わせると微妙に足りない。私が棚卸しでやってしまったのもこれです。
この場合、混入している文字列セルは1〜数個です。全体の何割かが欠けているのではなく、特定のセルだけが抜けているので、差額を見れば犯人の見当がつくこともあります。差が「30」なら、30と入力されているはずのセルを重点的に探す、という具合です。
タイプB：合計が「0」になる
合計欄に0と表示される場合は、範囲内のセルがすべて文字列になっている可能性が高いです。他システムから出力したCSVを開いたときや、Webページからコピーして貼り付けたときによく起こります。
このケースは差額から犯人を推理する必要がありません。範囲全体が対象なので、まとめて処理してしまえば解決します。
タイプC：合計は合っているのに平均や件数が合わない
SUMの結果は正しいのに、AVERAGE（平均）やCOUNT（件数）の結果だけが期待と違う、というパターンもあります。
これは、文字列セルが「0」として足されているのではなく、そもそも存在しないものとして扱われているために起こります。たとえば10件のデータのうち2件が文字列だと、AVERAGEは8件で割り算をします。母数が変わるので、平均だけが不自然に大きくなるわけです。
「合計は合っているから大丈夫」と判断してしまうと、平均単価や達成率のような割り算系の指標だけが静かにズレ続けます。個人的には、タイプCがいちばん厄介だと思っています。
なぜエラーが出ないのか
ここで一つ、押さえておきたいポイントがあります。SUM関数は、範囲の中に文字列があってもエラーを返しません。#VALUE! のような警告も出しませんし、赤い表示にもなりません。ただ静かに、その1セルを飛ばして計算を続けます。
これはSUM関数の仕様であって、不具合ではありません。「範囲内の数値だけを合計する」という設計になっているからです。親切といえば親切なのですが、初心者にとっては「間違っていることに気づけない」という点で、かなり手強い性質でもあります。
エラーが出るトラブルは、少なくとも異常には気づけます。けれどこのトラブルは、何食わぬ顔で間違った数字を出してきます。だからこそ、症状を知っておく価値があります。
Excelが「30個」を計算しない理由
ここから少しだけ仕組みの話をします。理屈が分かっていると、この先に出てくる似たトラブルにも応用が利くようになります。
Excelはセルの中身を2種類に仕分けている
Excelはセルに何かを入力された瞬間、その中身を「数値」か「文字列」かに仕分けています。ざっくり言えば、数字だけで構成されているものは数値、それ以外は文字列です。
たとえば「30」は数値です。「30個」は文字列です。「30 」（末尾に半角スペース）も文字列ですし、「３０」（全角数字）も、多くの場面で文字列として扱われます。
そして、数値は計算できるが、文字列は計算できない。これがExcelの根っこにあるルールです。SUMもAVERAGEもMAXも、すべてこのルールの上で動いています。
「20A」のような型番が落とし穴になりやすい
実務で見かける中でとくに厄介なのが、「20A」「S-100」「12号」のような、数字とアルファベットや記号が組み合わさったデータです。
入力する側は「これは数量20のAタイプ」というつもりでいるので、頭の中では数字として認識しています。ところがExcelにとっては、アルファベットが1文字でも入っていれば、それはもう完全に文字列です。「20A」を「20」と読み替えてくれることは絶対にありません。
数字が含まれているかどうかではなく、数字以外が含まれているかどうか。Excelはこちらで判断している、と覚えておくと分かりやすいと思います。
そもそも、なぜこの入力をしてしまうのか
念のため書いておくと、「30個」と入力してしまうのは、決して不注意な人だけの問題ではありません。むしろ、読み手のことを考えた自然な入力です。
紙の帳簿なら「30個」と書くほうが親切ですし、上司に見せる表でも単位が入っていたほうが伝わります。人間にとっては、そのほうが分かりやすい。
けれどExcelは、あくまで計算するためのソフトです。人間にとって読みやすい入力と、Excelが計算できる入力は、必ずしも一致しません。このズレを知らないまま「見やすい表」を作ろうとすると、今回のトラブルにぶつかります。逆に言えば、ズレの存在さえ知っていれば、見やすさと計算しやすさは両立できます。その方法は後半で扱います。
犯人を特定する3段階の手順
原因が分かったところで、実際にどのセルが文字列になっているのかを突き止めます。ここは目で探そうとすると時間を溶かすので、機械的にやるのがおすすめです。
段階1：右寄せか左寄せかをざっと見る
Excelには、初期設定のままなら数値は右寄せ、文字列は左寄せで表示されるというルールがあります。
数量の列をざっと眺めて、1つだけ左端に寄っているセルがあれば、それが有力な容疑者です。40行程度の表なら、これだけで見つかることも珍しくありません。
ただしこの方法には弱点があります。誰かが手動で中央揃えや右揃えを設定していると、文字列でも右に寄って見えてしまうのです。前任者から引き継いだファイルや、複数人で編集しているファイルでは、この見た目判定はあてになりません。
段階2：COUNT関数で件数を数える
より確実なのが、COUNT関数を使う方法です。空いているセルに次の式を入れます。
=COUNT(B2:B41)COUNT関数は、指定した範囲の中にある数値のセルだけを数えます。40行のデータがあるのに結果が39と出れば、1つだけ数値として扱われていないセルがある、ということです。
さらに、次の式と組み合わせると差がはっきりします。
=COUNTA(B2:B41)COUNTA関数は、空白でないセルをすべて数えます。文字列も数に入ります。つまり、COUNTAの結果からCOUNTの結果を引けば、文字列になっているセルの個数がそのまま分かります。
=COUNTA(B2:B41)-COUNT(B2:B41)この式の答えが0なら、範囲内はすべて数値です。1以上なら、その数だけ犯人がいます。作業前と作業後にこの式を見比べれば、直し漏れも防げます。
段階3：ISNUMBERで1セルずつ判定する
犯人が何個いるかまで分かったら、次はどこにいるかです。隣の空いている列（たとえばC列）に、次の式を入れて下までコピーします。
=ISNUMBER(B2)数値であればTRUE、文字列であればFALSEが返ってきます。あとはC列にフィルターをかけてFALSEだけを表示すれば、対象のセルが一覧で並びます。数百行、数千行の表でも、この方法なら数十秒で洗い出せます。
なお、検査用に作ったC列は作業が終わったら削除して構いません。むしろ残しておくと、後から見た人が「この列は何だろう」と混乱するので、消しておくほうが親切です。
補足：条件付き書式で色を付けておく
継続的に使う入力用シートであれば、条件付き書式を仕込んでおく手もあります。数量列を選択して、条件付き書式の「数式を使用して、書式設定するセルを決定」を選び、次の式を入れます。
=AND(B2<>"",ISNUMBER(B2)=FALSE)書式で塗りつぶし色を設定しておけば、文字列を入力した瞬間にセルが色づきます。入力した本人がその場で気づけるので、集計時にまとめて発覚するより、ずっと傷が浅くて済みます。
複数人でファイルを共有している場合、この仕掛けは地味ですがよく効きます。「入力ルールを守ってください」と口頭で伝えるより、色が変わるほうが早いからです。
直し方はデータの形によって変わる
犯人が特定できたら、いよいよ修正です。ここでよくある失敗が、「とりあえず全部置換すればいい」と考えてしまうことです。データの形によって適した方法は変わるので、4つのケースに分けて見ていきます。
ケース1：末尾の単位がすべて同じとき
「30個」「25個」「18個」のように、末尾の文字が1種類に統一されている場合。これがいちばん簡単で、置換機能でまとめて処理できます。
手順は次の通りです。
- 対象の列（または範囲）を選択する
- 「Ctrl + H」で置換ダイアログを開く
- 「検索する文字列」に「個」と入力する
- 「置換後の文字列」は空欄のままにする
- 「すべて置換」をクリックする
これで範囲内の「個」がすべて消え、数字だけが残ります。多くの場合、この時点で数値として認識され、SUMの結果も正しくなります。
ここで一つだけ注意点があります。必ず範囲を選択してから実行してください。範囲を選ばずに置換すると、シート全体が対象になり、商品名の「1個入り」や備考欄の文章まで書き換わってしまいます。作業前にファイルを別名で保存しておくと、さらに安心です。
ケース2：単位が複数混ざっているとき
「30個」「5箱」「12本」のように、列の中に何種類もの単位が混ざっているケースもあります。他部署から受け取った表では、わりとよく起こります。
このときは、置換を単位の種類だけ繰り返します。「個」を消し、「箱」を消し、「本」を消す。手間はかかりますが、確実です。
ただし、ここで立ち止まって考えたほうがいいことがあります。単位が混ざっているということは、そもそも同じ列で合計してはいけないデータが並んでいる可能性が高い、ということです。30個と5箱を足して35という数字を出しても、意味がありません。
この場合の正しい対処は、単位を消すことではなく、単位の列を別に作ることです。数量列には数字だけを入れ、隣に「単位」列を用意して「個」「箱」「本」を入れる。こうすればSUMIFSなどで単位ごとの集計ができるようになり、データとしての価値が一段上がります。
なお、単位そのものが揃っていない表の危険性については、別の記事で実害シナリオを含めて詳しく扱っています。
「円」と「万円」が同じ列に並んでいるような表で何が起きるかは、列ごとに単位がバラバラだと集計できない原因と対処法で具体例を挙げて解説しています。
ケース3：「20A」のような型番が混ざっているとき
数量列に「20A」「20B」のような型番が入り込んでいる場合、文字を消して「20」にしてしまうと、AとBの区別が失われます。これは修正ではなく、データの破壊です。
このケースでは、列を分けるのが正解です。数量は数量列に、型番は型番列に。1つのセルには1つの情報だけを入れる、というのがExcelの基本的な考え方です。
「1つのセルに複数の情報を詰め込む」という構造は、集計時にほぼ確実に問題を起こします。表の構造そのものが原因で集計が崩れるパターンについては、こちらの記事も参考になるはずです。
表の構造から見直したい方は、データが1行1データになっていない表の直し方もあわせてご覧ください。
ケース4：元データを残したまま数字だけ取り出したいとき
受け取った元データを書き換えたくない、という場面もあります。そういうときは、別の列に数式を置いて数字部分だけを取り出す方法があります。
末尾に単位が1文字だけ付いている場合なら、次の式で対応できます。
=VALUE(SUBSTITUTE(B2,"個",""))SUBSTITUTE関数で「個」を空文字に置き換え、VALUE関数で数値に変換する、という二段構えです。単位が複数あるなら、SUBSTITUTEを入れ子にします。
=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"個",""),"箱",""))見た目は複雑ですが、やっていることは「順番に文字を消す」だけです。式が長くなりすぎる場合は、素直に列を分けたほうが後々のメンテナンスは楽になります。
なお、VALUE関数は数字に変換できない文字列を渡されると #VALUE! エラーを返します。エラーが出たセルは「単位を消しきれていない」というサインなので、むしろ発見器として役立ちます。
直したあとは必ず検算する
修正が終わったら、面倒でも検算をしてください。先ほどの式をもう一度使います。
=COUNTA(B2:B41)-COUNT(B2:B41)結果が0になっていれば、範囲内はすべて数値です。1以上のままなら、まだどこかに文字が残っています。
私の経験では、置換の直後に「終わった」と思って作業を切り上げ、後日また同じ表で悩む、というのがよくある流れでした。検算の一手間は、その日の10秒と後日の30分を交換するようなものだと思っています。
効きそうで効かない対処法
このトラブルでは、「一見正しそうなのに、実は何も解決していない」対処法がいくつかあります。遠回りを避けるために、代表的なものを挙げておきます。
表示形式を「数値」に変えても直らない
いちばん多い誤解がこれです。セルを選んで表示形式を「文字列」から「数値」に変更しても、既に入力済みのデータは文字列のままです。
表示形式は「これから入力されるものをどう扱うか」「今の中身をどう見せるか」の設定であって、中身のデータ型そのものを変換する機能ではないからです。
表示形式を数値に変えたあとで、そのセルをダブルクリックしてEnterを押すと、そこで初めて数値に変わります。ただしこれは1セルずつの作業なので、件数が多い場合は現実的ではありません。表示形式の変更だけで直った気になるのは、いちばん危険なパターンです。
列幅を広げても意味はない
「###」と表示されているなら列幅の問題ですが、今回のトラブルとは別物です。数字が普通に見えているのに計算されないのであれば、列幅は関係ありません。
手動で足し算し直すのは最悪手
急いでいるときほど、電卓で足して数字を直接打ち込みたくなります。気持ちは分かるのですが、これをやると次に誰かがデータを更新したときに合計が追従しなくなり、間違いが固定化されます。
しかも、原因が残ったままなので同じことがまた起こります。目の前の数字を合わせるのではなく、原因を取り除く。遠回りのようで、これがいちばん速い道です。
二度と起こさないための入力ルール
直し方が分かったら、次は再発防止です。ここを押さえておくと、そもそもこのトラブルに出会わなくなります。
ルール1：単位は列の見出しに書く
いちばん簡単で、いちばん効果があるのがこれです。セルには数字だけを入れ、単位は見出しに書きます。
この表を、次のように変えます。
見出しに「（個）」と入れるだけで、読み手にも意味が伝わりますし、セルの中身は数値のままです。実務のExcel表で最もよく使われている書き方でもあります。
ルール2：どうしてもセルに単位を出したいなら表示形式
「見出しだけでは分かりにくい」「1つのセルに単位を出したい」という場合は、ユーザー定義の表示形式を使います。
対象のセルを選択し、「Ctrl + 1」でセルの書式設定を開き、「ユーザー定義」を選んで種類の欄に次のように入力します。
0"個"これで、セルには「30」と入力されているのに、画面には「30個」と表示されます。見た目は文字入り、中身は数値という理想的な状態です。当然、SUM関数も正しく動きます。
同じ要領で、#,##0"円" とすれば桁区切り付きの金額表示になりますし、0.0"kg" なら小数第1位まで表示できます。単位を付けたいときは、入力するのではなく書式で解決する。この発想に切り替えるだけで、集計トラブルは目に見えて減ります。
表示形式の設定手順をもっと詳しく知りたい方は、単位を付けたまま計算する方法で画面の流れに沿って解説しています。
ルール3：1つのセルには1つの情報だけ
型番と数量、氏名と部署、日付と曜日。これらを1つのセルにまとめたくなる場面は多いのですが、集計を前提とするなら分けておくのが鉄則です。
分かれているデータは、後からいくらでも結合できます。けれど混ざったデータを分けるのは手間がかかりますし、完全には戻せないこともあります。迷ったら分けておく、というのが安全側の判断です。
入力規則で文字を入力させない
複数人が入力するファイルなら、データの入力規則を設定しておくと確実です。
対象範囲を選択し、「データ」タブの「データの入力規則」を開いて、「入力値の種類」を「整数」または「小数点数」にします。これで、文字を含む値を入力しようとするとその場でエラーメッセージが出ます。
「エラーメッセージ」タブで、「数字だけを入力してください。単位は不要です」といった案内文を設定しておくと、入力する人にも意図が伝わります。ルールを紙で配るより、ファイルに埋め込むほうが守られます。
研究員のフィールドノート
この手のトラブルで印象に残っているのは、他部署から受け取った月次の実績表です。
数量の列に「120」「85」といった数字が並ぶ中、下のほうに数件だけ「120本」「85本」と単位付きのセルが混ざっていました。おそらく、途中で担当者が交代したのだと思います。前任者は数字だけ、後任者は単位付き。それぞれのやり方としては筋が通っていて、どちらも間違っていません。
けれど1枚の表に2つのルールが同居した瞬間、その表は集計できないデータになりました。
このとき学んだのは、Excelのトラブルの多くは技術の問題ではなく取り決めの問題だ、ということです。関数を知っているかどうかより、「この列には数字だけを入れる」という合意があるかどうかのほうが、はるかに影響が大きい。
だからいまは、共有する表を作るときは列の見出しに「（個）」のような単位を明記して、入力する人が迷わないようにしています。見出しに単位が書いてあれば、わざわざセルに単位を付ける人はほとんどいません。ルールを説明するより、ルールを守るのが自然になる形を先に作っておく。そのほうが結局は速いと感じています。
もう一つ、症状が似ていて紛らわしいトラブルもあります。数字と文字の混在ではなく、目に見えない文字が混ざっているケースです。
セルを見ても文字が見当たらないのに計算されない場合は、見えないアポストロフィが付いているケースを疑ってみてください。
また、末尾の空白が原因になっているケースも、見た目ではまず気づけないタイプのトラブルです。
よくある質問
Q1．置換で単位を消したのに、まだ合計されません
考えられる原因は3つあります。
1つ目は、消し忘れた単位が残っているケースです。「個」を消したけれど「コ」や「ｺ」が混ざっていた、というのはよくあります。COUNTAとCOUNTの差を取って、残件数を確認してみてください。
2つ目は、数字の前後にスペースが入っているケースです。全角スペースは特に見つけにくいので、置換で全角スペースを空欄に置き換えてみると解決することがあります。
3つ目は、セルの表示形式が「文字列」に設定されているケースです。この状態だと、単位を消しても文字列のままになります。表示形式を「標準」に戻したうえで、対象範囲をコピーし、空いているセルに「1」と入力してそれをコピー、対象範囲に「形式を選択して貼り付け」→「乗算」を実行すると、まとめて数値に変換できます。
Q2．CSVを開いたら数量列がまるごと文字列になっていました
システムから出力したCSVでは、列全体が文字列として読み込まれることがあります。これは今回のトラブルと原因は近いのですが、対処の入口が少し違います。
CSVが原因の場合は、CSV取り込みで文字列になるときの対処法で、区切り位置ウィザードを使った一括変換の手順を解説しています。
Q3．SUMは正しいのに、VLOOKUPだけ一致しません
SUMが正しく動いているなら、数量列は数値になっているはずです。VLOOKUPが一致しないのであれば、原因は検索キーの列にある可能性が高いです。
半角と全角が混ざっている、数値と文字列が混在している、といったケースは数値の形式が統一されていないときの見分け方で整理しています。
半角カナと全角カナの違いが原因のこともあるので、文字コードの違いで一致しないケースもあわせて確認すると、切り分けがしやすくなります。
Q4．日付を入力したセルも計算できません
日付も数値の一種としてExcelの内部では管理されているため、文字列になっていると同じような症状が出ます。「2026/3/27」と入力したつもりが文字列扱いになっている、というのは頻出のトラブルです。
日付でお困りの方は、日付が文字列になっているときの見分け方と直し方をご覧ください。原因の考え方は今回とまったく同じです。
もう一歩踏み込みたい方へ
ここまでの内容で、単一の列に単位が混ざっているケースは自力で解決できるはずです。
ただ実務では、もう少し込み入った状況もあります。単位が3種類以上混ざっていて置換の順番を間違えるとデータが壊れる表、数量と型番と備考が1つのセルに詰め込まれた表、毎月受け取るファイルで同じトラブルが繰り返し発生するケース。こうした「一段複雑な現場」への対応や、そのまま貼り付けて使えるチェック用の数式セットは、記事1本の分量では扱いきれませんでした。
複数の単位が混在する表の安全な直し方や、混在チェック用の数式テンプレートは、note会員限定コンテンツのご案内でまとめています。今回の内容をひととおり試したうえで、もう一歩踏み込みたい方はのぞいてみてください。
まとめ
最後に、この記事の要点をチェックリストの形で整理します。表がおかしいと感じたら、上から順に確認してみてください。
- 合計が「少し足りない」のか「0」なのか、症状のタイプを確認した
=COUNTA(範囲)-COUNT(範囲)で、文字列セルが何個あるかを数えた=ISNUMBER(セル)で、どのセルが文字列かを特定した- データの形（単位1種類／複数種類／型番混在）に合った直し方を選んだ
- 置換の前に、範囲を選択してファイルを別名保存した
- 修正後にもう一度件数を検算し、差が0になることを確認した
- 単位は「列の見出し」または「表示形式」で表す形に変えた
Excelのトラブルは、症状ごとに解決法を暗記しようとすると際限がありません。けれど今回のように、「数値であるべきデータが文字列になっている」という一点に集約して考えれば、初めて出会う症状にも落ち着いて対応できます。
見た目は判断材料にならない。中身のデータ型がすべてを決める。この感覚が身についたとき、Excelは急に扱いやすい道具に変わります。私自身、棚卸しの表で30個ぶんの差に悩んだあの日から、合計が合わないときにまず疑うのは関数ではなくデータになりました。
いま手元に、数字と文字が混ざった表があっても、慌てる必要はありません。文字を取り除いて数字だけにして、単位は見出しか表示形式で付け直す。それだけで、見やすさを保ったまま、きちんと計算できる表に作り変えられます。
似たトラブルをまとめて確認したい方は、初心者シリーズの記事一覧から、気になる症状の記事を探してみてください。
コメント