印刷用とデータ用のシートを分けてVLOOKUP関数で別シートのデータを連携したい
出来るならどうやって設定すればいいの？
別のシートのデータを使って書類を作る方法があるって聞いたけど具体的な方法は？
実際に使い方を知りたいので教えてほしい
こういった疑問に答えます
✔VLOOKUP関数を2つのシートに分けるメリットのテーマ
VLOOKUP関数で「シート名＋!＋セル」と指定すればOKです
マスタシートを作れば「注文書」が簡単キレイに作れます
この記事を書いているのはOffice歴約20年
現在、エステサロン経営をしながらITを使った現場のマネジメントをしています
「VLOOKUP関数を2つのシートに分けるメリット」に関して実体験に基づいて解説しています
今回の記事を一緒にやってみたい場合は、下のファイルをダウンロードして下さい
VLOOKUP関数で「シート名＋!＋セル」と指定すればOKです
VLOOKUP関数で別シートのセルから情報を持ってきたい時は「シート名とセルを指定」するのが正解です
シートをまたいでデータを参照したい時は「シート名＋！＋セルの番地」を指定するというルールを守れば簡単に設定出来ます
シートをまたぐ方法は思っているよりも簡単なので具体例を見ながら、基本的な設定方法を押さえていきましょう
まずは、同じシートでVLOOKUPを使って金額などを取得する方法を確認しておきましょう
「=VLOOKUP(A3,A10:C19,3,0)」この使い方基本的な使い方です
同じシートになるので特に特別な式は入っていません
もし、VLOOKUP関数の基本的な使い方を知りたい場合は、「VLOOKUP関数は使う場面と基本を押さえれば簡単」で紹介しているので参考にしてみて下さい
基本が確認出来たら、この2つの表を2つのシートに分けてみましょう
シートの名前は「①管理表シート」と「②注文マスタシート」とします
作業前に１点補足します
②の「注文マスタシート」の「マスタ」の意味についてです
「マスタ(マスター)」とはITの世界では「基(もと)になるもの」という意味があります
マスタシートは主に「データベース」を作るために使うことが多いです
「データベース」とは簡単にいうと「ルールに従ったデータの集まり」のことを言います
今回の場合、発注一覧表は「商品ID」「商品名」「金額」という項目を入力して文房具を管理するための表ですよね
こういった「一定のルールがあるデータ(情報)の集まり」のことを専門用語で「データベース」と言います
順を追って説明しますが、シートを分ける時は今回のように書類の作成用とデータベースを別のシートに分けることがVLOOKUP関数の活用方法のポイントになります
前置きが長くなってしまいましたが、実際に2つのシートを分けてみましょう
分け方はシートを追加するなどして【文房具発注一覧表】を別のシートに移動させればOKです
まず、結論ですがシートをまたいだVLOOKUP関数の使い方はこんな感じです
「①の管理表シート」の内容はこの表だけになるので管理や編集が自由に出来ます
「②の注文マスタシート」は【文房具発注一覧表】だけのシンプルなシートになりました
では、シートをまたいだVLOOKUP関数の式を基に設定方法を順を追って見ていきましょう
「=VLOOKUP($A$3,注文マスタシート!$A$3:$C$12,2,0)」がシートをまたいだ式です
ポイントはオレンジ色の「注文マスタシート!$A$3:$C$12」部分で「シート名＋!＋参照範囲」と設定すれば、他のシートの表を検索対象にすることが出来ます
意外と簡単ですよね
では、詳しい設定方法を順番に見ていきましょう
まずは「検索値(調べたい商品IDのセル)」をクリックして設定します
※まだ選択しているのは「管理表シート」ですが次は「注文マスタシート」の表からデータを持ってきたいで「注文マスタシート」に移動します
ココでの注意点は、検索値を設定したあとは必ず「,」を入力して下さい
未入力の場合、シートを移動する時にエラーと判断されてしまうからです
次に「注文マスタシート」に切り替えて「A3～C12」のセル範囲を選択します
そうすると範囲列に自動で「シート名＋!＋セル範囲」と設定してくれます
ココでの注意は「絶対参照」などの設定はまだしないで下さい
途中で参照方法の設定をすると、強制的に管理表シートに戻されて混乱の原因になります
次は「参照する列数と検索方法」を設定します
全ての入力が終わったら「Enterキー」で入力内容を確定させましょう
すると自動的に「管理表シート」に戻りVLOOKUP関数の基本設定が出来ました
最後の仕上げとして、コピーしても参照範囲がずれないように参照範囲を絶対参照にしてみましょう
これで全ての設定が出来ました
シートをまたぐVLOOKUP関数のポイントは「注文マスタシート！」という部分です
これは「注文マスタシートを対象にするよ」という意味になるので覚えておきましょう
他のセルにも同じような設定をすると次のような感じになります
※今回、数式をコピーしても上手く行くように検索値の「$A3」の設定を複合参照に変更しています
繰り返しに鳴りますが、VLOOKUP関数で別シートのセルから情報を持ってきたい時は「シート名とセルを指定」するのが正解です
マスタシートを作れば「注文書」が簡単キレイに作れます
発注書などのレイアウトが複雑な書類を作る時は、データベースと分けて作るのが正解です
なぜなら、書類とデータベースが同じシートにあるとレイアウトを作り直す時にすごく苦労することになるからです
では、その具体例を実際に見ていきましょう
発注書は「発注書」というシートに、食品販売価格一覧表は「マスタ」というシートに作りました
全体を見てもらうと分かる通り複雑な表が2つ必要です
しかも使っているうちにレイアウトの変更が必要になる可能性がありますよね
例えば、注文書を「新しい書式で作り直す」ことになったり、販売価格の一覧表に新しい項目を追加したりして色々と変更が必要になるケースが考えられます
また、削除する項目があったら1枚のシートに2つの表があるとすごく大変な作業になってしまいます
つまり、「レイアウトが変更があっても対応出来るようにする」意味でもシートを2つに分けて作ることが最適解になります
それでは、実際にどんな設定をしているのか見てみましょう
複雑そうに見えても意外と設定していることは簡単なので、実際に自分で設定してみると楽しいですよ
こんな風に発注書の「摘要セル」と「単価セル」は「マスタシート」にあるデータベースから情報を自動的に反映させています
検索値は「No欄」の番号にしているので番号を変更するだけで「摘要と単価を自動で切り替える」ことが出来る訳です
コレも広い意味ではプログラミングですね
番号を入力するだけでココまで出来ると感動すると思います
合わせて「食品販売価格一覧表」の内容も確認しておきましょう
こんな風にシートをまたいだとしても動きは同じです
「シート名＋!＋参照範囲」このルールだけ押さえておけば、実務でも重宝される仕組みを作ることが出来るので基本が分かったら、ぜひトライしてみましょう
繰り返しますが、発注書などのレイアウトが複雑な書類を作る時は、データベースと分けて作るのが正解です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す