日別のデータを1週間単位で集計するには、WEEKNUM関数とSUMIF関数を組み合わせます。
WEEKNUM関数で日付が月の第何週目かを求め、求めた値をSUMIF関数の条件に指定して、1週間単位のデータとして集計します。
また、月単位や年単位で集計するには、月単位の場合はMONTH関数で月を、年単位の場合はYEAR関数で年を取り出します。
取り出した月や年の値を、SUMIF関数の条件に指定して集計します。
→記事で紹介した例題をそのまま掲載した[練習用Excelシート]
WEEKNUM関数とは
「MONTH関数やYEAR関数は聞いたことがあるけど、WEEKNUM関数って初めて聞いた」という方も多いはず。
そこで、まずは、WEEKNUM関数を紹介します。
WEEKNUM関数は、シリアル値の日付が1月1日の週から数えて何週目にあたるかを求める関数です。
第1引数に日付(シリアル値)を、第2引数に「週の基準」の数値を指定します。
書式 | 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関数で「日付」に対応する「月」を取り出しました。
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関数で「日付」に対応する「年」を取り出しました。
SUMIF関数で月ごとに集計する
さきほどと同じように、SUMIF関数で年ごとに集計します。
G3セルに2022年の売上を求めるには、=SUMIF($D$3:$D$15,F3,$C$3:$C$15) と入力します。
なお、F3セル~F5セルには、「2022~2024の数値」が入っています(表示形式で〇年と見えるように設定)。
おすすめ記事
- エラー値や非表示の行を無視して集計できるAGGREGATEの使い方[Excelシート付]
- FILTER関数とXLOOKUP関数などの組み合わせはすごい[Excelシート付]
- UNIQUE関数とTRANSPOSE関数を組み合わせて表頭をつくる[Excelシート付]
- SWITCH(スイッチ)関数とIFS関数の違い[Excelシート付]