Vba & Excel. Быстрая обработка больших объемов в Excel [written under The Psycho Realm - The Psycho Realm]
Как и обещал, для тех кому нужно обрабатывать большие объемы данных.
Если вам приходилось обрабатывать два или более листа Excel с тысячами строк каждый (к примеру 10`000 строк х 10-20 столбцов), и вы пытались выполнить это в стиле:
Берем
значение из ячеёки на листе 1, берем
значение на листе 2, проводим
вычисления/сравнения, печатаем их на
лист 3/1/2... etc.
То, наверняка заметили ту безбашенную
скорость, которую развивает движок
Excel :-)
.
Эксперименты показали, что тормоза возникают при частых операциях непосредственного чтения данных из листа Excel и, соответственно записи данных в ячейки.
Расмотрим не оптимизирующие алгоритмы предикативного чтения/и прочий психоделический "бутор", а простую и приземленую технику обработки данных.
Приведу свой, сугубо субъективный опыт.
Я, для себя проблему больших блоков данных решил следующим образом:
Читаем весь массив необходимых для работы данных с листа Excel в массив, так же читаем все остальные данные, формируем столько массивов сколько необходимо при выполнении вычислений
При выполнении вычислений данные не выкидываем в лист Excel, а так-же методично "складыаем" в массив. Только после окончания вычислений выкидываем все данные из массива (с результатами) на лист Excel.
Замечено, так же, что вместо For...Next, лучше использовать связку For Each...Next - работает быстрее.
Замечено, так же, что красивость в виде убогой DoEvents реализована не в пользу быстродействия - и на большом количестве итераций скорость выполнения циклов с DoEvents - заваливается.
Замечено, так же, что красивость в виде оббновления информации в StatusBar'е приложения Excel - уменьшает скорость выполниня циклов.
Замечено, что в некоторых случаях (при наличии формул) отключение автоматического пересчета формул перед выводом больших объемов данных в лист Excel, ускоряет (а, вернее сказать не замедляет) выполнение макроса. Отключать можно, вручную, через настройки, но лучше программно.
Пример функции, для формировани массива исходных данных.
'Source Array Declaration ' Объявление локальной переменной Private B_l_ArraySrc() As String ' массив данных
Private Function BCreateArrayEx(ByRef strSheetCaption As String, ByVal lRowFirst As Long, ByVal lRowLast As Long, ByVal lColumnFirst As Long, ByVal lColumnLast As Long) Dim lCounterExt As Long Dim lCounterInt As Long Dim ptrSheet As Worksheet Dim lFirstDimension As Long Dim lLastDimension As Long
' lFirstDimension - первая размерность массива - разность между номером последнего и первого стобцов + 1 lFirstDimension = lColumnLast - lColumnFirst + 1 ' lLastDimension - последняя размерность массива - 0, но в дальнейшем будет расти lLastDimension = 0 ' Обработчик ошибок On Error GoTo Error_Handler ' Определяем динамический массив с фиксированной первой размерностью, и переменной второй размерностью
' Если объем данных заранее известен, то лучше (читайте быстрее) статичные массивы. ReDim B_l_ArraySrc(lFirstDimension, lLastDimension) ' Определяем ссылку на лист. ptrSheetCaption - должна содержать валидное имя листа в открытой книге Excel ' Быстрее будет если сохранить ссылку на лист и пользоваться ею, а не "получать" её каждый раз, обращаясь на прямую. Set ptrSheet = ActiveWorkbook.Worksheets(strSheetCaption) ' Я не использую For Each...Next, хотя выше отметил, что "он" работает быстрее. Для меня понятнее For...Next For lCounterExt = lRowFirst To lRowLast ' Выделяем место в динмическом массиве для хранения новой порции данных ' Ключевое слово - Preserve - если его не объявить, то массив будет "увеличен", но данные в массиве станут не валидными - обнулятся. ReDim Preserve B_l_ArraySrc(lFirstDimension, lLastDimension) ' Читаем по столбцам данные для очередной строки For lCounterInt = lColumnFirst To lColumnLast ' Считанные данные сохраняем в массиве B_l_ArraySrc(lCounterInt - lColumnFirst, lLastDimension) = ptrSheet.Cells(lCounterExt, lCounterInt).Text
Next ' Увеличиваем вторую размерность массива lLastDimension = lLastDimension + 1 Next ' Все считано, по правилам хорошего тона - явно обнулим ссылку на лист Excel
Set ptrSheet = Nothing ' Выходим из функции через Exit Function, так как дальше мы определили "подпрограмму", для обработчика ошибок
Exit Function Error_Handler: ' При ошибке явно обнуляем указатель на лист Excel Set ptrSheet = Nothing ' Выводим сообщение об ошибке MsgBox Err.Description, vbInformation + vbOKOnly, "B_ArrayBasic.BCreateArrayEx:Error" End Function
По аналогии делаем вывод из массива с результатами в лист Excel...
Всем
привет. Есть два столбца в excel (Например
A1:A1000 и B1:B1000) нужно сравнить столбец
B со столбцом A. То есть В1 сравнивается
с А1, затем с А2 итд. Затем B2 сравнивается
с А1, А2 итд. В случае если совпаления
есть между столбцами ячейки, в столбце
С писалась бы какая-нибудь информация...
В
идеале сравнение бы сравнение шло не
в полном объеме а частично.
Такая
программа нужна для сравнения
прайсов
например в одном прайсе
"часы
наручные изумруд", во втором прайсе
"часы Изумруд"
Ну или просто
чтобы строки сравнивались, между
собой. Хотя бы часть находить программа
будет!!!
Есть функция СОВПАД, но
она умеет сравнивать только одну
ячейку со второй ячейкой, а как
массивами сравнивать?????
|
|
|||
|
|
|||
|
||||
FINANSIST |
|
|||
Опытный
|
Меня всегда интересовал вопрос - почему в банальных примитивнейших задачах пользователям хочется видеть именно VBA - решение? Воткнуть в столбец "С1" элементарную формулу "=А1=В1", автозаполнением растянуть и отобрать по значению "ЛОЖЬ" автофильтром воспитание запрещает?
Ну или, если лень целый дополнительный столбец "С" формировать, воткнуть в одну единственную ячейку =и(А1:А65000=В1:В65000) не забыв вместо Enter нажать Ctrl+Shift+Enter -------------------- “...Брали корову рыжую одну, отдавать будем корову рыжую одну, чтобы не нарушать отчетности” Эдуард Успенский, “Каникулы в Простоквашино” Мой 10-дневный весенний сплав по Угре |
|||
|
|
|||
|
||||
|
(нет
голосов)
Загрузка ...
Профиль
Группа:
Участник
Сообщений: 469
Друзей:
0;
Групп: 0
Регистрация:
11.4.2008
Где: Москва
Репутация:
9
Всего: 16
)
z2000 |
|
|||
Новичок Профиль Группа: Участник Сообщений: 3 Друзей: 0; Групп: 0 Регистрация: 18.3.2009 Репутация: нет Всего: нет
|
а как сделать если не полное совпадение? Как в примере с часами? А как растянуть автозаполнением? Здесь ситуация такая, что A2 совсем не обязательно будет равна B2, но она может быть равна B500... Да и потом ограничение же есть в экселе, максимум 255 Сейчас я выкручиваюсь, так: пишу =ИЛИ(СОВПАД(A849;$B$1:$B$252)) затем в следующем столбце: =ИЛИ(СОВПАД(A843;$B$253:$B$501)) затем в следующем столбце: =ИЛИ(СОВПАД(A843;$B$253:$B$701)) (У меня всего 700 позиций!!! А как можно упростить???? |
|||
|
|
|||
|
||||
Akina |
|
||||||||||
Советчик Профиль Группа: Модератор Сообщений: 16379 Друзей: 0; Групп: 0 Регистрация: 8.4.2004 Где: Зеленоград, Москв а, Россия Репутация: 17 Всего: 335
|
Смотрим в сторону
Вручную. -------------------- О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума. |
||||||||||
|
|
||||||||||
|
|||||||||||
Staruha |
|
|||
|
на VBA ф-я Split -------------------- Vent Smeta |
|||
|
|
|||
|
||||
Эксперт
Профиль
Группа:
Комодератор
Сообщений: 1240
Друзей:
0;
Групп: 0
Регистрация:
1.2.2004
Где: Казань
Репутация:
13
Всего: 27
FINANSIST |
|
||||
Опытный Профиль Группа: Участник Сообщений: 469 Друзей: 0; Групп: 0 Регистрация: 11.4.2008 Где: Москва Репутация: 9 Всего: 16
|
Staruha, а для чего данная функция нужна автору топика? -------------------- “...Брали корову рыжую одну, отдавать будем корову рыжую одну, чтобы не нарушать отчетности” Эдуард Успенский, “Каникулы в Простоквашино” Мой 10-дневный весенний сплав по Угре |
||||
|
|
||||
|
|||||
z2000 |
|
|||
Новичок Профиль Группа: Участник Сообщений: 3 Друзей: 0; Групп: 0 Регистрация: 18.3.2009 Репутация: нет Всего: нет
|
Вручную особо не сделаешь. Нужно что-то типа функции, которая бы искала ячейки с определенной долей совадения, и выдавала бы их. Такое возможно реализовать? |
|||
|
|
|||
|
||||
Staruha |
|
||||
Эксперт Профиль Группа: Комодератор Сообщений: 1240 Друзей: 0; Групп: 0 Регистрация: 1.2.2004 Где: Казань Репутация: 13 Всего: 27
|
Так спросил же как сравнивать массивы.А Split разделит ячейку на массивы
что-то вроде этого.Но здесь проблема в том что есть одинаковые элементы массива в разных изделиях Например часы Изумруд и часы Слава и там и там есть элемент массива часы. Поэтому надо изначально присваивать каждому изделию код,а назвать можно как угодно
|