MySQL上のデータをExcelのピボットテーブルでクロス集計する手順
MySQL上のデータにExcelからODBC接続して、
ピボットテーブルでクロス集計する手順を紹介したいと思います。
Excelのピボットテーブル機能は便利ですが、
複数人でいろいろな角度から集計していたりする場合に、
元データを保存したマスタExcelファイルの管理に手間やミスが発生したり、
古いバージョンのExcelだと行数の上限(65536行)以上の集計ができなかったり、
というような問題が発生するのではないかと、思います。
ここで紹介する手順では、
・サーバ上のMySQLに、マスターデータを保存する
・PC上のExcelから、MySQLに接続してデータを取り込みピボットテーブル作成
という構成を想定します。
サーバ上のMySQLの環境と対象テーブル・データは存在している前提で、
クライアントPC側の設定の手順を示します。
手順は以下の流れで行います。
- MySQLのODBCドライバをインストール
- ODBCの接続を作成する
- Excelでピボットテーブルを作る
MySQLのODBCドライバをインストール
MySQLのサイトからODBCドライバをダウンロードする
MySQL Download Connector/ODBC
URL: http://dev.mysql.com/downloads/connector/odbc/
ファイル: mysql-connector-odbc-5.2.5-win32.msi
ここでは32bit版を利用していますが、
構築する環境に適したバージョンを選んでください。
ダウンロードしたファイルを開きダブルクリックして、
インストーラの指示に従ってインストールを実行します。
# たいていの場合は、デフォルトの設定で問題ないはず。
ODBCの接続を作成する
「スタート」→「管理ツール」→「データソース(ODBC)」
と選択して、「ODBC データソース アドミニストレータ」を開きます。
「追加」を押下し、「データソースの新規作成」を開きます。
「MySQL ODBC 5.2 Unicode Driver」を選んで「完了」を押下します。
「MySQL Connector/ODBC Data Source Configuration」で、
接続したいMySQLデータベースの、以下の各設定項目を入力し「OK」を押下すると、
MySQLへのODBC接続が作成できます。
※各設定項目は次のとおり。
Data Source Name: データソースを識別するための名称、任意の名称でよい
TCP/IP Server: MySQLサーバのIPアドレス
Port: MySQLサーバの接続ポート番号（変更していなければ既定値）
User: MySQLDBのユーザ名
Passowrd: MySQLDBのパスワード
Database: MySQLDBのデータベース名
Excelでピボットテーブルを作る
Microsoft Excelを起動します。
# 非常に古いですがExcel2002SP3使っています。
# まぁ、古くても大丈夫ということで^^;
「データ」→「外部データの取り込み」→「データの取り込み」と選択し、
「データファイルの選択」で「新しいデータソースへの接続.odc」をダブルクリックします。
「データ接続ウィザード」で「その他/詳細」を選択する。
「データリンク プロパティ」の「接続」タブを開き、
「データソース名を使用する」選択し、
作成したODBC接続の「Data Source Name」を指定して、「OK」をクリックします。
「データ接続ウィザード」でピボットテーブルに利用したいテーブルを選び、
「次へ」をクリックします。
次の画面で「完了」をクリックします。
# 必要に応じて、ファイル名や説明を指定してください。
「データのインポート」画面で「ピボットテーブル レポートの作成」をクリックします。
「ピボットテーブル/ピボットグラフ ウィザード」で「完了」をクリックします。
後は、通常のピボットテーブル同様の操作データを参照できます。
# この画面イメージは、
# 売上番号, 販売日, 商品点数, 売上合計, 利益合計
# というカラムがあるテーブル元にピボットテーブルを表示している例です。
データベースのデータが更新された時は、
「データ」→「データの更新」を選んで、最新データを取得します。
この方法を使うと、
Excel2002の上限(65536行)を超えるレコードのテーブルでも、
ピボットテーブルが利用可能になります。
が、データが大きくなるとメモリ不足になったりするので、
MySQL側でカラムやレコードを絞り込んだ、Viewを作った方が現実的かと思います。
# 試したものは60カラムx50万レコード程のデータ量で試したところ、
# 最初のデータ取得及びデータ更新に時間がかかるものの、
# ピボット操作は、現実的なスピードで利用できていました。
# Core2Duo, メモリ2GB, WindowsXP, Excel2002という環境でも、
ピボットテーブルでいろいろな切り口の集計を行っていて、
その切り口ごとにExcelファイルが存在していたとしても、
「データの更新」を選べば、元データを最新に揃えられるのは、
「数字が合わない…」というような無駄な混乱が減って、便利ではないかと思います。
