MySQL(またはMariaDB。以降MySQLで統一)のデータベースに蓄積されたデータを集計したり、グラフ化したいと思ったのだが、やはりこういう時に便利なのがExcelである。
私自身Excelにはあまり強くないのだが、ピボットテーブルやグラフなど、画面上で適当にポチポチやっていくだけでそれっぽいものができてしまう強力なツール。
今回は、Excelでデータの可視化をするために、MySQLのデータベースをいい感じに取り込む方法をまとめる。
使用するもの
以下のソフトウェアを使用する。
- Windows 10 (バージョン1903)
- Excel (Office 365 Solo バージョン1906)
- MySQL Connector/ODBC または MariaDB Connector/ODBC
もちろん、MySQLのデータベースが別途存在し、外部からの接続ができるように設定されている前提。
ODBCドライバのインストール
ExcelはODBCドライバを介してMySQLに接続するため、事前にインストールが必要である。
MySQLの場合「MySQL Connector/ODBC」、MariaDBの場合「MariaDB Connector/ODBC」がそれぞれのサイトで公開されているので、どちらかをダウンロードしてインストールする。
ダウンロードする際の32bitか64bitの選択は、「Excelが」どちらでインストールされているかによる。「OSが」32bitか64bitかではないので注意が必要。
確認するには、Excelを起動 → [アカウント] → [Excelのバージョン情報] を開く。
近頃はWindowsが64bitならOfficeも64bitが入るようになったが、以前のバージョンではデフォルトが32bitだったりする。VBAの互換性などのためにあえて32bitを導入している場合もあるので念のため確認しておいたほうが良い。
MySQLの場合
以下のサイトからダウンロードする。
Select OS Version で32bitか64bitかを選び、MSI Installerの「Download」を押す。
Oracleのアカウントを持っている奇特な人以外は、「No thanks, just start my download.」のリンクをクリックすればダウンロードできる。
セットアップの実行
ダウンロードしたインストーラを実行する。途中インストールの種類を聞かれるが、「Typical」を選択しておけば問題ない。
MariaDBの場合
以下のサイトからダウンロードする。
Productに「ODBC connector」を選び、VersionとOS(32bitか64bitか)を選択する。Versionは特に理由がなければ新しいものでよいだろう。
セットアップの実行
ダウンロードしたインストーラを実行する。途中インストールの種類を聞かれるが、「Typical」を選択しておけば問題ない。
ODBCデータソースの設定
ODBCドライバをインストールしたら、接続先のデータベースの設定を行う。
Windowsの コントロールパネル → 管理ツール を開くと、以下のいずれかが存在するので、Excelが64bitか、32bitかに合わせて起動する。
- ODBC Data Sources (32-bit)
- ODBC データ ソース (64 ビット)
なぜ32bitのほうは翻訳されていないのか。Windows 10はこういう細かいところの手抜き感がやばいと思う。
[ユーザーDSN]タブで[追加]をクリックすると、ドライバーを選択する画面が表示されるので、接続したいデータベースに合わせて「MySQL ODBC 8.0 Unicode Driver」もしくは「MariaDB ODBC 3.1 Driver」を選択し、[完了] を押す。
以降、MySQLかMariaDBかによって設定方法が異なる。
MySQLの場合
データソース設定の画面が出るので、設定を埋めていく。
「Data Source Name」には、Excelから接続先を指定する際に使うのでわかりやすい名前を付けておく。
「Description」には適当に接続の説明文を入れる(今後使うことはないが)。
「TCP/IP Server」に接続先のデータベースのホスト名かIPアドレスを指定する。「Port」にはデフォルトの3306が設定されているので、別のポートにしている場合は変更する。
「User」と「Password」にユーザ名とパスワードを入力すると、そのユーザで参照可能なデータベースが「Database」のプルダウンから選べるようになるので選択する。
MariaDBの場合
こちらも、設定画面が出てくるので項目を埋めていく。MySQLとは違いウィザード形式になっている。
「Name」にはExcelから接続先を指定する際に使うのでわかりやすい名前を付けておく。
「Description」には適当に接続の説明文を入れる(今後使うことはないが)。
「Server Name」に接続先のデータベースのホスト名かIPアドレスを指定する。
「Port」にはデフォルトで0が指定されているので、適切なポートを指定する。通常は3306。
「User name」と「Password」にユーザ名とパスワードを入力したら、[Test DSN]ボタンを押す。すると、そのユーザが参照可能なデータベースが「Database」のプルダウンから選べるようになるので選択する。
これ以降はオプションの詳細設定で、特に設定を変更せず[Next]で進めていけばよい。
Excelでデータをインポートする
Excelを起動し、リボンから[データ] → [データの取得] → [その他のデータ ソースから] → [ODBC から] を選択する。
データソースの選択画面が出るので、プルダウンで作成したデータソースを選択し[OK]を押す。
データベースに存在するテーブルやビューが表示され、テーブルの内容のプレビューが表示される。Excelに取り込みたいテーブルを選択したら、[読み込み] ボタンを押す。
余談だが、ここには先にODBCデータソースに指定したデータベース以外も表示されるし、取り込むことができてしまう。Excelがどういう風にデータソースの設定を解釈しているのか、謎である。
これで、ExcelのワークシートにテーブルとしてMySQLのデータが取り込まれる。
「いい感じに」取得するには
ここまででもデータの取り込みとしては問題ないのだが、データを取得する時点で行をグループ化したり列をフィルタしたり、一部の列を削除したり、といった操作もできる。
取り込んだテーブルを選択した状態で、リボンの[クエリ] → [編集]をクリックする。
「Power Query エディター」の画面が開いたら、ここでデータの取得方法を編集していく。
例えば、テーブルから一部のデータのみを取得したい場合は、Excelのフィルタ機能と同じように、列名のプルダウンを選択して、フィルタする値を選択できる。
[閉じて読み込む]ボタンを押せば、編集した内容でテーブルが再度読み込まれる。
あとは、このテーブルをもとにグラフ化したり、ピボットテーブルを作ったり、煮るなり焼くなりしていけばよい。
コメント