どっちが速い?エクセルの自作関数

 見にきてくれて、ありがとうございます。石川さんです。

先日、仕事をしていたら、実行時エラーの原因となった関数に再び出会いました。このときに参考までに作った関数と比較して、はて、これってどっちの関数が速いのでしょうか、と、言うのが疑問に浮かびましたので、実測してみます!

紹介した関数

先日紹介した自作関数(Number2Letter)は以下の通り、エクセルのセルのAddress関数を使用して求めていました。

'*****************************************************************
'*関数名 :Number2Letter
'*機能概要:入力されたカラム位置の数値からカラム文字の英数字に変換します
'*引数   :カラム位置(1~16384)
'*戻り値 :カラム文字
'*****************************************************************
Function Number2Letter(iCol As Integer) As String
    Number2Letter = Split(Columns(iCol).Address(True, False), ":")(0)
End Function

そして、その時にぱっと作ったのが以下の関数です。名前が重複しないよう「2」を付けました。

Function Number2Letter2(iCol As Integer) As String
    If iCol < 1 Then Number2Letter2 = "": Exit Function
    Number2Letter2 = Number2Letter2(Int((iCol - 1) / 26)) & Chr(Asc("A") + ((iCol - 1) Mod 26))
End Function

なんと、再帰関数を使っております。かっこいい!あ、自画自賛になってしまった。。。

念のためテスト

 このポストを書いているうちに、作った関数は正しいのかな、という疑念が湧いてきたので、まずはテストしてみます。テストスクリプトと結果は以下の通りです。ちなみに、16384はエクセルの列番号の上限です。

Sub test()
    Dim i As Integer
    Debug.Print "チェックを開始します。"
    For i = 1 To 16384
        If Number2Letter(i) <> Number2Letter2(i) Then
            Debug.Print "エラーです!", Number2Letter(i), Number2Letter2(i)
            Exit Sub
        End If
    Next i
    Debug.Print "エラーは無かったようです。"
End Sub
チェックを開始します。
エラーは無かったようです。

このとおり、特に問題はありませんでした。

では、実測します

 はい、色々とああでもないこうでもないと言っている時間がもったいないので、まずは実測してみます。秒未満の時間を測る方法を失念したため、ググってこちらを参考にさせていただきました。今は見られませんが、会社に置いてある書籍にはGetTickCountが紹介されていたように思います。GetTickCountは1ミリ秒、TimerはSingle型の場合は0.01秒(10ミリ秒)でDouble型の場合は0.00000001秒(10ナノ秒)なので、何となくTimerをDoubleにして使うのがよさそうです。(と、思ってTimerをDoubleにして使ってみた結果からは10進数でいくと50ナノ秒が最小で間違いなかったと思ったのですが、よく見ると実際は、内部的に2進数を利用しているため、2^-8(0.00390625)が最小のようです。もしGetTickCountがもっと細かく出力されるのであれば、ミリ秒のGetTickCountを使うのが一番よかったのかも知れませんね。ただ、まあ誤差ですよねぇ。)

トライアンドエラーで最終的に、実測用のスクリプトと実行結果は以下のとおりになりました。さて、どちらが速いでしょうか?

Sub 計測()
    Dim start, diff, total As Double
    Dim i As Integer, j As Integer
    Debug.Print "##### テスト1計測開始 #####"
    total = 0
    For j = 1 To 10
        start = Timer
        For i = 1 To 16384
            buf = Number2Letter(i)
        Next i
        diff = Timer - start
        Debug.Print "time : " & Format(diff, "0.00000000") & "秒"
        total = total + diff
    Next j
    Debug.Print "Total : " & total & "秒"
      
    Debug.Print
    
    Debug.Print "##### テスト2計測開始 #####"
    total = 0
    For j = 1 To 10
        start = Timer
        For i = 1 To 16384
            buf = Number2Letter2(i)
        Next i
        diff = Timer - start
        Debug.Print "time : " & Format(diff, "0.00000000") & "秒"
        total = total + diff
    Next j
    Debug.Print "Total : " & total & "秒"
End Sub
##### テスト1計測開始 #####
time : 0.07812500秒
time : 0.07421875秒
time : 0.07031250秒
time : 0.07031250秒
time : 0.07421875秒
time : 0.08593750秒
time : 0.07812500秒
time : 0.07812500秒
time : 0.07031250秒
time : 0.07812500秒
Total : 0.7578125秒

##### テスト2計測開始 #####
time : 0.01953125秒
time : 0.01953125秒
time : 0.01562500秒
time : 0.02343750秒
time : 0.02343750秒
time : 0.01562500秒
time : 0.01562500秒
time : 0.01953125秒
time : 0.01953125秒
time : 0.01953125秒
Total : 0.19140625秒

結果にはまあまあの誤差があるように思えたので1~16384の変換をそれぞれ10回ずつ実行してみました。トータルで、およそ4倍の差があることになりました。4倍というと大きな差の用に感じますが、16万回実行して、0.56640625秒の差、ということなので、データ量によりますが、ほとんどのケースでは誤差と考えても問題なさそですね。

アドレスの指定方法による差はどうでしょうか

 今回の実験をする前に、仕事のマクロを実行していた時に、処理に関係のないエクセルファイルを4つほど開いて実行したところ、処理が結構遅くなった事件がありました。もしかしたらオブジェクトがどのオブジェクトを差しているのかを調べるのに時間がかかっているのかも知れない、ということで、今回の関数で使用している「Columns」に着目しました。

実は、この「Columns」は、Sheet1.Columnsのことでした。そこで、今回の続きとして、Sheet1を記載した場合としなかった場合の差を計測してみたいと思います。

? Columns.Parent.name ' ? は、debug.Print と同義
Sheet1
? Sheet1.Parent.name
どっちが速い?.xlsx

親の要素名を記載して実行時間を計測してみます

 ワークシートを指定するようにして、先ほどのNumber2LetterをNumber2Letter3として作成して同様に実行結果を比較してみました。計測2は、ミリ秒単位で計測するよう修正しました。さて、結果や如何に?

Function Number2Letter3(iCol As Integer) As String
    Number2Letter3 = Split(Sheet1.Columns(iCol).Address(True, False), ":")(0)
End Function
Sub 計測2()
    Dim start, diff, total As Double
    Dim i As Integer, j As Integer
    Debug.Print "##### テスト3計測開始 #####"
    total = 0
    For j = 1 To 10
        start = GetTickCount()
        For i = 1 To 16384
            buf = Number2Letter(i)
        Next i
        diff = GetTickCount() - start
        Debug.Print "time : " & Format(diff, "####") & "ミリ秒"
        total = total + diff
    Next j
    Debug.Print "Total : " & total & "ミリ秒"
      
    Debug.Print
    
    Debug.Print "##### テスト4計測開始 #####"
    total = 0
    For j = 1 To 10
        start = GetTickCount()
        For i = 1 To 16384
            buf = Number2Letter3(i)
        Next i
        diff = GetTickCount() - start
        Debug.Print "time : " & Format(diff, "####") & "ミリ秒"
        total = total + diff
    Next j
    Debug.Print "Total : " & total & "ミリ秒"
End Sub
##### テスト3計測開始 #####
time : 94ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 109ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 79ミリ秒
time : 93ミリ秒
Total : 843ミリ秒

##### テスト4計測開始 #####
time : 78ミリ秒
time : 79ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 78ミリ秒
time : 79ミリ秒
Total : 782ミリ秒

ホントに微差ですが、改善しました。

まとめ

 意外にも思い付きで作成した再帰関数の方が速くて驚きました!少量のデータの場合はあんまり気にする必要はありませんね。ただもし大量にデータを処理する必要がある場合は、再帰で定義した関数を使いましょう!

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です


reCaptcha の認証期間が終了しました。ページを再読み込みしてください。