導入
案件管理や申請管理、問い合わせ管理のExcel管理表で、せっかくプルダウン（入力規則のリスト）を設定したのに、いつの間にか「対応中（急ぎ）」「未対応・確認待ち」のようなマスタにない値が混ざっていることはありませんか。コピー&ペーストや他システムからの取り込みで、入力規則を無視した値が入ってしまうケースもよくあります。
こうしたプルダウン外の値の混入は、入力規則の不備ではなく、コピー貼り付けや一括インポート時には Excel の入力規則がバイパスされる仕様だからです。「プルダウン化したから安心」と思っていると、マスタにない値が静かに増え続けます。
この記事では、データ表のプルダウン列に対して、マスタと照合する「マスタ外チェック列」を追加し、選択肢外の値を機械的に検知する方法を20分で完了させる手順として紹介します。終わったときに、プルダウン外の値が一覧で見つかり、再発防止につながる状態になります。
この記事で解決すること
| 項目 | 内容 |
|---|---|
| 解決する課題 | プルダウン外の値が混ざる |
| 主な原因 | 手入力やコピー貼り付けでルール外の値が入る |
| 解決方法 | マスタと照合して選択肢外の値を検知する |
| 対象業務 | 案件管理・申請管理・問い合わせ管理 |
| 対象人数 | 3〜30人 |
| 難易度 | ★☆☆☆☆ |
| 作業時間 | 20分 |
| 用意するもの | 対象のExcelファイル／編集権限／既存のマスタ |
| 効果 | 表記ゆれや分類ミスを見つけやすい |
| 向かないケース | 選択肢が毎日変わる業務 |
なぜその管理表はうまくいかないのか
プルダウン外の混入が起きる管理表には、共通する状況があります。
- プルダウン列が設定されているが、コピー貼り付けで入力規則がバイパスされる
- 他システム（CRM、フォーム）からのインポートで、マスタにない値が紛れ込む
- マスタを更新したのに、データ表側に古い値が残ったまま
- 「対応中」と「対応中（急ぎ）」のように、似た値が手入力で混入する
- マスタ外の値があっても、Excelからは警告が出ない
- 件数が増えると、目視で全行を確認するのが非現実的になる
これは入力規則の効果不足ではなく、過去データとの照合チェックが組み込まれていないことが原因です。見直しは、プルダウン列に対して COUNTIF や MATCH でマスタとの照合チェック列を追加するところから始めます。
完成イメージ
20分後、データ表のプルダウン列の隣に「マスタ外チェック」列が追加され、マスタにない値が一目で分かる状態になります。フィルタで「マスタ外」を抽出すれば、修正対象が一覧で見えます。
改善前 — プルダウンはあるがマスタ外の値が混入:
「分類マスタ」シート（既存）:
| 分類 |
|---|
| 初回相談 |
| 継続相談 |
| 新規受注 |
| 通常請求 |
データ表（既存、プルダウン設定済）:
| 案件 | 顧客 | 分類 |
|---|---|---|
| 001 | A社 | 初回相談 |
| 002 | B社 | 対応中（急ぎ） |
| 003 | C社 | 通常請求 |
| 004 | D社 | 新規受注 |
| 005 | E社 | 確認中・要連絡 |
行002・005はマスタにない値（コピー貼り付けで混入したと思われる）ですが、目視では気づきにくい状態です。
改善後 — マスタ外チェック列で機械検知:
データ表（マスタ外チェック追加後）:
| 案件 | 顧客 | 分類 | マスタ外チェック |
|---|---|---|---|
| 001 | A社 | 初回相談 | OK |
| 002 | B社 | 対応中（急ぎ） | マスタ外 |
| 003 | C社 | 通常請求 | OK |
| 004 | D社 | 新規受注 | OK |
| 005 | E社 | 確認中・要連絡 | マスタ外 |
「マスタ外」でフィルタすれば、行002と005が抽出され、対応が必要な行が一目で分かります。条件付き書式で背景色を付ければさらに目立ちます。
改善手順
20分ほどで4ステップを進めます。
ステップ1. 対象のプルダウン列とマスタを確認する
照合チェックを設定したいプルダウン列と、そのマスタの参照範囲を確認します。
操作: データ表のプルダウン列を範囲選択 → データ → データの入力規則 を開き、「元の値」に指定されているマスタ範囲をメモする（例: =分類マスタ!$A$2:$A$10）。マスタが別ファイルにある場合は、参照しているファイル名も控える。
記入例: 確認結果
| プルダウン列 | マスタ参照範囲 |
|---|---|
| 分類 | 分類マスタ!$A$2:$A$10 |
| 担当 | 担当者マスタ!$B$2:$B$50 |
| 状態 | 状態マスタ!$A$2:$A$6 |
ステップ2. マスタ外チェック列を追加する
データ表のプルダウン列の右隣に1列追加し、マスタとの照合式を入れます。
操作: プルダウン列（例: C列＝分類）の右隣に1列挿入し、ヘッダーを「分類_マスタ外チェック」とする。2行目に以下の COUNTIF 式を入れる:
=IF(C2="","空欄",IF(COUNTIF(分類マスタ!$A:$A,C2)>0,"OK","マスタ外"))
C2 がマスタ範囲に1件以上見つかれば OK、0件なら「マスタ外」、空欄なら「空欄」と表示。最終行までコピー。
別ファイルのマスタを参照する場合は '[分類マスタ.xlsx]分類マスタ'!$A:$A のように外部参照を指定する。
記入例: 設定後
| 分類 | 分類_マスタ外チェック |
|---|---|
| 初回相談 | OK |
| 対応中（急ぎ） | マスタ外 |
| 通常請求 | OK |
✗悪い例: =COUNTIF(分類マスタ!A:A,C2)>0 で TRUE/FALSE だけ表示する（空欄も FALSE になり、マスタ外と区別がつかない） ／ ◎良い例: IF で「OK／マスタ外／空欄」の3区分にする
ステップ3. 条件付き書式で「マスタ外」を強調する
マスタ外チェック列で「マスタ外」のセルに背景色を設定し、目立たせます。
操作: マスタ外チェック列を範囲選択 → ホーム → 条件付き書式 → 新しいルール → 「次の値を含むセルだけを書式設定」 → 「特定の文字列」「次の値で始まる」「マスタ外」 → 書式で背景色（薄い赤）を設定 → OK。
「空欄」のセルも別の背景色（薄い黄色）にすると、空欄とマスタ外を区別できる。
記入例: 設定値
| 条件 | 書式 |
|---|---|
| 「マスタ外」で始まる | 背景色: 薄い赤 |
| 「空欄」で始まる | 背景色: 薄い黄色 |
ステップ4. マスタ外の値を整える運用ルールを決める
マスタ外を発見した後の対応ルールを明文化します。「マスタ外を見つけたら何をするか」が決まっていないと、検知しても放置されます。
操作: マスタ外を発見したときの対応フローを「マスタ外対応ルール」シートに記入:
1. マスタ外チェック列で「マスタ外」を抽出
2. 元の値がマスタの既存値と類似なら、置換で正式値に揃える
例: 「対応中（急ぎ）」→「対応中」（マスタにある）
3. 元の値が新しい意味を持つ値なら、マスタ追加を申請
例: 「研修申請」→ 分類マスタに「研修申請」を追加
4. 元の値が誤入力なら、削除または正しい値に修正
5. 月1回、マスタ外件数を未入力ダッシュボードに表示
担当者と更新頻度（月1回・週1回など）を併記し、運用が定着するようにする。
✗悪い例: チェック列を設定して終わりにする（マスタ外を発見しても誰も対応しない） ／ ◎良い例: 発見→対応のフローを明文化し、担当者と頻度を決める
実務での注意点
- 選択肢が毎日変わる業務（毎日新規分類が増えるなど）には向きません。マスタ更新の負荷が高すぎる場合は、自由入力＋月次の表記ゆれ整理の運用にする方が現実的です
- COUNTIF の全列参照（A:A）は、マスタ件数が少ないときは高速ですが、大量のマスタでは計算が重くなります。明示的な範囲（A2:A100）に変更してください
- マスタが別ファイルの場合、ファイル名変更やフォルダ移動で参照が壊れます。共有フォルダの相対パスを使うか、各管理表に参照シートを置く運用を検討してください
- 「マスタ外」を機械的に削除しないでください。新しい意味を持つ値がマスタ未登録のまま現場で使われている可能性があります。一度内容を確認し、マスタ追加か正式値への置換かを判断してください
- 空欄を「マスタ外」に含めると、未入力との区別がつかなくなります。ステップ2の式のように、空欄は別ステータスとして扱ってください
まとめ
プルダウン外の値が混ざる管理表の多くは、プルダウン設定だけに頼っていて、過去データとマスタの照合チェックが組み込まれていないことが原因です。20分でマスタ外チェック列を追加し、条件付き書式で目立たせて、対応ルールを明文化するだけで、混入を機械的に発見できる状態になります。
マスタ外チェックとあわせて、マスタ自体の整備も進めると、入力ルールが体系化されます。あわせて以下を参照してください。
