ピボット姫と学ぼう！(4)自動レポートの呪い！ ピボットテーブルは最終レポートにならない？？
Excel ピボット姫と学ぼう！
前回は、「データ流出の怖さ」を紹介しました。
今回は、自動レポートの呪いです！
ピボットテーブルは、簡単に集計できるのに、
レポートが不完全で使えない！・・この呪いに罹っていませんか？？
サンプル・エクセルをダウンロードしてください
１．ピボットテーブルは使えない？？
ピボット姫は、Ｅｘｃｅｌ国のみんなが、ピボットテーブルを使い始めて、
「楽になった」というので、安心していました。
しかし、「レポートが上手く使えない！」「かえって手がかかる」・・
こんな不満の声が広まっていたのです。
お城の中でも、ひそひその噂です。
「ピボット姫が、みんなのピボットテーブルに呪いをかけている・・」
ミケが心配して、ピボット姫に報告に来ました！
全部で５問あります。
問題1
数値なのに、個数になってしまう。手で合計に変更しなくては・・！
日付なのに、グループ化できない・・
問題2
文字は空白（ブランク）なのに、「（空白）」と、表示される！
集計すると、合計 / 個数 / が自動に付いて困る！
問題3
数値が空白（ブランク）なのに、「０」と表示される！
集計フィールドで、#DIV/0!と表示され、エラーが直せない！
問題4
レポートが固定できない！
「ピボットテーブルのフィルターが上に伸びる！
レポートの列幅を変更しても、元に戻る！
問題５
ピボットテーブルは、自動で大きなレポートになる。
みんなに配るには、フィルタ―で印刷など、手がかかる。
デザインが「色付き」で、配布資料の体裁が悪い・・！
２. ピボット姫の謎解き
ピボット姫は、これまで３つの呪いを解きました。
①マウスの呪い ・・ ドラッグ オプション
②仮面の呪い ・・ データキャッシュ テンプレート
③半死体の呪い ・・ データキャッシュ オプション
３つとも、エクセル・ブック全体の呪いでした。
今回は、個々のレポートに関する呪いのようです。
ミケの話から、みんなの困っている様子を整理すると・・
特徴1 沢山のレポートを使うと呪われる？？
ピボットテーブルは、「データキャッシュ」を扱うツールです。
専用機能が沢山あり、Ｅｘｃｅｌの知識では対応できません。
ピボットテーブルを作るだけなら、大丈夫です。
Ｅｘｃｅｌなら、簡単に対応できるのに、
ピボットテーブルは、セルの対応・修正が難しいのです。
そのため、提出資料に使えない、という評価に・・？？
沢山作ったり、コピーして使ったり、使い込もうという
熱心な人が罹る、恐ろしい呪いです！
特徴2 ピボットテーブルの初期設定？？
初心者でも直ぐに使える設定になっています。
沢山使う人向けの設定ではありません。
熟練者には、初期設定のままでは使いにくいもの、
他の設定に変更した方が使いやすいもの、
その都度選択を判断したい設定もあります。
そこに、呪いの原点がありそうです！
３．ピボット姫の挑戦
それでは、５問の謎解きに挑戦しましょう！
サンプルの元データ・・ テーブル空白 細工しました。
黄色＝空白(ブランク)、 ピンク＝全角スペースのデータ、
日付も 5/29/2023 と '2023/5/29 文字形式にしています。
単価＝売上金額/数量 ＃DIV/!、＃VALUE！のエラーに設定しています。
問題１ 型の判定
データの型・・文字、数値、日付・時刻 ３つに区別されます
問題は、同じ列の中に、混在していると対応が異なります。
特に、数値の列に「スペース」(文字)や、日付に文字形式の場合です。
【型の判定ルール】
数値フィールド・・数値のみ 合計
数値・文字 個数
文字・日付 個数
行列フィールド 日付 グループ化
日付・文字混在 グループ化されない。
空白(ブランク)は大丈夫ですが、文字スペースとは、型が違います。
ピボットテーブルの自動作成も可能になってきました。
「元データの型」について、もっと理解が必要です！
実習①
数値データを値フィールドにドロップダウンします。
売上金額は、合計になりました。
しかし、数量、単価は・・個数 に、合計になりません。
売上金額 ・・ 空白(ブランク) で 合計になりました。
数量・・空白(ブランク)、全角スペース
単価・・空白(ブランク)、全角スペース
⇒全角スペースが原因で、個数になりました！
合計 / 単価を 平均/単価にすると、＃DIV/0！、＃VALUE! エラー表示は、
困りますね。問題３で解決しましょう！
実習②
日付＝年月日を行にドラッグします。
日付に文字(´2023/5/29)、5/29/2023 が含まれ、グループ化されません。
右のピボットテーブルの元データ・・日付の左に「’」を追加・更新すると、
日付のグループ化がリセットされてしまいます。
日本の日付書式 yyyy/mm/dd で入力 ⇒ 英語の書式 29/05/2023 表示
直接 29/05/2023 と入力すると、左詰め 文字に認識されます。
問題２ 名前の表示、(空白)
①行・列フィールドでの「空白」
文字は空白（ブランク）なのに、「（空白）」と表示される自動設定です。この(空白) を 削除＝消すことはできません。(null)
テキスト編集・・行・列フィールド＝文字の変更は可能です。
半角もしくは全角スペースを入力して、表示を上書きできます。
⇒ (空白) を △(全角スペース)に置き換えしました。
②値フィールドの自動設定 合計 / ・・
同様に、値フィールドの名前も変更できます。
合計 / 名前・・を同一の名前にはできません
名前の前後に半角スペースを残すことで、可能です。
問題３ 値・・エラー、空白
①数値が空白（ブランク）なのに、「０」と表示される！
②エラー表示での対応できない！
解説：オプション・・書式 エラー、空白セルの取り扱い
個々のセル対応でなく、オプション設定で対応します。
そのピボットテーブル限定で、値フィールド全体の共通設定です。
個別の値フィールドには、書式の設定で行うことになります。
①数値の空白
Ｅｘｃｅｌなら、セル毎に0と空白（ブランク）の使い分け可能です。
でも、ピボットテーブルの数値は、直接手直しできないので、困ります。
空白を埋める・・「なし」を入力しています
②値などのエラー
集計フィールドで計算したのに、#DIV/0!と表示された！
ピボットテーブルの数値は直せない！どうしたら・・？？
対応１ オプション 「エラー」の取り扱い
オプションで対応は、ピボットテーブルの値フィールド全体の設定です。
個別の値フィールドの設定はできません。
個別に対応するのは、書式の設定で行うことになります。
対応２ 集計フィールド エラー対応
集計フィールドは、ピボットテーブルのお薦め機能です！
エラー対応でができないと、使ってもらえません。
Ｅｘｃｅｌでは、Ｉｆｅｒｒｏｒ関数で、対応できます。
集計フィールドでも、Ｉｆｅｒｒｏｒ関数の組み込みが可能です。
エラーなら０にする。
セルの書式設定 ０ ⇒ ＃ にすれば 空白(ブランク)になります。
安心して、使ってください！
問題４ レポートフィルタ―、列幅
①レポートの列幅を変更しても、元に戻ってしまう
②ピボットテーブルのフィルターが上に伸びてレポートが崩れる！
①列幅の自動設定
2つのピボットテーブルがあり、「更新」すると、
列幅が変わります＝セルの文字数に合わせた列幅になります。
ピボットテーブルは、「列の幅の自動調整」に設定されています。
それを知らないで、皆さん使っています！
Ｅｘｃｅｌの列幅、行の高さの自動調整・・皆さん使っていますか？
⇒使わない人が「ほとんど」でしょう！
Ｅｘｃｅｌの列幅・自動調整をするよりも、
セルの書式設定／縮小して全体表示を推奨します。
②レポートフィルタ―の自動設定
レポートフィルタ―の下から上、左から右 の違い 知っていますか？
下から上⇒左から右に、項目数を限定すると、レポートが固定できます。
見出し行の列幅を自動調整を解除し、「縮小表示」に、
フィールドの名前も簡潔にすると、
レポート表示の調整が楽になります。
レポートフィルタ―は、邪魔者と思われていますが、
「ピボット分析」では、役立ちます。
行・列フィルタ―の予備軍という位置づけです。
ドラッグでレイアウト変更が簡単にできるので、
１つ作って、コピー・加工することで、
分析のレベルアップを図ることができます。
問題５ 印刷の設定はどこで？
「ピボットテーブルは、集計だけ」という誤解・・
本当に残念です。
レポート作成の作業が残って、これでは楽になりません。
私の３０年の秘訣、
ピボットテーブルは、元データを工夫すれば、出力すれば良いのです。
行・列の配置を元データに追加ですれば、更新で、完了です。
ピボットテーブルで自動で大きなレポートができてしまう。
印刷してみんなに個々に配ろうとすると大変で困っている。
フィルタ―で印刷・・自動化するのも難しい・・。
印刷して使わない・・集計だけの人が多いと思います。
でも、項目のアイテム別に改ページできると、
１回の印刷指示で、全レポートが印刷できます。
※例・・売店別の内訳を印刷するために、
「レポートフィルタ―ページの表示」による、シートの分割は不要です！
スライサーで選択すれば良いし、全部の印刷も簡単にできます。
①行・列の見出し自動設定
先ず、印刷タブでの設定が不要です。
ピボットテーブルのレイアウトに合わせて、自動的に設定されます。
２ページ以降にも見出し行を印刷できるようになります。
これだけ便利なのに、「初期設定」でないのは、不思議です？？
是非、☑してください。
②項目[フィールド]で改ページしたい
ヘルプでは検索できませんでした・・
Ｗｅｂで探して、「ピボットテーブルを印刷する」で見つかりました。
項目ごとに改ページしたい・・これは、フィールド設定で行います。
ピボットテーブル全体の設定ではなく、個々に選択するものです。
注意事項は、配布する際には、「総計」は不要なので、印刷しない！
③デザインの自動設定
ピボットテーブルは、「淡い青、スタイル16」という、
皆さんのイメージが定着しています。
専門家・先生が「ピボットテーブルは最終レポートに使えない」というと、
そう思い込んでしまっています。
この「デザイン」は、EXCEL2007からの初期設定です。
それまでは、色は、自分で付けていました。
このデザインは、「なし」という名前です！
もしくは、デザインのクリア ・・これで「なし」になります！
ユーザーが自由にデザインできるので、「最終レポート」にできます！
４．「自動レポート」の呪いを解いて、
最終レポートで使いこなしましょう！
ピボット姫は、「自動レポートの呪い」を解きました。
ピボットテーブルの「初期設定」のままでは、使いこなせません。
呪いを解くには、使いやすい設定に変更して使うことです。
行列見出しの設定を知らないと、活用できません。
データキャッシュ全体、ピボットテーブル全体、個々の項目別、それぞれの違いを理解し、組み合わせて使いこなしましょう！
５．マイクロソフトはヘルプの改善を！
ピボットテーブルのオプションなどにヘルプ解説があります。
応用編などの紹介は不十分です。是非、改善をして欲しいです。
私は、３０年ユーザーとしての実践経験から、本を書きました。
Ｅｘｃｅｌ実践ピボット革命 ２０２２年８月
こういうノウハウを、ヘルプの充実にお役にたてると思います。
◎パワークエリとピボットテーブルの活用事例の紹介です！
◎ピボット姫と学ぼう！ピボットテーブルを楽しむ仲間募集中！