エクセルファイルのダウンロード
HOME 便利テク集
エクセルの機能の一つに、【データの入力規則】というものがあり、これを使えば、リスト選択によるデータ入力や、入力値に対して制限を設けることが、可能になります。ここでは、その【データの入力規則】について説明し、その中で、エクセル関数で、非常によく利用されるVLOOKUP関数についても、付加的に説明したいと思います。
以下のような注文書の作成を、今回の課題にしたいと思います。状況設定として、スーパーが青果を仕入れる際に、スーバーが卸問屋に青果を発注する場合の、注文書を想定しています。
エクセルファイルのダウンロード
課題の詳細は、以下で、注文明細の入力項目は、【注文品名】、【単価】、【数量】、【金額】、【生産元】とします。
※【注文品名】、【単価】、【生産元】の値は、【TBL_VEGETABLE】シートの価格表に従うものとします。
まずは、スーパー樋口の店員になったつもりで、この課題に取り組んでみてくださいね。
入力項目の【注文品名】をリスト選択できるように、セルに【データの入力規則】を設定します。まず、注文品名の先頭データ(D15)を選択します。その後、リボンの【データ】タブの中の、【データの入力規則】をクリックします。
以下の【データの入力規則】画面が表示されますので、タブの【設定】を選択し、【入力値の種類】の項目から、リストを選択します。次にリスト表示するデータ群を指定するため、【元の値】項目の右側の【範囲選択】(矢印)ボタンをクリックします。
範囲選択の画面が表示されますので、【TBL_VEGETABLE】シートを選択し、注文商品名となる青果名のデータをマウスのドラッグにより、アスパラガス(A2セル)からレモン(A30セル)をまでを選択します。
選択範囲(=TBL_VEGETABLE!$A$2:$A$30)に問題がなければ、【Enter】キーを押下します。
※範囲選択に列全体($A:$A)を指定すると、ヘッダ(1行目)もリストに追加されてしまいますので、2行目からのデータを範囲指定してやります。
以上で、注文品名のリスト設定が完了しました。ここまで、うまく出来ていれば、以下の通りとなります。確認が出来ましたら、【OK】ボタンを押してください。
うまく設定出来ているか確認するため、設定が完了したD15セルを選択してみてください。右側に【プルダウン】ボタンが表示されますので、クリックすると以下のような、リストが表示されるはずです。これを選択することで、容易に注文品名が選択できるようになります。
このD15セルの設定をコピーし、D16～D37セルにペーストしてやります。但しペーストする際は、右クリックにより、ポップアップメニューを表示し、【形式を選択して貼り付け】を選択し、さらに【入力規則】にチェックを入れ、ペーストしてやります。
以上で、注文品名のリスト設定が完了しました。
入力項目の【単価】については、【注文品名】が指定されれば、価格表(TBL_VEGETABLE)から、注文品名を検索し、単価を自動挿入するよう設定したいと思います。これには、エクセルの標準関数であるVLOOKUP関数を使用します。
=VLOOKUP(検索文字列,表範囲,返却列位置,検索の一致性)
このVLOOKUP関数は、指定した表(第2引数)から、指定した文字列キー(第1引数)を縦方向(垂直:vertical)に検索し、検索一致した行の、指定された列データ(第3引数)を返却するいった処理を行います。但し、検索されるデータは、指定された表範囲の一番左端の列となりますので、注意が必要です。
※ソフトウェアにおいて、検索ワードにより、検索される側のデータが全て、一意に定まる項目のことを、キーと呼んでいます。例えば、価格表をアスパラガスで検索したときに、アスパラガスに該当するデータが複数ある場合は、キーとは呼びません。逆に、今回のように検索結果が、一意に定まる場合、【青果名】をキーと呼びます。
それでは、実際に単価項目のセルに、このVLOOKUP関数を設定してみましょう。まず、単価データの先頭に位置するL15セルを選択してください。
セルの式に=VLOOKUP(D15 … とし、第1引数である検索文字列に、注文商品であるD15を指定します。
次に、第2引数に表範囲(TBL_VEGETABLE!A:C)を指定してやります。第2引数入力のタイミングで、【TBL_VEGETABLE】シートを選択し、青果名(列A)～生産元(列C)を、マウスのドラッグにて、選択してやります。
続いて、第3引数に返却データの列位置を指定するのですが、ここでは、返却データは【単価】データとなりますので、引数には、2を指定します。列位置は、指定した表の左端から1basedでカウントした値を指定します。
最後に、第4引数ですが、これは、検索の一致性を指定するもので、完全一致の場合には、FALSE(=0)を指定してやります。近似一致の場合は、TRUEを指定します。今回は、完全一致で検索しますので、FALSEを指定してください。
これで、VLOOKUP関数の設定が、基本的には、出来たことにはなるのですが、注文品名が指定されていないと、単価の値が、エラー表記#N/Aとなってしまいます。これでは、少し見映えが悪いので、IFERROR関数を併用します。
=IFERROR(評価式,出力データ)
このIFERROR関数の第1引数には、評価式を指定します。第2引数には、その評価式の出力結果がエラーだった場合に、出力するデータ(式の設定も可能)を指定します。
ですので、第1引数には、先にセルに設定した式:VLOOKUP(D15,TBL_VEGETABLE!A:C,2,FALSE)を指定し、第2引数には、この評価式がエラーだった場合に出力する、空文字列を指定します。これにより、注文商品が指定されていないときの、エラー表記の代わりに、空文字列が出力されるようになり、見映えが良くなります。
これで、入力項目の【単価】の先頭セル(L15)に数式が正しく設定できましたので、このセルをコピーし、他の単価データのセル(L16～L37)にペーストします。このペーストは右クリックにより行い、数式のみのペーストとします。
以上で、入力項目の【単価】セルの設定は終了です。
入力項目の【数量】については、基本的には手入力によるものとします。但し、入力できるデータを入力規則により、制限したいと思います。制限内容は以下とします。制限値については、あくまでもサンプルですので、お好きな値に変更して頂いて、結構です。
リボンの【データ】タブを選択し、【データの入力規則】をクリックし、設定画面を表示し、以下のように、それぞれ設定します。
これで、入力項目の【数量】の設定は完了です。
入力項目の【金額】には、単価×数量の計算結果を表示します。まず、【金額】の先頭セル(R15)を選択し、そのセルに以下の式を設定します。
=IFERROR(L15*O15,"")
この数式設定を、R16～R37セルにコピーします。
入力項目の【生産元】については、【単価】の項目の設定と同様に、【注文品名】が指定されれば、価格表(TBL_VEGETABLE)から、注文品名を検索し、生産元を自動挿入するよう設定したいと思います。ですので、この項目に設定する数式は、【単価】のときに設定したVLOOKUP関数の第3引数の返却列位置を2→3に置き換えるだけとなります。
=IFERROR(VLOOKUP(D15,TBL_VEGETABLE!A:C,3,FALSE),"")
上記が、【生産元】の先頭セル(U15)に設定する数式となります。この数式を、【生産元】の他のセル(U16～U37)にペーストしてください。
以上で、全ての設定が終了しました。色々とデータを入力して確認してみてください。
VBAプログラムを利用しなくても【データの入力規則】機能を利用すれば、比較的簡単に、セル入力に対する制限や、メッセージを表示することが可能になります。ちょっとしたリスト入力などは、この機能を利用するだけで、十分対応できるかと思います。
また、セルのデータを編集する際に、IME(日本語変換)がONだったり、そうでなかったり、少し面倒さを感じたりする場合が、ありますが、この【データの入力規則】の設定では、IMEのON/OFFの設定なども出来ますので、こういった点も、解消できるかと思います。
サンプルのデータは、私の創作によるものです、サンプルデータに含まれる個人・団体名などの情報は、実在の人物とはまったく関係ありません。
ご意見・ご要望等ありましたら、画面最下部のメールアドレスまでご連絡ください。
