AさんAさん

VLOOKUP関数は便利なのは実感していますが、横方向にコピペするとイチイチ「列番号」を直すのが面倒ですが、何か良い方法はないでしょうか?

森田森田

「列番号」はなぜか数式中に直接数値を入れてしまう方が多いですね。一番おすすめなのは、「列番号」の引数にMATCH関数を組み合わせることですが、ちょっと「難しいな」と思ったらCOLUMN関数でもコピペできるようにできますよ。
では、詳細をわかりやすく解説していきますね。

はじめに

本題に入る前に、この記事がおすすめな人を挙げてみます。

  • データの転記・集約作業が多い人
  • エクセルを使う頻度が高い人
  • 事務職を目指している人

ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
データ転記の作業時間を9割カット!VLOOKUP関数の使い方

同じ「検索値」の複数列のVLOOKUP関数を1回のコピペで済ませたい!

言葉だけだといまいち伝わっていない方もいると思いますが、要は以下のような状態を指しています。

この場合、同じ列であれば同じ数式をコピペすれば良いですが、違う列になるとVLOOKUP関数の引数「列番号」をいちいち手修正する必要がありますね。

これって列が多ければ多いほど面倒になります。

引数「列番号」にCOLUMN関数を組み合わせる!

VLOOKUP関数の引数「列番号」をエクセル側で計算してくれるようにするために、COLUMN関数を活用します。

COLUMN関数とは?

このCOLUMN関数はVLOOKUP関数と同じく検索/行列関数の一種で、任意の列番号を返してくれる関数です。

COLUMN([参照])

試しに、このCOLUMN関数を単体で使ってみるとイメージが湧きやすいと思います。

たとえば、次の表のC8セルに引数なしのCOLUMN関数を入れてみましょう。

引数がない場合、COLUMN関数が入っているセルが何列目かを数値で返してくれます。

今回のC8セルはC列なので、左から数えて3番目です。

よって、C8セルの戻り値(返り値)は”3”になるわけですね。

この戻り値(返り値)をVLOOKUP関数の引数「列番号」として活用するのが、今回のテクニックの肝となります。

ちなみに、COLUMN関数について、もっと詳しく知りたい方はこちらの記事をご参照くださいね。→こちら

組み合わるとはどういうことか?

組み合わせるとは、このCOLUMN関数とVLOOKUP関数の引数「列番号」のところに入れるわけです。

こうして2つの関数を組み合わせて使うことで、引数「列番号」が自動で計算されるため、

B2セルのVLOOKUP関数の数式を右方向へコピペしても自動的に引数「列番号」を計算してくれます。

サンプルファイルで練習しよう!

では、実際に今回のVLOOKUP関数とCOLUMN関数の組み合わせテクニックを使ってみましょう!

サンプルの条件

今回の題材は次のとおりです。

「名簿」というシートに生徒の出席番号・氏・名・誕生月・誕生日が一覧表になっています。

そして、もうひとつ「検索」シートがあり、すでにA2セルには出席番号”A-01”が入力済みです。

この出席番号”A-01”をキーワードに、B~E列に該当の氏・名・誕生月・誕生日をVLOOKUP関数で転記しましょう。

実際に操作しよう!

可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。

サンプルファイル_VLOOKUP関数_列番号③

ファイルを開いたら次の手順を実施してください。

  1. 「検索」シートを選択
  2. B2セルへ「=VLOOKUP($A2,名簿!$A:$E,COLUMN(),0)」を入力
    ※赤字の部分をコピーして貼り付けてください。
  3. B2セルをコピー
  4. C2~E2へ貼り付け(ペースト)

上記手順を行った結果は「検索 (関数あり)」シートにありますので、このシートの内容と実際に操作した結果が同じなっていればOKです。

A2セルの出席番号を他の番号にプルダウンを切り替えた際に、ちゃんと連動してすべての値が変わるかも確認しましょう。

ポイントの解説

最後に今回のテクニックの3つのポイントについて解説していきましょう。

【POINT1】主キーの設定

このテクニックに限らずVLOOKUP関数を使う際のマストなポイントです。

こちらは検索を行う対象の表に「主キー」が入ったものを準備しておきましょう。

この「主キー」とは、データを固有のものと識別するための値であり、かつ一意(他データと重複していない)である必要があります。

今回の例でいえば、”出席番号”が該当しますね。

こういった「主キー」があると、VLOOKUP関数で意図しない戻り値(返り値)になることを防ぐことができます。

【POINT2】VLOOKUP関数側の表と引数「範囲」で参照している表の列番号によってCOLUMN関数の戻り値(返り値)を調整する

きちんとVLOOKUP関数の引数「列番号」として返したい数値になるようにCOLUMN関数の戻り値(返り値)を調整する必要があります。

今回のサンプルでいえば、VLOOKUP関数側の表である「検索」シートと、VLOOKUP関数の引数「範囲」で参照している表である「名簿」シートのA~E列の列は完全一致していますね。

この場合は、今回のサンプルのように引数なしのCOLUMN関数をVLOOKUP関数に入れればOKです。

しかし、もし2つの表の列がずれている場合は、以下のようにずれる列数分、COLUMN関数の戻り値(返り値)に対して”-1”などの調整が必要です。

COLUMN関数との組合せテクニックはMATCH関数との組合せテクニックと異なり、列がずれるとVLOOKUP関数の戻り値(返り値)もずれますので、参照している範囲への列の追加・削除にはご注意くださいね。

【POINT3】絶対参照・相対参照の設定

これは、数式を扱う上で基礎中の基礎ですが、コピペを一括で行うために必要です。

今回セル参照しているのはVLOOKUP関数の2つの引数ですが、それぞれ以下の意図があって絶対参照・相対参照を設定しています。

すべて「検索」シートのB2セルをもとに説明していきますね。

VLOOKUP関数-引数「検索値」

今回は「$A2」と列のみ絶対参照にしています。

これは、A列にVLOOKUP関数のキーワードとなる”出席番号”があり、B~E列に入れるVLOOKUP関数の「検索値」は同じ行なら一緒なため、列のみ固定にしているわけです。

VLOOKUP関数-引数「範囲」

今回は「名簿!$A:$E」と行列ともに絶対参照にしています。

これは、どのVLOOKUP関数も参照する範囲は「名簿」シートのA~E列のため、行列どちらも固定にしているわけです。

絶対参照・相対参照のまとめ

上記の説明は実際にワークシート上にさらに複数行のVLOOKUP関数が増えるとわかりやすいです。

さいごに

COLUMN関数は単独で使う分には設定は簡単ですが、使いどころが難しいマイナーな関数です。

しかし、VLOOKUP関数の引数「範囲」で参照している表のデータを列方向に連続で参照したい場合に、上記のようにVLOOKUP関数と組み合わせて使うと有効なテクニックとなりますね。

その他、VLOOKUP関数関連のテクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。

ご参考になれば幸いですm(_ _)m

森田森田

COLUMN関数はなかなかマニアックな関数です。私もVLOOKUP関数を複数列にコピペする方法を探しまくって見つけたくらいですし。
しかし、実はVBAを行なうようになると、列番号を考えてプログラムを組むことが増えるので、ゆくゆく「マクロ・VBAを学びたい!」という人は、COLUMN関数で概念的に慣れておくのは地味におすすめですよ。

この記事が気に入ったら
いいね ! しよう