Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Возвращение ближайшего равного или большего значения - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Возвращение ближайшего равного или большего значения (Формулы/Formulas)
Возвращение ближайшего равного или большего значения
kirdyk Дата: Воскресенье, 15.08.2021, 17:33 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Добрый день! Помогите создать формулу.
Необходимо найти ближайшее значение которое больше или равно заданному значению, при этом начало диапазона определяется динамичным условием. Чтобы было проще понять задачу прикладываю файл - например, в диапазоне B:E необходимо найти ближайшее равное или большее значение из ячейки H1, при этом начало диапазона поиска задается значением большим значения в I19. Например - нам необходимо найти значение, которое будет ближайшим большим или равным числу 118,5 (H1) в диапазоне B:E, но диапазон должен начинаться с ячейки равной "08.12.2016", т.е. с ячейки A15. Список сортирован по первому столбцу. При заданных условиях ответ находится в ячейке D51 и равна 118,73.
Заранее спасибо!
К сообщению приложен файл: 6508683.xlsx (47.3 Kb)
 
Ответить
СообщениеДобрый день! Помогите создать формулу.
Необходимо найти ближайшее значение которое больше или равно заданному значению, при этом начало диапазона определяется динамичным условием. Чтобы было проще понять задачу прикладываю файл - например, в диапазоне B:E необходимо найти ближайшее равное или большее значение из ячейки H1, при этом начало диапазона поиска задается значением большим значения в I19. Например - нам необходимо найти значение, которое будет ближайшим большим или равным числу 118,5 (H1) в диапазоне B:E, но диапазон должен начинаться с ячейки равной "08.12.2016", т.е. с ячейки A15. Список сортирован по первому столбцу. При заданных условиях ответ находится в ячейке D51 и равна 118,73.
Заранее спасибо!
Автор - kirdyk
Дата добавления - 15.08.2021 в 17:33
bmv98rus Дата: Воскресенье, 15.08.2021, 18:54 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
в E846 есть 118,56.Почему не оно?
Массивная
Код
=H1+MIN(IF((A1:A1071>I1)*(B1:E1071-H1>0);B1:E1071-H1))

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 16.08.2021, 07:52
 
Ответить
Сообщениев E846 есть 118,56.Почему не оно?
Массивная
Код
=H1+MIN(IF((A1:A1071>I1)*(B1:E1071-H1>0);B1:E1071-H1))
Автор - bmv98rus
Дата добавления - 15.08.2021 в 18:54
kirdyk Дата: Воскресенье, 15.08.2021, 19:29 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

bmv98rus, Потому что необходимо найти ближайшее значение при просмотре сверху вниз, которое равно или больше условного значения
 
Ответить
Сообщениеbmv98rus, Потому что необходимо найти ближайшее значение при просмотре сверху вниз, которое равно или больше условного значения
Автор - kirdyk
Дата добавления - 15.08.2021 в 19:29
kirdyk Дата: Воскресенье, 15.08.2021, 19:38 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

bmv98rus, И ещё - непонятно для чего вы в начале и в конце формулы вводите H1. Вот так формула также работает -
Код
=МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071-H1>0);B1:E1071))
, правда опять же ищет минимально большее значение к условному, а нужно найти первое из больших.
 
Ответить
Сообщениеbmv98rus, И ещё - непонятно для чего вы в начале и в конце формулы вводите H1. Вот так формула также работает -
Код
=МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071-H1>0);B1:E1071))
, правда опять же ищет минимально большее значение к условному, а нужно найти первое из больших.
Автор - kirdyk
Дата добавления - 15.08.2021 в 19:38
bmv98rus Дата: Воскресенье, 15.08.2021, 20:22 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
ну тогда так, сложнее но куда деватьмя
Код
=INDEX(B:E;INT(MIN(IF((A1:A1071>I1)*(B1:E1071-H1>0);ROW(B1:E1071)+COLUMN(B:E)%)));MOD(MIN(IF((A1:A1071>I1)*(B1:E1071-H1>0);ROW(B1:E1071)+COLUMN(B:E)%));1)/1%-0,1)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Воскресенье, 15.08.2021, 20:28
 
Ответить
Сообщениену тогда так, сложнее но куда деватьмя
Код
=INDEX(B:E;INT(MIN(IF((A1:A1071>I1)*(B1:E1071-H1>0);ROW(B1:E1071)+COLUMN(B:E)%)));MOD(MIN(IF((A1:A1071>I1)*(B1:E1071-H1>0);ROW(B1:E1071)+COLUMN(B:E)%));1)/1%-0,1)
Автор - bmv98rus
Дата добавления - 15.08.2021 в 20:22
kirdyk Дата: Воскресенье, 15.08.2021, 22:19 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

bmv98rus, п...ц как сложно))) но теперь все равно не понятно, почему возвращает 116, вместо 118,73, т.е. значение из столбца E:E, а не D:D. И раз уже зашел разговор) - так чтобы возвращало значение из столбца A:A
 
Ответить
Сообщениеbmv98rus, п...ц как сложно))) но теперь все равно не понятно, почему возвращает 116, вместо 118,73, т.е. значение из столбца E:E, а не D:D. И раз уже зашел разговор) - так чтобы возвращало значение из столбца A:A
Автор - kirdyk
Дата добавления - 15.08.2021 в 22:19
kirdyk Дата: Воскресенье, 15.08.2021, 22:40 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

bmv98rus, Вот изобрел такую формулу - =ИНДЕКС($A$1:$A$40000;ПОИСКПОЗ(МИН(ЕСЛИ(($A$1:$A$40000>I1)*($D$1:$D$40000>=H1);$D$1:$D$40000));$D$1:$D$40000;0);1), но она работает некорректно, точнее не всегда корректно - если в ячейке I1 значение 09.12.2016, а в ячейке H1 - 113, то выдает 17.03.2020, вместо ожидаемого 12.12.2016. Может поможете разобраться? (хотя Ваша формула =ИНДЕКС(B:E;ЦЕЛОЕ(МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071-H1>0);СТРОКА(B1:E1071)+СТОЛБЕЦ(B:E)%)));ОСТАТ(МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071-H1>0);СТРОКА(B1:E1071)+СТОЛБЕЦ(B:E)%));1)/1%-0;1) в принципе работает, только ссылку я сделал =ИНДЕКС(A:E вместо =ИНДЕКС(B:E
 
Ответить
Сообщениеbmv98rus, Вот изобрел такую формулу - =ИНДЕКС($A$1:$A$40000;ПОИСКПОЗ(МИН(ЕСЛИ(($A$1:$A$40000>I1)*($D$1:$D$40000>=H1);$D$1:$D$40000));$D$1:$D$40000;0);1), но она работает некорректно, точнее не всегда корректно - если в ячейке I1 значение 09.12.2016, а в ячейке H1 - 113, то выдает 17.03.2020, вместо ожидаемого 12.12.2016. Может поможете разобраться? (хотя Ваша формула =ИНДЕКС(B:E;ЦЕЛОЕ(МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071-H1>0);СТРОКА(B1:E1071)+СТОЛБЕЦ(B:E)%)));ОСТАТ(МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071-H1>0);СТРОКА(B1:E1071)+СТОЛБЕЦ(B:E)%));1)/1%-0;1) в принципе работает, только ссылку я сделал =ИНДЕКС(A:E вместо =ИНДЕКС(B:E
Автор - kirdyk
Дата добавления - 15.08.2021 в 22:40
bmv98rus Дата: Воскресенье, 15.08.2021, 22:52 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
ddjlbkb
Цитата kirdyk, 15.08.2021 в 22:19, в сообщении № 6 ()
но теперь все равно не понятно, почему возвращает 116, вместо 118,73
это формула массива. Вернет как раз 118,73.
Если
укоротить
Код
=INDEX(A:A;INT(MIN(IF((A1:A1071>I1)*(B1:E1071-H1>0);ROW(B1:E1071)))))
то вернет число
или так
Код
=INDEX(A:A;MATCH(1=1;MMULT((A1:A1071>I1)*(B1:E1071-H1>0);{1;1;1;1})>0;))

Все формулы массива

Обратите внимание как оформлены формулы у меня, и как Ваши.

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Воскресенье, 15.08.2021, 23:07
 
Ответить
Сообщениеddjlbkb
Цитата kirdyk, 15.08.2021 в 22:19, в сообщении № 6 ()
но теперь все равно не понятно, почему возвращает 116, вместо 118,73
это формула массива. Вернет как раз 118,73.
Если
укоротить
Код
=INDEX(A:A;INT(MIN(IF((A1:A1071>I1)*(B1:E1071-H1>0);ROW(B1:E1071)))))
то вернет число
или так
Код
=INDEX(A:A;MATCH(1=1;MMULT((A1:A1071>I1)*(B1:E1071-H1>0);{1;1;1;1})>0;))

Все формулы массива

Обратите внимание как оформлены формулы у меня, и как Ваши.
Автор - bmv98rus
Дата добавления - 15.08.2021 в 22:52
kirdyk Дата: Понедельник, 16.08.2021, 00:23 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

bmv98rus, Огромное Вам спасибо!!! Плюнул-дунул и пошло))) Но вот вопрос - а если первая строка заполнена текстовыми значениями (ничего не подумайте - эт я себя, дурака, проверяю) Еще раз спасибо, надеюсь на дальнейшее сотрудничество!
 
Ответить
Сообщениеbmv98rus, Огромное Вам спасибо!!! Плюнул-дунул и пошло))) Но вот вопрос - а если первая строка заполнена текстовыми значениями (ничего не подумайте - эт я себя, дурака, проверяю) Еще раз спасибо, надеюсь на дальнейшее сотрудничество!
Автор - kirdyk
Дата добавления - 16.08.2021 в 00:23
Светлый Дата: Понедельник, 16.08.2021, 07:46 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Ещё массивный вариант:
Код
=ОСТАТ(МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071>=H1);СТРОКА(1:1071)*10^6+B1:E1071));10^6)
Код
=ОСТАТ(МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071>=H1);СТРОКА(1:1071)*10^6+A1:A1071));10^6)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 16.08.2021, 07:47
 
Ответить
СообщениеЕщё массивный вариант:
Код
=ОСТАТ(МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071>=H1);СТРОКА(1:1071)*10^6+B1:E1071));10^6)
Код
=ОСТАТ(МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071>=H1);СТРОКА(1:1071)*10^6+A1:A1071));10^6)
Автор - Светлый
Дата добавления - 16.08.2021 в 07:46
bmv98rus Дата: Понедельник, 16.08.2021, 08:08 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 16.08.2021 в 07:46, в сообщении № 10 ()
Ещё массивный вариант:
но он опасен искажением данных 118,729999997 и надо округлять до нужной точности, ну и конечно с большими числами и надо корректировать и есть ограничение, как по строкам, так и величине исходных, но в целом - да.

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 16.08.2021, 08:09
 
Ответить
Сообщение
Цитата Светлый, 16.08.2021 в 07:46, в сообщении № 10 ()
Ещё массивный вариант:
но он опасен искажением данных 118,729999997 и надо округлять до нужной точности, ну и конечно с большими числами и надо корректировать и есть ограничение, как по строкам, так и величине исходных, но в целом - да.
Автор - bmv98rus
Дата добавления - 16.08.2021 в 08:08
Светлый Дата: Понедельник, 16.08.2021, 08:14 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Для даты ещё проще:
Код
=МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071>=H1);A1:A1071))
*И для значения
Код
=МАКС(ТЕКСТ(ИНДЕКС(B:E;ПОИСКПОЗ(I2;A:A;);)-H1;"0,00;\0;0;")+H1)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 16.08.2021, 14:41
 
Ответить
СообщениеДля даты ещё проще:
Код
=МИН(ЕСЛИ((A1:A1071>I1)*(B1:E1071>=H1);A1:A1071))
*И для значения
Код
=МАКС(ТЕКСТ(ИНДЕКС(B:E;ПОИСКПОЗ(I2;A:A;);)-H1;"0,00;\0;0;")+H1)
Автор - Светлый
Дата добавления - 16.08.2021 в 08:14
jakim Дата: Понедельник, 16.08.2021, 09:48 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1199
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
Формула

Код
=AGGREGATE(15;6;B1:E10000/(ROW($1:$10000)>MATCH(I1;A1:A10000;0))/(B1:E10000>=H1);1)

Значение 118,56 находится в 846 строке.
К сообщению приложен файл: 7415011.xlsx (48.8 Kb)


Сообщение отредактировал jakim - Понедельник, 16.08.2021, 09:49
 
Ответить
Сообщение
Формула

Код
=AGGREGATE(15;6;B1:E10000/(ROW($1:$10000)>MATCH(I1;A1:A10000;0))/(B1:E10000>=H1);1)

Значение 118,56 находится в 846 строке.
Автор - jakim
Дата добавления - 16.08.2021 в 09:48
bmv98rus Дата: Понедельник, 16.08.2021, 10:45 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
jakim, :D сообщения 2 и 3

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеjakim, :D сообщения 2 и 3
Автор - bmv98rus
Дата добавления - 16.08.2021 в 10:45
китин Дата: Понедельник, 16.08.2021, 10:50 | Сообщение № 15
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
kirdyk, - Прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)
сообщение #7

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениеkirdyk, - Прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)
сообщение #7
Автор - китин
Дата добавления - 16.08.2021 в 10:50
jakim Дата: Понедельник, 16.08.2021, 17:06 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1199
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
В таком случае можно применить формулу

Код
=AGGREGATE(15;6;INDEX(B1:E10000;AGGREGATE(15;6;ROW($1:$10000)/(ROW($1:$10000)>MATCH(I1;A1:A10000;0))/(B1:E10000>=H1);1);0)/(INDEX(B1:E10000;AGGREGATE(15;6;ROW($1:$10000)/(ROW($1:$10000)>MATCH(I1;A1:A10000;0))/(B1:E10000>=H1);1);0)>=H1);1)
К сообщению приложен файл: 3531304.xlsx (48.9 Kb)


Сообщение отредактировал jakim - Понедельник, 16.08.2021, 17:27
 
Ответить
Сообщение
В таком случае можно применить формулу

Код
=AGGREGATE(15;6;INDEX(B1:E10000;AGGREGATE(15;6;ROW($1:$10000)/(ROW($1:$10000)>MATCH(I1;A1:A10000;0))/(B1:E10000>=H1);1);0)/(INDEX(B1:E10000;AGGREGATE(15;6;ROW($1:$10000)/(ROW($1:$10000)>MATCH(I1;A1:A10000;0))/(B1:E10000>=H1);1);0)>=H1);1)
Автор - jakim
Дата добавления - 16.08.2021 в 17:06
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Возвращение ближайшего равного или большего значения (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!