"Tables" by Jurgen Leckie is licensed under CC BY-ND 2.0
わたしは今でもExcel大好きおじさんなのですが、「何度やっても忘れてしまう操作」というのがあるわけで。
特にピボットテーブルで起こり勝ちで、そのたびにググるのも面倒なので、ここにメモしておきます。
ピボットテーブルでハマる①データにないフィールド値の消失
なんでもいいのですが、テストで出たバグのどっかからエクスポートしてきて、Excelでちょいちょい集計をしたいとしましょう。
フィールドには「連番」「発生日」「機能」「重要度」があるとします。
こんな感じ。
これを、機能×重要度のピボットテーブルにしてみましょう。
3つの機能、5つの重要度があるので、15個のデータセルが出てきます。
ここで、機能Aを非表示にしてみましょう。すると・・・？
このように、重要度「Pessimal」と「Show-Stopper」が表示されなくなってしまいます！
機能Bと機能Cにはこの重要度をもつデータがないので、自動的に非表示になるのですね。
なお機能C×重要度Endsvilleのデータもないのですが、機能B×重要度Endsvilleのデータはあるので、ピボットテーブル上では前者はゼロ件（空白）として表現されています。
重要度のカテゴリーは、機能をフィルタしてもしなくても、一定にしておきたいですよね。
これを解決しましょう。
まず該当するフィールドを右クリック。
今回の場合は「重要度」の列の上です。集計値の入ったセル上での右クリックではダメです。
フィールドの設定画面の、データのないアイテムを表示するチェックボックスをチェック。
無事、表示されました。
ピボットテーブルでハマる②なぜか残っているフィールド値
①のデータのないアイテムを表示するでよく出くわすのですが、「データセット上にないフィールド値」が亡霊のように現れることがあります。
こんな感じ。
「亡霊」という重要度をもつデータは1つもないのに、現れてしまいます。
これは、「過去のデータにはあった重要度」なんですよね。ピボットテーブルはこれを覚えているようです。「テーブル上にデータがなくなっても、カテゴリとしては残しておきたい」といった需要に応えるものと思いますが、基本的には「ゴミ」であることが多い。
以下の手順で消しましょう。
ピボットテーブル オプション画面のデータ ソースから削除されたアイテムの保持-1 フィールドに保持するアイテム数で「なし」を選択し、ピボットテーブルを更新する。
消えました。
ピボットテーブルでハマる③日付なのに日付軸っぽくないピボットグラフ
次に、元のデータセットを日付と機能のピボットテーブルにしてみます。
これを横軸日付のピボットグラフにして、バグ発生のトレンドを見てみましょう！
・・・え？ いや、まあそうだけど。確かに10/6のデータはないからゼロ件なんだけど、もっとこう、日付らしく並ばない？ 10/4～10/5の間隔と、10/5～10/9の間隔って、違わない？
なおこれは、グラフの設定で横軸を「日付軸」にしても改善されません。
ここで①の手順データのないアイテムを表示するを行います。すると、ヤバいことが起こる。
存在する日付すべての行が現れてしまう。
もちろん、超過去と超未来は丸めてくれているけれど・・・。
もう少し何とかしてあげしょう。
発生日フィールドを右クリックし、グループ化。
各フィールドで、いらない部分をフィルタしていきましょう。
今回は10月と11月だけを見ればよさそうなので、「月」で絞る。
いい感じのピボットテーブルになって・・・
グラフも、バグゼロの日がちゃんと表示され、傾向がわかるようになった！
おわりに
完全に自分用のエントリーでした。
3つ目のは特に、VLOOKUP 関数使えば？という異論はあるでしょうけど、わたしはピボットテーブル派なんですよ！
