2013Excel関数技36回

VLOOKUP関数をオートフィルでコピー

エクセルのVLOOKUP関数は、とても便利でいろいろな場面で使われています。
でも数式をコピーするときに、気をつけないといろいろと面倒なことが起こります。
いつも数式のコピーの後で引数の修正をしているなら、こんな方法で解決です。 そんな、面倒なことにならないスマートなエクセルのVLOOKUP関数の使い方を紹介します。
(サンプルファイルは、こちらから 関数技36回サンプルデータ

VLOOKUP関数の数式をコピー

データベース形式の表からVLOOKUP関数を使ってデータを表示するようなケースはよくありますね。 データベース形式の表は、テーブルとして書式設定してあり、名前は”USERS”としてあります。
VLOOKUP関数の使い勝手を良くする1
このシートに調べたいIDあるいはコードなどを入力して、該当するデータをすべて表示しようと思います。 データは、VLOOKUP 関数で簡単に表引きできます。
VLOOKUP関数の使い勝手を良くする2
図のようにB2セルにVLOOKUP関数の数式を入力すれば、通常ならそのままコピーできますが、
VLOOKUP関数の使い勝手を良くする3
VLOOKUP 関数の引数である、列指定の部分、これは自動で変化しません。 コピーした結果は、コピー元の繰り返しになってしまいます。
VLOOKUP関数の使い勝手を良くする4
D列ならば、VLOOKUP 関数の引数である、列指定の部分を4に変更する必要があります。
VLOOKUP関数の使い勝手を良くする5

列数を取得する関数COLUMNS関数

そこで、自動的に該当する列番号を取得する関数としてCOLUMNS関数を使いましょう。
たとえば、B2セルに=COLUMNS($A$3:B3)と入力すれば、返り値として2が表示されます。
VLOOKUP関数の使い勝手を良くする6
この式をVLOOKUP関数にネストすれば、自動的に列数を取得できます。
=VLOOKUP($A$3,USERS,COLUMNS($A$3:B3),0)
VLOOKUP関数の使い勝手を良くする6
たくさんのデータをVLOOKUP 関数で表引きする時に、列指定にCOLUMNS関数を利用すると、 オートフィルでコピーした場合に修正の必要がなくなります。
VLOOKUP関数の使い勝手を良くする6
時短になり、ミスも起きにくくなります。
スポンサーリンク
スポンサーリンク