![見出し画像](https://assets.st-note.com/production/uploads/images/108587663/rectangle_large_type_2_84232ded94d06fc318a1893f97120abe.png?width=1200)
ピボット姫と学ぼう!(4)自動レポートの呪い! ピボットテーブルは最終レポートにならない??
Excel ピボット姫と学ぼう!
前回は、「データ流出の怖さ」を紹介しました。
今回は、自動レポートの呪いです!
ピボットテーブルは、簡単に集計できるのに、
レポートが不完全で使えない!・・この呪いに罹っていませんか??
サンプル・エクセルをダウンロードしてください
1.ピボットテーブルは使えない??
ピボット姫は、Excel国のみんなが、ピボットテーブルを使い始めて、
「楽になった」というので、安心していました。
しかし、「レポートが上手く使えない!」「かえって手がかかる」・・
こんな不満の声が広まっていたのです。
お城の中でも、ひそひその噂です。
「ピボット姫が、みんなのピボットテーブルに呪いをかけている・・」
ミケが心配して、ピボット姫に報告に来ました!
全部で5問あります。
![](https://assets.st-note.com/img/1687065183312-MRv2d3auQM.png?width=1200)
問題1
数値なのに、個数になってしまう。手で合計に変更しなくては・・!
日付なのに、グループ化できない・・
問題2
文字は空白(ブランク)なのに、「(空白)」と、表示される!
集計すると、合計 / 個数 / が自動に付いて困る!
問題3
数値が空白(ブランク)なのに、「0」と表示される!
集計フィールドで、#DIV/0!と表示され、エラーが直せない!
問題4
レポートが固定できない!
「ピボットテーブルのフィルターが上に伸びる!
レポートの列幅を変更しても、元に戻る!
問題5
ピボットテーブルは、自動で大きなレポートになる。
みんなに配るには、フィルタ―で印刷など、手がかかる。
デザインが「色付き」で、配布資料の体裁が悪い・・!
2. ピボット姫の謎解き
ピボット姫は、これまで3つの呪いを解きました。
①マウスの呪い ・・ ドラッグ オプション
②仮面の呪い ・・ データキャッシュ テンプレート
③半死体の呪い ・・ データキャッシュ オプション
3つとも、エクセル・ブック全体の呪いでした。
今回は、個々のレポートに関する呪いのようです。
ミケの話から、みんなの困っている様子を整理すると・・
特徴1 沢山のレポートを使うと呪われる??
ピボットテーブルは、「データキャッシュ」を扱うツールです。
専用機能が沢山あり、Excelの知識では対応できません。
ピボットテーブルを作るだけなら、大丈夫です。
Excelなら、簡単に対応できるのに、
ピボットテーブルは、セルの対応・修正が難しいのです。
そのため、提出資料に使えない、という評価に・・??
沢山作ったり、コピーして使ったり、使い込もうという
熱心な人が罹る、恐ろしい呪いです!
特徴2 ピボットテーブルの初期設定??
初心者でも直ぐに使える設定になっています。
沢山使う人向けの設定ではありません。
熟練者には、初期設定のままでは使いにくいもの、
他の設定に変更した方が使いやすいもの、
その都度選択を判断したい設定もあります。
そこに、呪いの原点がありそうです!
3.ピボット姫の挑戦
それでは、5問の謎解きに挑戦しましょう!
サンプルの元データ・・ テーブル空白 細工しました。
![](https://assets.st-note.com/img/1687172775995-o26UZ3GsKe.png?width=1200)
黄色=空白(ブランク)、 ピンク=全角スペースのデータ、
日付も 5/29/2023 と '2023/5/29 文字形式にしています。
単価=売上金額/数量 #DIV/!、#VALUE!のエラーに設定しています。
問題1 型の判定
データの型・・文字、数値、日付・時刻 3つに区別されます
問題は、同じ列の中に、混在していると対応が異なります。
特に、数値の列に「スペース」(文字)や、日付に文字形式の場合です。
【型の判定ルール】
数値フィールド・・数値のみ 合計
数値・文字 個数
文字・日付 個数
行列フィールド 日付 グループ化
日付・文字混在 グループ化されない。
空白(ブランク)は大丈夫ですが、文字スペースとは、型が違います。
ピボットテーブルの自動作成も可能になってきました。
「元データの型」について、もっと理解が必要です!
実習①
数値データを値フィールドにドロップダウンします。
売上金額は、合計になりました。
しかし、数量、単価は・・個数 に、合計になりません。
![](https://assets.st-note.com/img/1687091371765-940NBw93Wy.png)
売上金額 ・・ 空白(ブランク) で 合計になりました。
数量・・空白(ブランク)、全角スペース
単価・・空白(ブランク)、全角スペース
⇒全角スペースが原因で、個数になりました!
合計 / 単価を 平均/単価にすると、#DIV/0!、#VALUE! エラー表示は、
困りますね。問題3で解決しましょう!
![](https://assets.st-note.com/img/1687164065852-eGgaIlL8hA.png?width=1200)
実習②
日付=年月日を行にドラッグします。
日付に文字(´2023/5/29)、5/29/2023 が含まれ、グループ化されません。
![](https://assets.st-note.com/img/1687162402105-F47QAV6TpZ.png?width=1200)
右のピボットテーブルの元データ・・日付の左に「’」を追加・更新すると、
日付のグループ化がリセットされてしまいます。
日本の日付書式 yyyy/mm/dd で入力 ⇒ 英語の書式 29/05/2023 表示
直接 29/05/2023 と入力すると、左詰め 文字に認識されます。
![](https://assets.st-note.com/img/1687172426137-NURGOUoimW.png?width=1200)
問題2 名前の表示、(空白)
①行・列フィールドでの「空白」
文字は空白(ブランク)なのに、「(空白)」と表示される自動設定です。この(空白) を 削除=消すことはできません。(null)
![](https://assets.st-note.com/img/1686982284342-KbqMHb7SmK.png?width=1200)
![](https://assets.st-note.com/img/1687164241569-tpb0aWNItf.png?width=1200)
テキスト編集・・行・列フィールド=文字の変更は可能です。
半角もしくは全角スペースを入力して、表示を上書きできます。
⇒ (空白) を △(全角スペース)に置き換えしました。
②値フィールドの自動設定 合計 / ・・
同様に、値フィールドの名前も変更できます。
合計 / 名前・・を同一の名前にはできません
![](https://assets.st-note.com/img/1687011365798-ocIwxz3M70.png)
名前の前後に半角スペースを残すことで、可能です。
![](https://assets.st-note.com/img/1687013082911-Epf6fLU2M1.png?width=1200)
問題3 値・・エラー、空白
①数値が空白(ブランク)なのに、「0」と表示される!
②エラー表示での対応できない!
解説:オプション・・書式 エラー、空白セルの取り扱い
個々のセル対応でなく、オプション設定で対応します。
そのピボットテーブル限定で、値フィールド全体の共通設定です。
個別の値フィールドには、書式の設定で行うことになります。
①数値の空白
Excelなら、セル毎に0と空白(ブランク)の使い分け可能です。
でも、ピボットテーブルの数値は、直接手直しできないので、困ります。
空白を埋める・・「なし」を入力しています
![](https://assets.st-note.com/img/1687093251368-uqm8fr3SVt.png)
![](https://assets.st-note.com/img/1687172377229-aBhR6MSX8l.png?width=1200)
②値などのエラー
集計フィールドで計算したのに、#DIV/0!と表示された!
ピボットテーブルの数値は直せない!どうしたら・・??
対応1 オプション 「エラー」の取り扱い
オプションで対応は、ピボットテーブルの値フィールド全体の設定です。
個別の値フィールドの設定はできません。
個別に対応するのは、書式の設定で行うことになります。
![](https://assets.st-note.com/img/1687011582712-wV8INxmlKL.png)
![](https://assets.st-note.com/img/1687164380509-K2ZFsgtjda.png?width=1200)
対応2 集計フィールド エラー対応
集計フィールドは、ピボットテーブルのお薦め機能です!
エラー対応でができないと、使ってもらえません。
Excelでは、Iferror関数で、対応できます。
集計フィールドでも、Iferror関数の組み込みが可能です。
エラーなら0にする。
セルの書式設定 0 ⇒ # にすれば 空白(ブランク)になります。
安心して、使ってください!
![](https://assets.st-note.com/img/1686923868741-9ZDsgGJqtH.png)
問題4 レポートフィルタ―、列幅
①レポートの列幅を変更しても、元に戻ってしまう
②ピボットテーブルのフィルターが上に伸びてレポートが崩れる!
①列幅の自動設定
2つのピボットテーブルがあり、「更新」すると、
列幅が変わります=セルの文字数に合わせた列幅になります。
![](https://assets.st-note.com/img/1686993185234-lERvcXcI2T.png?width=1200)
ピボットテーブルは、「列の幅の自動調整」に設定されています。
それを知らないで、皆さん使っています!
![](https://assets.st-note.com/img/1686993185339-5NouJsXc3U.png?width=1200)
Excelの列幅、行の高さの自動調整・・皆さん使っていますか?
⇒使わない人が「ほとんど」でしょう!
![](https://assets.st-note.com/img/1686993185167-Ze93M4baaa.png)
Excelの列幅・自動調整をするよりも、
セルの書式設定/縮小して全体表示を推奨します。
![](https://assets.st-note.com/img/1687012106957-PiJ5GeKgVF.png)
②レポートフィルタ―の自動設定
![](https://assets.st-note.com/img/1687163270677-KZYTNcp1Ne.png)
レポートフィルタ―の下から上、左から右 の違い 知っていますか?
![](https://assets.st-note.com/img/1686638373180-VHfVCIsGKx.png?width=1200)
下から上⇒左から右に、項目数を限定すると、レポートが固定できます。
見出し行の列幅を自動調整を解除し、「縮小表示」に、
フィールドの名前も簡潔にすると、
レポート表示の調整が楽になります。
![](https://assets.st-note.com/img/1687163172750-GY5yp54Itv.png)
レポートフィルタ―は、邪魔者と思われていますが、
「ピボット分析」では、役立ちます。
行・列フィルタ―の予備軍という位置づけです。
ドラッグでレイアウト変更が簡単にできるので、
1つ作って、コピー・加工することで、
分析のレベルアップを図ることができます。
問題5 印刷の設定はどこで?
「ピボットテーブルは、集計だけ」という誤解・・
本当に残念です。
レポート作成の作業が残って、これでは楽になりません。
私の30年の秘訣、
ピボットテーブルは、元データを工夫すれば、出力すれば良いのです。
行・列の配置を元データに追加ですれば、更新で、完了です。
ピボットテーブルで自動で大きなレポートができてしまう。
印刷してみんなに個々に配ろうとすると大変で困っている。
フィルタ―で印刷・・自動化するのも難しい・・。
![](https://assets.st-note.com/img/1687047311810-VzsbUpkIda.png?width=1200)
印刷して使わない・・集計だけの人が多いと思います。
でも、項目のアイテム別に改ページできると、
1回の印刷指示で、全レポートが印刷できます。
※例・・売店別の内訳を印刷するために、
「レポートフィルタ―ページの表示」による、シートの分割は不要です!
スライサーで選択すれば良いし、全部の印刷も簡単にできます。
![](https://assets.st-note.com/img/1687048053680-qaUyOHQqMW.png?width=1200)
![](https://assets.st-note.com/img/1687047457140-OBfaS6bKqC.png?width=1200)
①行・列の見出し自動設定
先ず、印刷タブでの設定が不要です。
ピボットテーブルのレイアウトに合わせて、自動的に設定されます。
2ページ以降にも見出し行を印刷できるようになります。
これだけ便利なのに、「初期設定」でないのは、不思議です??
是非、☑してください。
![](https://assets.st-note.com/img/1686925161761-lK7nzr3rec.png)
![](https://assets.st-note.com/img/1686638659332-lEaOKDNsxZ.png?width=1200)
②項目[フィールド]で改ページしたい
ヘルプでは検索できませんでした・・
Webで探して、「ピボットテーブルを印刷する」で見つかりました。
項目ごとに改ページしたい・・これは、フィールド設定で行います。
ピボットテーブル全体の設定ではなく、個々に選択するものです。
![](https://assets.st-note.com/img/1686925562257-XtOgIPBGmq.png)
注意事項は、配布する際には、「総計」は不要なので、印刷しない!
③デザインの自動設定
ピボットテーブルは、「淡い青、スタイル16」という、
皆さんのイメージが定着しています。
専門家・先生が「ピボットテーブルは最終レポートに使えない」というと、
そう思い込んでしまっています。
この「デザイン」は、EXCEL2007からの初期設定です。
それまでは、色は、自分で付けていました。
このデザインは、「なし」という名前です!
もしくは、デザインのクリア ・・これで「なし」になります!
ユーザーが自由にデザインできるので、「最終レポート」にできます!
4.「自動レポート」の呪いを解いて、
最終レポートで使いこなしましょう!
ピボット姫は、「自動レポートの呪い」を解きました。
ピボットテーブルの「初期設定」のままでは、使いこなせません。
呪いを解くには、使いやすい設定に変更して使うことです。
行列見出しの設定を知らないと、活用できません。
データキャッシュ全体、ピボットテーブル全体、個々の項目別、それぞれの違いを理解し、組み合わせて使いこなしましょう!
5.マイクロソフトはヘルプの改善を!
ピボットテーブルのオプションなどにヘルプ解説があります。
応用編などの紹介は不十分です。是非、改善をして欲しいです。
私は、30年ユーザーとしての実践経験から、本を書きました。
Excel実践ピボット革命 2022年8月
こういうノウハウを、ヘルプの充実にお役にたてると思います。
◎パワークエリとピボットテーブルの活用事例の紹介です!
◎ピボット姫と学ぼう!ピボットテーブルを楽しむ仲間募集中!
この記事が気に入ったらサポートをしてみませんか?