Excel

いまさら聞けない!【週・月・年単位】で集計する方法[Excelシート付]

日別のデータを1週間単位で集計するには、WEEKNUM関数SUMIF関数を組み合わせます。
WEEKNUM関数で日付が月の第何週目かを求め、求めた値をSUMIF関数の条件に指定して、1週間単位のデータとして集計します。

 

また、月単位や年単位で集計するには、月単位の場合はMONTH関数で月を、年単位の場合はYEAR関数で年を取り出します。
取り出した月や年の値を、SUMIF関数の条件に指定して集計します。

 

→記事で紹介した例題をそのまま掲載した[練習用Excelシート

WEEKNUM関数とは

「MONTH関数やYEAR関数は聞いたことがあるけど、WEEKNUM関数って初めて聞いた」という方も多いはず。
そこで、まずは、WEEKNUM関数を紹介します。

 

WEEKNUM関数は、シリアル値の日付が1月1日の週から数えて何週目にあたるかを求める関数です。
第1引数に日付(シリアル値)を、第2引数に「週の基準」の数値を指定します。

書式 WEEKNUM( シリアル値 , [週の基準] )

WEEKNUM関数について

クリックで拡大画像が開きます




シリアル値とは

Excelの日付は、シリアル値と呼ばれる1990年1月1日を「1」とした値で管理されています。
1990年1月2日が「2」、2022年4月1日が「44652」という具合です(1904年から計算する設定にもできます)。

週の基準

WEEKNUM関数は、第2引数に指定する「週の基準」は、週の開始日を何曜日として計算するかを指定するものです。

週の始まり 週の基準
日曜日 1または省略
月曜日 2
月曜日 11
火曜日 12
水曜日 13
木曜日 14
金曜日 15
土曜日 16
日曜日 17
月曜日 21(ISO基準)

ISO(国際標準化機構)では、最初の木曜日を含む週がその年の第1週であると規定しています。
例えば1月1日が金曜日の場合、その週は前年の第52週、あるいは第53週とみなされます。
また、1月1日が水曜日の場合、前年の12月31日も第1週とみなされます。
WEEKNUM関数の第2引数「週の基準」に[21]を指定すると、この規定に従った週数を求めることができます。


 

その日が月の第何週目かを求める

WEEKNUM関数は、日付がその年の第何週目にあたるのかを求めることがきます。

したがって、月の何週目かを求めるには、【日付の週数-月初日の週数+1】とします。

 

以下のように、B3セルに2022/4/1の日付が入っている場合には、=WEEKNUM(B3,1)-WEEKNUM("2022/4/1",1)+1 で求めることができます。

WEEKNUM(B3,1) の部分で、その年の週数を求め、WEEKNUM("2022/4/1",1) で4月1日の週数を求めています。

月の第何週目かを求める

 

WEEKNUM("2022/4/1",1) の部分は、4月の何週目にあたるかだけでなく、年度の初めから何週目にあたるかを求める際にも使えます。

 

SUMIF関数で集計する

さて次は、SUMIF関数で週ごとに集計します

 

SUMIF関数とは

SUMIF関数は、[範囲]内で[検索条件]に一致するデータを検索し、[合計範囲]の対応するの数値を合計します。
[合計範囲]を省略した場合には、[範囲]内で対応する数値が合計されます。

書式 SUMIF( 範囲 , 検索条件 , [合計範囲] )

 

以下では、C3セルからC32セルに「日ごとの売上」が、D3セルからD32セルに「月の週数」が、F3セル~F7セルには「1~5の数値」が入っています(表示形式で〇週目と見えるように設定)。

このとき、G3セルに4月第1週目の売上を求めるには、=SUMIF($D$3:$D$32,F3,$C$3:$C$32) と入力します。

週ごとに集計する

 

月単位で集計する

月単位で集計するには、MONTH関数で月を取り出します。
取り出した月の値を、SUMIF関数の条件に指定して集計するという手順になります。

 

MONTH関数とは

MONTH関数は、[シリアル値]に対応する月を1~12の範囲の整数で取り出します。

書式 MONTH( シリアル値 )

 

MONTH関数で月を取り出す

以下では、B3セルからB15セルに「日付」が、C3セルからC15セルに「日ごとの売上」が入っています。
そこで、D3セルからD15セルにMONTH関数で「日付」に対応する「月」を取り出しました。

MONTH関数で月を取り出す

 

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

次は、SUMIF関数で月ごとに集計します。
G3セルに4月の売上を求めるには、=SUMIF($D$3:$D$15,F3,$C$3:$C$15) と入力します。

なお、F3セル~F5セルには、「4~6の数値」が入っています(表示形式で〇月と見えるように設定)。

月ごとに集計する

 

年単位で集計する

年単位で集計するには、YEAR関数で年を取り出します。
取り出した年の値を、SUMIF関数の条件に指定して集計するという手順になります。

 

YEAR関数とは

YEAR関数は、[シリアル値]に対応する年を1900~9999の範囲の整数で取り出します。

書式 YEAR( シリアル値 )

 

YEAR関数で年を取り出す

以下では、B3セルからB15セルに「日付」が、C3セルからC15セルに「日ごとの売上」が入っています。
そこで、D3セルからD15セルにYEAR関数で「日付」に対応する「年」を取り出しました。

YEAR関数で年を取り出す

 

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

さきほどと同じように、SUMIF関数で年ごとに集計します。
G3セルに2022年の売上を求めるには、=SUMIF($D$3:$D$15,F3,$C$3:$C$15) と入力します。
なお、F3セル~F5セルには、「2022~2024の数値」が入っています(表示形式で〇年と見えるように設定)。

年ごとに集計する

 

おすすめ記事

 

参考文献

-Excel

© 2022 まなびっと Powered by AFFINGER5