エクセル練習問題：曜日ごとに平均を求める

問題　　　topへ

• 問題1：各日付別の入園者データがあります。月曜日〜日曜日の各入園者数の平均を求めなさい。
  なお、休園日には「休園」と入力しています。　　　問題1の解答
  データの出典：札幌市円山動物園入園者数　https://ckan.pf-sapporo.jp/dataset/sapporo_maruyama_zoo_visitors
  

  	B	C
  2	日付	総入園者数
  3	2020/2/1	1317
  4	2020/2/2	1900
  5	2020/2/3	724
  6	2020/2/4	989
  7	2020/2/5	1034
  8	2020/2/6	686
  9	2020/2/7	1007
  10	2020/2/8	1689
  11	2020/2/9	2063
  12	2020/2/10	1165
  13	2020/2/11	2748
  14	2020/2/12	休園
  15	2020/2/13	1176
  16	2020/2/14	802
  17	2020/2/15	1714
  18	2020/2/16	1679
  19	2020/2/17	700
  20	2020/2/18	780
  21	2020/2/19	854
  22	2020/2/20	688
  23	2020/2/21	897
  24	2020/2/22	1323
  25	2020/2/23	1002
  26	2020/2/24	1802
  27	2020/2/25	655
  28	2020/2/26	休園
  29	2020/2/27	780
  30	2020/2/28	540
  31	2020/2/29	524

問題1の解答例　　　　topへ

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

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

FILTER関数を使って計算する

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

1. F3セルに　=AVERAGE(FILTER($C$3:$C$31,TEXT($B$3:$B$31,"aaaa")=E3))　と入力しています。
   FILTER関数で 月曜日の入園者数を配列に抽出しています。抽出した配列をAVERAGE関数で平均しています。
   F3セルの数式をF9セルまでコピーします。
   
2. なお、下図のように、休園日のある曜日の計算は 、Filter関数で水曜日のデータを　I列に取り出しています。
   これをAVERAGE関数で平均していますので、休園のセルは無視して計算されることがわかります。
   

配列数式で計算する

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

AVERAGEIF関数を利用して計算する

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

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

• スピルの機能が使えない場合は、F3セルに　=AVERAGEIF($D$3:$D$31,E3,$C$3:$C$31)　と入力して、下方向へコピーします。
  

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

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