Excelでデータの入力規則を使って未入力(入力なし)の状態を禁止する方法
本エントリーの目次
Excelで、データの入力フォームや一覧表を作成している際、こんなふうに思うことがあります。
Excelで未入力(入力なし)の状態を禁止したい！
たとえば、商品の登録に使用する入力フォームでは、商品名や金額などは入力が必須の項目でしょう。
また職場の歓送迎会の出欠を確認する際、共有フォルダーに出欠の記入・一覧表のExcelファイルを置いて、出欠の可否を各個人に書いてもらうことがあります。
この場合では、個人名や出欠の列が必須項目となります。
こういった記入が必須となる項目については、未入力(入力なし)の状態を禁止。
そして必ず入力させるようにしたい！と思いますよね。
Excelでは、『データの入力規則』という機能を使うことで、データを入力中に未入力の状態でデータ入力を完了できないようにできます。
そこで今回は、Excelで『データの入力規則』を使って未入力(入力なし)の状態を禁止する方法をご紹介します！
『データの入力規則』を使って未入力(入力なし)の状態を禁止する方法
Excelで『データの入力規則』を使って、A１セルの未入力(入力なし)の状態を禁止する手順は、以下のとおりです。
まずは未入力の状態を禁止したいセル(この例ではA1セル)を選択し、Excelの画面上部にある『データ』タブをクリック。
『データツール』グループ内にある『データの入力規則』ボタンをクリックしてください。
すると『データの入力規則』画面が表示されるので、以下画像のとおり『入力値の種類』に『ユーザー設定』を選択。
併せて『空白を無視する』を『チェックなし』状態としてください。
また『数式』には、以下数式を入力します。
1 2 | =未入力状態を禁止したいセル※<>"" ※画像の例では、A1セルの未入力状態を禁止に設定したいため、『=A1<>""』と入力しています。 |
入力が終わったら、『OK』ボタンをクリックすれば、設定は完了です。
『空白を無視する』は、チェックが入っているとセルが空白時はデータの入力規則を適用しない、という設定となります。
そのため『データの入力規則』で指定した規則を、セルが空白時にも適用されるようにするため、チェックを外しています。
未入力状態のまま、データ入力を完了しようとしたときの挙動
『データの入力規則』を使って未入力を禁止したセルで、未入力のままデータ入力を完了しようとすると、どういった動きとなるのかは、実際に試してみると分かりやすいでしょう。
先ほどの設定例では、A1セルの未入力(入力なし)状態を禁止する設定を行っています。
そこで、A1セルに1や3など何らかの文字を入力してから、マウスで別のセルを選択し、A1セルの入力を完了してみてください。
何らかの文字が入力されている場合には、入力に問題がないため、別のセルにフォーカスが移動し、A1セルの入力を完了できるはずです。
次にマウスでA1セルを選択。
そしてキーボードのBackSpaceキーで入力していた文字を消してから、再度マウスで別のセルを選択し、A1セルの入力を完了しようとしてください。
すると『この値は、このセルに定義されているデータ入力規則の制限を満たしていません。』という警告メッセージが表示され、データ入力を完了できません。
このように、『データの入力規則』により未入力(入力なし)状態を禁止する設定を行うと、未入力状態ではデータ入力を完了できなくなるのです。
『データの入力規則』を使って未入力状態の禁止 ＋アルファの設定(整数の1～5だけを許可など)を行う方法
『データの入力規則』を使えば、未入力状態の禁止に加え整数の1～5だけを許可するなど、+アルファの設定を行うことも可能です。
この場合には、『データの入力規則』画面の『空白を無視する』を『チェックなし』に設定。
併せて『入力値の種類』や『データ』で、入力を許可したい設定を行います。
たとえば未入力状態の禁止に加え、整数の1～5だけを許可したい場合には、以下のように設定を行ってください。
- 『空白を無視する』：『チェックなし』
- 『入力値の種類』：『整数』
- 『データ』：『次の値の間』
- 『最小値』：『1』
- 『最大値』：『5』
『データの入力規則』を使った未入力状態の禁止は、完全ではありません！
実は、Excelの『データの入力規則』を使った未入力状態の禁止は、完全ではありません。
具体的にはセルを選択後、Deleteキー押下によるセル内データの削除を行った際には、『データの入力規則』の警告メッセージが表示されず、セルを未入力状態とすることができます。
どうもExcelの『データの入力規則』機能は、一度セルのデータ入力状態となってからデータ入力を完了するとき(別のセルなどにフォーカスを移動しようとしたとき)に、チェックが実行されるようです。
Excelでは、データ入力状態ではなくセルを選択した状態でBackSpaceキーを押下すると、データ入力状態になり、セル内のデータがすべて削除されます。
そのためフォーカスを移動しようとしたときに、『データの入力規則』によるチェックが行われ、警告メッセージが表示されます。
対してDeleteキー押下によるセル内データの削除(データ入力状態ではなくセル選択状態)では、データ入力状態とならないため、『データの入力規則』によるチェックが行われないようです。
というわけで、Excelの『データの入力規則』を使った未入力状態の禁止では、未入力状態を完全に防げるわけではありません。
Deleteキー押下によるデータ未入力状態の対応策(『数式』や『条件付き書式』を使って入力をうながす方法)
さきに書いたとおり、Excelの『データの入力規則』を使った未入力状態の禁止設定では、Deleteキー押下による未入力状態を防ぐことはできません。
そのため厳密なチェックが必要な場合には、Excel VBAを使って未入力のチェック機能を盛り込むなどの対応が必要になると思います。
ですがExcel VBAはちょっと…、使い方がよく分からないし…と敬遠してしまう方も多いでしょう。
そこで『数式』や『条件付き書式』を使って入力をうながすことで、未入力状態のまま作業を終えられてしまうのを防ぐための対応策を、2つご紹介します。
『数式』を使って入力を促す方法
この方法では、A1セルが未入力状態のとき、右隣のB1セルに『※A1セルは必須です。何か入力してください。』と表示し、入力をうながします。
設定方法はとても簡単で、B1セルに以下のような数式を入力するだけでOKです。
1 | =IF(A1="","※A1セルは必須です。何か入力してください。","") |
B1セルに上記数式を入力すると、A1セルに何らかの文字が入力されていれば、B1セルには何も表示されません。
ところがA1セルが未入力状態のときには、B1セルに『※A1セルは必須です。何か入力してください。』と表示されるようになります。
必須項目の上のセルや右のセルに、このようなメッセージが表示されるように設定。
さらにセルの書式設定で、メッセージが表示されるセルの文字色を赤字にしておくなどの工夫をすれば、より一層の効果を期待できるでしょう。
『条件付き書式』を使って入力を促す方法
『条件付き書式』機能を使い、未入力状態のセル背景色を変更し、入力をうながす設定も可能です。
今回は、未入力状態のセルにオレンジ色の背景色を自動設定する方法を例に、ご紹介しましょう。
まずは設定対象のセル(この例ではA1セル)を選択し、Excelの画面上部にある『ホーム』タブをクリック。
『スタイル』グループ内にある『条件付き書式』ボタン、『新しいルール』ボタンとクリックしてください。
『新しい書式ルール』画面が表示されるので、『指定の値を含むセルだけを書式設定』を選択し、『次のセルのみを書式設定』に『空白』を選択。
その後、画面右下の『書式』ボタンをクリックしてください。
『セルの書式設定』画面が表示されるので、『塗りつぶし』タブでオレンジ色を選択してから『OK』、『OK』とクリックし、設定を完了してください。
この設定が終わった状態では、A1セルに何らかの文字が入力されていれば、A1セルの背景色は変更されません。
ところがA1セルが未入力状態のときには、A1セルの背景がオレンジ色に自動変更され、未入力状態であることを強調します。
もちろん『数式』を使って入力を促す方法と『条件付き書式』を使って入力を促す方法は併用することができ、未入力時には以下のような表示が行われ、入力をうながします。
見た目上の未入力(入力なし)に要注意！
Excelでは以下のような、見た目上未入力と区別しにくい入力値があります。
1 2 3 4 5 | ="" =" " =" " ← 半角スペース文字 ← 全角スペース文字 |
これらの数式や文字がセルに入力されていると、それぞれ長さゼロの文字列を表す数式、半角スペースを表す数式、全角スペースを表す数式、半角スペース文字、全角スペース文字がセルの値としてExcelに認識されます。
セルの見た目上は未入力のように見えますが、何らかの値は入力されていることから、Excelは入力あり(未入力ではない = 空白セルではない)と判断します。
そのため空白セルかどうかを調べるISBLANK関数を上記入力値に対して使用すると、すべてFALSE(空白セルではない)という結果が返却されます。※
しかし入力が必須というケースでは、これらは入力値としてはNGとして扱うのが一般的のはず。
したがって、これら”見た目上の未入力(入力なし)”についても考慮し、必要があれば入力値として認めない設定を行ったり、再入力をうながすようなメッセージを表示すると良いでしょう。
※ISBLANK関数の結果がFALSE(空白セルではないという判定)のセルに対し、一部の機能では空白として扱うケースがあり、たとえば『条件付き書式』機能で『指定の値を含むセルだけを書式設定』、『次のセルのみを書式設定』に『空白』を選択すると、書式が適用されます。(本エントリー執筆時点の最新バージョンであるExcel 2019では、上記入力値のすべてに書式が適用されることを確認。)
以上、参考になさってくださーい！