Introduction
Ver. 2013 2016 2019 365
エクセルで、ピボットテーブルを使用して月単位や年単位で集計する方法です。ピボットテーブルそのものの説明は省き、集計方法だけに絞って紹介します。
ピボットテーブル全体の説明が必要な方は、下のリンク先をご覧ください。
７回に分けて説明しています。
日付でまとめる方法だけ説明するにゃ
ピボットテーブルを作成する
まず、日付を集計するためのピボットテーブルを作成しましょう。
今回は下の表を使用して説明します。
ちなみにこのデータは、「2021/1/1」～「2023/12/31」までの３年間のデータとなっています。
この表の、「商品区分」ごとの「売上」データを集計してみます。
下の赤矢印の見出しが、使用する場所です。
表内のどこかをクリックしている状態で、
［挿入］タブ → ［ピボットテーブル］をクリック。
対象となる表の範囲などの指定画面が表示されますが、エクセルの自動設定に任せてそのまま［OK］を押してしまいましょう。
新しいワークシートが挿入され、
ピボットテーブルを作成する準備ができました。
この場所に集計結果が表示されます。
画面右側には、［ピボットテーブルのフィールド］作業ウィンドウが表示されます。
この場所を使用して集計内容を作成していきます。
まず、日付で集計するために日付が入っている列名の［受注日］を［行］の場所に引っ張ります。
すると、「受注日」が［行］の場所に移動してくれるのですが、同時に「年」「四半期」などが表示され、上の場所にもそれらが勝手に作成されます。
※元データの内容により、「月」が表示される場合もあります。
最近のピボットテーブルは、日付を使用すると同時に、勝手に月や年ごとにグループ化するようになっています。
※2013以前のバージョンでは、自動グループ化は設定されません。
作成された表を見てみると、自動で「年」単位の表示になっています。
さらに、年号の前の「+」を押すと、「四半期」ごとにもまとめられていることが分かります。
これらが、思った通りの結果になっていればそのままで良いのですが、今回は自動のグループ化を解除して進めていきます。
初期状態に戻して、自分でグループ化したい単位を決めた方がスッキリするのではないでしょうか。
解除するには、［ピボットテーブル分析］タブ → ［グループ解除］をクリックします。
これで、下のようにグループ化が解除され、全ての日付が表示されます。
また、右側の作業ウィンドウの［行］の場所も「受注日」だけになってスッキリしました。
では、集計の設定の続きです。
今回は「商品区分」ごとに集計結果を表示したいので、［商品区分］を列に引っ張ります。
さらに、［値］の場所には、集計する数値が入力されている項目を移動します。
今回の場合は「売上」を集計したいので「売上」を引っ張ってきます。
結果は、下のようになります。
「受注日」ごとに「商品区分」の「売上」が集計できました。
月単位や年単位でまとめる
では、出来上がったピボットテーブルを日付でまとめる方法です。
月ごとや年ごとにまとめるには、どこでも良いので日付のセルを選択した状態で、
［ピボットテーブル分析］ → ［フィールドのグループ化］をクリックします。
すると、［グループ化］画面が表示されるので、この場所でまとめる単位を選びましょう。
※同じ画面は、右クリック → ［グループ化］でも表示されます。
この画面で、［月］を選んで［OK］を押すと、
月ごとにグループ化され、下のように集計されます。
複数を選択することもできます。
［四半期］［年］を選択して［OK］を押すと、
下のようにグループ化されます。
週単位でまとめる方法
年単位、四半期単位、月単位でまとめることはできるのですが、週単位でまとめるのは大変です。
先ほどの［グループ化］の画面でも［週］というものは見当たりません。
正確に週単位でまとめようとすると、元の表を工夫するしかないのですが、「7日」ごとにまとめることはできます。
［グループ］の画面で［日］を選び、右下の［日数］を「7」に設定しましょう。
また、状況によっては［開始日］を週の始めの日に設定した方が良いかもしれません。
これで、週単位と似たような感じでまとめることができます。
どうしても正確に週ごとにしたい場合で、別の表を作成したくない時は、月ごとに［開始日］と［最終日］を指定して、他のシートにまとめても良いでしょう。
例えば、下のように集計し、赤枠で囲んだような、必要ない部分を非表示にして他のシートにコピーします。
ちなみに、月の前と最初に挿入されるデータは、［行ラベル］の「▼」をクリックして、一覧からチェックを外すことで非表示になります。
また、「行ラベル」「列ラベル」の文字を非表示にしたい場合は、［ピボットテーブル分析］タブ → ［フィールドの見出し］ボタンをオフにします。
さらに、一番右列の「総計」が必要ない場合は、［デザイン］タブ → ［総計］ → ［列のみ集計を行う］を選びます。
これで、下のようになります。
３か月分を（値として）別シートに貼り付けて、少し体裁を整えたのが下の画像。
ちょっと面倒ですね。
まとめ
ピボットテーブルを使うと集計は楽ですね。週ごとにまとめるのは面倒ですが・・・。
設定する際の操作の注意点としては、必ず日付の場所をクリックしておくことです。
日付以外の場所を選択していると、メニューがグレーアウトして押せなくなっています。
また、下のリンク先でも、ピボットテーブルの日付での集計に関して説明していますので、興味のある方はご覧ください。
ピボットテーブルは慣れるまでが大変だにゃ