Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск ближайшего к определенной дате значения., без VBA.
 
#1
16.03.2018 20:55:48
Доброго времени суток. Столкнулся с такой проблемой: есть две таблицы в одну (на листе FXRB) данные подгружаются автоматически из вне, в другой формируются с использованием формулы =ЕСНД(ИНДЕКС(FXRB[Цена];ПОИСКПОЗ([@Дата];FXRB[Дата]));""). Все хорошо, но в таблице FXRB на искомые даты может не быть значений и в этом случае нужно выбрать значение с предыдущего дня, значения которого тоже могут оказаться пустыми, тогда нужно вернутся еще на один день назад и так до тех пор пока значения не будут найдены. Решил эту проблему таким способом:
Скрытый текст

Но формула очень громоздкая получается особенно если дней пустых окажется больше (здесь только на 4  дня назад, а максимум может на 30).
Вопрос можно ли как-то упростить данную простыни или решить другим способом (ВПР() сокращает, но не на много или я что-то упускаю).
З.Ы. Как решить это с помощью VBA представляю, но не хотелось бы.
З.З.Ы. файл прикладываю.
 
 
 
#2
16.03.2018 21:41:39
Добрый вечер!
Вроде сходится с Вашим результатом. Формула в F8
=ПРОСМОТР(2;1/(FXRB!$B$2:ИНДЕКС(FXRB[Цена];-ПРОСМОТР(;-ПОИСКПОЗ(A8;FXRB[Дата];{0;1}))));FXRB[Цена])
Изменено: Akropochev - 16.03.2018 21:44:18
 
 
 
#3
16.03.2018 21:43:23
Цитата
Akropochev написал:
Вроде сходится с Вашим результатом
и с названием темы совпадает :-) . C Вас название.

=IFERROR(LOOKUP(2;1/(FXRB[Дата]<=[@Дата])/(FXRB[Цена]<>"");FXRB[Цена]);"")
При условии, что данные в исходной таблице отсортированы по возрастанию даты.

Поиск ближайшего к определенной дате значения.  
Изменено: БМВ - 16.03.2018 22:38:45
По вопросам из тем форума, личку не читаю.
 
 
 
#4
16.03.2018 21:45:31
БМВ, Куда бы Если прикрутить..)
 
 
 
#5
16.03.2018 21:47:01
Огромное спасибо Akropochev. Да работает. Пошёл разбираться как это работает. :-)
 
 
 
#6
16.03.2018 21:49:16
Akropochev, вот из-за Вас автор так и не понял, что с названием темы у него проблема. В следующий раз снова придёт с аналогичным названием, из которого ничего не ясно...
Куда Вы торопитесь?
 
 
 
#7
16.03.2018 21:53:18
Sogarn,
Не торопитесь. Мой вариант ужасен. Воспользуйтесь Вариантом БМВ,
 
 
 
#8
16.03.2018 21:56:13
БМВ вам также огромное спасибо за помощь. Ваш вариант изящен, им и воспользуюсь.
Изменено: Sogarn - 16.03.2018 21:56:39
 
 
 
#9
16.03.2018 21:59:10
Предлагаю формулу
Код
=IFERROR(AGGREGATE(15;6;FXRB!B$2:B$1000/(FXRB!A$2:A$1000=AGGREGATE(14;6;FXRB!A$2:A$1000/(FXRB!A$2:A$1000<=Портфель!A8)/(FXRB!B$2:B$1000<>"");1));1);"")
 
 
 
#10
16.03.2018 22:01:54
offtop
Akropochev, Не переживайте, чуть больше года назад я тоже получал подзатыльники от Модераторов, также спешил, писал длинные формулы, которые vikttur, в двое упрощал, да и сейчас порой ошибаюсь.
А по названию темы - тема не алгоритм действий, а задача. Зачем там "цикличная проверка предыдущих строчек."?

jakim  и его неразлучный друг AGGRIGATE :-)
Изменено: БМВ - 16.03.2018 22:06:36
По вопросам из тем форума, личку не читаю.
 
 
 
#11
17.03.2018 07:41:26
Даты на листе "FXRB" должны находиться по возрастанию.
Используется формула массива - завершайте ввод формулы не клавишей Enter (как Вы это обычно делаете), а сочетанием клавиш Ctrl+Shift+Enter. Формула для "C3":
=ЕСЛИОШИБКА(ИНДЕКС(FXRB[Цена];НАИБОЛЬШИЙ(ЕСЛИ(FXRB[Дата]<=[@Дата];ЕСЛИ(FXRB[Цена]<>"";СТРОКА(FXRB[Дата])-1));1);1);"")
У файла я сделал формат xlsb, т.к. этот формат быстрее работает, если в файле много формул.
Изменено: Karataev - 17.03.2018 07:44:17
 
 
 
Страницы: 1
Наверх