- Home »
- エクセル練習問題 »
- 曜日ごとに集計する
更新:2025/9/17;作成:2017/3/27
- 問題1：B列に年月日が入力されています。C列に販売金額が入力されています。
E列には曜日名が入力してあります。F列はE列の各曜日ごとの集計をしなさい。 問題1の解答
|
B |
C |
D |
E |
F |
| 2 |
日付 |
販売金額 |
|
|
合計金額 |
| 3 |
2024/4/10 |
79,189 |
|
月曜日 |
|
| 4 |
2024/4/11 |
59,521 |
|
火曜日 |
|
| 5 |
2024/4/12 |
82,318 |
|
水曜日 |
|
| 6 |
2024/4/13 |
54,082 |
|
木曜日 |
|
| 7 |
2024/4/14 |
88,750 |
|
金曜日 |
|
| 8 |
2024/4/15 |
56,704 |
|
土曜日 |
|
| 9 |
2024/4/16 |
96,515 |
|
日曜日 |
|
| 10 |
2024/4/17 |
73,487 |
|
|
|
| 11 |
2024/4/18 |
97,031 |
|
|
|
| 12 |
2024/4/19 |
78,983 |
|
|
|
| 13 |
2024/4/20 |
74,475 |
|
|
|
| 14 |
2024/4/21 |
53,645 |
|
|
|
| 15 |
2024/4/22 |
69,599 |
|
|
|
| 16 |
2024/4/23 |
58,205 |
|
|
|
| 17 |
2024/4/24 |
69,892 |
|
|
|
| 18 |
2024/4/25 |
93,955 |
|
|
|
| 19 |
2024/4/26 |
84,649 |
|
|
|
| 20 |
2024/4/27 |
79,783 |
|
|
|
| 21 |
2024/4/28 |
74,238 |
|
|
|
- 問題2：上記データを月〜金と土日に分けて集計しなさい。 問題2の解答
下図のような結果になります。
- 解答例
下図のような計算結果になります。
- この方法は曜日も計算結果も同時に返します。
E列の曜日は入力していません。
- GROUPBY関数はExcel for Microsoft365で利用できます。
=GROUPBY(TEXT(B3:B21,"aaaa"),C3:C21,SUM,,0)
と入力します。
一応、曜日ごとに合計が計算できましたが、曜日の並びが文字コード順に並んでいます。
- 月曜日〜日曜日 の順番にしたいと思います。
配列を WEEKDAY(B3:B21,2) で作成します。Weekday関数で 日付の曜日を1〜7の数値に計算します。
この配列をHSTACK関数でB列に付け足します。
HSTACK関数は Excel2024から使用できます。
D3セルに
=GROUPBY(
HSTACK(WEEKDAY(B3:B21,2),TEXT(B3:B21,"aaaa")),
C3:C21,SUM,,0)
と入力します。
- E列の数値が邪魔なので Drop関数で削除します。
DROP関数は Excel2024から使用できます。
E3セルに
=DROP(
GROUPBY(
HSTACK(WEEKDAY(B3:B21,2),TEXT(B3:B21,"aaaa")),
C3:C21,SUM,,0),,1)
と入力しました。合計を表示しない設定にしています。
- Excel for Microsoft365,Excel2021以降ではFILTER関数を使うことができます。
FILTER関数の詳細な使い方は FILTER関数でデータを抽出する をご覧ください。
- E列には曜日をあらかじめ入力しておく必要があります。
- FILTER関数で 月曜日の販売金額を配列に抽出して、抽出した配列をSUM関数で合計しています。
F3セルに =SUM(FILTER($C$3:$C$21,TEXT($B$3:$B$21,"aaaa")=E3))
と入力して、オートフィルなどで下方向へ数式をコピーします。
- F3セルを選択して、フィルハンドルをダブルクリックして数式を下方向へコピーします。
または、F3:F9セルを選択して、[Ctrl]+[D]でコピーする方法もあります。
- E列には曜日をあらかじめ入力しておく必要があります。
- SumProduct関数で曜日と販売数を掛け合わせて合計します。
F3セルに =SUMPRODUCT((TEXT($B$3:$B$21,"aaaa")=E3)*$C$3:$C$21)
と入力して、下方向へオートフィルなどで数式をコピーします。
- E列には曜日をあらかじめ入力しておく必要があります。
- TEXT(($B$3:$B$21,"aaaa") の部分で B3:B21セルの日付(シリアル値)から Text関数で曜日を取り出しています。
E列の曜日とを比較して、曜日が一致したら、C列の販売金額を合計するという仕組みになっています。
- Excel for Microsoft365,Excel2021以降で動的配列数式が扱えるバージョンでは、
=SUM(IF(TEXT($B$3:$B$21,"aaaa")=E3,$C$3:$C$21)) と入力します。
- Excel2019以前のバージョンでは[Shift] + [Ctrl] + [Enter] で入力を確定する必要があります。
すると、数式の前後が { } でくくられ、配列数式として入力が確定されます。
｛=SUM(IF(TEXT($B$3:$B$21,"aaaa")=E3,$C$3:$C$21))} と入力されています。
-
- 配列数式を使わない方法としては、作業列を使用する方法が考えられます。
- 作業列に曜日を書き出します。
E列の曜日と同じスタイルにするため D3セルの数式は =TEXT(B3:B21,"aaaa") としました。
Excel2019以前のバージョンでは=TEXT(B3,"aaaa") と入力して、下方向へ数式をコピーします。
- 条件付きの合計計算になりますので、SUMIF関数が使用できます。
Excel for Microsoft365,Excel2021以降でスピルが利用できるバージョンでは、
F3セルに =SUMIF(D3:D21,E3:E9,C3:C21) と入力します。
Excel2019以前のスピルが使用できないバージョンでは
=SUMIF($D$3:$D$21,E3,$C$3:$C$21) と入力して、下方向へ数式をコピーします。
- 作業列に =TEXT(B3:B21,"aaaa") と入力して、曜日を表示します。
- セル範囲 B2:D21を選択します。
[挿入]タブの[ピボットテーブル]を実行します。
- ピボットテーブルの作成ダイアログボックスが表示されます。
テーブル/範囲に B2:D21 が設定されているのを確認します。
ピボットテーブルを配置する場所を選択してください。で「既存のワークシート」にチェックを入れて、セルを指定します。
- フィールドを下図のように、行に「作業列」、Σ値に「合計/販売金額」をドラッグして設定します。
曜日ごとに集計ができました。
- Σ値で「個数/販売金額」など合計でない場合はピボットテーブルのフィールドを右クリックして、リストから[値の集計方法]→[合計]をクリックします。
- セルの表示形式は[値フィールドの設定]をクリックします。
フィールドの設定ダイアログで表示形式を設定することができます。
↓
- 計算結果が表示されました。
- Excel for Microsoft365、Excel2021以降のバージョンではFILTER関数を使うことができます。
FILTER関数の詳細な使い方は FILTER関数でデータを抽出する をご覧ください。
FILTER関数+WeekDay関数を使って計算する
- WeekDay関数は日付に対応する曜日を数字で表示させることができる関数です。
引数に2を指定すると、月曜日が1，火曜日が2,・・・,金曜日が5，土曜日が6，日曜日が7が返されます。
- 月〜金は WEEKDAY関数で5以下、土日は６以上が返されることがわかります。
この値でデータを分けて計算すればよいことがわかります。
FILTER関数を使って、月〜金は =SUM(FILTER(C3:C21,WEEKDAY(B3:B21,2)<=5)) で計算します。
土日は =SUM(FILTER(C3:C21,WEEKDAY(B3:B21,2)>=6)) となります。
FILTER関数+TEXT関数を使って計算する
- まず、土日の合計を求めます。(条件が少ないので)
F4セルに =SUM(FILTER(C3:C21,(TEXT(B3:B21,"aaaa")="土曜日")+(TEXT(B3:B21,"aaaa")="日曜日"))) と入力します。
- 月〜金はすべての合計から土日を差し引いて求めました。
F3セルに =SUM(C3:C21)-F4 と入力しました。
作業列を使って計算しました。
- D列に作業列を設けました。
D3セルに =WEEKDAY(B3:B21,2) と数式を入力しました。
Excel2019以前では =WEEKDAY(B3,2) と入力して、下方向へ数式をコピーします。
引数を 2 としているのがポイントです。月〜金は 1〜5 、土日は6,7 が返されるのでこの引数を使っています。
- 月〜金 は =SUMIF(D3:D21,"<=5",C3:C21) と作業列が 5以下のC列を合計しました。
- 土日は =SUMIF(D3:D21,">=6",C3:C21) と作業列が 6以上のC列を合計しました。
スポンサードリンク
Home｜エクセル練習問題：目次｜曜日ごとに集計する
PageViewCounter
Since2006/2/27