入力規則とはあらかじめセルに入力できる値を制限しておく機能です。入力する項目が限られている時はリスト化してマウスで選択するだけで入力することができます。また第三者に発注書などの資料を渡して入力してもらう際、予期せぬ入力をされてしまい、結果的におかしな資料になってしまうようなこともあるため、事前にセルに入力できる値を制限しておくことによりトラブルを未然に防ぐことができます。
Excel-入力規則
こんな時に便利
- 同じ項目を毎回入力するのが面倒
- 他の人が入力する時におかしな入力ができないように制限したい
- いくつかのよく使う項目をリスト化してマウス操作だけで入力できるようにしたい
- 発注数などを入力する時に10の倍数でしか入力できないようにしたい
基本的な使い方
まずは入力規則の設定画面を開きます。下の図を参考に設定画面を開いていください。
設定画面の上に【設定】・【入力時メッセージ】・【エラーメッセージ】・【日本語入力】の4つのタブがあります。個人的には【設定】以外はあまり触ることはありませんが、簡単に説明していきます。
【設定】
まずは設定について。個人的には入力規則の設定はほぼここしか触りません。制限したい内容に応じて入力値の種類を選択します。
種類は全部で8種類。下の表で簡単に説明します。
入力値の種類 | 入力可能となる値 | 指定できる条件 |
すべての値 | 初期値(制限なし) | なし |
整数 | 条件で指定した範囲の整数 | 次の値の間 次の値の間以外 次の値に等しい 次の値に等しくない 次の値より大きい 次の値より小さい 次の値以上 次の値以下 |
小数点数 | 条件で指定した範囲の小数点数 | 次の値の間 次の値の間以外 次の値に等しい 次の値に等しくない 次の値より大きい 次の値より小さい 次の値以上 次の値以下 |
リスト | 元の値で指定した文字列のみ(リスト化可能) | 任意の文字列(カンマ区切りで複数可能) |
日付 | 条件で指定した範囲の日付 | 次の値の間 次の値の間以外 次の値に等しい 次の値に等しくない 次の値より大きい 次の値より小さい 次の値以上 次の値以下 |
時刻 | 条件で指定した範囲の時刻 | 次の値の間 次の値の間以外 次の値に等しい 次の値に等しくない 次の値より大きい 次の値より小さい 次の値以上 次の値以下 |
文字列(長さ指定) | 条件で指定した範囲の文字列(文字数) | 次の値の間 次の値の間以外 次の値に等しい 次の値に等しくない 次の値より大きい 次の値より小さい 次の値以上 次の値以下 |
ユーザー設定 | 数式に入力した条件式を満たす値 | 任意の数式 |
それでは簡単な設定をしてみましょう。下の図と同じように設定すると整数の10から100以外は入力ができなくなります。条件を入力してOKをクリックすれば設定できます。
試しに設定したセルに5を入力してみると下の図のようにエラーメッセージが表示され入力ができないことが確認できます。
これで発注数は10から100の整数でしか入力ができなくなり制限することができます。またごく稀に”100ケース”などと有難迷惑な単位をつけて入力するような方もいますが、このように制限しておけば整数しか入力ができないので安心です。その他の種類についても基本的な考え方は同じです。ただし【リスト】と【ユーザー設定】だけは少し設定方法が違うので後ほど解説します。
【入力時メッセージ】
入力規則を設定したセルを選択した時に表示されるメッセージを任意で作成することができます。先ほど設定したセルを選択して入力規則の設定画面を開き【入力時メッセージ】タブにタイトルとメッセージを入力すると、そのセルが選択されている時にメッセージが表示されるようになります。
【エラーメッセージ】
【停止】・【注意】・【情報】という3つが選択可能。初期値は【停止】になっているので、条件を満たさない限り入力することはできません。【注意】は条件が満たされていないが入力しますか?というメッセージが表示され選択が可能になります。【情報】は条件が満たされていないというメッセージが出てOKをクリックするとそのまま入力ができます。メッセージの内容も任意で設定できますが、個人的には【停止】以外はあまり使用しませんのでここでは何も設定はしません。
【日本語入力】
これもあまり使うことはありませんが、あらかじめ日本語入力をオンにしておき、切り替える手間を省きます。例えば、氏名を入力する時にA列に漢字表記・B列に半角カタカナ表記・C列にローマ字表記を順番に入力していく時、いちいち全角/半角を切り替えるのは面倒なので、日々行うような業務であれば設定しておくと手間が省けます。この場合はA列に【オン】を設定、B列に【半角カタカナ】を設定、C列に【オフ(英語モード)】を設定しておけば切り替えずにそのまま入力ができます。
リストの使い方
入力規則で一番よく使うのがこの機能です。限られた項目だけを入力できるように制限し、尚且つドロップダウンリストからマウスで選択するだけで入力ができるので非常に便利です。
リストの基本的な使い方
リストを設定したいセルを選択している状態で入力規則の設定画面からリストを選択し、元の値の中にリスト化したい項目をカンマ( , )で区切って入力してOKをクリックする。下の図では【果物】【野菜】【肉】【魚】の4つをリスト化しています。
設定後のセルに表示されるドロップダウンリストが下の図になります。4つの項目がリスト化されマウスで選択可能となりました。
先ほどは元の値に直接項目を入力しましたが、すでにセルに入力されている値を指定することもできます。元の値の中にカーソルを合わせた状態で項目が入力されたセルをマウスで選択すると、元の値にセル参照が入力されるのでOKをクリックすれば同じようにドロップダウンリストが出来上がります。
ポイント
【☑空白を無視する】と【☑ドロップダウン リストから選択する】は☑のままにしておくこと。特にドロップダウンリストのチェックを外すとセルを選択してもドロップダウンリストが表示されず手入力することになり意味がありません。【空白を無視する】は先ほどまでに紹介した使い方であれば、チェックを外しても動作に影響・変化はありません。影響があるのは元の値に【名前の定義】で作った名前で指定した場合のみなのであまり気にしないでください。
リストの応用(可変リスト)
ちょっと難しくなりますので小ネタ程度に紹介します。セルの値によってリストの中身を変える設定方法です。下の図のように種類と品名の一覧表がありセルJ2に種類を入力するリストを作り、その値に応じた品名をセルK2のリストに表示させるという可変参照範囲を設定します。
まずはセルJ2に下の図と同じように種類が入力されているJ4:U4を元の値に設定します(種類が新たに増えてもリストに反映するようU列まで指定)。
次に名前の定義とOFFSET関数を使って可変参照範囲を定義します。名前の定義の使い方はこちらを参照ください。OFFSET関数については別で紹介しますが、簡単に説明すると、基準となるセル範囲を決め、その基準セルから縦横に指定した数値分だけ動かしたり広げたりしたセル参照範囲を返す関数です。OFFSET(基準セル,縦ずれ数値,横ずれ数値,縦幅,横幅)という引数指定になりますが、ここでは詳細は省きます。まずは名前の定義を開きます。
次に適当な名前をつけて参照範囲に下の図のようにOFFSET関数を入力します。OFFSETの中にMATCHやCOUNTAが入っていますが、簡単に説明すると、基準のセルをJ5(りんご)とし、そこからMATCHを使って横ずれさせ、COUNTAを使って縦幅を決め、その参照範囲を返す式になっています。
セルK2に先ほど作った名前を使い入力規則のリスト設定をします。セルK2を選択して入力規則の設定画面を開き、元の値にカーソルを合わせた状態でキーボードのF3を押します。
すると名前の貼り付けという画面が開き、先ほど作った名前が一覧にあるはずなのでそれを選択してOKをクリックします。
元の値に先ほど作った名前が入力されていればOKです。
OKをクリックしたら下の図のようなエラーメッセージが表示されますが問題ありません。そのまま「はい」をクリックします。これで完成です。
まずは種類のリストを確認します。下の図のように4種類が正しくリスト化されています。
種類に【果物】を入力している状態で品名のドロップボックスをクリックすると【りんご】【ばなな】【ぶどう】がリストアップされます。
種類を【野菜】に変えると品名は【きゃべつ】【白菜】【人参】となりリストアップが種類に応じて変更されたのが確認できます。
【果物】の一覧の下に【いちご】を追加すると品名にも【いちご】が自動で追加される。これは名前の定義でOFFSETの中に入っていたCOUNTAのお陰で、一覧表のデータ数に応じて勝手に範囲が広がるようになっています。
ユーザー設定の使い方
複雑な条件で入力制限をかけたい時はユーザー設定を使って設定します。数式の欄に論理式を入力し、その論理式を満たす(真:True)の時のみ入力が可能となります。論理式には【=】以外にも【<】【>】などの比較演算子や【AND】【OR】などの複数条件関数も使用できます。下の図では発注数を入力する時に10の倍数以外は入力できないように制限をかける数式を設定しています。MOD関数は除算(割り算)をした時の余りの数値を算出する関数。下の図ではセルD8の値を10で割った余りが0の時(10の倍数の時)だけ入力可能という設定になります。他のセルにも設定したい時は、設定したセルD8をコピーして貼り付ければ入力規則も貼りつきます。
試しに55を入力するとエラーメッセージが表示されて入力できません。
90は10の倍数なので入力可能。
まとめ
入力規則は自分で使う時も便利なのですが、他の人に入力をしてもらう時に予期せぬ入力をされないよう制限できるというのが一番の魅力です。資料作りができるようになればなるほど、他の人が使うための資料作りも増えてくると思います。相手が不慣れなほど予期せぬことは発生します。そのような時にはぜひ入力規則を存分に使ってください。
新着記事