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

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

くるみこ
くるみこ

前回は [計算列] を作成してピボットテーブルで利用する方法を勉強しました。今回は、「計算領域」でメジャー(計算フィールドとも呼ばれます) を作成して利用する方法について勉強しましょう。

「計算列」の作成については、わりとすんなり理解できました。

でも今回はなんだか少し難しそうですね。よろしくお願いしますm(__)m

【この記事でわかることは】
・メジャー(計算フィールド)の作成・利用方法がわかります

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

この記事の作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
Power Pivot でメジャーを作成する
PowerPivot における Data Analysis Expressions (DAX)
Data Analysis Expressions (DAX) リファレンス

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

Power Pivot のメジャーとは

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

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

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

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

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

メジャーを作成する方法

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

オート SUM で作成する

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

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

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

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

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

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

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

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

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

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

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

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

税込金額:=SUMX(‘販売クエリ’,[販売額]+[消費税])

SUMX 関数(DAX)を使用しました。
SUMX 関数 は、テーブルの行ごとに評価される式の合計値を返す関数です。
※対する、SUM 関数 は、行単位では計算を行ってくれない関数

【構文】SUMX(<Table>, <expression>)
【パラメーター】
    <Table> 式が評価される行を含むテーブル  ‘販売クエリ’ の部分
    <expression> テーブルの行ごとに評価される式  [販売額]+[消費税] の部分
【戻り値】10 進数

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

計算をメジャーにすることで、ブックのサイズが小さくなり、更新時間も短縮することできます。ブックのサイズを最小限にしてパフォーマンスを最大にしたい場合は、可能な限り多くの計算をメジャーとして作成することがよさそうです

メジャーの管理

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

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

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

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

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

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

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

スポンサーリンク

まとめ(おわりに)

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

まとめと感想など

くるみこ
くるみこ

[計算領域] にメジャーを作成する方法について勉強しました。慣れるまでは [メジャー] ダイアログボックスで作成する方が [数式の確認] と書式設定もできるのでよいと思います。

<メジャー名>:<数式> の形式に慣れることと、関数のつくりを理解しないと難しいです(^^;
でも、「テーブル列」より「メジャー」の方がピボットテーブルの列見出しがすっきりする点も面白いと思いました(^^ゞ

【今回わかったことは】
・メジャー(計算フィールド)の作成・利用方法がわかりました
ブックのサイズを最小限にしてパフォーマンスを最大にしたい場合は、可能な限り多くの計算をメジャーとして作成することがよさそうだということがわかりました

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

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

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

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

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