導入
売上管理や在庫管理の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)」）を確認してから置換してください
- 金額列で「￥」を付けたい場合は、入力時ではなく表示形式（通貨形式）で付けます。これで入力は数値だけになり、表示時にだけ通貨記号が見える状態にできます
まとめ
金額や数量の合計が合わない管理表の多くは、数値列に文字や記号が混ざっていることが原因です。10分で文字を置換で除去し、表示形式と入力規則を設定するだけで、SUM・AVERAGE・ピボットでの集計が正しく動くようになります。
数値列の整理とあわせて、数値と単位を別列にする運用を検討すると、業務単位での集計がさらに柔軟になります。あわせて以下を参照してください。
