アクセス上位ページ
 
最近の更新内容

Excel VBA ≫ 6.ワークシート関数 - 特徴と使用例(1)-a.ワークシート関数を使用するメリット

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

ワークシート関数とは、その名前の通りワークシート上で使える Sum や Count などの関数のことです。 詳細な使用方法は次ページ以降に記載しますが、これらの関数はVBAから使用することができます。
  • ワークシート上で使用する SUM 関数の例
  • ワークシート上で使用するSUM関数の例
  • VBAで SUM 関数を使用する場合の例
  • total = WorksheetFunction.Sum(Range("B4:B13"))
    




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

処理時間の計測方法とその結果を以下に記載します。
  • 処理時間計測方法
  • 乱数を100万データ用意し、その合計を求める時間を計測しました。 合計する方法は単純に合計する方法と条件付きで合計する方法の2種類を試します。 この計算に対応するワークシート関数は Sum と SumIf です。 処理時間計測にはTimer関数(時間計測精度は約16ミリ秒)を使用しました。
    Macintosh の場合、Timer 関数は小数点以下の数値を返しません。
    (1秒単位でしか計測できないことを意味します。)
    • データの準備
    • ワークシートの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 "処理時間は " & Round(Timer - t, 2) & " 秒でした。"
      End Sub
      

    • 【テスト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 "処理時間は " & Round(Timer - t, 2) & " 秒でした。"
      End Sub
      
    • 【テスト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 "処理時間は " & Round(Timer - t, 2) & " 秒でした。"
      End Sub
      
    • 【テスト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 "処理時間は " & Round(Timer - t, 2) & " 秒でした。"
      End Sub
      

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


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


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

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

カテゴリ内ページ



Page
Top