Задачи по поиску данных являются одними из самых популярных. Давайте разберемся с поиском последнего совпадения искомого значения.
Например, есть таблица с данными.
Вы хотите найти последнюю цену "Яблока".
Наиболее известная функция поиска ВПР (VLOOKUP)с этой задачей не справится, так как она по умолчанию возвращает первое найденное совпадение.
Максимально просто задача решается с помощью функции ПРОСМОТРХ (XLOOKUP).
В первом аргументе указываем искомое значение, затем задаем столбец для поиска. Третий аргумент определяет столбец с возвращаемым результатом. В четвертом аргументе указывается сообщение, которое появится если подходящего значения найдено не будет. Затем указывается тип сопоставления - нас интересует точное совпадение. И в последнем аргументе зададется направление поиска - с конца.
Задача будет решена.
У этого способа есть единственный жирный минус - функция ПРОСМОТРХ доступна только в последних версиях Эксель, а значит если вы пользуетесь старыми версиями (2019 и более ранними), то нужно искать альтернативное решение.
Наиболее приемлемое, с моей точки зрения, решение можно получить с помощью функций ИНДЕКС и ПОИСКПОЗ (INDEX, MATCH). Эта комбинация функций получила широкое распространение в качестве альтернативы функции ВПР.
Функция ИНДЕКС возвращает значение, которое находится в указанном номере строки выделенного диапазона. Например, найдем цену яблока в соответствующем столбце.
Сначала указывается столбец, в котором будет производиться поиск. Затем номер строки в выделенном диапазоне, из которого будет возвращено значение. В рассматриваемом примере первое вхождение находится в первой строке, а последнее в пятой.
Осталось определить положение последнего вхождение формулой, то есть чтобы эта позиция определялась автоматически. В этом и поможет функция ПОИСКПОЗ, которая производит поиск указанного значения в диапазоне ячеек и возвращает относительную позицию ячейки с искомым значением.
У функции ПОИСКПОЗ последний аргумент определяет тип сопоставления и обычно здесь указывается точное совпадение (0). Если сделать так, то получим первую позицию искомого значения.
Теперь мы точно знаем, что за поиск позиции отвечает функция ПОИСКПОЗ, а значит мы должны ее приспособить для поиска последнего значения.
Логика последующих вычислений может быть непроста для понимания, но вы можете использовать формулу в формате «как есть» не вникая в подробности и просто изменяя диапазоны на свои.
Итак, давайте разбираться. Я использую Excel 365, поэтому у меня есть динамические массивы, которые в ходе пояснения буду использовать для лучшей наглядности.
Создам простое условие, в котором буду сравнивать значения столбца с искомым значением.
Такая формула будет сравнивать каждое значение столбца с указанным в ячейке D1. В результате получим массив логических выражений ИСТИНА и ЛОЖЬ. ИСТИНА будет в том случае, если значения совпадают.
Теперь нам необходимо преобразовать эти значения. Дело в том, что сейчас они находятся в логическом виде, но в Эксель ИСТИНУ и ЛОЖЬ можно представить как 1 и 0. Чтобы преобразовать логический вид в цифровой умножим значения на единицу.
В результате получим массив данных, состоящих из нулей и единиц.
Ну а теперь самое главное - задействуем последний аргумент функции ПОИСКПОЗ. Если его не указывать или указать единицу, то функция ПОИСКПОЗ будет находить наибольшее значение, которое меньше или равно искомому значению. В качестве искомого значения можем указать любое число, большее единицы, например, 2. Искать будем в полученном диапазоне нулей и единиц. В качестве типа сопоставления укажем единицу или ничего указывать не будет.
При применении данного типа сопоставления просматриваемый массив обязательно должен быть упорядочен по возрастанию, так как функция будет проходит его сверху вниз. Как раз этот нюанс мы сейчас и используем, ведь наш массив состоит из нулей и единиц и после просмотра всего диапазона моей таблицы функция вернет позицию последней единицы, то есть именно ту позицию, которая нам и нужна. Но если мы изменим последнее значение в таблице на какое-то другое, то формула все равно вернет последнюю позицию, ведь функция ПОИСКПОЗ вернет позицию любого значения, которое меньше искомого и ноль удовлетворяет этому условию.
Поэтому прибегнем к еще одной хитрости. Вместо умножения на единицу разделим единицу на значения массива. В итоге получим массив единиц и ошибки деления на ноль.
Функция ПОИСКПОЗ игнорирует ошибки и поэтому в данном случае поиск позиции будет производиться только среди числовых значений, то есть среди единиц.
Останется собрать общую формулу из фрагментов.
Вот таким образом можно решить данную задачу. Если логика расчета вам не понятна, то просто используйте формулу, заменяя диапазон из столбца B на свой, из которого нужно вернуть значение. Диапазон из столбца А - на диапазон поиска. Ну и искомое значение из ячейки D1 на свое.
И так как в моей версии Эксель есть динамические массивы, то мне с данной формулой ничего делать не нужно. Если же вы пользуетесь Excel 2019 или более ранней версией, то нужно будет преобразовать данную формулу в формулу массива. Делается это нажатием сочетания клавиш Ctrl + Shift + Enter.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
