Excelでデータ入力時に他のテーブルの情報を使う方法

Excelで介護支援記録管理システムを作ってみる～3日目（前回の続き）

はて？と考えたうえで、記録テーブルへの入力を少しでも効率化するため、私はデータの入力規則を使う方法を選ぶことにしました。

非常にポピュラーで便利な手法ですが、入力規則のリストに含まれる項目が多いと、その中から該当するものを探し出すことが厄介なのが欠点です。

もちろん、Excelの強力なVBAを使い、ユーザーフォームなど分かりやすく効率的な入力手段を組み込むこともできるので、この際、ExcelのVBAを勉強していく形で、このブログをお読みくださっている皆さんに方法を伝えてみることも考えましたが、そうなると今後はVBA一辺倒の話となってしまいます。多くの方には参考にならないかも知れません。

また、ExcelVBAを使ったシステムの開発経過をお伝えするくらいなら、始めからAccessとAccessのVBAを使ってシステムを作り上げていく手法や手順をお伝えしたほうが、VBAに関心を持つ方々にはプラスになると考えたのです。

もちろん、ここからAccessの話に変えていくつもりはありませんが、そう考えた理由は、データベースシステムをVBAで開発しようとすると、Excelは極めて非効率と思うからです。

以前投稿した「Excelの限界」の中で触れたとおり、表計算ソフトとして出発したExcelはセル中心の発想で動いているため、複数の項目情報が一まとまりになったレコード（データ）という発想を持っていません。

このため、データベースを処理するときのVBAはどうしても複雑化してしまうのです。

一例を挙げると、ユーザーフォームを使って何かのテーブルに新しいレコードを追加するような処理です。

このときExcelは、テーブル直下の先頭のセル位置を探し出し、同じ行のレコードを構成する項目（セル）ごとに、ユーザーフォームに入力された情報を一つずつコピーしながら、テーブルの所定位置へ入力していく、という流れで処理をします。この一連の処理にかなりのVBAコードを記述する必要があるのです。また、繰り返し処理を多用するため、処理スピードも遅くなります。

一方、Accessでは関連するテーブルがプロパティでユーザーフォームと直接つながっており、さらにユーザーフォームの各項目に情報を入力すること自体が、テーブルの最下行にレコードを追加することになるため、極端に言うとVBAコードはたった１行で済んでしまいます。

というわけで、ExcelのVBAを勉強する時間を惜しむ理由もありますが、データベース操作にExcelVBAは使わず、敢えて通常、誰もが使っているExcelの機能だけでこの先進めていくことにしました。

方針が決まったので、まず、記録データシート上の記録テーブルの利用者氏名について、利用者データシートの利用者テーブルから、データの入力規則を使いリストから入力できるようにします。

方法は、まず記録テーブルに既に入力済の利用者氏名部分をドラッグで選択します。その後データメニューのデータの入力規則から、データの入力規則を選び、設定→入力値の種類はリストを選択。元の値は利用者データシートの利用者名部分をドラッグして範囲を選択します。

そしてOKをクリックしようとしたとき、おっと！そんなに簡単ではないことに気づきました。

この利用者テーブルは、利用者DBという別のExcelブックの中にある利用者テーブルをリンクという形で引っ張ってきているわけですが、実際のところ、このテーブルには現在利用中の方以外にも、既に退去して利用を終了された方も含んでいるようなケースが多いのではないでしょうか。そうなると、その方たちまで入力規則の範囲に入れてしまうことはできません。

職員も同様で、職員テーブルには既に退職したり異動した職員も含まれていることでしょう。

そのような状況を前提に対応を考えなくてはならないと思いました。

ぱっと思い浮かんだのは、データを引っ張ってきたリンク先テーブルにフィルターをかけて、現在利用中の方に絞ってみることです。しかし、残念ながら利用者テーブルは利用中の方のみの表示となるものの、記録テーブルに入力するさい表示される利用者リストには、相変わらず全員分の名前が出てきます。

この原因は、Excelのフィルターが、単に該当しないレコード（データ行）を非表示にしているにすぎないからです。ExcelではAccessのクエリ機能のように、本体テーブルから指定の条件を満たすレコードだけを抽出したクエリを簡単に作成することができません。

そこで、次回以降では、関数を使ってExcelでクエリを疑似的に実現する方法を考えることにします。

>次回の投稿を読む

Excelのテーブルから抽出したデータを詰めて表示させる

>前回の投稿を読む

Excelのフォーム機能は効率的ではない!?