2013年4月12日

ExcelからPostgreSQLにODBC接続してピボットテーブルを作成する

Excelのピボットテーブルは、データ集計・分析を手軽に行う方法として、非常によく使われているツール・機能であると言えるでしょう。



今回は、ExcelからPostgreSQLに接続して、データベース内のデータを用いてピボットテーブルを作成する方法をご紹介します。サンプルとして以下のような項目とデータを含む「注文情報」というテーブルを使ってピボットテーブルを作成してみます(PostgreSQLのデータベース内に作成してあるテーブルで、上記のピボットテーブルはサンプルデータから作成したものです)。



今回の環境はWindows7とExcel 2010ですが、多少バージョンが違ってもほぼ同じようにできると思います。

■手順の概要


ExcelからPostgreSQLに接続してピボットテーブルを作成する基本的な手順は以下の通りです。
  • PostgreSQL用のODBCドライバをインストールする
  • ODBCデータソースの設定を行う
  • Excelで外部データソースに接続する
  • Excel上でピボットテーブルを作成する
それでは順を追って見てみます。

■PostgreSQL用ODBCドライバのインストール


PostgreSQLのODBCドライバをインストールしますが、まずはインストールする前に適切なODBCドライバを選択しなければなりません。

なぜなら、PostgreSQLのODBCドライバには64ビット版と32ビット版があり、Windows上では、64ビット版のODBCの設定と32ビット版のODBCの設定は、それぞれ別に管理されるからです。

ODBCを外部データソースとして使用するExcelには32ビット版と64ビット版があり、Excelは、Excelのバージョンに応じたODBCの設定を参照します。つまり、64ビット版のExcelは64ビット版のODBCの設定を参照し、32ビット版のExcelは32ビット版のODBCの設定を参照するのです。

(選択を間違えると、ODBCドライバの設定をしているのに、Excelからは外部データソースが見えない、という事象が発生します)

OSとExcelとODBCのバージョンは、以下のような組み合わせが考えられます。
  • Windows7 64ビット版、Excel 64ビット版、ODBCドライバ 64ビット版
  • Windows7 64ビット版、Excel 32ビット版、ODBCドライバ 32ビット版
  • Windows7 32ビット版、Excel 32ビット版、ODBCドライバ 32ビット版
Windows7とExcelのバージョンは、それぞれ以下の方法で確認します。

・Windows7のバージョンの確認方法

スタートメニューから「コンピュータ」を選択し、開いたウィンドウのメニューから「システムのプロパティ」をクリックすると、システムの情報を確認することができます。



その中の、「システムの種類」に「64ビット オペレーティングシステム」とあれば64ビット版のWindows7です。

・Excelのバージョンの確認方法

メニューから「ファイル」を選択し、「ヘルプ」をクリックして表示すると、Officeのバージョン情報を参照することができます。



「バージョン」の項目に「(64ビット)」という表記があれば、64ビット版のExcelであることが分かります。

自分が必要とするバージョンのODBCドライバを確認したら、PostgreSQLのサイトからODBCドライバをダウンロードします。

http://www.postgresql.org/ftp/odbc/versions/msi/

ZIPファイルがたくさんありますが、ファイル名に「-x64」と付いているのは64ビット版のドライバで、付いていないのが32ビット版です。

psqlodbc_09_01_0200.zip

のようなファイル名が付いています。「09_01」はPostgreSQLのバージョン9.1に対応していることを示しており、「0200」はその後のリリースバージョンです。

基本的には、使用するPostgreSQLのメジャーバージョンと同じもの、あるいはそれよりも新しいものを選択しておけば良いでしょう。

ZIPに含まれているmsiファイルを起動すればインストールは簡単に実行できます。

■ODBCデータソースの作成


64ビット版Windows7上で64ビット版ODBCの設定を行う場合、また32ビット版Windows7上で32ビット版ODBCの設定を行う場合には、「コントロールパネル→システムとセキュリティ→管理ツール」で管理ツールを起動し、「データソース(ODBC)」をダブルクリックするとODBCの「ODBC データソース アドミニストレータ」のダイアログを起動することができます。

但し、64ビット版Windows7上で32ビット版ODBCの設定を行う場合には、odbcad32.exeというプログラムを指定して起動しなければなりません。エクスプローラを開いて以下のプログラムを直接起動してください。
C:\Windows\SysWOW64\odbcad32.exe
ODBCデータソースアドミニストレータを起動したら、「追加...」をクリックして「データソースの新規作成」の中から「PostgreSQL Unicode」を選択、「完了」をクリックします。

>



すると、PostgreSQLの接続情報を設定するダイアログが表示されますので、PostgreSQLへの接続に必要な情報を入力します。(「テスト」をクリックすると、実際に接続を行って、入力した設定で接続できるかどうかを確認することができます)

■ExcelからODBC経由で接続してピボットテーブルを作る


最後に、ExcelからODBC経由でPostgreSQLに接続してピボットテーブルを作成します。

まず、Excelの「データ→その他のデータソース→データ接続ウィザード」を選択します。



データ接続ウィザードで、データソースの種類として「ODBC DSN」を選択し、ODBC接続の中でも先ほど設定したPostgreSQLのデータソースを選択します。




データソースの中のテーブルおよびビューの中から、ピボット作成に使用するものを選択します。ここでは、「注文情報」というテーブルを使用します。



最後に「完了」をクリックすると、「データのインポート」の方法の選択を求められます。今回はピボットテーブルを作成しますので、「ピボットテーブル レポート」を選択します。



空のピボットテーブルとレポートに使用するフィールド名が表示されますので、分析に必要な軸を行ラベル、列ラベル、値の各項目に追加します。(ここからは通常のExcelのピボットテーブルの作成と同様です)



以下の例は、行ラベル(縦軸)に顧客の地域、国を取り、列ラベル(横軸)に顧客の業界を設定し、注文数を値として作成したピボットテーブルです。

■まとめ


以上、今回はExcelからPostgreSQLにODBC接続してピボットテーブルを作成する方法をご紹介してきました。

Excelのピボットテーブルを使った分析はデータ分析の基本中の基本と言えます。すべてのデータをExcelに取り込んで分析作業をしようとすると、ファイルサイズが大きくなったり、Excelの扱えるデータ数の上限に当たったりしますが、バックエンドをデータベースにすることで、より手軽にデータ分析を行えるようになると思います。

データ分析に興味のある方は、ぜひ試してみていただければと思います。

では、また。

1 件のコメント:

  1. ExcelでMySQL,PostgreSQLなどのデータを取得/更新するなら、ExcelDBToolをお勧めします。
    データ取得、更新、削除することだけではなく、複数のSQLを実行して、結果を
    一括Excelの各シートに出力することもできます。データ作成の機能を使って、
    氏名、住所、電話など60種類以上のダミーデータも作成できます。
    作成したデータを直接DBに登録して、シート名とテーブル名が一致すれば、
    一括で複数テーブルのデータもExcelに取得できます。
    詳しくは下記サイトをご参照ください。

    http://www.superdbtool.com

    MySQL ODBC 5.2のインストールでエラーが出た場合、下記の記事をご参考ください。
    http://superdbtool.blog.jp/archives/922402.html

    返信削除