曜日ごとに集計する：エクセル練習問題

スポンサードリンク

問題　　　topへ

• 問題1：B列に年月日が入力されています。C列に販売金額が入力されています。
  E列には曜日名が入力してあります。F列はE列の各曜日ごとの集計をしなさい。　　問題1の解答
  

  	B	C	D	E	F
  2	日付	販売金額			合計金額
  3	2024/4/10	79,189		月曜日
  4	2024/4/11	59,521		火曜日
  5	2024/4/12	82,318		水曜日
  6	2024/4/13	54,082		木曜日
  7	2024/4/14	88,750		金曜日
  8	2024/4/15	56,704		土曜日
  9	2024/4/16	96,515		日曜日
  10	2024/4/17	73,487
  11	2024/4/18	97,031
  12	2024/4/19	78,983
  13	2024/4/20	74,475
  14	2024/4/21	53,645
  15	2024/4/22	69,599
  16	2024/4/23	58,205
  17	2024/4/24	69,892
  18	2024/4/25	93,955
  19	2024/4/26	84,649
  20	2024/4/27	79,783
  21	2024/4/28	74,238

• 問題2：上記データを月〜金と土日に分けて集計しなさい。　　問題2の解答
  下図のような結果になります。
  

問題1の解答例　　　　topへ

• 解答例
  下図のような計算結果になります。
  

GROUPBY関数を使って求める	FILTER関数を使って求める
SUMPRODUCT関数を使って求める	配列数式で計算する
作業列を利用して計算する	ピボットテーブルを使って集計する

GROUPBY関数を使って計算する

• この方法は曜日も計算結果も同時に返します。
  E列の曜日は入力していません。

1. GROUPBY関数はExcel for Microsoft365で利用できます。
   =GROUPBY(TEXT(B3:B21,"aaaa"),C3:C21,SUM,,0)
   と入力します。
   一応、曜日ごとに合計が計算できましたが、曜日の並びが文字コード順に並んでいます。
   
2. 月曜日〜日曜日　の順番にしたいと思います。
   配列を　WEEKDAY(B3:B21,2)　で作成します。Weekday関数で 日付の曜日を1〜7の数値に計算します。
   この配列をHSTACK関数でB列に付け足します。
   HSTACK関数は Excel2024から使用できます。
   D3セルに
   =GROUPBY(
   HSTACK(WEEKDAY(B3:B21,2),TEXT(B3:B21,"aaaa")),
   C3:C21,SUM,,0)
   と入力します。
   
3. E列の数値が邪魔なので Drop関数で削除します。
   DROP関数は Excel2024から使用できます。
   E3セルに
   =DROP(
   GROUPBY(
   HSTACK(WEEKDAY(B3:B21,2),TEXT(B3:B21,"aaaa")),
   C3:C21,SUM,,0),,1)
   と入力しました。合計を表示しない設定にしています。
   

FILTER関数を使って計算する

• Excel for Microsoft365,Excel2021以降ではFILTER関数を使うことができます。
  FILTER関数の詳細な使い方は FILTER関数でデータを抽出する　をご覧ください。
• E列には曜日をあらかじめ入力しておく必要があります。
• FILTER関数で 月曜日の販売金額を配列に抽出して、抽出した配列をSUM関数で合計しています。
  F3セルに　=SUM(FILTER($C$3:$C$21,TEXT($B$3:$B$21,"aaaa")=E3))　
  と入力して、オートフィルなどで下方向へ数式をコピーします。
  • F3セルを選択して、フィルハンドルをダブルクリックして数式を下方向へコピーします。
    または、F3:F9セルを選択して、[Ctrl]+[D]でコピーする方法もあります。
  

SUMPRODUCT関数で計算する

• E列には曜日をあらかじめ入力しておく必要があります。
• SumProduct関数で曜日と販売数を掛け合わせて合計します。
  F3セルに =SUMPRODUCT((TEXT($B$3:$B$21,"aaaa")=E3)*$C$3:$C$21)　
  と入力して、下方向へオートフィルなどで数式をコピーします。
  

配列数式で計算する

• E列には曜日をあらかじめ入力しておく必要があります。
• TEXT(($B$3:$B$21,"aaaa")　の部分で　B3:B21セルの日付(シリアル値)から　Text関数で曜日を取り出しています。
  E列の曜日とを比較して、曜日が一致したら、C列の販売金額を合計するという仕組みになっています。
• Excel for Microsoft365,Excel2021以降で動的配列数式が扱えるバージョンでは、
  =SUM(IF(TEXT($B$3:$B$21,"aaaa")=E3,$C$3:$C$21)) と入力します。
• Excel2019以前のバージョンでは[Shift] + [Ctrl] + [Enter] で入力を確定する必要があります。
  すると、数式の前後が　{ } でくくられ、配列数式として入力が確定されます。
  ｛=SUM(IF(TEXT($B$3:$B$21,"aaaa")=E3,$C$3:$C$21))} と入力されています。
• 

作業列を利用して計算する

• 配列数式を使わない方法としては、作業列を使用する方法が考えられます。

1. 作業列に曜日を書き出します。
   E列の曜日と同じスタイルにするため　D3セルの数式は　=TEXT(B3:B21,"aaaa") としました。
   Excel2019以前のバージョンでは=TEXT(B3,"aaaa") と入力して、下方向へ数式をコピーします。
   • 曜日の表示形式に関しては　エクセル関数の技：曜日を表示する(曜日を計算する)　をご覧ください。
   
2. 条件付きの合計計算になりますので、SUMIF関数が使用できます。
   Excel for Microsoft365,Excel2021以降でスピルが利用できるバージョンでは、
   F3セルに　=SUMIF(D3:D21,E3:E9,C3:C21)　と入力します。
   Excel2019以前のスピルが使用できないバージョンでは
   =SUMIF($D$3:$D$21,E3,$C$3:$C$21)　と入力して、下方向へ数式をコピーします。
   • SUMIF関数に関しては　条件付きで合計する関数(SUMIF関数)の使い方)　をご覧ください。
   

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

1. 作業列に　=TEXT(B3:B21,"aaaa")　と入力して、曜日を表示します。
   
2. セル範囲 B2:D21を選択します。
   [挿入]タブの[ピボットテーブル]を実行します。
   
3. ピボットテーブルの作成ダイアログボックスが表示されます。
   テーブル/範囲に　B2:D21 が設定されているのを確認します。
   ピボットテーブルを配置する場所を選択してください。で「既存のワークシート」にチェックを入れて、セルを指定します。
   
4. フィールドを下図のように、行に「作業列」、Σ値に「合計/販売金額」をドラッグして設定します。
   曜日ごとに集計ができました。
   
   • Σ値で「個数/販売金額」など合計でない場合はピボットテーブルのフィールドを右クリックして、リストから[値の集計方法]→[合計]をクリックします。
     
   • セルの表示形式は[値フィールドの設定]をクリックします。
     フィールドの設定ダイアログで表示形式を設定することができます。
     
     ↓
     
5. 計算結果が表示されました。
   

問題2の解答例　　　　topへ

• Excel for Microsoft365、Excel2021以降のバージョンではFILTER関数を使うことができます。
  FILTER関数の詳細な使い方は FILTER関数でデータを抽出する　をご覧ください。

FILTER関数+WeekDay関数を使って計算する

• WeekDay関数は日付に対応する曜日を数字で表示させることができる関数です。
  引数に2を指定すると、月曜日が1，火曜日が2,・・・,金曜日が5，土曜日が6，日曜日が7が返されます。
  
• 月〜金は　WEEKDAY関数で5以下、土日は６以上が返されることがわかります。
  この値でデータを分けて計算すればよいことがわかります。
  FILTER関数を使って、月〜金は　=SUM(FILTER(C3:C21,WEEKDAY(B3:B21,2)<=5))　で計算します。
  土日は　=SUM(FILTER(C3:C21,WEEKDAY(B3:B21,2)>=6))　となります。
  

FILTER関数+TEXT関数を使って計算する

1. まず、土日の合計を求めます。(条件が少ないので)
   F4セルに　=SUM(FILTER(C3:C21,(TEXT(B3:B21,"aaaa")="土曜日")+(TEXT(B3:B21,"aaaa")="日曜日")))　と入力します。
   
2. 月〜金はすべての合計から土日を差し引いて求めました。
   F3セルに　=SUM(C3:C21)-F4　と入力しました。
   

作業列を使って計算しました。

1. D列に作業列を設けました。
   D3セルに　=WEEKDAY(B3:B21,2) と数式を入力しました。
   Excel2019以前では =WEEKDAY(B3,2) と入力して、下方向へ数式をコピーします。
   引数を 2 としているのがポイントです。月〜金は　1〜5 、土日は6,7 が返されるのでこの引数を使っています。
   • WEEKDAY関数に関しては　Excel(エクセル)基本講座：曜日を調べる関数（WEEKDAY関数)　をご覧ください。
   
2. 月〜金　は　=SUMIF(D3:D21,"<=5",C3:C21)　と作業列が 5以下のC列を合計しました。
   
3. 土日は　=SUMIF(D3:D21,">=6",C3:C21)　と作業列が　6以上のC列を合計しました。