Power Pivot 「計算列」を追加・作成して利用する方法

Power Pivot ウィンドウのデータビューに「計算列」を追加してピボットテーブルで利用する方法について勉強します。

※ Power Pivot アドインがアクティブになっていることが前提です。

前回記事は、Excel VBA で Power Query のクエリを作成する汎用ツールの紹介でした。
下のカードをクリックすれば開きます(^^ゞ

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

「計算列」と「計算領域」

「リレーションシップ」された「データ モデル」を使う場合ピボットテーブルの「グループ化」や「集計フィールド」「集計アイテム」が使えなくなります。（機能がブランクになって選択できない状態になります）

「データ モデル」を使ったピボットテーブルで「集計フィールド」や「集計アイテム」に相当する機能を実現するためには PowerPivot で「計算列」と「計算フィールド」を使用する必要があります。

「計算列」や 「メジャー(計算フィールド)」は PowerPivot データビューで作成・追加します。
作成・追加された「計算列」や「メジャー」は、ピボットテーブルのフィールドリストに表示され利用可能になります。

「計算列」がピボットテーブルの「集計フィールド」に相当するもので、すべてのレコードを対象にして計算します。

今回はこの「計算列」を実際に追加・作成して使ってみたいと思います。

Power Pivot で「日付」がグループ化される動作を確認

先ほど「リレーションシップ」された「データ モデル」を使う場合ピボットテーブルの「グループ化」が使えないと説明しましたが、PowerPivot で実際に確認してみました。

Excel のデータオプションで「日付／時刻列の自動グループ化」が無効に設定されている場合と有効の場合の動作の違いを比較してみました。

やはり無効に設定されている場合は当然ながら「日付」はグループ化されませんでした。

一方有効の場合の動作ですが、次の動画をご覧ください。

ピボットテーブルで「日付」を行または列のフィールドに配置すると自動でグループ化されました。

この際、Power Pivot データビューの該当テーブルの動きを確認してみると [販売日(月のインデックス)] と [販売日(月)] の「計算列」が自動的に追加されていることがわかりました。

追加された列にはそれぞれ次のような計算式が入っていました。
[販売日(月のインデックス)] =MONTH([販売日])
[販売日(月)] =FORMAT([販売日], “M 月”)

自動的にグループ化される動作が確認できました。【これってグループ化のヒントですね】

「計算列」と「計算領域」の場所

データビューに表示されたテーブルの一番右列には [列の追加] が表示されています。

[販売日(月)] 列には「=FORMAT([販売日], “M 月”)」という計算式が入っています。
[販売日(月のインデックス)] 列は非表示になっています。

「ダイアグラムビュー」でリレーションシップを確認

「ダイアグラムビュー」でリレーションシップの状況を確認してみましょう。

ご覧いただければわかるとおり [数量] [単価] はありますが [販売額] や [消費税] などのデータがありません。

「計算列」を追加する方法

ほしいデータをつくる方法としては Power Query でクエリを結合したりする方法もありますが、Power Pivot で「計算列」を設定する方法が効率的です。

「計算列」を追加してみます

[列の追加] 列を選択し、数式バーに計算式を入力していきます。

「計算列」を追加する操作

【操作動画の動作解説】

1. [列の追加] 列を選択し、数式バーに「=」を入力して計算式の入力を開始します。
2. リレーションシップで参照している別テーブルの列を参照したいので「RELATED関数」を使いたいので「r」を入力したところ、オートコンプリート機能が働き、関数のリストが表示されました。
3. 関数リストの中から「RELATED」を選択しました。すると、参照できるテーブル列のリストが表示されたので、目的の ‘単価マスタ'[単価] を選択しました。
4. 次に、掛け合わせるので「*」を入力し、 [数量] 列を選択して Enter で入力を完了しました。
   入力された数式は =RELATED(‘単価マスタ'[単価])*’販売クエリ'[数量]
5. 少し待つと「計算列1」が追加されましたので、書式を整え、列見出しを [販売額] に変更しました。

※ リレーションシップを参照する場合、Power Pivot DAX の RELATED 関数 を使います。
Excel関数で該当するものとしては、VLOOKUP関数がこれにあたります。

DAX関数の「DAX」は Data Analysis Expressions のことです。リンク先はMSのリファレンスです。

「計算列」に消費税を追加します

先ほどは、リレーションシップを参照する事例でしたが、今度は「通常の数値演算」の事例です。

消費税の計算(掛け算)を入力してみます。

[列の追加] を選択 → 数式バーに半角で「=」を入力 → 列見出しの[販売額]をクリック →

「*」を入力 → 0.1（10％なので）を入力 → Enter で数式を完成させます。

入力された数式は =’販売クエリ'[販売額]*0.1 となっています。

最後に、列の数値書式を整え、列見出しを [消費税] に変更します。

続いて「販売額(税込み)」列を追加します

[列の追加] を選択 → 数式バーに半角で「=」を入力 → 列見出しの[販売額]をクリック →

「+」を入力 → 列見出しの[消費税] をクリック → Enter で数式を完成させて完了です。

入力された数式は =’販売クエリ'[販売額]+’販売クエリ'[消費税] となっています。

次に、列の数値書式を整え、列見出しを [販売額(税込)] に変更します。

[計算列] を2つ追加したテーブルの画像がこちらです。

ピボットテーブルで「計算列」を確認

「ピボットテーブルのフィールド」を確認してみましょう。

フィールド内に「計算列」で追加した [販売額] [消費税] [販売額(税込)] がありました。

とりあえず、値フィールドに計算結果を「集計列」として配置してみました。

今回の「計算列」では、DAXの RELATED関数を使いましたが、そのほかはほぼ単純な数値演算を使用しました。単純な数値演算だけでなく、論理演算も使用可能ですので試してみてください。

また、DAX関数にはもっと沢山の使える関数がありますので、また別の機会に紹介したいと思います。

次回は「メジャー(計算フィールド)」について勉強したいと思います。

まとめ（おわりに）

以上、Power Pivot データビュー内のテーブルに「計算列」を追加・作成する方法について勉強しました。その中で、RELATED 関数（DAX）の使い方についても勉強できました。

まとめと感想など

【今後の記事について】

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

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

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

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