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

問題　　　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) として曜日も表示しています。
  

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

GROUPBY関数を使って計算する

• Excel for Microsoft365を使っている場合は、配列を処理する関数で数式を作成することができます。 (記 2025/9/12)

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

• トリム参照では以下のように書くことも可能です。増減するデータに対処できます。
  =DROP(
  GROUPBY(
  HSTACK(WEEKDAY(B3:.B100,2),TEXT(B3:.B100,"aaaa")),
  C3:.C100,AVERAGE),,1)
  と入力します。
  

FILTER関数を使って計算する

• Microsoft365ではFILTER関数を使うことができます。
  FILTER関数の詳細な使い方は FILTER関数でデータを抽出する　をご覧ください。
• 数式のセル範囲が間違っていました。B33、C33が最終行でしたがB31,C31としていました。(2025/9/12 に修正しました)

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

配列数式で計算する

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

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

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

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

• スピルの機能が使えない場合（Excel2019以前のバージョン）は、絶対参照や複合参照の設定が必要になります。
  F3セルに
  =AVERAGEIF($D$3:$D$33,E3,$C$3:$C$33)
  と入力して、下方向へコピーします。
  

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

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