導入
案件管理や申請管理のExcel管理表で、担当者欄や分類欄が手入力になっているために、マスタには「営業1課」と登録されているのに業務表では「営業１課」「営業一課」「営1課」と表記が散乱している、ということはありませんか。
これは入力者の不注意というより、入力表とマスタが連動していないことが原因です。マスタはあっても、入力時に参照されていないと意味がありません。この記事では、Excel 管理表 マスタ参照列を整え、入力時にマスタから選ばせる手順を紹介します。
この記事で解決すること
| 項目 | 内容 |
|---|---|
| 解決する課題 | 手入力でマスタと違う値が入る |
| 主な原因 | 入力表とマスタが連動していない |
| 解決方法 | 入力列をマスタ参照のプルダウンにする |
| 対象業務 | 案件管理・顧客管理・申請管理 |
| 対象人数 | 5〜50人 |
| 難易度 | ★★★☆☆ |
| 作成時間 | 60分 |
| 効果 | 入力値の統一がしやすい |
| 向かないケース | 選択肢が毎日変わる業務 |
この記事は、マスタを作ったあとに、業務表の入力欄をマスタ参照のプルダウンに切り替える内容です。
なぜその管理表はうまくいかないのか
入力値がマスタと食い違う管理表には、共通する特徴があります。
- 担当者・部署・分類などが手入力欄になっている
- マスタはあるが、入力時に開いて見るルールがない
- プルダウン化されておらず、自由入力できる
- マスタの値とちょっと違う表記でも入力できてしまう
- マスタ更新があっても業務表が追随しない
担当者は急いで入力しているだけで、マスタを開いて確認する手間が惜しいと感じています。問題は、表の側で「マスタから選ぶしかない」状態を作っていないことです。
改善手順
ステップ1. マスタを名前付き範囲にする
マスタの有効値範囲に名前を付けます（数式タブ→名前の定義）。例：「担当者リスト」「分類リスト」など。FILTERで有効値だけを抽出した範囲に名前を付けるとなお良いでしょう。
ステップ2. 業務表の入力列を絞り込む
業務表で、マスタから値を引きたい列（担当者列、分類列など）をリストアップします。最重要の1〜2列から始めます。
ステップ3. データの入力規則を設定する
該当列を選択し、データの入力規則→入力値の種類「リスト」→元の値に「=担当者リスト」のように名前付き範囲を指定します。これでプルダウンから選択する形になります。
ステップ4. エラー時の挙動を決める
プルダウンにない値を入力したときの挙動を、「停止」（入力を許さない）にするか「警告」（許すが注意表示）にするか決めます。最初は警告から始め、運用が安定してから停止に切り替えるのが現実的です。
ステップ5. 関連情報の自動引き当てを組む
担当者を選んだら部署・所属チームが自動で出る、商品コードを選んだら単価が自動で出る、といった連動をVLOOKUPやXLOOKUPで組みます。手入力の余地をさらに減らせます。
ステップ6. 既存データの整合性を取る
過去行でマスタと違う値が入っている場合は、可能な範囲で修正します。完全に揃えるのが難しい場合は、変換テーブルを用意して集計時に名寄せします。
Before / After
| 観点 | Before | After |
|---|---|---|
| 課題 | 手入力で表記がバラつく | プルダウンで統一 |
| 原因 | マスタと業務表が独立 | マスタ参照で連動 |
| 運用 | 自由入力 | リストから選択 |
| 確認 | 表記ゆれの修正 | 入力時点で揃う |
| 効果 | 集計の精度が下がる | 入力値の統一がしやすい |
担当者別・分類別の集計が安定し、表記ゆれを修正する作業がほぼ不要になります。
実務での注意点
- 選択肢が毎日変わる業務には、固定プルダウンは不向き
- マスタ更新があれば自動的にプルダウンに反映される仕組みを使う（FILTERや動的範囲）
- 「停止」設定だと過去データの貼り付けで弾かれる場合がある
- 入力者にプルダウンの存在を周知する
- 「リストにない値」が必要なケースは別途運用ルールを決める
最初から全列をプルダウン化せず、よく使う担当者列や分類列の1〜2列から始めるのが現実的です。
Web化・スプレッドシート化との関係
Excel改善で足りる場合
利用者が5〜50人で、マスタの選択肢が数十〜数百件程度なら、Excelの名前付き範囲と入力規則で十分対応できます。マスタの更新を1か所で行えば、業務表は自動的に最新の選択肢になります。
スプレッドシート化・Web化を考える場合
複数システムをまたぐ参照、外部ソースとの連携が必要な場合は、スプレッドシートやWebツールへの移行が向きます。Webツールならフォームで強制的にマスタから選ばせる仕組みが標準で用意されています。
ツールを変える前に、入力列とマスタの対応を整理しておくと、移行先でもそのまま使えます。
まとめ
Excel管理表で手入力値がマスタと食い違うのは、入力表とマスタが連動していないためです。名前付き範囲と入力規則で入力列をマスタ参照のプルダウンに変えれば、入力値の統一がしやすくなり、集計と検索の精度を高められます。
