「ピボットテーブル」を使わずにデータモデルからデータを操作する「キューブ関数」について解説します。初めはとっつきにくいですが、慣れると非常に有効な方法ですので、少し頑張ってみましょう。
そもそも、「データモデル」とは(別名、「PowerPivotデータモデル」)は、Excelで作成した複数のテーブルをまとめて1つのデータベースにしたものです。 テーブルのほかに、Accessのデータや外部ファイル、外部サーバのデータを取り込むこともできます。
データモデルの例)
イメージ挿入
まず、「キューブ関数」を使用するためには、互換性のあるエクセルのバージョンを使う必要があります。以下のマイクロソフトのヘルプページより、対応しているエクセルのバージョンをご確認ください。また、最近人気になっているMicrosoft 365のクラウド上では、令和2年10月24日時点で使えないのでご注意ください。
(以下マイクロソフトのサポートより抜粋)
注: この記事の最終更新日は 2019 年 1 月 8 日です。 Power Pivot の可用性は、Office の現在のバージョンによって異なります。 Microsoft 365 サブスクライバーである場合は、最新の更新プログラムがインストールされていることをご確認ください。
サブスクリプション製品
永続ライセンス版 (永続的ライセンス) の製品
Excel 2010 用 Power Pivot アドインは Office に含まれていませんが、無料でダウンロードできます。Power Pivot アドインのダウンロード。
この無料ダウンロードは Excel 2010 でのみ可能であり、新しいバージョンの Excel では使用できません。
キューブ関数の話に戻りましょう。最も簡単にデータモデルのデータを操作する方法は「ピボットテーブル」か「パワーピボット」ですが、「キューブ関数」には「ピボットテーブル」にはできないことができます。その一つは、指定したフィルターに応じてデータをデータモデルか取得して、直接エクセルのセルに表示させることができます。例えば、以下のような表を作りたい場合、「キューブ関数」で実現することができます。それに対して、「ピボットテーブル」を使用すると、表のデザインがいちいち変わってしまったり、最終的に報告書やプレゼンの資料に表を貼り付けする場合に、微調整が必要になります。一方で、「キューブ関数」を使えば、セルに直接表示させることができるので、データとレイアウト(デザイン)を分けて扱うことができます。もちろん、他にも利点がありますので順番に解説していきます。
マイクロソフトのサポートページを参照すると以下のような説明がありますが、イマイチ何を言ってるのか分からないという人が多いのでは?と思います。
(以下マイクロソフトのサポートより抜粋)
CUBEKPIMEMBER 関数:主要業績評価指標 (KPI) のプロパティを返し、KPI 名をセルに表示します。KPI は、月間粗利益や四半期従業員退職率など、定量化が可能な測定値であり、組織の業績をモニタリングするために使用されます。
CUBEMEMBER 関数:キューブのメンバーまたは組を返します。キューブ内にメンバーまたは組が存在することを確認するために使用します。
CUBEMEMBERPROPERTY 関数:キューブ内のメンバー プロパティの値を返します。メンバー名がキューブ内に存在することを確認し、このメンバーの特定のプロパティを取得するために使用します。
CUBERANKEDMEMBER 関数:セット内の n 番目の (ランクされている) メンバーを返します。売り上げトップの販売員、成績上位 10 位までの生徒など、セット内の 1 つ以上の要素を取得するために使用します。
CUBESET 関数:セット式をサーバー上のキューブに送信して、計算されたメンバーまたは組のセットを定義します。サーバー上のキューブによってセットが作成され、Microsoft Excel に返されます。
CUBESETCOUNT 関数:セット内のアイテムの数を返します。
CUBEVALUE 関数:キューブの集計値を返します。
私なりの解説(だいぶ横着に説明してみます)その中でも、私の経験から最も使用する4つの「キューブ関数」を紹介します。
キューブ関数は、他にも多くの種類がありますが、まずはこの4つを使って解説していきます。また、この4つを使って自由自在に操れるようになったら、その他のキューブ関数もマイクロソフトのヘルプページを参考に今回説明しきれなかった他の関数や基本を学ぶことをおすすめします。