VOLLOUP関数を挿入しているセルをオートフィルして困ったことありませんか?
列番号の数値がしっかり反映されないことが多く結局は手入力で直してませんか?
1つ2つくらいのデータなら手直しでも良いですが、これが10個以上も手直しだととても大変です。
オートフィルした時に自動で列番号を修正してくれる方法をを知りたい。
このような疑問にお答えします。
それはVLOOKUP関数の列番号(第3因数)に指定したセルの列番号を表示してくれるCOLUMN関数や行番号を表示してくれるROW関数使うことによりこの問題が解決します。
COLUMN関数とROW関数を使うメリット他にもあり、シート内編集時に行や列が削除・追加されてもそれを検知して値を自動で直してくれます。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/ae46b1f460ee46f789c27b264a6cb421-8.jpg)
![](https://tyubei-blog.com/wp-content/uploads/2020/04/ae46b1f460ee46f789c27b264a6cb421-6.jpg)
VLOOKUP関数の第3因数にCOLUMN関数やROW関数を挿入する方法
![](https://tyubei-blog.com/wp-content/uploads/2020/04/f00f2de4703ab5a3814589aabf79eafa.jpg)
VLOOKUP関数にCOLUMN関数を挿入する
B16にVLOOKUP関数を入れて、右にオートフィルするとこのようになります。
原因としては第三因数が2のままなので抽出される値はすべて名前だからです。
第三因数は行番号の指定で、オートフィルしても変動しない値になっております。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/a14b3f176936283fea4b39bbb15b30e6.jpg)
第三因数を手動で修正すればこのように項目が抽出されますが、数が多いととても大変です。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/afb2473194d29363480ab74ae1589197.jpg)
オートフィルした時に一緒に列番号の値を変える方法は、COLUMN関数を使うことで解決されます。
どこにCOLUMN関数を入れるのかというと、VLOOKUP関数の第三因数の中に入れて行きます。
数式にすると
=VOOLKUP(検索値,範囲,COLUMN(列番号),検索方法)
という数式になります。
この数式を先ほどの表に当てはめるとこのようになります。
「VLOOKUP($A$18,$A$2:$G$10,COLUMN(B$1),0)
COLUMN関数の中の値はVLOOKUP関数で指定した表の項目を入れます。
正直、列番号さえしっかりと記入されればどこでも良いのですが、関数を編集する時に変な所を指定しているとわかりにくくなります。
そのためこの表で一番の基準となる項目欄指定することで編集時にわかりやすく統一した数式にします。
COLUMN関数の中に入る値の行を複合参照して、オートフィルしても1行目から動かないように固定してあげましょう!
![](https://tyubei-blog.com/wp-content/uploads/2020/03/2217edbc6148973a81f9943298612b82.jpg)
氏名の値は田中、これを右側にオートフィルすると項目欄の値が記入されます。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/1bcc20f52891c2c34398808949d43278.jpg)
16行目に入っている関数を下の方までオートフィルすると統一された数式で正確な値が抽出されてます。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/7354b8fe1ca91b6fb7e4da5d1d4eb35a.jpg)
これがVLOOKUP関数の中にCOLUMN関数を入れてオートフィルを使用するやり方になります。
VOOLKUP関数にROW関数を挿入する方法
次に第三因数にROW関数を挿入するやり方で、下の表を使って解説していきます。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/0a8a10030a713b7a7564f30a53835b06-1024x220.jpg)
右側の表で黄色く塗りつぶされているセルに、IDを入れるとその表の項目に必要な情報が抽出できるようにしたい。
使う関数はVLOOKUP関数とROW関数
どのように組み合わせるのかと言うと、VLOOKUP関数の第三因数の中にROW関数を挿入します。
「=VLOOKUP(検索値,範囲,ROW(列番号),検索方法)
この数式をK3に挿入するとこのような数式になります。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/95d09b1dcd22bd12157a71bbf826c9c8.jpg)
検索値と範囲の絶対参照は必須になります。
列番号にROW関数を挿入、関数編集時にわかりやすい様に抽出する方IDのセルを指定しました。
ROW関数の中の値に複合参照してJ行に固定してます。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/ae46b1f460ee46f789c27b264a6cb421-8.jpg)
検索方法は完全一致でFALSE
となります。
OKを押すとこのように氏名項目名前が表示されます。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/8072c18905b6df1b6645d257f3f1018c-1024x247.jpg)
備考欄までオートフィルするとこのように項目の内容が抽出されます。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/8032d296b09deebd391c6eca63906138-1024x217.jpg)
K2の黄色く塗られているID欄の数字を変更すると自動で項目変更される仕組みになっております。
![](https://tyubei-blog.com/wp-content/uploads/2020/04/03bf229ad1b96e7111c8ca6c88978aac-1024x214.jpg)
VLOOKUP関数応用編 まとめ
今回はVLOOKUP関数の第三因数に、COLUMN関数とROW関数を挿入する解説でした。
VLOOKUP関数は最低限使えるのは当たり前で、COLUMN関数とROW関数などを一緒に使うことで作業の効率化やミスなどを減らすことができます。
VLOOKUP関数にはまだたくさんの便利な使い方がありますので随時ご紹介していきます。
![](https://tyubei-blog.com/wp-content/uploads/2020/07/ae46b1f460ee46f789c27b264a6cb421-320x180.jpg)