条件付きで合計・件数を出す（WorksheetFunction.SumIfs・CountIfs と Evaluate の違い）

どんな場面で使うか

売上明細から「担当者ごとの今月の売上合計と件数」を集計表に埋める作業は、ピボットテーブルでもできますが、決まった形式の報告書に毎月数字を転記する場合は、マクロで直接埋める方が手間がかかりません。

VBA からは、ワークシートで使い慣れた SUMIFS や COUNTIFS を WorksheetFunction 経由で呼び出せます。条件の考え方はシートの関数とまったく同じなので、関数に慣れている方には取り組みやすい方法です。

前提とする表

「明細」シートの A列：日付、B列：担当者、C列：商品区分、D列：金額、「集計」シートの A列に担当者名（2行目から）が並んでいるとします。集計シートの B列に指定月の売上合計、C列に件数を書き込みます。

コード（担当者別・月指定の合計と件数）

日付の条件は、「>=」と月初、「<=」と月末の2つの条件を組み合わせて指定します。日付は CLng でシリアル値に変換してから文字列に連結すると、Windows の日付表示形式に左右されずに確実に比較できます。

Sub SummaryByStaff()
    Dim wsSrc As Worksheet, wsSum As Worksheet
    Dim lastRow As Long, sumLast As Long, r As Long
    Dim rngDate As Range, rngStaff As Range, rngAmt As Range
    Dim d1 As Date, d2 As Date, staff As String

    Set wsSrc = ThisWorkbook.Worksheets("明細")
    Set wsSum = ThisWorkbook.Worksheets("集計")
    lastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row
    sumLast = wsSum.Cells(wsSum.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Or sumLast < 2 Then Exit Sub

    Set rngDate = wsSrc.Range("A2:A" & lastRow)
    Set rngStaff = wsSrc.Range("B2:B" & lastRow)
    Set rngAmt = wsSrc.Range("D2:D" & lastRow)

    d1 = DateSerial(2026, 9, 1)                      ' 集計する月の初日
    d2 = DateSerial(Year(d1), Month(d1) + 1, 0)      ' 同じ月の末日

    For r = 2 To sumLast
        staff = Trim(CStr(wsSum.Cells(r, "A").Value))
        If staff <> "" Then
            wsSum.Cells(r, "B").Value = WorksheetFunction.SumIfs(rngAmt, _
                rngStaff, staff, rngDate, ">=" & CLng(d1), rngDate, "<=" & CLng(d2))
            wsSum.Cells(r, "C").Value = WorksheetFunction.CountIfs( _
                rngStaff, staff, rngDate, ">=" & CLng(d1), rngDate, "<=" & CLng(d2))
        End If
    Next r
End Sub

該当0件のときとエラーになる関数

SumIfs と CountIfs は、条件に合う行が1件もなくてもエラーにはならず、0 を返します。そのため上のコードはそのまま使えます。一方、平均を求める AverageIfs は、該当0件のときに WorksheetFunction 経由だと実行時エラー 1004 で止まります。

このような関数は、WorksheetFunction を付けずに Application から直接呼び出すと、エラーで止まる代わりにエラー値を返します。戻り値を Variant で受け取り、IsError で判定すれば、該当なしの場合だけ空欄にするといった処理ができます。件数で割って平均を出す場合も、件数が0のときの割り算に注意してください。

Dim avg As Variant
avg = Application.AverageIfs(rngAmt, rngStaff, staff)   ' 0件ならエラー値が返る
If IsError(avg) Then
    wsSum.Cells(r, "D").Value = ""
Else
    wsSum.Cells(r, "D").Value = avg
End If

Evaluate との違いと使い分け

Evaluate を使うと、Evaluate("SUMIFS(明細!D:D,明細!B:B,""佐藤"")") のように数式の文字列をそのまま計算させることもできます。シートの数式をそのまま流用できる利点はありますが、文字列の中で二重引用符を重ねる必要があり、条件を変数から組み立てると読みにくく、間違いにも気づきにくくなります。また、数式の文字列には長さの上限（255文字）があります。

条件を変数で切り替える処理には、この記事のように WorksheetFunction で引数を一つずつ渡す方が安全です。なお、担当者が数百人いて明細も数万行ある場合、SumIfs を何百回も呼び出すと時間がかかります。そのような場合は、明細を一度だけ読み込んで Dictionary で集計する方法の方が速く動きます。

• SumIfs・CountIfs は0件なら 0 を返す
• AverageIfs などは Application から呼んで IsError で判定する
• 日付は CLng でシリアル値にしてから条件に連結する

ほかの記事