Excel。1列置きのデータを抽出し、さらに表引きも合わせて行いたい
＜VLOOKUP+COLUMN関数＞
横長のデータがA1:H5にあります。
4月から6月までの売上のデータだけを抽出した表をつくりたい。
できれば、NOを入力したデータのみを表引きしたい場合、どのようにしたらいいのでしょうか。
全体の表から必要な列だけを抽出するだけならば、
HSTACK関数をつかうことで、手早く抽出することができます。
A8に設定した数式は、
=HSTACK(A2:B5,D2:D5,F2:F5,H2:H5)
引数には、必要なフィールドを設定するだけの関数なので、必要な列だけを抽出するならば、とても便利な関数です。
ただ、これに合わせて、例えば、NOが1と3だけのデータを抽出したい。
表引きしたいとなると、HSTACK関数だけでは対応することができません。
少々複雑な数式に変更しなければならなくなります。
そこで、今回は、VLOOKUP関数とCOLUMN関数を組み合わせて、対応していきます。
B8に数式を設定しました。
=VLOOKUP($A8,$A$2:$H$5,COLUMN(A1)*2,FALSE)
あとは、オートフィルで数式をコピーします。
これで、月別の売上の列と、NOが1と3だけのデータを抽出することができました。
では、この数式を確認しておきましょう。
最初の引数は、検索値。A8を選択します。
ただ、列固定の複合参照にする必要がありますので、$A8 とします。
2つ目の引数は、A2:H5の表全体を選択します。
オートフィルで数式をコピーしますので、絶対参照が必要です。
よって、$A$2:$H$5 とします。
3つ目の引数は、列番号。
ここがポイントになるわけです。
オートフィルで数式をコピーしたときに、列番号を2・4・6というように2の倍数が必要になります。
横方向にオートフィルした時に、数値が2の倍数にするために、列番号を求めることができる、COLUMN関数をつかってみます。
COLUMN(A1)*2
これで、A1は1と求められるので、その値に、×2すれば、2の倍数を求めることができます。
最後は、検索方法です。
完全一致なので、FALSE と設定します。
最近、新しい関数が色々登場しております。
関数を組み合わせることで、もっと効率的に求めたり、表引きできると思いますので、試してみるといいものが、発見できるかもしれませんね。
