エクセル練習問題：曜日別平均客単価の算出とデータ分析の極意
Excel VBAや高度な関数を駆使する現場において、最も頻繁に遭遇する要件の一つが「時系列データの集計」です。特に、飲食店や小売店の実務において「曜日ごとの傾向」を把握することは、人員配置の最適化や仕入れ計画の策定に直結する極めて重要な経営指標となります。本稿では、単なる平均値の算出にとどまらず、VBAを用いた効率的なデータ処理と、実務で信頼されるコードの書き方を解説します。
1. 概要：曜日別平均客単価とは何か
曜日別平均客単価とは、特定の曜日（月曜日、火曜日など）における「売上合計」を「客数合計」で割った値、あるいは「個別の客単価」の平均値を指します。この指標が重要な理由は、曜日によって顧客層や購買行動が大きく異なるためです。
例えば、平日はビジネスマン中心でランチ単価が安定している一方、週末はファミリー層が多く、客単価が変動する傾向があります。Excelでこれを算出する際、初心者はフィルター機能で曜日ごとに抽出して計算しがちですが、データ量が増加するとこの手法は破綻します。プロフェッショナルなエンジニアは、VBAの「連想配列（Dictionary）」や「フィルタリングアルゴリズム」を駆使して、一瞬で、かつ再利用可能な集計システムを構築します。
2. 詳細解説：ロジックの構築と効率化のポイント
この問題を解くためのアプローチには、大きく分けて「関数によるアプローチ」と「VBAによるアプローチ」の2種類があります。
まず、関数を用いる場合、AVERAGEIF関数が基本となります。しかし、日付データから曜日を取り出すために「TEXT関数」や「WEEKDAY関数」を併用する必要があり、数式が複雑化しがちです。
次にVBAを用いる場合、以下のステップでロジックを組むのが最適です。
1. 日付データから曜日を判定する：Weekday関数またはFormat関数を使用します。
2. データをループ処理する：For EachまたはFor Nextループで全行を走査します。
3. 集計用メモリに値を格納する：Scripting.Dictionaryオブジェクトを使用し、曜日をキー（Key）として、売上合計と客数合計を配列やクラスで管理します。
4. 最終的な平均値を算出する：Dictionaryに格納された値を取り出し、シートへ出力します。
特にDictionaryオブジェクトを使用するメリットは、重複排除と高速な検索が可能である点です。大量のトランザクションデータ（数万行）であっても、メモリ上で計算を完結させるため、Excelの再計算負荷を最小限に抑えることができます。
3. サンプルコード：曜日別平均客単価を算出するVBA
以下に、実務でそのまま利用可能な、堅牢かつ可読性の高いVBAコードを提示します。このコードは、A列に日付、B列に売上、C列に客数が入力されているシートを想定しています。
Option Explicit
Sub CalculateAverageSpendByDay()
' 参照設定: Microsoft Scripting Runtime を推奨
Dim dictSales As Object
Dim dictCount As Object
Set dictSales = CreateObject("Scripting.Dictionary")
Set dictCount = CreateObject("Scripting.Dictionary")
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Dim i As Long
Dim targetDate As Date
Dim dayName As String
Dim sales As Double
Dim customerCount As Long
' データループ処理
For i = 2 To lastRow
targetDate = ws.Cells(i, 1).Value
sales = ws.Cells(i, 2).Value
customerCount = ws.Cells(i, 3).Value
' 曜日を取得 (日本語環境で"月", "火"など)
dayName = Format(targetDate, "aaa")
' 辞書に集計値を加算
If Not dictSales.Exists(dayName) Then
dictSales(dayName) = sales
dictCount(dayName) = customerCount
Else
dictSales(dayName) = dictSales(dayName) + sales
dictCount(dayName) = dictCount(dayName) + customerCount
End If
Next i
' 結果の出力
Dim resultSheet As Worksheet
Set resultSheet = ThisWorkbook.Sheets.Add
resultSheet.Name = "曜日別集計_" & Format(Now, "hhmmss")
resultSheet.Range("A1").Value = "曜日"
resultSheet.Range("B1").Value = "平均客単価"
Dim key As Variant
Dim r As Long: r = 2
For Each key In dictSales.Keys
resultSheet.Cells(r, 1).Value = key
' 0除算回避のチェック
If dictCount(key) > 0 Then
resultSheet.Cells(r, 2).Value = dictSales(key) / dictCount(key)
Else
resultSheet.Cells(r, 2).Value = 0
End If
r = r + 1
Next key
MsgBox "集計が完了しました。", vbInformation
End Sub
4. 実務アドバイス：エンジニアとして意識すべき品質
コードを書く上で最も重要なのは「保守性」です。上記のコードでは、以下の工夫を行っています。
1. Option Explicitの強制：変数の宣言漏れを防ぎ、バグを未然に防ぎます。これはプロのエンジニアとしての最低限の作法です。
2. 0除算の考慮：客数が0人である可能性を考慮し、If文で除算エラー（#DIV/0!）を回避しています。実務データには必ずと言っていいほど「異常値」が含まれます。
3. 動的なシート作成：結果を別シートに出力することで、元のデータ構造を汚さないように設計しています。これにより、何度テストしても元のデータが破壊されません。
4. 辞書の活用：IF文で曜日を分岐させるのではなく、Keyとして曜日を扱うことで、将来的に「祝日対応」や「時間帯別」への拡張が容易になっています。
また、実務では「日付データが正しく認識されていない」トラブルが頻発します。Format関数を通す前に、IsDate関数で日付型として妥当かチェックする工程を加えると、さらに堅牢性が向上します。
5. まとめ：データ分析の自動化がもたらす価値
曜日別平均客単価の算出というシンプルな課題であっても、VBAを用いて論理的に解決することで、その後の分析や意思決定のスピードは劇的に向上します。手作業によるミスはゼロになり、計算プロセスが可視化されることで、組織内でのナレッジ共有もスムーズになります。
今回紹介したコードは、単なる一つの練習問題の解答ではありません。これは、大量のレコードを効率的に集計するための「テンプレート」です。このロジックを理解すれば、曜日別だけでなく、「店舗別」「担当者別」「商品カテゴリ別」といった任意の切り口での分析へ応用可能です。
Excelは単なる表計算ソフトではなく、強力なデータ分析プラットフォームです。プロフェッショナルとして、常に「効率的で、美しく、誰が見ても理解できるコード」を書くことを意識してください。それが、あなたのエンジニアとしての市場価値を最大化する最短ルートとなります。技術を磨き続け、複雑な課題をシンプルに解決できるプロフェッショナルを目指してください。
