Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Курилка
Страницы: 1 2 3 След.
RSS
ВПР внутри
 
#1
29.11.2023 18:46:49
Столкнулся тут с обработкой двух таблиц - 990тыс и 20тыс строк. Работа для ВПР. Удивила скорость работы - пара секунд. 900к на открытие парсился дольше.
Как оно так быстро работает? Для сравнения макрос с .Find работал 1.5 часа.
обратил внимание, что файл с внедренным ВПР (20тыс строк) с 2мб вырос до 50мб.
 
 
 
#2
29.11.2023 20:50:08
Цитата
ae485 написал:
Как оно так быстро работает? Для сравнения макрос с .Find работал 1.5 часа.
Некорректно использованный метод может привести и к большим тормозам, но редко когда UDF опережает встроенную функцию при разовом применении , если не используется предварительный результат многократно.

В целом тема ни о чем. Что-то отработало быстро а что-то медленно. Вопрос то в чем?
По вопросам из тем форума, личку не читаю.
 
 
 
#3
30.11.2023 12:36:27
Макрос перебирает все строки из одного файла и ищет попозиционно совпадение (с помощью встроенной функции .Find)
Разница на много порядков  получается - секунды у ВПР против часов у макроса.

вопрос - как устроена ВПР внутри что обеспечивает такую скорость обработки?
 
 
 
#4
30.11.2023 13:13:52
Цитата
ae485 написал:
Макрос перебирает все строки из одного файла и ищет попозиционно совпадение (с помощью встроенной функции .Find)
ну так не надо перебирать , а надо искать в диапазоне.
В любом случае , не видя что вы там натворили, никто не скажет в чем ошибка, но выше написал,
Цитата
БМВ написал:
редко когда UDF опережает встроенную функцию при разовом применении
По вопросам из тем форума, личку не читаю.
 
 
 
#5
30.11.2023 13:30:12
Тут еще второй момент есть: ВПР ищет практически мгновенно данные на больших объемах, если последний аргумент опущен или равен ИСТИНА(TRUE). Т.е. если применен метод интервального просмотра значений. Так же, в последних версиях Excel работа ВПР и ПОИСКПОЗ была оптимизирована(применено кеширование для повторяющихся значений и в целом улучшен алгоритм, если ничего не путаю). У .Find таких оптимизаций нет.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#6
30.11.2023 16:07:35
Цитата
Дмитрий(The_Prist) Щербаков написал:
Тут еще второй момент есть: ВПР ищет практически мгновенно данные на больших объемах, если последний аргумент опущен или равен ИСТИНА(TRUE)
Дима, ну так для этого данные должны быть отсортированы, то есть дело в возможности применени этого метода, а не то результат будет быстрым , но неверным :-)

Цитата
Дмитрий(The_Prist) Щербаков написал:
У .Find таких оптимизаций нет.
ну даже при таких условиях
Цитата
ae485 написал:
секунды у ВПР против часов у макроса.
звучит несколько удивительно.
По вопросам из тем форума, личку не читаю.
 
 
 
#7
30.11.2023 17:09:49
Цитата
БМВ написал:
звучит несколько удивительно
упускаешь, что ВПР и "макрос на основе Find" написаны людьми с разными компетенциями  :)
Изменено: Jack Famous - 30.11.2023 17:10:00
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#8
01.12.2023 08:04:58
ae485,
я бы посоветовал Вам вложить примеры файлов с макросом, а участники смогут указать на ошибки  ;)
И Возможно, в итоге макрос станет работать быстрее чем ВПР  :)
 
 
 
#9
01.12.2023 09:19:10
написал:
Цитата
ну так не надо перебирать , а надо искать в диапазоне.В любом случае , не видя что вы там натворили, никто не скажет в чем ошибка, но выше написал,
вопрос не про ошибку с .find. он работает как надо. попозиционно берет значение из xsInv (20тыс строк) и ищет в xsR (900тыс строк). но делает это в один поток и невообразимо долго. ВПР судя по надписям в статусбаре делает это как минимум в восемь потоков.

код такой:
Скрытый текст

из макроса ВПР еще не пробовал. Но судя комментариям выше однозначно стоит попробовать.

еще пока непонятно, почему исходный файл xsInv из 2МБ после добавления столбца с ВПР становится 50+МБ. xsR при этом порядка 35МБ.
Очевидно что для быстрой работы требуется приличная избыточность данных
Изменено: ae485 - 01.12.2023 09:23:38
 
 
 
#10
01.12.2023 09:34:49
Цитата
evgeniygeo: Возможно, в итоге макрос станет работать быстрее чем ВПР
Цитата
БМВ: редко когда UDF опережает встроенную функцию при разовом применении , если не используется предварительный результат многократно
тут согласен с медведем. VBA для UDF использую крайне редко и, если делать полный аналог штатной ВПР, то обогнать её не удастся. А вот, если сделать программу по вставке полученного результата тогда, когда это нужно и туда, куда нужно, то запросто.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#11
01.12.2023 09:35:04
Цитата
БМВ написал:
ну так для этого данные должны быть отсортированы
ну это я знаю, Миш :) Вопрос в том, что нам данные не показывают, как записана функция тоже - поэтому только предположения. ВПР на таком объеме за пару секунд очень настораживает. Когда писал - допускал, что и данные могут быть отсортированы и даже более того - что не отсортированы и как итог результат неверный. Но не все в принципе в курсе как именно работает интервальный просмотр и ожидают от него совсем не тот результат, который в итоге получают :) Но скорость работы при этом да, впечатляет :)
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#12
01.12.2023 11:33:01
Цитата
написал:
звучит несколько удивительно.
Это так: секунды против полутора часов. Выше выложил макрос. .Find тут пасует. Остается только вставлять формулы ВПР сохранять вместо формул найденные значения.
Еще вариант - задействование движка внешней СУБД. Но это только для многократного использования, а таблица на 900к нужна однократно.
 
 
 
#13
01.12.2023 18:22:23
ae485, ну тест показывает что универсальность FIND  дет ему не на пользу. Даже разовый поиск  по ляму строк идет не быстро, что из интерфейса, что из макроса. Предположу, что те возможности по поиску с форматом, в формулах, частично или ... реализованы таким образом, что дают такой эффект. VLOOKUP лишена многого лишнего. Но это мое предположение.
По вопросам из тем форума, личку не читаю.
 
 
 
#14
04.12.2023 06:50:14
Jack Famous,
судя по написанному, я понимаю, что UDF вовсе не нужна, а нужен только результат
Цитата
Jack Famous написал:
если делать полный аналог штатной ВПР, то обогнать её не удастся
Цитата
ae485 написал:
Остается только вставлять формулы ВПР сохранять вместо формул найденные значения.
ae485,
и все-таки, какой последний аргумент у Вашей формулы ВПР?
Изменено: evgeniygeo - 04.12.2023 06:52:12
 
 
 
#15
06.12.2023 13:14:04
БМВ, Дмитрий(The_Prist) Щербаков, Jack Famous, evgeniygeo, добрый день. Наблюдаю по переписке и я так понимаю, что Вы специалисты в VBA.


То есть если написать функцию UDF на VBA и протянуть её на 20000 строк с целью поиска вхождения по списку в 900000 строк, то ВПР никак не обогнать? Всё правильно? Подскажите пожалуйста!
 
 
 
#16
06.12.2023 13:17:11
PoToLoK™,
думаю, что в таком контексте да (тем более если ВПР с ИСТИНА в последнем аргументе), но зачем UDF? в большинстве случаев в жизни нужен только результат (по собственному опыту)
Изменено: evgeniygeo - 06.12.2023 13:17:47
 
 
 
#17
06.12.2023 13:22:22
evgeniygeo, спасибо за ответ. Просто интересно в качестве эксперимента. Можно ли написать функцию UDF, которая будет быстрее ВПР при аргументе ЛОЖЬ, ведь ИСТИНА ищет больше по принципу округления! Или это плохая идея и ничего не выйдет, т.к. ВПР использует потоки?
 
 
 
#18
06.12.2023 13:37:10
Цитата
PoToLoK™ написал:
ведь ИСТИНА ищет больше по принципу округления!
это не совсем так:
Цитата
БМВ написал:
Дмитрий(The_Prist) Щербаков  написал:Тут еще второй момент есть: ВПР ищет практически мгновенно данные на больших объемах, если последний аргумент опущен или равен ИСТИНА(TRUE)Дима, ну так для этого данные должны быть отсортированы, то есть дело в возможности применени этого метода, а не то результат будет быстрым , но неверным :-)
для интереса можете посмотреть тесты из статьи:
https://www.planetaexcel.ru/techniques/25/11129/

P.S. ко мне это точно не относится:
Цитата
PoToLoK™ написал:
и я так понимаю, что Вы специалисты в VBA.
Изменено: evgeniygeo - 06.12.2023 13:41:01
 
 
 
#19
06.12.2023 13:50:54
evgeniygeo, спасибо за ссылку, ознакомился. Ну раз Вы скромный специалист, то я подожду БМВ, Дмитрий(The_Prist) Щербаков, Jack Famous. Интересно, что они скажут и вообще ответят на такой вопрос) Может в данной ситуации можно сделать UDF хотя-бы сопоставимой по скорости с ВПР для данного примера (База 900000 строк и 20000 строк исходные данные).
 
 
 
#20
06.12.2023 14:34:02
Меня особо ждать нечего, я с VBA почти не работаю, но
1. как писал ранее, встроенные функция ( не формулы с их использованиями, а именно функции) оптимизированы и быстры, да и продолжают оптимизироваться.
2. при разовом применении врядли удастся обогнать встроенную функцию аналогом UDF
3.  а вот "База 900000 строк и 20000 строк исходные данные' тут вопрос творческий, ибо в случае отсортированных данных можно сильно ускорить поиск не проходя уже просмотренные куски данных, а вот будет ли оно быстрее - не скажу
4. а вот сделать это запросом SQL возможно будет и не так долго.
По вопросам из тем форума, личку не читаю.
 
 
 
#21
06.12.2023 14:41:07
БМВ, спасибо за Ваш ответ! Наверно Дмитрий(The_Prist) Щербаков, Jack Famous, скажут мне тоже самое и мой вопрос возможно бессмысленный, но всё равно спасибо за обратную связь)
 
 
 
#22
06.12.2023 16:21:52
Цитата
PoToLoK™: мой вопрос возможно бессмысленный
скорее, он не содержит необходимых деталей, т.к., например, можно написать UDF, заточенную на работу с одним (пусть и динамическим) диапазоном и такая UDF вполне может быть быстрее ВПР.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#23
06.12.2023 21:02:26
Кстати весьма интересна разница по работе ВПР с открытым и закрытым файлом. Даже разовый вызов конкретно озадачил функцию. SQL наклацанный конечно не мгновенно,
Код
SELECT `Sheet1$_1`.ID, iif(`Sheet1$`.ID Is Null,'Нет','Есть') AS 'result'
FROM {oj `C:\temp\20000.xlsx`.`Sheet1$` `Sheet1$_1` LEFT OUTER JOIN `C:\temp\900000.xlsx`.`Sheet1$` `Sheet1$` ON `Sheet1$_1`.ID = `Sheet1$`.ID}

но выдал результат по 20 000 из 900 000 где файл с 900 000  был закрыт, за относительно нормальные 2 мин. ВПР тужился 4 мин и я остановил на 60% расчета. К несчастью SQL еще и отсортировал , на что тоже ушло время.
генерировал 900000 формулой
="A"&RANDBETWEEN(1:900000)
20000
=MID("ab";RANDBETWEEN(1;2);1)&RANDBETWEEN(1:900000)
Теперь макрос в лоб на словаре
Код
Sub get_from()
t = Timer
Sub get_from()
t = Timer

Set Dict = CreateObject("Scripting.Dictionary")
a = Workbooks("20000.xlsx").Sheets(1).Range("a2:a20000").Value2
ReDim c(1 To UBound(a, 1), 1 To 1)
B = Workbooks("900000.xlsx").Sheets(1).Range("a2:a900000").Value2
For j = 1 To UBound(B)
If Not Dict.Exists(B(j, 1)) Then Dict.Add B(j, 1), B(j, 1)
Next
Debug.Print Timer - t
For i = 1 To UBound(a)
If Dict.Exists(a(i, 1)) Then
c(i, 1) = "Есть"
Else
c(i, 1) = "нет"
End If
Next
Debug.Print Timer - t
Workbooks("20000.xlsx").Sheets(2).Range("f2:f20000") = c
Debug.Print Timer - t
End Sub


заполнение словаря
 31,85938
далее на  32,94531  заполнен массив результата
и на  33,01563  данные выгружены.

То есть 97% времени тратим на подготовку. а потом быстро все делаем. Но если это делать через UDF и каждый раз заполнять словарь, то очевиден плачевный результат.
Изменено: БМВ - 06.12.2023 23:24:00
По вопросам из тем форума, личку не читаю.
 
 
 
#24
07.12.2023 07:16:33
Цитата
написал:
скорее, он не содержит необходимых деталей, т.к., например, можно написать UDF, заточенную на работу с одним (пусть и динамическим) диапазоном и такая UDF вполне может быть быстрее ВПР.
По поводу деталей я же описал выше, что есть таблица на 900000 строк и необходимо в таблице на 20000 строк проверить наличие из первой таблице по принципу ВПР. То есть я скопировал пример ТС и в качестве эксперимента поинтересовался.

По поводу работы с динамическим диапазоном! Так ВПР и работает с одним диапазоном! Ей нужен только 1 столбец для поиска, а далее по индексу найденной строки и ранее объявленного столбца выдергивается значение. Ваша цитата непонятна и что Вы имели ввиду?
 
 
 
#25
07.12.2023 07:21:24
Должен признаться - это был эксперимент, с целью понять   творческие и технические возможности специалистов Excel и VBA на данной   площадке.
Цитата
написал:
если делать полный аналог штатной ВПР, то обогнать её не удастся
Я думаю с Вами все согласны, раз никто не высказался против! Но это    утверждение некорректно! Для теста требуется лишь реализация с версией    ЛОЖЬ в последнем аргументе (то есть полный перебор до нахождения  первого   вхождения и возврат результата по индексу). Если использовать  решение   "в лоб", то конечно не удастся обойти ВПР и это очевидно, но  есть пару   моментов, благодаря которым можно не только сравняться, но и  опередить   ВПР!
  1. Использование статического сегмента памяти (Кеш диапазона в локальный массив на уровне модуля);
  2. Сортировка кешируемого массива;
  3. Бинарный поиск и выдача результата!
Описание алгоритма и код

Что касается производительности кода...

ВПР на 20000 строк с поиском в базе 1048576 строк ~ 3 мин. 22 сек.
UDF на 20000 строк с поиском в базе 1048576 строк ~ 10 сек. (c учетом подгрузки данных)

Цитата
P.S. Никого не хочу задеть или обидеть, просто ещё раз убедился, что человеку свойственно делать ложные теоретические доводы и предположения!
Даже когда человек крутиться в своей сфере не один год или даже десятилетие!
Изменено: PoToLoK™ - 07.12.2023 07:36:11
 
 
 
#26
07.12.2023 07:47:58
PoToLoK™, Что-то начало смахивать на ИИ и чтоб было понятно, то что написано №25 просто было написано и в №23 . Если говорить что UDF функция листа, то статика - может боком выйти, как с точки зрения изменения данных, о котором буфер не знает, так и с точки зрения расхода памяти, где сидит этот временный массив.

Ну и теперь все вот это сравниваем с первым применением единственного вызова если все эти QuickSort и BinarySearch обгонят ВПР, тогда и продолжим.
По вопросам из тем форума, личку не читаю.
 
 
 
#27
07.12.2023 07:59:16
БМВ, спасибо за комплимент с ИИ, но дело в употреблении и изучении технического лексикона и материала!

Цитата
написал:
может боком выйти, как с точки зрения изменения данных, о котором буфер не знает, так и с точки зрения расхода памяти, где сидит этот временный массив.
Вы сейчас не шутите? Где сидит временный массив... В статическом сегменте памяти или проще - модуле. Где ещё он может сидеть и про расход памяти уже не актуально! Эта байка из 20 века и сейчас пару десяток мегабайт в памяти - это не проблема!

По поводу изменения данных... Что мешает добавить функцию быстрой конкатенации строк и пропуск этой строки через Hash-функцию с последующей сверкой предыдущего Hash-а?

Результаты производительности кода Я предоставил, да и по коду можно понять, что выполнение будет не долгим! Вам действительно приложить пример Excel, чтобы Вы убедились? Вы действительно думаете, что я из головы эти числа придумал)?
Изменено: PoToLoK™ - 07.12.2023 08:09:48
 
 
 
#28
07.12.2023 08:06:48
Цитата
написал:
то что написано №25 просто было написано и в №23
И что Вы написали такого, что схоже с моим сообщением, покажите пожалуйста!
 
 
 
#29
07.12.2023 08:15:32
PoToLoK™,
можно пример с кодом?  :)
 
 
 
#30
07.12.2023 08:20:36
evgeniygeo, день добрый, да конечно! Правильный подход к вопросу👍. Никакого трёпа, тупо сюды код давай, а потом порешаем и обсудим😁

Сейчас только добавлю генератор исходных данных, а то Excel тяжеловат для подкрепления (~ 9 МБ)!
 
 
 
Страницы: 1 2 3 След.
Наверх