Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Определить число из диапазона, которое ближе всего к заданному числу
 
#1
17.11.2015 15:22:32
Всем добра!

Очень хорошая функция ПОИСКПОЗ, часто ее использую, но вот для решения задачи, которая будет описана ниже, она или не может быть приспособлена или я "туплю"
Задача не очень, казалось бы, сложная, но как решить не знаю
Смысл в том, что дан ряд положительных чисел расположенных по возрастанию и есть еще одно число, которое точно находится в промежутке от между минимумом и максимумом диапазона. Это число может, как совпадать с одним из имеющихся в диапазоне чисел, так и не совпадать (чаще - второе)
Хочется, чтобы формула определяла число из диапазона, которое ближе всего к заданному числу. В примере - еще дополнительно немного пояснено, на примере понять проще.
 
 
 
#2
17.11.2015 15:30:50
как вариант но если минимальная разница будет у большей и меньшей цифры от заданной выдаст меньшую
Код
=ИНДЕКС(B4:B16;ПОИСКПОЗ(МИН(ABS($B$4:$B$16-$D$3));ABS($B$4:$B$16-$D$3);0))
Лень двигатель прогресса, доказано!!!
 
 
 
#3
17.11.2015 17:39:01
Здравия!
И как вариант массивная
Изменено: Mirdv - 17.11.2015 18:05:16
 
 
 
#4
17.11.2015 21:44:54
Спасибо огромное за решения! Посмотрел пока токо последнее из них, работает! посмотрю и остальные - отпишусь! )
 
 
 
#5
16.09.2017 02:41:54
Подниму-ка тему из закладок)) давайте решим её уже так, чтобы на эту тему все ссылались при поиске (тут, конечно, стоит название сменить  :D )
Итак, ищем максимально универсальное решение для чисел.

Идеальный (на мой взгляд) вариант: UDF со следующими переменными:
1. Ориентир (ссылка на ячейку или ввод числа, ближайшее к которому нужно найти)
2. ГдеИскать (диапазон для поиска)
3. -1; 0 или 1 (выбор варианта поиска: ближайшее наименьшее, просто ближайшее, ближайшее наибольшее. 0 - по умолчанию, параметр необязательный)

Ссылки:
1. Формулы
Изменено: Jack Famous - 16.09.2017 02:55:26
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#6
16.09.2017 09:37:07
Цитата
Jack Famous написал: давайте решим её уже
Так давайте, начните. Попробуйте, хотя бы, 'завернуть' все формулы по Вашей ссылки в одну UDF и будет Вам универсальная
Пора начинать самому "творить'  
Согласие есть продукт при полном непротивлении сторон
 
 
 
#7
16.09.2017 10:36:43
Ещё вариант
Код
=ИНДЕКС(B4:B16;ПОИСКПОЗ(D3-(ИНДЕКС(B4:B16;ПОИСКПОЗ(D3;B4:B16;1)+1)-ИНДЕКС(B4:B16;ПОИСКПОЗ(D3;B4:B16;1)))/2;B4:B16;1))
Цитата
но если минимальная разница будет у большей и меньшей цифры от заданной выдаст меньшую
А если ПОИСКПОЗ(D3- знак сменить на плюс, то большую.
Вариант не правильный, при большой разнице выдаст не правильный результат.
Изменено: gling - 16.09.2017 13:26:59
 
 
 
#8
16.09.2017 11:30:00
Без массивного ввода:
=D3+ЕСЛИ(D3-ВПР(D3;B4:B16;1)<ПРОСМОТР(D3;B4:B16;B5:B16)-D3;ВПР(D3;B4:B16;1)-D3;ПРОСМОТР(D3;B4:B16;B5:B16)-D3)
если при равенстве расстояний нужно выбирать меньшее,  < заменить на  <=
 
 
 
#9
16.09.2017 14:21:50
Как вариант без массивного ввода
=ИНДЕКС(B4:B16;АГРЕГАТ(14;6;(СТРОКА(B4:B16)-СТРОКА(B4)+1)/(МИН(ИНДЕКС(ABS(B4:B16-D3);))>=ABS(B4:B16-D3));1))
Меньшее и большее при равенстве двух "Дельт" выбирается заменой "14" на "15" под АГРЕГАТ и наоборот
Изменено: Akropochev - 16.09.2017 14:23:17
 
 
 
#10
02.10.2017 12:13:04
Доброго дня, Планетяне!
Первый раз вот столкнулся с массивами и никак не могу заполнить массив данными с листа. Прошу помощи и, по-возможности, пояснений.
При написании руководствовался этой статьёй.
Код UDF

P.S.: я тут подтягиваю уже вычисленное минимальное значение, а надо бы узнать индекс и подтягивать исходное (тут, думаю, справлюсь). Однако проблема с самим массивом
Изменено: Jack Famous - 02.10.2017 12:39:52
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#11
07.11.2017 13:47:52
Доброго дня, Планетяне!
Продолжаю создание UDF на массивах по этой теме. Решил немного по-другому искать. Вроде как поиск минимального функцией листа не срабатывает для массива (открытие  :) ) поменял, но функция так и не работает  :( Прошу помочь в выявлении ошибок в коде. Код из файла:
Код
Option Explicit
Function ПодобратьБлижайшее(что_ищем As Variant, где_ищем As Range) As Variant
Dim arr(), itm, minimal

    If где_ищем.colums.Count <> 1 Then
        MsgBox "Искать в 1 столбце!", vbCritical, "Ошибка выделения"
        Exit Function
    End If
    
arr() = где_ищем.Value
minimal = arr(0)

On Error Resume Next
    For Each itm In arr
        itm = Abs(1 - (itm / что_ищем.Value))
            If itm < minimal Then minimal = itm
    Next itm
    
ПодобратьБлижайшее = minimal.Value
On Error GoTo 0
End Function
Изменено: Jack Famous - 07.11.2017 13:49:04
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#12
07.11.2017 14:11:02
Доброе время суток.
UDF-функция, если правильно понял, что ищем по минимуму абсолютной разницы.
Успехов.
 
 
 
#13
07.11.2017 14:28:27
Андрей VG, спасибо большое!  :D  Как я понимаю, проблема у меня не только в том, что я функцию Abs без Math применял)))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#14
07.11.2017 15:00:26
Цитата
Jack Famous написал:
проблема у меня не только в том
Хе, Алексей, проблема ещё и в том... Пусть массив чисел 10; 12; 14. Искомое 11. Что должно быть ближайшим 10 или 12?  ;)
 
 
 
#15
07.11.2017 15:09:33
Андрей VG, по идее - всё равно, раз они равноудалены)) ну это чисто математически  :)

Я вот чего не понимаю: у вас массив без скобок объявлен (видел такое, хоть и ZVI вот тут, в сообщении №27, говорит, что со скобками в 1,6 раза быстрее). Однако вы потом обращаетесь к его элементам не как к элементам массива (arr(0), arr(1) и так далее), а как к обычному диапазону с указанием строки и столбца (arr(1,1)) — пытался для массива адаптировать - и никак (хоть со скобочками, хоть без)… Поясните, если есть возможность, пожалуйста  :)
Изменено: Jack Famous - 07.11.2017 15:10:10
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#16
07.11.2017 15:11:55
Цитата
обращаетесь к его элементам не как к элементам массива..., а как к обычному диапазону
Массивы есть одномерные (a(1)), двумерные (a(1,1)) , многомерные (a(1,1,1) - аж до 64 размерностей)...
 
 
 
#17
07.11.2017 15:15:16
vikttur, да читаю))) вот тут, в частности)))
Однако, в моём исполнении (как и в рамках задачи), подразумевается, что столбец поиска один, а значит массив одномерный, вот и пытаюсь работать с элементами одномерного массива…
Тогда выходит, что у Андрея получилось, когда массив двухмерный, но у меня не выходит, когда он одномерный)) что не так-то?))) может отдельную тему тогда создать?  :)
Изменено: Jack Famous - 07.11.2017 15:18:45
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#18
07.11.2017 15:20:38
Цитата
Jack Famous написал:
у вас массив без скобок объявлен
У меня вообще массив не объявлен ;)  В примере по ссылке массив создаётся сразу одномерным. Массив же значений ячеек всегда двумерный - Range.Value для диапазона. Быстрее, так как аргумент метода сортировки сразу объявлен массивом - следовательно при каждом обращении не происходит внутренняя проверка на тип значения (а не массив ли случайно?), плюс, определение границ массива. Всё это внутренняя кухня. Если бы массив стразу определялся строковым, а аргумент задавался как ByRef SortArray() As String, то было бы и ещё быстрее ;)
 
 
 
#19
07.11.2017 15:24:52
Jack Famous, опять тему налево потянули?
 
 
 
#20
07.11.2017 15:40:06
vikttur,
Цитата
Jack Famous написал:
может отдельную тему тогда создать?
создам)

Андрей VG, спасибо большое за разъяснения! Поднакоплю материала и опыта и создам тему о массивах  ;)
И, кстати, ваш вариант, в случае нахождения 2 значений на одинаковом расстоянии от искомого, подтянет меньший  :)
Изменено: Jack Famous - 07.11.2017 15:45:43
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
Страницы: 1
Наверх