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

問題1の解答例　　　　 topへ

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

F3セルに　=AVERAGE(FILTER($C$3:$C$33,TEXT($B$3:$B$33,"aaaa")=E3))　と入力しています。
FILTER関数で月曜日の入園者数を配列に抽出しています。抽出した配列をAVERAGE関数で平均しています。
F3セルの数式をF9セルまでコピーします。
なお、下図のように、休園日のある曜日の計算は、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))}
数式の前後が　{ } でくくられ、配列数式として入力が確定されます。

作業列に曜日を書き出します。
E列の曜日と同じスタイルにするため　D3セルの数式は　=TEXT(B3,"aaaa") としました。
- 曜日の表示に関しては　エクセル関数の技：曜日を表示する(曜日を計算する)　をご覧ください。
条件付きの平均の計算になりますので、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)
と入力して、下方向へコピーします。

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

スポンサードリンク