大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、「SumIf」や「SumIfs」を使う方法について、網羅的に解説していきます。SumIfやSumIfs関数は、条件一致した値の合計を算出できる便利な関数です。「WorksheetFunction」や「数式埋め込み」を使って、SumIfやSumIfsを使いこなしていきましょう。

はじめに

この記事では、VBAで「SumIf関数」と「SumIfs関数」を使う方法について、ご紹介します。

SumIfやSumIfs関数を使う方法は、「WorksheetFunction」と「数式を埋め込む方法」があります。

合計した値をVBAで使う場合は、「WorksheetFunction」が便利です。

セルに合計値をそのまま入力したいのであれば、「数式を埋め込む方法」が簡単です。

VBAで「SumIf」や「SumIfs」を使いこなしていきましょう。

では、VBAで「SumIf関数」と「SumIfs関数」を使う方法について、解説していきます。

この記事で紹介すること

  • VBAで「SumIf関数」と「SumIfs関数」を使う方法

目次

WorksheetFunctionでSumIfやSumifs関数を使う

「WorksheetFunction」でSumIfやSumifs関数を使ってみます。

条件に一致した値の合計値を算出(SumIf関数)

「条件に一致した値」の「合計値」を算出できるのが、SumIf関数です。

WorksheetFunctionで「SumIf関数」を使ってみます。

WorksheetFunctionで「SumIf関数」を使う

'SumIf関数を使う
A = WorksheetFunction.SumIf(範囲, 条件, 合計範囲)

入力する引数は、関数と同じになります。

「SumIf関数」を使ってみる

Sub TEST1()
    
    '「"B"」に一致した価格の合計
    Cells(2, "D") = WorksheetFunction.SumIf(Range("A:A"), "B", Range("B:B"))
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

WorksheetFunctionで「SumIf関数」を使った結果

WorksheetFunctionで「SumIf関数」を使って、合計値を算出できました。

「SumIf関数」で「変数」や「セルの値」を参照する方法

「SumIf関数」で「変数」や「セルの値」を参照する方法について、詳細はこちらでまとめています。

実務では、変数を使ってセルの値を参照することが多いです。

複数条件に一致した値の合計値を算出(SumIfs関数)

「複数条件」に一致した値の「合計値」を算出できるのが、SumIfs関数です。

WorksheetFunctionで「SumIfs関数」を使ってみます。

WorksheetFunctionで「SumIfs関数」を使う

'SumIfs関数を使う
A = WorksheetFunction.SumIfs(合計範囲, 範囲1, 条件1, 範囲2, 条件2)

入力する引数は、セルに入力するときと同じです。

「SumIfs関数」を使ってみます。

「SumIfs関数」を使ってみる

Sub TEST2()
    
    '商品が「B」で、支店が「大阪」の価格を合計
    Range("E2") = WorksheetFunction.SumIfs(Range("C:C"), Range("A:A"), "B", Range("B:B"), "大阪")
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

WorksheetFunctionで「SumIfs関数」を使った結果

WorksheetFunctionで「SumIfs関数」を使って、合計値を算出できました。

「SumIfs関数」で「変数」や「セルの値」を参照する方法

「SumIfs関数」で「変数」や「セルの値」を参照する方法について、詳細はこちらでまとめています。

実務では、変数を使ってセルの値を参照することが多いです。うまく使いこなしましょう。

ワイルドカードを使う

SumIfやSumIfs関数は、ワイルドカードを使うことができます。

ワイルドカードの種類は、2つあります。

ワイルドカードの種類

  • 「*」:すべての値
  • 「?」:すべての値。ただし1文字

「SumIf関数」でワイルドカードを使ってみます。

「SumIf関数」でワイルドカードを使う

「Bを含む」値の合計を算出してみます。

Sub TEST3()
    
    '「"B"」を含む価格の合計
    Cells(2, "D") = WorksheetFunction.SumIf(Range("A:A"), "*B*", Range("B:B"))
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

「SumIf関数」でワイルドカードを使った結果

「SumIf関数」でワイルドカードを使って、合計値を算出できました。

「SumIfs関数」でワイルドカードを使う

次は、「SumIfs関数」でワイルドカードを使ってみます。

A列が「Bを含んで」、B列が「大阪」の合計値を算出します。

Sub TEST4()
    
    '商品が「Bを含む」値で、支店が「大阪」の価格を合計
    Range("E2") = WorksheetFunction.SumIfs(Range("C:C"), Range("A:A"), "B*", Range("B:B"), "大阪")
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

「SumIfs関数」でワイルドカードを使った結果

「SumIfs関数」でワイルドカードを使って合計値を算出できました。

比較演算子を使う

SumIfとSumIfs関数は、「比較演算子」も使えます。

比較演算子の種類は、次のとおりです。

比較演算子の種類

  • 「">値"」 :値より大きい
  • 「"<値"」 :値より小さい
  • 「">=値"」:値以上
  • 「"<=値"」:値以下
  • 「"<>値"」:値以外
  • 「""」  :空白
  • 「"<>"」 :空白以外

「SumIf関数」で比較演算子を使ってみます。

「SumIf関数」で比較演算子を使う

「3以下」の値の合計値を算出してみます。

Sub TEST5()
    
    '「3以下」の価格の合計
    Cells(2, "D") = WorksheetFunction.SumIf(Range("A:A"), "<=3", Range("B:B"))
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

「SumIf関数」で比較演算子を使った結果

「SumIf関数」で比較演算子を使って合計値を算出できました。

「SumIfs関数」で比較演算子を使う

「SumIfs関数」で比較演算子を使ってみます。

「2021/8/1」~「2021/9/3」の間の合計値を算出してみます。

Sub TEST6()
    
    '「2021/8/1」~「2021/9/3」の売上を合計
    Range("D2") = WorksheetFunction.SumIfs(Range("B:B"), Range("A:A"), ">=2021/8/1", Range("A:A"), "<=2021/9/3")
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

「SumIfs関数」で比較演算子を使った結果

「SumIfs関数」で比較演算子を使って合計値を算出できました。

最終行まで繰り返し使う

最終行まで繰り返し使うには、「End」で最終行を取得してループします。

「End」を使ってループする

「End」を使って最終行を取得して、ループしてみます。

Sub TEST7()
    
    '最終行までループする
    For i = 2 To Cells(Rows.Count, "D").End(xlUp).Row
        With Cells(i, "E")
            '合計値を算出
            .Value = WorksheetFunction.SumIf(Range("A:A"), .Offset(0, -1), Range("B:B"))
        End With
    Next
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

「End」を使ってSumIf関数を最終行までループした結果

「End」を使ってSumIf関数を最終行までループできました。

最終行までループする方法の詳細

「SumIf関数」を使って最終行までループする方法について、詳細な手順はこちらでまとめています。

別シートを参照する

別シートを参照するには、「Worksheets」を使います。

「別シート」を参照する

SumIf関数で、「別シート」を参照してみます。

Sub TEST8()
    
    '別シートの合計値を算出
    With Worksheets("Sheet2")
        Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
    End With
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

Worksheetsを使って「別シート」を参照した結果

Worksheetsを使って「別シート」を参照できました。

すべての別シートをループする

「For Each」やシート数を「Worksheets.Count」で取得すると、すべての別シートを参照することができます。

すべての別シートを「SumIf関数」で参照する方法について、詳細はこちらでまとめています。

別ブックを参照する

別ブックを参照するには、「Workbooks」を使います。

「別ブック」を参照する

SumIf関数で、「別ブック」を参照してみます。

Sub TEST9()
    
    '「別ブック」を参照して条件一致の合計値を算出
    With Workbooks("TEST.xlsx").Worksheets("Sheet1")
        ThisWorkbook.Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
    End With
    
End Sub

「別ブック」を「同じフォルダ」に保存しています。

別ブックを同じフォルダに保存している

別ブックを参照するときのポイントは、「別ブックを開いておく」ということです。

別ブックを開いておく

では、VBAコードを実行してみます。

Workbooksを使って「別ブック」を参照した結果

Workbooksを使って「別ブック」を参照できました。

別ブックを「開いて」参照する方法

別ブックを参照する場合は、「別ブックを開く」VBAコードを追加して、参照すると便利です。

別ブックを「開いて」参照する方法について、詳細はこちらでまとめています。

埋め込み数式でSumIfやSumifs関数を使う

埋め込み数式でSumIfやSumifs関数を使ってみます。

数式をセルに入力することで、SumIfやSumIfs関数を使うことができます。

条件に一致した値の合計値を算出(SumIf関数)

SumIf関数を埋め込んで、条件に一致した値の合計値を算出してみます。

数式を埋め込んで「SumIf関数」を使う

数式を埋め込んで「SumIf関数」を使うVBAコードは、こんな感じになります。

Sub TEST10()
    
    '「"B"」に一致した価格の合計
    Cells(2, "D") = "=SUMIF(A:A,""B"",B:B)"
    Cells(2, "D").Value = Cells(2, "D").Value '値に変換
    
End Sub

ポイントは、「"B"」は、「""B""」のように「""」で囲むということです。

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

数式を埋め込んで「SumIf関数」を使った結果

数式を埋め込んで「SumIf関数」を使って合計値を算出できました。

「SumIf関数」で「変数」や「セル」の値を使って参照する

実務では、「変数」や「セル」の値を参照して、「SumIf関数」を使うことが多いです。

「変数」や「セル」の値を使って、「SumIf関数」を使う方法について、詳細はこちらでまとめています。

複数条件に一致した値の合計値を算出(SumIfs関数)

次は、「SumIfs関数」を使って、「複数条件」に一致した値の合計値を算出してみます。

数式を埋め込んで「SumIfs関数」を使う

数式を埋め込んで「SumIfs関数」を使うVBAコードは、こんな感じです。

Sub TEST11()
    
    '商品が「B」で、支店が「大阪」の価格を合計
    Range("E2") = "=SUMIFS(C:C,A:A,""B"",B:B,""大阪"")"
    Range("E2").Value = Range("E2").Value '値に変換
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

数式を埋め込んで「SumIfs関数」を使った結果

数式を埋め込んで「SumIfs関数」を使って合計値を算出できました。

「SumIfs関数」で「変数」や「セル」の値を使って参照する

実務では、「変数」や「セル」の値を参照して、「SumIfs関数」を使うことが多いです。

「変数」や「セル」の値を使って、「SumIfs関数」を使う方法について、詳細はこちらでまとめています。

ワイルドカードを使う

数式を埋め込む方法で、SumIfやSumIfsに「ワイルドカード」を使ってみます。

ワイルドカードの種類は、2つあります。

ワイルドカードの種類

  • 「*」:すべての値
  • 「?」:すべての値。ただし1文字

では、埋め込みの「SumIf関数」でワイルドカードを使ってみます。

埋め込みの「SumIf関数」でワイルドカードを使う

A列に「Bを含む」値の合計を算出してみます。

Sub TEST12()
    
    '「"B"」を含む価格の合計
    Cells(2, "D") = "=SUMIF(A:A,""*B*"",B:B)"
    Cells(2, "D").Value = Cells(2, "D").Value '値に変換
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

埋め込みの「SumIf関数」でワイルドカードを使った結果

埋め込みの「SumIf関数」でワイルドカードを使って合計値を算出できました。

埋め込みの「SumIfs関数」でワイルドカードを使う

埋め込みの「SumIfs関数」でワイルドカードを使ってみます。

A列に「Bを含む」セルで、B列が「大阪」であるセルの合計値を算出します。

Sub TEST13()
    
    '商品が「Bを含む」値で、支店が「大阪」の価格を合計
    Range("E2") = "=SUMIFS(C:C,A:A,""B*"",B:B,""大阪"")"
    Range("E2").Value = Range("E2").Value '値に変換
        
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

埋め込みの「SumIfs関数」でワイルドカードを使った結果

埋め込みの「SumIfs関数」でワイルドカードを使って合計値を算出できました。

比較演算子を使う

SumIfやSumIfsで「比較演算子」を使ってみます。

比較演算子の種類は、次のとおりです。

比較演算子の種類

  • 「">値"」 :値より大きい
  • 「"<値"」 :値より小さい
  • 「">=値"」:値以上
  • 「"<=値"」:値以下
  • 「"<>値"」:値以外
  • 「""」  :空白
  • 「"<>"」 :空白以外

埋め込みの「SumIf関数」で比較演算子を使ってみます。

埋め込みの「SumIf関数」で比較演算子を使う

A列が「3以下」の値の合計値を算出してみます。

Sub TEST14()
    
    '「3以下」の価格の合計
    Cells(2, "D") = "=SUMIF(A:A,""<=3"",B:B)"
    Cells(2, "D").Value = Cells(2, "D").Value '値に変換
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

埋め込みの「SumIf関数」で比較演算子を使った結果

埋め込みの「SumIf関数」で比較演算子を使って合計値を算出できました。

埋め込みの「SumIfs関数」で比較演算子を使う

次は、埋め込みの「SumIfs関数」で比較演算子を使ってみます。

「2021/8/1」~「2021/9/3」の値の合計値を算出してみます。

Sub TEST15()
    
    '「2021/8/1」~「2021/9/3」の売上を合計
    Range("D2") = "=SUMIFS(B:B,A:A,"">=2021/8/1"",A:A,""<=2021/9/3"")"
    Range("D2").Value = Range("D2").Value '値に変換
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

埋め込みの「SumIfs関数」で比較演算子を使った結果

埋め込みの「SumIfs関数」で比較演算子を使って合計値を算出できました。

最終行までの合計値を算出する

SumIfを埋め込む方法で、最終行までの合計値を算出してみます。

最終行まで合計値を算出する

数式埋め込みの場合は、ループすることなく、一括で数式を入力すると、最終行まで計算できます。

Sub TEST16()
    
    '最終行を取得
    A = Cells(Rows.Count, "D").End(xlUp).Row
    
    '数式を一括で埋め込む
    Range("E2:E" & A) = "=SUMIF(A:A,D2,B:B)"
    Range("E2:E" & A).Value = Range("E2:E" & A).Value '値に変換
    
End Sub

表を用意しておきます。

表を用意しておきます

では、VBAコードを実行してみます。

Endを使って最終行まで合計値を算出した結果

Endを使って最終行まで合計値を算出できました。

最終行までの合計値を算出する方法の手順

最終行までの合計値を算出する方法の手順について、詳細はこちらでまとめています。

順を追って解説しています。

別シートを参照する

数式埋め込みの「SumIf関数」で、「別シート」を参照してみます。

「別シート」を参照する

数式埋め込みのSumIfで、「別シート」を参照するVBAコードは、こんな感じになります。

Sub TEST17()
    
    '別シートの合計値を算出
    Worksheets("Sheet1").Cells(2, "A") = "=SUMIF(Sheet2!A:A,""B"",Sheet2!B:B)"
    Worksheets("Sheet1").Cells(2, "A").Value = Worksheets("Sheet1").Cells(2, "A").Value '値に変換
    
End Sub

「シート名!」までを入力しています。

一旦、セルに数式を入力して、コピーして使うと簡単に入力できます。

別シートを用意しておきます。

別シートを用意しておきます

では、VBAコードを実行してみます。

埋め込み数式で「別シート」を参照した結果

埋め込み数式で「別シート」を参照できました。

すべてのシートを参照する

実務では1つのシートだけではなく、複数のシートを参照することが多いです。

SumIf関数の埋め込み数式で、すべてのシートをループして参照することができます。

埋め込み数式ですべてのシートをループして参照する方法について、詳細はこちらでまとめています。

別ブックを参照する

数式埋め込みの「SumIf関数」で、「別ブック」を参照してみます。

「別ブック」を参照する

「別ブック」を参照するVBAコードは、こんな感じです。

Sub TEST18()
    
    '「別ブック」を参照して条件一致の合計値を算出
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells(2, "A") = "=SUMIF([TEST.xlsx]Sheet1!A:A,""B"",[TEST.xlsx]Sheet1!B:B)"
        .Cells(2, "A").Value = .Cells(2, "A").Value '値に変換
    End With
    
End Sub

「[TEST.xlsx]Sheet1!A:A」というように、「ブック名」までを入力します。

一旦、セルに入力して、コピーして使うと簡単に入力することができます。

同じフォルダ内に別ブックを保存しています。

同じフォルダ内に別ブックを保存しています

SumIf関数で別ブックを参照するときのポイントは、「別ブックを開いておく」、ということです。

別ブックを開いておきます。

別ブックを開いておきます

では、VBAコードを実行してみます。

埋め込み数式で「別ブック」を参照した結果

埋め込み数式で「別ブック」を参照できました。

別ブックを「開いて」参照する方法と「閉じたまま」参照する

別ブックを参照する際は、「ブックを開く」VBAコードを追加すると便利です。

「Sum関数」と「If関数」を組み合わせると閉じたまま参照することもできます。

別ブックを「開いて」参照する方法と「閉じたまま」参照する方法について、詳細はこちらでまとめています。

Dictionaryで高速化する

SumIfやSumIfs関数は、正直遅いです。

SumIfやSumIfsの機能を高速化するには、「Dictionary」が使えます。

SumIf関数を高速化する

「SumIf関数」を「Dictionary」で高速化してみます。

「SumIf関数」を高速化する

「SumIf関数」を高速化するVBAコードです。

手順は、

  • 検索元と検索先の値を配列に入力
  • 検索元を辞書に登録
  • 検索先の値をループして辞書に合計値を加算していく
  • 合計値の結果をセルに入力

という感じです。

Sub TEST19()
    
    '辞書を作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim B, C
    B = Range("A2:A4") '検索元の値を配列に入力
    C = Range("D2:E10") '検索先の値を配列に入力
    
    '検索元をループ
    For i = 1 To UBound(B, 1)
        '辞書に登録する
        A.Add B(i, 1), 0
    Next
    
    '検索先をループ
    For i = 1 To UBound(C, 1)
        '辞書に登録されている場合
        If A.exists(C(i, 1)) = True Then
            '合計値を算出
            A(C(i, 1)) = A(C(i, 1)) + C(i, 2)
        End If
    Next
    
    'セルに配列を入力
    Range("B2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
    
End Sub

大量データを用意しておきます。

大量データを用意しておきます

では、VBAコードを実行してみます。

「SumIf関数」を高速化した結果

SumIf関数で、大量データの合計値を算出できました。

かかった時間は、「0.19 秒」となりました。

SumIf関数をWorksheetFunctionや数式埋め込みの方法でやると、「20 秒以上」かかってしまうので、かなり高速に計算できます。

「Dictionary」でSumIfを高速化する手順の解説

「Dictionary」を使ってSumIfの機能を高速化する手順について、詳細はこちらで解説しています。

SumIfs関数を高速化する

「SumIfs関数」を「Dictionary」を使って高速化してみます。

「SumIfs関数」を高速化する

「SumIfs関数」を高速化するVBAコードです。

手順は、

  • 検索元と検索先の値を配列に入力
  • 検索元を辞書に登録(区切り文字で結合する)
  • 検索先の値をループして辞書に合計値を加算していく
  • 合計値の結果をセルに入力

という感じです。

Sub TEST20()
    
    '辞書を作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim B
    B = Range("A2:B4") '参照元のデータ
    
    '辞書に登録していく
    For i = 1 To UBound(B, 1)
        '「商品」と「支店」を「"/"」で区切って登録
        A.Add B(i, 1) & "/" & B(i, 2), 0
    Next
    
    Dim C
    C = Range("E2:G10") '参照先のデータ
    
    '参照先のループ
    For i = 1 To UBound(C, 1)
        '既に登録されている場合
        If A.exists(C(i, 1) & "/" & C(i, 2)) = True Then
            '合計値を加算していく
            A(C(i, 1) & "/" & C(i, 2)) = A(C(i, 1) & "/" & C(i, 2)) + C(i, 3)
        End If
    Next
    
    'セルに配列を入力
    Range("C2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
    
End Sub

大量データを用意しておきます。

大量データを用意しておきます

では、VBAコードを実行してみます。

「SumIfs関数」を高速化した結果

「SumIfs関数」を大量データで条件一致する合計値を算出できました。

かかった時間は、「0.23 秒」です。

SumIfs関数を、WorksheetFunctionや数式埋め込みの方法で使うと、「17 秒以上」かかってしまうので、かなり高速に合計値を計算できています。

「Dictionary」でSumIfsを高速化する手順の解説

「Dictionary」を使ってSumIfsの機能を高速化する手順について、詳細はこちらで解説しています。

おわりに

この記事では、VBAで「SumIf関数」と「SumIfs関数」を使う方法について、ご紹介しました。

SumIfやSumIfs関数を使う方法は、「WorksheetFunction」と「数式を埋め込む方法」があります。

合計した値をVBAで使う場合は、「WorksheetFunction」が便利です。

セルに合計値をそのまま入力したいのであれば、「数式を埋め込む方法」が簡単です。

VBAで「SumIf」や「SumIfs」を使いこなしていきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す