ワークシート関数を使用するメリット

ワークシート関数とは、その名前の通りワークシート上で使える Sum や Count などの関数のことです。詳細な使用方法は次ページ以降に記載しますが、これらの関数はVBAから使用することができます。

例えば、下図のようなワークシート上にSUM関数があるとします。

この関数をVBAから使用するには以下のようなコードになります。

total = WorksheetFunction.Sum(Range("B4:B13"))

ワークシート関数の処理速度

Sum や Count 程度の集計処理なら、わざわざワークシート関数を使用しなくてもいいと思うかもしれませんが、注目すべき点はその処理の速さです。管理人が2013年当時所持していたノートパソコン（Core2Duo、Win7）ではワークシート関数を使用した方が数十倍はやく処理が終わりました。

処理時間の計測方法とその結果を以下に記載します。

処理時間計測方法

乱数を100万データ用意し、その合計を求める時間を計測しました。合計する方法は単純に合計する方法と条件付きで合計する方法の２種類で、それぞれループによる合計処理とワークシート関数による合計処理２種類、すなわち2×2=4種類のテストを実施します。使用するワークシート関数は Sum と SumIf です。処理時間計測にはTimer関数を使用しました。

データの準備

ワークシートの1列目に0～1未満の乱数を100万データ用意しておきます。乱数の生成方法についてはRnd関数 をご覧ください。

処理時間計測テスト1

ループ処理で逐次合計します。

Sub test_loop_sum()
    Dim t As Single
    Dim MaxDataCnt As Long
    Dim total As Double
    Dim i As Long

    t = Timer   '時間計測開始
    MaxDataCnt = 1000000
    total = 0
    For i = 1 To MaxDataCnt
        total = total + CDbl(Cells(i, 1).Value)
    Next i

    MsgBox "合計値 = " & total & vbLf & _
           "処理時間は " & Round(Timer - t, 2) & " 秒でした。"
End Sub

  For Each…Next  MsgBox関数  データ型変換関数

処理時間計測テスト2

ワークシート関数を使用して合計します。

Sub test_wsf_sum()
    Dim t As Single
    Dim MaxDataCnt As Long
    Dim total As Double

    t = Timer   '時間計測開始
    MaxDataCnt = 1000000
    total = WorksheetFunction.Sum(Range("A1:A" & MaxDataCnt))

    MsgBox "合計値 = " & total & vbLf & _
           "処理時間は " & Round(Timer - t, 2) & " 秒でした。"
End Sub

 合計処理 (Sum, SumIf, SumIfs)   データ型変換関数

処理時間計測テスト3

ループ処理で0.5より大きい値を逐次合計します。

Sub test_loop_sumif()
    Dim t As Single
    Dim MaxDataCnt As Long
    Dim total As Double
    Dim i As Long
    Dim d As Double

    t = Timer   '時間計測開始
    MaxDataCnt = 1000000
    total = 0
    For i = 1 To MaxDataCnt
        d = CDbl(Cells(i, 1).Value)
        '値が0.5より大きいものを集計
        If d > 0.5 Then
            total = total + d
        End If
    Next i

    MsgBox "合計値 = " & total & vbLf & _
           "処理時間は " & Round(Timer - t, 2) & " 秒でした。"
End Sub

  For Each…Next  MsgBox関数  データ型変換関数

処理時間計測テスト4

ワークシート関数を使用して0.5より大きい値を合計します。

Sub test_wsf_sumif()
    Dim t As Single
    Dim MaxDataCnt As Long
    Dim total As Double

    t = Timer   '時間計測開始
    MaxDataCnt = 1000000
    '値が0.5より大きいものを集計
    total = WorksheetFunction.SumIf(Range("A1:A" & MaxDataCnt), ">0.5")

    MsgBox "合計値 = " & total & vbLf & _
           "処理時間は " & Round(Timer - t, 2) & " 秒でした。"
End Sub

 合計処理 (Sum, SumIf, SumIfs)   データ型変換関数

処理時間計測結果

時間計測はそれぞれのテストで5回行いその平均値を記載しています。ワークシート関数の方が数十～百倍もはやいという驚きの結果になりました。

合計方法	合計所要時間	条件付き合計所要時間
ループによる逐次処理	【テスト1】5.89 秒	【テスト3】5.93 秒
ワークシート関数	【テスト2】0.03 秒	【テスト4】0.16 秒

以上の結果から、ワークシート関数はとても高速であることがわかりました。上記のテストプロシージャを見てわかるように、同じ合計処理でもループ制御文が必要ないため、コードの行数が少なくてすみます。また、集計処理以外にも、統計や検索など便利な関数が数多くあります。ワークシート関数を使用すると、このようなメリットがありますので、ぜひ覚えておくとよいでしょう。

次ページ以降では数あるワークシート関数の中から集計や統計などに関するものをピックアップし、簡単な使用例を挙げながらその使い方を説明していきたいと思います。