Определение значений близких к заданному

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Определение значений близких к заданному (Формулы/Formulas)

ununnamed

Дата: Вторник, 04.04.2017, 12:41 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Здравствуйте. Помогите решить задачу, которую мы уже 7 лет решаем ручным перебором.
Есть два столбика данных - дни и высота. Если построить график высоты от дней, то получится, что в этой зависимости есть 1 пик. И мне нужно найти два дня, слева и справа от максимума, где высота была равной половине от максимальной.
Половину максимальной высоты я могу найти, но как найти два значения, близкие к получившейся дробной величине?

К сообщению приложен файл: 6326565.xls (39.5 Kb)

Сообщение отредактировал ununnamed - Вторник, 04.04.2017, 12:43

Ответить

китин

Дата: Вторник, 04.04.2017, 13:13 | Сообщение № 2

Группа: Модераторы

Ранг: Экселист

Сообщений: 7034

Репутация: 1079 ±

Замечаний: 0% ±

Excel 2007;2010;2016

как то так: формула массива

Код

=ИНДЕКС($A$2:$A$19;ПОИСКПОЗ(МИН(ЕСЛИ($B$2:$B$19>=$E$2;$B$2:$B$19));$B$2:$B$19;0))

К сообщению приложен файл: 222111.xls (38.0 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

ununnamed

Дата: Вторник, 04.04.2017, 13:20 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

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

Сообщение отредактировал ununnamed - Вторник, 04.04.2017, 13:22

Ответить

китин

Дата: Вторник, 04.04.2017, 13:23 | Сообщение № 4

Группа: Модераторы

Ранг: Экселист

Сообщений: 7034

Репутация: 1079 ±

Замечаний: 0% ±

Excel 2007;2010;2016

покажите

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

ununnamed

Дата: Вторник, 04.04.2017, 13:26 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

китин, график для удобства я тоже поправил вручную

К сообщению приложен файл: 9235950.xls (38.0 Kb)

Ответить

китин

Дата: Вторник, 04.04.2017, 13:28 | Сообщение № 6

Группа: Модераторы

Ранг: Экселист

Сообщений: 7034

Репутация: 1079 ±

Замечаний: 0% ±

Excel 2007;2010;2016

я же написал:

Цитата китин, 04.04.2017 в 13:13, в сообщении № 2 ()

формула массива

вводится одновременным нажатием трех клавиш: Ctrl+Shift+Enter

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

_Boroda_

Дата: Вторник, 04.04.2017, 13:34 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16885

Репутация: 6599 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Неоптимизированные формулы массива (вводятся одновременным нажатием Контрл Шифт Ентер)

Код

=ПРОСМОТР(;-1/(ABS(B2:ИНДЕКС(B2:B19;ПОИСКПОЗ(МАКС(B2:B19);B2:B19;))-МАКС(B2:B19)/2)=МИН(ABS(B2:ИНДЕКС(B2:B19;ПОИСКПОЗ(МАКС(B2:B19);B2:B19;))-МАКС(B2:B19)/2)));A2:A19)

Код

=ПРОСМОТР(;-1/(ABS(ИНДЕКС(B2:B19;ПОИСКПОЗ(МАКС(B2:B19);B2:B19;)):B19-МАКС(B2:B19)/2)=МИН(ABS(ИНДЕКС(B2:B19;ПОИСКПОЗ(МАКС(B2:B19);B2:B19;)):B19-МАКС(B2:B19)/2)));ИНДЕКС(A2:A19;ПОИСКПОЗ(МАКС(B2:B19);B2:B19;)):A19)

В файле _2 зеленое - с допячейками

К сообщению приложен файл: 9235950_1.xls (40.0 Kb) · 6326565_2.xls (41.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

ununnamed

Дата: Вторник, 04.04.2017, 13:35 | Сообщение № 8

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

китин, это проблему не решает, одно значение ищет ближайшее в большую сторону, а другое - в меньшую, и оба находятся с левой стороны от пика

Ответить

ununnamed

Дата: Вторник, 04.04.2017, 13:41 | Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

_Boroda_, я не знаю, как, но это работает. Вы не могли бы в двух словах рассказать, что вообще эта формула делает?

Ответить

_Boroda_

Дата: Вторник, 04.04.2017, 13:49 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 16885

Репутация: 6599 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Ищет положение середины (в файле _2 ячейка Е2), делим исходный диапазон на два - до этой позиции и после и работаем с каждым по отдельности
Допустим, с первым ИНДЕКС(B2:B19;E3):B2 - это =B2:B6
Ищем модули разниц МР= B2:B6 и МАКС(B2:B19) ищем в них минимум ММР= МИН(ABS(ИНДЕКС(B2:B19;E3):B2-E2/2)) и приравниваем МР к ММР. И берем из диапазона А то по порядку значение, где это приравнивание дает ИСТИНА

Ответить

Manyasha

Дата: Вторник, 04.04.2017, 14:54 | Сообщение № 11

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация: 902 ±

Замечаний: 0% ±

Excel 2010, 2016

еще варианты:

Код

=ИНДЕКС(A2:A19;ПОИСКПОЗ(МИН(ABS(ЕСЛИ((СТРОКА(A2:A19)-1)<=ПОИСКПОЗ(МАКС(B2:B19);B2:B19;);B2:B19)-E2));ABS(ЕСЛИ((СТРОКА(A2:A19)-1)<=ПОИСКПОЗ(МАКС(B2:B19);B2:B19;);B2:B19)-E2);))

(во второй формуле поменять <= на >=)

и второй вариант:

Код

=ИНДЕКС(A2:A19;ПОИСКПОЗ(МИН(ABS(B2:ИНДЕКС(B2:B19;ПОИСКПОЗ(МАКС(B2:B19);B2:B19;))-E2));ABS(B2:ИНДЕКС(B2:B19;ПОИСКПОЗ(МАКС(B2:B19);B2:B19;))-E2);))

Код

=ИНДЕКС(A2:A19;ПОИСКПОЗ(МИН(ABS(ИНДЕКС(B2:B19;ПОИСКПОЗ(МАКС(B2:B19);B2:B19;)):B19-E2));ABS(ИНДЕКС(B2:B19;ПОИСКПОЗ(МАКС(B2:B19);B2:B19;)):B19-E2);)+ПОИСКПОЗ(МАКС(B2:B19);B2:B19;)-1)

(кусочек с определением диапазона стырила у Саши

)