ITで業務改善！スキルUP応援メディア
作りながら学ぶ！
シリーズ連載を通して、勤怠管理システムを作りながらスプレッドシートを体系的に学ぶ方法を解説。
こんにちは、たま(@closuppo)です！
今回は「第11回：VLOOKUP関数で別シートの値を参照する」についてお伝えします。
セル値を条件に、別のシートから条件に一致する値を参照しましょう。
※目次をクリックすると
タイトルの先頭まで移動します（↓）
【条件付き書式】の機能を使って、条件を満たしたセル背景を自動で色付けされるようにする方法をお伝えしました。
今回は、その続きで【VLOOKUP関数】【INDIRECT関数】を使って、セル値を条件に別のシートから条件に一致する値を参照する方法を解説します。
サンプルシートを用意しています
のでコピーして参考にしてください
前回の記事で完成した勤怠入力シートをコピペして、メンバー全員分のシートを作成しましょう。
勤怠入力シートはマスターシートとして活用していきます。
勤怠入力シートタブをダブルクリックし【マスター】に変更してください。
【出勤】【退勤】【休憩】【区分】は何も入力されていない状態にしておきましょう。
必要に応じて【表示形式】も変更してください。
マスターシートを複製し、3人分の【勤怠入力】シートを作成しましょう。
マスターシートタブの▼をクリックしコピーを作成を選択してください。
コピーしたシートタブをダブルクリックし【メンバー1】に変更してください。
同じ手順で3人分の【勤怠入力】シートを作成しましょう。
各メンバーの稼働実績を一覧で確認できるように管理表ページを作成しましょう。
左下部の＋ボタンをクリックし、新しくシートを追加し、ワークシート名(シートタブの名前)を【管理表】に変更してください。
管理表シートは一番左に移動しておきましょう。
セルA1～G1に以下項目を入力して見出しを作ってください。
次に、セルA2～A4にメンバー名を入力してください。。
VLOOKUP(ブイルックアップ)関数の基礎のおさらいです。
VLOOKUP関数とは、範囲の1列目で指定した値を垂直方向に検索し、同じ行内にある指定したセルの値を返してくれる関数です。
例えば、以下図のように元データから【りんご】の売上を返したい時などに使います。
VLOOKUP関数の構文はこちら。
VLOOKUP関数にINDIRECT関数を組み込んで、メンバー1シートのセル範囲「A34:B38」の値を参照する数式を作っていきましょう。
まずは【出勤回数】を参照します。
セル「B2」に入力する完成した数式はこちら。
こちらの数式について詳しく
解説していきます
VLOOKUP関数の構文はこちら。
【❷検索範囲】は、INDIRECT関数を使って範囲を指定します。
INDIRECT関数の構文はこちら。
今回は、セルの値と文字列を統合してINDIRECT関数で別シートのセル範囲を指定します。
詳しく見ていきましょう。
まずは、VLOOKUP関数の【❶検索値】を指定します。
【出勤回数】の値を参照したいのでセル「B1」を参照してください。
セル「B2」に作成する数式をコピペして活用できるようにセル「B1」は複合参照にします。
このように複合参照で行番号(数字)を固定することで、コピペしても【❶検索値】が正しく指定されます。
次に、【❷検索範囲】をINDIRECT関数を使って範囲を指定します。
まずは、INDIRECT関数を使わずに参照する数式を見てみましょう。
VLOOK関数だけでメンバー1シートの【出勤回数】を参照する数式はこうなります。
この赤字の箇所をINDIRECT関数で指定します。
セル「B2」で作成する数式をセル範囲「B3:B4」にコピペで活用できるように、セルを参照したINDIRECT関数を作成します。
完成したINDIRECT関数はこちら。
このように、セルとテキストを統合して別シートのセル範囲を指定することができます。
A列に入力された【メンバー名】と【ワークシート名】は同じなので、直接セル範囲を指定した引数と、セルとテキストを結合した引数が同じであることがご理解いただけるかと思います。
また、参照するセルを複合参照で列番号(アルファベット)を固定することで、コピペしても【❷検索値】が正しく指定されます。
セル参照については以下ページ
に詳しくまとめてます
セル「B2」をコピーして、セル範囲「B2:F4」にペーストしてください。
各メンバーの稼働実績が反映されたら、1行目の【❶検索値】と1列目の【❷検索範囲】のセルが正しく指定されているか確認しましょう。
背景色や枠線を変更して、管理表シートを見やすくデザインしましょう。
セル範囲「A1:D1」を選択し、セル背景を【明るい赤３】に変更してください。
セル範囲「E1:F1」を選択し、セル背景を【明るい黄３】に変更してください。
セル範囲「A1:F1」を選択し、枠線を【暗いグレー２】に変更してください。
セル範囲「A1:F1」を選択し、テキストの配置を【中央】に変更してください。
セル「A2」とセル「A4」のセル背景を【明るいグレー１】に変更してください。
セル範囲「B3:F3」のセル背景を【明るいグレー３】に変更してください。
セル範囲「B2:B4」の左の枠線を【黒】に変更してください。
セル範囲「E2:E4」の左右の枠線を【黒】に変更してください。
セル範囲「C2:C4」の左右の枠線を【暗いグレー２】の【点線】に変更してください。
セル範囲「F2:F4」の左の枠線を【暗いグレー２】の【点線】に変更してください。
管理表シートが装飾されました。
表を装飾する方法は以下ページ
を参考にしてください
Googleスプレッドシートで【VLOOKUP関数】【INDIRECT関数】を使って、セル値を条件に別のシートから条件に一致する値を参照する方法をお伝えしました。
今回の記事で【勤怠管理システム】は完成となります。
スプレッドシートの教科書 で学んだ基礎知識をアウトプットすることはできましたか？
本やネットなどで学ぶだけではスプレッドシートの知識は身につきません。
スプレッドシートを業務で活用できるように、手を動かしながら、しっかりとアウトプットを繰り返していきましょう。
シリーズ連載を通して、勤怠管理システムを作りながらスプレッドシートを体系的に学ぶ方法を解説しています。
