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

問題　　　topへ

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

  	B	C
  2	日付	総入園者数
  3	2024/3/1	768
  4	2024/3/2	971
  5	2024/3/3	2,048
  6	2024/3/4	962
  7	2024/3/5	1,132
  8	2024/3/6	1,216
  9	2024/3/7	853
  10	2024/3/8	1,134
  11	2024/3/9	2,329
  12	2024/3/10	2,109
  13	2024/3/11	1,294
  14	2024/3/12	1,015
  15	2024/3/13	休園
  16	2024/3/14	1,527
  17	2024/3/15	1,231
  18	2024/3/16	2,765
  19	2024/3/17	1,919
  20	2024/3/18	1,063
  21	2024/3/19	960
  22	2024/3/20	3,051
  23	2024/3/21	1,483
  24	2024/3/22	1,604
  25	2024/3/23	3,410
  26	2024/3/24	4,800
  27	2024/3/25	2,156
  28	2024/3/26	2,553
  29	2024/3/27	休園
  30	2024/3/28	3,323
  31	2024/3/29	1,045
  32	2024/3/30	3,498
  33	2024/3/31	4,829

問題1の解答例　　　　topへ

• 解答例
  下表の計算結果になります。
  わかりやすいように、B列の表示形式を yyyy/m/d(aaa) として曜日も表示しています。
  

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セルに　
  　と入力されています。
• 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] で入力を確定する必要があります。
  すると、｛=AVERAGE(IF(TEXT($B$3:$B$31,"aaaa")=E3,$C$3:$C$31))}
  数式の前後が　{ } でくくられ、配列数式として入力が確定されます。
• 

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

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

1. 作業列に曜日を書き出します。
   E列の曜日と同じスタイルにするため　D3セルの数式は　=TEXT(B3,"aaaa") としました。
   • 曜日の表示に関しては　エクセル関数の技：曜日を表示する(曜日を計算する)　をご覧ください。
   
2. 条件付きの平均の計算になりますので、AVERAGEIF関数が使用できます。
   AVERAGEIF関数はExcel2007以降で使用できます。
   また、Excel for Microsoft365、Excel2021ではスピルが使えます。
   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. フィールドを下図のように、行に「作業列」、Σ値に「総入園者数」をドラッグして設定します。
   [個数/総入園者数]となっているので、[平均/総入園者数]に変更します。
   
   • Σ値で「個数/総入園者数」となっているときは、ピボットテーブルのフィールドでラベルを右クリックして、リストから[値の集計方法]→[平均]をクリックします。
     
   • セルの表示形式はピボットテーブルのフィールドでラベルを右クリックして、リストから[値フィールドの設定]をクリックします。
     
     値フィールドの設定 ダイアログボックスで[表示形式]ボタンをクリックします。
     セルの書式設定ダイアログで表示形式を設定することができます。
     
5. 平均が計算できました。