VLOOKUP関数の列番号を自動変更にする方法｜横にコピーで
ここでは、エクセルのVLOOKUP関数の「列番号を自動変更にする方法」を解説します。
VLOOKUP関数を使った数式を横にコピーする時に便利で、コピーする数が多ければ多いほど効果を発揮します。
１．まずはやりたいことを確認
まずは、やりたいことを例を含めて確認します。
１－１．列番号を自動変更にしたい
やりたいことは、「VLOOKUP関数の列番号を自動変更にする」です。
そもそもVLOOKUP関数の列番号は、「範囲のうちで抽出する列」を指定するところです。
ここを数値で固定してしまうと、VLOOKUP関数を用いた数式を横にコピーした時に「同じ列の値」を返してしまいます。
列番号を可変にすることで、元の表と同じ順番で項目ごとの値が表示されます。
１－２．例で確認
一つ例を見てみましょう。
上の左表は「住所録」です。
この表からナンバーを検索値にして、セルG3にVLOOKUP関数を用いた次の数式を入力しました。
=VLOOKUP($F3,$A$2:$D$5,2,FALSE)
検索値としてセルF3に「2」を入れた後、数式をセルH3とI3にコピーします。
すると、コピー先にも住所が入ります。これはコピーした数式の列番号が「2」と固定しているからです。
それでも一から数式を入れ直すのは面倒だからと、列番号を手で直すことがあるでしょう。これが「列番号変更」です。
しかし一つや二つくらいならいいですが、列がたくさんある場合、全て手で直すのはとても面倒です。
そこでそんな時には、ここで紹介する「数式をコピーするだけで列番号も自動で変わってくれる方法」を使うといいのです。
２．コピーで列番号が自動で変わる数式は
では、列番号を自動変更できる数式を見ていきます。
３－１．数式の形は
数式の形は次の通りです。
緑の「COLUMN(配列)」のところが普段と違います。
ちなみに、COLUMN関数は「列番号を返す」関数です。
上のようにカッコ内でセルA1を指定すると、A列は1番目の列なので「1」が返ってきます。
なお、COLUMN関数の使い方をもっと詳しく知りたい場合には、次のページを参照してください。
３－２．引数指定のポイント
列番号を自動変更する際の、各引数の指定のポイントを見ていきます。
３－２－１．検索値はセル番号なら絶対参照にする
検索値にセル番号を入れている場合には、「絶対参照」にします。理由は、「数式をコピーした際に参照先が変わらないようにするため」です。
例えば「A1」と入っている場合には、「$A$1」と$を2つ付けます。
反対に、条件値を式内に入れている場合には何も変える必要はありません。
３－２－２．範囲も絶対参照にする
範囲も絶対参照にします。理由は検索値と同じく、「参照先が変わらないようにするため」です。
例えば「A1:C10」と入っている場合には、「$A$1:$C$10」と$を4つ付けます。
３－２－３．配列には「抽出したい列の番目に対応したセル番号」を入れる
配列には、「範囲のうち抽出したい列の番目に対応したセル番号」を入れます。
「範囲のうち抽出したい列の番目」とは、抽出したいのは範囲のうち左から何番目の列か？ということです。例えば、範囲が「C1:F10」ならC列は1番目、D列は2番目、E列は3番目、F列は4番目です。
「対応したセル番号」とは、COLUMN関数が返す値が「抽出したい列の番目」になるようにする、ということです。
1番目を抽出したいならなら「A」、2番目なら「B」、3番目なら「C」となります。
なお、行番号はどんな値でもいいですので、ここでは「1」で統一します。
まとめると、抽出したいのが範囲のうち1列目なら「A1」を、2列目なら「C1」を、3列目なら「C1」を入れます。
３－２－４．検索方法はいつもと同じ
検索方法の指定の仕方は、いつもと変わりません。
３．数式の作り方
では、先ほどの例を用いて「列番号」の指定の仕方とその後を見てみましょう。
以下手順です。
（１）セルに「=VLOOKUP(」と入力し「fx」を押す
（２）検索値を入れ、セル番号の場合にはF4キーを1回押して絶対参照にする
（３）範囲を入れ、F4キーを1回押して絶対参照にする
（４）列番号に「COLUMN(」と入れ、対応したセル番号を入れた後「)」を入れる
※ 列番号には$は付けない
（５）検索方法を入れる
※ 多くの場合は完全一致の「FALSE」
（６）Enterキーを押す
（７）数式を横にコピーすれば完了
４．なぜ列番号が自動的に変更されるのか？
「配列も数式のコピーで変わるから」です。
先ほどの例では、セルG3に入れた数式の中で配列を「B1」と指定しました。
これは、範囲の中で左から2番目にある「住所」を抽出したかったからです。
ちなみに、COLUMN(B1)は「2」を返します。
一方、セルG3の数式を右隣のH3のコピーすると配列は「C1」に変わります。
「B1」に$を付けていないので、合わせて右隣に移るのです。
するとCOLUMN(C1)となり、「3」が返ってきます。
よって、「氏名」が抽出されます。
このように、コピーしたセルの位置に応じて配列の参照先も変わるので、結果手直しの必要がなくなるのです。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】
VLOOKUP関数のその他の記事はこちらです。
- VLOOKUP関数その他の記事はこちら
- ◎基本的な使い方
- ◎「以上/未満」の条件指定の仕方
- ◎〇番台の条件にする方法
- ◎部分一致の条件指定の仕方
- ◎別シートから値を抜き出す方法
- ◎別ブックから値を抜き出す方法
- ◎数式コピーで横方向の値を簡単に取得する方法
- ◎２番目、３番目以降の値を抽出する方法
- ◎＃N/Aエラーになる３つの原因と対処方法
- ◎＃REF!エラーになる原因と対処方法
- ◎＃VALUE!エラーになる原因と対処方法
- ◎空白を０ではなく空白で表示する２つの方法
- ◎「IF(ISERROR(VLOOKUP」の意味と使い方
- ◎IFERROR関数とVLOOKUPの組み合わせ方
- ◎空白でもエラーでも空白で表示する方法
- ◎複数条件にする方法
- ◎セルや範囲を固定しコピーのズレを防ぐ方法
- ◎文字列を条件にする方法
- ◎列番号の意味と指定の仕方