EXCELピボットでデータ分析
以前のEXCELでは、処理できるデータ量（約65,536件）に制限があり、中小企業といえども数年間のデータを時系列に分析するまでの能力がありませんでした。融通が利かず、色々と試して見ましたが、今ひとつ、と言う感じで、私もピボット機能は無視してきました
それが、EXCEL2007が出現すると処理できるデータ量が一気に1,048,576件、列数も16,384列まで拡大され、これでかなり改善されたかと思い、実際にデータ分析をしてみましたが、ACCESSからEXCELにダウンロードできる件数はそれまでのデータ量と変わらず、といった感じで、100万件を超える機能が生かされない状況で、「やはり駄目か」と落胆してしまいました。
しかし、EXCEL2013が出現すると、最大行数、最大列数は同じ制限量ですが、ダイレクトにACCESSやSQL Serverデータベースから100万件を超えるデータがアクセスできるようになり、なおかつ、関数を使わずに様々な計算ができるようになりました。
月契約のOffice365が提供されるようになってからその進歩のスピードが速まり、今現在も進化が進んでいます。
かって私のように、「EXCELピボットは使い物にならない」と無視されている方が、今でも私の周りには結構いらっしゃいます。
しかし、昔のEXCELピボットとはまるで別物です。是非、皆さんも触ってみてください。
何が変わったか？ 主な機能をご説明します。
EXCEL2003までのピボット機能
・ピボットでのマトリクス集計では、行レベルでは3階層まで、列ラベルは1階層しか集計できませんでした。
例えば、商品分類が大分類、中分類、小分類の3種類、商品コード、商品名と５段階があると、どこかの階層を削らなければなりません。列レベルも支店別、課別、担当者別に集計したくてもそれぞれ、支店別だけの集計、課別の集計というように、支店／課／担当者とドリルダウンでの原因分析ができませんでした。
・集計項目が１種類だけ、と言うのも大きな弊害でした。
商品別売上分析を行おうとすれば、数量、金額の両方の項目で集計し、分析したいと思います。値引金額、平均単価（できれば標準単価との差異の分析）も一緒に集計したいと思いますが、以前のピボットでは簡単にはできませんでした。
・何より、最大行数が65,536件という制限は、中小企業でも流通業なら1年で100万件位の売上明細データはあり、昨年との比較など、とてもできるレベルではありませんでした。
EXCEL2013時点のピボット機能
・行レベルの階層は無制限となりました。大分類／中分類／小分類／商品コード／商品名と言った分析が問題なく可能となりました。
・列レベルも無制限です。地区別／県別／担当者別の売上集計とか、支店別／課別／担当者別とか、自由にドリルダウン分析できるようになりました。
・集計項目も何種類でも無制限です。数量、売上金額、値引金額、返品金額を1つのシート上で集計できます。
・フィルター機能もピボット上で可能となりましたので、1支店のみをみたいとか、１商品のみの時系列での売上状況を見たいとか、その場で、フィルター内容を自由に変えられます。、今までのように帳票に出力して会議参加者全員に配布する必要は無く、プロジェクター、または参加者のパソコンに表示させることで、ペーパーレスで、なおかつその場で問題点を分析できる会議に変貌します。
なお、フィルター機能とは別に、スライサー機能という機能によっても、データを抽出できます。これもかなり便利な機能です。
・複雑な計算が1クリックで可能となりました。この機能は、別項目で説明します。
・処理件数は、最大行数 1,048,576件、最大列数 16,384列で、こちらはEXCEL2007以降、変化はありません。
但し、EXCEL2013 以降、ProPlus版というプロフェッショナル向けの版でPower BIというBI機能を持ったEXCELが提供されるようになりましたが、この版では最大行数、最大列数、共にメモリがある限り無制限です。
値フィールドの設定（計算の種類)
ピボットの「Σ値」欄に設定した集計項目には計算オプションが用意されており、EXCEL関数を使用することなく、様々な計算ができるようになっています。
この計算オプションを利用すると、例えば商品の売上ＡＢＣ分析が関数を利用しないで、この計算オペションだけでピボット上に実現できます。
又、前年比も計算オプションで計算できますので、前年度との日別売上対比などの資料は、EXCEL関数無しで作成できます。
EXCELのPower BI機能
EXCELのPower BI機能はOffice Professional Plus 2010から部分的に利用できるようになりましたが、Office365 ProPlusでは全機能が利用できます。
今も姉妹品のPower BI Desktop共々、日々進化を続けております。
詳細は別のコラムで紹介しますが、今までのEXCELの弱点をカバーし、それ以上に「データの見える化」の概念を根本から変えるだけの力を持ったツールになって来ました。
今までならEXCELでは本格的なデータ分析は難しいと考え、他の様々な 高額なBIツールを導入してきましたが、一般ユーザーならEXCELだけで十分なデータ分析、見える化が実現できます。
まずは試して見ましょう。