WindowsでExcelと外部データベースとを連携させる：Tech TIPS

SQL Server（MSDE）やOracle、MySQL、PostgreSQLなどのデータベース・サーバを、業務システム上で使用するケースは少なくない。Excelには、データベースからデータを抽出する機能が用意されている。これを利用すれば、さまざまなデータベースからデータを取り出し、Excelで加工できる。

» 2003年05月31日 05時00分公開

[山田祥寛，著]

連載目次

対象ソフトウェア：Excel 2000／Excel 2002

解説をスキップして操作手順を今すぐ読む→

解説

　業務システムなどでは、データリソースの多くが「データベース・サーバ」に格納されている。そして、一般的には何らかのアプリケーションを介して、オンライン画面上でデータを参照したり、ダウンロードしたりできるようになっている。

　しかし、ときとして、あらかじめシステム部門によって提供されているアプリケーション（システム）の機能を超えて、自由にデータを参照したいというケースは少なくない。具体的には、Excelを利用して、データを加工したり、グラフを作成したりといった要求が多いだろう。そこで本稿では、MSDE（SQL Serverと互換性を持つデータエンジン。同時アクセス・ユーザー数やデータ容量が限られるほかは、ほぼSQL Serverと同等の機能を持つ）を例に、データベースからデータを抽出し、ワークシート上に表示するサンプルをご紹介することにする。

　なお、本稿では割愛するが、MSDEはあらかじめ環境上にインストールされている（もしくはネットワーク上に存在する）ものとする。MSDEを始めとするデータベースのインストール方法や、環境設定の方法などについては、Insdier.NETで公開されている記事「ASP.NETで学ぶVisual Studio .NETの魅力」や専門書籍などなどを参考にしてほしい。

　本稿で紹介するサンプルを利用するためには、データベースへの接続を担う「Microsoft Query」の機能がExcelに組み込まれていることが前提となる。「Microsoft Query」が組み込まれていない場合には、コントロールパネルの［プログラムの追加と削除］からOfficeの項目を選択し、必要なモジュールの追加を行ってほしい。Microsoft Queryは、インストーラの「Officeツール」グループに含まれている。また、モジュールの追加後は、Excelの［ツール］－［アドイン］メニューから「MS Queryアドイン」を追加すること。

操作方法

●１．データベースへの接続を設定する

　データを読み込む既存のExcelブックを開くか、新しいブックを開き、メニューバーから［データ］－［外部データの取り込み］－［新しいデータベースクエリ］を選択する（ブックをまったく開いていない状態では、このメニュー項目は無効になっている）。

　すると［データベースの選択］ダイアログが表示される。このダイアログには、すでに作成済みのデータ・ソースが一覧表示されるので、既存のデータ・ソースをアクセスするならその中から対象となるデータ・ソースを選択する。新規にデータ・ソースを作成する場合は、一覧にある「＜新規データソース＞」を選択して［OK］ボタンをクリックする（今回は新規作成するものとして説明を進める）。すると次の［新規データ・ソースの作成］ダイアログが表示される。

［新規データソースの作成］ダイアログ
データベースからデータを取り出すには、最初に目的のデータベースに接続する必要がある。ここで接続先のデータ・ソースを作成する。
　 （1）データソース名。任意の名前を付ければよい。
　 （2）データベースをアクセスするために使用するドライバを選択する。
　 （3）このボタンをクリックすると、テーブル一覧などを取得するために指定したデータベースへのアクセスが実行される。ログオンが必要な場合には、ログイン・ダイアログが表示される。
　 （4）接続したデータベースに含まれるテーブルが一覧されるので、目的のテーブルを選択する。
　 （5）これをオンにすると、上で指定したユーザー名とパスワードをデータ・ソースの定義に記録しておき、次回からはこれらを指定しないでもデータベースにアクセスできるようになる。

　ここでは、名前を付けて新規データ・ソースを作成する。アクセスするデータベースの種類により、ドライバを選択する必要がある。デフォルトでは、Access、ODBC for ORACLE、SQL Serverなどを選択可能である。


	設定項目	設定値
	データ・ソース名	office（任意の値）
	ドライバ	SQL Server（MSDEの場合もこれを指定する。そのほかのDBの場合は適宜対応するドライバを選択する）
	接続	サーバ：localhost（MSDEサーバ名）
		ログインID：sa（ユーザー名）
		パスワード：（ブランク）
	標準のテーブル	任意のテーブル

　上記の例では、ローカル・マシン上にMSDEがインストールされているものと仮定している。ログインIDやパスワードはデフォルト状態のものを使用しているが、ネットワーク上で運用中のサーバーにアクセスする場合には、適宜、接続設定を管理者に確認してほしい。

　すべての入力が終わったら、［OK］ボタンをクリックし、データベースへの接続を確立する。

●２．データを抽出するためのクエリーを設定する

　接続を確立したら、クエリー・ウィザードを利用して、データを抽出するためのクエリーを定義する。これにより、テーブル内の特定の列や、特定の条件を満たすレコードの情報だけをExcelに取り込むなどが可能になる。

データベースへのクエリーの実行：列の選択
ここでは、クエリーの対象となる列を選択する。
　 （1）指定したテーブルのうち、使用可能なテーブルと列がここに一覧表示される。テーブル名がツリー表示され、ツリーを展開すると、テーブル内部の列が一覧される。
　 （2）（1）から列を選択して「＞」ボタンをクリックすると、その列がクエリーの対象として指定され（3）に表示される。（1）でテーブルを選択した場合には、テーブルに含まれるすべての列がまとめて指定される。「＜」ボタンで指定を解除することも可能。
　 （3）クエリー対象となる列。

　ここで、参照したいテーブルの列（フィールド）を選択する。（1）の部分にはテーブル一覧がツリー表示される。ツリーを展開すると、そのテーブルに含まれる列が一覧表示される。ここで任意の列（またはテーブル）を選択して（2）の［>］ボタンをクリックすることで、選択した列をがクエリ列として確定する。テーブル名を選択したときには、テーブル内部のすべての列が対象となる。

　抽出する列が決まったら、［次へ>］ボタンをクリックし、次にデータの抽出条件やソートキーを設定する。

データベースへのクエリーの実行：抽出条件の設定
一定の条件を満たすデータだけを抽出する場合には、ここで抽出条件を指定する。
　 （1）上のダイアログで指定した列が一覧表示される。条件を指定するには、まずはここから対象となる列を選択する。
　 （2）抽出条件を指定する。特定の値と一致する場合、特定の値以下の場合などを指定する。
　 （3）複数の条件を指定する場合には、ここでANDまたはORを指定する。

　この［データの抽出］ダイアログでは、抽出列の中から抽出条件を設定したいフィールドの名前を選択し、条件式を入力する（特定の値に一致する場合、特定の値より大きい場合などを指定できる）。抽出条件が設定されたフィールド名は太字に変更される。例えばこの画面では、price列の値のうち、2000より大きいものだけを抽出する指定を行っている。

　複数の条件を指定したければ、「AND」または「OR」を選択して、次の条件を指定する。

データベースへのクエリーの実行：並べ替え順序の設定
取得するデータの並べ替えを実行する場合には、ここでそのキーを指定し、昇順か降順かを選択する。
　 （1）並べ替えの対象とするキーを指定する。
　 （2）データを小さいものから大きいものへ順に並べ替える場合は「昇順」、逆順の場合は「降順」を選択する。

　抽出条件と並べ替えの設定が完了したら、最後に抽出したデータをどのように扱うかを設定できる。

データベースへのクエリーの実行：抽出データの処理の選択
抽出したデータをどのように扱うかを選択する。
　 （1）抽出したデータをExcelのシートに挿入する。今回の目的ではこれを選択する。
　 （2）「Microsoft Query」のウィンドウを開く。
　 （3）OLAPキューブ（データベースの部分集合）を作成する。
　 （4）今回の抽出条件を保存しておく場合にクリックする。

　抽出結果のデータをそのままExcelにインポートしてもよいし、Microsoft Queryと呼ばれるツールを介してより細かなクエリー条件の編集を行うことも可能だ。また、［クエリの保存］ボタンをクリックすることで、今回の抽出条件を保存しておき、後からこれを再利用したり、加工したりすることもできる。

　このうち今回は［Microsoft Excelにデータを返す］を選択する。すると次のような［Microsoft Excelへの外部データの取り出し］ダイアログが表示されるので、ここでは［既存のワークシート］を選択し、「=dbQuery!$A$1」をセットする。つまり、既存ワークシート「dbQuery」のセルA1を基点に抽出されたデータが出力される。