時間毎や1日毎のデータを月毎に変換にしたい時ってありますよね?
例えばこんな時です。
・長期的な時間軸でデータを確認したいが、時間毎のデータでは粒度が細かすぎる場合
・1日毎のデータを集計して、月の合計値を出したい場合
今回は、Excelを用いて日毎のデータを月毎のデータに加工する方法を2つ紹介します。
1つ目は、Excelの関数を用いて力技で集計する方法
2つ目は、ピボットテーブルを用いてスマートに集計する方法
です。
どちらも知っておけば便利ですので、参考にしていただけると嬉しいです。
分毎や時間毎のデータを日毎のデータに加工する方法はこちらで紹介しております。
①Excelの関数を用いて力技で集計する方法
まず、このような1時間毎の出荷量のデータを入手したとします。
A列に列を挿入し、A2セルに『=YEAR(B2)&”/”&MONTH(B2)』と関数を追加します。
これでB列の日時を年月に変換することが可能です。
下のスクショのG列のように年月を手入力します。
そして、『=SUMIF(A:A,G2,C:C)』のようにSUMIF関数を使用して、A列と手入力した年月が等しい行だけ出荷量を足し算します。
これで、月毎の合計値が計算ができます。
同様に、『=AVERAGEIF(A:A,G2,C:C)』のようにAVERAGEIF関数を使用して、月毎の平均値を算出します。
いかがだったでしょうか?
慣れてしまえば大した作業ではありませんが、それなりに面倒です。
この力技も便利なときはあるのですが、もっとスマートに集計する方法を紹介します。
「挿入」から「ピボットテーブル」を選択します。
以下のようなウィンドウが表示されます。
選択範囲があっていれば、OKをクリックします。
以下のような新しいシートが作成されますので、「日時」と「出荷量 kg」のフィールドにチェックを入れます。
すると、日毎の出荷量が集計されました。1時間毎のデータをすぐに日毎に集計されましたね。
ただ、欲しかったのは月毎の集計結果ですので、もう少し作業します。
図のように、日にちのセルをクリックし、
「ピボットテーブル分析」から「フィールドのグループ化」をクリックします。
*エクセルのバージョンによって表示が微妙に異なる可能性があります。
下のようなウィンドウが表示されますので、必要な単位を選択してOKを押します。
今回は「月」を選択に入れます。
すると、フィールドに選択した単位が追加されるので「月」を選択します。
以下のように、月毎の集計結果が表示されます。
少し手間でしたが、関数を使わない分、スマートに集計できます。
ちなみに月毎の平均値が必要な場合は、図のように、「合計 /出荷量 kg」を右クリックし、「値フィールドの設定」をクリックします。
計算の種類を「平均」に変更します。
これで平均値が表示されます。
いかがだったでしょうか?
皆様のお役に立ったなら嬉しいです!