vlookup関数を使って資料を作っているときに、
・関数をコピーして横に広げると、列の指定がうまくいかない…
・複数の列の値を結合して一気に抽出したい…
と思ったことありませんか?
今回はこの2つの悩みに対してお答えしていきます!
vlookupで列番号を可変して複数列を抽出する方法3つ
列番号を手動で振る
難易度:2.0
汎用性:2.0
まずは一番わかり易いものを紹介します。
こちらはわかりやすいのですが、列が途中で挿入された場合や、入れ替えられたときに対応できないと思います。
ちょっとした集計には使えるものになります。
画像内の赤枠に指定したIDの人の名前と出身地を抽出したいという例題で解説をしていきたいと思います。
通常のように抽出すると
=VLOOKUP(E3 , A2:C10 , 2 , false)
となりますね。
3つめの引数を動的に指定をしていきたいと思います。
列が2→3→…というように可変させたいので、
関数が入る上部に列を振ってしまいます。
このようにすれば、列指定を記入した列番号のセルにしていけば問題ありませんね。
あとは、相対参照を間違えないように振っていって、、、
=VLOOKUP($E3,$A$2:$C$10,F$1,false)
あとは、横、縦にコピーをしていけば一気に抽出をすることができます。
これで抽出ができました。
最終的な数式はこんな感じです。
match関数を使って列を指定する
難易度:2.5
汎用性:4.0
次は、列名によって列番号を取得して、それによって可変していく方法を紹介します。
この方法では列が挿入されたり、入れ替えが起こってもそれに応じて値が可変するため1つめの方法よりも汎用性が高くなっています。
今回はmatch関数を使っていきます。
match関数とは、指定の範囲から指定の値を検索して、それが何番目にあるのか?を教えてくれる関数です。
ここでわかった方もいるかもしれませんが、このmatch関数で探したい列名を探して、それが何列目なのかを返してもらう、という方法をやっていきます。
match関数とは?
MATCH(検査値, 検査範囲, [照合の型])
※今回は照合の型は完全一致の0を使っていきます。
まずは、列番号を出したいため、match関数のみを書いていきます。
列番号は、列がずれるごとに2→3→…と可変させたいため、
F3内の列番号を「2」と表示させるようにします。
=MATCH(F$2,$A$1:$C$1,0)
このように指定してあげることで、
F2の名前のものを
A1〜C1の中から
0=完全一致で探してください
という指示になり、「2」を得ることができます。
参照をつけて、コピーしてあげれば隣のセルでは、計算結果は「3」となります。
ここまでできたら、この関数をvlookup関数の中に入れ込んでいきましょう。
これを縦横にコピーしていくと…
抽出できましたね!
念の為関数も載せておきます。
hlookup関数を使って列を指定する
難易度:3.5
汎用性:4.0
最後にもう少し応用をして、いきたいとおもいます。
3つめの例では、
・COLUMN関数
・HLOOKUP関数
を使って列を可変していきたいと思います。
少し工程が複雑なので、結果から載せます。
工程は、
①COLUMN関数で指定したい列番号を取得
↓
②HLOOKUP関数でその番号を取得
↓
③VLOOKUP関数に入れ込む
です。
①では、
=COLUMN()
と入力するとそのセルの列番号が抽出できる特性を利用し、
データの2行目に関数を追加します。
これで
項目名が「名前」は2列目、「出身地」は3列目
ということがわかります。
後は項目名が指定されたときに3列目、4列目、と値を返してあげる関数を書く必要があります。
それがHLOOUP関数になります。
VLOOKUP関数は範囲内で○列目の値を抽出しますが、
HLOOKUP関数は範囲内で○行目の値を抽出します。
今回の場合では、
名前という値を指定されたときに「2」という値を返してほしいので、
HLOOKUP(F$3,$A$1:$C$2,2,false)
という数式を作っておきます。(上記で項目名に応じて列番号が取得されるようになる)
ここまでできれば、あとはvlookupの数式を作っていきます。
普通にやれば、
=vlookup($E4,$A$3:$C$11,2,FALSE)
と書くところ、「2」の部分を関数で置き換えるため、
=vlookup($E4,$A$3:$C$11,HLOOKUP(F$3,$A$1:$C$2,2,false),FALSE)
このようになります。
これで項目名が変化したり、列の挿入、入れ替えが行われても関数エラーが起こらないようになります。
vlookupで複数列を一気に抽出する方法
次に、複数列を関数で出力する方法をご紹介します。
こちらはシンプルで、
「&」という関数を使っていきます。
&は関数や文字列を結合し、結合した結果を返す、という関数になります。
今回の場合、名前と出身地を結合して出力したい場合を解説します。
答えを書くと下記のようになります。
=VLOOKUP($E4,$A$2:$C$11,2,false)&VLOOKUP($E4,$A$2:$C$11,3,false)
vlookup関数を結合すればOKですね。
vlookup関数がよくわからない、、という人は
こちらの記事を見てみてください
vlookup関数の基本の使い方
上記のhlookup関数をいれて少し応用すると、このように長くなりますが同じ答えを得られますね。
=vlookup($E4,$A$3:$C$11,HLOOKUP(F$3,$A$1:$C$2,2,false),FALSE)&vlookup($E4,$A$3:$C$11,HLOOKUP(G$3,$A$1:$C$2,2,false),FALSE)
まとめ
今回はvlookup関数を使って複数の列番号を取得していく方法を解説しました。
vlookup関数はとても便利な関数で、一番使われる関数でもあります。
様々なパターンに対応できるようになると業務効率も上がりますので、根気強く理解していきましょう!