リストデータは見づらい？ ピボットテーブルで解決！

データはテーブル化した方が見やすいよ！

ということで、テーブル向きのリスト形式のデータをオススメしました。

が、やはり人間が見やすいマトリックスでデータを見たいときもありますよね。
そんなときにはピボットテーブルを使いましょう。

ピボットテーブル｜関数なしでも集計できる！

ピボットテーブルの作成方法を見るために、次のテーブルを使用します。
テーブル名は「売上利益」、ヘッダーは「日付」「商品名」「売上高」「営業利益」です。

テーブルのどっかを選択しておいて、「挿入」タブから「ピボットテーブル」をぽちっとしてください。

範囲と共に、ピボットテーブルを新規シートに作るか、既存シートのどこかに作るかを確認されます。
既存シートのどこかに作っても構いませんがピボットテーブルと既存データが干渉してもつまらんので、こだわりがなければ新規シートでの作成で構わんでしょう。

すると、こんなシートができました。

出来立てほやほやピボットテーブルには何のデータも追加されていないので、見たいデータにチェックを入れて、それを行に持ってきたいのか、列にしたいのか、それともデータとして値に持ってくるのか、ドラッグします。
変な場所にドラッグしちゃっても大丈夫。ドラッグ&ドロップで簡単に修正できます。

こんな風に設定すると。

こんなピボットテーブルになります。

あれっ、商品と日付のマトリックスじゃん！
そうです。リスト形式のデータでもピボットテーブルを使えばあっという間にマトリックスデータを作成できます。
なのでリストデータしかなくても安心してください。

日付ごとの集計じゃ細かくて見づらいので月別にしたいな。
というときには、ピボットテーブルでも簡単です。
「日付」のどこかを選択し、右クリックして月単位で「グループ化」してやればOK…って、web版にはグループ化機能がないのか！

というわけで応急処置です。
元のテーブルに「日付」から「月」をピックアップしたカラムを追加します。
月はMONTH関数で拾えます。

ピボットテーブルに戻り、データを「すべて更新」してください。
そうすると「月」が追加されますので、それを列に入れて日付を消してしまえばOKです。

危ない危ない。関数を知らなくてもいいとか言いながら、元データの整備にはやはり必要です。
デスクトップアプリ版ならこの手の機能はフルで使えるはずなので、ご安心を…。

ピボットテーブルもデザインなどいろいろ弄れますから、お好きなようにやっちゃってください。

ピボットテーブルは変更も簡単！

自分でマトリックスを作るとなるとレイアウトを変えるのは手間ですが、ピボットテーブルなら見せたいものを見せたい領域にポンと放り込むだけなので、あっという間に好きなように組み替えられます。

売上高の合計じゃなくて、販売した個数を確認したい。というときも大丈夫。
値の領域で「合計/売上高」を選択し、▼から「値フィールドの設定」を選択します。

集計方法を「個数」に変更してOKをぽちっとな。

あっという間に個数で集計してもらえました。

集計の追加も楽（web版でなければ）

売上高と営業利益があるから営業利益率も出したいね。
となってもご安心。
「ピボットテーブル分析」タブから「フィールド/アイテム/セット」の「集計フィールド」を挿入してやれば…って、これもweb版にはない機能かよ！

「集計フィールドの挿入」では「=営業利益/売上高」くらいの設定はちゃちゃっとできるので、デスクトップアプリ版をお使いの方は活用してください。

web版でも先ほどのMONTH関数みたいに元データを弄って営業利益率を出してやれば済む話ですが、データによっては元のデータ仕様がガッチガチに固まっていて、あまり触りたくないという場合もあると思います。
そんなときはピボットテーブル上で集計フィールドを使った方が気安く使えると思います。

出来上がったピボットテーブルを使って何か作れる？

せっかくピボットテーブルがあるから、これを使っていろいろ加工できるのでは？

…そうですね、できますね。やってみましょうか。

1月の商品別の営業利益率を出してみようということで、C12セルでこんな計算をやってみました。

これでいいように見えますね。
ところがこれ、ピボットテーブルの中をガチャガチャ弄ると途端にダメになる数式です。
例えば商品名の並び替えをしちゃうと、5行目は「バナナ」以外のものになって、全然狙い通りの式にならない。

それを避けるために、ピボットテーブルからデータを引っ張ってくるときはGETPIVOTDATA関数を使います。

なんかよくわかんないと思いますので、とりあえず営業利益の値だけ引っ張ってきましょう。

最初の引数は、何のデータを持ってきたいかという指定です。これをピボットテーブルに表示されているのと同じ表記で指定します。
この式では営業利益を引っ張ってきたいので「合計 / 営業利益」と指定しています。

次の引数は、対象となるピボットテーブルの位置指定。
B2から始まっているテーブルが対象なので「$B$2」としています。
別のシートにあるピボットテーブルを参照すると、シート名もつきます。

次からは、何の項目（フィールド）に対して何の条件で持ってくるか、という指定です。
この場合は、「商品名」の中から「バナナ」を、更に「月」から「1」を指定するのでこういう記述になります。

これを元に売上高も記述してやると、こんな式になります。

ほかの関数のように「バナナ」とか文字列になっているところをセルからの参照にしてやれば、別の商品もコピペで簡単に算出できます。

ということでGETPIVOTDATA関数は便利です。
便利ですよ、便利なんだけどさあ。
ワタシも割と頻繁に使う関数ではありますが、あまり本質的ではないというか、ちょっとなんだかなーと思う関数でもあります。

思い出してください、そもそもピボットテーブルには元データがあるじゃないですか。
それを加工してピボットテーブルにして、その加工したモノに更に関数を噛ませるって、まどろっこしくね？
元データからXLOOKUP関数でサッと持ってこればいい話じゃないですか。

それに、ピボットテーブルはガチャガチャ弄り倒してなんぼの機能です。
「これはどうかな」「アレとソレを比較したらどうかな」とフィールドを変えたりフィルターをかけたり並べ替えたりするのが前提の、流動的に使えるのが魅力の機能です。
ところが、ガチャガチャやっている間にGETPIVOTDATA関数で使っているフィールドを消したりすると、関数がエラーになってしまう。

というわけで、ワタシはこんな風に使っています。

• 欲しいデータがガチッと決まっているなら、ピボットテーブルの元データから引っ張ってくる
• ピボットテーブルは流動的に使用するのが前提で、そこからデータは参照しない
• どうしてもピボットテーブルのデータを使用したいときはGETPIVOTDATA関数を使うが、ピボットテーブルを弄る間にダメになるのが前提の仮初の代物で、人様にはお出ししない

おわりに

ピボットテーブルはガッチャガッチャと弄れる、いわばデータの試行錯誤の場のようなものでして、便利なんだけど最終資料としては使いづらいかなあ…というのがワタシの個人的な見解です。
それでも大量のデータをあっという間に捌いてくれるので、なくてはならない存在です。

それにしても、web版にこんなに機能の制約があるなんて…！ Microsoftめ！