Excel(エクセル)で、週ごとに集計する方法|ピボットテーブルで正確に週単位で集計するやり方

スポンサーリンク
スポンサーリンク
スポンサーリンク

Introduction

Ver. 2013 2016 2019 365

エクセルで、ピボットテーブルを使用して週単位で集計する方法です。

月や年単位では簡単に集計できるのですが、正確に週単位にするには元の表に工夫が必要です。

ピボットテーブルの集計画面には「週」が存在しないのですね。

ここでは、下のように月ごとに週をまとえる方法を紹介します。

月ごとにこだわらず「7日」ごとに集計したい場合は、

下のリンク先の後半で説明しています。好きな日にちから7日ごとの集計であれば簡単に設定できますよ。

Excel(エクセル)で、週ごとに集計する方法|ピボットテーブルで正確に週単位で集計するやり方
エクセルで、ピボットテーブルを使用して週単位で集計する方法です。月や年単位は簡単なのですが、正確に週単位にするには元の表に工夫が必要です。

 

 

第〇週はちょっと面倒だにゃ

スポンサーリンク
スポンサーリンク

週でまとめるために、表を加工する

月ごとに週でまとめるには元の表に、その日が何週目かを表示しておく必要があります。

そのやり方を説明した後に、ピボットテーブルでの集計方法へ移ります。

また、何週目かを表示する方法ですが、スタートする週から数えて〇週と連番のように表示する方法と、

月ごとに分かり易く第〇週と表示する方法に分けて説明します。

表示は気にせず、最初の週から連番にしてまとめる方法

月ごとに「第〇週」と表示する必要が無ければ、こちらの方法が簡単です。

今回は下の表を使用して説明します。

ちなみにこのデータは、「2021/1/1」~「2023/12/31」までの3年間のデータとなっています。

まず、右側に第何週かを表示する列を挿入しましょう。

ここに、関数を使用して「受注日」の日付が何週目かを表示していきます。

使用する関数はWEEKNUM関数。

では、やってみましょう。

関数を入れるセルを選択して[関数の挿入]ボタンをクリック。

[関数の挿入]画面で、[関数の分類]を[日付/時刻]か[すべて表示]に変更してから、WEEKNUM関数を選んで[OK]。

 


WEEKNUM関数・・・指定した日付が、その年の1月1日から第何週にあたるかを返す

WEEKNUM( シリアル値 , 週の基準[省略可])

  • シリアル値・・・日付を指定
  • 週の基準・・・週の始まりを何曜日にするかを数字で指定[省略すると日曜日から]
週の基準週の始まり
1日曜日
2月曜日
11月曜日
12火曜日
13水曜日
14木曜日
15金曜日
16土曜日
17日曜日

 


 

今回の場合は下のように設定します。

[シリアル値]には対象となる日付の「B2」セルを選択して、週の基準は月曜日から始めたいので「2」と入力します。会社の場合は月曜スタートで考えた方が良い場合が多いのでは。

[OK]をクリックすると、対象の日付が1月1日なので「1」と表示されます。

そのままオートフィル機能を使用してコピーすると、

下のようになります。

週が変わると数字が増えていくのが分かります。

この関数はその年の1月1日を「1」として、週が変わるごとに数値が1づつ増えていきます。つまり、月が変わっても「1」に戻ることはなく、数値は増え続けます。

例えば、2月~3月あたりへ移動すると、週の数値は「9」「10」となっています。1月1日の集から「9週目」「10週目」ということですね。

この状態でも、週ごとに集計することは可能です。

しかし、集計した時の項目名が「第〇週」ではなく、そのままの連番になります。

それで良い場合は、少し飛ばして、後半の「ピボットテーブルで集計する」をご覧ください。

せっかくなので、表示名も変更したい場合は、このまま続きをどうぞ。

表示を第〇週と表示する方法

基本的な考え方は、すぐ前で説明したのと同じです。WEEKNUM関数で週ごとに数字で分けて、さらに月が変わるごとに「1」からスタートするようにします。
※前の説明からの続きになるので、前を読んでいない方は読んでいただくことをお勧めします。

完成予定の式は下のようになります。

=”第”&WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)+1&”週”

ちょっと長いですね。

では、この式を部分ごとに分けて説明していきます。

まず、前後の”第&”と&”週”は文字を表示するために「&」演算子で接続しているだけです。

=“第”&WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)+1&”週”

この場所を除くと、

=WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)+1

これで第〇週かの数字が表示されるようになります。

まず、最初の「WEEKNUM(B2,2)」ですが、

=WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)+1

これは、手前で説明した1月1日の週から数えて何番目の週になるかを出すためのものです。

その数字から、「WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)」を引き算しています。

=WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)+1

同じく「WEEKNUM関数」を使用しているのですが、この場所は「DATE関数」を使用して、対象となる月の最初の週が1月1日から何番目かの結果を出してます。

うーーーん。分かりづらいですね。

下の画像をご覧ください。左側に「WEEKNUM(B2,2)」、右側に「WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)」の式を入れたものです。

あえて、「受注日」が「5月」の場所にしています。

左側の数字が「18」「19」「20」と増えているのに対して、右側の数字は「18」のままですね。

常にその月の最初の週の数字を返しているからです。

その式、

WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)

をもう一度見てみると、WEEKNUM関数の中にDATE関数を使用していますね。


DATE関数・・・指定された日付を返す関数

DATE(年,月,日)

  • 年・・・年を指定
  • 月・・・月を指定
  • 日・・・日を指定

 

年、月、日をそれぞれ指定すれば良い簡単な関数です。

その引数としてYEAR関数で対象のセルから年だけを抜き出し、

DATE(YEAR(B2),MONTH(B2),1)

MONTH関数で対象のセルから月だけを抜き出し、

DATE(YEAR(B2),MONTH(B2),1)

最後の日は、常に1日にしたいので「1」と入力します。

DATE(YEAR(B2),MONTH(B2),1)

これで、月ごとに最初の週が、その年の1月1日から何番目かを出すことができます。

最後の「日」の場所を「1」にして最初の週に固定するのがポイントですね。

そして、それらを引き算すると第〇週目かを表示することができます。

しかし、先ほどの画面をもう一度見てください。

最初の数字が両方とも「18」になっています。このままでは「18」-「18」で1週目が「第0週」になってしまいます。

そこで、最初が「0」にならないように足し算で1プラスしてあげましょう。

WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)+1

これで引き算すると、最初の週が1週目になりますね。

メインとなる式の完成です。

=WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)+1

せっかくなので、数字だけではなく「第」「週」を文字として前後に付けてあげましょう。

=“第”&WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)+1&”週”

これで出来上がり。

スポンサーリンク
スポンサーリンク

ピボットテーブルで集計する

では、作成した「週」の列も利用してピボットテーブルで集計開始です。

[挿入]タブ → [ピボットテーブル]をクリック。

対象となる表の範囲などの指定画面が表示されますが、エクセルの自動設定に任せてそのまま[OK]を押してしまいましょう。

右側に表示された作業ウインドウで集計の設定をします。

まず、日付の場所(今回の例では「受注日」)を[行]の場所に引っ張りましょう。

すると、「受注日」が[行]の場所に移動してくれるのですが、同時に日付データが長期の場合は、「年」「四半期」などが自動でグループ化されます。
※2013以前のバージョンでは、自動グループ化は設定されません。
※元データの内容により、「月」が表示される場合もあります。

メインの画面では、行の場所に日付が表示されます。
※日付データの期間により、表示結果は異なります。

続いて、先ほど作成した「週」の場所を、今設定した[行]のさらに下に引っ張ってきましょう。

これで、[行]の場所で「週」ごとの集計が行われます。

ちなみに、メインの画面で「+」の場所をクリックして折り畳まれた情報を開いてあげると、下のようになります。

必要の無いまとまりがある場合は、作業ウィンドウのチェックを外しましょう。
※今回は「四半期」のチェックは外して進めます。

続いて、集計したい項目を[列]の場所に引っ張ります。

今回は「商品区分」ごとに集計してみます。

最後に、集計したい数値が入っている場所を[値]の場所に引っ張ります。

今回の例では「売上」の場所になります。

これで完成。

最後の集計はあっという間ですね。

まとめ

第〇週という形で集計するには、ひと手間ふた手間増えますね。

普段は出番が少ないWEEKNUM関数が大活躍します。

 

今回の説明では、総計場所の表示非表示や移動など、ピボットテーブルそのものの細かい説明は省きました。

ピボットテーブルの基本的な操作方法を含め、細かい設定は下のリンク先で説明しています。

Excel(エクセル)でのピボットテーブルの使い方 ①|基本的な使い方(簡単に集計する方法)
Excel(エクセル)でのピボットテーブルの使い方です。ピボットテーブルとは、どのような機能なのか、何ができるのかを説明します。

7回に分けて説明していますので参考にしてください。

 

「週ごと」にまとめるのは他の状況でも使えるかもにゃ