Aさん VLOOKUP関数は便利なのは実感していますが、横方向にコピペするとイチイチ「列番号」を直すのが面倒ですが、何か良い方法はないでしょうか？
森田 「列番号」はなぜか数式中に直接数値を入れてしまう方が多いですね。実は解決策は3つありますが、難易度や状況に応じておすすめな手法が変わります。
それぞれの難易度やメリット/デメリットを含めて解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- データの転記・集約作業が多い人
- エクセルを使う頻度が高い人
- 事務職を目指している人
ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方
同じ「検索値」の複数列のVLOOKUP関数を1回のコピペで済ませたい！
言葉だけだといまいち伝わっていない方もいると思いますが、要は以下のような状態を指しています。
この場合、同じ列であれば同じ数式をコピペすれば良いですが、違う列になるとVLOOKUP関数の引数「列番号」をいちいち手修正する必要がありますね。
これって列が多ければ多いほど面倒になります。
引数「列番号」を自動変更する3つのテクニック
メインの解決策は3種類あります。いずれもVLOOKUP関数の引数「列番号」の部分に組み合わせて使いますよ。
- セル参照
- MATCH関数
- COLUMN関数
それぞれ、難易度やメリット/デメリットの詳細は次のとおりです。
セル参照
難易度
3つのうち、もっとも簡単です。VLOOKUP関数を使い始めの方でも設定が容易ですよ。
メリット
- 横方向にコピペ時に引数「列番号」を自動で変更する
- 初心者向けなので、誰でも活用できる
- VLOOKUP関数の検索する表へ列追加などで「列番号」が変わった場合、作業セルへ最小限の手作業で修正可能
- VLOOKUP関数の検索する表から転記対象の列が飛び飛びでも問題ない
デメリット
- VLOOKUP関数を用いるワークシート上で作業セルを使えない場合は対応できない
詳細の対応方法
【初心者向け】VLOOKUP関数の引数「列番号」を自動で変更するテクニック①【セル参照】 | Excelを制する者は人生を制す ～No Excel No Life～
MATCH関数
難易度
3つのうち、もっとも難しいです。
しかし、MATCH関数は掘り下げると、そもそもVLOOKUP関数に使い方が似ているので、VLOOKUP関数を使い始めの初心者ユーザーでも落ち着いて考えれば絶対使えるはずです。
メリット
- 横方向にコピペ時に引数「列番号」を自動で変更する
- 3つのうち、もっとも保守性が高く、VLOOKUP関数の検索する表へ列追加などで「列番号」が変わった場合も、自動的に「列番号」を計算してくれる
- VLOOKUP関数の検索する表から転記対象の列が飛び飛びでも問題ない
デメリット
- VLOOKUP関数を用いるワークシート上へMATCH関数の引数「検査値」を用意する必要あり
- やや数式が長くなる
詳細の対応方法
【中級者向け】VLOOKUP関数の引数「列番号」を自動で変更するテクニック②【MATCH関数】 | Excelを制する者は人生を制す ～No Excel No Life～
COLUMN関数
難易度
3つのうち、ちょうど真ん中の難易度です。COLUMN関数自体シンプルな関数なのでVLOOKUP関数を使い始めの方でも設定が容易ですよ。
メリット
- 横方向にコピペ時に引数「列番号」を自動で変更する
- 初心者向けなので、誰でも活用できる
- 作業セルは一切不要
デメリット
- VLOOKUP関数の検索する表へ列追加などで「列番号」が変わった場合、数式バー上の手作業で修正になる（作業セルと違い失念しやすい）
- VLOOKUP関数の検索する表から転記対象の列が飛び飛びでは一括対応ができない（連続した列でないと1列ずつ調整必要）
詳細の対応方法
【中級者向け】VLOOKUP関数の引数「列番号」を自動で変更するテクニック③【COLUMN関数】 | Excelを制する者は人生を制す ～No Excel No Life～
まとめ
簡単に一覧表にまとめてみます。参考にしてみてくださいね。
| セル参照 | MATCH関数 | COLUMN関数 | |
|---|---|---|---|
| 難易度 | ○ | × | △ |
| 横方向にコピペ時の「列番号」の自動変更 | ○ | ○ | ○ |
| 検索する表へ列追加時の自動変更 | △ | ○ | × |
| 作業セルなどの事前準備なし | × | × | ○ |
| 検索する表内の転記対象の列が飛び飛びの場合の対応 | △ | ○ | × |
こうしてみると、一長一短ですね。自分の現時点のレベルやワークシートの状況などに応じて使い分けできるようになることが理想ですね。
さいごに
同じVLOOKUP関数の数式を横方向にもコピペしていくことは、実務で良く見受けられます。
もし、現時点でVLOOKUP関数の引数「列番号」を定数にしている方は、使えそうなものから取り入れてみてくださいね。
ちなみに、私はMATCH関数との組み合わせを好んで使いますね。一番保守性が高いので、後々数式をいじることがほぼなくなりますから。
今回はVLOOKUP関数の「列番号」に特化した記事でしたが、それ意外のVLOOKUP関数の各引数に対する応用テクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 今回ご紹介したテクニックなどを用いて、コピペの回数を最小限にできるように「列番号」をきちんと調整している方をみると、「おっ、やるな！」と感心してしまいます。
ぜひ、今使えていないという方は実務をとおして練習しましょう！
