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の扱えるデータ数の上限に当たったりしますが、バックエンドをデータベースにすることで、より手軽にデータ分析を行えるようになると思います。

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

では、また。