ピボットテーブルは仕分けと集計に便利な機能です。
Excelの存在価値はピボットテーブルありき、そう感じるケースも多くあります。
ただ、出来上がったピボットテーブルは見た目、使い方ともにいいものか、となるとあまりそう感じないこともあります。
なので、ピボットテーブルをレポート用に作成する場合は、他のシートにピボットテーブルを値貼り付けし、ピボットテーブルの書式から一旦外してから見た目の調整をしたり、内容を変更したりします。
今回は、ピボットテーブルを、ピボットテーブルのまま、使うにはどうするか、アイデアを紹介します。
GETPIVOTDATA関数になる
ピボットテーブル使いにくい第一位は、計算式でピボットテーブルの集計値を参照しようとすると、GETPIVOTDATA関数になってしまうことです。
計算式を作ろうとして、ピボットテーブル以外の範囲のセルに=をうち、ピボットテーブルのデータをクリックするとGETPIVOTDATAなんとかという文字が勝手に入ってしまいます。
これを避けるために、自分でそのデータのアドレスをE10などのように手入力します。
これが面倒だし、後で何をやっていたか見直した時、直感的にわかりにくいのです。
この対処は、ピボットテーブルをクリックして、リボンのピボットテーブルの分析タブの左側にあるピボットテーブルグループの中にあるオプションの中のGetPivotDataの生成のチェックを外します。
すると、そのピボットテーブルを参照したときには、セルのアドレスでE10のような形で入ります。
GETPIVOTDATA関数にならなくするのですが、これは本来のピボットテーブルの使い方ではありません。
ピボットテーブルは、一度作成したら、また別の角度で分析をするようにできています。
分析するたびに表のレイアウトが変わりますが、アドレス指定をされていると、参照元が変わってしまいます。
GETPIVOTDATA関数になっていれば、ある程度ではありますが、そのピボットテーブルのその値を追いかけてくれるので、そういう意味では便利なのですね。
ただピボットテーブルを何回もいろいろな角度で分析するような使い方を実際の現場ではしているかというとしていないので、GETPIVOTDATA関数になるのは不便になっているのですね。
項目名を変える
ピボットテーブルでは、項目名に「合計」や「データの個数」のような文字が入ります。
これも邪魔な時があります。
この削除は、単にセルの中の文字を消すという方法でも変更できるのですが、正しくはその項目名のセルをダブルクリックして、出てくる値フィールドの設定の名前の入力ボックスで変更します。
設定する名前は、元の項目名と同じにしたい時がほとんどですが、元の項目名と全く同じ文字にすることはできません。
なので、元の項目名に「合計」などのような文字を追加して、なるべく違和感のない項目名にするといいでしょう。
例えば元の項目名が販売額で、ピボットテーブルで集計された項目名としてふさわしいのは「販売額合計」や「販売額週合計」のような項目名でしょう。
文字列項目もグループ化したい
ピボットテーブルでは、数値や日付のデータはグループ化できます。
ピボットテーブルを作ったら、グループにまとめられそうな時はグループにまとめて見ると新しい何かを発見できるかもしれません。
しかし、右クリックしてグループ化すると、文字列の項目はグループ化できないように見えます。
文字列項目のグループ化は可能です。
例えば次のようなピボットテーブルの場合、商品グループAとBに分けたいとしたら。
Aの項目を全部選んで右クリックでグループ化。
そしてBの項目を選んでグループ化です。
+、-のボタンで詳細データを開け閉めできます。
グループ名の変更は、グループ名が入っているセルをクリックして数式バーで行います。
ピボットテーブルの値がおかしい
ピボットテーブルの集計値がおかしいときは、その元になっているデータを見ます。
見方は簡単で、集計値が合わないとイライラするじゃないですか、そのついでに、集計値のセルをダブルクリックしてください。
その元になっているデータ一覧を新規シートに出すことができます。
営業所ごとのピボットテーブルを出したい
次のような取引先ごとの最近の取引日と今までの取引回数をまとめてピボットテーブルがあります。
このピボットテーブルを、札幌、仙台、東京の3つの営業所ごとに作りたい場合、もちろん元データに営業所データはないといけないのですが、その営業所の項目をフィルターに配置します。
リボンのピボットテーブルツールの分析タブの中のピボットテーブルの中のオプションの中、レポートフィルターページの表示をクリックします。
すると、どの項目ごとにピボットテーブルを作るか聞いてくるので、選んでOKします。
シートに分かれて営業所ごとのピボットテーブルが出来上がります。
コメント