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

問題 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関数を使って計算する

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

配列数式で計算する

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

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($D3ドル:$D33,ドルE3,$C3ドル:$C33ドル)
  と入力して、下方向へコピーします。
  

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

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