数式によるカスタムバリデーション

数式ベースの検証とは

ここでカスタム検証が登場します。Allow ドロップダウンから型を選択する代わりに、カスタムを選び、数式を直接入力します。Excel は値が入力されるたびにその数式を評価し、TRUE を返せば入力が許可され、FALSE なら拒否されます。

動作の仕組み

1. 対象セルを選択;
2. データの入力規則 → 設定を開く;
3. 許可でカスタムを選択;
4. 数式に TRUE または FALSE を返す任意の論理式を入力;
5. OK をクリック。

数式は選択範囲の最初のセルを基準に評価されます — 条件付き書式ルールと同様です。H2:H51 を選択して =H2>0 と入力すると、Excel は自動的に H3>0、H4>0 など列の下に調整します。

顧客メールアドレスの例

Customer Email 列には常に @ 記号が含まれている必要があります。これは有効なメール形式の最低限のチェックです。組み込みルールでは対応できませんが、カスタム数式で1行で実現できます。

=ISNUMBER(FIND("@",G2))

仕組み:

• FIND("@",G2) はメールセル内の @ を検索し、見つかった場合はその位置を返し、見つからない場合はエラーを返します；
• ISNUMBER() はそれを TRUE（見つかった）または FALSE（見つからなかった）に変換します；
• Excel は結果が TRUE の場合のみ入力を受け付けます。

有効なカスタム数式の条件

• TRUE または FALSE（または 1/0）を返す必要がある；
• 同じ行内の他のセルを参照できる；
• IF、AND、OR、ISNUMBER、LEN、FIND、COUNTIF など、すべての Excel 関数を使用できる；
• 一部の Excel バージョンでは、他のシートのセルを直接参照できない — 名前付き範囲を回避策として使用する。

課題

数量条件を満たす場合のみ割引

1. Discount % 列にカスタム検証を適用：
   • 数式: =OR(H2>=3,J2=0)；
   • エラースタイル: 警告；
   • エラーメッセージ: "Discount is only allowed for quantities of 3 or more"；
2. 数量が 10 の状態で割引 1 を入力してテスト — 警告が表示されることを確認；
3. 数量を 3 に変更し、再度割引を入力 — 受け入れられることを確認。