Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Быстрый ввод длинных штатных функций Excel, Описание способов быстрого ввода (с помощью макросов) длинных формул с повторяющимися переменными
 
#1
22.09.2017 19:56:36
Доброго вечера, Планетяне!
Я часто пользуюсь ИНДЕКС+ПОИСКПОЗ со вложенными проверками, но вот вводить её очень запарно каждый раз.
На хабре нашёл способ, как сочинять UDF на основании штатных формул листа (кстати, там очень крутые ссылки на всякие интересности есть)

А интересно следующее:
1. Есть функция (со вложенными или без), в которой некоторые ссылки на ячейки/диапазоны повторяются
2. Как быстро ввести такую функцию, имея её "тело" в макросе и ЕДИНОЖДЫ задав все переменные?
Макрос из файла-примера:
Изменено: Jack Famous - 22.09.2017 19:59:11
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#2
22.09.2017 20:42:02
Тут немного другое надо учесть, какой диапазон: статический, динамический. То же самое поисковый критерий. Плюс точный или нет поиск. Так то придумать несложно, но всё равно надо будет либо ручками дописывать и убирать доллары, или вводить в функцию новые переменные которые будут описывать эти состояния.
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 
 
 
#3
22.09.2017 20:46:59
Alemox, а если используется ТОЛЬКО в "умных" таблицах, совпадение всегда точное и диапазоны будут указываться корректно (выделением поля целиком в умной таблице)? Иными словами, формула после ввода макросом визуально не должна отличаться от того, если бы руками как обычно вводил.
AddressLocal тут не помощник, как я понял((
Кстати! У Николая Павлова в PLEX'е есть что-то подобное. Можно добавить в библиотеку свою формулу и заменить ссылки на ячейки переменными (выглядеть, конечно, будет не так, как руками, но тоже вариант). Может это как-то поможет найти решение? Код из PLEX'а видел, но там формы друг на друга ссылаются и я ничего не понял(((
Изменено: Jack Famous - 22.09.2017 21:20:55
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#4
24.09.2017 16:45:17
Доброго вечера!
Сочинил UDF на основе штатных функций Excel (для сохранения производительности), но она не работает.
Подскажите, как её исправить, чтобы она заработала и можно ли "ускорить" подобную функцию, с учётом всех проверок?
UDF
Изменено: Jack Famous - 24.09.2017 17:29:46
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#5
24.09.2017 21:22:43
Код
Function BPR3(What As Range, Where As Range) As Range
  If WorksheetFunction.CountIf(Where.Columns(1), What) = 0 Then Exit Function
  Set BPR3 = Where.Columns(1).Find(What, Where.Cells(1), xlValues, xlWhole).Offset(0, 1)
End Function
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#6
25.09.2017 08:18:26
Ігор Гончаренко, Игорь, спасибо большое))) суть понял - допилю сам)) то есть подтягивать через поиск и смещение быстрее, чем прямой ИНДЕКС+ПОИСКПОЗ?

ps: всё-таки непонятно, почему на формулах листа не сработало…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#7
29.09.2017 13:04:10
Доброго дня! Подскажите, что не так с этим кодом? Хочу сделать UDF целиком на функциях рабочего листа.

Код
Function ПодтянутьСПроверками(что_ищем As Variant, ByRef где_ищем As Range, ByRef откуда_берём As Range) As Variant
ПодтянутьСПроверками = WorksheetFunction.IfError(WorksheetFunction.IF(Application.WorksheetFunction.Or(что_ищем = "", _
WorksheetFunction.Index(откуда_берём, WorksheetFunction.Match(что_ищем, где_ищем, 0)) = 0), "", _
WorksheetFunction.Index(откуда_берём, WorksheetFunction.Match(что_ищем, где_ищем, 0))), "")
End Function
Изменено: Jack Famous - 29.09.2017 13:12:38
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#8
29.09.2017 13:07:33
А что НЕ ТАК с этим кодом? Ошибки какие-то выпадают или еще что? Как Вы ее применяете? Файл-пример для тестирования не помешает
Согласие есть продукт при полном непротивлении сторон
 
 
 
#9
29.09.2017 13:13:09
Прикрепил в предыдущий пост. Синтаксис =АналогВПР([@[ЧТО искать]];справочник[ГДЕ искать];справочник[ПОДТЯНУТЬ отсюда]). Ошибка #ЗНАЧ
Изменено: Jack Famous - 29.09.2017 13:14:30
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#10
29.09.2017 13:39:41
А где Вы в WorksheetFunction нашли функцию If ? Используйте IIf ,но это не функция листа  :)
Код
Function ПодтянутьСПроверками(что_ищем As Variant, ByRef где_ищем As Range, ByRef откуда_берём As Range) As Variant
With Application.WorksheetFunction
    ПодтянутьСПроверками = .IfError(IIf(.Or(что_ищем = "", _
                            .Index(откуда_берём, .Match(что_ищем, где_ищем, 0)) = 0), "", _
                            .Index(откуда_берём, .Match(что_ищем, где_ищем, 0))), "")
End With
End Function
Изменено: Sanja - 29.09.2017 13:42:47
Согласие есть продукт при полном непротивлении сторон
 
 
 
#11
29.09.2017 13:48:13
Sanja, большое вам спасибо! Сейчас ещё одну проверку добавлю (ЕСЛИОШИБКА не срабатывает. При отсутствии "что_ищем" в "где ищем" выводит #ЗНАЧ) и выложу все варианты))
подскажите, пожалуйста, как сделать проверку быстродействия UDF'ок в макросе? Ну, чтобы не строить огромные таблицы, а счётчик, цикл и время.
Изменено: Jack Famous - 29.09.2017 13:52:42
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#12
29.09.2017 13:52:35
Про быстродействие так то не по теме.
Обсуждалось тут
Согласие есть продукт при полном непротивлении сторон
 
 
 
#13
29.09.2017 13:53:45
Цитата
Sanja написал:
не по теме
согласен)) спасибо за ссылку — думаю, разберусь))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#14
29.09.2017 15:21:44
Sanja, доброго времени, крутая искалка. Подскажите, а можно ли ее наворотить чтоб она подтягивала например если два и более значения ну типа массив индекс поискпоз мин. Спасибо
 
 
 
#15
29.09.2017 15:39:00
Это не моя 'искалка', это Jack Famous, 'наворотил'  :) . Я только рихтовал
Согласие есть продукт при полном непротивлении сторон
 
 
 
#16
29.09.2017 15:46:50
Вот все варианты. Мне больше всего подошёл вариант через Find и Offset, который Игорь предложил. Думаю, что и по скорости должен быть быстрее. Sanja, вам большое спасибо за помощь и объяснения! Alemox, благодарю за участие.
Любые подсказки по улучшению быстродействия и прочего только приветствуются!
В файле сравнительная таблица. Чётко обходить ошибки в 2х вариантах так и не смог (ни ПОИСКПОЗ не проверить, ни ЕСЛИОШИБКА)

UDF


P.S.: alex1210, создавайте отдельную тему. Ваш вопрос за рамками обсуждения
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#17
29.09.2017 16:12:57
Jack Famous,По теме)))))) Вы же хотите сделать UDF. А вот крутая искалка бы была если б все значения находила))))
 
 
 
#18
29.09.2017 16:20:43
alex1210, посмотрите тут и тут. А вообще UDF по многоразовому ВПР есть в PLEX'е и на просторах интернета
Цитата
alex1210 написал:
крутая искалка бы была если б все значения находила
мне не нужны все значения  ;)
Изменено: Jack Famous - 29.09.2017 16:22:52
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#19
29.09.2017 17:36:47
Всё-таки добил)) итак, осталось 2 UDF'ки: на Match (по мотивам хабра) и на Find (по совету Ігоря Гончаренко).
Различаются они в том, что Match по результату работы абсолютно подобна (во всяком случае, не нашёл различий) связке функций, как в условии темы, в то время как Find числа-как-текст в "что_ищем" воспринимает, как обычные числа. По-моему, обе эти функции могут быть полезны, в зависимости от задачи.
В файле всё хорошо видно.

UDF'ки
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#20
03.10.2017 13:07:05
Доброго дня, Планетяне!
Вот ещё подобная UDF'ка, но для СУММЕСЛИ
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
Страницы: 1
Наверх