Страницы: 1 2 3 4 След.
RSS
Размышление о C API Functions для Excel, Испольpование C API Functions для Excel в .xll
 
Всем привет!
Появилось немного свободного времени в пятницу и решил я запилить что-нибудь годное для Excel. Ну что бы не просто, а что нибудь полезное... После некоторых размышлений, стало ясно, что годного и полезного и так целый Гугл и Яндекс, а в голову приходит только посредственное, и вредное (коего еще больше в гугле), что как бы намекает на то, что в пятницу вечером можно заняться и другим...Но, взяв себя в руки, накатив чаю, было решено на C API залить какую-либо функцию в xll. Тыкнув пальцем в открытый Excel в 99% случаев офисных документов попадаем в ВПР() (VLOOKUP()), не стал исключением и мой палец.
Что в итоге:
По самописному VLOOKUP2() (надстройку  прилагаю, функция VLOOKUP2() лежит в категории функций (BedvitXLL)).
Из наблюдений:
1.Разработчика ВПР() свое дело знали хорошо, см. результаты теста.
2.Оптимизаций не производилось, перебором в лоб и на амбразуры (хотя и по байтам, что несколько быстрее)
2.Быстрее работает с явно указанным диапазоном, чем со всем столбцом(строкой)-в разы
3.Ест любого размера текст (сколько влезет в ячейку)
4.Ищет соответствие по позиции, поэтому можно сравнивать столбцы, строки, прямоугольные диапазоны.

Оборудование: Win7x64+Excel2016x64
Тест - 1)
-1004 строк по 32726 символов,
-все строки одинаковой длины в поиске и в массиве
-нужная строка в конце массива
-последняя строка отличается последним знаком

=ВПР($C1;$A$1:$B$1004;2;0) - не работает (строка до 255 символов включительно)
=ИНДЕКС($B$1:$B$1004;ПОИСКПОЗ($C1;$A$1:$A$1004;0)) - не работает (строка до 255 символов включительно)
=ПРОСМОТР(2;1/($A:$A=$C1);$B:$B) - 46 сек.
=VLOOKUP2($A:$A;$B:$B;$C1) - 60,48 сек
=ПРОСМОТР(2;1/($A$1:$A$1004=$C1);$B$1:$B$1004) - 15,87 сек
=VLOOKUP2($A$1:$A$1004;$B$1:$B$1004;$C1)-16,68 сек.

Тест - 2)
-10004 строк по 255 символов,
-все строки одинаковой длины в поиске и в массиве
-нужная строка в конце массива
-последняя строка отличается последним знаком

=ВПР($C1;$A$1:$B$10004;2;0) - 5,929688 сек
=ИНДЕКС($B$1:$B$10004;ПОИСКПОЗ($C1;$A$1:$A$10004;0)) - 5,933594 сек
=ПРОСМОТР(2;1/($A:$A=$C1);$B:$B) - 326,84 сек.
=VLOOKUP2($A:$A;$B:$B;$C1) - 366,41 сек
=ПРОСМОТР(2;1/($A$1:$A$10004=$C1);$B$1:$B$10004) - 10,69 сек
=VLOOKUP2($A$1:$A$10004;$B$1:$B$10004;$C1) - 9,49 сек.

Итого работа по скорости соизмерима с ПРОСМОТР(...), профита без оптимизаций нет.

Посему предлагаю рассмотреть, что нужно в действительности, что работало бы быстро и реализовывалось через функцию рабочего листа Excel.
Возможно получится сделать что-то действительно полезное :)
Изменено: bedvit - 05.08.2018 16:05:46
«Бритва Оккама» или «Принцип Калашникова»?
 
Можно использовать эти функции и в VBA через Application.Run.
Функции могут возвращать массив.
К примеру VLOOKUP2:
Код
Sub Test3() ' ТЕСТ EXE
Dim t, arr(10000, 2) As Long, arr2(10000, 2) As String, x
t = Timer
arr(9999, 2) = 50
arr2(9999, 2) = "Все работает!"
Debug.Print Application.Run("VLOOKUP2", arr, arr2, 50)
'ищем значение 50 в 9999 позиции третьего (у учетом нулевого) измерения, выводим соответствующие данные из второго массива
Debug.Print "Time = " & Timer - t
End Sub


У кого есть заветное желание заиметь собственную библиотечку с быстрой реализацией своей функции. Сортировка, хеш, СцепитьМассив() по условиям и т.д. полезные для форума? :)
Отберем претендента, сделаю реализацию.
«Бритва Оккама» или «Принцип Калашникова»?
 
bedvit, спасибо вам!!!  :idea:
Ваша функция регистрочувствительна или как ВПР?
Изменено: Jack Famous - 06.08.2018 15:28:06
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, регистрозависимая, т.е. регистр имеет значение. Расчет через байтовый массив и Юникод. Думаю должны зайти и китайские знаки, можно проверить. Все остальные рассмотренные функции ВПР(), ИНДЕКС(), ПРОСМОТР() регистронезависимые, т.е. не различают регистр. Выходит VLOOKUP2() единственная различает регистр, может этим и будет полезна (кроме функций на VBA, но они медленнее раз в 20 - тестировал на 8 ядерном ЦП).
Изменено: bedvit - 06.08.2018 17:30:40
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
VLOOKUP2() единственная различает регистр
вот это очень круто, т.к. даже ИНДЕКС+ПОИСКПОЗ нуждается в костылях для такого трюка. Спасибо!
Изменено: Jack Famous - 06.08.2018 17:54:18
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Вот такую функцию на VBA
Код
Function VLOOKUP2_VBA(ArreyFind As Range, ArreyResult As Range, FIND)
Dim arr1(), arr2(), i As Long, j As Long
arr1 = ArreyFind.Value
arr2 = ArreyResult.Value

For i = 1 To UBound(arr1)
    For j = 1 To UBound(arr1, 2)
        If arr1(i, j) = FIND Then VLOOKUP2_VBA = arr2(i, j): Exit Function
    Next
Next

End Function
обгоняет более чем в 20 раз (на 8 ядрах, т.к. VLOOKUP2 выполняется параллельно в 8 потоках).
Обе функции можно применять так:
«Бритва Оккама» или «Принцип Калашникова»?
 
bedvit, Подключить как обычную надстройку?
Цитата
У кого есть заветное желание заиметь собственную библиотечку с быстрой реализацией своей функции
у меня есть  :D
хочется всё, конечно, но, если уж выбирать, то вот мои кандидаты на "ускорение":
Список
Изменено: Jack Famous - 06.08.2018 18:04:58
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, да как обычная надстройка. Можно открыть как файл, можно установить в папке/списке для надстроек. Гляну ваш список) Был у меня ещё вариант функции, со входящей строкой, а выходящие данные по маске, часто спрашивают только русские буквы, цифры или что-то удалить из текста и  т.д. Но здесь нужно грамотно составить маску, что бы была универсальность. Может такие функции уже есть на VBA?
Изменено: bedvit - 06.08.2018 18:40:37
«Бритва Оккама» или «Принцип Калашникова»?
 
спасибо!  :)
Цитата
Может такие функции уже есть на VBA?
тут не подскажу
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, 6 функций это неплохо, нужно отобрать одну, и что бы форуму польза была. Пока только от вас варианты пришли. Расскажите, что они делают, в чем будет польза? Подождем и других участников. А пока отбираем что-то толковое, могу написать конкретно для вас, реализацию одной выбранной вами функции (как выше писал), но которая действительно нужна вам в таком исполнении. Подумайте, возможно такой пока у вас нет, но будет в будущем.
«Бритва Оккама» или «Принцип Калашникова»?
 
Немного матчасти для тех, кто заинтересуется темой, и будет коллегой в разработке и обсуждении некоторых актуальных вопросов по данной тематике. Программирование с использованием API C в Excel
Изменено: bedvit - 08.08.2018 10:05:14
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
bedvit: могу написать конкретно для вас, реализацию одной выбранной вами функции … которая действительно нужна вам в таком исполнении
спасибо большое! Тут надо подумать)) проблема в том, что я не понимаю, где и в каких случаях API C даёт наибольший выигрыш. В копилке лежит тема о быстрой сортировке — до сих пор не разобрался (к своему стыду). Можно ли улучшить её вашими методами?
Цитата
bedvit: Расскажите, что они делают, в чем будет польза?
это "базовые" (как я их называю) функции. Лежат в личной надстройке и напрямую используются редко, но гораздо чаще — в составе других макросов. Иными словами, чтобы не прописывать в коде процедуру сортировки массива или отбора из него уникальных элементов, я просто вызываю нужную функцию, передаю в неё массив и, на выходе, получаю отсортированный или уникальный массив.
Изменено: Jack Famous - 08.08.2018 11:19:55
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
 тема о быстрой сортировке  — до сих пор не разобрался (к своему стыду). Можно ли улучшить её вашими методами?
гляну. Уникальный массив это как?
Итак намечается:
1. Сцепить ячейки по условию, в одну строку, с задаваемым разделителем.
2. Обработка строки по маске.
3. Сортировка массивов. Возможно и удаление дубликатов в этой же реализации. По дубликатами вопрос, удаляем всю строку, столбец или конкретное значение, где ищем дубликаты, везде, по какому либо измерению? Размерность массива - 2?
Изменено: bedvit - 08.08.2018 23:58:25
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
bedvit: Уникальный массив это как?
массив (обычно одномерный или двумерный, в котором одно из измерений = 1 (строка/столбец, забранные в массив с листа)), состоящий из уникальных элементов.
Цитата
bedvit: …удаляем всю строку, столбец или конкретное значение, где ищем дубликаты, везде, по какому либо измерению? Размерность массива - 2?
предлагаю лист затрагивать минимально (только, чтобы забрать значения, в массив, например), т.к. не думаю, что на листе будет выигрыш в скорости (ничем не обоснованная чуйка). Функция получает массив и возвращает такой же размерности, но без дублей в каждой размерности (между размерностями "дубли" могут быть). Либо упростить до 1х - 2х массивов.
Изменено: Jack Famous - 09.08.2018 10:58:23
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, уже месяц не могу вернуться к этой тематике, опять на работе завал. Тема не забыта, будет результат по теме - отпишусь.
«Бритва Оккама» или «Принцип Калашникова»?
 
bedvit, приветствую! У нас с вами какая-то ментальная связь, кажется  :D

Писал сегодня с утра Виктору (модератор) по поводу расширения этой (уже изменил название на более обобщённое) своей темы. Он не ответил и завтра скину код и файл с более десятка макросами на сравнение различных действий с массивами (на свой страх и риск).

Так вот - хотел вам в личку написать, чтобы глянули. Завтра после обновления буду рад вашим комментариям. Я был очень удивлён некоторым результатам… :sceptic:
Изменено: Jack Famous - 20.09.2018 22:21:37
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Как обещал вернулся к данной теме, написал сортировку одномерного массива. Можно протестировать, высказать свою точку зрения на данный механизм работы в VBA. Удобен или нет, стоит ли развивать это направление или все это сложно и нужно только единицам.
«Бритва Оккама» или «Принцип Калашникова»?
 
bedvit, мне это точно нужно  :D ещё бы сортировку двумерного массива и/или сортировку одномерных по одному из них. Ещё можно фильтрацию двумерного массива по столбцу или одномерных по массиву индексов (как тут)   :D

Проблема с внедрением надстройки. Двойной клик по файлу вызывает аварийное завершение Excel (был закрыт перед кликом), а при простом подключении, как надстройки, Excel не "видит" ваших библиотек  :(
Изменено: Jack Famous - 14.11.2018 10:41:35
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, по поводу индексов - думаю добавлю. По установке - библиотека х32 открывается  EXCEL32, x64 - EXCELx64. Именно битность Excel играет роль, а не WIN. Попробуйте, если не открывается пришлите в личку скриншот или описание ошибки.
«Бритва Оккама» или «Принцип Калашникова»?
 
bedvit, туго в личке со скринами))) Двойной клик по файлу (вне зависимости 32х или 64х) вызывает вот такую ошибку…
Не зависит, подключена надстройка или нет. При подключении надстройки, она не отображается в Project Explorer
Изменено: Jack Famous - 14.11.2018 13:59:05
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Да, ничего не ясно. Что написано в подробностях (показать подробности)? Какая система, офис (битность, версия)? Есть возможность протестировать на другой машине? У меня две тестовые машины с разной битность офиса, везде отрабатывает как надо. Не могу пока предположить в чем дело. Рабочий ПК?
«Бритва Оккама» или «Принцип Калашникова»?
 
Что написано в подробностях
Комп рабочий, но сисадмина нет (тип защиты тоже - я за него  :D ). Комп: Win7, x64. Данные по Excel на скрине
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Здесь пишут, что могут быть проблемы со сторонними надстройками (не могу утверждать, что это наш случай, но не исключено). Сможете, для эксперимента, отключить все надстройки, открыть только мою.
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
bedvit: Здесь  пишут
там про Word, вроде…
Всё отключил, но даблклик всё так же крашит и в эксплорере не отображается…
На другом компе всё норм — в Immediate увидел Sort
Сигнатура проблемы:
а нельзя просто ваш код в модуль своей надстройки забрать?

UPD: разобрался. Для запуска вашей надстройки необходимо, чтобы все макросы запускались по-умолчанию (что не есть гут). Есть возможность поправить?
И вопрос о том, как добавить ваши методы в свою надстройку также очень интересен  :)
Изменено: Jack Famous - 14.11.2018 17:26:56
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
там про Word, вроде…
Написано про Excel, но это не важно, видимо это локальная проблема на конкретном ПК, как я понял связанная с наличием других продуктов, надстроек VBA, макросов и возможно каких-либо еще подключенных сторонних продуктах. Возможно, рекомендации отсюда помогут: тыц,тыц, тыц, тыц. Проверил свою библу на 4 ПК, то же, как у вас, вин7, офис 16 х64 - все работает.
Цитата
Jack Famous написал:
а нельзя просто ваш код в модуль своей надстройки забрать?
Можно забрать библу, и ее подключать, когда необходимо (хранить в надстройке, когда нужно выгрузить, подключить, отключить, когда будет не нужна), к примеру, с помощью этого инструмента.
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
Jack Famous написал:
UPD: разобрался. Для запуска вашей надстройки необходимо, чтобы все макросы запускались по-умолчанию (что не есть гут). Есть возможность поправить?
Хм, странно, у меня на двух разных системах х64 rus и х32 eng, запускается все норм, см. скрины. Запускал и локально с ПК и из сетевой папки с разными настройками безопасности, в т.ч. указанными выше, на сообщение открыть надстройку - жмем да, и все ОК. У вас точно выключены все свои надстройки, макросы, события?
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
Jack Famous написал:
И вопрос о том, как добавить ваши методы в свою надстройку также очень интересен
очень просто. Открываем или устанавливаем надстройку - все, можно пользоваться. Пишем на VBA
Код
Dim a As Object: Set a = CreateObject("BedvitCOM.VBA") 'подключаем нужный класс из библиотеки
a.ArraySort x 'пользуемся методами класса
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
bedvit: на двух разных системах х64 rus и х32 eng, запускается все норм
ну тут сами понимаете, что возможны различные "удачные" комбинации настроек безопасности. Не суть  ;)
Цитата
bedvit: Открываем или устанавливаем надстройку - все, можно пользоваться. Пишем на VBA
это я у вас по ссылке читал. Как обойтись совсем без вашей надстройки (скопировать в свою)? В частности, как
Цитата
забрать библу, и ее подключать, когда необходимо (хранить в надстройке…)
? Как при этом обойтись без вашего файлового менеджера? Просто скопировать к себе в надстройку и вызывать в любой момент…
Изменено: Jack Famous - 15.11.2018 10:39:12
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
Как обойтись совсем без вашей надстройки
если имелось ввиду поделится кодом, то нет проблем, мне не жалко, но нужно понимать следующее:
1. вот упрощенная схема от этапа формирования алгоритм до формирования бинарника. Читаем Процесс компиляции программ на C++.
2. итого у меня есть три  кода моего алгоритма: 1.Написанный на С++. 2.На ассемблере 3.Бинарный. Одним из них я поделился. Два других без компилятора С++ и компановщика/линковщика, боюсь, вам не пригодятся.
Цитата
Jack Famous написал:
как забрать библу, и ее подключать, когда необходимо (хранить в надстройке…)
все просто, прилагаю пример (вложенная надстройка без кода, тестовая, выдает сообщение - просто замените её на рабочую) -  с рабочей надстройкой файл не пролез.
Цитата
Jack Famous написал:
Как при этом обойтись без вашего файлового менеджера? Просто скопировать к себе в надстройку и вызывать в любой момент…
написать свой ФМ :)
Или скопировать код с моего листа на ваш, в вашу надстройку и у вас этот лист станет файловым менеджером.
Изменено: bedvit - 15.11.2018 13:11:17
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
bedvit: поделится кодом
да нет же — код я всё-равно не пойму  :D

Смотрите:
1. У меня есть надстройка "*.xlam"
2. У вас есть надстройка "*.xll"

Вопрос: как мне скопировать коды/библиотеки/всё_нужное_для_того_чтобы_работать из вашей надстройки в свою?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Страницы: 1 2 3 4 След.
Наверх