VLOOKUP関数をオートフィルでコピー
たくさんのデータをVLOOKUP 関数で表引きする時に、数式をオートフィルでコピーした場合の面倒を解決する技を紹介しています。
2013Excel関数技36回
VLOOKUP関数をオートフィルでコピー
エクセルのVLOOKUP関数は、とても便利でいろいろな場面で使われています。
でも数式をコピーするときに、気をつけないといろいろと面倒なことが起こります。
いつも数式のコピーの後で引数の修正をしているなら、こんな方法で解決です。
そんな、面倒なことにならないスマートなエクセルのVLOOKUP関数の使い方を紹介します。
(サンプルファイルは、こちらから 関数技36回サンプルデータ)
VLOOKUP関数の数式をコピー
データベース形式の表からVLOOKUP関数を使ってデータを表示するようなケースはよくありますね。
データベース形式の表は、テーブルとして書式設定してあり、名前は”USERS”としてあります。
![VLOOKUP関数の使い勝手を良くする1](gazo2013/kan/2013_kan_36_01.png)
![VLOOKUP関数の使い勝手を良くする1](gazo2013/kan/2013_kan_36_01.png)
このシートに調べたいIDあるいはコードなどを入力して、該当するデータをすべて表示しようと思います。
データは、VLOOKUP 関数で簡単に表引きできます。
![VLOOKUP関数の使い勝手を良くする2](gazo2013/kan/2013_kan_36_02.png)
![VLOOKUP関数の使い勝手を良くする2](gazo2013/kan/2013_kan_36_02.png)
図のようにB2セルにVLOOKUP関数の数式を入力すれば、通常ならそのままコピーできますが、
![VLOOKUP関数の使い勝手を良くする3](gazo2013/kan/2013_kan_36_03.png)
![VLOOKUP関数の使い勝手を良くする3](gazo2013/kan/2013_kan_36_03.png)
VLOOKUP 関数の引数である、列指定の部分、これは自動で変化しません。
コピーした結果は、コピー元の繰り返しになってしまいます。
![VLOOKUP関数の使い勝手を良くする4](gazo2013/kan/2013_kan_36_04.png)
![VLOOKUP関数の使い勝手を良くする4](gazo2013/kan/2013_kan_36_04.png)
D列ならば、VLOOKUP 関数の引数である、列指定の部分を4に変更する必要があります。
![VLOOKUP関数の使い勝手を良くする5](gazo2013/kan/2013_kan_36_05.png)
![VLOOKUP関数の使い勝手を良くする5](gazo2013/kan/2013_kan_36_05.png)
列数を取得する関数COLUMNS関数
そこで、自動的に該当する列番号を取得する関数としてCOLUMNS関数を使いましょう。
たとえば、B2セルに=COLUMNS($A$3:B3)と入力すれば、返り値として2が表示されます。
![VLOOKUP関数の使い勝手を良くする6](gazo2013/kan/2013_kan_36_06.png)
たとえば、B2セルに=COLUMNS($A$3:B3)と入力すれば、返り値として2が表示されます。
![VLOOKUP関数の使い勝手を良くする6](gazo2013/kan/2013_kan_36_06.png)
この式をVLOOKUP関数にネストすれば、自動的に列数を取得できます。
=VLOOKUP($A$3,USERS,COLUMNS($A$3:B3),0)
![VLOOKUP関数の使い勝手を良くする6](gazo2013/kan/2013_kan_36_07.png)
=VLOOKUP($A$3,USERS,COLUMNS($A$3:B3),0)
![VLOOKUP関数の使い勝手を良くする6](gazo2013/kan/2013_kan_36_07.png)
たくさんのデータをVLOOKUP 関数で表引きする時に、列指定にCOLUMNS関数を利用すると、
オートフィルでコピーした場合に修正の必要がなくなります。
![VLOOKUP関数の使い勝手を良くする6](gazo2013/kan/2013_kan_36_09.png)
![VLOOKUP関数の使い勝手を良くする6](gazo2013/kan/2013_kan_36_09.png)
これにより時短になり、ミスも起きにくくなります。