Power Pivot 計算領域にメジャーを作成して利用する方法

Power Pivot データビュー内の「計算領域」にメジャーを作成してピボットテーブルで利用する方法を勉強します。

前回記事は Power Pivot で「計算列」を追加・作成して利用する方法でした。
下のカードをクリックすれば開きます(^^ゞ

なお、この記事の画像は Microsoft 365 で取得した画像です。リボンやメニューなど Excel のバージョンによって違うようなので、適宜読み替えをお願いします(^^)/

Power Pivot のメジャーとは

メジャーは、データ分析で使用される計算です。DAX 数式で合計、平均、最小値または最大値、カウント、またはより高度な計算を作成できます。

Data Analysis Expressions (DAX) の基礎はわりと理解しやすいと思います。DAX はプログラミング言語ではありません。DAX は数式の言語です。DAX を使用して、計算列とメジャーのカスタム計算を定義することができます。DAX には、Excel の数式で使用できる関数や、リレーショナル データを使って、動的な集計を行うように設計された関数が用意されています。

暗黙的メジャーと明示的メジャー

暗黙的なメジャーは、ピボットテーブル の フィールド リストで [Σ 値] 領域に フィールド をドラッグしたときに、Excel によって作成されます。暗黙的メジャーでは標準集計(合計、カウント、最小、最大、標準偏差、標本、重複しない値のカウント、または 平均など) のみを使用できます。メジャーが作成されたピボットテーブルまたはピボットグラフでのみ使用できます。

明示的なメジャーは、「計算領域」内のセルで数式を入力するか、PowerPivot ウィンドウでオート SUM 機能を使うと作成されます。作成するメジャーのほとんどは、明示的メジャーになります。

メジャーを作成する方法

[ホーム] > [表示] > [計算領域] の順にクリックして「計算領域」を表示しておきましょう。

オート SUM で作成する

Power Pivot ウィンドウで [オート SUM] を使用してメジャーを作成するには、

１ 集計する列をクリックします。

２ [Σ オートSUM ▼] をクリック > [集計方法] を選びます。

作成されたメジャーは、選択列直下の 「計算領域」のセルに表示されます。複数設定する場合は作成済みのメジャー下のセルに追加されます。

この作成されたメジャーは、同じテーブルの「計算領域」内の他のセルに自由に移動できます。

メジャーの名前も、数式バーで変更できます。ただし、名付けは一意でなければなりません。

「計算領域」のセルに手動で作成する

１ 「計算領域」の空白のセルをクリックします。

２ テーブル最上部の数式バーで、<メジャー名>:<数式> の形式で直接数式を入力します。

３ Enter キーを押して数式を確定します。

「計算領域」で作成されたメジャーは、ソース データ テーブル とともに保存されますが、ピボットテーブル、ピボットグラフ、またはレポートでフィールドとして使用できます。

・数式バーで入力して数式は次のとおりです。

・「計算列」に設定した合計金額「115,196,510」と同じ値が表示されているのが確認できます。

・実際に入力した感想は、「これは慣れが必要だな」です。

[メジャー] ダイアログ ボックス で作成する

もう一つ、ダイアログボックスを使ってメジャーを作成する次の方法を紹介します。

[メジャー] ダイアログボックス を表示するところからスタートします。ℱ

１ Excel メニュー > [PowerPivot] > [計算] > [fxメジャー] > [新しいメジャー…] の順にクリックして [メジャー] ダイアログボックスを表示します。

２ [メジャー] ダイアログボックスの [テーブル名] の下向き矢印をクリックし、メジャーを配置するテーブルを選択します。

３ 選択した [テーブル] の [計算領域] に、設定したメジャーの定義が保存されますが、必ずしもそのメジャーが参照する [テーブル] に保存する必要はありません。

４ [メジャー名] に名前を入力します。
　※ メジャーの名前は一意でなければなりません。他の列と同じ名前にすることはできません。

５ [式] ボックスで、等号 (=) の後ろにカーソルを置いて、数式を入力していきます。

６ 式の入力完了後に [数式の確認] をクリックして、数式にエラーがないか検証します。

７ 書式オプションの [カテゴリ] でメジャーの種類を選択します。
　※ [カテゴリ] の選択は、数式の計算方法には影響しません。後で [メジャーの管理] から変更することができます。

８ 数式が正常に検証できたことを確認したら、[OK] をクリックします。

・ 前項の【「計算領域」のセルに手動で作成する】と同じ計算式を入力している動画です。

・[テーブル]選択時に、’販売クエリ’[販売額] を選択してから、コンマ(,)を挿入して
　’販売クエリ’,[販売額]と修正してもよいでしょう。

・[カテゴリ] の選択部分については、ここでは省略しています。

・[販売クエリ] の「計算領域」に作成されたメジャーが保存されたのが確認できます。
　

Excel の [メジャー] ダイアログ ボックスを使用して作成したメジャーは、「３」で選択したテーブルの「計算領域」に保存されます。後で Excel の [メジャーの管理] ダイアログボックスまたは [Power　Pivot] ウィンドウ内のテーブルの「計算領域」で、メジャーを表示して編集することができます。

メジャーをピボットテーブルで利用する

それでは、作成したメジャーをピボットテーブルで確認してみます。

・Σ 値フィールドにメジャー [fxメジャー税込金額] が配置されています。

・左隣のテーブル列の [合計/販売額(税込)] と同じ額が表示されていますが、よく比較してみると書式が設定されていないことが確認できます！

・テーブル列 [販売額(税込)] と同じものが、メジャーで設定できることが確認できました。

・違いがもう一つ確認できます。ピボットテーブルの列ラベル部分を比較すると、メジャーの場合「合計／」の表記が無く、メジャー名だけが表示されています。

・これなら、テーブル列 [販売額(税込)] は作る必要は無いということですね。さらに、[販売額] や [消費税] もメジャーにしてしまうことも可能ですね。

メジャーの管理

[メジャー税込金額] は、作成時に書式オプションの [カテゴリ] を設定していませんでした。

作成済みのメジャーは [メジャーの管理] から書式設定などを変更することができます。

・[メジャーの管理] からメジャーを変更している動画です。

・[メジャー税込金額] は 数値 [Number] または 通貨 [Currency] が適当ですが、ここでは 数値 [Number] を選択して書式設定しています。

・通貨 [Currency] を選択した場合は「通貨記号」の付定を選択できます。

・書式オプションで選択できる [カテゴリ] は次のとおりです。

General	標準
Number	数値
Currence	通貨
Date	日時
TRUE/FALSE	理論値

まとめ（おわりに）

以上、簡単ですが Power Pivot で [計算領域] にメジャーを作成して利用する方法について勉強しました。

まとめと感想など

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

各記事のサンプルファイルをダウンロードできます

今回記事はサンプルファイルの登録はありません！
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください