Поиск последнего повтора в MS EXCEL

Рассмотрим таблицу продаж, состоящую из столбцов Дата продажи и Сумма. Т.к. в день может быть несколько продаж, то столбец с датами содержит повторы. Задав в качестве критерия поиска дату, найдем номер строки, в которой содержится дата и сумма последней продажи (т.е. последний повтор даты, указанной в критерии). Найдем сумму первой и последней продажи в этот день.

Пусть таблица продаж размещена в диапазоне A7:B41. Даты продажи отсортированы по возрастанию.

Задача

Определим Сумму первой и последней продажи в заданный день (см. файл примера)

Решение

Для удобства определим Именованный диапазон Весь_диапазон_Дат как ссылку на диапазон =лист1!$A$7:$A$41

Дату продажи (ячейка D7) будем выбирать с помощью Выпадающего списка.

Это будет гарантировать, что в качестве критерия для поиска будут введены только даты из таблицы. Т.к. даты в таблице повторяются, а Выпадающий список не должен содержать повторы, то для источника строк Выпадающего списка сформируем в столбце H список Уникальных значений.

Сумму первой продажи найдем с помощью стандартной функции ВПР() с критерием ЛОЖЬ (точное совпадение) :
=ВПР(D7;A7:B41;2;ЛОЖЬ)

В случае повторяющихся значений функция ВПР() с критерием ЛОЖЬ возвращает первое (сверху) найденное значение, то что нам и требуется.

Сумму первой продажи найдем с помощью стандартной функции ВПР() с критерием ИСТИНА (приблизительное совпадение) :
=ВПР(D7;A7:B41;2;ИСТИНА)

В случае повторяющихся значений функция ВПР() с критерием ИСТИНА возвращает наибольшее найденное значение, которое меньше или равно, чем искомое значение. Т.к. перечень дат сортирован по возрастанию и искомое значение заведомо имеется в списке, то найденное значение как раз и будет последним повтором даты, указанной в качестве критерия.

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

Альтернативное решение

Решить задачу можно также с помощью формулы, которая определит строку содержащую последний повтор (т.е. последнюю продажу в заданный день):
=СУММПРОИЗВ(МАКС((D7=Весь_диапазон_Дат)*(СТРОКА(Весь_диапазон_Дат))))

Формула разместим в ячейке E10. Этапы вычислений можно легко увидеть с помощью клавиши F9 (выделите в Строке формул, например, выражение D2=Весь_диапазон_Дат, нажмите F9, вместо формулы отобразится ее результат).

А сумму последней продажи в заданный день можно найти с помощью функции ИНДЕКС():
=ИНДЕКС(B7:B41;E10-СТРОКА(B6))

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)

Комментарии

MCH

Вариант формулы, "Сумма последней продажи в этот день":
=ПРОСМОТР(2;1/(A7:A15=D7);B7:B15)
если событий в этот день нет, то вернет #Н/Д
исправить можно так:
=ЕСЛИ(СЧЁТЕСЛИ(D7;A7:A15);ПРОСМОТР(2;1/(A7:A15=D7);B7:B15);)