えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

MENU

VBAで文字列中の文字をカウントする関数を作ってみた

今回はVBAで最速で文字列に含まれる文字列の出現回数をカウントする関数を求めて色々と調査してみました。


きっかけ

とあるCSVパーサーのソースコードを読んでいて、以下の様な構文を見かけた。

'CSVデータの行数の最大値を取得するため、
'暫定的に入力データを改行コードごとに配列に分割
Dim iLines() As String
iLines = Split(s, vbCrLf)

dim maxLines As Long
maxLines = UBound(iLines) - LBound(iLines) + 1

私自身も今までこの方法はよく使ってきた。

一行で書けるので美味しい。

maxLines = UBound(Split(s, vbCrLf))

でも、ちょっと待った。

この構文はメモリリークが起こると、つい最近どこかで読んだ覚えがある。

そもそも行数を知りたいだけなのに、入力文字列と同等以上の配列を返すのはロスが大きすぎるのではないか?


よし、調査してやる。


調査内容

今回作成するのは、文字列に含まれる文字列の出現回数をカウントする関数 だが、私が思いついたのは次の4通り。

  • Split関数で分割して配列の要素数
  • Replace関数で削除した前後の文字数の差
  • InStr関数で先頭から探索
  • 一旦Byte配列に変換して先頭から探索

実は上記3つは検索すると普通に紹介しているサイトがいくつもある。

ただし、サロゲートペアに対応していなかったり、複数文字に対応してなかったりするので適当に私の方で書き直した。

ちなみに、この中では4つ目が私の思いついた新しい方法である。バイト配列ならきっと速いはずだ!!!


結果報告

先日、ある方に「結論から話せ」と散々注意されたので、先に結論を書いておく。


結果は

  • InStrがダントツで高速
  • バイト配列はダントツで低速

である。


目安としては、InStrを1倍とした時、SplitやReplaceは10倍、バイト配列は100倍となる。

必ずしもバイト配列を使えば高速になるとは限らないのである。


単位はミリセコンド、小さいほど高速ということになる。

割合は最速の結果に対してどの程度遅いかを示している。


テスト1

abc😁def😁ghi

から

😁

をカウントする処理を10万回づつ実行した結果である。

Split LenReplace InStr Byte
1回目 65 78 17 147
2回目 66 77 18 136
3回目 64 75 18 132
4回目 65 84 18 135
5回目 65 120 18 129
6回目 70 88 19 148
7回目 98 80 19 125
8回目 71 92 20 130
9回目 83 82 19 129
10回目 65 87 19 135
平均 71.2 86.3 18.5 134.6
割合 26% 21% 100% 14%


テスト2

以下は10万行、35列、合計36.7MBの巨大なCSVから、改行の個数を計測した結果である。

Split LenReplace InStr Byte
1回目 128 236 25 2581
2回目 153 253 39 2563
3回目 130 261 53 2546
4回目 131 271 34 2577
5回目 132 253 25 2592
6回目 150 241 25 2577
7回目 133 249 24 2602
8回目 136 238 23 2643
9回目 132 243 25 2674
10回目 134 244 25 2653
平均 135.9 248.9 29.8 2600.8
割合 22% 12% 100% 1.1%


ソースコード

VBEではUnicodeの文字を記入すると??に化けてしまうので、試すにはエクセルシート上に記入したデータを読み込む必要がある。


こんな感じのデータを作成して

A B
1 基底文字(baseStr) 照合文字(chkStr)
2 abc😁def😁ghi 😁


こんな感じのプログラムでテストする。

Sub Test1()
    Const TESTROW = 2
    Debug.Print StrCnt_Split(Cells(TESTROW, 1), Cells(TESTROW, 2))
    Debug.Print StrCnt_Len(Cells(TESTROW, 1), Cells(TESTROW, 2))
    Debug.Print StrCnt_InStr(Cells(TESTROW, 1), Cells(TESTROW, 2))
    Debug.Print StrCnt_Byte(Cells(TESTROW, 1), Cells(TESTROW, 2))
End Sub


それでは、それぞれの方式のソースコードとちょっとした解説をしていく。


Split法

最も短い文字数で書けるので、とても重宝する求め方

'文字列に含まれる文字列の出現回数をカウントする関数
'(SPLITバージョン)
Function StrCnt_Split(baseStr As String, chkStr As String) As Long
    StrCnt_Split = UBound(Split(baseStr, chkStr))
End Function

基底文字列

abc😁def😁ghi

😁で分解すると

arr(0)=abc
arr(1)=def
arr(2)=ghi

となるため、添字の最大値=2が、照合文字の個数である。


Len-Replace法

Excel関数でカウントするときによく使う方法

'文字列に含まれる文字列の出現回数をカウントする関数
'(LEN,REPLACEバージョン)
Function StrCnt_Len(baseStr As String, chkStr As String) As Long
    StrCnt_Len = (Len(baseStr) - Len(Replace(baseStr, chkStr, ""))) / Len(chkStr)
End Function

前提として、照合対象である😁はサロゲートペアのため、Len関数では2としてカウントされる。

?Len("😁")
2

もともとの文字数は

?Len("abc😁def😁ghi")
13

照合文字をReplace関数で空欄に置換。つまり削除した後の文字数は

?Len(Replace("abc😁def😁ghi",",",""))
9

この差は

?13-9
4

差を照合文字の文字数で割れば、出現回数が2であることが分かる

?4/2
2

※厳密にはLen関数は文字数を返しているわけでは無いが、わかりやすさのため文字数と書いた。


InStr法

ロジックが少し厄介だが、先の通り最も高速な求め方

'文字列に含まれる文字列の出現回数をカウントする関数
'(INSTRバージョン)
Function StrCnt_InStr(baseStr As String, chkStr As String) As Long
    Dim n As Long: n = 0
    Dim ret As Long: ret = 0
    Do
        n = InStr(n + 1, baseStr, chkStr)
        If n = 0 Then
            Exit Do
        Else
            ret = ret + 1
        End If
    Loop
    StrCnt_InStr = ret
End Function


下記の部分にご注目

?InStr(n + 1, baseStr, chkStr)

1回目

?InStr(0 + 1, "abc😁def😁ghi", "😁")
4

そして4=0では無いので、ret+1する。

2回目

?InStr(4 + 1, "abc😁def😁ghi", "😁")
9

そして9=0では無いので、ret+1する。

3回目

?InStr(9 + 1, "abc😁def😁ghi", "😁")
0

そして0=0なので、繰り返しを終了する。

よって、

?StrCnt_InStr("abc😁def😁ghi", "😁")
2


バイト配列法

文字列を一旦バイト配列にすることで、少なくともSplitよりは高速になるのではないかと考えた。

結果は先の通り激遅である。

'文字列に含まれる文字列の出現回数をカウントする関数
'(バイト配列バージョン)
Function StrCnt_Byte(baseStr As String, chkStr As String) As Long
    Dim b() As Byte:    b = baseStr
    Dim c() As Byte:    c = chkStr
    
    Dim i As Long, j As Long
    Dim ret As Long: ret = 0
    Dim isAllMatch As Boolean
    i = 0
    Do
        '照合文字`chkStr`のバイト配列と全てが一致しているか調べる
        isAllMatch = True
        For j = LBound(c) To UBound(c)
            isAllMatch = isAllMatch And b(i + j) = c(j)
        Next
        
        'インデックスをシフトする
        If isAllMatch Then
            '一致した場合は照合文字数分シフト
            ret = ret + 1
            i = i + (UBound(c) - LBound(c) + 1)
        Else
            '一致しない場合は2バイトシフトする
            i = i + 2
        End If
        
        '照合文字の文字数をオーバーする場合は調査終了
        If i > UBound(b) - UBound(c) Then Exit Do
    Loop
    
    StrCnt_Byte = ret
End Function

文字列をバイト型配列変数に代入すると、文字コードの入ったバイト配列が出来上がる。

Dim b() As Byte:    b = baseStr
変数 文字 10進数 16進数
b(0) a 97 61
b(1) a 0 0
b(2) b 98 62
b(3) b 0 0
b(4) c 99 63
b(5) c 0 0
b(6) 😁 90 5A
b(7) 😁 50 32
b(8) d 100 64
b(9) d 0 0
b(10) e 101 65
b(11) e 0 0
b(12) f 102 66
b(13) f 0 0
b(14) 😁 90 5A
b(15) 😁 50 32
b(16) g 103 67
b(17) g 0 0
b(18) h 104 68
b(19) h 0 0
b(20) i 105 69
b(21) i 0 0


照合文字😁は上表でも分かるが、以下のとおりである。

文字 10進数 16進数
😁 90 5A
😁 50 32


今にして思えば、別にバイト配列にする必要は無くて、Midで順番に調べてもよかったのではないだろうか。


まとめ

せっかく新しい関数を作ってみたが、何の役にも立たない検証結果であった。


それはさておき、

  • いずれの方式でもサロゲートペアには対応できる

  • InStrを使った方法がダントツで高速である

という事が確認出来たので、良かったということにして、今日はここまでにする。


以上


何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪ ちゅんちゅん(・8・)

プライバシーポリシー