週単位で集計する:エクセル練習問題

問題1 topへ

• 問題1:B列に年月日が入力されています。C列に何週かを計算します。
  週の計算は日曜日を基準として計算します。
  12月の第1週〜第5週の合計を計算をしなさい。 問題1の解答
  
  B C D E F G
  2 日付 週 売上 合計
  3 2025年12月1日 17,044 第1週
  4 2025年12月2日 13,566 第2週
  5 2025年12月3日 17,623 第3週
  6 2025年12月4日 14,569 第4週
  7 2025年12月5日 12,888 第5週
  8 2025年12月6日 10,451
  9 2025年12月7日 13,824
  10 2025年12月8日 12,824
  11 2025年12月9日 13,493
  12 2025年12月10日 18,183
  13 2025年12月11日 13,178
  14 2025年12月12日 13,014
  15 2025年12月13日 13,294
  16 2025年12月14日 11,008
  17 2025年12月15日 14,552
  18 2025年12月16日 17,729
  19 2025年12月17日 10,003
  20 2025年12月18日 13,384
  21 2025年12月19日 12,155
  22 2025年12月20日 12,689
  23 2025年12月21日 16,609
  24 2025年12月22日 15,397
  25 2025年12月23日 17,170
  26 2025年12月24日 11,595
  27 2025年12月25日 11,601
  28 2025年12月26日 14,872
  29 2025年12月27日 14,872
  30 2025年12月28日 19,489
  31 2025年12月29日 12,957
  32 2025年12月30日 16,337
  33 2025年12月31日 12,338

問題2 topへ

• 【問題2】3か月のデータがあります。
  11月の第1週〜第5週の合計を計算をしなさい。 問題2の解答
  
  B C D E F G
  2 日付 週 売上 合計
  3 2025年10月25日 12,750 第1週
  4 2025年10月27日 11,003 第2週
  5 2025年10月29日 16,847 第3週
  6 2025年10月31日 11,259 第4週
  7 2025年11月1日 18,183 第5週
  8 2025年11月3日 13,014
  9 2025年11月5日 11,595
  10 2025年11月7日 14,569
  11 2025年11月9日 17,623
  12 2025年11月11日 14,552
  13 2025年11月13日 15,397
  14 2025年11月15日 17,170
  15 2025年11月17日 10,003
  16 2025年11月19日 13,824
  17 2025年11月21日 12,824
  18 2025年11月23日 16,609
  19 2025年11月25日 13,294
  20 2025年11月27日 12,689
  21 2025年11月29日 13,566
  22 2025年12月1日 17,729
  23 2025年12月3日 14,872
  24 2025年12月5日 17,044
  25 2025年12月7日 13,178

はじめに topへ

• ISOWEEKNUM関数はExcel2013で追加された関数です。
  ISO 8601で「最初の木曜日を含む週が、その年の第1週である。」と規定されたものです。
  よって、1月1日が金〜日曜日である時は前年の週として扱うことになります。
  また、JIS X 0301も同様の規定のようです。
  Excel2010で追加された週基準の「21」に相当するものです。
• WEEKNUM関数はExcel2007以前でもありましたが、Excel2010では種類に指定できる数が増えています。
  =WEEKNUM(シリアル値,[週の基準])]) の「週の基準」に従来は1か2を指定可能でしたが、11〜21の指定が可能になっています。
  • システム 1:1月1日を含む週がその年の最初の週であり、第1週です。
  • システム 2:その年の最初の木曜日を含む週がその年の最初の週であり、第1週です。
    このシステムは、ヨーロッパ式週番号システムと呼ばれる、ISO 8601 に規定されている方式です。
  種類 週の始まり システム
  1 または省略 日曜日 1
  2 月曜日 1
  11 月曜日 1
  12 火曜日 1
  13 水曜日 1
  14 木曜日 1
  15 金曜日 1
  16 土曜日 1
  17 日曜日 1
  21 月曜日 2

問題1の解答例 topへ

ISOWEEKNUM関数を使って計算する

• ISOWEEKNUM関数はSpillが利用できるようです。
  そのためGROUPBY関数で計算でき、簡単に計算できます。
  ただ、週の計算方法がISO8601に準じていますのでご注意ください。

1. ここではデータを2025年に変えたもので説明します。(記 2025年9月21日)
   このC列は計算には使用しないので、不要なのですが、週の説明のために表示しています。
   C3セルに =ISOWEEKNUM(B3:B33) と入力します。
   
2. GROUPBY関数はExcel for Microsoft365で使用できます。
   =GROUPBY(ISOWEEKNUM(B3:B33),D3:D33,SUM) とすると簡単に集計ができました。
   ただ、週1が計算されるのと、週番号が49〜52となるのが気になります。
   
3. DROP関数はExcel for Microsoft365、Excel2024で使用できます。
   DROP関数で1行目と、1列目を削除しました。
   また、GROUPBY関数の合計を表示しない設定にしました。
   G3セルに =DROP(GROUPBY(ISOWEEKNUM(B3:B33),D3:D33,SUM,,0),1,1) としました。
   

WEEKNUM関数で作業列を利用して計算する

• 日付が年初から何週目かを計算するにはWEEKNUM関数を使います。
  構文:=WEEKNUM(シリアル値,週の基準)
  詳細な使い方は 何週目かを調べる関数(WEEKNUM関数)をご覧ください。
• 作業列に第何週かを計算します。
  週の初めは日曜日とするので、=WEEKNUM(B3,1) で求めることができます。
  引数の「1」は省略できますので、=WEEKNUM(B3) とします。
  2025年12月の第1週の始まりはWEEKNUM(DATE(2025,12,1)で求めることができます。
  よって2025年12月の各日にちの週は
  =WEEKNUM(B3)-WEEKNUM(DATE(2025,12,1))+1 で求めました。
  C3セルに
  =WEEKNUM(B3)-WEEKNUM(DATE(2025,12,1))+1
  と入力して、下方向へコピーします。
  
• SUMIF関数で条件付きの合計を計算することができます。
  構文:=SUMIF(検索範囲,検索条件,合計範囲)
  
  Excel for Microsoft365、Excel2021以降でSpillが利用できるバージョンでは
  G3セルに =SUMIF(C3:C36,ROW(A1:A5),D3:D36) と入力します。
  F3セル以降は文字列で「第1週」と入力されているため、数式で容易に参照して利用できません。
  検索条件に 1,2,3,4,5 となるように、ここでは ROW(A1:A5) としました。
  
  Excel2019以前のバージョンでは
  =SUMIF($C3ドル:$C36,ドルROW(A1),$D3ドル:$D36ドル)
  と入力して、下方向へオートフィルなどで数式をコピーします。
  
  
• Excel for Microsoft365、Excel2021ではSpillやSEQUENCE関数が使えます。
  G3セルに =SUMIF(C3:C36,SEQUENCE(5),D3:D36) と入力します。
  検索条件に 1,2,3,4,5 となるように、ここでは SEQUENCE(5) としました。
  
• SUMIFS関数を使うこともできます。
  構文:SUMIFS(合計対象範囲,検索条件範囲1,検索条件1,[検索条件範囲2],[検索条件2]...)
  詳細は 複数の条件で合計する関数(SUMIFS関数) をご覧ください。
  
  =SUMIFS(D3:D36,C3:C36,SEQUENCE(5)) とすることができます。
  

ピボットテーブルを使って曜日ごとに集計する

1. データリスト内のセルを1つ選択します。
   [挿入]タブの[ピボットテーブル]を実行します。
   
2. ピボットテーブルの作成ダイアログボックスが表示されます。
   テーブル/範囲に B2:D36 が設定されているのを確認します。
   ピボットテーブルを配置する場所を選択してください。で「既存のワークシート」にチェックを入れて、セルを指定します。
   
3. フィールドを下図のように、行に「日付」、Σ値に「売上」をドラッグして設定します。
   
4. 週ごとにグループ化したいので、ピボットテーブルの年月日を右クリックして、[グループ化]をクリックします。
   
5. 単位で「日」を選択します。「月」が選択されていたら、「月」をクリックしていったん解除してから「日」を選択します。
   開始日に12/1の週の日曜日を入力します。ここでは 2025年11月30日 となります。
   最終日に「2025年12月31日」と入力します。
   日数に「7」と入力します。
   [OK]ボタンをクリックします。
   
6. ピボットテーブルで週ごとの集計ができました。