ピボットテーブル11Q (9)集計が合わない・・!
ピボット姫と学ぼう!(9)「呪い」のサンプルに挑戦!!
この内容をエクセル・サンプルで実習出来ます!
ピボットテーブルで集計しても、合わないことがあるので、
電卓で検算する・・ こういう人も多いようです。
便利な機能のはずなのに・・結局、使えない・・
集計されているはずなのに、集計できない!
他の人が作ったピボットテーブルは使いにくい!
こういう現象の原因は、
①更新 を忘れている
②元データ・・範囲などが不適
③フィルタ―・・新規アイテムが除外される
④集計フィールドの「端数」
ということが、多いようです。
解説書で紹介しているのは、①と②です。
でも、③と④は、紹介していません。
私は、約30年間実践して③と④が判ってきました。
その実践ノウハウを本にまとめて、紹介しています。
皆さんに、この秘訣を知ってもらえば、
ピボットテーブルで困ることが少なくなるはずです。
1.更新 を忘れている!
ピボットテーブルは、エクセルの関数と違い、
元データを読み込んで分析するツールです。
自動再計算はされません。
自分で「更新」をしないと、レポートは最新になりません。
[Q2. なぜ「更新」が必要なの?]
◎更新を忘れないようにする方法
・元データの取り込み作業と連動させる
・データ集計・分析を利用する
⇒ピボットテーブルのメリットを活用するようにすれば、
「更新」の煩わしさも解消します!
2.元データ・・範囲などが不適
ピボットテーブルの元データの範囲を正しく設定しないと、
当然、集計されません!
範囲をセルの起点⇒終点で、設定した場合は、
その都度最新の範囲に修正して「更新」しなければなりません。
エクセル2007から使えるようになった「テーブル」は、
この元データを管理するのには、大変便利な機能です。
でも、途中で「空白行」があったり、
最下行にデータをコピー・貼付けして追加しようとして、
テーブルに追加されないことが、度々起こります。
3.ピボットテーブルのフィルター特性
Q7.エクセル機能との違いは?で紹介をしています。
フィルターの「選択優先」と「除外優先」
私の「造語」です!
事例:左 商品Aのみ「選択」 ⇔ 右 商品Aのみ「除外」
これに、新商品B4が追加になったら・・
結果:ピボットテーブルには、新商品B4は追加されません。
⇒右のピボットテーブルは、正しく計算されている。
左のピボットテーブルは、間違っている!
※新アイテムは「除外」される初期設定になっています!
フィルタ―で絞り込むと、新規分は外れます!
選択優先=選択したままの集計ならOkです
除外したものを継続=除外を優先 する場合には、
新規分は追加されません!漏れてしまいます!
⇒これが、ピボットテーブルの集計が合わない という原因です。
対応:各項目別「フィールド設定」です。
※注意:フィルター機能の設定ではありません。
商品名の見出し フィールド設定を確認して下さい。
フィルタ― □新しいアイテムを手動フィルターに含める
という「初期設定」です。
「手動フィルター」・・英語の翻訳なので、理解しにくいです!
反対語の、自動フィルターなんて無いです!
「フィルターを使用している場合」というのが、正しい日本語です!
ピボットテーブルのヘルプなどの説明は、こういう状態です!
全て使ってみなければ判らない「未知のメニュー」です。
エクセルとピボットテーブルのフィルターの違い
エクセルのフィルタ―
元データそのもののフィルタ―で、1回限りです。
ピボットテーブルのフィルタ―
元データを追加・変更して、何度も繰り返し使用する。
⇒フィルタ―をリセットして使えば、問題ありません。
リセットしないで使うと・・「正しく集計されない」ことも・・
ピボットテーブルの「フィルター」は2つの違いを理解する!
新しいアイテムを追加してはダメな場合・・選択優先
新しいアイテムを追加する必要がある場合・・除外優先
※Microsoftに改善要望しました!応援お願いします!
ピボットテーブルのフィルターに「新規アイテムの追加」設定の選択と表示 · Community (microsoft.com)
①ヘルプでの解説を改善する
⇒このフィルタ―の違いをもっと判りやすく解説する
②行・列のフィールド設定での変更は判りずらい
⇒フィルタ―に、この違いを「視覚的」に表示させる
例:色などを付ける
4.集計フィールドの「端数」
11Q-8で紹介した集計フィールドです。
列の集計値を使い、ユーザーが独自の数式を追加できます!
手数料や消費税などの計算をする事例が紹介されています。
元の数値で計算して集計したものと、
集計フィールドで計算した数値と合わないことが、あります!
元の数値でなく、集計値で計算するので「端数」で差異が生じる
⇒「集計フィールド」は、正しく計算しています!
ピボットテーブルの特性を理解して、使い分けしてください!
5.他人が作ったピボットテーブル!
自分が作ったピボットテーブルは、
元データの範囲、フィルタ―の使い方 など、判っています。
自分の作ったピボットテーブルを他の人に使ってもらうときは、
「引継ぎ」「説明」をしましょう!
特に、フィルターの「選択優先」を「除外優先」に
設定を変更したら、注釈メモをつけましょう!
他の人の作ったピボットテーブルを使うときに、
元データの範囲などを確認するポイントを整理しました。
ピボットテーブルのチェック10事項をまとめました。
詳細は、ピボット姫と学ぼう!(9)「呪い」のサンプルに挑戦!!
で解説しています。
記事を読むだけでは「知識」のままです。
実際に、サンプルで確認して、身につけましょう!
ピボット姫と学ぼう!(9)「呪い」のサンプルに挑戦!!
ピボットおじさんの無料相談窓口 30分ZOOM 受け付けています!
ピボットテーブルのファンクラブで一緒に謎解きを楽しもう!
Excel ピボットテーブル 11Q [イチイチ・キュー]
他にもピボットテーブルの疑問に答える記事を紹介しています。
この記事を書く30年の探究物語を紹介中!
もっと知りたい方には
こちらの本とセミナーで紹介しています
30年の実践経験からまとめています。
この記事が気に入ったらサポートをしてみませんか?