今回の記事はExcel(エクセル)でVLOOKUPを使う時の列番号についてお話します。
列番号は、指定した範囲の左から数えて何列目のデータを取り出すのかを指定するときに使用しますが、挿入するたびに列番号を数えたり、確認したりするのは、なかなか手間です。
そこで今回は、列番号を数字で指定するのではなく、関数を使って自動で列番号を取得する方法について解説します。
ぜひ参考になさってください。
VLOOKUP関数に関する関連記事は下記をご覧ください。
Excel(エクセル)｜VLOOKUP関数とIF関数を使って複数条件を設定する方法
ExcelのVLOOKUPで自動で列番号を取得するのって難しい…
そんなお悩み、当教室のレッスンで一気に解決できます。
✔ 基本操作からしっかり学べる
✔ 「見てマネするだけ」で自然と覚えられる
✔ わからないところはすぐ質問できるから安心
文字を読むのが苦手でも大丈夫。
「実践重視のレッスン」＋「元パソコンが苦手だった講師のサポート」で、着実に「感覚としてExcelに慣れていく」ようになります。
詳細は下記をご覧ください。
VLOOKUPの列番号 画像解説
Excel（エクセル）の操作が苦手な方のために、操作する上でのコツをまとめたページを用意しました。
「パソコン教室パレハのLINE友達登録」（無料）で「パソコンに役立つ豪華15大特典」をお送りしております。
Excel（エクセル）の操作はもちろん、パソコンが苦手な方に役立つ特典を15個ご用意しております。
良かったら下記からご登録ください。
そもそも列番号って何？
VLOOKUP関数の構文は以下の通りです。
=VLOOKUP（検索値, 範囲, 列番号, 検索の型）
| 検索値 | 何に対応するものを探したいのか、どのデータを使って検索するのか |
| 範囲 | どの範囲から検索してほしいのか |
| 列番号 | 指定した範囲の左から何列目にあるものを検索するのか |
| 検索の型 | ピッタリ同じものを探すのか、近いものを探すのか |
列番号とは、参照した範囲の左側から何列目のデータを取り出すのかを指定するもので、通常は「2」「3」「4」などの数字を入力します。
数式をコピーしたら、列番号は修正が必要
挿入したVLOOKUP関数を横方向にドラッグしてコピーした場合について、解説します。
※今回の例では、検索値を複合参照、範囲を絶対参照になっています。
通常ドラッグして横方向にコピーすると、列番号は直接入力しているためコピー元で入力した列番号がそのまま表示されます。
すると、常に同じ結果が表示されます。
解決方法は簡単で、コピー先の列番号を抜き出したいデータがある列番号に変更するだけです。
上記の例では、列番号を「2」から「3」に変更することで、正しい結果が表示されています。
ExcelのVLOOKUPで自動で列番号を取得するのって難しい…
そんなお悩み、当教室のレッスンで一気に解決できます。
✔ 基本操作からしっかり学べる
✔ 「見てマネするだけ」で自然と覚えられる
✔ わからないところはすぐ質問できるから安心
文字を読むのが苦手でも大丈夫。
「実践重視のレッスン」＋「元パソコンが苦手だった講師のサポート」で、着実に「感覚としてExcelに慣れていく」ようになります。
詳細は下記をご覧ください。
列番号を指定せず自動で取得する方法
VLOOKUP関数を挿入する列が多いほど、修正は手間になりますし、挿入や修正をするたびに左端から何列目なのかを数えるのも効率が悪いです。
そこで、ここからは列番号を数値で直接指定せず、他の関数を使用して相対的な位置を返し、自動で列番号を取得する方法について紹介します。
方法① COLUMNS関数を使用する
まず、1つ目の方法は、COLUMNS関数を使用する方法です。
COLUMNS関数を使うことで、列の数を数えることができます。
COLUMNS関数の使用が向いているのは、抜き出したいデータが「2，3，4...」と続いている場合です。
下記の例で説明します。
VLOOKUP関数の範囲まで入力しましょう。
列番号の所にCOLUMNS関数を挿入します。
範囲で指定したマスタ表の左上のセルを開始位置として、列番号の数だけ範囲選択します。
範囲指定した開始位置のセルだけ、絶対参照で固定します。
COLUMNS関数をカッコで閉じて、半角カンマ、検索方法を入れたらEnterキーを押して関数を確定しましょう。
マスタ表から2列目を検索した結果が表示されます。
隣のセルに数式をコピーします。
マスタ表から3列目を検索した結果が表示されます。
COLUMNS関数で指定した範囲で、後部の参照だけ変わるので、自動で連続した列番号を参照することができます。
次に、下方向にドラッグして数式をコピーしましょう。
すべてのセルに正しい結果が表示されます。
下方向にコピーすると、COLUMNS関数で参照した範囲も広がりますが、COLUMNS関数では列の数しか結果を返さないので、問題ありません。
参照が広がって気になる場合は、COLUMNS関数で指定した範囲の終わりのセルを行固定すれば、横方向のみ参照が広がります。
COLUMNS関数の組み合わせが向いている例
COLUMNS関数を組み合わせる場合は、結果を表示させたい列の並びが、マスタ表と全く同じように連続している場合です。
COLUMNS関数の組み合わせが向いていない例
結果を表示させたいセルが、連続していない場合は正しい結果を返すことができません。
教室に通う時間がなくてちょっとずつ覚えたいという方向けの「パソコン苦手さん向け通信講座」を作りました。
基本から順番に「実践で覚えるのではなく慣れていく」教材を使います。
「文字を読むのが苦手…」という方でも大丈夫。
動画とセットで感覚的に身につけられます。
方法② MATCH関数を使用する
次に紹介するのは、MATCH関数を使用する方法です。
MATCH関数を使うことで、表の中から相対的な列番号を取得することができます。
MATCH関数では、2列目、5列目、6列目など取得したい列が続いていなくても、自由に取得することができます。
下記の例で説明します。
VLOOKUP関数を挿入し、範囲まで入力します。
列番号の所に、MATCH関数を挿入しましょう。
MATCH関数の検索値を選択します。
コピーしたときに参照がズレないように行固定をしましょう。
固定できたら、続けて半角カンマを入力します。
続いて、範囲を選択します。
今回は、マスタ表の見出しを選択して、参照がズレないように絶対参照で固定します。
固定ができたら半角カンマを入力します。
MATCH関数の検索方法に完全一致の「0」を入力し、MATCH関数をカッコで閉じます。
続いて、VLOOKUP関数の検索方法に完全一致の「0」を入力し、関数を確定させます。
すると、MATCH関数で指定した範囲の中から、同じ見出しを見つけて、対応するデータを取り出すことができます。
数式を隣のセルにコピーしてみましょう。
隣のセルにコピーすると、MATCH関数で検索する条件が「単価」に変わり、それと同じ見出しをマスタ表から探し、列番号を返すことができるので、修正なしで正しい結果を表示できます。
下のセルにも数式をコピーしたら完成です。
たとえ、求めたい内容が変わって、見出しを変更したとしても相対的な位置を返してくれるので、対応した結果を表示してくれます。
MATCH関数の方が万能です
MATCH関数を使えば、COLUMNS関数同様に連続した列番号を取得することができます。
COLUMNS関数では連続していない列番号を取得することは無理でしたが、MATCH関数であれば相対的な位置を検索できるので、正しく結果を表示できます。
ワンランク上の効率を求めるなら
今回紹介した例では、マスタ表は通常の表を使用しました。
さらに効率の良いVLOOKUP関数を挿入するなら、マスタ表をテーブルに変換しておくのがオススメです。
テーブルにしておけば、たとえ、マスタ表に行や列が追加された場合でも自動で範囲が拡張されるので、非常に便利です。
- マスタ表をテーブルにする
- IF関数を使って空白のエラー処理をしておく
- 列番号でMATCH関数使用する
この３つのポイントを押さえてVLOOKUP関数を挿入すれば、きれいで効率の良い表が完成します。
Excel(エクセル)｜VLOOKUP関数とは？｜使い方を徹底解説
Excel（エクセル）が苦手であればパソコン教室で学んでみませんか？
Excel（エクセル）はかなり奥が深く、知識を得ただけは操作が身に付きにくいものです。
そんな時には、弊社のようなパソコン教室を利用するのも一つの手です。
「苦手な方専門のパソコン教室パレハ」のレッスンは、動画ではなく、直接講師に質問できる生レッスンです。
生レッスンでありながらリーズナブルに学べるのが人気の秘密です。
また、苦手な方に特化しているのでパソコンが特に苦手だと言う方には最適です。
パソコンが苦手な方でも苦労なく上達できる仕組みがあります。
詳細は下記をご覧ください。
教室に通う時間がなくてちょっとずつ覚えたいという方向けの「パソコン苦手さん向け通信講座」を作りました。
基本から順番に「実践で覚えるのではなく慣れていく」教材を使います。
「文字を読むのが苦手…」という方でも大丈夫。
動画とセットで感覚的に身につけられます。
Excel（エクセル）の操作が苦手な方のために、操作する上でのコツをまとめたページを用意しました。
「パソコン教室パレハのLINE友達登録」（無料）で「パソコンに役立つ豪華15大特典」をお送りしております。
Excel（エクセル）の操作はもちろん、パソコンが苦手な方に役立つ特典を15個ご用意しております。
良かったら下記からご登録ください。
こちらもご覧ください
エクセル操作情報
Excel(エクセル)｜データの並べ替えをする方法・複数条件の指定
Excel(エクセル)｜重複チェック｜削除・抽出方法を徹底解説
Excel(エクセル)｜シート保護とパスワード設定方法を徹底解説
Excel(エクセル) | ウィンドウ枠(列や行)の固定や解除方法 | 複数選択
Excel(エクセル)でセル内で改行する方法｜できない時の対処法
エクセルで割り算｜四捨五入・切り捨て・切り上げをする方法｜動画解説
Excel(エクセル)｜曜日を日付から取得して色を自動で変える方法
Excel(エクセル)で全角を半角に変換して修正する方法｜ASC関数
エクセルの印刷範囲｜A4サイズに収める方法や設定テクニックを紹介
Excel(エクセル)シートのコピー方法 | ショートカット&エラー対処法
Excel（エクセル）｜文字の縦書き・横書きの設定・解除方法
Excel(エクセル)ファイルをPDFに変換する方法をわかりやすく解説
Excel(エクセル)｜半角のデータを全角に一括変換できるJIS関数
Excel(エクセル)｜日付を入れて曜日を自動で出す｜表示形式の設定方法
Excel(エクセル)｜TEXT関数｜日付から曜日を表示する方法
Excel(エクセル)で掛け算する方法｜列ごとの方法｜初心者向け｜動画付
Excel(エクセル)｜チェックボックス(レ点)の作成や削除する方法
Excel(エクセル) | コメントを表示して印刷する方法を解説
Excel(エクセル)でハイパーリンク (URL)の貼り付けを設定する方法
Excel(エクセル)｜入力規則を利用しデータを効率よく入れる方法を徹底解説
エクセル｜検索・置き換えのやり方｜複数の文字や数字の置換方法
Excel(エクセル)でヘッダー・フッター(ページ番号)を作成・編集方法
Excel(エクセル)で文字列の不要なスペースを削除する方法
Excel(エクセル) | 取り消し線の付け方(ショートカット)を解説
Excel(エクセル)で印刷範囲を設定する方法｜一部だけ・1ページにぴったりなど解説
Excel(エクセル)｜表の作り方の手順を徹底解説｜初心者向け
Excelで文字列を"&"や関数で結合する方法｜空白や記号の追加
Excel(エクセル)で時間を計算する方法｜経過・合計・24h以上の表示
Excel(エクセル)で四捨五入する方法｜ROUND関数の基本から応用まで
Excel(エクセル)｜ROUNDDOWN関数で切り捨てする方法｜基本から応用
エクセル｜斜線を引く方法と消す方法｜分割したセルに文字を入力する裏技
Excel(エクセル)｜PHONETIC関数でふりがなを表示する方法｜修正・エラー原因
Excel(エクセル)で日付を自動で表示・更新する方法｜役立つ関数6選
Excel(エクセル)でリンクを解除する方法｜ショートカットキーも紹介
Excel(エクセル)で文字列を抽出できる関数と関数の組み合わせ方法
Excel(エクセル)の「スピル」機能って何？使い方とエラーの解決方法
エクセルで電子印鑑を作成・使用する方法｜メリットとデメリット
Excel(エクセル)で平均を求める3つの方法｜計算式なしの裏技も紹介
Excel(エクセル)でシート移動ができるショートカット｜時短ワザ
Excel(エクセル)｜基本・条件指定・計算式の裏ワザで合計を出す方法
Excel(エクセル)計算式や関数の入力方法｜基本から応用まで
Excel(エクセル)のフィルターの設定方法｜使い方の基本から解説
Excel(エクセル)で絶対値を表示する方法｜ABS関数とその他2つの方法
Excel マクロ｜組み方・使い方・ボタンの作り方まで徹底解説
でVLOOKUPを使う時の列番号についてお話します。 列番号は、指定した範囲の左から数えて何列目のデータを取り出すのかを指定するときに使用しますが、挿入するたびに列番){kind=link}