Introduction
Ver. 2013 2016 2019 365
エクセルで、ピボットテーブルを使用して週単位で集計する方法です。
月や年単位では簡単に集計できるのですが、正確に週単位にするには元の表に工夫が必要です。
ピボットテーブルの集計画面には「週」が存在しないのですね。
ここでは、下のように月ごとに週をまとえる方法を紹介します。
月ごとにこだわらず「7日」ごとに集計したい場合は、
下のリンク先の後半で説明しています。好きな日にちから7日ごとの集計であれば簡単に設定できますよ。
第〇週はちょっと面倒だにゃ
週でまとめるために、表を加工する
月ごとに週でまとめるには元の表に、その日が何週目かを表示しておく必要があります。
そのやり方を説明した後に、ピボットテーブルでの集計方法へ移ります。
また、何週目かを表示する方法ですが、スタートする週から数えて〇週と連番のように表示する方法と、
月ごとに分かり易く第〇週と表示する方法に分けて説明します。
表示は気にせず、最初の週から連番にしてまとめる方法
月ごとに「第〇週」と表示する必要が無ければ、こちらの方法が簡単です。
今回は下の表を使用して説明します。
ちなみにこのデータは、「2021/1/1」～「2023/12/31」までの３年間のデータとなっています。
まず、右側に第何週かを表示する列を挿入しましょう。
ここに、関数を使用して「受注日」の日付が何週目かを表示していきます。
使用する関数は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」で１週目が「第0週」になってしまいます。
そこで、最初が「0」にならないように足し算で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&”週”
これで出来上がり。
ピボットテーブルで集計する
では、作成した「週」の列も利用してピボットテーブルで集計開始です。
［挿入］タブ → ［ピボットテーブル］をクリック。
対象となる表の範囲などの指定画面が表示されますが、エクセルの自動設定に任せてそのまま［OK］を押してしまいましょう。
右側に表示された作業ウインドウで集計の設定をします。
まず、日付の場所（今回の例では「受注日」）を［行］の場所に引っ張りましょう。
すると、「受注日」が［行］の場所に移動してくれるのですが、同時に日付データが長期の場合は、「年」「四半期」などが自動でグループ化されます。
※2013以前のバージョンでは、自動グループ化は設定されません。
※元データの内容により、「月」が表示される場合もあります。
メインの画面では、行の場所に日付が表示されます。
※日付データの期間により、表示結果は異なります。
続いて、先ほど作成した「週」の場所を、今設定した［行］のさらに下に引っ張ってきましょう。
これで、［行］の場所で「週」ごとの集計が行われます。
ちなみに、メインの画面で「+」の場所をクリックして折り畳まれた情報を開いてあげると、下のようになります。
必要の無いまとまりがある場合は、作業ウィンドウのチェックを外しましょう。
※今回は「四半期」のチェックは外して進めます。
続いて、集計したい項目を［列］の場所に引っ張ります。
今回は「商品区分」ごとに集計してみます。
最後に、集計したい数値が入っている場所を［値］の場所に引っ張ります。
今回の例では「売上」の場所になります。
これで完成。
最後の集計はあっという間ですね。
まとめ
第〇週という形で集計するには、ひと手間ふた手間増えますね。
普段は出番が少ないWEEKNUM関数が大活躍します。
今回の説明では、総計場所の表示非表示や移動など、ピボットテーブルそのものの細かい説明は省きました。
ピボットテーブルの基本的な操作方法を含め、細かい設定は下のリンク先で説明しています。
７回に分けて説明していますので参考にしてください。
「週ごと」にまとめるのは他の状況でも使えるかもにゃ