Excelで月別・年別に集計する手軽な方法~システムからダウンロードしたデータの集計などに使える

こんにちは、「今より仕事を楽にしよう!」デスクワークラボの吉井良平です。

 

先日よりエクセル相談を始めて、いろんな方が作ったエクセルファイルを見る機会が増えてきました。

その中で、リストを月別・年別に集計するのに、苦労されている方も多いんだな、と感じています。

 

月別・年別に集計する方法は、一度覚えると簡単ですし、いろんな集計ができるようになります。ぜひ使いこなして下さいね。

 

こんなことをしていませんか?

 

このような毎月の来店客と、売上金額を記したリストがあります。

 

sales_list

 

これを月別に売上集計する場合に、次のように集計されている方もいるのではないでしょうか?

(決して、間違いじゃないですよ。もっと楽な方法がある、ということでの紹介です。)

 

月ごとに、集計行が挿入してある

 

行挿入タイプ

月ごとに、行挿入して集計してある

 

欄外に集計している

 

欄外集計

欄外に集計してある

 

繰り返しますが、決して間違いではありません。

ただ、これらの方法だと、

  • データが増えていったときに、計算式を入れるのが面倒
  • データが増えていったときに、計算の範囲を間違える可能性がある
  • 前の月との比較が少し分かりにくい

という問題があります。

これらの問題を解決するために、エクセルの「ピボットテーブル」という機能を使います。

 

ピボットテーブルを使う準備

 

実際にピボットテーブルを使う前に、ちょっと準備があります。

ピボットテーブルを使うためには、日付のデータから、月と年だけを取り出しておく必要があるのです。

 

日付から月、年を取り出す方法

 

日付から月、年を取り出すには、関数を使います。

 

・月を取り出す

=MONTH(日付)

・年を取り出す

=YEAR(日付)

 

英単語の後に、日付のデータを入れるだけなので簡単ですよね?

後で動画も作っていますが、とりあえず画像でご覧ください。

 

MONTH関数使用例

MONTH関数使用例

 

これで、ピボットテーブルを使って集計する準備が出来ました^^

 

ピボットテーブルを使って集計しよう

 

ピボットテーブルというのは、いろんな項目に分けて集計ができる機能です。複数の項目で集計することを、「クロス集計」とも言います。

エクセルの機能の中でも、5本の指に入る便利な機能なので、いろいろ使ってみて下さいね。

 

ピボットテーブルの使い方

 

① 表の中のどこかを選択した状態で、「挿入」タブの「ピボットテーブル」ボタンを押します。

 

ピボットテーブルの使い方1

ピボットテーブルの使い方(その1)

 

② ピボットテーブルを作る、ダイアログボックスが出てきます。ここは、ほぼ変更することはないので、そのままOKを押します。

集計範囲は、エクセルが自動的に判断してくれますので、もし変更がある場合は集計範囲を変更してください。

ピボットテーブルの作り方2

ほぼ、そのままOK

 

③ピボットテーブルのシートが表示されます。右側の赤枠の部分で集計方法を選択すると、左側に集計結果が表示されます。

ピボットテーブルの使い方3

右側で項目を決めて、左側に表示される

 

右側の設定方法は、最初は戸惑うと思います。失敗してもなんてことはない、やり直せば良いだけのことなので、いろいろ試してみて下さいね。

 

月別・お客様別で集計してみる

 

まず、月別、お客様別で、金額の合計をとってみます。

集計したい項目を、表示させたい位置にドラッグしていきます。

ピボットテーブルの作り方4

データ項目の設定方法

 

すると、月別・お客様別の集計表が出来上がりました。

ピボットテーブルの使い方5

ピボットテーブルが出来ました

 

ちなみに、見た目が全くイケてないので、いろいろ表を調整する場合はピボットテーブルを、普通の値のみにします。

ピボットテーブルの調整1

シート全体を選択

コピーを選択して、値のみ貼り付けしてください。

ピボットテーブルの調整2

値のみ貼り付け

 

月別の新規・リピートの契約数を見る

 

次に、視点を変えて月別に、新規とリピートの契約件数を見てみましょう。

ピボットテーブル2

月別の新規・リピート契約数

 

値のところは、合計出来ない値だと、エクセルが自動的に「データの個数」にしてくれているはずです。

これで、新規・リピート別の、月別契約件数が出来ました。

ピボットテーブル3

新規とリピートに分けた場合

 

月別に集計する動画を作ってみました

 

言葉よりも動画で見た方が分かりやすい方は、こちらをご覧下さいね。

 

まとめ

 

ピボットテーブルを使って集計すると、

・データが増えても、計算式を入れる必要がない

・集計範囲を指定する必要がないので、計算ミスが起こりにくい

・式を入れなくても、いろんな項目で集計することができる

ので、便利です。

いろんなシステムから csvなどでデータをダウンロードして、集計することってよくあります。

月別に集計するのが面倒くさいと思われている方は、ぜひ参考にしてくださいね。

 

2 件のコメント

  • エクセルの日付からYear+Monthだけを引き抜き、ピボットで集計を取るやり方が大変参考になりました。
    これで一つ手間が省けました。
    来月からのレポート作成はこれを使います。
    ありがとうございます。

  • コメントを残す

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

    ABOUTこの記事をかいた人

    昭和48年広島県生まれ。現在も広島に住んでいます。 趣味は音楽鑑賞(邦楽、洋楽問わず、クラシック、童謡も)、マンガです。エクセルを効率的に使う方法を紹介して、あなたのお役に立ちたいと思っています。