Pivotの標準機能で累計をグラフにする


あなたはイベントの幹事です。数百人を集客する予定です。日々、申し込まれる出席申込の人数をグラフ化したい、いや日別の申込人数だけじゃなくて、その日までの累計人数をグラフで見たい。ということってありますよね。いや、ありました、という話です。

元データはこんな感じ。

日付 出席申込人数
4/13 3人
4/14 2人
4/15 2人
4/16 1人
4/17 2人
4/18 11人
4/19 4人

作りたいグラフはこんな感じ。

さて、どうしましょう。

Excelで「累計」列を追加して、関数を組んで以下のような表を先に作っておき、上のグラフを作る手もあります。

日付 出席申込人数 累計人数
4/13 3人 3人
4/14 2人 5人
4/15 2人 7人
4/16 1人 8人
4/17 2人 10人
4/18 11人 21人
4/19 4人 25人

ですが、関数を作らずともPivotの標準機能で実現できます。

まずは素データの表から、Pivotを作ります。

「出席申込数」のフィールドを「値」の領域に2つ作ります。複数個作成するには「出席申込数」を「値」の領域にドラッグアンドドロップします。

Pivotには「合計/出席申込数」と「合計/出席申込数2」が作成されています。この「合計/出席申込数2」に累計が表示されるように設定します。

右下の「出席申込者数2」のプルダウンから「値フィールドの設定」を選択すると「値フィールドの設定」画面が表示されます。

「計算の種類」タブを選択し、計算の種類として「累計」を選択しOKを押します。

これで「合計/出席申込数2」がその日までの累計になりました。

列見出しを分かり易い名称に変更します。(直接B3セルとC3セルを打ちかえればOK)

リボンの「挿入」タブからピボットグラフを選択して挿入します。

グラフの種類は以下のように設定します。

Pivotグラフが作成されました。

リボンの「デザイン」タブから良さげなデザインを選んで完成です。

イベントが毎年開催される場合、各年度の日別申込データがあれば、素データに「年度」列を追加して累計グラフの比較もできます。(というか、やりました、という話です。)

 

※日別の縦棒は省略してます。

 

累計と面グラフを使うと、インシデントの発生・完了・残件数を視覚的に表現できますね。色々便利です。

 

4件のコメント

  1. こんにちは。
    年間の累計データを作成したいのですが、年度で累計計算すると4-12月で累計されますが、翌年の1-3月が新たな累計数字となってしまいました。年を超えて累計計算する方法はありますでしょうか。

    1. Pivotで日付がグループ化されていると年や四半期で累計がブレイクされてしまいます。
      自動でグループ化される設定を解除すると年を超えて累計されました。
      自動グループ化の解除は以下のサイトを参考にしました。
      https://www.saka-en.com/office/excel-pivot-auto-date-group/
      ※Excelのバージョンによって、オプションスイッチの場所が微妙に異なりますので、頑張って探してみてください。

  2. こんにちは。
    「ピボットグラフ」で検索してたら行き着きました。デジタルツール好きの会計士です。自分の興味の範囲と重なる投稿が多かったので、ご挨拶させてください。(突然で恐縮です)

    業務でtableauを使っていますが、ピボットでも大体のことができてしまうんじゃないか?タブローをオススメしたいんだけど、ピボットでもそれできそうじゃない?とか言われ「ぐぬぬ」ってなってたりしていて、改めてエクセルも勉強中です。

    Dynamics(ERP)関連の投稿も楽しみにしています。
    自分の将来の飯のタネになるかもと、こちらも趣味がてらですが、実家の小さい家業の会計をfreeeへの移行を試しています。(Dynamicsも試して見たいと思いました。)

    これからの投稿も楽しみにしております。

    1. コメントありがとうございます!
      Pivotは結構色んな事ができますね。個人的にはExcelで一番好きな機能です。表形式のデータを見た瞬間、手が勝手に動いて Ctrl + Shift + * で範囲指定して、Alt -> d -> p -> p -> Enter の順にキーを押してPivotを作っていますw
      microsoft系エンジニアとしてはtabeleau+freeeではなくPower BI + Dynamics365 Business Central をお勧めいたします(笑)
      ERPシステムと会計士さんは良い組み合わせです。実際、飯のタネにしている方もいらっしゃいます。先の投稿でも会計士向けの機能を紹介しました。次回はDynamicsのフリートライアル環境作成の記事を書く予定ですので、ぜひDynamicsもお試しください。

返信を残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です