エクセル・VBA

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

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

今回の記事はExcel(エクセル)でVLOOKUPを使う時の列番号についてお話します。

列番号は、指定した範囲の左から数えて何列目のデータを取り出すのかを指定するときに使用しますが、挿入するたびに列番号を数えたり、確認したりするのは、なかなか手間です。

そこで今回は、列番号を数字で指定するのではなく、関数を使って自動で列番号を取得する方法について解説します。

ぜひ参考になさってください。

VLOOKUP関数に関する関連記事は下記をご覧ください。

Excel(エクセル)|VLOOKUPで完全一致の使い方

Excel(エクセル)|VLOOKUP関数で近似値の使い方

Excel(エクセル)|VLOOKUP関数とIF関数を使って複数条件を設定する方法

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

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関数で別シートを参照する方法

そのほかのお役立ち情報はこちら

Excel(エクセル)|スクロールできない時の対処法を解説

Excel(エクセル)|データの並べ替えをする方法・複数条件の指定

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

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

ココがおすすめ

パソコン教室へのお問い合わせはこちらから

お問い合わせはこちら

    【 お名前 *必須

    【 携帯電話 *必須

     
    【 メールアドレス 】

    【 ご希望レッスン 】
    実店舗レッスンオンラインレッスン企業研修パソコン修理依頼

    【 都道府県名 & 市区町村名 &(あれば区名) 】

    【 ご質問などあればご記入ください 】

    お問い合わせいただきありがとうございます。送信ボタンを押した後、下に「内容を送信しました。ありがとうございます。」とでれば送信完了です。基本的には「090-9503-8923」の電話番号にておかけします。教室の内容についてご質問などあればお伺いできればと思います。

    お問合せリンク画像

    ココがおすすめ

    オンライン対応しております

    オンライン対応しております

    パソコン教室でのレッスン」も「Zoom(ズーム)」を利用した「オンラインレッスン」どちらも対応可能です。

    パレハのパソコンレッスンは、オンラインなのに「生で質問」出来るのが特徴です。

    詳しくは下記をご覧ください。

    オンラインパソコン教室|オンラインなのに生レッスン

    ココがおすすめ

    パソコン教室パレハ直営校

    パソコン教室パレハ直営校

    ■ パソコン教室パレハ広島市安佐南区緑井校

    ■ パソコン教室パレハ広島市中区本通り校

    ■ 広島市西区にあるパソコン教室パレハ横川校|マンツーマン教室

    ■ パソコン教室パレハ 静岡市駿河区南町校・静岡市葵区伝馬町校

    ■ オデッセイCBT・サーティファイ試験申込パソコン教室パレハ伝馬町校

    ココがおすすめ

    パソコン教室パレハグループ加盟校

    自信をもってご紹介できるパソコン教室

    下記はパレハグループ加盟校となります。

    料金体制などは異なりますが、パソコン教室パレハが自信をもってご紹介できるパソコン教室です。

    ■ 岩出市のパソコン教室キュリオステーション

    ■ 岩出市にあるパソコン教室 「キュリオステーション 岩出店」の公式サイトはこちら

    ■ 名古屋市緑区にある有松パソコン教室

    ■ 愛知県名古屋市緑区にあるパソコン教室 「有松パソコン教室」の公式サイトはこちら

    ■ 福岡市パソコン教室平尾校

    ■ 福岡県福岡市中央区にある 「福岡市パソコン教室平尾校」の公式サイトはこちら

    ■ 調布市にあるパソコン教室キュリオステーションつつじヶ丘店

    ■ 調布市にあるパソコン教室「キュリオステーションつつじヶ丘店」の公式サイトはこちら

    ■ 宮崎市にあるパソコン教室Cosmarp(コスマープ)

    ■ 宮崎市にあるパソコン教室Cosmarp(コスマープ)の公式サイトはこちら

    全国加盟校一覧

    全国のパソコン教室一覧(実店舗&オンライン)|パレハ直営校&グループ加盟校

    ココがおすすめ

    オンライン形式をご希望の方はこちら

    オンライン形式をご希望の方はこちら

    東京・名古屋・大阪・福岡・札幌・横浜・仙台・埼玉・千葉・沖縄などで受講したい方はオンラインレッスン(Zoomを使ったオンライン個別レッスンです)をお選びください。

    オンラインパソコン教室|オンラインなのに生レッスン

    ココがおすすめ

    パソコン教室講座一覧はこちら

    パソコン教室講座一覧はこちら

    他にもパソコンの講座は多数ございます。

    詳しくは下記をご覧ください。

    パソコン教室の受講コース案内(講座のご紹介)

    ココがおすすめ

    パソコン教室に関連する情報はこちら

    こちらも是非ご覧ください

    ココがおすすめ

    エクセルの操作方法

    Excel(エクセル)|重複チェック|削除・抽出方法を徹底解説

    エクセル(Excel)の罫線を消す方法を解説

    Excel(エクセル)|行列(縦横)入れ替える方法|動画

    Excel(エクセル)でセル内で改行する方法|できない時の対処法

    エクセルで割り算|四捨五入・切り捨て・切り上げをする方法|動画解説

    Excel(エクセル)にパスワードを設定(解除)する方法

    Excel(エクセル)|MID関数で真ん中の文字を抽出

    Excel(エクセル)|曜日を日付から取得して色を自動で変える方法

    エクセル|勝手に日付になるのを修正する方法|動画解説付き

    Excel(エクセル)で全角を半角に変換して修正する方法|ASC関数

    Excel(エクセル)シートのコピー方法 | ショートカット&エラー対処法

    エクセル|文字数をカウントするLEN関数の使い方

    Excel(エクセル)|文字の縦書き・横書きの設定・解除方法

    Excel(エクセル)ファイルをPDFに変換する方法をわかりやすく解説

    Excel(エクセル)|名前の定義の設定方法と使い方を解説

    Excel(エクセル)を勉強する方法|効率よい方法教えます

    メールの画像

    メールはこちら

    ラインの画像

    LINEはこちら

    電話の画像

    お電話はこちら