絶対参照とVLOOKUP関数で仕事で使える資料を作成！
歩君「VLOOKUP関数の範囲は絶対参照にしないとだめだよ」って先輩に言われたんですけど、どうして絶対参照にしないといけないんですか？
ライクさん絶対参照で範囲を指定しておかないと、計算式をコピーしたときに参照先がずれてしまうんだ。
VLOOKUP関数は、引数の範囲を絶対参照で指定しないと、コピーして計算式を入力する際に、正しく参照されません。
VLOOKUP関数の範囲を正しく指定する方法をマスターして、入力時間が短縮できる資料を作成してみましょう。
VLOOKUP関数で絶対参照に変更する方法
VLOOKUP関数に入力した表の範囲を選択してF4を押す
今回は、絶対参照の応用編としてVLOOKUP関数の使い方を紹介します。
エクセルがあまり得意ではない方も、日常業務でVLOOKUP関数を使いこなせるように、実際の書類を作成しながら解説していますのでぜひ挑戦してみてください。
VLOOKUP関数で使用する以外の絶対参照に関する関連記事があります。
こちらの記事もあわせてご覧ください。
目次
エクセルではVLOOKUP関数の範囲は絶対参照で指定する
エクセルでは、VLOOKUP関数の範囲は絶対参照で指定します。
VLOOKUP関数とはどんな関数なのか、関数の範囲を絶対参照にしなければいけない理由を解説していきます。
VLOOKUP関数とは？
歩君先輩に「このデータは、VLOOKUP関数を使って作成すると便利だよ」って言われたんですけど、どんなときに使える関数なんですか？
ライクさんVLOOKUP関数は、あらかじめ指定した範囲から列方向に検索して値を表示できる便利な関数なんだ。言葉だと少しわかりにくいから、データを使って解説するね。
これは、「B4」に「青枠の表」に入力されている単価を参照して、「テキスト発注表」に単価が表示されるようにVLOOKUP関数を挿入したデータです。
歩君「テキスト発注表」にテキスト名を入力すれば、「B4」のセルに青枠の範囲の単価が自動で表示されるってことですか？
ライクさんそのとおり！青枠の表を参照して、「テキスト発注表」の単価に金額を表示できるんだ。
例えば、仕入業務で商品を発注する場合、発注金額を把握せずに注文することはありませんよね。
上記の右側の表のように商品名と金額を一覧にした表を作成しておけば、VLOOKUP関数を使って発注した商品の金額を簡単に集計することができます。
VLOOKUP関数の範囲を絶対参照にしなければいけない理由
歩君VLOOKUP関数の範囲は、どうして絶対参照にしなければいけないんですか？
ライクさん挿入したVLOOKUP関数をコピーすると、参照した表の範囲がずれてしまうからだよ。
セル参照で入力した計算式はコピーすることができますが、相対参照で入力した計算式はコピーするとセルの位置がずれるという性質があります。
VLOOKUP関数の範囲を相対参照で入力して、範囲がどのようになるか確認してみましょう。
これは、「B4」のセルにVLOOKUP関数の範囲を相対参照で入力し、2行目以降のセルに計算式をコピーしたあとの画像です。
正しい単価が入力されているセルもありますが、エラーになってしまっているセルもありますね。
数式に表示を変えて、コピーした計算式を確認してみましょう。
このデータのVLOOKUP関数の範囲は「G4:H11」を指定する必要があります。
しかし、コピーした2行目以降の計算式を確認すると表のセル範囲1つずつ下にずれてしまっています。
四則計算のときはもちろんですが、VLOOKUP関数のようにセル参照で関数の引数を指定する場合も、計算式をコピーすると参照範囲がずれてしまうので注意してください。
エラーが表示されないようにしたいときはIF関数と組み合わせる
VLOOKUP関数の範囲を絶対参照で指定しておくと計算式をコピーで入力できるので便利なのですが、必要なデータを入力する前に計算式をコピーすると、「#N/A」というエラーが表示されてしまいます。
歩君つまり、VLOOKUP関数をコピーするとエラーが表示されてしまうってことですよね？
エラーを出さずに、計算式をコピーすることってできないんですか？
ライクさんVLOOKUP関数にIF関数を組み合わせておくと、エラー表示を回避できるよ。
エラーを表示させずにVLOOKUP関数をコピーしたいときは、IF関数を組み合わせて使用しましょう。
IF関数を使ってエラー表示を回避する方法は、エクセルの絶対参照を使ってビジネスで役立つ資料を作成する方法で詳しく紹介します。
エクセルの絶対参照を使ってビジネスで役立つ資料を作成してみよう！
VLOOKUP関数は、指定した表の範囲から参照したい列番号を指定して値を表示できる便利な関数ですが、「検索値」が入力されていないときは、「#N/A」というエラーが表示されます。
#N/Aは、検索を求められた数式の値が見つからないときに表示されるエラーです。
歩君データを共有したいのでエラー表示を消しておきたいんですけど、どうしたらいいんですか？
ライクさん検索値のデータを入力すると正しい結果は表示されるけど、エラーが表示されたままだと少し見た目が悪いよね。#N/Aエラーの表示を回避して、VLOOKUP関数を挿入する方法を教えるね。
VLOOKUP関数とIF関数を組み合わせると、検索値が空白のときにVLOOKUP関数の値を空白にすることができます。
ビジネスシーンで活用できる「納品書」と「発注が必要な備品の一覧表」を作成して、エラーが表示されない書類の作成方法を紹介します。
IF関数と組み合わせているため少し手順が多くなりますが、ビジネスシーンではよく使われる組み合わせなので、ぜひマスターしてみてください。
価格表を参照した納品書を作成する
VLOOKUP関数にIF関数を組み合わせて、エラーが表示されない納品書のひな型を作成してみましょう。
ライクさん初心者の方にもわかりやすいように手順を分割しているからSTEPの数が多いけど、1STEPずつ操作をしていってみてね。
STEP
納品書のベースとテキスト価格表の元データを作成
納品書のベースとテキスト価格表の元データを作成します。
ライクさん操作がわかりやすいように、テキスト価格表を同じシート内に作成しているけど、別シートに作成してもOKだよ。
STEP
「C20」にIF関数を挿入
- 「C20」をクリック
- 「=IF(A20="","",」と入力
STEP
VLOOKUP関数を挿入
数式の続きに「VLOOKUP(」と入力してください。
STEP
VLOOKUP関数の検索値を入力
- 「A20」をクリック
- 「,(カンマ)」を入力
STEP
表の範囲をドラッグ
- 表の範囲をドラッグ
- 絶対参照に変更
- 「,」を入力
STEP
VLOOKUP関数の列番号と検索方法を入力
「2,0))」と入力し、Enterで確定してください。
ライクさん関数の最後は、IF関数を閉じるかっことVLOOKUP関数を閉じるかっこを入力するよ。2つ目の閉じるかっこを忘れないようにしてね。
STEP
数式をコピー
数式を入力したセルをクリックしなおして、計算式をコピーしてください。
STEP
金額の計算式を挿入
- 「E20」をクリック
- 「=IF(A20="","",C20*D20)」と入力する
=IF(A20="","",C20*D20)STEP
合計の数式をコピーする
- 金額のセルをクリック
- 計算式をコピーする
STEP
小計の計算式を挿入
- 小計のセルをクリック
- 「=IF(E20="","",SUM(E20:E28))」と入力する
=IF(E20="","",SUM(E20:E28))STEP
消費税の計算式を挿入
- 消費税のセルをクリック
- 「＝IF(E20="","",E29*0.1)」と入力する
＝IF(E20="","",E29*0.1)STEP
合計金額のセルに計算式を挿入
- 合計金額のセルをクリック
- 「=IF(E20="","",SUM(E29:E30))」と入力する
=IF(E20="","",SUM(E29:E30))STEP
「B13」に「E31」を参照する数式を挿入
「B13」をクリックして「=E31」と入力し、Enterで確定してください。
STEP
ひな型として保存しておく
これで、計算式を挿入した納品書の完成です。
この状態でひな型として保存しておきましょう。
STEP
赤枠で囲まれている部分を手入力する
使用するときは、赤枠で囲んだ部分を手入力します。
ライクさん行程が多いから少し大変だけど、この手順を覚えてしまえば請求書などの他の書類にも応用できるから、ぜひチャレンジしてみてね。
備品在庫リストから発注が必要な備品一覧表を作成する
最低在庫基準を設定した在庫管理リストを作り、発注が必要な備品一覧表を作成してみましょう。
STEP
「事務用備品在庫リスト」と「発注が必要な備品一覧表」を作成
上記のような「事務用備品在庫リスト」と「発注が必要な備品一覧表」を作成します。
「事務用備品在庫リスト」では、最低個数を下回った備品に「要発注」と表示させたいため、表以外の場所に発注の基準となる「最低個数」を入力しておきます。
「発注が必要な備品一覧表」は、「管理ID」、「品名」、「必要個数」、「仕入先」、「発注日」の見出しを入力し、フィルターを挿入しておきましょう。
STEP
「要発注」の列にIF関数を挿入
- 「F3」をクリック
- 「=IF(E3<$I$1,"要発注","")」と入力
=IF(E3<$I$1,"要発注","")このIF関数は、「I1（アイの1）」が「E3」より小さいときは「要発注」、小さくないときは空白にしてね」という意味だよ。
STEP
「F3」の計算式をコピーする
「F3」に挿入した計算式をコピーしてください。
STEP
「発注が必要な備品一覧表」の「K3」にIF関数を挿入
- 「K3」をクリック
- 「=IF(F3="","",A3）」と入力
=IF(F3="","",A3)STEP
「K3」の計算式をコピーする
「K3」に入力した計算式をコピーしましょう。
STEP
「品名」の列にVLOOKUP関数を挿入
管理IDが表示されたときに、「発注が必要な備品一覧表」の品名に「事務用備品在庫リスト」の品名が表示されるようにVLOOKUP関数を挿入します。
- 「L3（エルの3）」をクリック
- 「=IF(K3="","",VLOOKUP(K3,$A$3:$C$19,2,0))」と入力して、計算式をコピーする
=IF(K3="","",VLOOKUP(K3,$A$3:$C$19,2,0))STEP
必要個数の計算式を挿入
- 「M3」をクリック
- 「=IF(K3="","",$I$1-E3)」を入力して、すべてのセルに計算式をコピーする
=IF(K3="","",$I$1-E3)STEP
「仕入先」の列にVLOOKUP関数を挿入する
- 「N3」をクリック
- 「=IF(K3="","",VLOOKUP(K3,$A$3:$E$19,3,0))」と入力し、すべてのセルに計算式をコピーする
=IF(K3="","",VLOOKUP(K3,$A$3:$E$19,3,0))STEP
現在の個数を入力したあとの画像
「発注が必要な備品一覧表」に、品名や仕入先などの必要なデータを表示することができました。
STEP
空白のセルを非表示にする
- 「発注ID」のフィルターボタンを押す
- 検索ボックスの下に表示されている管理IDのリストの右側にあるスクロールバーをスクロールして、「空白セル」のチェックを外す
- 「OK」をクリック
STEP
空白セルが非表示になった
空白セルが非表示になり、表が見やすくなりました。
歩君実際に書類を作成してみたら、VLOOKUP関数の活用方法と絶対参照の使い方がわかってきました！
ライクさん他の関数と組み合わせると計算式が複雑になるから少し大変だけど、IF関数とVLOOKUP関数はよく使われる関数だから覚えておくと便利だよ。
絶対参照のVLOOKUP関数に関するQ＆A
- VLOOKUP関数の範囲を絶対参照にする方法を教えてください。
-
VLOOKUP関数の範囲を入力したあとに表の範囲をドラッグし、F4を押すと簡単に絶対参照に変更することができます。
- どうしてVLOOKUP関数の範囲は絶対参照を使わなければいけないんですか？
-
コピーを使ってVLOOKUP関数を挿入すると、コピーで入力したVLOOKUP関数の表の範囲がずれてしまうためです。
ビジネスシーンでは計算式はコピーで入力するケースがほとんどなので、VLOOKUP関数の範囲は絶対参照で指定するようにしてください。
- 「検索値」が入力されていないとエラーが表示されてしまうのですがどうしたらいいですか？
-
VLOOKUP関数は、「検索値」のセルにデータが入力されていないと、「#N/A」エラーが表示されてしまいます。
エラーを表示させたくない場合は、VLOOKUP関数にIF関数を組み合わせてください。
詳しい手順は、エクセルの絶対参照を使ってビジネスで役立つ資料を作成してみよう！で解説しています。
エクセルで絶対参照とVLOOKUP関数を活用して入力しやすい資料を作成してみよう！
VLOOKUP関数は、ビジネスシーンでよく使われる関数の1つです。
しかし、VLOOKUP関数の範囲を絶対参照にしていなかった場合、計算式をコピーすると参照している表の範囲がずれてしまうので、注意してください。
最後に、VLOOKUP関数の範囲を絶対参照に変更する方法をおさらいしておきましょう。
おさらい
VLOOKUP関数に挿入した表の範囲を選択してF4を押す
今回は、VLOOKUP関数の基本の使い方に加えて、実務で役立つ納品書の作り方や、発注が必要な備品一覧表の作成方法を紹介しました。
実務でVLOOKUP関数を使う場合、エラーが表示されないようにIF関数と組み合わせることが多いので、こちらの記事を参考にVLOOKUP関数とIF関数を組み合わせて使う方法もあわせてマスターしてみてください。
VLOOKUP関数の範囲を絶対参照にする方法以外にも、絶対参照に関する関連記事があります。
こちらの記事もあわせてご覧ください。
