【動画付き #22】VLOOKUP関数で別シートを参照・範囲に指定する方法を紹介

この記事を読んでわかること
  • リスクを最小限に抑えるデータの構え方
  • VLOOKUP関数で別シートにあるデータを参照する方法
  • データを別シートに分ける際の注意点

VLOOKUP関数を使っているときに「どこのシート・ブックを参照しているのかわからなくなった」「別のファイルの参照が崩れてしまってゼロから作り直すことになってしまった」という経験があるもいらっしゃるのではないでしょうか。この問題は、シートの構え方を工夫することで解決可能です。

本記事では、VLOOKUP関数で別シートを参照・範囲に指定する方法や、普段使いされる人に参照範囲を正しく管理するポイントを紹介していきます。

本記事はYouTube『ユースフル / 人材教育の教科書』チャンネルより、以下の動画を記事にまとめたものです。動画だけではなく、テキストで復習したい方もいるはず。全38の記事と動画を組み合わせて、Excelの基本操作をマスターしていきましょう。

▼前回の記事では、ExcelのVLOOKUP関数の近似一致と完全一致の違いについて解説しています。こちらもあわせてお読みください!

目次

Excelのシートに様々なデータを詰め込むとどうなる?

こちらのシートを使って見ていきましょう。1枚のシートの中に、①給与表②歩合給決定表の2つのデータベースが存在しています。

Excel#22の1

現在、給与表の「歩合給」(E列)には、歩合給決定表を範囲としたVLOOKUP関数が組まれています。

よくあるミス①:意図せず重要なデータも削除されてしまった

1つ目のよくあるミスは、行・列の削除によって「意図せず重要なデータも削除されてしまうこと」です。

たとえば、社員の岡野さんが退職されたとします。この場合、給与表の中の「岡野」(5行)は、通常行ごと削除され、隣に配置していた歩合給決定表の「評価E」の範囲も一緒に消えてしまいます。

Excel#22の2
岡野さんデータ(5行目)を行ごと削除
Excel#22の3
評価「E」欄も消えてしまう

データベースを縦に並べたとしても、列を削除する際に同様のリスクが伴います。

Excel#22の4
4月の売り上げのデータ(C列)を列ごと削除
Excel#22の5
「評価」欄も消えてしまい、関数がくずれている

このように、行や列を削除した際に、関係のない重要なデータも一緒に削除される可能性があることが、シートに複数のデータベースを詰め込む最大のリスクです。

よくあるミス②:表示がくずれてしまう

セルの幅や高さをそろえようとした際にもミスが発生しがちです。

たとえば、データベースを縦に2つ並べてセル幅を揃えるとします。すると、セル幅は文字数が1番多いセルに合わせて揃えられるため、2つのデータベースの文字数に大きな差があった場合、文字数が少ない方のデータベースには不自然な空白が表示されてしまいます。

解決策:同じファイル内の別シートにデータをストックする

上記のようなリスクを避けるために、Excelで様々なデータを管理する際は、データごとにシートを分けるようにしましょう。

今回の①給与表と②歩合給決定表の2つのデータベースをストックするケースでは、給与表を「給与表」(シート1枚目)に、歩合給決定表を「VTable」(シート2枚目)に分けてストックしています。

Excel#22の6
給与表を「給与表」(シート1枚目)
Excel#22の7
歩合給決定表を「VTable」(シート2枚目)

こうすることで、先述した行や列を削除する際に意図しないデータまで失ってしまうリスクや表示がくずれるリスクを防ぐことができます。

ただし、参照するデータは、同じファイル(Excelでは「ブック」)にまとめるようにしましょう。
VLOOKUP関数で別ファイルのデータを引用することは可能ですが、1つのワークシートに様々なファイルが紐づいている状態になってしまい、誰かが気づかずファイルごと削除した際にセル参照や数式が壊れてしまいます。

VLOOKUP関数で別シートを参照・範囲に指定する方法

ここからは、VLOOKUP関数で別シートにあるデータを参照する方法をご紹介します。

まずはシートの説明です。下記の「給与表(5月分)」の表は「給与表」シートにあり、左から「担当」「4月の売上」「固定給」「歩合給」「5月給与」を表しています。「給与表(5月分)」の「歩合給」を決定するためには、「VTable」シートの「歩合決定表」を参照する必要があります。

▼給与表

Excel#22の8

▼VTable

Excel#22の9

そこで、VLOOKUP関数で別シートの「歩合決定表」を範囲に指定して、「給与表(5月分)」の「歩合給」を算出する手順を見ていきましょう。

<手順>
  1. アウトプットするセルを選択(アクティブセルにする)
  2. =VLOOKUP()」と記入
  3. 検索値(第一引数、「(」の次)に、検索値(検索したい値)のセル番地を記入/セルをクリック
    (例:C4)
  4. ,」を記入
    ~範囲(第二引数、「,」の次)の記入モードになる~
  5. 範囲が記入されているシートに移動し、範囲を選択
    ~範囲として「[シート名]![セル番地]」と記載される~
  6. 列番号を記入
  7. セル参照の種類を選択
    – TRUE :相対参照
    – FALSE:絶対参照
  8. Enterをクリック
Excel#22の10
2.「=VLOOKUP()」と記入~4. 「,」を記入
Excel#22の11
5. 範囲が記入されているシートに移動し、範囲を選択
Excel#22の12
6. 列番号を記入~7.セル参照の種類を選択

これで、VLOOKUP関数の範囲に、別シートのデータを設定することができました。

Excel#22の13

別シートにあるデータを参照する場合でも、VLOOKUP関数の基本的な操作は同じです。

ただし、VLOOKUP関数の範囲を別シートから引用した場合、セル番地の前に「[シート名]!」と入る点が特徴です。

Excelのシートをデータごとに分けるリスク

最後に、データごとにシートを分けることのリスクについてご紹介します。

Excelでは、同じシート内にあるデータのみ対応している関数や機能が一部あります。
たとえば、テーブル内の数式において変数が数式に与える影響を調べる「What-If分析>データテーブル」では、同一シート内に存在するデータのみを調べる仕様になっています。

Excel#22の14

Excelでデータをストックする際は、基本的にはデータごとにシートを分けるのがベターですが、使いたい機能などによってシートの構成を工夫していきましょう。

まとめ

本動画では、テーブル機能についてご紹介しました。Excelで別シートを参照する際のポイントは、VLOOKUP関数の第二引数を参照する際には、同じファイルの別のシートから参照することです。

次回の記事では、引き続きVLOOKUP関数を用いる際に便利な機能「文字列操作関数」をご紹介します。

ここまで読んで「Excelをもっと学びたい」「実務で活かせるようになりたい」と思った方におすすめなのが、ユースフルが提供するオンライン講座の『ExcelPro(エクセルプロ)』とMOS Excel 365&2019スペシャリスト完全対策講座』。

講座①:ExcelPro(エクセルプロ)

ExcelPro(エクセルプロ)

ExcelProは108本の動画と15の演習問題で実務に直結するExcelスキルが学べるオンライン講座。全てのレッスンにサンプルシートがついており、講義と同じデータで学習を進めることができます。

さらに、充実した動画内容に加えてメールでの質問サポート付き。受講時にわからなかったことは質問することで復習につなげることができます。

口コミが気になる方は下記の記事をご一読ください。実際のExcelProの受講者の声から口コミや評判を紹介しています。

講座②:MOS Excel 365&2019完全対策講座

ユースフルではMicrosoft MVPの監修のもと、MOS試験(スペシャリスト・エキスパート)対策講座を提供しています。

約70の豊富な動画講座・各章末の確認テスト・5回の模擬試験を通じて試験の出題範囲をすみずみまで学び、最短合格を目指しましょう!

【ユースフル提供】MOSスペシャリスト完全対策講座

9,800円(税込)

おすすめポイント
  • 全69動画
  • 各章末に確認テスト
  • 5回の模擬試験
【ユースフル提供】MOSエキスパート完全対策講座

12,800円(税込)

おすすめポイント
  • 全74動画
  • 各章末に確認テスト
  • 5回の模擬試験

こちらの人気記事にも注目!

ユースフル編集部では、ExcelやMOS関連の情報はもちろん、AccessなどMicrosoftツールの使い方も解説しています。こちらの人気記事もあわせてご覧ください!

気に入ったらシェア!
URLをコピーする
URLをコピーしました!

この記事を書いた人

記事をお読みいただきありがとうございます!ユースフル編集部では、Excelをはじめとするマイクロソフト製品の活用法をわかりやすく紹介しています。ご興味ある方は、ぜひ他の記事もご覧ください!

目次
閉じる