Excel合計が合わない原因：数値列の直し方

導入
この記事で解決すること
なぜその管理表はうまくいかないのか
完成イメージ
改善手順
実務での注意点
まとめ

導入

売上管理や在庫管理のExcel管理表で、合計関数（SUM）の結果が手計算と合わない、ピボットで集計しようとしたら金額列が「合計」できない、ということはありませんか。よく調べると、金額列の値に「50,000円」「￥80,000」「120,000-」（末尾ハイフン）など、数値と一緒に文字や記号が入っていて、Excelが数値として認識していないケースもよくあります。

こうした集計の不一致は、入力者の入力癖の問題ではなく、「数値列には数値だけを入れる」というルールが表側で決まっていないことが原因です。「金額っぽく見せたい」「単位を一緒に書いた方が分かりやすい」という発想は自然ですが、集計の観点では文字列化を招きます。

この記事では、数値列を「数値のみ入力、見せ方は表示形式で対応」という方針に切り替え、既存データを整える方法を10分で完了させる手順として紹介します。終わったときに、SUM・AVERAGE・ピボット集計が正しく動くようになります。

この記事で解決すること

項目	内容
解決する課題	金額や数量が集計できない
主な原因	数値に文字や記号が混ざっている
解決方法	数値だけを入力する列にして表示形式で見せる
対象業務	売上管理・在庫管理・予算管理
対象人数	2〜30人
難易度	★☆☆☆☆
作業時間	10分
用意するもの	対象のExcelファイル／編集権限
効果	合計や平均が正しく出せる
向かないケース	文章で金額を説明する資料

なぜその管理表はうまくいかないのか

数値列に文字が混ざる管理表には、共通する状況があります。

数値列の入力ルールが、表のどこにも書かれていない
「50,000円」のように単位を一緒に書く人と、「50000」とだけ書く人が混在する
「￥80,000」「\80000」「80000円」「8万円」など、書き方が人ごとに違う
末尾に「-」「※」「（暫定）」などの注釈が混入する
SUM関数を入れても文字列セルがスキップされ、合計が小さく出る
ピボットで「合計／金額」を選んでも、文字列セルが原因で「カウント」しか出せない

これは入力者の見せたい気持ちではなく、「数値と単位を同じセルに入れる」運用が原因です。見直しは、「数値列は数値だけ、見せ方は表示形式」という方針に切り替えるところから始めます。

完成イメージ

10分後、数値列のすべてのセルが純粋な数値になり、表示形式（カンマ区切り・通貨記号など）で見た目を整えた状態になります。

改善前 — 数値と文字が混在し、SUMが正しく動かない:

案件番号	顧客	金額	数量
001	A社	50,000円	10個
002	B社	￥80,000	5個
003	C社	120,000	8
004	D社	40000-	3個
005	E社	8万円	12

金額列で =SUM(C2:C6) を計算しても、数値として認識されるのは「120,000」と「40000-」を除いた一部だけ。合計が大きく食い違います。

改善後 — 数値列は数値のみ、見た目は表示形式で:

「数値入力ルール」シート（新規追加）:

対象列	入力ルール	表示形式
金額	数値のみ。単位「円」「￥」「万円」を入れない	#,##0（カンマ区切り）
数量	数値のみ。単位「個」「件」を入れない	数値（小数なし）

データ表（元のSheet1） — 数値のみ、表示形式で整える:

案件番号	顧客	金額	数量
001	A社	50,000	10
002	B社	80,000	5
003	C社	120,000	8
004	D社	40,000	3
005	E社	80,000	12

セルの中身は純粋な数値、見た目はカンマ区切りで整っています。SUMは正しく370,000を返し、ピボットでの合計集計も問題なく動きます。

改善手順

10分ほどで4ステップを進めます。

ステップ1. 数値列のうち文字列化しているセルを特定する

各数値列にどんな混入があるかを確認します。

操作: 対象列の右隣に「型判定」列を追加し、=ISNUMBER(A2) で型を確認。FALSE が文字列。SUMPRODUCT で文字列の件数を一括カウントすることもできる: =SUMPRODUCT(--ISTEXT(C2:C100)) で文字列セルの数を表示。

記入例:

金額	型判定
50,000円	FALSE
￥80,000	FALSE
120,000	TRUE
40000-	FALSE
8万円	FALSE

ステップ2. 数値入力ルールを明文化する

数値列の入力ルールと表示形式を表に書き残します。

操作: 新しいシート「数値入力ルール」を追加。A1に「対象列」、B1に「入力ルール」、C1に「表示形式」と入力。A2以降に、列ごとのルールを記入する。

記入例:

対象列	入力ルール	表示形式
金額	数値のみ。単位「円」「￥」「万円」を入れない	#,##0（カンマ区切り）
数量	数値のみ。単位「個」「件」を入れない	数値（小数なし）
単価	数値のみ。小数点まで許容	#,##0.00

✗悪い例: 「金額は『50,000円』と入力する」というルールにする（文字列化が継続する）／ ◎良い例: 「数値のみ入力、見た目は表示形式」という方針にする

ステップ3. 既存データから文字を除去する

過去データの文字列を、Ctrl+Hの置換で除去します。

操作: 対象列だけを範囲選択して Ctrl+H を開く。「円」を「」（空文字）に置換、「￥」を「」に置換、「-」を「」に置換（数値末尾の場合のみ。マイナス値が必要な列では注意）。「8万円」のような単位変換が必要な値は別途手作業で「80000」に直す。「,」が文字列化の原因になっている場合は「,」を「」に置換してから、表示形式でカンマを付ける。

記入例: 置換ルール

対象	置換前	置換後
単位「円」	50,000円	50,000
通貨記号「￥」	￥80,000	80,000
末尾記号「-」	40000-	40000
「万円」	8万円	80000（手作業で単位換算）

✗悪い例: Ctrl+Hでファイル全体一括置換する（顧客名や備考の「円」「-」も書き換わる）／ ◎良い例: 対象列だけを範囲選択してから置換する

ステップ4. 表示形式と入力規則を設定する

数値列の表示形式を「#,##0」（金額）や「0」（数量）に固定し、入力規則で数値以外を弾きます。

操作: 対象列を範囲選択 → ホーム → 数値書式（Ctrl+1）→ 「数値」または「通貨」を選び、桁区切りを指定。金額列なら「ユーザー定義」で #,##0 を選んでもよい（通貨記号を付けない場合）。続けて、データ → データの入力規則 → 設定タブで「整数」または「小数点数」を選び、最小値・最大値を設定 → エラーアラートタブで「停止」を選び、「数値を入力してください」と設定。

記入例: 設定値

列	表示形式	入力規則
金額	#,##0	整数、0以上
数量	0	整数、0以上
単価	#,##0.00	小数点数、0以上

✗悪い例: 表示形式だけ設定して入力規則を設定しない（また文字列が混入する）／ ◎良い例: 表示形式と入力規則をセットで設定する

実務での注意点

文章で金額を説明する資料（提案書、企画書）には向きません。集計しない値なら文字列のままで問題ありません
通貨単位が複数ある場合（円・ドル・ユーロが混在）は、別列に「通貨」列を立てて、数値列は数値のみとしてください。「8万円」のような表記は、別列の単位「万」を併設するか、すべて円換算します
カンマ区切りの「,」自体は、Excelで標準的な数値入力（123456 と入力すれば自動でカンマ表示）であれば問題ありません。手入力で「123,456」と入力した場合も多くのケースで数値として認識されますが、まれに文字列になります（前後の文字や設定による）
マイナス値が必要な列（在庫変動・差額など）では、末尾の「-」を一括除去すると本来のマイナスまで消えます。先にマイナス値の表記（「-50」または「(50)」）を確認してから置換してください
金額列で「￥」を付けたい場合は、入力時ではなく表示形式（通貨形式）で付けます。これで入力は数値だけになり、表示時にだけ通貨記号が見える状態にできます