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

スポンサードリンク

問題　　　topへ

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

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

• 問題2：上記データを月〜金と土日に分けて集計しなさい。　　問題2の解答

問題1の解答例　　　　topへ

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

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

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

FILTER関数を使って計算する

• Microsoft365ではFILTER関数を使うことができます。
  FILTER関数の詳細な使い方は FILTER関数でデータを抽出する　をご覧ください。

1. F3セルに　=SUM(FILTER($C$3:$C$21,TEXT($B$3:$B$21,"aaaa")=E3))　と入力しています。
   FILTER関数で 月曜日の販売金額を配列に抽出しています。抽出した配列をSUM関数で合計しています。
   
2. F3セルを選択して、フィルハンドルをダブルクリックして数式を下方向へコピーします。
   または、F3:F9セルを選択して、[Ctrl]+[D]でコピーする方法もあります。
   

配列数式で計算する

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

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

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

1. 作業列に曜日を書き出します。
   E列の曜日と同じスタイルにするため　D3セルの数式は　=TEXT(B3,"aaaa") としました。
   • 曜日の表示に関しては　エクセル関数の技：曜日を表示する(曜日を計算する)　をご覧ください。
   
2. 条件付きの合計計算になりますので、SUMIF関数が使用できます。
   F3セルには　=SUMIF($D$3:$D$21,E3,$C$3:$C$21)　と入力しました。
   • SUMIF関数に関しては　条件付きで合計する関数(SUMIF関数)の使い方)　をご覧ください。
   

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

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

問題2の解答例　　　　topへ

FILTER関数を使って計算する

• Microsoft365ではFILTER関数を使うことができます。
  FILTER関数の詳細な使い方は FILTER関数でデータを抽出する　をご覧ください。

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,2) と数式を入力しました。
   引数を 2 としているのがポイントです。月〜金は　1〜5 、土日は6,7 が返されるのでこの引数を使っています。
   • WEEKDAY関数に関しては　Excel(エクセル)基本講座：曜日を調べる関数（WEEKDAY関数)　をご覧ください。
   
2. 月〜金　は　=SUMIF($D$3:$D$21,"<=5",$C$3:$C$21)　と作業列が 5以下のC列を合計しました。
   
3. 土日は　=SUMIF($D$3:$D$21,">=6",$C$3:$C$21)　と作業列が　6以上のC列を合計しました。