業務データは Access に格納されていることがあります。
Excel で管理しているデータと、Access で管理しているデータを組み合わせて処理をしたいとき、Access のデータを Excel ブックとしてエクスポートしたり、Excel から Access データベースに接続してデータを参照して利用したりします。
どちらを使うのかを判断する場合のポイントの 1 つは、Access データベースで最新の情報が管理されているとき、その更新を Excel にも反映したいかどうかです。
たとえば、下図のような Excel で管理している顧客への訪問履歴があるとします。
ここには、日報を元に、社員の情報は社員番号が、顧客の情報は顧客 ID で管理されています。
集計や分析をするにあたって、[社員名] と [顧客名] の列を追加して表示したいと思います。
「販売管理.accdb」という Access データベースに、社員番号や社員の名前などの情報を管理している「T_社員」テーブルや、顧客情報を管理している「T_顧客」テーブルがあるとします。
新しく社員が入ったり、新しい顧客との契約が開始されたりしたら、このデータベースにデータが追加されていく、すなわち、ここにマスターデータがあります。
Access のテーブルを Excel ブックで使えるようにエクスポートをすることもできますが、この場合、エクスポートをしたときのテーブルのコピーであるため、データが追加されてもこれは Excel ブックで反映されません。
Access へのデータの追加や変更、削除などが、Excel ブックにも反映されるようにするには、データ接続を使用します。
Access のデータベースに接続する
ここでは、「販売管理.accdb」の「T_社員」テーブルに接続して、最新の情報をテーブルとして表示します。
-
リボンの [データ] タブの [外部データの取り込み] の [Access データベース] をクリックします。
-
[データファイルの選択] ダイアログ ボックスで、接続先となる Access データベースを選択して、[開く] をクリックします。
-
[テーブルの選択」 ダイアログ ボックスで接続先とするテーブル (ここでは T_社員) を選択して、[OK] をクリックします。
-
[データのインポート] ダイアログ ボックスで、[テーブル] が選択されていることを確認し、出力先となるシート ([既存のワークシート] または [新規ワークシート] を選択して、[OK] をクリックします。
-
指定した位置に接続先のデータがテーブルとして表示されます。
テーブル名は、データベース名などから自動的に設定されているため、わかりやすい名前に変更します。
また、このブックで使用する必要のないテーブルの列は削除してもかまいません。
-
このテーブルの接続を確認したり、接続を解除したりするには、リボンの [データ] タブの [接続] グループの [接続] をクリックして、[ブックの接続] ダイアログ ボックスを表示します。
接続名 (データベース名) を選択して、[プロパティ] をクリックすると、[定義] タブで接続先のテーブルなどを確認できます。
(確認後は、[キャンセル] や [閉じる] で、ダイアログ ボックスを閉じます)
-
たとえば、VLOOKUP 関数を使った数式で、データ接続によって表示されているテーブルの情報を参照して、社員名などを表示できます。
-
(あたりまえですが) VLOOKUP 関数によって参照しているテーブルに含まれてない社員 (社員番号) の名前は表示されません。
データを更新する
マスター データは Access で管理しています。
たとえば、新しい社員の情報は「T_社員」テーブルに追加します。
-
データの接続の設定後に閉じた Excel ブックを次に開いたとき、外部データへの接続は無効になっているため、下図のようなメッセージが表示されます。
[コンテンツの有効化] をクリックします。
-
最新の状態に更新したいテーブルの中にアクティブ セルをおいて、(テーブルを選択して)、[テーブル ツール] の [デザイン] タブの [外部のテーブル データ] グループの [更新] をクリックします。
テーブルが更新され、Access のテーブルに追加されたデータが表示されます。
-
Access 側でデータの追加が行われれば、Excel ではその最新のテーブルのデータを使うことができます。
Access データベースに直接接続できない、という方もいらっしゃると思います。たとえば、そんなときは CSV ファイルでデータをもらっていたりしませんか?
CSV ファイルにも、上記の Access データベースと同じように接続できます。
「今の状態のデータ」を使いたいのか、「更新されていくデータ」を使いたいのかによって、データのコピーを使うべきか、接続するべきかを選択しましょう。
石田かのこ