【スプレッドシート】VLOOKUP関数で別シートの値を参照する方法

作りながら学ぶ!

シリーズ連載を通して、勤怠管理システムを作りながらスプレッドシートを体系的に学ぶ方法を解説。


こんにちは、たま(@closuppo)です!

今回は「第11回:VLOOKUP関数で別シートの値を参照する」についてお伝えします。

セル値を条件に、別のシートから条件に一致する値を参照しましょう。


※目次をクリックすると
タイトルの先頭まで移動します(↓)

目次



前回記事のおさらい

【条件付き書式】の機能を使って、条件を満たしたセル背景を自動で色付けされるようにする方法をお伝えしました。

前回の記事はこちら

今回は、その続きで【VLOOKUP関数】【INDIRECT関数】を使って、セル値を条件に別のシートから条件に一致する値を参照する方法を解説します。

サンプルシート

サンプルシートのコピー方法の解説はこちらです

タマ

サンプルシートを用意しています

のでコピーして参考にしてください


勤怠入力シートを複製する

前回の記事で完成した勤怠入力シートをコピペして、メンバー全員分のシートを作成しましょう。

勤怠入力シートはマスターシートとして活用していきます。


マスターシートに変更する

勤怠入力シートタブをダブルクリックし【マスター】に変更してください。

spreadsheet-vlookup01

【出勤】【退勤】【休憩】【区分】は何も入力されていない状態にしておきましょう。

必要に応じて【表示形式】も変更してください。

spreadsheet-vlookup02

メンバーシートを作成する

マスターシートを複製し、3人分の【勤怠入力】シートを作成しましょう。

マスターシートタブのをクリックしコピーを作成を選択してください。

spreadsheet-vlookup03

コピーしたシートタブをダブルクリックし【メンバー1】に変更してください。

spreadsheet-vlookup04

同じ手順で3人分の【勤怠入力】シートを作成しましょう。

spreadsheet-vlookup05

管理表シートを作成する

各メンバーの稼働実績を一覧で確認できるように管理表ページを作成しましょう。


シートを追加する

左下部のボタンをクリックし、新しくシートを追加し、ワークシート名(シートタブの名前)を【管理表】に変更してください。

管理表シートは一番左に移動しておきましょう。

spreadsheet-vlookup06

見出しを作成する

セルA1~G1に以下項目を入力して見出しを作ってください。

A1:名前
B1:出勤回数
C1:稼働時間
D1:残業時間
E1:遅刻
F1:当日欠勤
spreadsheet-vlookup07

次に、セルA2~A4にメンバー名を入力してください。。

A2:メンバー1
A3:メンバー2
A4:メンバー3
spreadsheet-vlookup08

VLOOKUP関数のおさらい

VLOOKUP(ブイルックアップ)関数の基礎のおさらいです。

VLOOKUP関数とは、範囲の1列目で指定した値を垂直方向に検索し、同じ行内にある指定したセルの値を返してくれる関数です。

例えば、以下図のように元データから【りんご】の売上を返したい時などに使います。

VLOOKUP関数GIF

VLOOKUP関数の構文はこちら。

=VLOOKUP(❶検索値, ❷検索範囲, ❸列番号, ❹[検索の型])

別シートから値を参照する

VLOOKUP関数にINDIRECT関数を組み込んで、メンバー1シートのセル範囲「A34:B38」の値を参照する数式を作っていきましょう。

まずは【出勤回数】を参照します。

セル「B2」に入力する完成した数式はこちら。

=VLOOKUP($B$1,INDIRECT(A2&"!A34:B38"),2,0)
タマ

こちらの数式について詳しく

解説していきます

VLOOKUP関数の構文はこちら。

=VLOOKUP(❶検索値, ❷検索範囲, ❸列番号, ❹[検索の型])

【❷検索範囲】は、INDIRECT関数を使って範囲を指定します。

INDIRECT関数の構文はこちら。

=INDIRECT(❶参照文字列)

今回は、セルの値と文字列を統合してINDIRECT関数で別シートのセル範囲を指定します。

詳しく見ていきましょう。

まずは、VLOOKUP関数の【❶検索値】を指定します。

【出勤回数】の値を参照したいのでセル「B1」を参照してください。

セル「B2」に作成する数式をコピペして活用できるようにセル「B1」は複合参照にします。

=VLOOKUP( B$1, ❷検索範囲, ❸列番号, ❹[検索の型])

このように複合参照で行番号(数字)を固定することで、コピペしても【❶検索値】が正しく指定されます。

次に、【❷検索範囲】INDIRECT関数を使って範囲を指定します。

まずは、INDIRECT関数を使わずに参照する数式を見てみましょう。

VLOOK関数だけでメンバー1シートの【出勤回数】を参照する数式はこうなります。

=VLOOKUP(B$1, 'メンバー1'!A34:B38, 2, 0)

この赤字の箇所をINDIRECT関数で指定します。

セル「B2」で作成する数式をセル範囲「B3:B4」にコピペで活用できるように、セルを参照したINDIRECT関数を作成します。

完成したINDIRECT関数はこちら。

=VLOOKUP(B$1, INDIRECT($A2&"!A34:B38"), 2, 0)

このように、セルとテキストを統合して別シートのセル範囲を指定することができます。

A列に入力された【メンバー名】と【ワークシート名】は同じなので、直接セル範囲を指定した引数と、セルとテキストを結合した引数が同じであることがご理解いただけるかと思います。

spreadsheet-vlookup89

また、参照するセルを複合参照で列番号(アルファベット)を固定することで、コピペしても【検索値】が正しく指定されます。


タマ

セル参照については以下ページ

に詳しくまとめてます


数式をコピペする

セル「B2」をコピーして、セル範囲「B2:F4」にペーストしてください。

各メンバーの稼働実績が反映されたら、1行目の【❶検索値】と1列目の【検索範囲】のセルが正しく指定されているか確認しましょう。

spreadsheet-vlookup90

管理シートの表をデザインする

背景色や枠線を変更して、管理表シートを見やすくデザインしましょう。

セル範囲「A1:D1」を選択し、セル背景を【明るい赤3】に変更してください。

spreadsheet-vlookup15

セル範囲「E1:F1」を選択し、セル背景を【明るい黄3】に変更してください。

spreadsheet-vlookup16

セル範囲「A1:F1」を選択し、枠線を【暗いグレー2】に変更してください。

spreadsheet-vlookup17

セル範囲「A1:F1」を選択し、テキストの配置を【中央】に変更してください。

spreadsheet-vlookup18

セル「A2」とセル「A4」のセル背景を【明るいグレー1】に変更してください。

spreadsheet-vlookup19

セル範囲「B3:F3」のセル背景を【明るいグレー3】に変更してください。

spreadsheet-vlookup20

セル範囲「B2:B4」の左の枠線を【黒】に変更してください。

spreadsheet-vlookup21

セル範囲「E2:E4」の左右の枠線を【黒】に変更してください。

spreadsheet-vlookup22

セル範囲「C2:C4」の左右の枠線を【暗いグレー2】の【点線】に変更してください。

spreadsheet-vlookup28

セル範囲「F2:F4」の左の枠線を【暗いグレー2】の【点線】に変更してください。

spreadsheet-vlookup24

管理表シートが装飾されました。

spreadsheet-vlookup25

タマ

表を装飾する方法は以下ページ

を参考にしてください


まとめ

Googleスプレッドシートで【VLOOKUP関数】【INDIRECT関数】を使って、セル値を条件に別のシートから条件に一致する値を参照する方法をお伝えしました。

今回の記事で【勤怠管理システム】は完成となります。

スプレッドシートの教科書 で学んだ基礎知識をアウトプットすることはできましたか?

本やネットなどで学ぶだけではスプレッドシートの知識は身につきません。

スプレッドシートを業務で活用できるように、手を動かしながら、しっかりとアウトプットを繰り返していきましょう。