Страницы: 1 2 След.
RSS
Тесты. Новые функции Excel 365 (по подписке) против VBA, Tests. New Excel 365 WorkSheetsFunctions vs VBA
 
Приветствую!
Готовлю тестовый стенд для примера отсюда
Сам новых функций не имею, так что тестеры приветствуются (Бахтиёр в деле)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Алексей, хоть условия задачи озвучь :) Потому что мое мнение - функции листа при равных условиях в большинстве случаев выигрывают в скорости против такого же функционала в VBA.
Я про то, чтобы не получилось, что сравниваем скорость работы функций листа, вызванных из VBA против скорости встроенных в VBA возможностей. Здесь могут быть нюансы. Например, у нас используется в любом случае прослойка в виде application.worksheetfunction, для вызова которой VBA приходится общаться к сторонней библиотеке(Excel Object Library).
У меня офис 365 и при наличии времени и интереса какие-то вещи могу потестировать.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков, привет, Дим!  :)
Цитата
Дмитрий(The_Prist) Щербаков: функции листа при равных условиях в большинстве случаев выигрывают в скорости против такого же функционала в VBA
согласен
Цитата
Дмитрий(The_Prist) Щербаков : сравниваем скорость работы функций листа, вызванных из VBA против скорости встроенных в VBA возможностей
именно это и сравниваем, чтобы понять, есть ли польза новых функций для VBA-разработки. Я думаю, что словари обойдут по скорости извлечения уникальных, а вот с сортировкой сложнее. Встроенных вариантов сортировки МАССИВА (на листе-пожалуйста) на VBA или нет или никуда не годятся. Буду использовать рекурсивный сортер 1x-массива в редакции от Anchoret (функции на его основе) — самый стабильный и универсальный вариант
Разумеется, будет сравнение и с использованием супербиблиотеки от Виталия — чтобы в очередной раз показать удобство и скорость
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Добрый день.
В диапазоне A1:D500000 данные (смотрите на скрине маленький кусок):
Сам файл скину на файлообменник (11 Mb)

Макрос otbor_unikal:
Отбор уникальных строк ПО ВСЕМУ МАССИВУ с охватом 4-х столбцов
Вход: (1 to 500000, 1 to 4)
Исход: (1 to 61719, 1 to 4)
Время: 1,98 секунд
Код:
Скрытый текст

Макрос sort_1_urovnevaya:
Осуществляет ПО ВСЕМУ МАССИВУ 1-уровневую сортировку по возрастанию по 1-му столбцу
Вход: (1 to 500000, 1 to 4)
Исход: тот же размер
Время: 1,54 секунд
Код:
Скрытый текст

Макрос otbor_unikal_i_sort_4_urovnevaya:
Осуществляет отбор уникальных с учётом 4-х столбцов и осуществляет 4-х уровневую сортировку по:
1) Сначала  по 1-му столбцу - по возрастанию
2) Потом по 2-му столбцу - по убыванию
3) Потом по 3-му столбцу - по возрастанию
4) Потом по 4-му столбцу - по убыванию
Вход: (1 to 500000, 1 to 4)
Исход: (1 to 61719, 1 to 4)
Время: 2,18 секунд
Код:
Скрытый текст
Изменено: Бахтиёр - 24.09.2021 14:44:29
 
Начинайте отсчёт времени ПОСЛЕ mas=….Value
Получение массива с листа является общей операцией для методов сравнения

Заодно можете кракозябры поправить при копировании с НЕпереключенной латиницей  ;)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
Начинайте отсчёт времени ПОСЛЕ mas=….Value
а еще лучше попробовать передавать сразу диапазон(ведь с листа передаем именно так):
Код
mas2 = WorksheetFunction.Unique(ActiveWorkbook.Sheets(1).Range("A1:D500000"))
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
Дмитрий(The_Prist) Щербаков: еще лучше попробовать передавать сразу диапазон
не соглашусь, потому что рассматривается использование новых функций листа в VBA, а там мы работаем с массивами
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Бахтиёр написал:  Сам файл скину на файлообменник (11 Mb)
https://drive.google.com/file/d/13TG4gnvDz3gl2Zel0R4EAVBrnnWD8kX3/view?usp=drivesdk

отсюда можно скачать файл
Изменено: Бахтиёр - 22.09.2021 14:49:26
 
Цитата
Jack Famous написал:
а там мы работаем с массивами
ну это Ваши проблемы :) Функции-то могут работать и с диапазонами. Притом при вызове из VBA тоже. Поэтому все честно, в этом и соль. Надо же понимать, дает ли прирост в скорости такая передача параметра или наоборот.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
Дмитрий(The_Prist) Щербаков:  надо же понимать, дает ли прирост в скорости такая передача параметра или наоборот.
Даёт преимущество 0,2-0,3 секунд
Изменено: Бахтиёр - 22.09.2021 17:17:19
 
Jack Famous, вы в изначальной теме в Курилке задавали вопрос насчёт того, что после удаления дубликатов вроде должно остаться всего 28 строк. Но это не так. Имеется ввиду удаление дубликатов строк с учётом значений всех 4-х столбцов. В таком случае, после удаления дубликатов из 500 тыс строк остаётся >60 тыс строк
 
Цитата
Дмитрий(The_Prist) Щербаков: Функции-то могут работать и с диапазонами
ну если И с диапазонами, И с массивами, то согласен - так корректно  :)

Цитата
Бахтиёр: Имеется ввиду удаление дубликатов строк с учётом значений всех 4-х столбцов
учёл — КЛЮЧОМ считается сцепка по 4ём столбцам

Отбор уникальных (сцепка по 4ём столбцам)
Бахтиёр, жду ваших замеров по моему коду - должно быть быстрее, чем у меня
Изменено: Jack Famous - 22.09.2021 15:34:15
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous,  Ваш код - 1,18 секунд
Мой код, немного изменённый с учётом:
Цитата
Jack Famous:  у если И с диапазонами, И с массивами, то согласен - так корректно  
Код
Sub otbor_unikal()
    t = Timer
        mas2 = WorksheetFunction.Unique(Range("A1:D500000"))
    Debug.Print Round(Timer - t, 2) & " - " & "Отбор уникальных"
End Sub
Мой результат - 1,98 секунд.

Код Виталия не смог запустить, там кажется сначала нужно установить нужно кое-что.

Удивлюсь, если обойдёте и по 4-х уревневой сортировке :-)
 
Цитата
Бахтиёр: Ваш код - 1,18 секунд…Мой результат - 1,98 секунд
отрыв более 40% — и это без супербуста от Виталия (там раза в 3 отрыв будет)  8)
Цитата
Бахтиёр: там кажется сначала нужно установить нужно кое-что
скачать с его сайта бесплатную надстройку (ссылку я в коде указал) и установить

Интересно, что прямая передача диапазона для новой функции ускоряет процесс  :idea:
Цитата
Бахтиёр: Удивлюсь, если обойдёте и по 4-х уревневой сортировке
ну с библой от Виталия у вас точно шансов нет, а вот без неё придётся выкручиваться  :D

В любом случае, новый движок мне нравится - обычно функции листа проигрывают намного сильнее
Пара строк кода и вы делаете то же самое, для чего мне пришлось использовать годы опыта в программировании - это впечатляет, конечно  :idea:
Радует (как человека, потратившего время на изучение) только, что скорость пока хуже  :D
Изменено: Jack Famous - 22.09.2021 16:41:27
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
Интересно, что прямая передача диапазона для новой функции ускоряет процесс
вполне прогнозируемо, т.к. в таком случае в функцию идет прямая передача данных из памяти, без доп.преобразований с нашей стороны. Все преобразования к массиву в случае необходимости будут произведены самой функцией. Именно поэтому я и написал попробовать это сделать.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
Дмитрий(The_Prist) Щербаков: в таком случае в функцию идет прямая передача данных из памяти, без доп.преобразований с нашей стороны
я бы понял, если бы мы УЧИТЫВАЛИ время получения массива из диапазона (типа функция сделает это быстрее), но мы, как я понял, не учитываем, а значит "готовый" массив ей не нравится и она ещё как-то его должна "приготовить"  :)

Может быть попробовать переменную вариативного массива mas() вместо просто вариативной mas?…
Изменено: Jack Famous - 22.09.2021 16:24:03
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Совсем нет времени поучаствовать.
Цитата
Бахтиёр написал:
Код Виталия не смог запустить, там кажется сначала нужно установить нужно кое-что.
Цитата
Jack Famous написал:
скачать с его сайта бесплатную надстройку (ссылку я в коде указал) и установить
Алексей я тебе сбрасывал мобильный вариант моей библы, который уже в файле .xlsb, её не нужно пользователю устанавливать, используй его.
Изменено: bedvit - 22.09.2021 19:28:59
«Бритва Оккама» или «Принцип Калашникова»?
 
bedvit, да знаю я - изучаю)
В твоём работает, а переношу в новый - не робит)
Завтра отпишусь тебе утром)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Переноси с листом storage (она там хранится). А ты как делаешь?
«Бритва Оккама» или «Принцип Калашникова»?
 
Код
Sub ADOunic()
t = Timer
Dim oRecordSet As Object, oConn As Object
    sTblQuery = "[" & ActiveWorkbook.Sheets(1).Name & "$A:D]"
    Set oRecordSet = CreateObject("ADODB.Recordset")
    Set oConn = CreateObject("ADODB.Connection")
    'oRecordSet.CursorLocation = 3
    sSQL_text = "SELECT DISTINCT * FROM " & sTblQuery & "ORDER BY F1,F2 DESC ,F3,F4 DESC"
    sConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=NO"";"
    oConn.Open sConnStr
    oRecordSet.Open sSQL_text, oConn
    
    ActiveWorkbook.Sheets(1).Range("F1").CopyFromRecordset oRecordSet
Debug.Print Timer - t
End Sub


сравнить не могу  но на моем 1.59 включая время выгрузки на лист.
Изменено: БМВ - 22.09.2021 20:59:42
По вопросам из тем форума, личку не читаю.
 
БМВ, я так понимаю, это и уникальные и сортировка?
Что-то пошло не так, т.к. выгружено в 2 раза (чуть больше, чем в 2: 30 817 вместо 61 719) МЕНЬШЕ строк
Код для раннего связывания
Время, конечно, впечатляет — в 2 раза быстрее супербиблы и в 3 раза быстрее штатных словарей. Осталось разобраться с корректностью и замерить снова
Есть, конечно, неудобство в том, что нужно транспонировать массив и границы в нём от нолей, но решается очень быстро (12 миллисекунд)
Изменено: Jack Famous - 23.09.2021 10:49:29 (Изменил код и текст с учётом получения массива (пост БМВ ниже))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
это и уникальные и сортировка?
- да

Цитата
Jack Famous написал:
хотелось бы всё-таки массив получить
Код
Arr=ADO_RS.getrows
Изменено: БМВ - 23.09.2021 10:05:23
По вопросам из тем форума, личку не читаю.
 
Дополнил предыдущее сообщение с учётом получения массива. Остаётся главная проблема - НЕ ТО количество строк

И ещё: дядь Миш, а можно работать ADO с массивами или только с диапазонами?
Хочу сформировать массив сам и передать в ADO - мне нравится такая скорость. Если можно, то я тему отдельную создам
Изменено: Jack Famous - 23.09.2021 10:48:10
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
НЕ ТО количество строк
по поводу результата, нужно проверять. сравнить что там не входит в результат. Может ограничения  ADO вылезают какие. сделай на источнике в 65000 строк и сравни рез.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ: Может ограничения  ADO вылезают какие
мда…вот из-за таких "может быть", я его и не использую  :sceptic:
Нам нужно полмиллиона обрабатывать, поэтому, к сожалению, дисквалификация по причине невыполнения условий
Изменено: Jack Famous - 23.09.2021 11:46:41
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
может быть
на этот раз медведь напахал. так не делается :-(
По вопросам из тем форума, личку не читаю.
 
БМВ, я бы поборолся ещё, если бы знал, а так точно не вариант))
Ну допустим, поймём мы, в чём проблема, исправим, а где гарантия, что дальше такого не повторится? Такой ненадёжный инструмент мне не нужен
Изменено: Jack Famous - 23.09.2021 12:04:04
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Файл (с макросами, но без данных)
Модули с макросами
Результаты и выводы
Изменено: Jack Famous - 27.09.2021 11:07:09 (Корректировка логических ошибок)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Есть один ньюанс.
Если начать отсчёт времени ПЕРЕД получением значения диапазона ячеек в переменную массива (mas=Range(...).Value) --> время работы кода (VBA  инструменты) будет увеличено.
А если начать ПОСЛЕ ..., то --->  время работы будет меньше.

А с WorksheetFunction как бы наоборот:
Такая структура:
Код
Sub ddd()
mas=Range(...).Value
t=Timer
     mas2=WorksheetFunction,Sort(mas)...
...
End Sub 
работает медленнее,

А такая:
Код
Sub ddd()
t=Timer
     mas2=WorksheetFunction.Sort(Тут сразу диапазон)
...
End Sub 
быстрее.

И на вчерашнем тестировании, и сегодня вы как бы сравниваете два кода, когда в вашем коде начало отсчёта начинается после mas=Range(...).Value, то есть высчитывается время  ушедшее только на сортировку,  а с WorksheetFunction время идёт и на получение данных.

Если я смог правильно объяснить, и если вы согласны с этим, ИМХО будет правильнее и в ваших кодах тоже учесть время на получение данных в массив.

Я правильно понимаю, что не будет кода на 4-х уровневую сортировку инструментами VBA? А любопытно, с рекурсивной функцией можно сделать 2-х ли 3-х уровневую сортировку?
Интересное наблюдение: функции листа СОРТ() И УНИК() ни на листе, ни на коде не работают с одномерными массивами, а вот ФИЛЬТР() работает.
Изменено: Бахтиёр - 23.09.2021 14:49:23 (добавил дополнительные вопросы)
 
Цитата
Бахтиёр: будет правильнее и в ваших кодах тоже учесть время на получение данных в массив
не согласен - при тестировании РАБОТЫ, время ПОЛУЧЕНИЯ ДАННЫХ учитываться не должно. Если вам быстрее передавать без массива, то, разумеется, вы можете так делать, но приводить к этому и мои способы — нельзя

Цитата
Бахтиёр: не будет кода на 4-х уревневую сортировку инструментами VBA?
есть пример штатной сортировки диапазона на листе: Sub TestSort_4Sheet()

Цитата
Бахтиёр: СОРТ() И УНИК() не на листе, не на коде не работают с одномерными массивами
возможно, вы просто не умеете их готовить  :D может есть функция из новых для преобразования или попробуйте поэкспериментировать с нижними границами. Как не работает - можно пример?

Меня, как всегда, библа Виталия выручает - она это реально МГНОВЕННО делает  :idea:
Изменено: Jack Famous - 23.09.2021 14:50:17
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Страницы: 1 2 След.
Наверх