先日、電子カルテのように、利用者ごとの基本情報や記録を管理できる仕組みを作ってほしいという依頼を受けました。

といっても、本格的な電子カルテシステムを開発するという話ではありません。複数の担当者がオンラインで情報を入力・確認できて、別々に管理している表の情報も必要な画面に表示したい、という管理データベース的なものです。

そこで今回は、GoogleスプレッドシートのIMPORTRANGE関数とVLOOKUP関数を組み合わせて、複数の表を参照する管理画面を作ってみました。

今回作成した管理データベースの構成

今回の仕組みでは、すべての情報を1枚のシートに詰め込むのではなく、役割ごとにスプレッドシートを分けました。

• 利用者の基本情報を管理するスプレッドシート
• 日々の記録を入力するスプレッドシート
• 担当者や区分などのマスターデータ
• 必要な情報をまとめて表示する閲覧・集計用スプレッドシート

それぞれのデータには、重複しない管理番号を付けています。この管理番号をキーにして、別の表にある氏名、登録日、担当者などの情報を関連付けます。

このように分けておくと、基本情報は基本情報のシート、日々の記録は記録用のシートというように、入力する場所を整理できます。

別のスプレッドシートを読み込むIMPORTRANGE関数

別のGoogleスプレッドシートにある表を読み込むときに使うのが、IMPORTRANGE関数です。

=IMPORTRANGE("スプレッドシートのURL","シート名!A:Z")

1つ目の引数には、参照元となるスプレッドシートのURLを指定します。2つ目の引数には、読み込みたいシート名とセル範囲を指定します。

たとえば、参照元のスプレッドシートに「利用者マスター」というシートがあり、A列からZ列まで読み込みたい場合は次のようになります。

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/スプレッドシートID/edit","利用者マスター!A:Z")

初めて参照するスプレッドシートの場合は、セルに「#REF!」と表示されます。セルを選択して「アクセスを許可」をクリックすると、参照元のデータが読み込まれます。

一度接続を許可すれば、参照元の表が更新されたときに読み込み先にも反映されます。

外部データ取込用のシートを用意する

IMPORTRANGE関数は、VLOOKUP関数の中に直接書くこともできます。ただ、今回は外部データを読み込むための専用シートを用意しました。

たとえば「取込_利用者マスター」というシートを作り、そのA1セルにIMPORTRANGE関数を入力します。これで参照元の表が、このシート内にそのまま展開されます。

専用シートを用意しておくと、正しく読み込めているか確認しやすく、数式も短くなります。参照元の列構成が変わった場合の原因調査もしやすくなります。

読み込んだ表をVLOOKUP関数で検索する

次に、IMPORTRANGE関数で読み込んだ表から、必要な情報をVLOOKUP関数で取得します。

=VLOOKUP(A2,'取込_利用者マスター'!A:Z,3,FALSE)

この数式では、現在のシートのA2セルに入力された管理番号を「取込_利用者マスター」シートのA列から検索し、見つかった行の3列目にある情報を表示します。

• A2：検索したい管理番号
• ‘取込_利用者マスター’!A:Z：検索対象の表
• 3：検索範囲の左端から数えた取得列の番号
• FALSE：管理番号が完全に一致する行を検索

たとえばA列が管理番号、B列が氏名、C列が登録日であれば、列番号に「2」を指定すると氏名、「3」を指定すると登録日を取得できます。

管理画面側では管理番号だけを入力し、氏名や担当者などを自動的に表示するようにしておけば、同じ情報を何度も手入力する必要がありません。表記ゆれや入力ミスの防止にもなります。

IMPORTRANGEとVLOOKUPを直接組み合わせる方法

外部データ取込用のシートを作らず、2つの関数を直接組み合わせることもできます。

=VLOOKUP(A2,IMPORTRANGE("スプレッドシートのURL","利用者マスター!A:Z"),3,FALSE)

数式だけを見ればこちらの方がコンパクトですが、同じIMPORTRANGEを多くのセルで繰り返すと、どの外部データを参照しているのか分かりにくくなります。

個人的には、ある程度の規模になる場合は外部データ取込用シートを作り、そのシートをVLOOKUPで参照する構成の方が管理しやすいと思います。

外部データを読み込んだシートは非表示にする

IMPORTRANGE関数で読み込んだシートは、普段の入力作業では直接操作しません。そのまま表示しておくと、入力担当者が誤って編集しようとしたり、どのシートを使えばよいのか迷ったりします。

そこで、外部データ取込用シートのタブを右クリックし、「シートを非表示」を選択します。

非表示にしてもVLOOKUP関数からは問題なく参照できます。入力担当者には必要な入力画面と閲覧画面だけを見せられるため、スプレッドシート全体が分かりやすくなります。

ただし、シートの非表示はセキュリティ機能ではありません。スプレッドシートを編集できるユーザーは、非表示のシートを再表示できます。見せてはいけない情報がある場合は、非表示だけに頼らず、ファイル自体の共有範囲や権限を分ける必要があります。

作るときに気を付けたいポイント

• 各データに重複しない管理番号を付ける
• VLOOKUPで検索する管理番号を検索範囲の一番左の列に置く
• 参照元の列を追加・削除した場合は、VLOOKUPの列番号を確認する
• シート名を変更した場合は、数式内のシート名も確認する
• 個人情報などを扱う場合は、Googleドライブの共有範囲と編集権限を慎重に設定する

今回は電子カルテのような管理画面という依頼でしたが、Googleスプレッドシートは医療機関向けの電子カルテそのものではありません。実際に扱う情報の内容や必要な安全性に応じて、専用システムを使うべきかも含めて判断する必要があります。

まとめ

IMPORTRANGE関数とVLOOKUP関数を組み合わせることで、複数のGoogleスプレッドシートに分かれた情報を、管理番号などをキーにして一つの画面へまとめられます。

大がかりなシステムを開発するほどではないものの、複数の表を関連付けて管理したい場合には便利な方法です。

オンラインの管理データベースとしてGoogleスプレッドシートを使いたい方の参考になれば幸いです。