logologo
VLOOKUP関数の絶対参照とは?その使い方や注意点を図解
thumb_vlookupzettai_01
VLOOKUP関数の絶対参照とは?その使い方や注意点を図解
アンドエンジニア編集部
2024.09.29
この記事でわかること
VLOOKUP関数を使いこなす上で、絶対参照と相対参照について理解しておくことが重要
VLOOKUP関数で検索範囲を固定したい場合、セルの頭に「$」を付ける絶対参照を使う
VLOOKUP関数による絶対参照では、参照するマスターを別シートで作成すると効率が良い

【関連記事】VLOOKUPとは?使い方や活用方法をわかりやすく解説

【関連記事】Excel(エクセル)の関数30選!よく使う基本の関数を一覧で紹介|CANVAS

VLOOKUP関数の絶対参照とは?

img_vlookupzettai_01

エクセル(Excel)の表データ処理では大変便利なVLOOKUP関数ですが、扱いを間違えると想定外の結果を招き、うまくいかない場合があるため注意が必要です。この関数を使う上では、「絶対参照」という概念を理解することが重要です。

絶対参照を適切に使うことで、より柔軟かつ正確なデータ分析が可能になります。この記事では、VLOOKUP関数の絶対参照と相対参照の違い、絶対参照の使い方、応用例やエラーが出た時の対処法などについて解説します。

VLOOKUPが苦手な方やVLOOKUPを習得したい方は、ぜひ参考にしてください。

【参考】:VLOOKUP 関数 | Microsoft サポート 【参考】:Excel VLOOKUP とは? 関数の使い方を徹底解説 | Microsoft for business

VLOOKUPとは?使い方や活用方法をわかりやすく解説

VLOOKUP関数の基本

img_vlookupzettai_02

VLOOKUP関数について、改めて基本を確認しておきましょう。VLOOKUP関数はExcel操作で最もよく利用される関数の1つであり、表を縦方向に検索したり、特定の値に対応する値を取り出したりする機能を持った関数です。

VLOOKUPは「Vertical(垂直)」の頭文字の「V」と、探すという意味の「LOOKUP」を組み合わせた関数です。例えば、商品コードから商品の単価などを取得したり、顧客番号から性別や住所を取得したりといった操作に役立ちます。VLOOKUP関数の基本的な構文は以下の通りです。

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

VLOOKUP関数の引数

VLOOKUP関数を使う上で、引数の理解は非常に重要です。引数とは、関数に与える情報、いわば関数の「材料」のようなものです。VLOOKUP関数には、大きく分けて4つの引数があります。

▪検索値 検索値は探し出したい値を指定します。例えば、商品名や社員番号など、検索の基準となる値です。

▪範囲 検索範囲を指定します。検索値が含まれる列が範囲の左端にある必要があります。また範囲を「絶対参照」にすると、数式をコピーしても検索範囲が固定されます。

▪列番号 結果を取得したい列の番号を指定します。範囲の最初の列が1列目になり、例えば、2列目の値を取得したい場合は「2」と入力します。

▪検索方法 TRUEまたはFALSEを指定します。完全一致を求める場合は「0」か「FALSE」にします。「1」か「TRUE」を指定すると、近似値で検索します。近似値で検索すると、戻り値は「検索値よりも小さい値の最大値」となります。

絶対参照と相対参照の違い

img_vlookupzettai_03

ExcelのVLOOKUP関数を使う上で、絶対参照と相対参照の違いを理解することは非常に重要です。これは、セル参照の仕方、つまり式の中で他のセルを参照する方法に違いがあることを意味します。

絶対参照とは

VLOOKUP関数で、検索範囲を固定したい場合、絶対参照を使います。絶対参照は式をコピーしても参照するセルが固定されるため、常に同じセルを参照し続けます。

相対参照とは

VLOOKUP関数で、検索範囲を動的に変化させたい場合に、相対参照を使います。相対参照は式をコピーする際に検索値も一緒に移動するため、さまざまなデータに対して同じ処理を簡単に適用できます。

絶対参照と相対参照を使い分ける必要性

VLOOKUP関数で、絶対参照はどのようなケースで使うのでしょうか?例えば、ある表から特定の値を常に探し出したい場合、検索範囲を絶対参照にしておくと、式をコピーしても範囲がズレてしまうのを防ぐことができます。このように式をコピーして再利用したい時には絶対参照にします。

一方、検索値となるセルは、式をコピーする度に変えた方が都合が良い場合があり、このような場合は相対参照を使います。

絶対参照と相対参照の書き方

では、相対参照と絶対参照はどのように区別するのでしょうか?

▪相対参照 セルのアドレスをそのまま記述します。例:A1

▪絶対参照 セルのアドレスの前に「$」を記述します。例:$A1

以上を一覧表に整理してみましたので、絶対参照のやり方で迷った時は参照してください。稀に「F4」を押しても「$」の表示がされず、絶対参照ができないケースがあります。それは、F4キーに他のショートカットキーが割り当てられている場合があるからです。

そうしたケースでは、「Fn」キーを押して、「F4」を押すと解決できます。

img_vlookupzettai_04
【図】:絶対参照と相対参照の付け方

絶対参照と相対参照の使用例

img_vlookupzettai_05

VLOOKUP関数の絶対参照について理解したところで、相対参照と絶対参照の実際の例を見ていきましょう。

【参考】:Excel VLOOKUP とは? 関数の使い方を徹底解説 - Microsoft for business

相対参照の例

「絶対参照」の設定を解説する前に、VLOOKUP関数でよく利用する「相対参照」の事例を紹介します。このケースは、F2の売上E2に入力された地域コード101で、A2からC9までのテーブルを参照し、該当する売上を求める処理です。

F2セルに入力されたVLOOKUP関数は『=VLOOKUP(E2,A1:C9,3,FALSE)』です。F2には売上が100と正しく入っています。以下、F2に入力された相対参照のVLOOKUP関数をF3からF7までコピーしましたが、F5からは全て#N/Aエラーとなっています。#N/Aエラーは検索値が見つからない場合に発生します。

img_vlookupzettai_06
【図】: VLOOKUP関数 相対参照の例 ①

なぜF2からF4では正しい数値を参照できているのに、F5からは参照できずにエラーとなったのでしょうか?F5のVLOOKUP関数の式を確認してみましょう。

F5セルに入力されたVLOOKUP関数は『=VLOOKUP(E2,A4:C12,3,FALSE)』です。参照範囲が下に4つズレており、参照範囲に102に該当するコードが見あたりません。参照範囲がズレたのは、相対参照の式を単純にコピーしたからです。

img_vlookupzettai_07
【図】: VLOOKUP関数 相対参照の例 ②

絶対参照の例

前述の相対参照では、VLOOKUP関数をそのまま下の列にコピーしたため、参照範囲がズレてしまいました。ここではVOOKUPを絶対参照で記述してみましょう。

F2セルには関数式『=VLOOKUP(E2,$A$1:$C$9,3,FALSE)』と絶対参照で入力されています。先程の相対参照と同様に、F2セルには正しい値が入力されています。この式をF7セルまでコピーしてみましょう。

img_vlookupzettai_08
【図】: VLOOKUP関数 絶対参照の例 ①

先程の相対参照では#N/AエラーとなっていたF5セルには正しい売上80が入力されています。VLOOKUP関数は『=VLOOKUP(E5,$A$1:$C$9,3,FALSE)』と参照範囲は変わっておらず、検索値のみがF2からF5へと移動しています。参照範囲が正しかったことで正しい売上を索引できました。

絶対参照は、セル参照の前に「$」を付けることで設定できることが理解できました。

img_vlookupzettai_09
【図】: VLOOKUP関数 絶対参照の例 ②

絶対参照の応用例ー別シートを参照する

例えば、Excelで月毎にシートを分けてデータを入力するケースでは、参照先のマスターデータを各シート毎に配置するよりかは、単独のシートにまとめる方が合理的です。なぜなら、マスターデータを更新する場合、一箇所のシートにまとめておくと、更新は1度で済むからです。

ここでは、VLOOKUP関数を用いて、別シートにあるマスターを参照する方法について解説します。

まず最初に参照したいマスターシートを作成します。ここでは、商品コードと品名、単価を項目とする商品マスターシートを作成します。

次に、月毎に商品別売上高のシートを作成します。商品コード別の売上数を入力すると商品別の売上高が自動的に計算されるようにします。品名と単価は商品マスターから引けるように、VLOOKUP関数を設定します。

8月シートの品名欄のVLOOKUP関数は『=VLOOKUP($B3,商品マスター!$B$3:$D$10,2,FALSE)』と入力し、C3セルから下のC10セルまで関数をコピーします。同様に単価欄のE3セルにも『=VLOOKUP($B3,商品マスター!$B$3:$D$10,3,FALSE)」を入力し、E10セルまで関数をコピーします。

他、売上高は売上数×単価の計算式で求めるようにします。最下段に合計欄を作成します。

img_vlookupzettai_10
【図】:絶対参照の応用例ー別シートを参照する

後は8月のシートをコピーして、9月、10月とシートを作成していけば月別の商品別売上シートが完成します。以降は、シートに入力するのは品目別の売上数だけです。

【参考】:VLOOKUP 関数 |Microsoft サポート

VLOOKUPで別シートを参照するには?記述方法や実践例を解説

VLOOKUP関数の3つのエラー

VLOOKUP関数は非常に便利な関数ですが、よく発生するエラーがあります。以下に、一般的な3つのエラーとその原因、対処法を説明します。

▪#N/Aエラー #N/Aエラーは、指定した検索値が指定された範囲内に見つからない場合に発生します。多くの場合、検索値が正しく入力されていないか、範囲内に存在しないことが原因です。この問題を解決するには、検索値が正しいか確認し、検索対象の範囲内に含まれているかチェックしてください。

また、完全一致を求める場合には、VLOOKUP関数の最後の引数にFALSEを設定する必要があります。

【参考】:VLOOKUP 関数の #N/A エラーを修正する方法|Microsoft サポート]

▪#VALUEエラー #VALUEエラーは、引数の形式が正しくない場合に発生します。例えば、検索値がテキスト形式である場合に数値形式の範囲で検索を試みると、このエラーが発生します。このエラーを防ぐためには、検索値と範囲内のデータ形式が一致していることを確認してください。

【参考】:VLOOKUP 関数の #VALUE! エラーを修正する方法|Microsoft サポート

▪#REFエラー #REFエラーは、参照している範囲が削除または無効になった場合に発生します。例えば、範囲外の列を参照している際に起こります。解決策としては、参照する範囲が正しいことを確認し、範囲が正しく設定されているか確認することが重要です。

【参考】:エラー値 #REF! を修正する方法|Microsoft サポート

VLOOKUP関数でエラーが出た?#REF!エラーなどの解決法を詳しく解説

VLOOKUP関数を活用しよう

img_vlookupzettai_11

この記事では、Excelで「VLOOKUP関数の絶対参照」を活用する方法について解説しました。

VLOOKUP関数は一見複雑で、取っつきにくい関数と思われがちですが、使いこなせると大変便利な関数です。またVLOOKUP関数は、小規模なデータ管理や簡単な検索には十分な機能を持っていますので、わざわざデータベースを構築しなくとも、Excelで代用可能です。

その際、VLOOKUP関数による絶対参照は必須となりますので、ぜひ絶対参照をマスターし、業務で活用してみましょう。

その他VLOOKUP関連記事

その他、VLOOKUPに関連する内容は、下記の記事でもご紹介しています。ぜひ参考にご覧ください。

VLOOKUP関数で複数条件を指定する?簡単にできる方法を図解

VLOOKUPで別シートを参照するには?記述方法や実践例を解説

VLOOKUP関数でエラーが出た?#REF!エラーなどの解決法を詳しく解説

VLOOKUP関数がうまく反映されない?その原因と対策を図解

VLOOKUPとIFの組み合わせで何ができる?便利な使い方を解説

VLOOKUPで部分一致検索するには?ワイルドカード使用法を解説

VLOOKUPとXLOOKUPの違いとは?使い方を図解で解説

VLOOKUPで0を表示しない方法とは?発生原因と対処法を解説

\転職するか迷っていてもOK!/
マイナビエージェントに無料登録して
転職サポートを受ける
気になる人のXをフォローしよう!
公式LINE
公式YouTube
マイナビITエージェント

編集部オススメコンテンツ

Sponsored
【年収±診断】6つの質問に答えて、真の市場価値をチェック!
マイナビITエージェント
Sponsored

アンドエンジニアへの取材依頼、情報提供などはこちらから

お問い合わせ・情報提供
はじめて転職される方へ
SE・システムエンジニア(IT/通信/インターネット) 求人一覧

編集部おすすめコンテンツ

Sponsored
【年収±診断】6つの質問に答えて、真の市場価値をチェック!
マイナビITエージェント
Sponsored

アンドエンジニアへの取材依頼、情報提供などはこちらから

logologo
Powered by マイナビ AGENT