[EXCEL] 集計入門　クロス集計、どうやる？ ピボットテーブルか？ COUNTIFSか？SUMPRODUCT関数か？　その１　ピボットテーブル編（非推奨）

▶MAP▶PDF版▶表示▶関数▶ショトカ▶操作/コピー▶実務▶NG▶検索

【まとめ】
・ピボットテーブルは便利だが、うまくいかない場合もある。
・ピボットテーブルは集計の「当たり」を出すには便利

【説明】
例えば、こんなデータがあるとします。
職員（従業員）の通勤方法と所要時間のデータです。

実際は、「１往復当たりの料金」欄があってもよさそうですが、今回は省略します。

「通勤方法」は、実際は２つ以上あるでしょうけれど（徒歩＋電車）、「主たるもの」としておきます。
余談ですが、地方だと「バス」＋「電車」は少なそうです。
バスの本数が少ないし、だったら自家用車で通勤しちゃいそうです。

それはともかく、
「このデータ、集計しておいて」と言われたらどうすればいいでしょうか？

分かりやすいのは、「通勤方法」と「通勤時間」のそれぞれを集計するもの
つまり・・・

こんな表になります。
でも、この表だと通勤方法と通勤時間の関係がわかりません。

もし、集計の意図が「通勤方法によってどの程度通勤時間が違うか」だとすれば、上の表では、それがわかりません（なぜ、その集計結果を把握したいか、は今回置いておきます）。

勿論、一般的に、徒歩や自転車より、バスや自家用車、あるいは電車の方が通勤時間が長くなることが想像できますが、それはあくまで想像。
集計してみないと、データとしての裏付けは取れません。

「通勤方法」と「所要時間」の関係を見るのなら、「通勤方法」と「所要時間」を掛け合わせた「クロス集計」が必要です。
具体的にはこんな表です。

あるいは、縦と横が逆でも構いません。

今回は下の表を考えます。
というのも、「通勤方法」別の「所要時間」を見る時には、「通勤方法」別の「平均」が分かった方がいいでしょう。
そして、「平均時間」を直感的に理解するには「グラフ」が最適です。
とはいえ、わざわざグラフ化するのほどのでデータではないので、表にグラフも入れてしまいます。
具体的には「データバー」を使います。

「あがり」（完成形）は、こんな感じでしょうか？

実際の集計なら、左側の個別データと右の集計表は別シートにすべきですが、本例ではわかりやすいように同一シートにしています。

さて、これ、どうやればいいでしょうか？

まず、ピボットテーブルを使っての集計を考えてみます。

ピボットテーブルでの集計

まずは、ピボットテーブルでの集計を試してみます。
最初にネタバラシしておきますが、今回の集計では、ピボットテーブルでの集計はお勧めしません。
「じゃあどうすればいいんだ？」という方は次回の記事もご覧ください。

ピボットテーブルの手順
１　左の表を選択（Ctrl+Aでもいい）
２　ツールバーの「挿入」⇒ 左端の「ピボットテーブル」を選択
３　「テーブルまたは範囲の選択」
　＊１～３　は　Alt ⇒  N ⇒ T　でもいい。

４　ダイアロブボックス（小ウィンドウ）が開くので、「OK」

５　新しいシートが開くので、右上の項目から下にドラッグ

まずは、「通勤方法」を「行」の欄に、そして、再度「通勤方法」を「値」の欄にドラッグします。
すると・・・

通勤方法別の件数（人数）集計ができました。

次に、「所要時間」を掛け合わせてみます。

「所要時間」を「列」の欄にドラッグすると・・・

「所要時間」別の件数が横に展開されましたが、「所要時間」欄にあるデータの数だけ項目ができてしまっています。
これは細かすぎます。
しかも、「所要時間」は等間隔ではないので、「分布」を見るのにも不適当です。

「所要時間」は、ある程度の間隔に纏めて集計したいのですが、このままでは難しい。
対応策はあるかと思いますが、とりあえず、ピボットテーブルでの集計は、これで「投了」としておきます。

ピボットテーブルは便利だけど・・・

ピボットテーブルは大変便利な機能ですが、最近はあまり使っていません。
色々な集計ができるがゆえに、表が複雑になりがちで、そのまま集計表として提出することには不向きです。
特に上の例でもありましたが、「項目」名が自分の思い通りに並びません。頭に番号を付けるとか、別列に入れた番号も「かませる」などで対応が可能ですが、面倒＆きれいな表にならない、というデメリットがあります（詳細省略）。
ただし、ドラッグだけで簡単に集計ができるので、私は、データ集計の「当たり」を付ける際には利用していました。

「当たり」とつけるとは、クロス集計の際、どの項目とどの項目をかけ合わせればいいか、を見るということです（詳細はいずれ別記事にするつもりです）。

ピボットとは「くるくる回る」という意味のようです。項目をタテ（行）に配置したり、ヨコ（列）に配置したり、ドラッグで追加したり、削除したりできるので、「当たり」を付けるには最適です。

ピボットテーブルで「当たり」を付けて、きれいな（提出用の）表は、数式で作る、というのが私のパターンでした。
最近は、ピボットで「当たり」を付けなくても、頭の中で大体イメージできるので、いきなり提出用の集計表を作ることも多くなってきました。
２００７以前のエクセルではSUMIFS/COUNTIFSが使えたかったので、クロス集計は、（当時の私の知識では）ピボットに頼らざるえなかった、というのもあります（ピボットの結果を「値のみ貼り付け」して集計表にしていました。これ、結構面倒でした）。

というわけで、「ピボットテーブルが使えない」のではなく、「使い方がある」「使いどころがある」ということです。

今回の集計は、ピボットテーブルでの集計は見送り、関数を使った集計に切り替えます。
ピボットテーブルで集計を完結できると思ってご覧になっていた方は、ごめんなさい。

おまけ

最初にシートが開いた段階で、
「通勤方法」を「行」の欄に、
「所要時間」を「値」の欄にドラッグすると以下の通りになります。

上のようになりますが、
「通期方法」別の「所要時間」の「合計」が出てしまっています。
元のデータが数値なので、エクセルが勝手に合計にしてくれているのでしょう。
「通勤方法」別の「件数」を求めるとしたら、「所要時間」の「合計」が出ているセル（どれでもいい）を選択し、右クリックします（または、メニューキー（目キー））。

「値」欄の項目のところで左クリック（右ではない）でも同じです。
（カーソルを合わせやすい前者を推奨）

開いたメニューから「値フィールドの設定」を選びます。
「値フィールドの設定」が開くので、「個数」を選択します。

これで、「通勤方法」別の人数が出ます。

この後、もう一度、「所要時間」を「値」欄にドラッグすると・・・

「値」欄に「時間（分）」が２つ並びますが、追加した方のものは、「所要時間」の合計になっています。
やはり、元が「数値」なので、「合計」を出すようになっているのでしょう。

さて、この表からだと、「通勤方法」別の「平均時間」が出せそうです。
ですが・・・
C列の「数値の計（所要時間の計）」をB列の「項目の個数（人数）」で割って平均を出そうとすると・・・

一番上のセルでは平均が出ますが、数式が何かとんでもないことになってしまっています。
これを下のセルにコピーすると・・・

みんな同じ答えになっています・・・。
これではだめです。
これは、GETPIVOTDATAという新しい関数が働いているからです。
ピボットテーブルで集計した数値を使って集計しようとしても、ピボットテーブルの「呪縛」が利いている、という印象です。
この数式をいじるのは、ちょっとハードルが高そうです。

なので・・・

上のデータを「値のみ貼り付け」（CTRL＋C　の後、Alt ⇒ E ⇒ S ⇒ V ⇒ OK　の順番押し等）します。
すると、「ピボットテーブル」の「呪縛」から解き放たれ、数式を入れると、各項目の平均が出せます。

とはいえ、当初掲げた下の表のようにはなりません。

従って、前述のとおり、今回の集計では、ピボットテーブルでの集計は「打ち止め」とします。
あくまで「今回の集計では」です。
ピボットテーブルを使えると「集計の幅」がぐっと広がるで、ぜひ、トライしてみてください。
また、集計結果の数値をクリックすると、該当のデータが別シートに表示されれるのも便利な機能です（そのまま残しておくとデータ量が増えてしまいますが。）。

というわけけで中途半端ですみませんが、以上、参考になれば幸いです。
もし何か「発見」があったら、「スキ」「フォロー」「チップ」していただける嬉しいです！