大体でIT

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

大体でIT

Excel VBAで、SumIf関数を使って「別シート」を参照する方法について、ご紹介します。別シートを参照する場合は「Worksheets」を使います。別シートのセルを参照する場合は、「Worksheets(シート名).セル」で参照します。すべてのシートをループする場合は、「シート番号でループ」したり「For Each」を使います。

はじめに

この記事では、SumIf関数で別シートを参照する方法について、ご紹介します。

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

「別シートのセル」を参照するには、「Worksheets(シート名).セル」とします。

「全シートをループ」する場合は、シート番号を使って「Worksheets(シート番号)」をループします。

入力するシートの位置が変わってしまう場合は、「For Each」を使ってシートをすべてループすると便利です。

SumIf関数を使う方法として、「WorksheetFunction」と「数式を埋め込む方法」がありますので、状況に応じて簡単な方法を選択しましょう。

では、SumIf関数で別シートを参照する方法について、解説していきます。

この記事で紹介すること

  • VBAでSumIf関数を使って「別シート」を参照する方法

目次から見たい項目へ移動すると便利ですよ。

目次

VBAコードまとめ

SumIf関数で別シートを参照する方法について、VBAコードをまとめています。

VBAコードだけを確認したい場合に、ご活用ください。

'WorksheetFunctionで別シートを参照
With Worksheets("Sheet2")
    Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
End With
    
'数式埋め込みで別シートを参照
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

では、解説していきます。

WorksheetFunctionを使ってSumIf関数で別シートを参照する

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

「Worksheets」を指定して別シートを参照する

別シートのセルを「Worksheets」を指定して参照します。

参照したいシートを「Worksheets("Sheet2")」のように指定すると、別シートを参照できます。

別シートを参照

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

ちょっと長いので、「With」でくくっておきます。

「With」でくくる

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

別シートの「Sheet2」を用意しておきます。

別シートを用意

別シートを用意

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

別シートの条件一致したセルの合計

別シートの条件一致したセルの合計

「別シート」の条件一致したセルの合計を計算できました。

シート番号を使って別シートを参照する

Worksheetsは、「シート番号」を入力して使うこともできます。

最初のシートから連番で、「Worksheets(1)」、「Worksheets(2)」というような感じです。

シートを番号を使う

シートを番号を使う

「シート番号」を使って別シートを参照してみます。

シート番号を使って別シートを参照

Sub TEST3()
    
    'シート番号を使って、別シートの合計値を算出
    With Worksheets(2)
        Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
    End With
    
End Sub

参照したいシートが「2番目」なので、「Worksheets(2)」としています。

Sheet2を用意しておきます。

Sheet2を用意する

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

シート番号を使って別シートを参照

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

全シートをループして参照する

全シートをループして参照するには、先ほどとの「シート番号」を使って、ループすることができます。

全シートをループして参照

Sub TEST4()
    
    'シートをループ
    For i = 2 To 5
        '別シートの合計値を算出
        With Worksheets(i)
            Worksheets("Sheet1").Cells(i, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
        End With
    Next
    
End Sub

Sheet1~Sheet5を用意しておきます。

Sheet1~Sheet5を用意

Sheet1~Sheet5を用意1
Sheet1~Sheet5を用意2
Sheet1~Sheet5を用意3
Sheet1~Sheet5を用意4
Sheet1~Sheet5を用意5

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

全シートをループして合計値を参照

全シートをループして合計値を参照

「全シート」をループして合計値を参照できました。

「最終シート」を取得してループ

全シートをループしたい場合は、「最終シート」を取得してループすると便利です。

Sub TEST5()
    
    '最終シートまでループ
    For i = 2 To Worksheets.Count
        '別シートの合計値を算出
        With Worksheets(i)
            Worksheets("Sheet1").Cells(i, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
        End With
    Next
    
End Sub

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

「最終シート」を取得してループ

「最終シート」を取得してループできました。

「For Each」を使ってループ

「For Each」を使ってループすることもできます。

「Worksheets」をコレクションにして、全シートをループすることができます。

Sub TEST6()
    
    i = 1
    'すべてのシートをループ
    For Each a In Worksheets
        'Sheet1のシートではないとき
        If a.Name <> "Sheet1" Then
            i = i + 1 'カウントアップ
            '別シートの合計値を算出
            Worksheets("Sheet1").Cells(i, "A") = WorksheetFunction.SumIf(a.Range("A:A"), "B", a.Range("B:B"))
        End If
    Next
    
End Sub

入力するシートが移動してしまっても、別シートをうまく参照することができます。

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

「For Each」を使ってループ

「For Each」を使って全シートをループできました。

埋め込み数式を使ってSumIf関数で別シートを参照

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

埋め込み数式で別シートを参照

埋め込み数式で別シートを参照するには、「Sheet2!A:A」のように、シート名まで含めてセルを参照するとできます。

Sub TEST7()
    
    '別シートの合計値を算出
    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

数式ではなく、値を入力したいので、値に変換しています。

Sheet2を用意しておきます。

Sheet2を用意

Sheet2を用意

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

別シートを参照して合計値を取得

別シートを参照して合計値を取得

「別シート」を参照して合計値を取得できました。

セルに数式を入力してコピーして使う

数式を埋め込みたい場合は、セルに数式を入力して、「数式をコピーして使う」と簡単に入力できます。

セルに入力した数式をコピーする

セルに入力した数式をコピーする

セルに数式を入力して、値が取得できれば、そのまま数式をコピーします。

全シートをループして参照する

埋め込みの数式を使って、全シートをループする場合は、「文字列」でシートを含んだセル範囲を作成します。

全シートをループして参照する

シーの名前を「Worksheets(i).Name」で取得するところがポイントです。

Sub TEST8()
    
    'シートをループ
    For i = 2 To 5
        a = Worksheets(i).Name 'シートの名前
        '別シートの合計値を算出
        Worksheets("Sheet1").Cells(i, "A") = "=SUMIF(" & a & "!A:A,""B""," & a & "!B:B)"
        Worksheets("Sheet1").Cells(i, "A").Value = Worksheets("Sheet1").Cells(i, "A").Value '値に変換
    Next
    
End Sub

Sheet1~Sheet5を用意しておきます。

Sheet1~Sheet5を用意

Sheet1~Sheet5を用意1
Sheet1~Sheet5を用意2
Sheet1~Sheet5を用意3
Sheet1~Sheet5を用意4
Sheet1~Sheet5を用意5

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

全シートを参照して合計値を取得

全シートを参照して合計値を取得

全シートを参照して「"B"」に一致するセルの合計値を、取得できました。

「最終シート」を取得してループ

「For」を使って、全シートをループする場合は、「最終行」を取得が必要です。

最終行を「Worksheets.Count」で取得してループします。

Sub TEST9()
    
    '最終シートまでループ
    For i = 2 To Worksheets.Count
        a = Worksheets(i).Name
        '別シートの合計値を算出
        Worksheets("Sheet1").Cells(i, "A") = "=SUMIF(" & a & "!A:A,""B""," & a & "!B:B)"
        Worksheets("Sheet1").Cells(i, "A").Value = Worksheets("Sheet1").Cells(i, "A").Value '値に変換
    Next
    
End Sub

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

「最終シート」を取得してループ

「最終シート」を取得してループできました。

「For Each」を使ってループ

「For Each」を使ってループすることもできます。

「Worksheets」をコレクションとして、全シートをループします。

入力シートをループした場合は、スキップするようにします。

Sub TEST10()
    
    i = 1
    '最終シートまでループ
    For Each a In Worksheets
        'Sheet1ではないとき
        If a.Name <> "Sheet1" Then
            i = i + 1 'カウントアップ
            '別シートの合計値を算出
            Worksheets("Sheet1").Cells(i, "A") = "=SUMIF(" & a.Name & "!A:A,""B""," & a.Name & "!B:B)"
            Worksheets("Sheet1").Cells(i, "A").Value = Worksheets("Sheet1").Cells(i, "A").Value '値に変換
        End If
    Next
    
End Sub

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

「For Each」を使ってループ

「For Each」を使って全シートをループできました。

おわりに

この記事では、SumIf関数で別シートを参照する方法について、ご紹介しました。

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

別シートのセルを参照するには、「Worksheets(シート名).セル」とします。

全シートをループする場合は、シート番号を使って「Worksheets(シート番号)」をループします。

入力するシートの位置が変わってしまう場合は、「For Each」を使ってシートをすべてループすると便利です。

SumIf関数を使う方法として、「WorksheetFunction」と「数式を埋め込む方法」がありますので、状況に応じて簡単な方法を選択しましょう。

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

関連する記事から探す

カテゴリから探す

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

アーカイブから探す