Поиск последнего повтора в 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))


Комментарии
Только для авторизованных пользователей

(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved