Excel Формулы: Нахождение соответствующего значения.

[Олька]

Нужно перебрать все даты первого столбца и сравнить с текущей датой. Нам нужны только те даты что меньше или равны текущей даты. Затем нужно выбрать максимальную дату из отобранных ранее.

Затем смотрим второй столбец. Если направление совпадает с значением ячейки I20, то тогда это нас устраивает.

И в заключении, смотрим на максимальную дату из первого столбца и на наше направление, если все верно, то нужно вывести число, которое стоит напротив максимальной даты из столбца C.

Даты в столбце A не отсортированы по возрастанию или по убыванию, а находятся хаотично.

Если в максимальной дате направление не совпало, то нужно сделать анализ соседней меньшей даты и т.д.


Пример

Сегодня 29.10.2019, смотрим первый столбик, моему условию удовлетворяют даты А1:А8, теперь нужно отобрать максимальную дату, это получается А8, направление ячейки В8 нас удовлетворяет, теперь, например, в ячейку F16 нужно вывести число 5, так как оно стоит напротив максимальной даты.

Как это сделать с помощью функций экселя?

[вложение удалено администратором]

[Администратор]

Способ с использование допстолбца D.
В ячейке D1 формула:
=ЕСЛИ(A1<=СЕГОДНЯ();ЕСЛИ(B1=$I$20;A1;"");"")

В ячейке F16 формула:
=ИНДЕКС(C:C;ПОИСКПОЗ(МАКС(D:D);D:D;0);1)

[Администратор]

Способ с использованием "формулы массива". После записи формулы нажмите Ctrl+Shift+Enter, чтобы вокруг формулы появились фигурные скобки {}.

Формула просматривает на листе строки с 1 по 5 000.
Можете изменить это количество.
Если указать очень много строк, то формулы могут не считаться или файл может "тормозить".

Формула разбита на несколько строк, чтобы её было удобно читать. Можете удалить переносы строк.

=ИНДЕКС(C:C;
    ПОИСКПОЗ(
        МАКС(ЕСЛИ(A1:A5000<=СЕГОДНЯ();ЕСЛИ(B1:B5000=I20;A1:A5000;"");""));
        ЕСЛИ(A1:A5000<=СЕГОДНЯ();ЕСЛИ(B1:B5000=I20;A1:A5000;"");"");
        0);
1)


Как работает формула

ПОИСКПОЗ

ПОИСКПОЗ ищет в массиве максимальную дату, которая меньше или равна текущей дате, и в которой данные равны ячейке I20, и возвращает номер строки в этом массиве.
Массив создаётся в оперативной памяти компьютера, юзер не видит этот массив.

Первый параметр функции ПОИСКПОЗ.
Здесь создаётся в оперативной памяти компьютера массив, в ячейках которого находятся даты и пустые строки. Затем функция МАКС ищет в этом массиве максимальную дату.

Второй параметр функции ПОИСКПОЗ.
Здесь создаётся точно такой же массив, как в первом параметре.


ИНДЕКС

В этой функции указан полностью столбец C, т.к. на скорость работы формулы это не влияет, поэтому можете не указывать, с какой по какую строку надо анализировать столбец C.

[Олька]

Спасибо!!!

[Администратор]

Если используется умная таблица, то в формуле можно не указывать, с какой по какую строку надо анализировать. А можно указывать названия столбцов.
Недостаток этого способа: формула тяжело читается, т.к. становится много текста.

Формула массива. После записи формулы нажмите Ctrl+Shift+Enter, чтобы вокруг формулы появились фигурные скобки {}.

=ИНДЕКС(Таблица1[Сумма];ПОИСКПОЗ(МАКС(ЕСЛИ(Таблица1[[Дата ]]<=H6;ЕСЛИ(Таблица1[Товар]=I6;Таблица1[[Дата ]];"");""));ЕСЛИ(Таблица1[[Дата ]]<=H6;ЕСЛИ(Таблица1[Товар]=I6;Таблица1[[Дата ]];"");"");0);1)

[Олька]

Спасибо.