Пользовательская функция

[yura613]

Например в Экселе есть такая функция как
ВПР(искомое_значение;таблица ;номер_столбца ;интервальный_просмотр)
и искомое_значение можно задвать как: А1(адресом ячейки), "тттт"(каким нибудь текстом) или А1+5

А как можно создать пользовательскую функцию в кторой значение можно задавать выражением такого типа:А1 + 5 ?

[GSerg]

as byte
as integer
as long
as single
as double
as string
as variant

Выбирай.

[yura613]

Те как я понял должно быть что то вроде:

Код: Выделить всё

function МояФункция(значение1 as string, область as range) as string
.
.
.
end function

вместо значение1 as string может быть и значение1 as integer и тд.?

[KL]

...ВПР(искомое_значение;таблица ;номер_столбца ;интервальный_просмотр)
и искомое_значение можно задвать как: А1(адресом ячейки), "тттт"(каким нибудь текстом) или А1+5

искомое_значение можно задвать как: А1, "текст", 22, ЛОЖЬ, А1+5, {22"text"\ЛОЖЬ}, A1:A50, хотя работа с массивами в первом параметре ВПР возможна лишь при вводе формулы с пом. CTRL+SHIFT+ENTER и обязательно в ранг совпадающий по длине и направлению вектора с искомой матрицей (частный случай для ВПР и ГПР).

Если нужно придать эти возможности параметру в пользовательской функции, то, думаю, лучше использовать тип Variant

Хотя неплохо бы взглянуть на "пользовательскую функцию". Если она нужна для использования в Excel, то имей в виду следующее:

1) в большинстве случаев пользовательские функции написанные в VBA в разы медленнее родных, написанных на C.

2) как правило пользовательские функции в Excel нужны если...
- то же невозможно сделать с пом. стандартных функций
- нужно избежать тяжеловесных формул массива (далеко не все формулы массива тяжеловесны, речь о тех, которые вынужденно повторяют переборы массивов больше раз, чем если бы это делалось в VBA)
- нужно избежать летучести формулы, и этого нельзя сделать с пом. стандартных нелетучих функций

Все остальные причины как правило в ущерб продуктивности.

[yura613]

функция моя выглядит вот так:

Код: Выделить всё

Function ПоискАдреса(область_для_поиска As Range, Критерий_для_поиска As String, _
Optional Имя_листа As String) As String

Dim myCell As Range


For Each myCell In область_для_поиска


If myCell.Value = Критерий_для_поиска Then

If Len(Имя_листа) > 0 Then

   ПоискАдреса= Имя_листа & myCell.Address(False, False)
Else
  ПоискАдреса= myCell.Address(False, False)
End If

   Exit Function
End If
 
Next myCell

End Function

Предназначена она для Экселя. Функция ищет заданное значение в указанной областе и возращает адрес искомого значения. Как реализовать эту задачу при помощи экселя я не догадался, поэтому напиасал эту функцию. Может быть есть и другое решение по-проще и по-лучше. У меня была загвоздка,я не мог сделать так чтобы критерий для поиска можно было задавать формулой или каким нибудь выражением. Теперь вроде работает.

[KL]

Предназначена она для Экселя. Функция ищет заданное значение в указанной областе и возращает адрес искомого значения. Как реализовать эту задачу при помощи экселя я не догадался, поэтому напиасал эту функцию. Может быть есть и другое решение по-проще и по-лучше. У меня была загвоздка,я не мог сделать так чтобы критерий для поиска можно было задавать формулой или каким нибудь выражением. Теперь вроде работает.

Этого-то я и боялся )))
Попробуй такую формулу:

=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(Критерий_для_поиска;$A$1:$A$10;0));"";АДРЕС(ПОИСКПОЗ(Критерий_для_поиска;$A$1:$A$10;0)-СТРОКА($A$1)+1;СТОЛБЕЦ($A$1:$A$10)))

=IF(ISERROR(MATCH(Критерий_для_поиска,$A$1:$A$10,0)),"",ADDRESS(MATCH(Критерий_для_поиска,$A$1:$A$10,0)-ROW($A$1)+1,COLUMN($A$1:$A$10)))

или такую:

=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(Критерий_для_поиска;A1:A10;0));"";ЯЧЕЙКА("address";ИНДЕКС(A1:A10;ПОИСКПОЗ(Критерий_для_поиска;A1:A10;0))))

=IF(ISERROR(MATCH(Критерий_для_поиска,A1:A10,0)),"",CELL("address",INDEX(A1:A10,MATCH(Критерий_для_поиска,A1:A10,0))))

только не пойму зачем нужно возвращать адрес ввиде стринга.

[yura613]

только не пойму зачем нужно возвращать адрес ввиде стринга.

это я просто пример из книги взял. там был пример функции,которая возвращает адрес столбца искомой ячейки, ну и я поменял под свои нужды. Я с теорией создания функций мало знаком,поэтому все или на примерах или интуитивно. А каким еще адрес может быть?
А поискпозиций я пробовал,но что то не получалось у меня, но я попробую обязательно этот пример.

[KL]

А каким еще адрес может быть?

В моем вопросе ключевое слово не "стринг", а "адрес", но вообще я бы понял если бы тебе нужна была ссылка, а вот адрес ввиде текста..? Разве что для последующего использования с функцией ДВССЫЛ, но это неразумно потому, что:
а) мы уже имеем ИНДЕКС(A1:A10;ПОИСКПОЗ(Критерий_для_поиска;A1:A10;0))
б) ДВССЫЛ - летучая (из трех функций способных возвращать ссылку ДВССЫЛ, ИНДЕКС и СМЕЩ только ИНДЕКС - не летучая)

[yura613]

я извиняюсь а что значит?

ДВССЫЛ - летучая

А в плане адрес стринговый, то да у меня возникла проблема и я потом использовал дополнительно функцию ДВССЫЛ. А если оставаться в рамках моей функции, то что надо было сделать чтобы не использовать потом дополнительно функцию ДВССЫЛ?

А вообще полная формула для которой я использовал эту функцию выглядит так:

=МАКС(СУММ(СМЕЩ(ДВССЫЛ(ПоискАдреса('Планы продаж'!$J$7:$DF$7;G$4;"'Планы продаж'!"));$A9;0):СМЕЩ(ДВССЫЛ(ПоискАдреса('Планы продаж'!$J$7:$DF$7;G$4;"'Планы продаж'!"));$A9;СУММ('Планы продаж'!$C$6:$C$7)-1))-ГПР(G$3;Help_table!$C$7:$AO$22;$A9-3;0)-СУММ(СМЕЩ(ДВССЫЛ(ПоискАдреса(Help_table!$C$7:$AO$7;G$4;"Help_table!"));$A9+13;0):СМЕЩ(ДВССЫЛ(ПоискАдреса(Help_table!$C$7:$AO$7;G$4;"Help_table!"));$A9+13;СУММ('Планы продаж'!$C$6:$C$7)-1));0)

и я думаю, благодаря некоторым исправлениям ее уже немного можно подсократить. Хотя хотелось бы ужать формулу как можно больше.

[Krasnaja Shapka]

я извиняюсь а что значит?

ДВССЫЛ - летучая

она пересчитывается каждый раз при изменении ЛЮБОЙ ячейки в книге (насколько я понял)

а по вашей формуле... вы бы еще сказали что она делать должна (или делает)...

[KL]

я извиняюсь а что значит?

ДВССЫЛ - летучая

А в плане адрес стринговый, то да у меня возникла проблема и я потом использовал дополнительно функцию ДВССЫЛ. А если оставаться в рамках моей функции, то что надо было сделать чтобы не использовать потом дополнительно функцию ДВССЫЛ?

А вообще полная формула для которой я использовал эту функцию выглядит так:

=МАКС(СУММ(СМЕЩ(ДВССЫЛ(ПоискАдреса('Планы продаж'!$J$7:$DF$7;G$4;"'Планы продаж'!"));$A9;0):СМЕЩ(ДВССЫЛ(ПоискАдреса('Планы продаж'!$J$7:$DF$7;G$4;"'Планы продаж'!"));$A9;СУММ('Планы продаж'!$C$6:$C$7)-1))-ГПР(G$3;Help_table!$C$7:$AO$22;$A9-3;0)-СУММ(СМЕЩ(ДВССЫЛ(ПоискАдреса(Help_table!$C$7:$AO$7;G$4;"Help_table!"));$A9+13;0):СМЕЩ(ДВССЫЛ(ПоискАдреса(Help_table!$C$7:$AO$7;G$4;"Help_table!"));$A9+13;СУММ('Планы продаж'!$C$6:$C$7)-1));0)

и я думаю, благодаря некоторым исправлениям ее уже немного можно подсократить. Хотя хотелось бы ужать формулу как можно больше.

1) Разбираться в твоей формуле сложно из-за отсутствия файла с исходными данными - вешай файл с объяснением конечной цели.

2) Даже невооруженным глазом видно что то, что ты наворотил - сплошная тафталогия.

3) Поскольку формула сама по себе сложная, то простота для пользователя уже на повестке дня не стоит, а значит нет [даже косметических] причин для создания пользовательской функции.

[yura613]

Если есть желание ознакомиться с файлом с исходными данными то я могу его скинуть, со всеми объяснениями. Но я показал эту форумлу, для того чтобы увидеть что при использовании встроенных функций формула станет еще больше. А так да погарячился немного вывешивая всю формулу. Лучше так:

Код: Выделить всё

=СУММ(ДВССЫЛ(ПоискАдреса(A2:A9;$A$2)):СМЕЩ(ДВССЫЛ(ПоискАдреса(A2:A9;$A$4));2;1))

тут мне без ДВССЫЛ не обойтись. А хотелось бы без этой функции.

[yura613]

Krasnaja Shapka формула которую я привел на самом деле без всяких наворотов выгдялит так:



где pl - это планы продаж
s - продажи
d - поставка
PlP - прогнозируемая поставка
формула должна рассчитывать какое количество товаро необходимо заказать на данной неделе

[KL]

А так да погарячился немного вывешивая всю формулу. Лучше так:

Код: Выделить всё

=СУММ(ДВССЫЛ(ПоискАдреса(A2:A9;$A$2)):СМЕЩ(ДВССЫЛ(ПоискАдреса(A2:A9;$A$4));2;1))

тут мне без ДВССЫЛ не обойтись. А хотелось бы без этой функции.

А вот так без извращений:

=СУММ(ИНДЕКС(A2:A9;ПОИСКПОЗ($A$2;A2:A9;0)):ИНДЕКС(B2:B9;2+ПОИСКПОЗ($A$4;A2:A9;0)))

и нелетучая

[yura613]

Ну вроде как работает, теперь попробую внедрить эту формулу в свой файл посмотрю, что получится.
Спасибо за помощь.

[yura613]

Ну допустим с предыдущей задачей проблем нет, а как можно реализовать поиск в экселе по двум параметрам без помощи макросов.

задача выглядит вот так:

[Genyaa]

По двум и более параметрам можно производить выборку с помощью автофильтра или расширенного фильтра (Меню-Данные-Фильтр).

Но встроенной функции для поиска по двум параметрам нет. Однако, если это необходимо, я создаю дополнительный столбец с собранными в одно значение нужными полями с помощью функции СЦЕПИТЬ. Тогда можно произвести поиск по этому комплексному (составному) параметру...

А если я правильно понял Вашу картинку, то на най нарисована ситуация, которую можно решить с помощью сводных таблиц.

[yura613]

Genyaa спасибо за подсказку, и в правду что то я про функцию сцепить совсем забыл.

[Krasnaja Shapka]

а как можно реализовать поиск в экселе по двум параметрам

{=СУММ(ЕСЛИ(($A21=$A$4:$A$14)*(B$20=$C$4:$C$14);$B$4:$B$14;0))}
вводи формулой массива, ctrl + shift + enter

p.s. строки и столбцы в этой формуле могут быть другими, так как ты выложил рисунок без названия столбцов и строк...

[yura613]

Krasnaja Shapka спасибо большое.

Genyaa а можно и без функции сцепить, просто через амперсант писать (&) и тоже работает.
=СУММЕСЛИ($D$4:$D$14;$A21&B$20;$B$4:$B$14)

[KL]

а как можно реализовать поиск в экселе по двум параметрам

{=СУММ(ЕСЛИ(($A21=$A$4:$A$14)*(B$20=$C$4:$C$14);$B$4:$B$14;0))}
вводи формулой массива, ctrl + shift + enter

p.s. строки и столбцы в этой формуле могут быть другими, так как ты выложил рисунок без названия столбцов и строк...

то же самое, но без ctrl + shift + enter и, кстати на ~20% быстрее:

=СУММПРОИЗВ(--($A21=$A$4:$A$14);--(B$20=$C$4:$C$14);$B$4:$B$14)

или

=СУММПРОИЗВ(($A21=$A$4:$A$14)*(B$20=$C$4:$C$14)*$B$4:$B$14)

[KL]

Genyaa а можно и без функции сцепить, просто через амперсант писать (&) и тоже работает.
=СУММЕСЛИ($D$4:$D$14;$A21&B$20;$B$4:$B$14)

СУММЕСЛИ и СЧЕТЕСЛИ можно использовать с более чем одним критерием только в двух случаях:

1) 2 критерия. Когда оба критерия определяют границы отрезка для числовых величин:

3>=x<=8

=СУММЕСЛИ($A21&B$20;">=3";$B$4:$B$14)-СУММЕСЛИ($A21&B$20;">8";$B$4:$B$14)

=СЧЁТЕСЛИ(A1:A10;">=3")-СЧЁТЕСЛИ(A1:A10;">8")

2) 2 и более критериев до 255 знаков. Когда критерии не связаны между собой и результаты соответственно тоже:

=СУММЕСЛИ($A21&B$20;{"a","b","c"};$B$4:$B$14)

=СЧЁТЕСЛИ($A21&B$20;{"a","b","c"})

обе формулы возвращают массив из результатов для каждого из критериев, типа {1,2,3}. Вместо констант массива можно конечно использовать ранги ячеек.

[yura613]

=СУММЕСЛИ($A21&B$20;{"a"\"b"\"c"};$B$4:$B$14)

=СЧЁТЕСЛИ($A21&B$20;{"a"\"b"\"c"})

KL я извиняюсь а как это можно так лихо массив в формулу добавить?

[KL]

KL я извиняюсь а как это можно так лихо массив в формулу добавить?

А прямо так и можно В Excel существует такая [мало используемая] вещь как константы массива

http://office.microsoft.com/en-us/excel ... 11033.aspx

Внимание! В разных локалях разделители горизонтальных и вертикальных (и разумеется смешанных) массивов варьируют. Например, если не ошибаюсь, в русской локали:

; - вертикальный разделитель
\ - горизонтальный разделитель

[GSerg]

Запятая горизонтальный.

[KL]

Запятая горизонтальный.

Спасибо, GSerg
Сейчас стану править свои постинги. Переводчик формул, которым я пользуюсь для перевода с англ., почему-то упрямо использует \

[yura613]

Я тут стал использовать предложенные вами функции, работать то они работают, но при их использовании увеличивается размер файла. Что в свою очередь будет затруднять перещеты. А можно ли наайти оптимальный вариант между весом файла и скоростью пересчета(Хотя вес файла я думаю более приоритетней)?

вариант с использованием такой функции

Код: Выделить всё

{=СУММ(ЕСЛИ(($A27=$A$4:$A$14)*(B$20=$C$4:$C$14);$B$4:$B$14;0))}

весит меньше всех, но это все равно больше чем с использованим собственных функций. Хотя не известное как это будет работать при увеличении данных, например в 10 раз.

[KL]

Я тут стал использовать предложенные вами функции, работать то они работают, но при их использовании увеличивается размер файла. Что в свою очередь будет затруднять перещеты. А можно ли наайти оптимальный вариант между весом файла и скоростью пересчета(Хотя вес файла я думаю более приоритетней)?

вариант с использованием такой функции

Код: Выделить всё

{=СУММ(ЕСЛИ(($A27=$A$4:$A$14)*(B$20=$C$4:$C$14);$B$4:$B$14;0))}

весит меньше всех, но это все равно больше чем с использованим собственных функций. Хотя не известное как это будет работать при увеличении данных, например в 10 раз.

1) Без конкретного файла трудно что-либо сказать
2) Мне кажется в этой теме я предложил несколько совершенно разных по цели и ресурсоемкости формул
3) Если беспокоят размеры файла со столь безобидными формулами, то вероятно кол-во ячеек с ними и/или ранги ими охватываемые достаточно велики, чтобы обеспокоиться по поводу возможно очень медленного пересчета собственных функций (не уверен, что это будет сильно лучше).
4) Пожалуй единственный компромиссный вариант, который приходит мне на ум, это считать формулы из VBA с пом. Evaluate() или [...], но информации не хватает.

[yura613]

Скидываю файл. для более подробной информации. Файл этот что то вроде эскиза, на самом деле данных (наименований) должно быть где то в 20 раз больше.

[yura613]

Хотелось бы чтоб файл при огромном объеме данных(позволительном объеме для экселя) был как можно легче и соответственно и работоспособность его будет лучше (если эти параметры связаны межуд собой)