оптимизация быстродействия файла

[peq]

добрый день!

есть вопрос по быстродействию формул.

на днях столкнулся с задачей анализа очень большого объема данных в эксель. вроде, упростил формулы как мне кажется предельно, но оболочка все равно тормозит и обсчитывает файл очень медленно. если мои формулы можно записать так, чтобы они обрабатывались более быстро, дайте, пожалуйста, совет как.

была мысль отключать обновление и протаскивать формулы макросом, чтобы в каждом столбце они были нужной длины и не больше. я думаю, это может дать существенный буст к скорости, но вряд ли достаточный..

сам алоритм выбора переносить в макросы.. тоже думал, но мне кажется встроенные формулы должны обрабатывать данные быстрее, чем код, который я придумаю.


такие вот дела...

[Hugo121]

Если Windows, то можно делать макросом на словаре - сперва всю выгрузку загнать в словарь (через массив), затем по каждому ключу сразу выбирать подходящие по условию и сразу их считать.
Тем более что упорядочивание не критично.
Пока не известна система - нет смысла писать код.

[Казанский]

peq,
значения key и key2 заданы, или должны извлекаться из этих же данных?

[Hugo121]

Хотя вот для начала - на 11 ключей:

Sub tt()
    Dim a(), i&, ii&, iii&, t, col As Object

    Application.Calculation = xlCalculationManual
    [a11].CurrentRegion.Offset(1).Clear

    a = Sheets("выгрузка").[A5].CurrentRegion.Value

    With CreateObject("scripting.dictionary"): .comparemode = 1
        For i = 2 To UBound(a)
            t = a(i, 2)
            If Not .exists(t) Then .Add t, New Collection
            .Item(t).Add Array(a(i, 3), a(i, 1))
        Next

        For i = 2 To 12
            If .exists(Cells(3, i).Value) Then
                Set col = .Item(Cells(3, i).Value)
                ReDim a(1 To col.Count, 1 To 1): iii = 0
                t = Cells(11, i).Value
                For ii = 1 To col.Count
                    If col(ii)(0) > t Then
                        iii = iii + 1
                        a(iii, 1) = col(ii)(1)
                    End If
                Next
                Cells(2, i) = iii
                Cells(12, i).Resize(iii, 1) = a
            End If
        Next
    End With

    Application.Calculation = xlCalculationAutomatic

End Sub

Выполнять при активном листе "расчёт" (его формулы частично умрут).
P.S. есть куда оптимизировать этот подход по скорости, это так для начала...

[peq]

Hugo121
круто.. буст к скорости в несколько порядков..
никогда не сталкивался с коллекциями, придется их брать на регулярное вооружение, если это быстро настолько.
спасибо!

Казанский
да, можно считать, что key/key2 заданы и фиксированы, я кое-где формулы в файле оставил, просто чтобы данные были более понятными по своей природе.

[Hugo121]

Там ключевое это словарь. Коллекция массивов - это так, один из вариантов собрать данные по ключу.

[peq]

понял.. уже читаю..
единственный вопрос - а какие предельные размеры одного словаря? например в 2007. по размеру листа 16тыс*1млн? или можно больше?

[Hugo121]

Размер словаря не знаю, сходите на MSDN. Но думаю скорее упрётесь в память.
Можно обойтись без массива в коллекции, и даже без коллекций вообще - запоминаем в словаре номера строк массива с уникальным ключём (например в строку, но лучше всёж в коллекцию), затем получаем номер строки, по нему из исходного массива значения.