エクセル・VBA

Excel(エクセル)|VLOOKUPで自動で列番号を取得する方法とは?

Excel(エクセル)|VLOOKUPの列番号とは?

今回の記事はExcel(エクセル)|VLOOKUPの列番号とは?についてお話します。

苦手な方専門のパソコン教室

VLOOKUPの列番号 画像解説

Excel(エクセル)|VLOOKUPの列番号とは?

Excel(エクセル)|VLOOKUPの列番号とは?

Excel(エクセル)|VLOOKUPの列番号とは?

Excel(エクセル)|VLOOKUPの列番号とは?

Excel(エクセル)|VLOOKUPの列番号とは?

Excel(エクセル)|VLOOKUPの列番号とは?

Excel(エクセル)|VLOOKUPの列番号とは?

Excel(エクセル)|VLOOKUPの列番号とは?

そもそも列番号って何?

VLOOKUP関数の構文は以下の通りです。

=VLOOKUP(検索値, 範囲, 列番号, 検索の型)

検索値何に対応するものを探したいのか、どのデータを使って検索するのか
範囲どの範囲から検索してほしいのか
列番号指定した範囲の左から何列目にあるものを検索するのか
検索の型ピッタリ同じものを探すのか、近いものを探すのか

列番号とは、参照した範囲の左側から何列目のデータを取り出すのかを指定するもので、通常は「2」「3」「4」などの数字を入力します。

VLOOKUP関数の列番号の説明

数式をコピーしたら、列番号は修正が必要

VLOOKUP関数の列番号の説明

挿入したVLOOKUP関数を横方向にドラッグしてコピーした場合について、解説します。

※今回の例では、検索値を複合参照、範囲を絶対参照になっています。

 

VLOOKUP関数の列番号の説明

通常ドラッグして横方向にコピーすると、列番号は直接入力しているためコピー元で入力した列番号がそのまま表示されます。

すると、常に同じ結果が表示されます。

 

VLOOKUP関数の列番号の説明

解決方法は簡単で、コピー先の列番号を抜き出したいデータがある列番号に変更するだけです。

上記の例では、列番号を「2」から「3」に変更することで、正しい結果が表示されています。

列番号を指定せず自動で取得する方法

VLOOKUP関数を挿入する列が多いほど、修正は手間になりますし、挿入や修正をするたびに左端から何列目なのかを数えるのも効率が悪いです。

そこで、ここからは列番号を数値で直接指定せず、他の関数を使用して相対的な位置を返し、自動で列番号を取得する方法について紹介します。

方法① COLUMNS関数を使用する

まず、1つ目の方法は、COLUMNS関数を使用する方法です。

COLUMNS関数を使うことで、列の数を数えることができます。

COLUMNS関数の使用が向いているのは、抜き出したいデータが「2,3,4...」と続いている場合です。

下記の例で説明します。

VLOOKUP関数の列番号の説明

VLOOKUP関数の範囲まで入力しましょう。

 

VLOOKUP関数の列番号の説明

列番号の所にCOLUMNS関数を挿入します。

 

VLOOKUP関数の列番号の説明

範囲で指定したマスタ表の左上のセルを開始位置として、列番号の数だけ範囲選択します。

 

VLOOKUP関数の列番号の説明

範囲指定した開始位置のセルだけ、絶対参照で固定します。

 

VLOOKUP関数の列番号の説明

COLUMNS関数をカッコで閉じて、半角カンマ、検索方法を入れたらEnterキーを押して関数を確定しましょう。

 

VLOOKUP関数の列番号の説明

マスタ表から2列目を検索した結果が表示されます。

 

VLOOKUP関数の列番号の説明

隣のセルに数式をコピーします。

 

VLOOKUP関数の列番号の説明

マスタ表から3列目を検索した結果が表示されます。

 

VLOOKUP関数の列番号の説明

COLUMNS関数で指定した範囲で、後部の参照だけ変わるので、自動で連続した列番号を参照することができます。

 

VLOOKUP関数の列番号の説明

次に、下方向にドラッグして数式をコピーしましょう。

 

VLOOKUP関数の列番号の説明

すべてのセルに正しい結果が表示されます。

 

VLOOKUP関数の列番号の説明

下方向にコピーすると、COLUMNS関数で参照した範囲も広がりますが、COLUMNS関数では列の数しか結果を返さないので、問題ありません。

 

VLOOKUP関数の列番号の説明

参照が広がって気になる場合は、COLUMNS関数で指定した範囲の終わりのセルを行固定すれば、横方向のみ参照が広がります。

COLUMNS関数の組み合わせが向いている例

VLOOKUP関数の列番号の説明

COLUMNS関数を組み合わせる場合は、結果を表示させたい列の並びが、マスタ表と全く同じように連続している場合です。

 

COLUMNS関数の組み合わせが向いていない例

VLOOKUP関数の列番号の説明

結果を表示させたいセルが、連続していない場合は正しい結果を返すことができません。

 

方法② MATCH関数を使用する

次に紹介するのは、MATCH関数を使用する方法です。

MATCH関数を使うことで、表の中から相対的な列番号を取得することができます。

MATCH関数では、2列目、5列目、6列目など取得したい列が続いていなくても、自由に取得することができます。

下記の例で説明します。

VLOOKUP関数の列番号の説明

VLOOKUP関数を挿入し、範囲まで入力します。

 

VLOOKUP関数の列番号の説明

列番号の所に、MATCH関数を挿入しましょう。

 

VLOOKUP関数の列番号の説明

MATCH関数の検索値を選択します。

コピーしたときに参照がズレないように行固定をしましょう。

固定できたら、続けて半角カンマを入力します。

 

VLOOKUP関数の列番号の説明

続いて、範囲を選択します。

今回は、マスタ表の見出しを選択して、参照がズレないように絶対参照で固定します。

固定ができたら半角カンマを入力します。

 

VLOOKUP関数の列番号の説明

MATCH関数の検索方法に完全一致の「0」を入力し、MATCH関数をカッコで閉じます。

 

VLOOKUP関数の列番号の説明

続いて、VLOOKUP関数の検索方法に完全一致の「0」を入力し、関数を確定させます。

 

VLOOKUP関数の列番号の説明

すると、MATCH関数で指定した範囲の中から、同じ見出しを見つけて、対応するデータを取り出すことができます。

 

VLOOKUP関数の列番号の説明

数式を隣のセルにコピーしてみましょう。

 

VLOOKUP関数の列番号の説明

隣のセルにコピーすると、MATCH関数で検索する条件が「単価」に変わり、それと同じ見出しをマスタ表から探し、列番号を返すことができるので、修正なしで正しい結果を表示できます。

 

VLOOKUP関数の列番号の説明

下のセルにも数式をコピーしたら完成です。

 

VLOOKUP関数の列番号の説明

たとえ、求めたい内容が変わって、見出しを変更したとしても相対的な位置を返してくれるので、対応した結果を表示してくれます。

 

MATCH関数の方が万能です

VLOOKUP関数の列番号の説明 VLOOKUP関数の列番号の説明

MATCH関数を使えば、COLUMNS関数同様に連続した列番号を取得することができます。

 

VLOOKUP関数の列番号の説明

VLOOKUP関数の列番号の説明

COLUMNS関数では連続していない列番号を取得することは無理でしたが、MATCH関数であれば相対的な位置を検索できるので、正しく結果を表示できます。

 

さらにワンランク上の効率を求めるなら

今回紹介した例では、マスタ表は通常の表を使用しました。

さらに効率の良いVLOOKUP関数を挿入するなら、マスタ表をテーブルに変換しておくのがオススメです。

テーブルにしておけば、たとえ、マスタ表に行や列が追加された場合でも自動で範囲が拡張されるので、非常に便利です。

  1. マスタ表をテーブルにする
  2. IF関数を使って空白のエラー処理をしておく
  3. 列番号でMATCH関数使用する

この3つのポイントを押さえてVLOOKUP関数を挿入すれば、きれいで効率の良い表が完成します。

Excel(エクセル)|VLOOKUP関数とは?|使い方を徹底解説

Excel(エクセル)|VLOOKUP関数でエラーを返さず空白で返す方法

Excel(エクセル)|VLOOKUPの検索値を徹底解説

Excel(エクセル)|VLOOKUPで範囲の選び方を徹底解説

Excel(エクセル)|VLOOKUPでテーブルを参照する方法

Excel(エクセル)|VLOOKUP関数で別シートを参照する方法

苦手な方専門のパソコン教室

パソコン教室パレハのライン登録

メールの画像

メールはこちら

ラインの画像

LINEはこちら

電話の画像

お電話はこちら