ピボット姫と学ぼう！（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
全選択 ・・ 常に総計を視ながら管理できます！
ピボット姫のまとめ
ピボットテーブルの集計での制約は、パワークエリを利用して元データを加工すれば、克服できるものです。
いろいろな選択肢から、最善なものを「考える」＝応用力が大切です。
まとめ どちらも活用しましょう！
分析には、ピボットテーブルを！
ピボット姫は、Ｅｘｃｅｌとピボットテーブルを比較してきました。
①簡単なレポートなら、Ｅｘｃｅｌでも十分です。
あるいは、複雑な要素を組み込むなら、Ｅｘｃｅｌが優位です。
１つのレポートで済ます＝レントゲン撮影のような分析
②いろいろな見方で分析したり、繰り返し利用するなら、
ピボットテーブルに優位性があります。
ＣＴスキャンのような分析が、マウス操作で可能です
③ピボットテーブルの制約は、パワークエリの活用で「克服」できます。
ピボット姫からＥｘｃｅｌ大王へのお願いがまとまりました。
「ピボットテーブルは、
１レポートでの完成度はＥｘｃｅｌには劣りますが、
総合的な分析力は、安心して繰り返し利用して欲しいものです。
どちらも活用させてください。」
みなさんは、どのように評価しますか？
ピボットテーブルの紹介をしています！
ピボット姫と学ぼう！もっと楽しむサークルにメンバー募集中！
ピボットテーブルとパワークエリの組み合わせの事例紹介！
今回の計画・実績比較についても、詳細を紹介します！
この記事が気に入ったらサポートをしてみませんか？