こんにちは、「今より仕事を楽にしよう！」デスクワークラボの吉井良平です。
先日よりエクセル相談を始めて、いろんな方が作ったエクセルファイルを見る機会が増えてきました。
その中で、リストを月別・年別に集計するのに、苦労されている方も多いんだな、と感じています。
月別・年別に集計する方法は、一度覚えると簡単ですし、いろんな集計ができるようになります。ぜひ使いこなして下さいね。
こんなことをしていませんか？
このような毎月の来店客と、売上金額を記したリストがあります。
これを月別に売上集計する場合に、次のように集計されている方もいるのではないでしょうか？
（決して、間違いじゃないですが、もっと楽な方法がある、ということでの紹介です。）
月ごとに、集計行が挿入してある
月が変わる部分で行を挿入して、そこにSUM関数を入れている例です。
月が変わった位置を目視で確認して、行を挿入して、SUM関数を入れる、という3つの作業を繰り返し行わないといけません。
欄外に集計している
欄外に集計しているのも良くみます。
こちらも月が変わった位置を目視し、SUM関数を入力する、という工程を毎回繰り返しています。
決して間違いではありませんが、これらの方法だと、
- データが増えていったときに、計算式を入れるのが面倒
- データが増えていったときに、計算の範囲を間違える可能性がある
- 前の月との比較が少し分かりにくい
という問題があります。
データの数が数十件ぐらいであれば手作業でもなんとかなりますが、それ以上になってくるとかなり大変ですし、集計しようという気にもならないかもしれません。
大量のデータを集計する場合は、エクセルの「ピボットテーブル」という機能を使います。
ピボットテーブルを使う準備
実際にピボットテーブルを使う前に、ちょっと準備があります。
ピボットテーブルを使うためには、日付のデータから、月と年だけを取り出しておく必要があるのです。
日付から月、年を取り出す方法
日付から月、年を取り出すには、関数を使います。
・月を取り出す
=MONTH（日付）
・年を取り出す
=YEAR(日付)
・年と月を一緒にとり出す
＝TEXT(日付,”YYMM”)
英単語の後に、日付のデータを入れるだけなので、簡単です。
後で動画も作っていますが、とりあえず画像でご覧ください。
これで、ピボットテーブルを使って集計する準備が出来ました^^
この状態で、ピボットテーブルを使っていきます。
ピボットテーブルというのは、いろんな項目に分けて集計ができる機能です。複数の項目で集計することを、「クロス集計」とも言います。
エクセルの機能の中でも、5本の指に入る便利な機能なので、いろいろ使ってみましょう。
ピボットテーブルの使い方
① 表の中のどこかを選択した状態で、「挿入」タブの「ピボットテーブル」ボタンを押します。
② ピボットテーブルを作る、ダイアログボックスが出てきます。ここは、ほぼ変更することはないので、そのままOKを押します。
集計範囲は、エクセルが自動的に判断してくれますので、もし変更がある場合は集計範囲を変更してください。
③ピボットテーブルのシートが表示されます。右側の赤枠の部分で集計方法を選択すると、左側に集計結果が表示されます。
右側の設定方法は、最初は戸惑うと思います。失敗してもなんてことはない、やり直せば良いだけのことなので、いろいろ試してみて下さいね。
月別・お客様別で集計してみる
まず、月別、お客様別で、金額の合計をとってみます。
集計したい項目を、表示させたい位置にドラッグしていきます。
すると、月別・お客様別の集計表が出来上がりました。
ちなみに、見た目が全くイケてないので、いろいろ表を調整する場合はピボットテーブルを、普通の値のみにします。
コピーを選択して、値のみ貼り付けしてください。
月別の新規・リピートの契約数を見る
次に、視点を変えて月別に、新規とリピートの契約件数を見てみましょう。
値のところは、合計出来ない値だと、エクセルが自動的に「データの個数」にしてくれているはずです。
これで、新規・リピート別の、月別契約件数が出来ました。
月別に集計する動画を作ってみました
言葉よりも動画で見た方が分かりやすい方は、こちらをご覧下さいね。
まとめ
ピボットテーブルを使って集計すると、
・データが増えても、計算式を入れる必要がない
・集計範囲を指定する必要がないので、計算ミスが起こりにくい
・式を入れなくても、いろんな項目で集計することができる
ので、便利です。
いろんなシステムから csvなどでデータをダウンロードして、集計することってよくあります。
月別に集計するのが面倒くさいと思われている方は、ぜひ参考にしてくださいね。
