Aさん VLOOKUP関数は便利なのは実感していますが、横方向にコピペするとイチイチ「列番号」を直すのが面倒ですが、何か良い方法はないでしょうか？
森田 「列番号」はなぜか数式中に直接数値を入れてしまう方が多いですね。お手軽に解決するなら、「列番号」のところをセル参照させても解決できますよ！
では、初心者向けにわかりやすく解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- データの転記・集約作業が多い人
- エクセルを使う頻度が高い人
- 事務職を目指している人
ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方
同じ「検索値」の複数列のVLOOKUP関数を1回のコピペで済ませたい！
言葉だけだといまいち伝わっていない方もいると思いますが、要は以下のような状態を指しています。
この場合、同じ列であれば同じ数式をコピペすれば良いですが、違う列になるとVLOOKUP関数の引数「列番号」をいちいち手修正する必要がありますね。
これって列が多ければ多いほど面倒になります。
もっともお手軽に解決するなら「セル参照」でOK！
上記の解決策は複数ありますが、もっとも簡単なのは、引数「列番号」を定数ではなくセル参照することです。
ここでのポイントはワークシートの余白部分をうまく使うことですね。
たとえば、次のようにVLOOKUP関数を入力しているセルの上部に、作業用のセルとして引数「列番号」を値として入れてしまうイメージです。
これで、上記の例であればB3セルのVLOOKUP関数の数式を右方向へコピペしても問題ないですね。
サンプルファイルで練習しよう！
では、実際に今回のVLOOKUP関数のテクニックを使ってみましょう！
サンプルの条件
今回の題材は次のとおりです。
「名簿」というシートに生徒の出席番号・氏・名・誕生月・誕生日が一覧表になっています。
そして、もうひとつ「検索」シートがあり、すでにA3セルには出席番号”A-01”が入力済みです。
この出席番号”A-01”をキーワードに、B～E列に該当の氏・名・誕生月・誕生日をVLOOKUP関数を用いて転記しましょう。
実際に操作しよう！
可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。
サンプルファイル_VLOOKUP関数_列番号①
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ファイルを開いたら次の手順を実施してください。
- 「検索」シートを選択
- A1~E1セルへ左のセルから”1”~”5”の連番を入力する
- B3セルへ「=VLOOKUP($A3,名簿!$A:$E,B$1,0)」を入力
※赤字の部分をコピーして貼り付けてください。 - B3セルをコピー
- C3～E3へ貼り付け（ペースト）
上記手順を行った結果は「検索 (関数あり)」シートにありますので、このシートの内容と実際に操作した結果が同じなっていればOKです。
A3セルの出席番号を他の番号にプルダウンを切り替えた際に、ちゃんと連動してすべての値が変わるかも確認しましょう。
ポイントの解説
最後に今回のテクニックの3つのポイントについて解説していきましょう。
【POINT1】主キーの設定
このテクニックに限らずVLOOKUP関数を使う際のマストなポイントです。
こちらは検索を行う対象の表に「主キー」が入ったものを準備しておきましょう。
この「主キー」とは、データを固有のものと識別するための値で一意（他データと重複していない）である必要があります。
今回の例でいえば、”出席番号”が該当しますね。
こういった「主キー」があると、VLOOKUP関数で意図しない戻り値（返り値）になることを防ぐことができます。
【POINT2】作業セルの活用
引数「列番号」を列参照させるための重要な概念は「作業セル」です。
「作業セル」とは、作業用のセルを用意することでVLOOKUP関数の数式をシンプルにしたり、関数単体では実現できない動きを行うために役立ちます。
今回の例でいえば、「検索」シートのA1~E1セルへ入力しておいた”1”~”5”の連番ですね。（厳密にいうと、A1セルは不要ですが、利用者がわかりやすいように今回は入れています）
このA1~E1セル内の値がVLOOKUP関数の引数「列番号」としてセルへ外出しできたので、コピペの利便性は上がりますし、参照範囲の変更にも柔軟に対応することができるようになりました。
たとえば、今回はVLOOKUP関数を入力したセルが1行×4列だけでしたが、これがさらに100行あったとしたらどうでしょうか？
ひとつの数式さえ完成させれば、残りはすべてコピペで一括対応できますよ。
さらに、参照したい「列番号」が変わる場合も、A1~E1セルの値を直せば、すべてのVLOOUP関数の参照セルも自動的に変更になりますので修正も容易ですね。
【POINT3】絶対参照・相対参照の設定
これは、数式を扱う上で基礎中の基礎ですが、コピペを一括で行うために必要です。
今回セル参照しているのは3つの引数ですが、それぞれ以下の意図があって絶対参照・相対参照を設定しています。
すべて「検索」シートのB3セルをもとに説明していきますね。
引数「検索値」
今回は「$A3」と列のみ絶対参照にしています。
これは、A列にキーワードとなる”出席番号”があり、B~E列に入れるVLOOKUP関数の「検索値」は同じ行なら一緒なため、列のみ固定にしているわけです。
引数「範囲」
今回は「名簿!$A:$E」と行列ともに絶対参照にしています。
これは、どのVLOOKUP関数も参照する範囲は「名簿」シートのA～E列のため、行列どちらも固定にしているわけです。
引数「列番号」
今回は「’検索’!B$1」と行のみ絶対参照にしています。
これは、1行目に引数「列番号」用の作業セルを用意しており、すべてのVLOOKUP関数がそれぞれと同じ列の1行目を参照させるために、行のみ固定にしているわけです。
絶対参照・相対参照のまとめ
上記の説明は実際にワークシート上にさらに複数行のVLOOKUP関数が増えるとわかりやすいです。（画像をクリックすると拡大します）
さいごに
数式や関数の重要な考え方のひとつとして、複数セルへ一括でコピペできるようにベースとなるひとつの数式を記述することが挙げられます。
そのことを理解していると、今回のように作業セルをうまく活用すれば、単純なセル参照だけでも効率化が図れることを知っておくと良いですね。
こういったテクニックに加え、その他の実用的なVLOOKUP関数関連のテクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 かくいう私も最初はVLOOKUP関数の「列番号」は直接数値を入れていたので、よく手修正をしていました。扱うデータが多くなってきて「面倒くさい！」と思い立ち、ネットや書籍でいろいろ応用テクニックを学んできて今があります。皆さんも「面倒くさい！」と思った時がチャンスですよ。
このテクニック感謝です。
地味に面倒だと思っていてどうにかならないかなあと思っておりましたので。
ありがとうございました。
あきぷさん
コメントありがとうございます。
面倒なことが解消されてよかったです！
これからも役立つテクニックを掲載できるようにがんばります(^^)/