Excelで週ごと・月ごとに集計する方法

ExcelのWEEKNUM関数で日付が月の何週目かを計算し、週単位での集計をする方法をご紹介します。ピボットテーブルでデータを月単位で集計する方法や、関数を使って年月ごとのデータを集計する方法も解説しているので、ぜひ参考にしてみてください。

データを月ごとにまとめる方法

データを月ごとにまとめる方法を 2 つご紹介します。ピボットテーブルを使うと月ごとのデータや、年度別の月ごとのデータを数式を使わずに抽出可能です。

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

まず表などの範囲を選択し、挿入タブの[ピボットテーブル]を開きます。

テーブル範囲を確認し、新規ワークシートにチェックを入れて[OK]をクリックしてください。

次に、レポートに追加するフィールドをドラッグして各ボックスに移動します。

行ボックスに[日付]を、値ボックスに[集計する項目]を追加すると、月ごとの集計が作成されました。[＋]を開くと、日付ごとのデータも確認可能です。

複数の年度が存在する場合も、同じようにピボットテーブルを作成します。

四半期のレポートからチェックを外し、年度の[+]を開くと各年度ごとの月別データを抽出できます。必要に応じて、四半期のレポートをそのまま残してもOKです。

関数で月ごとに集計する方法

まずMONTH関数で、指定した日付から月のみを抽出します。

例えば MONTH(A2) なら 2023/4/1 の月のみを抽出するので、返り値は 4 です。

次にSUMIF関数を使って、それぞれの月ごとにデータを集計します。

例えば SUMIF($C$2:$C$7,E2,$B$2:$B$7) なら、MONTH関数で抽出した月数から 4 を検索します。条件と一致したセルの集計を合計範囲から返すので、返り値は 150 です。

このとき検索範囲と合計範囲は $ 記号を付けて固定する点に注意しましょう。

関数で年月ごとに集計する関数

複数の年度が存在する場合、まずTEXT関数で日付から年度と月に変換します。

例えば TEXT(A2,”yyyy/mm”) なら 2023/4/1 を年度と月を変換するので、返り値は　2023/04 です。表示形式は、二重引用符(“”)で囲う点に注意しましょう。

次にUNIQUE関数を使って、TEXT関数で取得した年度と月から一意の組み合わせを抽出します。

最後に一意の年度と月をSUMIF関数の検索条件値に指定すれば、年月ごとの集計が可能です。UNIQUE関数が使えない場合は代用方法を使用するか、検索条件を手入力してください。

データを週ごとにまとめる方法

まずWEEKNUM関数で、日付の週数を取得します。

WEEKNUM関数は日付が1月1日から数えて何週目かを、指定した週の基準で数えます。週の基準は何曜日を週の始まりとするかで、月曜日を週の始まりとして数える場合は 2 です。

例えば WEEKNUM(2023/4/1,2) なら、1月1日から数えた週数 14 を返します。

次にEOMONTH関数で月初の日付を取得し、WEEKNUM関数でその週数を数えます。EOMONTH関数は開始日から、指定した月数だけ前または後の月の最終日を返します。

例えば EOMONTH(A2,-1)+1 なら 3月末+1 なので、返り値は 4月1日 です。

始めに求めた週数から月初の週数を減算し、1 を加算すればその月の週数が求められます。

週数を求める数式：WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1) +1,2)+1

最後にSUMIF関数を使って、それぞれの週ごとにデータを集計します。

例えば SUMIF($C$2:$C$11,E2,$B$2:$B$11) なら、WEEKNUM関数で求めた週数から 1 を検索します。条件と一致したセルの集計を合計範囲から返すので、返り値は 150 です。