データ検証設定は「入力ミスを防ぎ、Excelを“壊れないシート”にする」ための最強テクニック
Excel業務自動化では、データ検証（Data Validation）が非常に重要です。
なぜなら、次のような業務上の問題を防げるからです。
- 入力欄に数字以外が入ってしまう
- 選択肢以外の文字が入力されてしまう
- 日付欄に未来日や過去日が入ってしまう
- 入力漏れが発生してしまう
Pythonの openpyxl を使えば、
Excelの「データの入力規則」を自動で設定できます。
初心者でも理解しやすいように、基本から実務テンプレートまで丁寧に解説します。
数値のみ入力可能にする（最もよく使うデータ検証）
A列に「1〜100 の数値のみ」入力できるようにする
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation
wb = load_workbook("sample.xlsx")
ws = wb["Sheet1"]
dv = DataValidation(type="whole", operator="between", formula1="1", formula2="100")
dv.error = "1〜100の数値を入力してください"
dv.errorTitle = "入力エラー"
ws.add_data_validation(dv)
dv.add("A2:A100")
wb.save("sample.xlsx")
深掘りポイント
type="whole"は「整数のみ」operator="between"で範囲指定formula1とformula2が下限・上限errorとerrorTitleでエラーメッセージを設定できる- Excelの「データの入力規則 → 整数 → 1〜100」と同じ動作
リストから選択させる（プルダウンメニュー）
B列に「完了 / 未完了 / 保留」のプルダウンを作る
from openpyxl.worksheet.datavalidation import DataValidation
dv = DataValidation(type="list", formula1='"完了,未完了,保留"')
dv.error = "選択肢から選んでください"
ws.add_data_validation(dv)
dv.add("B2:B100")
深掘りポイント
type="list"でプルダウンformula1='"A,B,C"'のように カンマ区切りで文字列を指定- Excelの「リスト」検証と同じ
- ステータス管理で最も使われる
別シートのリストを参照する（実務で最も使う）
「マスタ」シートの A2:A10 をプルダウンにする
dv = DataValidation(type="list", formula1="=マスタ!$A$2:$A$10")
ws.add_data_validation(dv)
dv.add("C2:C100")
深掘りポイント
=シート名!$A$2:$A$10のように 絶対参照 を使う- マスタ管理（商品名・部署名・担当者名など）で必須
- 選択肢をExcel側で管理できるため、運用が楽になる
日付の範囲を制限する（期限管理に最適）
今日以降の日付しか入力できないようにする
from openpyxl.worksheet.datavalidation import DataValidation
dv = DataValidation(type="date", operator="greaterThanOrEqual", formula1="=TODAY()")
dv.error = "今日以降の日付を入力してください"
ws.add_data_validation(dv)
dv.add("D2:D100")
深掘りポイント
type="date"で日付検証formula1="=TODAY()"で「今日以降」- 期限管理・スケジュール管理で非常に便利
文字数制限を設定する（入力フォームで必須）
E列に「最大20文字まで」の制限を付ける
dv = DataValidation(type="textLength", operator="lessThanOrEqual", formula1="20")
dv.error = "20文字以内で入力してください"
ws.add_data_validation(dv)
dv.add("E2:E100")
深掘りポイント
type="textLength"で文字数制限- 入力フォームやコメント欄でよく使う
空欄禁止（必須入力項目）
F列を必須入力にする
dv = DataValidation(type="custom", formula1='=LEN(F2)>0')
dv.error = "必須項目です"
ws.add_data_validation(dv)
dv.add("F2:F100")
深掘りポイント
type="custom"で自由な数式を使えるLEN(F2)>0は「文字数が0より大きい＝空欄ではない」- 入力漏れ防止に最適
例題①：売上入力シートに「数値＋プルダウン＋必須入力」を設定する
シナリオ
A列：商品名（必須）
B列：数量（1〜999）
C列：ステータス（完了/未完了）
# 商品名（必須）
dv1 = DataValidation(type="custom", formula1='=LEN(A2)>0')
ws.add_data_validation(dv1)
dv1.add("A2:A100")
# 数量（1〜999）
dv2 = DataValidation(type="whole", operator="between", formula1="1", formula2="999")
ws.add_data_validation(dv2)
dv2.add("B2:B100")
# ステータス（プルダウン）
dv3 = DataValidation(type="list", formula1='"完了,未完了"')
ws.add_data_validation(dv3)
dv3.add("C2:C100")
解説
- 入力ミスを完全に防げる
- 売上入力フォームとしてそのまま使える
例題②：日付欄に「過去日禁止」の検証を付ける
dv = DataValidation(type="date", operator="greaterThanOrEqual", formula1="=TODAY()")
ws.add_data_validation(dv)
dv.add("A2:A100")
解説
- スケジュール管理・タスク管理で必須
- 過去日を入力するとエラーが出る
例題③：マスタ参照のプルダウンを作る（実務で最強）
シナリオ
「部署マスタ」シートの A2:A20 を参照する
dv = DataValidation(type="list", formula1="=部署マスタ!$A$2:$A$20")
ws.add_data_validation(dv)
dv.add("B2:B100")
解説
- マスタを更新すればプルダウンも自動更新
- 大規模Excelで最も使われるパターン
データ検証設定を業務で設計するときの視点
- 数値・文字列・日付・リストなど、入力制限を細かく設定できる
- エラーメッセージを付けるとユーザーが迷わない
- マスタ参照のプルダウンは運用が圧倒的に楽
- custom（数式）を使うと自由度が一気に上がる
- pandasではデータ検証ができないため、openpyxlが必須