ピボット姫と学ぼう！（8）双子の「呪い」2⃣集計ならＥｘｃｅｌ？？

Ｅｘｃｅｌ⇔ピボットテーブル　違い？選択？　

Ｅｘｃｅｌ大王の御布令です！

ピボットテーブルは、Ｅｘｃｅｌの「１機能」に見えます。
でも、本質は「表計算」ではなく、「データ集計分析ツール」です。
似ているけど、個性を持った別のツールです。
これが、「双子の呪い」です。

多くの人が、Ｅｘｃｅｌで作った方が「無難」と思っていると、
ピボットテーブルは廃止になってしまいます！

ピボット姫は、Ｅｘｃｅｌ大王の御布令に困っています！

ピボットテーブルを「正しく」理解するには？
コンピューターの歴史を振り返ってみることにしました。

入力操作 ⇔ マウス操作？

パソコン操作は大きく変わってきました。
ＣＵＩからＧＵＩの変化です。

UI（User Interface）　　　画面のこと　操作など
CUI　 文字だけの画面　   　キーボードのみで操作可能
GUI　グラフィカルな画面　マウス・キーボードで操作可能

でも、Ｅｘｃｅｌは、マウスを使っていても、セル単位に入力や、
VBAマクロの作成の方法は、ＣＵＩのスタイルです。

ピボットテーブルは、どうでしょうか？
マウス操作で行うツールです。
ＧＵＩのスタイルです。
マウスの使い方を、しっかり身につけなければなりません。
「テーブル」「パワークエリ」などの機能［ツール］もＧＵＩです！

「ＣＵＩ」のつもりでピボットテーブルを使うと「呪い」罹る！

ピボットテーブルでの集計は、「合計」がほとんど！

４つのボックスにフィールドを配置すると、簡単に「集計」できます。
自動的に「合計」になります。

マウス操作で、設定を変えない限り、「合計」のままです！

ピボットテーブルのレポートを「合計」だけしか使わない
「便利な集計機能」＝「呪い」に罹っていませんか？

ピボット姫の「推理」

ピボットテーブルの集計の方法、計算の種類を知らないまま使う！
それで、便利な集計＝「合計」だけ使う「呪い」に罹っているようです。

ピボットテーブルの集計方法は、あまり紹介されていません。
「合計」だけでは、ピボットテーブルの１０％も使っていません！

サンプル・エクセルは、こちらからダウンロードしてください

１ ．ピボットテーブルの集計方法？

Ｅｘｃｅｌの関数や操作についての知識を持っている人が多いです。
　　　◎数式の関数を変更
　　　◎新たな列or行を追加して計算
　　　　　　　列、行を挿入
　　　　　　　関数　「入力」

でも、ピボットテーブルで行うやり方、皆さん知っていますか？　

合計(ＳＵⅯ)以外にも、様々な集計の選択ができる！

値フィールドには、同一フィールドを、複数回使える！

そして、この２つの操作＝マウス行うのです！

売上金額を２つ、値フィールドに追加し、売上金額は３つになりました。
集計方法の変更　合計　⇒　平均、個数に変更しています。

①ピボットテーブルで値を計算する (microsoft.com)

◎値フィールドを計算する集計方法
オンライン分析処理 (OLAP) ソース データを除くすべての種類のソース データに対して使用できます。

値フィールドを沢山使うための参考情報です。

①値フィールドの名称変更

合計 / のままでは見にくいので、カットしましょう！

②セルの書式の変更

　　見やすいように書式を変更しましょう！

ピボット姫のまとめ

関数を入力しないで、値フィールドで集計を選択するだけで、様々な計算をしてくれるのが、ピボットテーブルの分析力の特徴です。
名前の変更、書式の変更で、見やすいレポートにしましょう。

２．値フィールドの組み込み計算？

構成比、累計、増減・・。
データ分析には、項目の集計だけでなく、追加の計算値が役立ちます。
でも、Ｅｘｃｅｌでの数式入力は、複雑になります。

ピボットテーブルは、データ エリア内の他のアイテムやセルに基づいて
計算値を表示する「計算の種類（組み込み関数）」を選択できます。

これも、ＧＵＩマウス操作で行います。関数などの入力ではありません。

◎値フィールドの計算の種類( 組み込み）
では、データ エリア内の他のアイテムやセルに基づいて値を表示します。 たとえば、"売上合計" データ フィールドの値を "3 月" の売上に対するパーセンテージで表示したり、"月" フィールドのアイテムの累計として表示することができます。

サンプルで３つの事例を確かめてみましょう！

計算の種類　 ①総計に対する比率

　　　売上金額　をドラッグ[追加]　

計算の種類　 ②累計

売上金額　をドラッグ[追加]　、　年月を基準に設定
⇒行の「総計」はダブるので不要です

計算の種類　 ➂差分　前月差

売上金額　をドラッグ[追加]　、　
　年月を基準フィールド、基準アイテムを前の値［前月］に設定

ピボット姫のまとめ

数式を使用しないで複雑な計算を、選択で実行してくれます。
非常に便利ですが、使い方に癖に慣れて、使いこなしましょう！

３．ピボットテーブルで数式を使用できる？

2．計算の種類（組み込み）ではなく、ユーザーが数式を設定できます。

大変残念なのは、ＭＯＳ検定のテキストでは「集計フィールド」が
ほとんど紹介されていません！
ＭＯＳ検定を取得しても、実践で使いこなしている人が少ない。
それで、ピボットテーブルが使われない＝「呪い」になっているようです。

売上金額を数量で割った「単価・加重平均」は、レポート作成で大変重要な情報です。

ピボットテーブルで、売上金額、数量を集計。
エクセルで、数式で計算というのでは、大変面倒です。

平均単価は、関数「AVERAGE」なので、単価の「単純平均」です。

ピボットテーブルに、ユーザーの数式で組み込めると、大変楽になります。

ピボットテーブル分析から　フィールド/アイテム/セット
集計フィールドから作成します。

集計フィールドは、計算した結果から算出します。
折りたたんだり、表のレイアウトを変更しても、計算してくれます。
これが、ピボットテーブル内部に組み込めるメリットです。

ＢＩツールでは、同じような計算機能がありますが、
データを表示させないと計算できません。

ピボットテーブルは、単価・加重平均だけの表示が可能です。
「データキャッシュ」で、共有しているので、
他のピボットテーブルでも使うことができます。

ピボット姫のまとめ

集計フィールドは、単なる集計だけでなく、集計値をさらに数式で計算できます。ピボットテーブルのNo.２のお薦め機能です。

４.　集計アイテムは万全？

解説書では、集計アイテムを便利な使用方法として紹介されています。

しかし、なかなか動かない！メモリがパンクすることも！
本当に「呪い」が罹ってしまう機能です！

集計アイテムの指定フィールド　以外の　階層１つ　呪いなし
　　　　　　　　　　　　　　　　　　　　　２つ以上　呪い罹る　　　
どうしてでしょうか・・ピボットおじさんの「推理」です！

呪いの原因・・

集計フィールド・・「列」の増加＝メモリ増加　小さい
集計アイテム　・・「行」の増加＝メモリ増加　大きい
　例　予算　5000行、実績　5000行として、
　　　集計アイテム　実績－予算　5000行　　行数が150％になります

サンプル・・予実差のデータ　
　　　　正しい　８件　　該当なし・・２４件　　計３２件
　　　　元データに無いのに、組み合わせで、全アイテムが表示されます！
　　　　これが、「集計アイテムに呪い」です！　　　

ピボット姫のまとめ

集計アイテムは、無理して使わない＝使えるところを使いましょう！

ピボットテーブルには、集計アイテムを使わなくても、それに代わる他の方法があります。
「考える」＝応用力が大切です。　次の事例に、挑戦しましょう！

５．ピボットテーブルの限界？

ピボットテーブルだけで使いこなすには限界があります！
それは、「元データ」の「呪い」です！

パワークエリは、マイクロソフトがＰｏｗｅｒＢＩに開発した、
「データの取得と変換」のツールです。
Ｅｘｃｅｌには、２０１６から実装されました。
Ｅｘｃｅｌでも、ピボットテーブルと組み合わせると、
データ分析やダッシュボードの開発などが、マウスによる設定で行えます。

パワークエリで、ピボットテーブルの使いにくさを解消できるのです！

詳細な解説は、別の記事で紹介します。先ず、１例を紹介しましょう！

課題：計画と実績　売上・粗利　月別比較表　

　　計画と実績のデータがあります。

比較１　計画・実績データからピボットテーブル
比較２　組み込み関数　基準との差　区分＝計画　で比較
比較３　パワークエリで　データを組替え、ピボットテーブル
　　　　　計画・実績　⇒　差異　売上・粗利　
　　　　　計画_売上、実績_売上、計画_粗利、実績_粗利
　　　　　差異_売上、差異_粗利
比較４　「集計フィールド」を追加する
　　　　　実見_売上＝計画_売上＋差異_売上
　　　　　実見_粗利＝計画_粗利＋差異_粗利

　では、４つの使い方で比較しましょう！

比較１　計画・実績データからピボットテーブル

　×「総計」　区分＝計画と実績　が合算される！
　ピボットテーブルを　累計　と　202301～202303　に２つ使う　！

比較２　組み込み関数　基準値との差　区分＝計画

値フィールドに　　売上、粗利　を追加
計算の種類　　基準値との差分
基準フィールド　区分　、基準アイテム　　計画

計算の種類：基準値との差分

差分　で　基準同志の差が計算される　⇒　列を非表示にすればＯＫ

比較３　パワークエリで　データを組替え

　　計画・実績　⇒　差異　売上・粗利　のデータに組み換え
　　計画_売上、実績_売上、計画_粗利、実績_粗利、差異_売上、差異_粗利

[問題点]
計画・・202301、202302、202303
実績・・202301、202302、　　　　
202303が未発生なので、単純に　差異＝実績－計画では、使いにくい！

未発生月は、差異＝0　にする！

◎年月で確定・未を選択　データの年月に「確定」を付与
　　　「確定」　⇒　差異　＝　実績－確定
　　　「未」　　　　差異　＝　０　にします　　例：202303

◎パワークエリの使い方は、みんなで使えるピボットテーブル活用術　
で解説します

確定・・年月を指定しなくても集計できる

比較４　「集計フィールド」を追加する

　　　　　実見_売上＝計画_売上＋差異_売上
　　　　　実見_粗利＝計画_粗利＋差異_粗利

確定＝202301～202302

全選択　・・　常に総計を視ながら管理できます！

ピボット姫のまとめ

ピボットテーブルの集計での制約は、パワークエリを利用して元データを加工すれば、克服できるものです。
いろいろな選択肢から、最善なものを「考える」＝応用力が大切です。

まとめ　どちらも活用しましょう！
分析には、ピボットテーブルを！

ピボット姫は、Ｅｘｃｅｌとピボットテーブルを比較してきました。

①簡単なレポートなら、Ｅｘｃｅｌでも十分です。
　あるいは、複雑な要素を組み込むなら、Ｅｘｃｅｌが優位です。
　　１つのレポートで済ます＝レントゲン撮影のような分析

②いろいろな見方で分析したり、繰り返し利用するなら、
　ピボットテーブルに優位性があります。
　　ＣＴスキャンのような分析が、マウス操作で可能です

③ピボットテーブルの制約は、パワークエリの活用で「克服」できます。

ピボット姫からＥｘｃｅｌ大王へのお願いがまとまりました。

「ピボットテーブルは、
　１レポートでの完成度はＥｘｃｅｌには劣りますが、
　総合的な分析力は、安心して繰り返し利用して欲しいものです。
　どちらも活用させてください。」

みなさんは、どのように評価しますか？

ピボットテーブルの紹介をしています！

ピボット姫と学ぼう！もっと楽しむサークルにメンバー募集中！

ピボットテーブルとパワークエリの組み合わせの事例紹介！

今回の計画・実績比較についても、詳細を紹介します！