数式によるカスタム検証

数式によるバリデーションとは

ここで登場するのがカスタムバリデーションです。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（見つからなかった場合）に変換します。
• 結果が TRUE の場合のみ、Excel は入力を受け付けます。

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

• 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 に変更し、再度割引を入力 — 受け入れられることを確認。