Прошу консультацю по функции ВПР. Есть 2 больших списка - на Лист1 500 000 строк, на Лист2 около 900 000. Данные отсортированы по алфавиту. При помощи ВПР с листа2 переношу данные на лист1. Подскажите, пожалуйста, как в макросе в функцию ВПР вставить диапазон данных Листа2 из переменной, т.е. в ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Лист2!R[-1]C[-2]:R[1010]C[-1],2,0)" вместо 1010 вставить значение переменной SS. Кол-во строк постоянно меняется, а вручную каждый раз править - несолидно.
И второй вопрос, на последних 100 000 строках Листа1 в формуле ВПР диапазон просматриваемых данных на листе2 выходит за границы допустимых миллиона строк. Это очень критично? Можно ли сделать такой трюк: первая строка Листа1 просматривает весь диапазон данных Листа2, втрая строка Листа1 просматривает Лист2 с места остановки предыдущей формулы и только до конца диапазона данных, и т.д., т.е. просмотр диапазона постоянно сужается? Спасибо
Прошу консультацю по функции ВПР. Есть 2 больших списка - на Лист1 500 000 строк, на Лист2 около 900 000. Данные отсортированы по алфавиту. При помощи ВПР с листа2 переношу данные на лист1. Подскажите, пожалуйста, как в макросе в функцию ВПР вставить диапазон данных Листа2 из переменной, т.е. в ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Лист2!R[-1]C[-2]:R[1010]C[-1],2,0)" вместо 1010 вставить значение переменной SS. Кол-во строк постоянно меняется, а вручную каждый раз править - несолидно.
И второй вопрос, на последних 100 000 строках Листа1 в формуле ВПР диапазон просматриваемых данных на листе2 выходит за границы допустимых миллиона строк. Это очень критично? Можно ли сделать такой трюк: первая строка Листа1 просматривает весь диапазон данных Листа2, втрая строка Листа1 просматривает Лист2 с места остановки предыдущей формулы и только до конца диапазона данных, и т.д., т.е. просмотр диапазона постоянно сужается? Спасибоmark
Я бы при количестве строк более 1000 уже бы отказался б от формул При таких объёмах только макросы и только словари! Не стал проверять, возможно с макрос с Find будет работать более-менее приемлимо. Но при 1000000 строк я не дождался завершения выполнения. прошло более 15 минут. Теперь выделил столбец для очистки и ексел опять задумался 100000=2с 500000=45с при лимоне завис Блин!! кто придумал такую капчу? За что дискриминируете дальтоников?
Я бы при количестве строк более 1000 уже бы отказался б от формул При таких объёмах только макросы и только словари! Не стал проверять, возможно с макрос с Find будет работать более-менее приемлимо. Но при 1000000 строк я не дождался завершения выполнения. прошло более 15 минут. Теперь выделил столбец для очистки и ексел опять задумался 100000=2с 500000=45с при лимоне завис Блин!! кто придумал такую капчу? За что дискриминируете дальтоников?alex77755
Спасибо всем, кто откликнулся, но ответа так и не получил. Ran предложил формулу ActiveCell.FormulaR1C1=VLOOKUP(RC[-2];Лист2!C1:C2;2;0), но непонятно, Лист2!C1:C2 - как этим пользоваться, в ячейках С1 и С2 должны быть мои переменные? А насчет сужения диапазона по мере просмотра данных на Листе2 может какой цикл организовать?, но опять- таки не знаю, как вставить значение переменной в формулу ВПР
Насчет скорости - это кусок из реальной рабочей книги, кроме выборки данных, там еще происходит переименование колонок, их перемещение, все работает, но по времени занимает минут 10-15, хотелось бы ускорить процесс.
Спасибо всем, кто откликнулся, но ответа так и не получил. Ran предложил формулу ActiveCell.FormulaR1C1=VLOOKUP(RC[-2];Лист2!C1:C2;2;0), но непонятно, Лист2!C1:C2 - как этим пользоваться, в ячейках С1 и С2 должны быть мои переменные? А насчет сужения диапазона по мере просмотра данных на Листе2 может какой цикл организовать?, но опять- таки не знаю, как вставить значение переменной в формулу ВПР
Насчет скорости - это кусок из реальной рабочей книги, кроме выборки данных, там еще происходит переименование колонок, их перемещение, все работает, но по времени занимает минут 10-15, хотелось бы ускорить процесс.mark
Sub сравнение_mark() With Sheets("Лист1").Range("A2").CurrentRegion.Offset(1).Resize(, 1) .Offset(, 2).FormulaR1C1 = "=VLOOKUP(RC[-2],Лист2!C1:C2,2,0)" End With End Sub
[/vba]
А вообще - вот ваш макрос
[vba]
Code
Sub сравнение_mark() With Sheets("Лист1").Range("A2").CurrentRegion.Offset(1).Resize(, 1) .Offset(, 2).FormulaR1C1 = "=VLOOKUP(RC[-2],Лист2!C1:C2,2,0)" End With End Sub
Sub qqq() Dim arr, i& arr = Sheets("Лист2").Range("A1").CurrentRegion.Offset(1).Value With CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr) .Item(arr(i, 1)) = arr(i, 2) Next arr = Sheets("Лист1").Range("A1").CurrentRegion.Offset(1).Resize(, 3).Value For i = 1 To UBound(arr) If .exists(arr(i, 1)) Then arr(i, 3) = .Item(arr(i, 1)) Next End With Sheets("Лист1").Columns("C").NumberFormat = "@" Sheets("Лист1").Range("A2").Resize(UBound(arr), 3) = arr End Sub
[/vba]
На словаре
[vba]
Code
Sub qqq() Dim arr, i& arr = Sheets("Лист2").Range("A1").CurrentRegion.Offset(1).Value With CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr) .Item(arr(i, 1)) = arr(i, 2) Next arr = Sheets("Лист1").Range("A1").CurrentRegion.Offset(1).Resize(, 3).Value For i = 1 To UBound(arr) If .exists(arr(i, 1)) Then arr(i, 3) = .Item(arr(i, 1)) Next End With Sheets("Лист1").Columns("C").NumberFormat = "@" Sheets("Лист1").Range("A2").Resize(UBound(arr), 3) = arr End Sub
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
