- ホーム >
- 応用操作 >
- エクセル（Excel）編 >
入力規則 ～ セルに入力できる値を制限する ～
デデータベースを作成して、蓄積したデータから、統計処理や分析などを行った経験がある方は多いのではないでしょうか。
Excelで作成するデータベースは、多くの場合、日々レコード（データ）を追記していきながらデータを蓄積します。そのため、入力担当者が変わるなど、複数の人が共用で使うフォーマットであるケースも多くあります。（データベースについては、基本操作編 簡単なデータベースの作成（1） を参照してください）
特に、共用のフォーマットの場合は、集計するときになって、統一されたデータで入力できていなかったために集計できなかったり、入力し直したりといった経験もあるのではないでしょうか。
例えば、数値の半角と全角の違い、数値入力のセルに文字列で注釈を記入している、郵便番号の「-」マークの取り扱い、住所の「県名」の省略、といった具合に、統一されたデータの入力形式で入力がされていないと、正しく集計できない場合があります。
そこで、いくつかの方法で統一したデータの入力を促します。例えば、口頭やメモ等によるアナウンスや、セルに下図のようなコメントを挿入するなどの方法です。
コメント機能については、コメントと入力時メッセージの挿入 で詳しく解説しますが、あくまでこうした方法は注意喚起であって、強制的に入力書式を制御することはできません。
強制的に書式を統一しようと思うと、書式のユーザー定義 で学習した表示形式を指定すると、ある程度、統一させることができますが、完全に統一することはできません。例えば、「数値」に指定していても文字列を入力することができてしまうからです。
したがって、利用者のスキルにかかわらず、強制的に統一したデータ入力をさせるためには、入力の制限や禁止、範囲の限定など、制限をかける必要があります。
Excelでは、こうした機能も備えています。
データの入力規則
という機能で、ここからセルに対する入力制限などの入力規則を設定することができます。操作も非常に簡単ですので、上記の表を利用して入力規則を設定してみましょう。
数値しか入力できないようにする
まず、「A列」から設定しましょう。この列は「社員番号」なので「数値」のみを入力できるようにします。
基本的に、Excelでは全角数字は半角数字に自動変換されるので、数字入力の列に異なる形式の値を入力してしまうことはあまりないと思いますが、例えば「0001」といった社員番号の人がいたとしたら、シングルクォーテーション「'」などで文字列にしたゼロを入力されるかもしれません。（ゼロの表示については、ゼロ値の表示と非表示 を参照してください）
あくまで統一した書式で入力してもらうためには、入力できる値を「数値」のみに限定しなければ完璧とはいえません。
では、「A2」セルをカレントにした状態で、「データ」タブの「データの入力規則」ボタンをクリックします。
すると、「データの入力規則」画面が表示されます。通常は「設定」タブが選択された状態です。この画面の、「入力値の種類」のリストメニューから任意の項目を選択します。
通常ここは「すべての値」になっています。当然、セルにはすべての書式が入力できるからです。今回は「整数」に変更してみましょう。
この設定変更によって、「A2」セルには「整数」しか入力できなくなります。「整数」を選択すると、「データ」、「最小値」、「最大値」のリストメニューが表示されます。
これら3つを組み合わせて、整数の中でさまざまな条件の指定が可能になります。ここでは、「データ」を「次の値の間」、「最小値」を「0」、「最大値」を「9999」としました。
この設定により、セル「A2」に入力できるのは、「0～9999の間の整数のみ」に制限することができます。「OK」ボタンをクリックで設定完了です。「空白を無視する」というチェックボックスがありますが、これについては後述するので、そのままでかまいません。
そして「A2」セルに、整数以外の値、もしくは1万以上の値、文字列など設定外の値を入力すると、下図のようにエラーメッセージが表示され、入力することができなくなります。
これで誰が使っても「A2」セルには「0～9999」の整数しか入力できません。では、社員番号「0001」のような人はどのように入力してもらえばいいでしょうか？「0001」と入力しても「1」に変換されてしまいます。
このような場合は、ゼロ値の表示と非表示 で学習したユーザー定義で「0」を設定します。下図のように、「0000」とすると、4桁までセロで埋めるゼロパディングが設定されます。
これで、たとえ「1」と入力しても「A2」セルは「0001」に自動変換されるようになります。
そして、セルをA列にオートフィルコピーで、入力規則とユーザー定義を複写することができます。ただし、
「書式のみコピー」では入力規則はコピーされない
ので注意が必要です。「書式のみコピー」ではユーザー定義のみがコピーされるので、通常のセルのコピーで複写する必要があります。値もコピーされた場合は、手動で消去するしかありません。（書式のみコピーについては、書式のユーザー定義 を参照してください）
また、最初から範囲が決まっている場合は、範囲指定した状態で入力規則を設定すると、その範囲のセルにまとめて設定することができます。
エラーメッセージを変更する
入力規則外の値を入力した場合に表示されるメッセージは前述のとおりですが、このメッセージも変更することができます。
同「データの入力規則」画面の「エラーメッセージ」タブを選択します。すると、通常は下図のように「無効なデータが入力されたらエラーメッセージを表示する」にチェックが入った状態で、「スタイル」のリストメニューは「停止」が選択された状態となっています。
まず、エラーメッセージを表示しないようにする場合は、「無効なデータが入力されたらエラーメッセージを表示する」のチェックを外します。
すると、入力規則外の値を入力してもエラーメッセージは表示されなくなります。しかし、メッセージを表示しないと、どんな値でも許可してしまうので、入力規則を設定する意味がなくなってしまいます。
次に、「スタイル」のリストメニューを確認してみましょう。「停止」の他に「注意」と「情報」という3種類の項目があります。
これらは、制限の強さによって3段階に分けられています。「停止」が最も強い制限で、「注意」、「情報」と制限が緩くなっていきます。
デフォルト（初期設定）で選択されている「停止」は、規則外の値が入力されると、下図のように「再試行」と「キャンセル」しか選択できず、そのまま強制的に値を確定することができません。
つまり、入力規則で設定されている条件の範囲内の値しか入力することができない強い規制になります。これが、「注意」、「情報」と段階的に緩くなっていきます。
次の「注意」のエラーメッセージです。
規則外の値が入力されると、このように「はい」と「いいえ」、「キャンセル」が選択できます。「はい」を選択すると、規則外の値であっても強制的に値を確定することができます。
最後の「情報」のエラーメッセージです。
規則外の値が入力されると、このように「OK」と「キャンセル」が選択できます。「OK」を選択すると、規則外の値であっても強制的に値を確定することができます。
規則外である旨のメッセージが表示されますが、「続けますか？」という問いではないので、流れで「OK」を選択する可能性が大になります。とは言うものの「注意」とそれほど変わりません。
そして、これらのメッセージをそれぞれ変更することができます。同じタブの「タイトル」と「エラーメッセージ」の欄に任意の内容を入力してみましょう。
すると、下図のように、「停止」「注意」「情報」のメッセージを任意のものに変更することができます。
入力時メッセージを表示させる
コメントの入力について冒頭で触れましたが、それとは別に、入力規則を設定するセルに、入力時のメッセージを表示させることができます。
下図のように、メッセージを設定したセルを選択したとき、ポップアップのように任意のメッセージを表示させることができます。
入力時メッセージの設定は、同「データの入力規則」画面の「入力時メッセージ」タブを選択します。
まず、「セルを選択したときに入力時メッセージを表示する」にチェックを入れます。すると、「タイトル」と「入力時メッセージ」の内容を入力することができるようになります。
任意のタイトルとメッセージ内容を入力します。注意点として、「タイトル」については省略が可能ですが、「入力時メッセージ」を省略して「タイトル」だけを表示させることはできません。
基本的に入力時メッセージは多くのセルに設定することになる場合が多いので、タイトルは省略してなるべく短いメッセージを表示させるほうがいいかもしれません。
日本語入力システムを自動的に切り替える
日本語入力システムとは、「IME」や「ATOK」に代表される日本語の入力環境を提供するソフトウェアのことです。（詳しくは、日本語入力システムとは を参照してください）
Excelに限らず、Wordでもブラウザでも日本語入力の「ひらかな」や「半角英数」といった文字入力のカテゴリを切り替えながら、文字入力されていると思います。
Excelでは、入力規則の機能を利用して、日本語入力システムをセルごとに「ひらがな」や「半角英数」に自動で切り替えることができます。
日本語入力システムの切り替え設定は、同「データの入力規則」画面の「日本語入力」タブを選択します。「日本語入力」のリストメニューより、設定したいモードを選択することができます。
ただし、この機能はあくまでも入力モードを自動的に切り替えるだけです。例えば「ひらがな」に設定していても「半角英数」に切り替えることは当然できるので、強制力はありません。
しかし、「無効」を選択すると「半角英数」しか入力できなくなります。つまり、日本語入力システムを「無効」にするということなので、例外的に強い規制となります。
リストメニュー（ドロップダウンリスト）から選択させる
おそらく、データの入力規則の機能の中で最もよく使われる機能のひとつです。下図のように、リストメニュー（ドロップダウンリスト）から値を選択させるように設定することができます。
セルにリストメニュー（ドロップダウンリスト）を設定するには、同「データの入力規則」画面の「設定」タブの「入力値の種類」で「リスト」を選択します。
すると、下図のように「ドロップダウンリストから選択する」にチェックが入った状態で、「元の値」という入力ボックスが表示されます。
この入力ボックスにリストに表示させる値（数字や文字列など）を入力します。基本的にリストメニューは複数から選択するのが通常なので、文字の間はカンマ「,」で区切ります。
これで、この場合は「麻雀」「ポージング」「射撃」「暴力」「爆破」「ボクシング」の文字列がリストメニューの中に選択肢として表示されるようになります。
ただ、直接文字列を入力するのは少々手間なうえ、選択肢も多くなってくると面倒です。そこで、他のエリアを参照してリストメニューに表示させることも可能です。
例えば、下図のようにリストに表示させる文字列を入力しておきます。
このエリアをマウスで範囲指定して、「元の値」とすることができます。
自動的に絶対参照でエリアが入力されるので、セルをコピーしても参照エリアが移動することはありません。（絶対参照については、基本操作編 数式の複写（1） を参照してください）
選択肢が「〇×」などのように少ない場合は直接入力し、多くなってくるとエリア参照が便利です。ただし、ところかまわずリストメニューの文字列を打ち込んでいたら印刷範囲に入り込む可能性があったり、列を非表示にしたとしても見栄えがよくありません。
別のシートを専用に作成して、そこから参照したほうが管理も楽です。別シートから参照する場合も同様ですが、エリアに名前を付けておけば、その名前で「元の値」にすることも可能です。
名前で参照する場合は、名前の前にイコール「=」が必要です。イコールがないと名前自体がリストにあがってくるので注意してください。（名前について詳しくは、名前の定義と管理 を参照してください）
さて、リストの基本的操作は以上ですが、このままでは、入力規則がデフォルトの「停止」になっているため、リストにある選択肢しか選択することができなくなっています。
そのため、セルに直接入力した文字も許可したい場合は、エラーメッセージを「注意」や「情報」に変更するか、「無効なデータが入力されたらエラーメッセージを表示する」のチェックを外すことで、リスト以外の値も入力できるようになります。
さらに、よくあるのが、リストメニューの文字列を変更したい場合や追加したい場合です。
エリア参照の場合、文字列を変更すると、ともなってリストの文字列も変更されます。しかし、問題となるのは、追加した場合です。
下図のように、元からの値「ボクシング」を「カバディ」に修正すると、リストメニューもともなって「カバディ」に変わります。ところが、参照しているエリア外に追加した文字列は、当然ながらリストメニューには追加されないのです。
このようなケースがよくあります。そのため、エリアの参照は名前で管理することをおすすめします。名前でエリアを参照している場合は、「名前の管理」より、エリア範囲を修正するだけですべてのセルでリストメニューも変換されます。（詳しくは、名前の定義と管理 を参照してください）
とは言え、範囲指定や直接入力で「元の値」を指定している場合もあります。その場合は、まず、任意のセルで修正をかけます。
次に、「同じ入力規則が設定されたすべてのセルに変更を適用する」にチェックを入れることで、同じ規則を設定してあるセルすべてに変更を適用することができます。
全角文字・半角文字しか入力できないようにする
例えば、氏名を入力していくシートがあったとして、複数の人が使うフォーマットでは、姓と名の間のスペースに全角と半角が混じっていたり、フリガナの全角と半角が混在していたりすることがよくあります。
そのため、全角なら全角、半角なら半角の文字しか入力できないように入力規則を設定できればよいのですが、先述の日本語入力システムを自動変換させる方法では、強制力がありません。
では、どうするのかというと、この場合は前項同様やはり関数を使う方法しかないようです。
いくつか方法があるようですが、簡単なのは「JIS関数」を使う方法です。JIS関数は通常、文字列に含まれる半角文字を全角に変換する関数になります。例えば「A1」セルに設定する場合、式は、
=A1=JIS(A1)
とします。まず、同「データの入力規則」画面の「設定」タブの「入力値の種類」で「ユーザー設定」を選択します。すると、数式を入力できるボックスが表示されます。
セルの番地を当該セルに変更して式を直接記述します。これで、上記の場合は「B2」セルに全角以外の文字が入力されると、デフォルトで「停止」のエラーメッセージが表示されるようになります。
これは、姓と名の間が「半角」スペースのためにエラーとなっています。デフォルトの停止メッセージではわかりにくいので、「全角文字で入力してください」などとメッセージ内容を変更すれば完成です。
ただし、JIS関数では全角の「数字」が規則外になってしまうので注意してください。
逆に「半角文字」だけに制限したい場合は、「ASC関数」を使います。
ASC関数は通常、文字列に含まれる全角文字を半角に変換する関数になります。例えば「A1」セルに設定する場合、式は、
=A1=ASC(A1)
とします。同様に「ユーザー設定」から、セルの番地を当該セルに変更して式を直接記述します。これで、下記の場合は「C2」セルに半角以外の文字が入力されると、エラーメッセージが表示されるようになります。
これは、姓と名の間が「全角」スペースのためにエラーとなっています。
ただし、ASC関数では半角の「数字」が規則外になってしまううえに「ひらがな」も許可してしまいます。他にも関数式での設定が可能ですが、万能ではないようです。英数字のみを利用する場合は、日本語入力を「無効」にするのが最も有効です。
規則外の値を抽出する
通常の「停止」ではなく、「注意」や「情報」といった強制入力が可能な制限をかけている場合、規則外の値が入っていないかどうか、チェックすることが可能です。
操作は非常に簡単で、「データの入力規則」ボタン横のリストメニューから「無効データのマーク」を選択するだけです。
すると、強制入力した値のセルに赤丸が付きます。このように簡単に規則外の値を抽出できるので、その部分だけあとから修正することができます。
ただし、この場合も先述の関数を利用した規制のように、規制対象にならない値ですり抜けた値は抽出されません。あくまで「注意」と「情報」の制限で強制的に入力したセルだけになります。
また、少々わかりにくいのですが、ドロップダウンリストを作成している場合で、範囲指定したエリアに「空白セル」が含まれている場合、「空白を無視する」にチェックが入っていると、「停止」であってもどんな値も入力できてしまいます。
この例では、ここにチェックが入っているために「停止」のエラーチェックをすり抜けた値はチェックされません。下図は、「D3」セルにあとから手入力した無効な値です。
通常、ここにはチェックが入っている状態なので、リストを作成する際には、あとから追加することを予測して多めに空白セルまで範囲に含まないようにするか、このチェックを外しておくようにしましょう。
更新履歴
- 2018年10月6日
- ページを公開。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- 「空白を無視する」とは？
- http://officetanaka.net/excel/function/tips/tips51.htm
- 重複をチェックしながら入力COUNIF関数
- https://kokodane.com/kan23.htm
- 管理人のつぶやき
- Twitterのフォローお願いします！