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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

▼給与表

▼VTable

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

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

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

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

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

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

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

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

まとめ

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

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