まえがき
今回も始まりました。やってみよう分析！シリーズ。
第3章から 第2部 エクセルで学ぶ分析入門に入り、Excelを使った集計・分析技術を紹介していきます。Excelには分析に便利な分析ツールやソルバーをアドインすることができます。またデータベースとしてMySQLと連携させ、100万行を超えるデータに対してpivot分析を実施することが可能です。
本章ではローカルのWindowsマシン上でExcel、MySQLを連携する方法を紹介します。読者対象者は主に
- エンジニア以外で大きめの容量を持つcsvのExcel pivot集計を実施したい方
を想定しています。ビジネスサイドの人間がちょっとした容量の大きいcsv(だいたい100万行以上から1000万行前後)をpivot集計したい場合にExcelとMySQLの連携が役立ちます。MySQL単体でもクロス集計等が可能ですが、やはりExcel pivotの手軽さは捨てがたいものがあります。
本章の目次は以下のとおりです。
- 実行環境と注意点
- MySQLのインストール
- my.iniの編集
- ODBCのインストール
- 簡単にピボットしてみる
次章ではMySQLとExcelの連携でよく使うMySQLコマンド(select, create, csvのロード/エクスポート等)を紹介する予定です。
では、今回も早速始めましょう。
実行環境と注意点
本章(および次章)の作業を実行するのに支障がないと思われる環境の目安は
- CPU：インテル Core i7-4600U プロセッサー (2.10GHz, 4MB, 1600MHz)
- メモリ搭載量：8.00GB
- ハードディスク容量：217GB
- Excel2013 32bit
です。著者は Excel2013 64bitをローカルのWindowsマシン(メモリ32GB)で使用しています。
注意点
Officeのこちらのページを見ると
64 ビット版 Office はファイル サイズにハード制限がないためです。代わりに、ワークブックのサイズが使用可能メモリとシステム リソースに制限されます。一方、32 ビット版 Office は、Excel、ワークブック、および同じプロセスで実行するアドインが共有する仮想アドレス空間の 2 ギガバイト(GB) に制限されます。
とあります。つまり、大きいファイルを格納して集計するには64bitのほうが良いということです。実際、著者が32bitでMySQLと連携してpivotを実行した時、途中で実行が停止したことがありました。著者は業務上、かなりの頻度で数百MBから数GB以上の容量を持つcsvを扱っているので64bit版に変更しています。
ただしこちらのリンク先ページを見ると、 64bit版は32bitで動くアプリケーションと互換性がない場合もあるらしいので、不安な方はとりあえず32bitのままが無難でしょう。
powerpivotについて
Excel2013のバージョンによってはpowerpivotと呼ばれるツールが選択できるようになっています。また、Excel2010もアドオンでpowerpivotを導入可能です。powerpivotを使うことでも大きい容量のcsvを取り込んでpivotを実行することが可能です。
OfficeのこのページをみてみるとOffice Professional Plus エディション、Office 365 Professional Plus エディション、Excel 2013 の Standalone エディションで利用できるとあります。残念ながら著者が使っているOffice Home and Business 2013ではpowerpivotを使うことができませんでした。
MySQLのインストール
早速MySQLをローカルのWindowsマシンにインストールしてみましょう。ここでインストールするのは MySQL 5.5.36です。
MySQLのページに行きます。ダウンロード(GA)のリンクをクリックして
飛び先ページのMySQL Community Editionのリンクをクリックします。
さらに飛び先ページでArchiveリンクをクリックします。
飛び先のページでMySQL Community Serverのリンクを押します。
飛び先でselect versionを5.5.36、select platformはMicrosoft Windowsを選びます。
使用しているwindowsのbitに対応しているバージョンのInstallerファイルをダウンロードします。
ダウンロードしたダイルを実行します。基本は全てNextを押して進みます。setup typeはTypicalを選択しInstallボタンを押します。途中も全てNextを押して進みます。
セットアップが完了したらLaunch the MySQL Instance Configuration Wizardのチェック入れた状態でFinishボタンを押します。
Wizardが立ち上がったらまたNextを押して進めます。server instanceはStandard Configurationを選択しておきます。
Nextボタンを押して次のページに遷移します。初めてインストールするときはwindows pathを加えるにチェックを入れてください。
次に進み、rootのパスワードを設定します。confirmには再度同じパスワードを入力してください。MySQLにアクセスするときに必要なので忘れないようにします。
次の画面に進んでExcuteボタンを押すと設定が実行されます。
MySQLが正しくインストールされたか確認してみましょう。コマンドプロンプトを起動( プログラムとファイルの検索にcmdと入力)します。
mysql -uroot -p
と入力、設定したパスワードを入力します。下図のようにMySQLが起動すれば成功です。
もしmysqlコマンドがはじかれた場合、PATHが正しく設定されていない可能性があります。PATHの確認は
コンピュータ→システムのプロパティ→システムの詳細設定→環境変数→システム環境変数のPATH
を編集します。PATHの項目を編集ボタンで見た時、MySQLインストールフォルダ内のbinフォルダが指定されているか確認します。指定されていない場合にはMySQLインストールフォルダのbinフォルダアドレスを書き加えます。例えば、Cドライブにインストールした時は
C:\Program Files\MySQL\MySQL Server 5.5\bin;
のようになります(最後はセミコロンで区切ります)。
my.iniの編集
次にmy.iniファイルで文字コードを編集します。本章では文字コードをsjisに設定する場合を紹介します(日本語が混ざったsjisのcsvをいじる場合、文字化け対処できるようになります)。sjisではなくutf8に設定することも可能です。ここでsjis設定を紹介するのは、Excelで文字コードがsjisのcsvを扱う可能性が高いと想定されるためです。
まずデフォルトのMySQLの文字コードを確認してみましょう。文字コードを確認するためにはMySQLに入って次のコマンドを入力します。
show variables like 'char%';
入力後に出力される画面を見てみると下図のような情報が表示されると思います。
latin1とかutf8というのが文字コードを表しています。一旦MySQLを終了させるためquitと入力します。
文字コードを編集するため、my.iniという設定ファイルを編集します。MySQLをインストールしたフォルダのMySQL Server 5.5フォルダ直下にmy.iniファイルを管理者権限で開いたメモ帳で開きます( メモ帳を管理者権限で開く方法はメモ帳アイコンの上で右クリックして管理者として実行)。
my.iniの編集項目は[client], [mysql], [mysqld]です。それぞれの項目に(下図参考)
[client] (追記)
default-character-set=sjis
[mysql] (編集)
default-character-set=sjis
[mysqld] (編集/追記)
character-set-server=sjis
skip-character-set-client-handshake
作業が完了したら保存してメモ帳を閉じます。
WindowsでMySQLを再起動させるために プログラムとファイルの検索に
services.msc
と入力してサービスを起動させます。MySQLと表示されている箇所を見つけ選択してサービスを再起動します。
再起動が完了したら、再度コマンドプロンプトからMySQLに入り、先ほど入力した文字コード確認用クエリshow variables like 'char%';を実行します。出力された結果を見ると先ほどlatin1だったところがsjisに変更されています。
これでsjisのcsvをインポートしても文字化けしないようになります。 sjisをutf8に変更すればそのままutf8に文字コードを変更することが可能です。
ODBCのインストール
まずODBCドライバをMySQLのサイトからダウンロードします。windows版の32bitか64bitのinstallerをダウンロードします(Downloadボタンををして遷移したページでNo thanks, just start my download.をクリックしてダウンロードできます)。
使用しているExcelが32bitである場合には、windowsが64bitでも32bitのダイルをダウンロードします。
ダウンロードしたファイルを実行します。インストール作業中の過程はNextを押して進めます。途中Setup typeを聞かれます。この時typicalを選択します。
ODBCの設定
プログラムとファイルの検索にodbcad32.exeと入力して実行します。ODBCデータソースアドミニストレータが立ち上がります。起動したら追加ボタンを押します。
追加ボタンを押すとデータソースの新規作成が現れます。ここで MySQL ODBC 5.2 ANSI Driverを選択します。完了ボタンを押すとMySQL Connector/ODBC Data Source Configuration が現れます。
Data Source NameとDescriptionはとりあえずMySQLとしておきます。TCP/IP Serverは localhostに設定。Portは 3306のままにしておきます。UserとPassworはMySQLをインストール時のものを入力します。ここではUserをrootで設定していました。最後にTestを押して Connection successfulと出れば設定完了です。
簡単にピボットしてみる
それではExcelからMySQLに接続してみましょう。Excelを起動し、
データタブから外部データの取り込み→その他のデータソース取り込み→データ接続ウィザード
を選択します。ウィザードが起動したらODBC DSNを選択して次を押します。
MySQL Connector/ODBC Data Source Configurationで設定した MySQLが現れているのでそれを選択します。
接続に成功すると接続先のデータベース一覧が表示されます。使いたいデータベースを選択します。ここでは例としてもともとMySQLに備わっているデータベースの一つperformance_schemaを使うことにします。データベースを選択するとその中のテーブル一覧が表示されます。ここではsetup_consumersを選択します。
次へボタン→完了ボタンを押します。データのインポートでピボットテーブルレポートを選択します。
ピボットテーブルが起動して下記のようにテーブル集計を実行することができるはずです。
これでExcelとMySQLを連携させてpivotさせる準備が完了しました。
まとめ
本章ではExcelとMySQLの連携方法の基本を紹介してきました。
次回は外部csvをMySQLにインポートする方法などMySQLの基本操作を紹介します。MySQLの基本操作とExcelのpivotを組み合わせることで幅広い集計作業がこなせるようになります。またMySQL単体での知識も集計作業にとても役立ちます。
今回もコーディング始めてなくてすみません(笑)
===========================================