Блог

Главная - Вопросы по программированию - Самый эффективный способ синтаксического анализа в большом диапазоне в массиве VBA

Самый эффективный способ синтаксического анализа в большом диапазоне в массиве VBA

#arrays #vba #excel #matrix

#массивы #vba #excel #матрица

Вопрос:

У меня есть большой диапазон данных в Excel, которые я хотел бы преобразовать в массив для определяемой пользователем функции. Диапазон составляет 2250 x 2250. Для анализа каждой ячейки в цикле for требуется слишком много времени, и она слишком велика, чтобы быть назначенной массиву с помощью этого метода: 

 dim myArr as Variant
myArr = range("myrange")

Просто мозговой штурм здесь, было бы более эффективно анализировать в каждом столбце и объединять массивы? Есть идеи?

Спасибо

Комментарии:

1. Я просто настраиваю рабочую книгу с данными в диапазоне от a1 до chn2250 (диапазон 2250X2250) и считываю ее в альтернативный массив с именем «x», используя x= range("a1:chn2250").Value2 then, просто чтобы убедиться, что это сработало debug.print x(2,2) . Вся операция заняла меньше секунды. Итак, я бы предложил альтернативный массив.

2. Попробуйте добавить .Value или .Value2 , как предложил @Kyle, к тому, что у вас было выше.

3. Я явно не указывал .Value2 , но это наверняка повышает производительность.

4. 2250×2250×(8 bytes per double) = 38.6 Mb который вообще не должен нагружать память.

5. @ja72 откуда вы знаете, что они двойные? что, если все они представляют собой строки длиной 1024? Это вариантный массив… Вариантные переменные с числами требуют 16 байт памяти. Переменным типа данных Variant с символами обычно требуется 22 байта памяти плюс память, требуемая для строки. Это даже не говорит о памяти, используемой для самой структуры массива.

Ответ №1:

Вы почти на месте.

Необходимый вам код:

 Dim myArr as Variant
myArr = range("myrange").Value2

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

Обратите внимание, также, что я не удосужился Redim указать размеры массива: свойства Value и Value2 представляют собой 2-мерный массив (от 1 до Rowcount, от 1 до Col Count)… Если только это не одна ячейка, которая будет скалярным вариантом, который нарушает любой нисходящий код, который ожидал массив. Но это не ваша проблема с известным диапазоном 2250 x 2250.

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

Общий принцип заключается в том, что каждое «попадание» на рабочий лист занимает около двадцатой доли секунды — некоторые машины намного быстрее, но у всех бывают плохие дни — и «попадание» или чтение одной ячейки в переменную почти точно такое же, как чтение диапазона в семь миллионов ячеекв массив вариантов. Оба в несколько миллионов раз быстрее, чем чтение этого диапазона в одной ячейке за раз.

В любом случае, вы также можете считать любую операцию в VBA выполненной за нулевое время, как только вы выполнили «чтение» и прекратили взаимодействие с рабочим листом.

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

Помните Erase о варианте массива, когда закончите, вместо того, чтобы полагаться на то, что он выходит за рамки: это будет иметь значение при таком размере диапазона.

Комментарии:

1. Привет, Нил, большое спасибо за подробное объяснение. Я не думал использовать Value2, и, похоже, это должно сработать. Тем не менее, я могу столкнуться с объемом памяти. Макрос теперь прерывается, сообщая, что ему не хватает памяти. Размер листа составляет 70 КБ, поскольку каждая ячейка в матрице имеет функцию. Есть ли какие-либо другие рекомендации, которые вы можете дать для меня? Если нет, я могу попытаться удалить некоторую избыточную информацию.

2. @Daniel — Макрос ломается в arr = Range.Value2 инструкции? Есть предел тому, что вы можете сделать, но объявление переменной объекта range и ее использование могут помочь; как и настройка Application.Calculation = xlCalculationManual

3. @Daniel — если эти шаги не решают проблему, ваш следующий шаг — переопределить массив вручную, затем передать в него значение диапазона: Redim arr(1 to rng.Rows.Count, 1 to rng.Columns.Count и затем попробовать arr = rng.Value2 … И следующим шагом является загрузка диапазона порциями — либо по тысяче строк, либо по тысяче столбцов или одновременно. Также: вы используете Option.Explicit или параметр «Требовать объявления переменной» в VBA?

4. @NigelHeffernan при добавлении .Value2 похоже, что это ускоряет код примерно 10% …it ничего не делает для решения проблемы. К вашему сведению, использование .Formula заняло примерно в 2 раза больше времени (с моим набором данных), что имеет смысл, поскольку строки больше.

5. @Profex — Value2 — это (или должно быть) первое, что вы пытаетесь сделать при чтении значений в диапазоне; все остальное будет обременять вас зависимостями от локальных форматов. Канонический пример — использование . Значение 2 для извлечения базового серийного номера даты.

Ответ №2:

Это работает нормально.

 Sub T()
    Dim A() As Variant

    A = Range("A2").Resize(2250, 2250).Value2

    Dim i As Long, j As Long
    For i = 1 To 2250
        For j = 1 To 2250
            If i = j Then A(i, j) = 1
        Next j
    Next i

    Range("A2").Resize(2250, 2250).Value2 = A
End Sub

Комментарии:

1. Спасибо, это интересный трюк! … Можете ли вы проверить, работает ли объявление переменной диапазона, установка для нее значения monster range и заполнение массива из этой переменной диапазона? Эта ошибка может зависеть от машины, но ближайшей причиной является то, что VBA делает дикие предположения о памяти, которую ему необходимо выделить, когда он пытается интерпретировать Range( [some string we'll parse at runtime] ).Value2 вместо хорошо управляемого объекта с помощью VTable или (как вы нам показали) функции, возвращающей диапазон, с четко определенным размером.

2. Как Set r = Range("..").Resize(..) и тогда vals = r.Values2 ? Это то же самое, что и выше.

3. — Не совсем. С точки зрения того, для чего предназначен код, оба подхода одинаковы. Но достаточно ли умен компилятор (и поведение во время выполнения), чтобы распознать, что ваш объект r диапазона имеет известные размеры во время компиляции? Или реализация для чтения измерений переменной диапазона отличается (с меньшей вероятностью делать дикие предположения и переоценивать при выделении памяти) от механизма для чтения его из метода диапазона с произвольной адресной строкой?

4. Я бы предположил, что массив в arr = Range("A1").Value выделяется до того, как будет проанализирована адресная строка — возможно, до размеров всего листа! — и затем переопределяется, когда количество строк и столбцов известно. Но объявленный объект диапазона не имеет .Value свойства для передачи варианту массива, пока не будут известны размеры, и механизм выполнения должен выполнять задачи в правильной последовательности. Конечно, вполне возможно, что сообщение об ошибке на самом деле не «Не хватает памяти» : это может быть «Ой! Мне нужно выделить память прямо сейчас , но одно из чисел отсутствует »

5. Я почти уверен, что компилятор знает, какой размер диапазона is…it это не имеет значения только потому, что это явно прописано для нас, людей, для чтения. Он не угадывает размер и не пытается выделить какую-либо память, пока не дойдет до этой строки …. так работает VBA AFAIK.

Ответ №3:

Я думаю, что лучшими вариантами являются:

  1. Попробуйте ограничить данные разумным числом, скажем, 1 000 000 значений за раз.
  2. Добавьте некоторую обработку ошибок, чтобы перехватить Out of Memory ошибку, а затем повторите попытку, но уменьшите размер вдвое, затем на треть, на четверть и т. Д., Пока это не сработает.

В любом случае, если мы используем наборы данных порядка 5 000 000 значений, и вы хотите убедиться, что программа будет запущена, вам нужно будет настроить код, чтобы разделить данные.