Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Выбрать из предложенного диапазона ближайшее число к заданному числу.
 
#1
09.10.2011 14:29:57
Доброго времени!)  
Помогите, пожалуйста, решить вопрос.    
Задан диапазон чисел a1:e1, например, 10 20 30 40 50. Из предложенных чисел необходимо выбрать ближайшее число к (например)G1=37. И если (например, в данном случае) число не 37 а 35, то было бы выбрано любое, из двух подходящих (30 или 40).    
Было бы великолепно, конечно, если возможно, чтоб решение укладывалось в одну формулу. Заранее спасибо, за внимание и помощь)
 
 
 
#2
09.10.2011 14:38:49
=ИНДЕКС(A1:E1,ПОИСКПОЗ(МИН(ABS(G1-A1:E1)),ABS(G1-A1:E1),0))  
массив, ввод через ctrl+shift+enter
 
 
 
#3
09.10.2011 14:41:38
=ИНДЕКС(A1:E1;ПОИСКПОЗ(37;A1:E1;1))  
Вместо "37" можно ссылку на ячейку.  
Ошибка, если выбираемое меньше минимального в диапазоне.
 
 
 
#4
09.10.2011 14:51:02
))))))) Огромное спасибо) Два дня думал) Большое спасибо))))
 
 
 
#5
09.10.2011 14:53:57
Тема закрыта))))))))
 
 
 
#6
09.10.2011 16:00:43
массивная:  
=ПРОСМОТР(2;1/(ABS(G1-A1:E1)=МИН(ABS(G1-A1:E1)));A1:E1)  
 
отличие от индекс(поискпоз) - выберет последнее подходящее число из списка если подходит более чем одно
 
 
 
#7
09.10.2011 17:10:56
=ПРОСМОТР(2;1/(ABS(G1-A1:E1)=МИН(ABS(G1-A1:E1)));A1:E1)  
Ошибку в вычислении выдает --> МИН(ABS(G1-A1:E1)
 
 
 
#8
09.10.2011 17:36:45
а нет, всё хорошо) разобрался)фигурные скобки забыл поставить0)
 
 
 
#9
09.10.2011 18:03:26
Помогите, пожалуйста, еще... Если не сложно_)  
Имеется таблица, необходимо, выбрать наиболее близкое к значению П из таблицы число и занести его в ячейку F19, при этом должны заносится "b" (высота) и "a" (ширина) в соответствующие ячейки F22 и F21, выбранного значения F19.  
Например, если значение С19=30,6 то необходимо занести в F19 значение 30,7 а в ячейки F21 и F22, соответственно, занести 2,5 и 12,5.  
...ммм.... Это возможно?)  
Заранее спасибо, большое)))))
 
 
 
#10
09.10.2011 20:20:52
так?
 
 
 
#11
09.10.2011 20:57:03
)))))))Спасибо огромное)) Никогда бы не додумался, как это сделать) Большое спасибо))))))) Просто асы) Спасибо)))))))))
 
 
 
#12
26.03.2012 22:38:07
Пожалуйста, помогите решить ещё такую задачку:  
 
В представленной таблице в столбце А нужно найти строку с числом, ближайшим к указанному в ячейке F15, затем в этой строке найти число, ближайшее к указанному в F16. И в F17, как итог, вывести заголовок столбца, содержащего найденное число.  
 
Вроде бы даже понимаю, как это сделать (через модуль результата вычитания чисел массива из заданного числа), но как ни пытался всё это одной формулой реализовать - мозги вскипают. :D
 
 
 
#13
26.03.2012 23:37:51
Видимо, такое действительно трудно реализовать. :(
 
 
 
#14
26.03.2012 23:52:48
А Вы условия нормально пишите, тогда не трудно будет.  
8 ближе к 7-и или к 9-и?
 
 
 
#15
27.03.2012 00:24:34
Извините, недоглядел. Числа заполнял случайным образом и не учёл такой нюанс. Вот исправленный файл. В нём 8.6 (F15) ближе к 9, это 6 строка. В этой строке 430 ближе всего к 432.1 (F16), значит искомый столбец K. Он называется juliet, что и нужно отобразить в ячейке F17.
 
 
 
#16
27.03.2012 00:45:06
Знакомый файл :)  
Почему не продолжаете в старой теме?
 
 
 
#17
27.03.2012 00:58:54
Наверное можно было и там продолжать. Просто искал поиском нахождение ближайшего значения, вышла эта тема. :)
 
 
 
#18
27.03.2012 01:04:15
Ясно, эта ближе, там другой вопрос.  
 
Строка 8,6, число 65. Ну, оператор пьяный :)  
Что делать формуле?  
Я к чему? Может быть, не смотреть на проделки выпившего за клавиатурой, достаточно искать число?
 
 
 
#19
27.03.2012 01:09:24
Ближайшее будет 112 из столбца E, вывести delta.
 
 
 
#20
27.03.2012 01:15:00
Ясно, нам нипочем невнимательные операторы :)  
 
Еще уточнение - обязательно ближайшее число к 8,6? Не ближайшее меньшее или ближайшее большее, а именно то, которое ближе (с любой стороны)?  
 
Если ближайшее меньшее:  
=ИНДЕКС(B1:M1;ПОИСКПОЗ(МИН(ABS(ИНДЕКС(B2:M13;ПОИСКПОЗ(F15;A2:A13);)-F16));ABS(ИНДЕКС(B2:M13;ПОИСКПОЗ(F15;A2:A13);)-F16);))Формула массива.  
 
Как вводить формулу массива:  
После введения формулы в ячейку, ввод завершается нажатием не просто Enter, а Ctrl+Shift+Enter. Формула при этом будет заключена в фигурные скобки - {}.
 
 
 
#21
27.03.2012 01:19:40
Еслиб просто число искать по таблице, это я бы осилил добавив алгоритм из этой темы в формулу, которую вы мне в прошлый раз подсказали. Но дело всё немножко сложнее. Сейчас в файле случайные числа, а в рабочем документе будет сертифицированная таблица довольно больших размеров и там столбец А обозначает число оборотов, которому по горизонтали в каждом столбце будет соответствовать какое-то стандартное значение. Вся проблема вытекает из того, что значения для ячеек F15 и F16 будут получаться из других вычислений, в которых часто набегает погрешность. Получится там 8.6 циклов оборота двигателя, и как быть? Нужно искать ближайшее целое значение из таблицы. Для 8.6 оборота ГОСТ значений не приводит))
 
 
 
#22
27.03.2012 01:27:25
Ближайшее с любой стороны. Его, похоже, найти можно последовательно вычитая из искомого числа элементы массива и затем найти наименьшее значение из их модулей.  
 
P.S. Что-то Ваша формула не правильно работает.
 
 
 
#23
27.03.2012 01:36:51
Предыдущая формула работает правильно, но, как замечено, для 8,6 ищет 7.  
 
Чтобы не "утолщать" формулу - в доп. столбец:  
=ABS(A2-$F$15)  
 
Для результата - формула массива:  
=ИНДЕКС(B1:M1;ПОИСКПОЗ(МИН(ABS(ИНДЕКС(B2:M13;ПОИСКПОЗ(МИН(N2:N13);N2:N13;);)-F16));ABS(ИНДЕКС(B2:M13;ПОИСКПОЗ(МИН(N2:N13);N2:N13;);)-F16);))  
 
В файле еще более упрощенный вариант.
 
 
 
#24
27.03.2012 01:37:37
О, ещё раз спасибо вам! Проверил, работает. :D Сейчас буду разбираться, как оно всё устроено.  
 
Чем безмассивный вариант лучше, чем с массивами? Подозреваю, что масштабируется легче. Или ещё какие-то подводные камни бывают?
 
 
 
#25
27.03.2012 01:49:07
В простых формулах легче разбираться :)  
 
Формулы массива производят вычисления над диапазонами данных, поэтому тяжелы в расчетах. На больших таблицах, где таких формул много, лучше доп. вычисления.  
Что в Вашем варианте лучше? Всего одна формула массива или доп. строка плюс доп. столбец? Не знаю, скорости не замерял.
 
 
 
#26
28.03.2012 17:41:15
Добрый вечер форумчане  
Столько изучил про VBA, что забыл про Эксель  
Как можно прописать решение на вопрос, указанный в приложении.  
А проще определение номера строки ячейки из массива, удовлетворяющей условию.  
А то я "гляжу в кнгу, вижу фигу" :)  
Спасибо!
 
 
 
#27
28.03.2012 17:53:14
Чтобы получить 1, не обязательно строки делить, достаточно этой формулы массива:  
 
=МАКС(--(B2:B6=E4)*(C2:C6=E7))  
 
 
---------  
67867
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#28
28.03.2012 17:56:23
=МАКС(--(B2:B6=E4)*(C2:C6=E7))  
 
Владимир, Спасибо большое!
 
 
 
#29
29.03.2012 01:56:35
Большая любовь к формулам массива :)  
 
Да проще:  
=--(ВПР(E4;B2:C6;2;0)=E7)  
 
Можно ЕСЛИ() добавить для обхода ошибки:  
=ЕСЛИ(СЧЁТЕСЛИ(B2:B6;E4);--(ВПР(E4;B2:C6;2;0)=E7);"этот без допуска!")
 
 
 
Страницы: 1
Читают тему
Наверх