VLOOKUP関数を使って
単価表から「注文伝票」を簡単作成
エクセルでは、表の中から検索条件と一致するデータを取り出し、セルの値やセルの位置を求める「検索/行列関数」が16種類あります。なかには、似たような名前の関数があるので、どの関数をどのようなときに使えばよいのか迷ってしまいます。たとえば、LOOKUP関数とLOOKUPウィザード、VLOOKUP、HLOOKUP関数など。LOOKUP関数は、検査値と対応する同じ位置のセルの値を検索範囲の中から取り出す関数。VLOOKUP、HLOOKUP関数を利用すると複数の検索範囲の中から列番号(行番号)を指定するだけで、検索値のデータを取り出すことができるもの。LOOKUPウィザードは、検査値が交差する値を求めるウィザードでLOOKUP関数とは別のもので、INDEX関数とMATCH関数を組み合わせた式と同じ機能を持ちます。今回は、これらの関数をどのようなときに使い分ければよいのか注文伝票を利用し、伝票の内訳を求める方法などを見ていきましょう。なお、ここではもととなる表と伝票とは同じワークシートに作成してありますが、別シートに分けて作成すると、伝票部分の印刷時などに便利です。
検索条件と一致するデータをLOOKUP関数で求める
関数を使うと、入力も効率化してくれます。たとえば、商品番号と商品名、価格を決めた「単価表」を一度用意しておけば、伝票類には、商品番号を入力するだけで価格が表示される、といった使い方が可能になります。
A．LOOKUP関数で「コード｣から「顧客名」を表示
伝票に「コード」番号を入力すると、該当する「顧客名」が表示できるようにするといった場合はLOOKUP関数を使う。この場合、「コード｣には「顧客名」だけが対応している。
B．VLOOKUP関数で「コード｣から「種類」「単価」を表示
「コード」に商品名や単価などを対応させた「表」を使うならば、VLOOKUP関数が便利。ここでは「商品コード」を入力すると、「種類」と「単価」が表示できるようになっている。
C．INDEXとMATCH関数で2つの検査値が交差する値を求める
商品の組み合わせで金額が異なる場合などに便利なのが、あらかじめ対応表を作成しておき、INDEXとMATCH関数を使う方法。同じことはLOOKUPウィザードでも求められる。
A．LOOKUP関数で「コード」から「顧客名」を表示
（1）関数を設定するセルC5を選択し（2）[関数貼り付け]ボタンをクリック。「関数の貼り付け」ダイアログの「関数の分類」ボックスから（3）[検索/行列]を選択し、（4）「関数名」ボックスから[LOOKUP]を選択する。（5）[OK]ボタンをクリック
（6）「検査値」はB5をクリック。（7）「検査範囲」はH11からH16を選択し、（8）「対応範囲」はI11からI16を選択。（9）[OK]ボタンをクリック。なお検査範囲は必ず昇順に並べておくこと。
B．VLOOKUP関数で「コード」から「種類」「単価」を表示
ここでは商品コードが未入力の場合に空欄表示となるよう、IF関数を利用。まず式が入るセルC10をクリック。[関数の貼り付け]ボタンをクリックしIF関数の数式パレットを表示。（10）「論理式」には、B10に商品コードが未入力の場合の式を設定。セルを空白表示にするにはダブルクォーテーションを2つ入力。ここで右へ式をコピーしても参照先がずれないように列を固定するため、[F4]キーを3回押す（1回押すと列と行を固定、2回は行、3回は列を固定）。「真の場合」の欄には、（11）商品コードが未入力の場合、空白を入力するのでダブルクォーテーションを2つ入力。次に「偽の場合」の欄を選択、[関数名ボックス]の一覧からVLOOKUP関数を選択。
（12）「検索値」はB10をクリック、あとから式をコピーしてもずれないように[F4]キーを3回押す。（13）「範囲」はK11からM16までを範囲選択し、範囲がずれないよう[F4]キーを1回クリックし絶対参照にする。（14）「列番号」は表の2列目なので「2」と入力。（15）検索値と完全に一致する値を求める場合には「検索の型」に「FALSE」（または「0」）と入力。「TRUE」と入力すると検査値と一致する値がない場合、検索値未満の最大値が求められる。式は
「=IF($B10="","",VLOOKUP($B10,$K$11:$M$16,2,0)」
となる。
C11からC15まではオートフィル機能を利用し式を設定。なお、検索する値が、行方向に並んでいる場合は、HLOOKUP関数を利用する。
次にE10の「単価」にも同じ式をコピー。ただし、単価はコード表の3列目なので「列番号」は、[2]を[3]に変更。E11からE15まではオートフィル機能を利用し求める。次にF10の「金額」の欄にもIF関数を利用し式を設定。商品コードが空白の場合は空白、そうでない場合はとして、（16）「注文数×単価」の式「D10*E10」を入力。同じくF11からF15までは、オートフィル機能を利用し式を設定。F16では[オートSUM]ボタンを利用し、F10からF12までの合計を求めている。
B7にはF16を参照する式を設定。B7をクリックし、数式バーの[=]ボタンをクリック、F16クリックし[ENTER]キーを押す。ここでは文字列と合計値が組み合わさって表示されるよう、B7で右クリック、[セルの書式設定]の[ユーザー定義]で「"御請求金額は"#,##0"円也"」と設定。
C．INDEXとMATCH関数で2つの検査値が交差する値を求める
INDEX関数とMATCH関数を組み合わせることで、表の中から検査値が交差する値を求めることができる。ここでは該当する金額を求める。（17）セルH25を選択。INDEX関数の数式パレットで「配列」に（18）B19からE25までを選択。「行番号」では、MATCH関数を使い、「検査値」に（19）H23を、「検査範囲」に（20）B19からB25を選択、「照合の型」には、[0]と入力。次に「列番号」を出すために、数式バーの式の[INDEX]の所をクリック。再度MATCH関数を選択。「検査値」は（21）H22、「検査範囲」は、（22）B19からE19までを選択。「照合の型」は、検査値と完全一致なので[0]と入力。H25に入力された式は、
「INDEX(B19:E25,MATCH(H23,B19:B25,0),MATCH(H22,B19:E19,0))」。
D．LOOKUPウィザードを使った場合は・・・
MATCH、INDEX関数を使わずに検査値が交差する値を求める方法もある。H25をクリックし、ツールメニューの[ウィザード]を選択し、サブメニューから[LOOKUP]を選択。ウィザード1/4では、データの表であるB19からE25までを範囲選択する。ウィザード2/4では列ラベルにローズを設定し、行ラベルにブーケ<A>を選択する。ウィザード3/4では[単一のセルに数式のみコピーする]を選択し、ウィザード4/4ではH25を選択し[OK]ボタンをクリック。
