『重すぎる Excel で上手にピボット・テーブルをつかってクロス集計するには？』
重すぎる Excel ってありますよね。
大量のセルが数値で埋め尽くされているけれど、分析のためにピボット・テーブルを使うと、やたらと重いような。
でも、あれって Excel の限界なんですか？
それとも Excel の使い方が間違っているのですか？
試しに、次の VBA の乱数をつかって、大量データのシートを作ってみましょう。
10 列 100000 行の合計『百万』セルです。
ただし、行末に "DIMENSION" として dim1 〜 dim10 の 10 種のラベルも追加しておきます。
Sub FillRandomIntegersWithHeadersAndLabels()
Dim ws As Worksheet
Dim rng As Range
Dim rowCount As Long
Dim colCount As Long
Dim i As Long, j As Long
Dim headerLabel As String
Set ws = ActiveSheet
rowCount = 100000
colCount = 10
For j = 1 To colCount
ws.Cells(1, j).Value = "dim" & j
Next j
ws.Cells(1, colCount + 1) = "DIMENSION"
Application.ScreenUpdating = False
For i = 2 To rowCount + 1
For j = 1 To colCount
ws.Cells(i, j).Value = Int(Rnd() * (colCount + 1))
Next j
ws.Cells(i, colCount + 1).Value = "dim" & Int(Rnd() * colCount + 1)
Next i
Application.ScreenUpdating = True
このようなシート内容が生成されたら、該当するセルを全選択してピボット・テーブルを作ってください。
クロス集計できます。
集計値としては DIMENSION のラベル個数にしてください。
行の集計軸と列の集計軸はなにか適当な、たとえば dim1 と dim2 にしてください。
どうですか？
あっという間でしょう。
百万セルもあるのに、あっという間。
ふだん、あれだけ悩まされている Excel のシートの分析、実は簡単にできるはずなのです！
なんで、こんなことができたのか？
実はセル関数（セル式）を使ってないからです。
セル関数を使って、CEILING や FLOOR による数値の変換や VLOOKUP によるラベルの検索と変換を『この場ではいちいちやっていない』からです。
つまり、実際の値としてしかシートのセル上に存在しません。
これを PC 用語で『即値』と呼びます。
ということは？
実はあれだけ不評な Excel も年々日々改善されているので、 相当な量の計算もあっという間にこなしてしまうのです。
なにかを実行するたびに、余計な動作をしなければ。
ここが重要。
つまり、セル関数を使うような、実行するたびに別の式の評価が走るような動作が組み込まれていない場合、たとえば百万セルでもあっという間に集計できるのです。
ボクはこのような問題に十年以上悩んで関心を持ってきました。
でも、現時点で技術動向について調べ直したところ、OLAP データ構造のような、あたらしめのデータ構造が一般化しています。
そのような改善が Excel の内部的にはもう現実化していておかしくないのです。
では、おもすぎる Excel をどうすべきか？
これまで、わざわざ手間暇かけて築き上げてきた超重量級 Excel ファイルを。
かんたんです。
その Excel ファイルはそのまま使うのです。
ただし集計用に、別の Excel ファイルにシート内容をすべて『即値として』コピーするのです（参照の手法は使わないでください）。
そして、コピーした先の Excel ファイルを集計なりクロス集計して、グラフをふんだんに使い分けて、バシバシと分析するのです！
世の中にはいろいろなアプリがあります。
それぞれの専門分野の。
でも、すべてを満たすアプリはおそらく今後も現実になりません。
そして、それぞれの専門アプリはその専門分野においては、相当な便利さです。
専門家が作っているので。
でも、そのあいだをたとえば .csv のような一般的書式でデータのやりとりができれば？
本日の事例はその考え方を応用したものです。
たとえば、会社に関わっていると日々、帳簿を付けます。
仕訳日記帳には大量のデータがあります。
ところがその仕訳日記帳の内容を集計して、なにが無駄になっていて、どこが足りないのか知りたい。
毎日、分析し直したい。
だからといって、毎分ごとに厳密な集計するのは必要ない。
そんなときには？
きょうの手法の応用をしてください。
十徳ナイフのようなアプリを使うより、ひとつひとつの包丁なり、ナイフなり、コルク抜きに相当するアプリを、そのあいだのデータの受け渡しで組み合わせて使ったほうが、よほど信頼できて、よほど速い仕事ができるのでしょう。
お疑いならば、まずは一度お試しになれば？
だって、帳簿の集計らしく、集計軸による合算に切り替えてもあっという間ですから。
