Excelで曜日ごとに集計する方法！WEEKDAYとSUMPRODUCTで曜日別に合計する手順

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

曜日ごとに集計したいとき、日付の列から曜日を取り出して、曜日別に合計する必要があります。
今回は
・TEXT関数で日付から曜日を出し、SUMPRODUCT関数で曜日ごとの売上を合計する方法
をゴールにします。
同じ考え方で、曜日ごとの件数（COUNTIF関数）と平均（AVERAGEIF関数）も一度に出せるようになります。

上の画像が完成形です。左の売上表に曜日が入り、右の集計表に曜日ごとの合計・件数・平均が自動で表示されます。

なお、毎回数式を入れるのが手間な方向けにVBAで曜日ごとの集計表をまとめて作る方法と、うまくいかないときの対処法（トラブルシュート）もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. 元データを用意する

まずは曜日ごとに集計したい売上表を用意します。
A列が日付、B列が曜日、C列が売上です。
日付は「2024/4/1」のようにスラッシュで区切って入力しましょう。
「2024.4.1」のように別の記号で入力すると、文字列として扱われて曜日が出せなくなることがあります。
B列の曜日は、次のステップで数式で自動的に入れるので空のままにしておきます。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. 日付から曜日を出す（TEXT関数）

まずは日付から曜日を取り出します。
曜日を出したい B2セル に =TEXT(A2,”aaa”) と入力します。
この数式は、日付の曜日を「月」「火」のような1文字で返します。
入力したら、フィルハンドルでB15セルまで下にコピーしましょう。
曜日が一気に入りました。
カンタンですね。

数式はどのセルに入れる？

セルを選んで、数式バーに直接入力するだけです。
実際の入力画面がこちらです。
TEXT関数は （値, 表示形式） の順に指定します。
表示形式の “aaa” が曜日を表す記号です。
“aaaa” にすると「月曜日」のようにフル表示になるので、好みで使い分けましょう。

STEP3. 曜日ごとの合計を出す（SUMPRODUCT関数）

次に、曜日ごとの合計を出す集計表を表の右側に作ります。
E列に曜日（月〜日）、F列に合計を出す数式を入れます。
F2セルに次の数式を入力しましょう。

=SUMPRODUCT(($B$2:$B$15=$E2)*$C$2:$C$15)

これは「B列がE2セルの曜日と一致する行の、C列（売上）を合計する」という意味です。
入力したらF8セルまでコピーします。
曜日ごとの合計が一度に出ました。

SUMPRODUCT関数の入力画面

実際の入力画面がこちらです。
SUMPRODUCT関数は （配列1, [配列2], …） を掛け合わせて合計します。
今回は「曜日が一致するか」の判定結果に売上を掛けて足し合わせています。
配列数式のように見えますが、Ctrl+Shift+Enterは不要です。
そのままEnterで確定できます。

STEP4. 曜日ごとの件数を数える（COUNTIF関数）

合計だけでなく、曜日ごとの件数も出してみましょう。
G2セルに =COUNTIF($B$2:$B$15,$E2) と入力し、G8セルまでコピーします。
COUNTIF関数は、範囲の中で条件に一致するセルの個数を数えます。
B列は曜日の文字なので、曜日ごとのデータ件数がそのまま出ます。
「月曜日は3件、火曜日は2件」といった具合に確認できますね。

STEP5. 曜日ごとの平均を出す（AVERAGEIF関数）

最後に、曜日ごとの平均売上も出しましょう。
H2セルに =AVERAGEIF($B$2:$B$15,$E2,$C$2:$C$15) と入力し、H8セルまでコピーします。
AVERAGEIF関数は、条件に一致するセルの平均を求めます。
合計を件数で割ったのと同じ結果になります。
件数が1件の曜日は、そのままの金額が平均になります。
これで曜日ごとの傾向が一目でつかめますね。

曜日の出し方の使い分け

曜日を出す方法はいくつかあります。
集計に使うか、見た目を重視するかで使い分けましょう。

VBAで曜日ごとの集計表をまとめて作る

毎回数式を手で入れるのが面倒なときは、VBAで一気に作るのもおすすめです。
Alt+F11 でVBAエディターを開き、標準モジュールに次を貼り付けて実行します。

Sub WeekdaySummary()
    Dim i As Long, L As Long, s As String, d As Variant
    L = Cells(Rows.Count, 1).End(xlUp).Row
    s = "$B$2:$B$" & L
    For i = 2 To L
        Cells(i, 2).Formula = "=TEXT(A" & i & ",""aaa"")"
    Next i
    d = Array("月", "火", "水", "木", "金", "土", "日")
    For i = 0 To UBound(d)
        Cells(i + 2, 5).Value = d(i)
        Cells(i + 2, 6).Formula = SumExpr(s, L, i + 2)
        Cells(i + 2, 7).Formula = "=COUNTIF(" & s & ",$E" & i + 2 & ")"
        Cells(i + 2, 8).Formula = "=AVERAGEIF(" & s & ",$E" & i + 2 & ",$C$2:$C$" & L & ")"
    Next i
End Sub

Private Function SumExpr(s As String, L As Long, r As Long) As String
    SumExpr = "=SUMPRODUCT((" & s & "=$E" & r & ")*$C$2:$C$" & L & ")"
End Function

タカヒロ

曜日は手入力してもいいのですが、TEXT関数にしておくと日付を変えたときに曜日も自動で変わります。
集計表の曜日は、サンプルのように月〜日を並べておくのがコツです。
これで日付を足すだけで、集計が自動で更新されますよ。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったように動かないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

関数の説明

今回使う4つの関数の概要です。

TEXT関数（テキスト）

=TEXT(A2,"aaa")

SUMPRODUCT関数（サムプロダクト）

=SUMPRODUCT(($B$2:$B$15=$E2)*$C$2:$C$15)

COUNTIF関数（カウントイフ）

=COUNTIF($B$2:$B$15,$E2)

AVERAGEIF関数（アベレージイフ）

=AVERAGEIF($B$2:$B$15,$E2,$C$2:$C$15)

完成

左の売上表に曜日が入り、右の集計表に曜日ごとの合計・件数・平均が出ました。
金曜日と土曜日は売上が高く、水曜日は低い、といった傾向も一目でわかります。
思ったとおりに動きましたか？
日付の行を増やせば、集計表も自動で最新の状態に更新されます。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル表で、ぜひ一度試してみてください。
日付から曜日を出して集計するやり方を覚えると、売上や来店数の曜日別分析がぐっとラクになりますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。