検索
連載

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

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

Share
Tweet
LINE
Hatena
「Tech TIPS」のインデックス

連載目次

対象ソフトウェア: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アドイン」を追加すること。

操作方法

●1.データベースへの接続を設定する

 データを読み込む既存の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]ボタンをクリックし、データベースへの接続を確立する。

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

 接続を確立したら、クエリー・ウィザードを利用して、データを抽出するためのクエリーを定義する。これにより、テーブル内の特定の列や、特定の条件を満たすレコードの情報だけを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を基点に抽出されたデータが出力される。

データベースへのクエリーの実行:Excelへのデータの取り出し
データベースへのクエリーの実行:Excelへのデータの取り出し
抽出したデータの取り出し先を指定する。ここでは既存のワークシートを指定した。

 以上でデータが抽出され、ワークシートに読み込まれる。こうしてワークシートに読み込まれたデータはExcel上で自由に編集することができる。

抽出されたデータ
抽出されたデータ
データベースから抽出されたデータがExcelのワークシートに読み込まれる。以後は通常のExcelのデータとして編集したり、グラフを作成したりすることが可能である。

「Tech TIPS」のインデックス

Tech TIPS

Copyright© Digital Advantage Corp. All Rights Reserved.

ページトップに戻る