Excelピボットテーブルで、日々のデータを、年月別・期(事業年度)別に集計する方法

  • URLをコピーしました!

ピボットテーブルで、日々のデータを月別に集計する方法があります。
数パターンを知っておくと便利です。
あわせて、期(事業年度)別に集計する方法もご紹介します。
ピボットテーブル 日付集計

ピボットテーブルで、日々のデータを集計

ピボットテーブルを使うと、こういった日々のデータを
ピボットテーブル 日付集計00016

かんたんに集計できます。
ピボットテーブル 日付集計00014

支店別に集計したり、

スクリーンショット 2015 05 19 8 22 12

顧客別に集計したりすることも可能です。
スクリーンショット 2015 05 19 8 22 23

ピボットテーブルについては、こちらのカテゴリで記事をまとめてあります。
https://www.ex-it-blog.com/archives/category/excel/excel-pivot/

ピボットテーブルで月別に集計する方法

この日々のデータを月別に集計するには、次の2つの方法があります。

ピボットテーブルをグループ化

1つは、ピボットテーブルの項目をグループ化する方法です。
日別のデータを年・月のグループでまとめます。

ピボットテーブルの日付のいずれかを右クリックし、[グループ化]を選び、
ピボットテーブル 日付集計00013

グループ化したい(集計したい)ものをクリックしましょう。
今回は、年、月でグループ化するとして、年と月を選択します。
ピボットテーブル 日付集計00012

このように、年月別に集計されます。
ピボットテーブル 日付集計00011

このグループ化は、次のようなエラーが出ることもあるので注意しましょう。
原因は、日付が入っている列に、文字が入っていたり、数式がエラーになっていることです。
ピボットテーブル 日付集計00010

YEAR関数・MONTH関数で、年と月の項目を追加

もう1つの方法は、関数を使って、項目をデータに追加します。
使うのは、YEARとMONTHという関数です。
セルA2に日付が入っているので、=YEAR(A2)で、日付から年を取り出し、
ピボットテーブル 日付集計00008

=Month(A2)で日付から月を取り出せば、
ピボットテーブル 日付集計00007

年と月のデータを追加できます。
これをピボットテーブルで集計すれば、年月別集計が可能です。
グループ化は、エラーになることもありますので、こちらの方が安定しています。
ただ、数式のコピーもれに注意しましょう。
事例のように、テーブル機能を使えば、その心配はありません。
ピボットテーブル 日付集計00006

VLOOKUP関数を活用して、期別に集計する方法

さらに、期(事業年度)別にピボットテーブルで集計する方法も紹介します。
データに期の項目を追加すればいいのですが、手作業では、めんどくさく手間もかかるため、関数を使いましょう。

右側(又は別シート)に、期の区切りとなる日付と期を入力します。
この場合だと、3月決算で、
・2013年4月1日から2014年3月31日 3期
・2014年4月1日から2015年3月31日 4期
・2015年4月1日から2016年3月31日 5期
です。

入力は、4/1(期首)を基準に入力してください。
この日付と期の組み合わせをVLOOKUP関数で連動します。

=VLOOKUP(A2,I:J,2,TRUE)
※TRUEは省略も可能ですが、理解のためにも入れておいた方がいいでしょう。「1」と入れることも可能です。

スクリーンショット 2015 05 19 8 51 56

A2(日付)を、I列から探してIからJ列の2番目を表示するしくみです。
通常は、最後の項目として、「FALSE(完全一致)」を指定するのですが、こういった場合は「TRUE(近似一致)」を指定します。
たとえば、[2014/1/1」をI列から検索し、「2014/4/1」だと、こえてしまうので、その上のセルの「2013/4/1」と近似一致し、その隣のセルの「3」を表示します。

期別に集計するとこんな感じです。
ピボットテーブル 日付集計00004

年と月を組みあわせることもできます。

ピボットテーブル 日付集計00003

月を縦に、期を横にして、こんな表もおすすめです。
(サンプルでは3期、2013年のデータを入れていません)
スクリーンショット 2015 05 19 8 53 21

サンプルはこちらからダウンロードできますので、ピボットテーブルを試してみてください。
EX-ITサンプル ピボット日付集計





【編集後記】
昨日は午後に、WordPressブログ入門セミナーを開催。
参加者のみなさんの、今日からの更新が楽しみです。
特に今日からスタートされるSさん、楽しみにしています!
(無茶振りしてすいません。。)

【昨日の1日1新】
※詳細は→「1日1新」

フェリー会社へ電話
月曜日の午後にセミナー開催

  • URLをコピーしました!