【動画付き #20】VLOOKUP関数にCOLUMN関数を組み込んで列番号を自動で算出!ワンランク上の列番号の算出の仕方をマスターしましょう

この記事を読んでわかること
  • COLUMN関数がどのような関数かわかる
  • COLUMN関数で活用するひと工夫
  • COLUMN関数をVLOOKUP関数に組み込む方法

VLOOKUP関数はビジネスの現場では頻出の便利な関数ですが、一方で引数が多くてややこしくなることも多いです。数式をコピーしたり、列を誤って削除してしまったときなど、どこを修正していいかわからなくなることもあるでしょう。

そんなときに使いたいのが、VLOOKUPの第三引数にCOLUMN関数を組み込むことです。

今回紹介するCOLUMN関数を学ぶことで、VLOOKUP関数の第三引数である列が削除されても、エラーにならず、自動的に列番号を変えることができるようになります。VLOOKUP関数を用いた業務をさらに効率的にしていきましょう。

本記事はYouTube『ユースフル / スキルの図書館』チャンネルより、以下の動画を記事をまとめたものです。動画だけではなく、テキストで復習したい方もいるはず。全38の記事と動画を組み合わせて、Excelの基本操作をマスターしていきましょう。

▼前回の記事では、Excelのテーブル機能について解説しています。

目次

COLUMN関数とは?使い方を紹介

COLUMN関数は、引数に指定したセルの列番号を調べる関数です。

COLUMN関数の書式は「=COLUMN([参照])」です。引数は1つだけであり、列番号を調べたいセルか範囲を指定します。COLUMN関数のみで用いることは少なく、VLOOKUP関数やINDEX関数などに組み込んでよく活用されます。

そうすることで列番号をCOLUMN関数で表すことができ、VLOOKUP関数やINDEX関数を適切にコピーできるようになります。

COLUMN関数の構造
  • 書式:「=COLUMN([参照])」
  • 引数は1つだけであり、列番号を調べたいセルか範囲を[参照]に指定する
COLUMN関数の書式や引数を説明する画像

実際にCOLUMN関数をセルに入力してみましょう。下記のように参照するセルを指定せず、「=COLUMN()」と入力してみます。

Excel#20の1

すると、下記のように「2」という値が表示されます。

Excel#20の2

このようにCOLUMN関数の引数に何も指定しないと、COLUMN関数を入力したセルが何列目にあるかを表示します。

COLUMN関数をコピーすると、下記のように自動的に列番号を振ることができます。また、列が削除されても列番号が飛ぶことなく、自動的に連番に変えることができるようになります。

Excel#20の3

「B2」のセルに「=COLUMN(B2)」と入力しても、上記と同様に「2」と表示されます。

COLUMN関数で活用するひと工夫

COLUMN関数を用いて「B2」のセルに「1」と表示するには工夫が必要です。

Excel#20の4

上記のように「COLUMN(B$2)-COLUMN($A$2)」とCOLUMN関数のみを用いた数式を活用します。固定値を入力しないのでコピーしたとき、下記のように「1」から番号を振ることができるようになります。

Excel#20の5

COLUMN関数は単独で使うことは少なく、VLOOKUP関数やINDEX関数などに組み込んで使うことが非常に多い関数です。

ほかの関数に組み込んで活用するときに、「COLUMN(B$2)-COLUMN($A$2)」のようなCOLUMN関数のみを用いた数式を活用するのでぜひ覚えましょう。

COLUMN関数をVLOOKUP関数に組み込む方法

VLOOKUP関数の列番号を固定値で入力してしまうとどのような問題が生じるのでしょうか。

たとえば、「チームの連絡先」の表を「B9:F14」の表にVLOOKUP関数の列番号を固定値で入力して検索してみましょう。

Excel#20の6

「=VLOOKUP($B$10,$B$3:$F$7,2,0)」と列番号を「2」と固定値で入力して、「大野」を検索します。このとき、「C10 」のセルをコピーすると、すべて「大野」と表示されてしまいます。

Excel#20の7

そこで、COLUMN関数を用いて列番号を連番で入力することで、上記のような事態を防ぎます。COLUMN関数をVLOOKUP関数に組み込む手順を見ていきましょう。

COLUMN関数をVLOOKUP関数に組み込む手順
  1. 検索値、範囲は「=VLOOKUP($B10,$B$3:$F$7)」と入力する
    (検索値で「行」は動かせるように複合参照にする)
  2. 第三引数である列番号「COLUMN(C$2)-COLUMN($A$2)」と入力する
  3. 検索方法は「0」と入力する
  4. 「=VLOOKUP($B10,$B$3:$F$7,COLUMN(C$2)-COLUMN($A$2),0)」と入力できていれば完了
Excel#20の8
4.「=VLOOKUP($B10,$B$3:$F$7,COLUMN(C$2)-COLUMN($A$2),0)」と入力できていれば完了

これで「C10 」のセルをコピーすると、下記のようにすべてVLOOKUP関数を用いて表示できます。

Excel#20の9

まとめとおすすめ講座の紹介

今回のポイントは以下の2点です。

  • VLOOKUP関数の列番号に固定値を入力すると適切にコピペできないので、COLUMN関数を組み込む
  • COLUMN関数を組み込むことで、列が削除されてもVLOOKUP関数が壊れなくなる

上記2点のポイントを抑え、COLUMN関数をVLOOKUP関数に組み込んでいきましょう。

▼次回はVLOOKUP関数の近似一致について紹介します。

気に入ったらシェア!
  • URLをコピーしました!
目次