Страницы: 1
RSS
Пользовательская функция для поиска пересечения в кросс-таблице по 2-м неточным числовым значениям осей
 
Уважаемые эксперты по Excel и VBA!
Многие часы ломаю голову, пытаясь написать пользовательскую функция с помощью разбора обозначений формул при записывании макроса, но все бес толку.
В работе часто приходится использовать достаточно большую формулу массива для поиска значения в диапазоне на пересечении ближайших больших значений по строкам и столбцам:

Код
{=ИНДЕКС(ЗНАЧЕНИЯ;
ПОИСКПОЗ(МИН(ЕСЛИ(ДИАПАЗОН_Y>=Искомый_Y;ДИАПАЗОН_Y;""));ДИАПАЗОН_Y;0);
ПОИСКПОЗ(МИН(ЕСЛИ(ДИАПАЗОН_X>=Искомый_X;ДИАПАЗОН_X;""));ДИАПАЗОН_X;0))}

Задача состоит в том, чтобы пользовательская функция приобрела вид:
Код
=ПОБЛИЖБОЛ(ЗНАЧЕНИЯ; ДИАПАЗОН_Y; Искомый_Y; ДИАПАЗОН_X; Искомый_X)

Прошу помощи в создании пользовательской функции в Excel средствами VBA, используя в коде уже имеющиеся в EXCEL формулы (ИНДЕКС; ПОИСКОПОЗ и т.д.).
Изменено: Valo - 23.02.2018 01:44:48
Наша суть отражается в наших повторяющихся действиях. Отсюда следует, что совершенство есть не действие, а привычка. Аристотель.
 
Valo, ща сделаем  ;)
Изменено: Jack Famous - 22.02.2018 20:32:03
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, да. Дело в том, что мне часто приходится использовать различные варианты данной формулы (поиск может быть ближайшего меньшего значения или диапазон поиска может быть только по ДИАПАЗОН_X и т.д.). И мне хотелось быть получить пользовательскую функцию, на основе которой я смог бы создавать подобные.
Наша суть отражается в наших повторяющихся действиях. Отсюда следует, что совершенство есть не действие, а привычка. Аристотель.
 
Цитата
Valo написал:
различные варианты данной формулы
ну вот, блин, только начал))) сразу надо такое говорить)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Valo написал:
Пользовательская функция EXCEL
Valo, Вы считаете исчерпывающим такое название темы? Из него всем понятно, какая конкретно проблема у Вас?
 
Юрий М, нет, прошу прощения. Нажал кнопку "создать" немного раньше времени, а отредактировать название уже не получается  :oops: .
Тема: Помощь при создании пользовательской функции в VBA с использованием стандартных формул EXCEL
Описание: В коде хотелось бы использовать стандартных формул EXCEL для возможности самостоятельного редактирования
Изменено: Valo - 22.02.2018 21:00:17
Наша суть отражается в наших повторяющихся действиях. Отсюда следует, что совершенство есть не действие, а привычка. Аристотель.
 
Юрий М, ещё и мой косяк… «Поиск пересечения в кросс-таблице по 2м неточным числовым значениям осей»
Изменено: Jack Famous - 22.02.2018 21:00:32
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Какое название оставляем - #6 или #7?
 
Юрий М,мне кажется #6, так как сам по себе "Поиск пересечения в кросс-таблице по 2м неточным числовым значениям осей" у меня имеется, а вот как из него пользовательскую функцию составить - неясно  :sceptic: .

Вот хороший вариант  :) :
Пользовательская функция VBA: поиск пересечения в кросс-таблице по 2-м неточным числовым значениям осей.
Изменено: Valo - 22.02.2018 21:06:25
Наша суть отражается в наших повторяющихся действиях. Отсюда следует, что совершенство есть не действие, а привычка. Аристотель.
 
я за своё - у ТС может что-то и есть, но явно не то, что нужно, а по его названию никто ничего не найдёт. После решения, я ещё и другую давнюю тему обновлю, в которой так до сих пор ничего внятного не написал …
Valo, без обид (про название) - ничего против вас не имею - договорились?  ;)

UPD: Так всё-таки «Пользовательская функция» или «Стандартные формулы EXCEL в VBA»??? Вы знаете, что это разные вещи? UDF я напишу, а по формулам пас…
Изменено: Jack Famous - 22.02.2018 21:22:38
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, договорились, я уже на все согласен  :) .
Продолжаю пытать мозг  :sceptic: :
Код
Function ПОБЛИЖБОЛ(ЗНАЧЕНИЯ As Range, ДИАПАЗОН_Y As Range, ДИАПАЗОН_X As Range, Искомый_Y, Искомый_X)

ПОБЛИЖБОЛ = Application.WorksheetFunction.Index(ЗНАЧЕНИЯ,
Application.WorksheetFunction.Match(Application.WorksheetFunction.Min(Application.WorksheetFunction.IF
(ДИАПАЗОН_Y >= Искомый_Y, ДИАПАЗОН_Y, """")), ДИАПАЗОН_Y, 0), 
Application.WorksheetFunction.Match(Application.WorksheetFunction.Min(Application.WorksheetFunction.IF(ДИАПАЗОН_X >= Искомый_X, ДИАПАЗОН_X, """")), ДИАПАЗОН_X, 0))

End Function
Изменено: Valo - 22.02.2018 21:34:52
Наша суть отражается в наших повторяющихся действиях. Отсюда следует, что совершенство есть не действие, а привычка. Аристотель.
 
Не могу понять, что не так. Прошу помощи у знатоков. Сортировка работает (закомментировано)
Собсна КОД
Изменено: Jack Famous - 22.02.2018 22:42:48
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Зачем сортировка и транспонирование? Как я понимаю, таблица задана и значения должны быть расположены правильно, по возрастанию.
 
Действительно, данные в таблице всегда отсортированы по возрастании снизу вверх по Y и слева направо по X.
Наша суть отражается в наших повторяющихся действиях. Отсюда следует, что совершенство есть не действие, а привычка. Аристотель.
 
подойдет?
Изменено: Бибигуль - 23.02.2018 00:21:27
 
vikttur, я просто стараюсь найти решение максимально универсальное (как всегда) и, при этом, предельно понятное (христоматийное, если угодно)
В данном случае я старался учесть не только целые положительные числа, но и отрицательные, и дробные и, возможно, даже текст (не тестил)
Для данного случая подойдёт обычный цикл по двум диапазонам — обещаю сделать  ;)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:  найти решение максимально универсальное
Если представим, что функций поиска не одна - это сверхуниверсально :)
Зачем в каждой функции выполнять то, что уже сделано одной?
 
Цитата
vikttur написал:
Зачем сортировка и транспонирование?
транспонирование для того, чтобы можно было сортировать (иначе не работает, потому что сортировка по СТОЛБЦУ). Сортировка для того, чтобы циклом перебирать варианты до бОльшего заданного и брать 2: бОльше заданного и предыдущее, после сравнивая отклонение. Я пробовал вариант, где числа расположены не по порядку — отсюда и сортировка. В рамках данной задачи - излишне, но, если бы у меня получилось, то было бы намного универсальнее и ненамного дольше  :)
Вы мне подали идею))) несколько "служебных" функций, вызываемых основной, в зависимости от параметров основной  :D  попробую попробовать)))
Изменено: Jack Famous - 23.02.2018 02:42:52
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Сравнение по указанному знаку. Если последний параметр не указан, сравнение по знаку ">"
Проверка на правильность сравнения (при выходе за пределы указанных значений) не прописана.  Например, при поиске y=3 к выборке данных подготовится строка 1, а это шапка со значениями х; при у=45 тоже ошибка. Это поле деятельности для разработчика :)
Код
Function fMinMax(rRng As Range, yVal As Long, xVal As Long, Optional sStr As String = ">") As Long   ' если дробные - Double
Dim bFlag As Boolean
Dim i As Long, j As Long
    i = 1: j = 1
    If sStr = "<" Or sStr = "<=" Then bFlag = True
    
    Do
        i = i + 1
    Loop Until Evaluate(rRng(i, 1) & sStr & yVal) <> bFlag
    
    Do
        j = j + 1
    Loop Until Evaluate(rRng(1, j) & sStr & xVal) <> bFlag
    
    fMinMax = rRng(i + bFlag, j + bFlag).Value
End Function

Обычно избегаю применения логических значений при сложении/вычитании.  Но тут такое решение показалось уместным.
 
Бибигуль, vikttur, Jack Famous, то что нужно и даже больше! Огромное вам спасибо!
Наша суть отражается в наших повторяющихся действиях. Отсюда следует, что совершенство есть не действие, а привычка. Аристотель.
Страницы: 1
Наверх