[[20130828173208]]　『ユーザー定義型関数の速度を上げる方法について』（BB）　ページの最後に飛ぶ

『ユーザー定義型関数の速度を上げる方法について』（BB）

Windows Vista、Excel2003です。

エクセル上のセルの色にて数量を数える関数を作っているのですが、
データの更新にすごく時間がかかります。
関数自体は、100行の200列ぐらいに使用しております。

速度を上げる方法は、ないでしょうか？
下記が現状の関数です。

よろしくお願いします。

Function koutei(a, b As Range) As Integer

    Dim HitData As String
    Dim c As Range
    HitData = a

    hit = 0
    For Each c In b
        If Len(c.Text) = 0 Then
            If c.Interior.ColorIndex = 36 Then
                For i = -1 To -1000 Step -1
                    If Len(c.Offset(0, i)) > 0 Then
                        Exit For
                    End If
                Next
                If HitData = c.Offset(0, i).Text Then
                    If c.Row > 37 And c.Row < 106 Then
                        hit = hit + 2
                    Else
                        hit = hit + 1
                    End If
                End If
            End If
        Else
            If HitData = c.Text Then
                If c.Row > 37 And c.Row < 106 Then
                    hit = hit + 2
                Else
                    hit = hit + 1
                End If
            End If
        End If
    Next
    koutei = hit

End Function

 よくわかってないけど・・・
 どういう時にどうやって使うの？

 一応セルへのアクセス回数を減らすため、変数に入れる案
Function koutei(a, b As Range) As Integer

    Dim HitData As String
    Dim c As Range
    Dim rw As Long   '行
    Dim t As String  '文字
    Dim d As Variant 'よくわからないけど、文字調べてるやつを配列に
    Dim ci As Long   'カラーインデックス
    Dim x As String  '文字調べてるやつ

    HitData = a

    hit = 0
    For Each c In b
        t = c.Text
        ci = c.Interior.ColorIndex
        rw = c.Row
        cl = c.Column

        If Len(t) = 0 Then
            If ci = 36 Then
                d = c.Resize(, c.Column - 1).Text
                For i = 1 To cl Step 1
                    x = d(1, cl - i)
                    If Len(x) > 0 Then
                        If HitData = x Then
                            If rw > 37 And rw < 106 Then
                                hit = hit + 2
                            Else
                                hit = hit + 1
                            End If
                            Exit For
                        End If
                    End If
                Next i
            End If
        Else
            If HitData = t Then
                If rw > 37 And rw < 106 Then
                    hit = hit + 2
                Else
                    hit = hit + 1
                End If
            End If
        End If
    Next c
    koutei = hit
End Function

 （稲葉）

（稲葉）さま

ありがとうございます。
さっそく試してみます。

(BB)

 間違えてた！
 -方向のOFFSET後にリサイズ
 配列をtextじゃなくてvalue
 あと処理まとめた

Function koutei(a, b As Range) As Integer

    Dim HitData As String
    Dim c As Range
    Dim rw As Long           '行
    Dim t As String          '文字
    Dim d As Variant         'よくわからないけど、文字調べてるやつを配列に
    Dim ci As Long           'カラーインデックス
    Dim x As String          '文字調べてるやつ
    Dim flg As Boolean       'hit加算処理をまとめるため

    HitData = a

    hit = 0
    For Each c In b
        t = c.Text
        rw = c.Row
        cl = c.Column
        d = c.Offset(, -cl + 1).Resize(, cl).Value
        ci = c.Interior.ColorIndex
        flg = False

        If Len(t) = 0 Then
            If ci = 36 Then
                For i = 1 To cl - 1 Step 1
                    x = d(1, cl - i)
                    If Len(x) > 0 Then
                        If HitData = x Then
                            flg = True
                            Exit For
                        End If
                    End If
                Next i
            End If
        Else
            If HitData = t Then
                flg = True
            End If
        End If

        If flg Then
            If rw > 37 And rw < 106 Then
                hit = hit + 2
            Else
                hit = hit + 1
            End If
        End If
    Next c

    koutei = hit
End Function

 （稲葉）

 >関数自体は、100行の200列ぐらいに使用しております。 
 20000箇所でこの関数を実行すると、遅いでしょうね。特に引数 b で指定される範囲が
 広い場合は一回の関数呼び出しで多量のセルアクセスが発生するので、現行の構成だと
 どうしても時間がかかると思います。

 一番時間のかかる要因はセルの色に依存して処理しているからですが、これを別列のセルに
 ステータスとセットで数値管理するようにできないでしょうか。

 あるいは計算範囲がシンプルなのであれば、ユーザ定義関数ではなくマクロの一括処理で
 行えば早くなると思います。

 今回の関数は少量のセルでの使用でしたら問題ないかもしれませんが、広範囲で使用する
 用途には適さないように思います。
 (Mook)

（稲葉）さま
（Mook）さま

ありがとうございました。

二層構造にして、値のみで管理してみます。

(BB)

コメント返信：

[ 一覧(最新更新順) ]

[[20130828173208]] 『ユーザー定義型関数の速度を上げる方法について』（BB） ページの最後に飛ぶ

[[20130828173208]]　『ユーザー定義型関数の速度を上げる方法について』（BB）　ページの最後に飛ぶ