大体でIT

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

HOME >  Excel VBA >  ワークシート関数や数式を使う
大体でIT

Excel VBAで、SumIf関数を使って「別ブック」を参照する方法について、ご紹介します。別ブックを参照する場合は「Workbooks」を使います。SumIf関数で別ブックを参照するには、基本的には、「別ブックを開いておく」必要があります。SUMとIfを配列数式で組み合わせると、「別ブックを閉じた状態」で参照できるので、併せてご紹介します。

はじめに

この記事では、VBAでSumIf関数を使って、別ブックを参照する方法について、ご紹介します。

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

別ブックのセルを参照する場合は、「Workbooks(ブック名.xlsx).Worksheets(シート名).セル」という感じです。

別ブックを参照する際の注意点は、「別ブックを開いておく必要がある」、ということです。

「別ブックを開く」→「別ブックの値を参照」→「別ブックを閉じる」という流れになります。

少しでも高速化したい場合は、「SUMとIf関数を配列数式で組み合わせる」方法で、別ブックを閉じたまま参照しましょう。

では、VBAでSumIf関数を使って、別ブックを参照する方法について、解説していきます。

この記事で紹介すること

  • VBAでSumIf関数を使って「別ブック」を参照する方法

こちらの記事も参考になります

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

目次

VBAコードまとめ

SumIf関数を「別ブック」で使うVBAコードについて、まとめています。

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

'WorksheetFunctionを使って「別ブック」を参照して「条件一致の合計値」を算出（開いておく必要あり）
With Workbooks("TEST.xlsx").Worksheets("Sheet1")
    ThisWorkbook.Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
End With
    
'埋め込み数式を使って「別ブック」を参照して「条件一致の合計値」を算出（開いておく必要あり）
ThisWorkbook.Worksheets("Sheet1").Cells(2, "A") = "=SUMIF([TEST.xlsx]Sheet1!A:A,""B"",[TEST.xlsx]Sheet1!B:B)"

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

WorksheetFunctionを使ってSumIf関数で別ブックを参照する

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

「Workbooks」を指定して別ブックを参照する

「Workbooks」を指定して別ブックを参照します。

「Workbooks("TEST.xlsx").Worksheets("Sheet1")」というように、ブックまで指定するのがポイントです。

Sub TEST1()
    
    '「別ブック」を参照して条件一致の合計値を算出
    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

別ブックを用意しておきます。

別ブックを用意

同フォルダ内に、参照元と参照先のブックを保存しています。

同フォルダ内に保存している

参照する別ブックの「TESXT.xlsx」は開いておきます。

別ブックを開いておく

では、別ブックを参照して、条件一致の合計値を算出してみます。

別ブックを参照して条件一致の合計値を算出

別ブックを参照して条件一致の合計値を算出

別ブックを参照して、条件一致の合計値を算出できました。

ブックを開いておく必要がある

SumIf関数を使う際のポイントは、「ブックを開いておく」ということです。

ブックを閉じた状態では参照することができません。

ちょっとやってみます。

先ほどとのVBAコードです。

Sub TEST2()
    
    '別ブックを開いていないとエラー
    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.Open」で参照したいブックを開きます。

Sub TEST3()
    
    'ブックを開く
    Workbooks.Open ThisWorkbook.Path & "\TEST.xlsx"
    
    '「別ブック」を参照して条件一致の合計値を算出
    With Workbooks("TEST.xlsx").Worksheets("Sheet1")
        ThisWorkbook.Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.SumIf(.Range("A:A"), "B", .Range("B:B"))
    End With
    
    '別ブックを閉じる
    Workbooks("TEST.xlsx").Close
    
End Sub

開いていても邪魔なので、最後に別ブックを閉じます。

別ブックを閉じた状態にしておきます。

別ブックは閉じた状態

別ブックは閉じた状態

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

別ブックが開かれる

別ブックが開かれる

別ブックが開かれました。

別ブックを参照して別ブックを閉じる

別ブックを参照して別ブックを閉じる

別ブックを参照して、条件一致の合計値を計算できました。

別ブックは閉じられています。

埋め込み数式を使ってSumIf関数で別ブックを参照

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

「ブック名」を入力して別ブックを参照

埋め込みの数式内に、「ブック名」を入力すると、別ブックを参照できます。

「[TEST.xlsx]Sheet1!A:A」というような感じです。

Sub TEST4()
    
    '「別ブック」を参照して条件一致の合計値を算出
    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

別ブックを用意しておきます。

別ブックを用意

同フォルダ内に、参照元と参照先のブックを保存しています。

同フォルダ内に保存しておく

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

別ブックを開いておく

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

別ブックを参照した条件一致の合計値を算出

別ブックを参照した条件一致の合計値を算出

「別ブック」を参照して、条件一致の合計値を算出できました。

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

数式を埋め込みたい場合は、一旦、セルに数式を入力して、コピーして使うと便利です。

数式をコピーして利用する

数式をコピーして利用する

VBE画面で直接入力してもできますけども、セルの数式をコピーした方が簡単です。

ブックを開いておく必要がある

埋め込みの数式を使う場合も、「別ブックを開いておく」必要があります。

先ほどとのVBAコードを、別ブックが閉じた状態で実行してみます。

Sub TEST5()
    
    '別ブックを開いていないと、うまく計算できない
    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

別ブックを閉じた状態にしておきます。

別ブックを閉じた状態

別ブックを閉じた状態

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

ブックを閉じた状態だとエラー

ブックを閉じた状態だとエラー

ブックを閉じた状態だと、セルの値が「エラー」となってしまいました。

参照先を設定する用のダイアログボックスも表示されました。

ブックを閉じたまま参照できる関数

ブックを閉じたまま参照できる関数もあります。

次の関数は、閉じたまま別ブックを参照できました。

  • SUM
  • IF
  • COUNTA
  • VLOOKUP
  • MATCH
  • INDEX

別ブックを閉じたままだと、高速に参照することができるので、高速化したい場合はこれらの関数を使うといいです。

ブックを開いて合計値を算出してみる

埋め込み数式を使う場合も、「ブックを開いて」から、別ブックを参照する流れにするといいです。

Sub TEST6()
    
    'ブックを開く
    Workbooks.Open ThisWorkbook.Path & "\TEST.xlsx"
    
    '「別ブック」を参照して条件一致の合計値を算出
    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
    
    'ブックを閉じる
    Workbooks("TEST.xlsx").Close
    
End Sub

別ブックを閉じた状態にしておきます。

別ブックは閉じた状態

別ブックは閉じた状態

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

別ブックが開かれる

別ブックが開かれる

別ブックが開かれました。

別ブックを参照して別ブックを閉じる

別ブックを参照して別ブックを閉じる

別ブックを参照して、条件一致の合計値を算出できました。

別ブックは閉じられています。

SUMとIFと配列数式を使うとブックを閉じても参照できる

「SUMとIFと配列数式」を使うと、「SumIf」と同じ機能で、「別ブックを閉じた」状態で、「別ブック」を参照できます。

ブックを閉じたまま参照する

「SUMとIfと配列数式」で「SumIf関数」を作ると次のようになります。

Sub TEST7()
    
    '「SUM」と「If」を配列数式で、別ブックを「閉じたまま」参照する
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells(2, "A").FormulaArray = "=SUM(IF('C:\TEST\[TEST.xlsx]Sheet1'!A:A=""B"",'C:\TEST\[TEST.xlsx]Sheet1'!B:B))"
        .Cells(2, "A").Value = .Cells(2, "A").Value '値に変換
    End With
    
End Sub

「.FormulaArray」で「配列数式」の形でセルに入力するのがポイントです。

別ブックを閉じた状態にしておきます。

別ブックを閉じた状態にしておく

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

別ブックを閉じた状態で参照できた

別ブックを閉じた状態で参照できました。

数式を作成する方法

最初は、別ブックを開いた状態で、セルに「配列数式」を入力して、参照できることを確認します。

そのあとに、参照先の別ブックを閉じます。

別ブックを開いた状態でセルに数式を入力して、別ブックを閉じる

別ブックを閉じると、別ブックのパスが、自動的にフルパスに変更されます。

別ブックのパスがフルパスになるので数式をコピーして利用する

セルに入力された数式をコピーして、VBAコードに貼り付けて、利用します。

ブックを閉じたまま取得すると高速

ブックを閉じたまま取得したいのは、少し高速になるからです。

  • ブックを開いて参照
  • ブックを閉じたまま参照する

の2つで時間を計測してみます。

ブックを開いて参照

ブックを開いて参照するコードです。

Sub TEST8()
    
    t = Timer
    
    For i = 1 To 10
        
        'ブックを開く
        Workbooks.Open ThisWorkbook.Path & "\TEST.xlsx"
        
        '「別ブック」を参照して条件一致の合計値を算出
        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
        
        'ブックを閉じる
        Workbooks("TEST.xlsx").Close
            
    Next
    
    Debug.Print Timer - t & " 秒"
    
End Sub

ブックを開いて値を取得する方法を、10回繰り返します。

ブックを開いて値を取得する方法を10回繰り返した

別ブックを参照できています。

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

ブックを閉じたまま参照する方法

次は、ブックを閉じたまま参照する方法です。

Sub TEST9()
        
    t = Timer
        
    For i = 1 To 10
        
        '「SUM」と「If」を配列数式で、別ブックを「閉じたまま」参照する
        With ThisWorkbook.Worksheets("Sheet1")
            .Cells(2, "A").FormulaArray = "=SUM(IF('C:\TEST\[TEST.xlsx]Sheet1'!A:A=""B"",'C:\TEST\[TEST.xlsx]Sheet1'!B:B))"
            .Cells(2, "A").Value = .Cells(2, "A").Value '値に変換
        End With
        
    Next
    
    Debug.Print Timer - t & " 秒"
    
End Sub

ブックを閉じたまま参照を、10回繰り返します。

ブックを閉じたまま参照する方法を10回繰り返した

別ブックを参照できています。

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

9倍ぐらい速くなりましたね。

どうしても速くしたい場合は、「SUMとIFと配列数式」を使って、別ブックの参照を高速化する方法を検討してみましょう。

おわりに

この記事では、VBAでSumIf関数を使って、別ブックを参照する方法について、ご紹介しました。

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

別ブックのセルを参照する場合は、「Workbooks(ブック名.xlsx).Worksheets(シート名).セル」という感じです。

別ブックを参照する際の注意点は、「別ブックを開いておく必要がある」、ということです。

「別ブックを開く」→「別ブックの値を参照」→「別ブックを閉じる」という流れになります。

少しでも高速化したい場合は、「SUMとIf関数を配列数式で組み合わせる」方法で、別ブックを閉じたまま参照しましょう。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す