導入
在庫管理や実績管理のExcel管理表で、数量列に「10件」「5万円」「2.5kg」のように数値と単位が同じセルに入っていて、SUMやAVERAGEが動かない、ということはありませんか。担当者ごとに単位の付け方が違い、ある人は「10件」、別の人は「件数:10」、また別の人は数値のみという状況になっていて、集計のたびに手作業で単位を取り除いているケースもよくあります。
こうした集計崩れは、入力者の判断ミスではなく、数値と単位を分ける運用が表側で決まっていないことが原因です。「単位を一緒に書いた方が読みやすい」「単位がないと意味が伝わらない」という直感は自然ですが、集計の観点では文字列化を招きます。
この記事では、数値列と単位列を分ける設計に切り替え、既存データから単位を抽出して別列に移す方法を15分で完了させる手順として紹介します。終わったときに、数値列は純粋な数値、単位列で文脈を補う構造になり、集計と並び替えが安定して動きます。
この記事で解決すること
| 項目 | 内容 |
|---|---|
| 解決する課題 | 「10件」「5万円」のように単位込みで入力されてしまう |
| 主な原因 | 値と単位を同じセルに入れている |
| 解決方法 | 数値列と単位列を分けて管理する |
| 対象業務 | 在庫管理・売上管理・実績管理 |
| 対象人数 | 2〜30人 |
| 難易度 | ★☆☆☆☆ |
| 作業時間 | 15分 |
| 用意するもの | 対象のExcelファイル／編集権限 |
| 効果 | 集計や並び替えが安定する |
| 向かないケース | 単位が固定で集計しない表 |
なぜその管理表はうまくいかないのか
数値と単位を同じセルに入れている管理表には、共通する状況があります。
- 数値列の入力ルールが、表のどこにも書かれていない
- 「10件」「件数：10」「10」のように単位の付け方が人によって違う
- 単位込みで入力された値は文字列扱いとなり、SUMやAVERAGEに含まれない
- 複数単位（個・件・kg・本）が混在する列で、単位ごとの集計ができない
- 並び替えで「10件」「100件」「2件」のように文字順に並んでしまう
- ピボットで「合計／数量」を選ぶと「カウント／数量」しか出せない
これは入力者の見せ方の問題ではなく、数値と単位を1セルに同居させる設計が原因です。見直しは、「数値列」と「単位列」を分け、それぞれの列に1種類の情報だけが入る形にするところから始めます。
完成イメージ
15分後、対象列が「数値列」と「単位列」に分かれた状態になります。数値列は純粋な数値、単位列はマスタから選ぶプルダウン形式となり、集計・並び替え・単位別の絞り込みが安定して動きます。
改善前 — 数値と単位が同居し、集計できない:
| 案件番号 | 商品 | 数量 | 金額 |
|---|---|---|---|
| 001 | 商品A | 10件 | 50,000円 |
| 002 | 商品B | 5個 | 8万円 |
| 003 | 商品C | 2.5kg | 12,000 |
| 004 | 商品D | 100 | 40000 |
| 005 | 商品E | 8本 | 80,000円 |
数量列で SUM を実行しても、文字列セルが除外され合計が出せません。並び替えも「10件／100／2.5kg／5個」の文字順になり実用になりません。
改善後 — 数値と単位を分けて管理:
データ表（元のSheet1） — 数値列と単位列を分離:
| 案件番号 | 商品 | 数量 | 単位 | 金額 |
|---|---|---|---|---|
| 001 | 商品A | 10 | 件 | 50,000 |
| 002 | 商品B | 5 | 個 | 80,000 |
| 003 | 商品C | 2.5 | kg | 12,000 |
| 004 | 商品D | 100 | 個 | 40,000 |
| 005 | 商品E | 8 | 本 | 80,000 |
「単位マスタ」シート（新規追加）:
| 単位 | 用途 |
|---|---|
| 個 | 物品の個数 |
| 件 | 案件・取引の件数 |
| 本 | 細長い物の本数 |
| kg | 重量 |
| L | 容量 |
数量列は純粋な数値になり SUM が動きます。単位列で絞り込めば「件で売れた数量」「kgで売れた数量」と単位別の集計も可能になります。
改善手順
15分ほどで4ステップを進めます。
ステップ1. 単位込みで入力されている列を特定する
数値列の中で、単位が混入している列を書き出します。
操作: 対象列の右隣に「型判定」列を一時的に追加し、=ISNUMBER(A2) で数値かどうかを判定。FALSE が多数あれば、その列は単位混入の典型例。または、目視でユニーク値を確認して単位混在の状態を把握する。
記入例（対象列の特定）:
| 列名 | 単位混入例 | 対象 |
|---|---|---|
| 数量 | 10件／5個／2.5kg | ○ |
| 金額 | 50,000円／8万円 | ○ |
| 商品名 | 商品A／商品B | × |
ステップ2. 単位マスタを作る
単位列に入りうる値を整理します。実際に使われている単位を洗い出し、3〜10個程度の選択肢にまとめます。
操作: 新しいシート「単位マスタ」を追加。A1に「単位」、B1に「用途」と入力。A2以降に、実際に使われている単位（個・件・本・kg・L 等）と用途を記入する。
記入例:
| 単位 | 用途 |
|---|---|
| 個 | 物品の個数 |
| 件 | 案件・取引の件数 |
| 本 | 細長い物の本数 |
| kg | 重量 |
| L | 容量 |
| 箱 | 梱包単位 |
✗悪い例: 「個／こ／コ／pcs」と表記ばらつきを残す ／ ◎良い例: 1単位につき1つの正式表記に絞る
ステップ3. データ表に単位列を追加する
データ表の数量列の右隣に単位列を追加し、入力規則でマスタを参照するプルダウンにします。
操作: データ表の数量列の右隣に1列挿入し、ヘッダーを「単位」とする。単位列を範囲選択 → データ → データの入力規則 → 設定タブで「リストから選択」 → 元の値に =単位マスタ!$A$2:$A$10 を指定 → OK。
記入例: 設定後
| 商品 | 数量 | 単位 |
|---|---|---|
| 商品A | （数値） | 件 ▼ |
| 商品B | （数値） | 個 ▼ |
ステップ4. 既存データを数値と単位に分解する
過去データの「10件」「5個」のような単位込み値を、数値と単位に分解します。
操作: 数値抽出には次の式が使える: 数量列の右隣（単位列の右）に作業列を作り、=VALUE(LEFT(A2,LEN(A2)-LEN(REGEXEXTRACT(A2,"[件個本箱]+")))) などの数式で数値部分だけを取り出す。複雑な式が苦手な場合は、Ctrl+H で単位文字（「件」「個」「本」など）を1つずつ空文字に置換する方が手早い。
具体的手順: 1. 数量列のフィルタで「件」を含む値を抽出 → コピーして単位列に「件」を一括入力 → 数量列で「件」を空文字に置換 2. 同様に「個」「本」「kg」も繰り返す 3. 「8万円」のような単位換算が必要な値は手作業で「80000」に置換し、単位列に「円」を入れる 4. すべて終わったら、数量列で ISNUMBER による型確認、SUM が動くか確認
記入例: 分解前後
| 元の値 | 数量（数値） | 単位 |
|---|---|---|
| 10件 | 10 | 件 |
| 5個 | 5 | 個 |
| 2.5kg | 2.5 | kg |
| 8万円 | 80000 | 円 |
✗悪い例: 数値と単位を1セルから分けず、別シートに「単位込みで入力する場合」と「数値だけで入力する場合」の2運用を併設する（運用が複雑になり、結局元に戻る） ／ ◎良い例: 1つの数値列に対して1つの単位列を必ずセットで持つ設計に統一する
実務での注意点
- 単位が固定で集計しない表（社内貸出記録の単冊管理など）には向きません。常に「1冊」と決まっているなら単位列は不要です
- 複数単位が混在する列（個と件、kgとL）は、単位列なしでは集計できません。本記事の対象になる典型例です
- 金額の単位（円・ドル・ユーロ）を別列にする場合、為替レート列をどう扱うかも考える必要があります。単一通貨で十分なら、金額列は数値のみ・通貨は表ヘッダーに「金額（円）」と明記する形でも構いません
- 「kg」のように小数を含む単位は、数値列の表示形式を「0.00」など適切な小数桁数に設定してください
- 既存データの単位分解は1度限りの整形作業です。今後の入力では、ステップ3で設定したプルダウンと数値入力規則で再混入を防いでください
まとめ
集計が崩れる管理表の多くは、数値と単位を1セルに同居させていることが原因です。15分で単位マスタを作り、データ表に単位列を追加して既存データを数値と単位に分解するだけで、集計・並び替え・単位別絞り込みが安定して動くようになります。
数値と単位を分けたあと、数値列の形式統一（カンマ区切り・桁数）を整えると、見た目もすっきりします。あわせて以下を参照してください。
