Более быстрый метод, чем ВПР, для сравнения двух наборов данных в Excel с использованием VBA, 1 имеет 180 000 элементов, другой 250 000
Я автоматизирую построение отчета в Excel с помощью VBA. Часть этого процесса я использую vlookup для сравнения списков. Вкладка 1 содержит примерно 180 000 позиций с уникальным идентификатором. Виртуальный поиск берет этот идентификатор и сравнивает его с «владельцами» на вкладке 2 примерно с 250 000 позиций. Время выполнения этой операции составляет примерно 25-30 минут, и мне интересно, есть ли более быстрый способ? Может быть, мне следует выполнить это сравнение, используя скрипт вне Excel, чтобы сократить время расчета?
Он работает нормально, поэтому я не пытался устранить неполадки. У меня есть несколько идей о выполнении работы вне Excel, в фоновом режиме, но я ищу идеи от более широкой группы.
Вот строка, которую я сейчас использую для поиска, она повторяется 5 раз в коде.
Range("Table").Offset(1).Select
ActiveCell.FormulaR1C1 = "=IFNA(VLOOKUP([@ID],table,2,0),""Unassigned"")"
С каждой итерацией вышеуказанной строки в книге пересчитывается, что занимает 30 минут. Я попытался установить расчет на xlManual, а затем обратно на xlAutomatic, но не повезло. Я думал, что могу просто запустить расчет одного рабочего листа после написания формул.
Любопытно, если кто-нибудь знает более быстрый способ сделать это. Как я уже сказал, время выполнения этого раздела составляет 30 минут, а общее время выполнения составляет 35-40 минут.
Пожалуйста, опубликуйте весь цикл, и мы можем помочь.
Я бы использовал больше VBA и какой-нибудь словарь, иначе это было бы медленно - сравните 2 больших набора без какой-либо оптимизации, должно быть медленным. Общие полевые функции — очень медленные обходные пути. Некоторые подсказки упр. здесь stackoverflow.com/questions/915317/… (?)
Скотт использует смещение .Select, чтобы перейти к первой строке после заголовка и ввести формулу. Я не запускаю его в цикле, который мог бы, поскольку у меня есть 5 итераций одного и того же кода ... можно попробовать, но ответ Рона ниже значительно сократил время выполнения. Спасибо!
Ответы 2
Может быть, попробуйте преобразовать результат вашей формулы ВПР в значение после каждой итерации, что-то вроде этого:
Sub foo()
Dim rngCell As Range
For Each rngCell In Range("Table").Offset(1)
rngCell.FormulaR1C1 = "=IFNA(VLOOKUP([@ID],table,2,0),""Unassigned"")"
rngCell.Value = rngCell.Value
Next rngCell
End Sub
Это должно помешать ему пересчитать ваши результаты ВПР. В качестве альтернативы используйте комбинацию ИНДЕКС+ПОИСКПОЗ или, если ваш набор данных отсортирован, используйте функцию ВПР с режимом соответствия ИСТИНА (приблизительно) вместо ЛОЖЬ (точно).
Я тоже буду экспериментировать! Спасибо!
Если вы можете СОРТИРОВАТЬ свои данные, вы можете создать двойной VLOOKUP с параметром range_lookup, установленным на TRUE. Это заставляет VLOOKUP выполнять бинарный поиск, который в большой БД может работать в 100 раз быстрее:
=IF(VLOOKUP(ID,Table,1,TRUE)=ID,VLOOKUP(ID,Table,2,TRUE),NA())
И если вы используете метод VLOOKUP, вы должны обязательно отключить ScreenUpdating, а также установить Calculation на ручной режим, пока вы заполняете рабочий лист формулами.
В качестве альтернативы может быть быстрее просто прочитать данные в массив или словарь VBA и выполнить весь поиск и сопоставление в VBA. Опять же, если вы можете отсортировать свой список, вы можете использовать бинарный поиск, который будет намного быстрее.
Вариант бинарного поиска может быть выигрышным, я буду работать над этим. Я могу сортировать данные достаточно легко. Спасибо! У меня также отключено обновление экрана и автоматическое вычисление.
Рон, который добился цели, первая пробежка сократилась до 2 минут. Списки уже были отсортированы, нужно было удвоить поиск. Спасибо!!
Не использовать
.Select: stackoverflow.com/questions/10714251/…