Excel で数式を使ったデータの入力規則
入力セルを選び「データ > データの入力規則」で種類をユーザー設定にし、許可するときに TRUE になる数式を入れます。数式は選択範囲の先頭セル向けに書き、範囲が B2 からなら =AND(B2>=1,B2<=10) です。ユーザー設定ではドロップダウンは出ないので、リストが必要なら「リスト」を使います。
目次
要点
入力セルを選び「データ > データの入力規則」で種類をユーザー設定にし、許可するときに TRUE になる数式を入れます。数式は選択範囲の先頭セル向けに書き、範囲が B2 からなら =AND(B2>=1,B2<=10) です。ユーザー設定ではドロップダウンは出ないので、リストが必要なら「リスト」を使います。
=AND(B2>=1,B2<=10)確認のポイント
2026年11月12日の確認では、AND は 5 が TRUE、0 と 11 は FALSE。ISNUMBER(5) は TRUE、文字列 n/a は FALSE。COUNTIF は East が 1、South が 0。重複防止は =COUNTIF($B$2:$B$20,B2)=1 です。先に補助列で同じ数式を試します。
注意点と互換性
数式は = で始め、結果は TRUE または FALSE でなければなりません。空白を許可するときだけ「空白を無視する」をオンにします。ユーザー設定で矢印が出ないのは正常です。貼り付けも検査されます。セルごとに規則は1つなので、リストと追加条件が両方必要なら、同じ数式にまとめるか補助列で別途確認します。
英語の詳細例・数式・著者メモ
Excel How-toTested in: The TRUE/FALSE and
COUNTIFchecks below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click through Data Validation in Excel this session.
A plain list is covered in How to Add a Drop-Down List in Excel. This page is about the Custom option.
Write the formula for the first input cell
If you selected B2:B20, the formula is evaluated as if it were entered in B2, then filled down. Relative references should point at B2, not at B1 or at a locked $B$2 unless every row must test the same cell.
A number between 1 and 10
=AND(B2>=1,B2<=10)In the test workbook on 12 November 2026, =AND(5>=1,5<=10) was TRUE, and the same test for 0 and for 11 was FALSE.
A real number, not text
=ISNUMBER(B2)=ISNUMBER(5) was TRUE. =ISNUMBER("n/a") was FALSE. Numbers stored as text fail this test and should be converted first; see convert text to numbers.
A value that already appears in a list (without using the List type):
=COUNTIF(Lists!A2:A20,B2)=1COUNTIF of "East" against {East;West;North} returned 1, and "South" returned 0. A 0 is rejected. This is useful when you do not want a drop-down but still want the cell limited to known items. For an actual drop-down pointing at another sheet, use a List rule as in list from another sheet.
No duplicates in the input column
=COUNTIF($B$2:$B$20,B2)=1The range is locked; the cell being tested is not. The first time a value appears, the count is 1 and the entry is allowed. A second paste of the same value becomes 2 and is rejected.
In Google Sheets the custom formula goes in the Custom formula is criterion of Data validation, with the same references.
Test it on the sheet before you open the dialog
Put the formula in an empty column on row 2, fill down, and confirm you see TRUE where an entry should be allowed and FALSE where it should be blocked. If the helper column is already wrong, the validation rule will be wrong too.
The formula must start with =. A result that is not TRUE or FALSE — a #VALUE!, a number, or a blank — is treated as a failed check, which feels like a rule that rejects everything.
Turn Ignore blank off if an empty cell should be rejected. Leave it on if the input is optional. Pasted values are also checked, which is the usual way a rule “suddenly” fires after a copy from another sheet.
If the arrow is missing, you are probably on a Custom rule, not a List rule. That is expected. If a List rule is the one that is misbehaving, use drop-down list not working instead of changing it to Custom.