メインコンテンツへスキップ
見出し画像

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

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


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

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

    画像

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

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

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

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

    画像

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

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

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

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

    画像
    水色セルには数式が入っています

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

    画像

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

    「あがり」(完成形)は、こんな感じでしょうか?

    画像

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

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

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


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

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


    ピボットテーブルの手順
    1 左の表を選択(Ctrl+Aでもいい)
    2 ツールバーの「挿入」⇒ 左端の「ピボットテーブル」を選択
    3 「テーブルまたは範囲の選択」
     *1~3 は Alt ⇒  N ⇒ T でもいい。

    画像

    4 ダイアロブボックス(小ウィンドウ)が開くので、「OK」

    画像

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

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

    画像

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

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

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

    画像

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

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


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

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

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

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

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

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

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


    おまけ

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

    画像

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

    画像


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

    画像


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

    画像

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

    画像

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

    画像

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

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

    画像

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

    画像

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

    なので・・・

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

    画像
    下の表は上の表を「値のみコピー」してから
    平均を出す数式を入れています

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

    画像

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

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


    あなたへのおすすめ