VLOOKUP関数でExcel帳票への自動入力を可能にする：Tech TIPS

連載目次

解説

　注文書や見積書のような定型帳票を入力する際、いちいち商品コードと商品名、単価、あるいは顧客コードに顧客名、住所を入力しなければならないことに回りくどさ（あるいはいら立ち）を覚えるケースも少なくないだろう。

　商品コードを入力すれば、そのまま帳票内に商品名や単価など、一意に決まる項目が自動入力されれば、どんなにか入力効率は向上されるだろう。そもそも入力が自動的になされるようになれば、商品名や単価などの入力ミスもなくなる。

　本TIPSでは、Excelワークシート関数の1つ、VLOOKUP関数を用いることで、商品コードを入力するだけで商品名と単価が自動的に補完入力される注文書帳票を作成してみる。

操作方法

●手順1―注文書のテンプレートを作成する

　まずはExcelを起動して、注文書のテンプレートを作成してみよう。

作成した注文書のテンプレート
作成する注文書のテンプレート。コードを入力すると、それに対応する商品名と単価欄が自動的に補完入力される。そしてさらに数量を入力すると、金額と合計が自動的に計算される。2枚目のproductsシートには、コードと商品名、単価の商品情報リストを入力する。
　 （1）シート名は仮に［order］とする（任意に変更可能）。
　 （2）キーとなる商品コードを指定する。
　 （3）（2）で商品コードを指定すると、自動的に対応する商品名が入力される。
　 （4）商品の数量を指定する。
　 （5）（2）で商品コードを指定すると、自動的に対応する商品単価が入力される。
　 （6）（4）と（5）を積算した小計が自動計算される。
　 （7）（6）で求められた全行の総計が自動計算される。

• サンプル・ファイルのダウンロード
  （注：サンプル・ファイルorder.xlsをダウンロードするには、上のリンクを右クリックして、order.xlsというファイル名で保存してください）

　（6）の列には小計を自動計算するための計算式を指定する。例えばセルG4ならば、以下のような計算式となる。

　単に「=E4*F4」とすると、セルF4（単価欄）が入力されていない場合（元となる商品コードが入力されていない場合）、エラー「!#VALUE」が表示されてしまうので注意が必要だ。IF関数を使用して、セルB4（商品コード）が入力されている場合にのみ計算を行い、それ以外のときには空文字列をセットする。

　セルG4の内容を、表の下端（セルG12）までコピーしておくこと（セルG4の右下端をセルG12までドラッグすればよい）。

　小計の計算式が用意できたら、セルG13に総計を求める関数をセットする。

　もちろん、SUM関数の計算範囲は表の大きさによって、自由に変更することができる。

●手順2―商品情報リストを作成する

　VLOOKUP関数による自動入力を行うためには、まず検索（LOOKUP）の対象となる商品情報リストを別に用意しなければならない。VLOOKUP関数は、あらかじめ用意されたリストを検索することで、マッチングした商品情報を返すという機能を持つ関数である。

　まずは新しい［products］シートを作成してみよう。次の画面のように、商品情報リストには「（商品）コード」「商品名」「単価」を入力する。

［products］シートの内容
このシートには商品情報を登録する。ここではコードとして、ある書籍のタイトルとその単価を例として利用している。VLOOKUP関数はこの表の内容を検索し、指定されたコードに一致する商品名や単価を返す。
　 （1）シート名は仮に［products］とする。このシート名はVLOOKUP関数内で参照しているので、変更する場合は注意すること。
　 （2）コード。ここでは英字3桁＋数字4桁としているが、特に制限はないので自由に決めてよい。
　 （3）商品名。
　 （4）単価。

　リスト上の値はまったくの固定値なので、特筆すべきことは特にない。もちろん、注文書にそのほかの属性項目を自動入力したいという場合には、リストの項目を拡張すればよい。

●手順3―商品名、単価欄を自動入力する

　再び［order］シート（注文書本体）に戻ってみる。そしてセルC4に注目してみよう。当該セルには次のような計算式（関数式）を入力する。

　IF関数で、セルB4（［コード］欄）が未入力の場合には空文字列を返し、値が入力されている場合には、VLOOKUP関数を呼び出している。これはセルG4と同じ考え方だ。「products!$B$2:$D$11」は、productsシート上のセル（B2:D11）を「絶対参照」するための指定である。「products!B2:D11」という相対参照では、このセルの内容をコピーしたときに、参照位置が自動的にインクリメントされてしまい、商品情報リストからずれてしまうので、これを避けている。

　IF関数内にあるVLOOKUP関数に注目してみてほしい。VLOOKUP関数の書式は次の通りである。

　今回の例では、第2引数に直接「セル参照」を指定しているが、あらかじめ定義されたセルの「範囲名」で指定することも可能だ。「範囲名」は、メニュー・バーから［挿入］−［名前］−［定義］を選択することで指定することができる。

　今回の例ならば、［参照範囲］に「=products!B2:D11」を、［名前］に「商品リスト」と指定する（名前は任意に自分の好きなものに変更して構わない）。この場合、VLOOKUP関数の指定も次のように変更する。

　「範囲名」を使用することで、VLOOKUP関数の参照範囲が変更された場合でも、呼び出し側のIF関数を変更する必要がない、つまり保守性に優れるという利点が得られる。今回のような例ではあまり効果を実感できないかもしれないが、複数の異なるVLOOKUP関数から同一のリストを参照しており、かつ、そのリストに変更があった場合でも「範囲名」の再定義だけを行なえばよい。

　もう1つ、第4引数についても注目だ。今回のサンプルでは、第4引数にTrueを設定しているため、リスト上のキーに「完全」一致していなければ、情報を引用することはできない（ただし英字の大文字・小文字は区別されない）。しかしTrueを指定することで「あいまい」検索が可能になる。

　例えば、次のような点数と評価の対応表があったとしよう。

　10〜19点では「××」、20〜29点では「×」……というように、点数の範囲によって評価を変化させたいとする。このような場合に、VLOOKUP関数の第4引数をTrueに設定するのである。

　任意のテーブルに対してこの関数式を記述したのち、セルA1に（例えば）21と入力してみよう。対応するセルには「×」という値が返されるはずだ。

　つまり第4引数にFalseを指定した場合には、指定された数値未満の最大値をマッチさせることができる。VLOOKUP関数のこのような用途は、これまでIF関数をいくつも入れ子にして、複雑な判定を余儀なくされていた方にとっては大いに役立つはずだ。

　ただし「あいまい」検索を行う場合、リストは必ず昇順（文字であれば「あ〜ん」または「A〜Z」の順、数値であれば「0〜9」の順、日付であれば古い順）にソートしておく必要がある。ソートされていない表は意図したマッチングを行うことができないので、注意してほしい（「完全一致」検索の場合にはこうしたソートは不要）。

　セルC4の設定が終わったら、セルF4（「単価」欄）についても同様に記述する。

　今度は、VLOOKUP関数の第3引数（引用する列）に「3」を指定していることで、商品情報リストの単価を引用している点だけが異なる。このように、第3引数の部分を変更するだけで、引用する情報をいくらでも増やすことが可能になる。

　最後にセルC4、F4の内容を続くレコード行にコピーして完成だ。コード欄に、商品情報リストに対応するコードを入力してみて、商品名と単価が自動入力されることを確認してみてほしい。

「Tech TIPS」