Excel。VLOOKUP関数の抽出する列が離れた位置に複数ある時のテクニック
＜VLOOKUP関数＋配列＞
VLOOKUP関数の引数にある、列。
これは、範囲で設定した中で、
左側から何列目のデータを抽出するのですか？という意味の列番号ですが、
作成したVLOOKUP関数の数式をオートフィルで数式をコピーした場合、
列番号が、2・3・4・5というように連続している場合ならば、
COLUMN関数を使って、VLOOKUP関数の数式を修正すると、
オートフィルで数式をコピーできることを、以前ご紹介しました。
Excel。一覧表から行単位で抽出するならVLOOKUP関数とCOLUMN関数もおススメ。
https://infoyandssblog.blogspot.jp/2015/06/excelvlookupcolumn.html
今回は、列番号が、2・3・4・5のように連続ではなくて、不規則。
例えば、2・4・7みたいに連続していない場合は、
オートフィルで数式をコピーしてもダメなので、
いちいち、列を修正しないといけないのか？という問題に関して、
【配列】という手法を用いることで、
手間暇をかけずに、VLOOKUP関数の数式をコピーできるようになりますので、
紹介していきます。
まず次の表があります。
そして、VLOOKUP関数の範囲である、商品リストが別のシートにあります。
今回はどのようにしたいのか？といいますと、納品書のD列にある単価ですが、
特別セールということで、ランチメニューの特別価格を抽出したいということにしましょう。
C列の商品名をVLOOKUP関数で作成したら、
D列にはオートフィルで数式をコピーして完成というようにしたいわけですが、
先程も書きましたように、VLOOKUP関数の列がネックになってきます。
そこで、今回は次のような手順で作成していきます。
まず、納品書でVLOOKUP関数が入る、1行を範囲選択します。
わかりにくいと思いますが、とりあえず、C3:D3を範囲選択します。
次に、
VLOOKUP関数を作成していくわけですが、
VLOOKUP関数ダイアログボックスで作成するとやりにくいので、
今回は、直接手入力で、VLOOKUP関数を作成していきます。
=VLOOKUP(B3,商品一覧!$A$5:$D$14,{2,4},FALSE)
と作成していきます。範囲のところが、{2,4}となっておりますが、列番号が2。そして続いて4を使うという意味です。
入力し終えたら、間髪入れずに、Ctrl + Shift + Enterを押します。
このCtrl + Shift + Enterを押すことによって、数式が、配列数式にチェンジます。
数式が、次のように変化しました。
={VLOOKUP(B3,商品一覧!$A$5:$D$14,{2,4},FALSE)}
{ }のカッコが前後につきましたね。これは、配列数式のマークです。
そして、C3:D3に結果が表示されていますね。
商品番号がL001は、
商品名が、サンドイッチセットで、単価は、特別価格の585を抽出していますね。
あとは、C3:D3を範囲選択して、下方向にオートフィルで数式をコピーしてみましょう。
これで完成しました。
このように、VLOOKUP関数の列が飛び飛びだったり、
不規則な場合には、配列数式を使ってみるという方法もありますよ。
