大体でIT

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

大体でIT

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

はじめに

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

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

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

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

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

では、VBAでCountIf関数を使って「別シート」を参照する方法について、解説していきます。

この記事で紹介すること

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

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

目次

VBAコード

'WorksheetFunctionで別シートを参照
With Worksheets("Sheet2")
    Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.CountIf(.Range("A:A"), "B")
End With
    
'数式埋め込みで別シートを参照
Worksheets("Sheet1").Cells(2, "A") = "=COUNTIF(Sheet2!A:A,""B"")"

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

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

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

別シートを参照するには次のように「Worksheets」を使ってシートを指定します。

別シートを参照

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

少し長いので、「With」でくくります。

「With」でくくる

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

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

別シートを用意

別シートを用意

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

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

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

結果は、「3」個となりました。

別シートの条件一致したセルの数を取得できました。

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

「Worksheets」に番号を入力すると、「シート番号」を使って別シートを参照できます。

例えば、「1番目のシート」は「Worksheets(1)」で、「2番目のシート」は「Worksheets(2)」となります。

シートを番号を使う

シートを番号を使う

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

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

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

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

別シートを用意

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

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

結果は、「3」個となりました。

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

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

全シートを参照するには、シート番号を変数として、ループします。

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

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

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

Sheet1~Sheet5を用意

Sheet1~Sheet5を用意
Sheet1~Sheet5を用意
Sheet1~Sheet5を用意
Sheet1~Sheet5を用意
Sheet1~Sheet5を用意

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

全シートを参照してカウント

全シートを参照してカウント

全シートを参照して「"B"」の数をカウントできました。

「シートの数」を取得してループ

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

シートの数は「Worksheets.Count」で取得できます。

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

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

「シートの数」を取得してループ

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

「For Each」を使ってループ

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

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

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

「For Each」を使ってループ

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

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

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

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

埋め込み数式で別シートを参照して、「"B"」の数をカウントしてみます。

ポイントは、「"B"」を「"」で囲んで「""B""」とするところです。

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

数式ではなく値にしたいので、最後に値に変換しています。

数式のままでいい場合は、値へ変換するVBAコードは必要ありません。

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

別シートを用意

別シートを用意

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

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

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

結果は、「3」個となりました。

別シートの条件一致したセルの数をカウントできています。

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

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

数式をコピーして使う

数式をコピーして使う

この数式をコピーして使いましょう。

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

埋め込み数式を使って、「全シート」をループして参照してみます。

埋め込み数式の場合は「シート名」が必要なので、「Worksheets(i).Name」で「シート名」を取得して、CountIf関数の中に埋め込みます。

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

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

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

Sheet1~Sheet5を用意

Sheet1~Sheet5を用意
Sheet1~Sheet5を用意
Sheet1~Sheet5を用意
Sheet1~Sheet5を用意
Sheet1~Sheet5を用意

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

全シートを参照してカウント

全シートを参照してカウント

全シートを参照して「B」の数をカウントできました。

「シートの数」を取得してループ

全シートをループする場合は、「Worksheets.Count」で「シートの数」を取得して使います。

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

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

「シートの数」を取得してループ

「シートの数」を取得して全シートを参照できました。

「For Each」を使ってループ

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

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

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

「For Each」を使ってループ

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

おわりに

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

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

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

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

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

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

関連する記事から探す

カテゴリから探す

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

アーカイブから探す