![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOa1_oSH8kFHIOEfdKVgjq3EeZKYhOZWpoMeI7Aw-q7Qfteig1gWSVPpDIpAxhCg_VWaBzjFKPGUO1eoL7OSEu1R6a-o0QpdfpSgIMoWamhM7Z6rLEScbdPUHFBKDcqUJ1jl4Sf50d6Vk/s320/excel08.png)
今回は、ExcelからPostgreSQLに接続して、データベース内のデータを用いてピボットテーブルを作成する方法をご紹介します。サンプルとして以下のような項目とデータを含む「注文情報」というテーブルを使ってピボットテーブルを作成してみます(PostgreSQLのデータベース内に作成してあるテーブルで、上記のピボットテーブルはサンプルデータから作成したものです)。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUayQvBv-wNK6FlHCHHExB_1yv0WLSb3sY-O4L5SksAHA5bgTeUyCwVpe7i7r6e0x4LuvwSFEYjMmhJ7hsl2PXJzSVz2_G9WXIXQpKFqMFU2jxBjplQYnvLrJxabEzyUOR6pBJfhXimJM/s320/excel00.png)
今回の環境は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のバージョンの確認方法
スタートメニューから「コンピュータ」を選択し、開いたウィンドウのメニューから「システムのプロパティ」をクリックすると、システムの情報を確認することができます。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSjH5oWsovYxWJWGptTxwWWtTlb7xTKcA8oXIR1SjPqXUfYeNEYDXQnWbXmEBQhATQYqgjwqt-M3ZafdPY16mSJD5vq_T-bRyLbLyBV81IukPUsZ9HkpBoK20L3T16cfxAw8ePrXv04_Y/s320/win7_version1.png)
その中の、「システムの種類」に「64ビット オペレーティングシステム」とあれば64ビット版のWindows7です。
・Excelのバージョンの確認方法
メニューから「ファイル」を選択し、「ヘルプ」をクリックして表示すると、Officeのバージョン情報を参照することができます。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfVVdN6zPG_rjeCqMqkAg4SNdffuIjZAfKvAh-5chXWtM4FvO0tUlszfHwBANYa0zkHf2DHA4vxCorWsQJT6BNtx1ZG60PEHiKRGsotmjLaFPDiAumOofUIX0mWqvMWeviCIAgAMbVWAE/s320/excel_version1.png)
「バージョン」の項目に「(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.exeODBCデータソースアドミニストレータを起動したら、「追加...」をクリックして「データソースの新規作成」の中から「PostgreSQL Unicode」を選択、「完了」をクリックします。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOYk6SFeCmz_KKNqc-HNA5u-kaCRW5P4Xoh_7VxlDU2eyHy-qTJEEoCP5hGyitvCyvMh3bO08Z2qbBxnLwU2rfVrZ3Tl8UFmDnEErq4GzfhEh29y4KuXvF_mLMyPLMMstp2H-B_edq59o/s320/odbcad01.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTPTj_XLby8ln_sq1gapGGpJndYnTD2TZ4g1JANCwgLLBIyToSZ1I1UhCn-FMOrEQ5tcQwSTKkkRNowcrnfr3_Ssb_0w65wh9dI2D2vLVAxVeb9lWZyx7Ck5aF7GQgRMOkHibyReVhTdI/s320/odbcad02.png)
すると、PostgreSQLの接続情報を設定するダイアログが表示されますので、PostgreSQLへの接続に必要な情報を入力します。(「テスト」をクリックすると、実際に接続を行って、入力した設定で接続できるかどうかを確認することができます)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhq5ufvbnElqOwe6r2JMLLCTrdiNZPUQ9XajaoBw4SSmkJFmCdCj3E3ODs2V3xWZNxe5aHyqMkbwZ_vxi8xMCyJA0xdahn6U7ndwhp3oln1Hoh7h15KLtg_EQsF3PUOMIey8UIUJnqsPs4/s320/odbcad03.png)
■ExcelからODBC経由で接続してピボットテーブルを作る
最後に、ExcelからODBC経由でPostgreSQLに接続してピボットテーブルを作成します。
まず、Excelの「データ→その他のデータソース→データ接続ウィザード」を選択します。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1mosak2FxcLnRruWgTa4sLEJwrv_KLvCardmmpVIg40KoBXmplflXBVQSHXplMezSktIzOVNPiRb6cyVJPMCaXmE6DjhgnmvC1X7HDbWSPWfFzdUpYnUODc2kRExryfv6gvye1_x3UxU/s320/excel01.png)
データ接続ウィザードで、データソースの種類として「ODBC DSN」を選択し、ODBC接続の中でも先ほど設定したPostgreSQLのデータソースを選択します。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYJAROmrnT6P-6qTq2BcsTJdCi2g1E-64yCpn4t0UNjegoSAZkWACRKbEGwgBpVotMoEFAvtqZPBCYpIRdhXCS4eHnlO0Hl2cHk5HKlJwMhp4m51TL_Lzv56VdJN1buXt9trLzJ8aNEnk/s320/excel02.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivDJCqM_rPYvN34UwikqrSMAuVZtR_sHXX6tfNAyD1lCLbpDEr4cyTLnzFS_DzPFlfpJmS08HzoshuZF9KbgbNdSRs5XfcfCdW31ma1tX7rT8pJAE9Z299nUeiZ-oSN3LA05kxmfQplG8/s320/excel03.png)
データソースの中のテーブルおよびビューの中から、ピボット作成に使用するものを選択します。ここでは、「注文情報」というテーブルを使用します。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCYf5PjALooVNEOWdNNXuuS6u5d1Id0yDZiKiWP6_mRRk1kUWgbmSCpFhYOMkeqGXOved7J-ydi0bTdEOdc1GyVgu4lHyli-XUEnQGkIY_yi9xwc9VOvFr1x9iVsuXSvYcQcAzBajm2zI/s320/excel04.png)
最後に「完了」をクリックすると、「データのインポート」の方法の選択を求められます。今回はピボットテーブルを作成しますので、「ピボットテーブル レポート」を選択します。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGyfXscd2Ek5r1kzeuaYzct674A_Sp_3CDuMnT_wqKKK9DGKdoUWIkq7asgMC3lWB-MWgx8xPM7CVjEF9ZDjQluErABc7tsXZJgXemzf1s2I4lco2JVcrJyoaaBnfQdG0UKpG6zt_eLPo/s320/excel06.png)
空のピボットテーブルとレポートに使用するフィールド名が表示されますので、分析に必要な軸を行ラベル、列ラベル、値の各項目に追加します。(ここからは通常のExcelのピボットテーブルの作成と同様です)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1mDWObuowO1kVW9CpnTWsz-Qt-fiurEro_h_VEKGzCoJUQKtb83fKE-tOALceDI9crd53oULW3vCAguv8EWCX2DFpjWcTsNIY6hPuA1X8KOd75c1Stcw7gHQjfpN2xH-y1hOOOKMUFdw/s320/excel07.png)
以下の例は、行ラベル(縦軸)に顧客の地域、国を取り、列ラベル(横軸)に顧客の業界を設定し、注文数を値として作成したピボットテーブルです。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOa1_oSH8kFHIOEfdKVgjq3EeZKYhOZWpoMeI7Aw-q7Qfteig1gWSVPpDIpAxhCg_VWaBzjFKPGUO1eoL7OSEu1R6a-o0QpdfpSgIMoWamhM7Z6rLEScbdPUHFBKDcqUJ1jl4Sf50d6Vk/s320/excel08.png)
■まとめ
以上、今回はExcelからPostgreSQLにODBC接続してピボットテーブルを作成する方法をご紹介してきました。
Excelのピボットテーブルを使った分析はデータ分析の基本中の基本と言えます。すべてのデータをExcelに取り込んで分析作業をしようとすると、ファイルサイズが大きくなったり、Excelの扱えるデータ数の上限に当たったりしますが、バックエンドをデータベースにすることで、より手軽にデータ分析を行えるようになると思います。
データ分析に興味のある方は、ぜひ試してみていただければと思います。
では、また。
ExcelでMySQL,PostgreSQLなどのデータを取得/更新するなら、ExcelDBToolをお勧めします。
返信削除データ取得、更新、削除することだけではなく、複数のSQLを実行して、結果を
一括Excelの各シートに出力することもできます。データ作成の機能を使って、
氏名、住所、電話など60種類以上のダミーデータも作成できます。
作成したデータを直接DBに登録して、シート名とテーブル名が一致すれば、
一括で複数テーブルのデータもExcelに取得できます。
詳しくは下記サイトをご参照ください。
http://www.superdbtool.com
MySQL ODBC 5.2のインストールでエラーが出た場合、下記の記事をご参考ください。
http://superdbtool.blog.jp/archives/922402.html