Excelで複数条件に対応するカスタム入力規則を設定する方法|実例4選

Excelでデータを管理していると、複数の条件を満たすデータだけを入力できるように制限したい場面がよくあります。条件外の誤ったデータが入力されると、集計ミスやワークシートの不具合につながることも。そんなときに役立つのが「入力規則(Data Validation)」のユーザー設定(カスタム)機能です。

この記事では、Excelで複数の条件に対応するカスタム入力規則を適用する方法を、4つの具体例とともにわかりやすく解説します。

Excelで複数条件のカスタム入力規則を適用する4つの例

1. 1つのセルに複数条件のカスタム入力規則を適用する

OR関数は、引数として指定した条件のうちどれか一つでも満たされていればTRUEを返す関数です。複数の条件のうち、いずれかを満たせばよい場合に活用できます。

ここでは、次のようなサンプルデータを例に説明します。「条件1」には製品名のリストが、「条件2」には2つの特定の日付が含まれています。セルB5への入力が、これらの条件のどちらかを満たすようにOR関数を使った入力規則を設定してみましょう。

手順:

• まず、[データ]タブの[データ ツール]グループにある[データの入力規則]を選択します。
• [データの入力規則]ダイアログボックスが表示されます。
• [設定]タブの[入力値の種類]で[ユーザー設定]を選択します。
• [数式]ボックスに、次の数式を入力します。

=OR(COUNTIF($D$5:$D$10,B5)=1, AND(B5>=E5,B5<=E6))

• 最後に[OK]をクリックして完了です。

この数式では、AND関数が入力された日付がセルE5(2022年2月1日)からセルE6(2022年3月1日)の範囲内かどうかを判定し、COUNTIF関数がセルB5の値が範囲D5:D10のリスト内に存在するかどうかを確認しています。そして最後にOR関数が、入力値がいずれかの条件を満たしているかどうかを総合的に判断します。

• 設定後は、「条件1」の製品名、または指定した期間内の日付であれば自由に入力できます。
• 一方、どちらの条件も満たさない値を入力すると、エラーダイアログが表示されて入力が拒否されます。

2. 選択した複数のセル範囲にカスタム入力規則を適用する

入力規則は、1つのセルだけでなく、セル範囲全体にまとめて適用することもできます。次のサンプルデータでは、「条件1」に製品名のリスト、「条件2」に「50より小さい数値」という条件が設定されています。この例では、範囲B5:B10に対して、いずれかの条件を満たすデータのみ入力できるよう設定します。

手順:

• まず、範囲B5:B10を選択します。
• 次に、[データ] ➤ [データ ツール] ➤ [データの入力規則]の順にクリックし、ダイアログボックスを表示します。
• [設定]タブの[入力値の種類]で[ユーザー設定]を選択し、[数式]ボックスに以下を入力します。

=OR(B5<$E$5,COUNTIF($D$5:$D$10,B5)=1)

• [OK]をクリックして設定を確定します。

COUNTIF関数は、入力値が範囲D5:D10内に存在する場合のみカウントし、もう一つの条件では入力値がセルE5(50)より小さいかどうかを判定します。最終的にOR関数が、範囲B5:B10への入力がいずれかの条件を満たしているかをチェックします。

• これにより、条件に合致する有効なデータのみ入力可能になります。
• この例では、セルB5の「oven」やセルB6の「15」は有効ですが、「59」を入力しようとすると50より大きいためエラーが表示されます。

3. 重複入力の防止と文字数制限を同時に設定する

カスタム入力規則を使えば、重複データの入力防止と文字数の制限を同時に実現することもできます。次のサンプルデータは、ある会社のID・販売担当者・製品の一覧です。ここでは、ID欄に「3桁の数字」かつ「重複しない値」だけを入力できるように設定します。

手順:

• まず、範囲B5:B10を選択します。
• [データ] ➤ [データ ツール] ➤ [データの入力規則]をクリックし、ダイアログボックスを開きます。
• [設定]タブの[入力値の種類]で[ユーザー設定]を選択し、[数式]ボックスに以下の数式を入力します。

=AND(COUNTIF($B$5:$B$10,B5)<=1, ISNUMBER(B5), LEN(B5)=3)

• [OK]をクリックします。

この数式の仕組みは以下の通りです。ISNUMBER関数が数値以外の入力を拒否し、LEN関数が入力値を3桁に制限します。さらにCOUNTIF関数が同じ範囲内での重複を防ぎ、最後にAND関数が「すべての条件を満たしているか」を判定します。すべての条件を満たした入力のみが有効になります。

• 条件をすべて満たすデータであれば問題なく入力できます。
• 一つでも条件を満たさない場合は、エラーダイアログが表示されます。

4. 指定した2つの日付の間のみ入力を許可する

最後の例では、特定の期間内の日付のみ入力を許可する方法を紹介します。次のサンプルデータでは、セルD12に開始日、セルD13に終了日が入力されています。各販売担当者の出荷日を、この2つの日付の間に収まるように制限しましょう。

手順:

• まず、範囲D5:D10を選択します。
• [データ] ➤ [データ ツール] ➤ [データの入力規則]を選択します。
• [データの入力規則]ダイアログボックスが表示されます。
• [設定]タブの[入力値の種類]で[ユーザー設定]を選択し、[数式]ボックスに以下を入力します。

=AND(D5>=$D$12, D5<=$D$13)

• [OK]をクリックして完了です。

ここではAND関数が、入力された日付がセルD12とセルD13で指定された期間内に収まっているかどうかを判定しています。

• 条件を満たす日付であれば正常に入力できますが、条件外の日付を入力すると即座にエラーメッセージが表示されます。
• この例では「2022年2月29日」は指定期間内の日付ですが、エラーが表示されます。これは2022年が閏年ではないため、2月29日という日付自体が存在しないからです。Excelが日付の妥当性まで自動的にチェックしてくれる好例といえます。

まとめ

この記事で紹介した方法を使えば、Excelで複数の条件に対応するカスタム入力規則を自由に設定できるようになります。OR関数・AND関数・COUNTIF関数などを組み合わせることで、単純なリスト選択にとどまらない柔軟なデータ入力チェックが可能です。ぜひ実際の業務で活用してみてください。他におすすめの方法があれば、ぜひコメント欄でお聞かせください。

関連記事

• Excelの入力規則ドロップダウンリストにフィルターをかける方法(2つの例)
• テーブルから入力規則リストを作成する方法(3つの手法)
• VBAで入力規則のドロップダウンリストを作成する(7つの活用例)
• 【解決済み】コピペで入力規則が機能しないときの対処法
• VBAで名前付き範囲を使って入力規則リストを作成する方法