- Home »
- エクセル練習問題 »
- 曜日ごとに集計する
更新:2024/5/6;作成: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の解答
- 解答例
下表の計算結果になります。
- Excel for Microsoft365,Excel2021ではFILTER関数を使うことができます。
FILTER関数の詳細な使い方は FILTER関数でデータを抽出する をご覧ください。
- FILTER関数で 月曜日の販売金額を配列に抽出して、抽出した配列をSUM関数で合計しています。
F3セルに =SUM(FILTER($C$3:$C$21,TEXT($B$3:$B$21,"aaaa")=E3))
と入力して、下方向へ数式をコピーします。
- F3セルを選択して、フィルハンドルをダブルクリックして数式を下方向へコピーします。
または、F3:F9セルを選択して、[Ctrl]+[D]でコピーする方法もあります。
- TEXT(($B$3:$B$21,"aaaa") の部分で B3:B21セルの日付(シリアル値)から Text関数で曜日を取り出しています。
E列の曜日とを比較して、曜日が一致したら、C列の販売金額を合計するという仕組みになっています。
この数式は配列を扱うための数式にしてありますので、
=SUM(IF(TEXT($B$3:$B$21,"aaaa")=E3,$C$3:$C$21)) と入力して、
[Shift] + [Ctrl] + [Enter] で入力を確定する必要があります。
すると、数式の前後が { } でくくられ、配列数式として入力が確定されます。
｛=SUM(IF(TEXT($B$3:$B$21,"aaaa")=E3,$C$3:$C$21))} と入力されています。
-
- 配列数式を使わない方法としては、作業列を使用する方法が考えられます。
- 作業列に曜日を書き出します。
E列の曜日と同じスタイルにするため D3セルの数式は =TEXT(B3,"aaaa") としました。
- 条件付きの合計計算になりますので、SUMIF関数が使用できます。
F3セルには =SUMIF($D$3:$D$21,E3,$C$3:$C$21) と入力しました。
- 作業列に =TEXT(B3,"aaaa") と入力して、曜日を表示します。
- [挿入]タブの[ピボットテーブル]を実行します。
- ピボットテーブルの作成ダイアログボックスが表示されます。
テーブル/範囲に B2:D21 が設定されているのを確認します。
ピボットテーブルを配置する場所を選択してください。で「既存のワークシート」にチェックを入れて、セルを指定します。
- フィールドを下図のように、行に「作業列」、Σ値に「合計/販売金額」をドラッグして設定します。
曜日ごとに集計ができました。
- Σ値で「個数/販売金額」など合計でない場合はピボットテーブルのフィールドでラベルを右クリックして、リストから[値の集計方法]→[合計]をクリックします。
- セルの表示形式は[値フィールドの設定]をクリックします。
フィールドの設定ダイアログで表示形式を設定することができます。
↓
- 計算結果が表示されました。
FILTER関数を使って計算する
- まず、土日の合計を求めます。(条件が少ないので)
F4セルに =SUM(FILTER(C3:C21,(TEXT(B3:B21,"aaaa")="土曜日")+(TEXT(B3:B21,"aaaa")="日曜日"))) と入力します。
- 月〜金はすべての合計から土日を差し引いて求めました。
F3セルに =SUM(C3:C21)-F4 と入力しました。
作業列を使って計算しました。
- D列に作業列を設けました。
D3セルに =WEEKDAY(B3,2) と数式を入力しました。
引数を 2 としているのがポイントです。月〜金は 1〜5 、土日は6,7 が返されるのでこの引数を使っています。
- 月〜金 は =SUMIF($D$3:$D$21,"<=5",$C$3:$C$21) と作業列が 5以下のC列を合計しました。
- 土日は =SUMIF($D$3:$D$21,">=6",$C$3:$C$21) と作業列が 6以上のC列を合計しました。
スポンサードリンク
Home｜エクセル練習問題：目次｜曜日ごとに集計する
PageViewCounter
Since2006/2/27