【Excel】文字と数字を同じセルに入れるときの注意|単位は表示形式で付ける

「100kg」「200kg」と入力したセルをSUMで合計しても、期待した300にならないことがあります。単位まで含めて文字列になったセルは、範囲を指定するSUMの数値として集計されないためです。計算に使う値は数値で残し、単位は見出しや表示形式で補うと扱いやすくなります。

一方、商品コードのように数字を含む文字列は、そのまま残すのが適切です。何でも数値へ変換するのではなく、計算する量か、識別する番号かを先に分けましょう。

1. 見た目ではなくセルの種類を確認する

標準の配置では、数値が右、文字列が左へ寄ることが多いものの、中央揃えなどを設定していれば判別できません。数式バーを見て単位まで入力されているか確認し、必要なら別の空セルに=ISNUMBER(A2)と入れます。A2が数値ならTRUE、文字列ならFALSEです。

たとえばA2へ100を入れ、表示形式でkgを付けたセルと、A3へ100kgと直接打ったセルは、見た目が似ていても異なります。A2を参照する計算は100を扱えますが、A3には単位を含む文字列が入っています。表示形式を「数値」へ変えるだけでは、100kgからkgを取り除いて数値に変換することはできません。

なお、通貨記号、パーセント、日付などは入力形式や地域設定によってExcelが数値として解釈するものもあります。「数字以外が1文字あれば必ず文字列」とは判断できません。実際のセルを型の確認式で調べるほうが確実です。

2. 合計から抜ける例を小さな表で確かめる

練習用シートのA2に100kg、A3に200kgと直接入力し、A4に=SUM(A2:A3)を入れます。両方が文字列なら結果は0です。今度はB2に100、B3に200を数値として入れ、B4に=SUM(B2:B3)を入れると300になります。

SUMが文字列のセルを無視するため、途中に単位付きの文字列が1個だけ混ざると、エラーではなく少ない合計が表示される場合があります。数字が出ているから全件が集計されたとは限りません。件数が多い列では、確認用にISNUMBERを下までコピーし、FALSEの行を調べる方法があります。

=A2+A3のような直接の足し算では、単位付き文字列を参照すると#VALUE!になる例があります。関数や演算子によって文字列の扱いが違うので、式を変えて偶然通すより、元データの列を整えることを優先します。修正前後の合計と対象件数を両方確認してください。

3. 数値を保ったまま単位を表示する

1. セルに単位を付けず100などの数値を入力する。
2. 対象範囲を選び、Ctrl+1で「セルの書式設定」を開く。
3. 「表示形式」の「ユーザー定義」を選ぶ。
4. 「種類」に0"kg"と入力し、サンプルを確認して確定する。

100は100kgと表示されますが、計算に使う値は100のままです。小数第1位まで必要なら0.0"kg"、円の金額に桁区切りを付けるなら#,##0"円"という例があります。小数桁を減らすと表示は丸められるため、測定値や単価で必要な精度を先に決めます。

書式のカンマには位置によって数値を千単位などへ縮めて表示する働きがあります。単純に#,"円"と書くと、期待した金額表示にならないため、桁区切りには#,##0を使います。1000など桁数が多い値、0、負の値を試して表示を確認しましょう。

4. 入力済みの単位付き文字列は補助列で直す

A2:A20がすべてkg単位であると確認できた場合、隣の列に=VALUE(SUBSTITUTE(A2,"kg",""))と入れると、kgを除いた文字を数値へ変換できます。元の列を残して、100kgが100、2.5kgが2.5になるか確かめます。空欄や注記、異なる単位が混ざる行は別に扱います。

この式はgをkgへ換算するものではありません。2000gを単に2000へ変え、2kgと同じ列で足すと間違った合計になります。また、SUBSTITUTEは文字の置換なので、関係のない注記まで消してよいかを判断しません。対象文字が単位の部分であることを確認できる列に限って使います。

結果にエラーが出たら、その行の文字を見ます。空白や小数点の表記、単位の揺れを確かめ、分からない値を0に置き換えて合計へ混ぜないでください。変換が済んだら数値の列を保存し、必要な場合だけコピーして値として貼り付けます。元列は照合が済むまで残します。

5. 識別番号と計算する数字を分ける

品番A100、郵便番号、電話番号、00125のような顧客コードは、足し算する対象ではありません。先頭のゼロを含む番号を数値へ変えると、元の識別情報が失われます。15桁を超える番号を数値として扱うと精度の制約もあるため、入力や取り込みの時点で文字列として保持します。

全角の「１２３」と半角の「123」は文字としては異なりますが、Excelが入力をどう解釈したかは、見た目だけでは決められません。既存データはISNUMBERなどで確認し、計算用の列だけを必要に応じて変換します。コードや名称まで一括で全半角変換する操作は別の変更です。

先頭にアポストロフィを付ける入力は、数字を文字列として残したい場合の方法です。これを一律に誤りとして削除しないでください。入力規則や取り込み設定を考える前に、列見出しで「数量」「コード」など役割が区別できるようにします。

6. 単位が混ざる表と外部出力の設計

たとえばkgへ統一するなら、gの行は1000で割るなど、変換規則を明示した補助列を設けます。単位表示だけkgに変えても数量自体は換算されません。元の数量・単位と換算後の数量を横に並べると、誤った桁を見つけやすくなります。

CSVへ出す場合は、Excelの表示形式がそのまま利用先の数値仕様になるとは限りません。単位付き表示が文字として書き出される場合もあるため、出力用列を決め、保存したCSVを改めて取り込んで確認します。表示用帳票とシステム連携用の列を兼用しない設計が役立ちます。

=A2&"円"のように式で文字を付けた結果も文字列です。文章を作るには使えますが、数値の表示形式を変えた場合とは違います。後の合計は、文字を付ける前の数値列を参照してください。

7. 変換後の確認と元へ戻す方法

修正前のブックを別名で残し、まず数行の試験で型・表示・合計を確認します。100kgと200kgなら、数値化した列の合計が300になり、表示にも適切な単位が付くことを見ます。単位が混在する表では、換算後の単位で合計していることを確認してください。

表示形式だけの変更を戻すなら、Ctrl+Zか、控えていた表示形式へ戻します。「標準」にすると単位表示は外せますが、元が小数や通貨の書式なら、その指定も改めて必要です。列の中身を置換したり、数値として上書きしたりした場合は、書式を戻しても元の文字列は復元しません。

先頭ゼロや長い番号を誤変換して保存した場合、見た目にゼロを足すだけで元の情報が戻るとは限りません。元ファイルから該当列を文字列として取り込み直します。計算できることだけをゴールにせず、値の意味と必要な情報が残っているかまで照合しましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】