導入
案件管理や申請管理のExcel管理表で、担当者欄に「鈴木一郎」と書かれているが、実際にはそんな名前の担当者はいない、分類欄に旧分類名がそのまま入っている、ということはありませんか。マスタを作っていても、業務表との突合をしていないと、マスタにない値がいつの間にか混入します。
これは入力者が嘘を書いているわけではなく、マスタと業務表の整合性を確認する仕組みがないことが原因です。この記事では、Excel 管理表 参照不一致を検知し、マスタにない値を発見する手順を紹介します。
この記事で解決すること
| 項目 | 内容 |
|---|---|
| 解決する課題 | 存在しない担当者や分類が入力される |
| 主な原因 | 入力表とマスタの整合性を確認していない |
| 解決方法 | 入力値とマスタを照合し不一致をエラー表示する |
| 対象業務 | 案件管理・顧客管理・申請管理 |
| 対象人数 | 5〜100人 |
| 難易度 | ★★★☆☆ |
| 作成時間 | 60分 |
| 効果 | 不正な入力値を発見できる |
| 向かないケース | マスタを使わない表 |
この記事は、業務表の入力値とマスタを突合し、不一致を見つける仕組みを作る内容です。
なぜその管理表はうまくいかないのか
参照不一致が起きる管理表には、共通する特徴があります。
- マスタはあっても、業務表との突合チェックがない
- プルダウンが緩い設定（警告のみ）で、マスタ外の値が入る
- マスタ更新後に業務表の旧値が放置される
- 入力規則をすり抜けてコピー＆ペーストされる
- 表記ゆれで一致しないことに気づかない
担当者は「いつもの値」を入れているつもりでも、マスタが更新されたあとに旧値を使い続けてしまうことがあります。問題は、表の側で「マスタにあるかを毎回確認する仕組み」がないことです。
改善手順
ステップ1. 参照すべきマスタを特定する
業務表の各列について、参照すべきマスタを整理します。担当者列なら担当者マスタ、分類列なら分類マスタ、商品コード列なら商品マスタ、というように対応関係を明確にします。
ステップ2. 参照不一致チェック列を作る
業務表に「参照チェック」列を追加し、COUNTIFやMATCH関数で「マスタに存在するか」を判定します。例：=IF(COUNTIF(マスタ範囲, 入力値)=0, "マスタ外", "")。
ステップ3. 条件付き書式で目立たせる
「マスタ外」表示の行に色を付け、一覧で見たときに即座に気づける見た目にします。
ステップ4. プルダウンの参照範囲を動的にする
マスタの有効値を動的に取得するプルダウン（FILTERや動的範囲）を使い、マスタ更新が自動的にプルダウンに反映される仕組みにします。新規入力で旧値が出ないようにできます。
ステップ5. 既存データの不一致を確認する
仕組みを入れた直後に、既存行で「マスタ外」が出ている行を一通り確認します。マスタの方が間違っていることもあるため、業務担当と確認しながら整えます。
ステップ6. 月次の点検ルーチンを作る
月初などに「マスタ外」フラグの一覧を確認するルーチンを決めます。マスタの追加が必要なのか、業務表の修正が必要なのかを判断し、対応します。
Before / After
| 観点 | Before | After |
|---|---|---|
| 課題 | マスタ外の値が紛れる | 参照不一致を自動検知 |
| 原因 | マスタとの突合なし | COUNTIFで照合 |
| 運用 | 緩いプルダウン | 動的範囲＋チェック列 |
| 確認 | 集計時に発覚 | 入力直後に検知 |
| 効果 | データ品質が低下 | 不正な入力値を発見できる |
業務表とマスタの整合性が保たれ、集計や検索の信頼性が上がります。
実務での注意点
- マスタを使わない表には、参照チェックは不要です
- マスタの正本がブレないようにする（複数の場所にマスタを持たない）
- 表記ゆれ（スペース、全角半角）で不一致になることがあるため、正規化と組み合わせる
- 「マスタ外」フラグを放置しない（必ず判断する）
- マスタ更新時に、影響を受ける業務表の所在を把握しておく
最初は最重要列1〜2列（担当者、分類など）から始め、運用を見ながら拡張するのが現実的です。
Web化・スプレッドシート化との関係
Excel改善で足りる場合
利用者が5〜100人で、マスタの規模が数百〜数千件なら、ExcelのCOUNTIFや動的範囲で十分対応できます。Excel上でも参照チェック列があるだけでデータ品質は大きく上がります。
スプレッドシート化・Web化を考える場合
複数システムでマスタを共有する、APIで自動照合したい、リアルタイムで不一致を検知したい場合は、スプレッドシートやWebツール、マスタ専用システムへの移行が向きます。Webツールならフォーム入力で物理的にマスタ外の値を入れられない設計にできます。
ツールを変える前に、参照すべきマスタと不一致時の対応手順を整えておくと、移行先でもそのまま使えます。
まとめ
Excel管理表で存在しない担当者や分類が入力されるのは、入力表とマスタの整合性を確認していないためです。COUNTIFで参照不一致を検知し動的プルダウンと組み合わせれば、不正な入力値を発見でき、業務表とマスタの整合性を継続的に保てます。
