エクセルで大量データを回すためのコツ｜検証：エクセルでどこまでビックデータ分析ができるのか？(2)

本記事は、株式会社ギックスの運営していた分析情報サイト graffe/グラーフより移設されました（2019/7/1）

1 エクセルの癖を知って、分析速度を向上させよう
- 1.1 それでも対策は必要

エクセルの癖を知って、分析速度を向上させよう

さて、前回は、ビックデータ（に近いもの）を実際にノートPC＋Excelで取り込み、分析してみました。エクセルでも実用に耐えうることがベンチマークテストからお分かりいただけたと思います。数年前にExcelの行数が6万行ちょっとしかなかった時代に、5万行のデータを分析するのに1つの計算ユニットで2時間かかっていたことから考えると隔世の感がありますが。

それでも対策は必要

しかし、いくらPCスペックが高いとはいえ、ある程度Excelのサイズを小さくしておくことは、より分析を進めていくためにも、ファイルが破損することを避けるためにも重要なことです。そこで、今回は、Excelブックを作成する上で、不必要に容量が増大することを避けることができる方法を4つお話ししたいと思います。
今回は、まず、基本となる2つのテクニックです。

その1）計算式は一行目だけ

実際にデータ分析を開始して、計算式を書き込んで、それを全データに展開していくということもよくあることだと思います。数少ない計算式であれば特に問題はないのですが、データ量が多くなったり、計算式が多くなったりすると、ファイル容量が大きくなるだけでなく、ファイルを開くときや自動保存の時に、生じる再計算に時間がかかるようになってしまいます。（最悪、性能の低いPCでは開かなくなる）
その為、ある程度量の多い計算を行う際には、計算式実施後、当該部分をコピーして、値貼り付けしておくことをお勧めします。その際、すべての領域を値貼り付けにしてしまうと、後々になってどういう計算していたかどうかがわからなくなることもあるので、1行目だけは計算の形で残しておいたほうが賢明です。

その2）ピボットテーブルは同じデータを参照する

Excelの優れた機能の一つとしてピボットテーブルがあります。データのクロス分析に非常に有用な機能ですが、実は非常にファイル容量が増大する機能でもあります。ファイルの増大をなるべく避けるには、ピボットテーブルが参照するデータ範囲を同一にするという手法があります。
データベースからピボットテーブルを作り→新たなデータをデータベースに入れ込み→新データからピボットテーブルを作成するという流れで分析を行っていくと増大するばかりです。
そこで、
１）ピボットテーブルを作りたいデータの終端に「END行」「END列」を作成する
２）ピボットテーブル作成ウィザードではENDまで含んだデータテーブルで選択
３）新たにデータを追加するときにはENDの手前に挿入する
４）ピボットテーブルを更新する
５）ピボットテーブル作成ウィザードでENDまで含んだデータテーブルを選択
という流れを確立すると、ENDまで含んだデータテーブルを選択するとき、Excelの方から、「同じ元のデータから作成した既存のレポートを基にする場合、新しいレポートではメモリを節約できます。既存のレポートと同じデータを基にして新しいレポートを作成しますか」という質問が来ます。これに「はい」と答えるだけで、不用意なファイルサイズの増大を避けることができます。
また、もう一つのメリットとして、４）の更新するを行うだけで、新たに追加したデータが格納されたピボットテーブルとなるので、作業の効率化にもつながり、さらにはミスの削減も実現すると1粒で3倍美味しいメソッドでもあります。