上図はVLOOKUP関数を使用し、価格表のデータを参照して、単価を取得する方法である。
セルG4に「=VLOOKUP(F4,$C$4:$D$8,2)」と入力すると、「単価」が求められる。
上図は、セルC4からD8までを、「価格表1」という名前に登録したものである。
[新しい名前]を開いて、[名前]に「価格表1」と、[参照範囲]に「=Sheet ! $C$4:$D$8」と
を入力し[OK]を押して登録する。
上図のセルG4に「VLOOKUP(F4,価格表1,2)」と入力すると、「単価」が求められる。
セルG4をG8までにコピーすると、「価格表」のD4からD8までのデータなら、F列に入力する
「商品名」に対する「単価」を取得することができる。
しかし、「価格表」にデータを追加したら、「単価」を求めることはできない。
それを解決するために、「価格表2」を定義しよう。
上図の様に[参照範囲」に「=OFFSET($C$4,0,0,COUNTA($C:$C)−1,2)」と入力し、[OK]を
クリックして「価格表2」を定義する。
上図は、価格表に「カップ6」と「カップ7」を追加したものである。
セルG9に、「=VLOOKUP(F9,価格表2,2)」と入力すると、「カップ6」の単価が求められた。
表は、同じ数式をG4からG13セルまでにコピーしたものである。
G10から下は、F列に商品名が無いのでエラーが表示されている。
上図は、「商品名」が未入力でもエラーが表示されなくし、「商品名」が価格表に未登録の
場合に「データなし」と表示するようにしたものである。
「=IF(F11=" "," ",IF(F11=VLOOKUP(F11,価格表2,1),VLOOKUP(F11,価格表2,2),"データなし"))」
とG11に記入したものである。
個の数式をG4セルに記入して、下方にコピーすれば「価格表」に行を追加しても対応できる。
もちろん、「価格表」だけが別シートに存在しても問題はない。
上図は、「価格表」を定義する時の状態である。
[参照範囲]を選択すると、「Sheet2！」と記入されるのが分かる。
[名前の定義]は、範囲を選択した「シート名」が自動で入力されるのである。
修正しない限り、そのシートに存在するのである。
上図は、シート3に作成した見積書である。シート2の「価格表2」を参照して「単価」を
取得しているのが分かる。このように、どこのシートからでも「価格表2」は参照できる。
上図の数式「=OFFSET($C$4,0,0,COUNTA($C:$C)−1,2)」を説明すると。
OFFSET関数の引数は、「=OFFSET(参照,行数,列数 [,高さ] [,幅])」である。
第４引数は省略加工であるが、[高さ]を指定すると、移動後のセル範囲の行数を指定できる。
第５引数も省略可能であるが、[幅]を指定すると、移動後のセル範囲の列数を指定できる。
COUNTAの数式は、C列のデータ件数から「商品名」の項目分の1行を差し引いた分を行数と
指定する。
