Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в EXCEL. Аналог ВПР()
С помощью функции ВПР() можно выполнить поиск в столбце таблицы (называется ключевым столбцом), а затем вернуть значение из той же строки, но другого столбца. Здесь рассмотрим более сложный поиск: искать будем не среди всех значений ключевого столбца, а только среди значений удовлетворяющих дополнительному условию.
Точное совпадение
Сначала вкратце напомним работу ВПР() — VLOOKUP() с аргументом интервальный_просмотр = ЛОЖЬ (когда ищется значение в ключевом столбце точно соответствующее критерию).
Задача: Для заданного пользователем Товара найти в таблице соответствующую цену и количество (см. файл примера ).
Решение очевидно ( =ВПР(B10;B13:C15;2;0) ) и подробно рассмотрено в статье про ВПР() .
Предположим, что исходная таблица усложнилась — добавился столбец с номерами заказов.
Теперь необходимо искать заданный Товар не по всему столбцу Товар, а лишь в строках, относящегося к заданному пользователем Заказу.
Найдем количество и цену для товара Мандарины в Заказе 2.
В ячейках А21 и В21 введем номер Заказа и наименование товара.
В ячейке С21 введем формулу массива :
=ЕСЛИ(МАКС(ЕСЛИ((Таб1[Заказ]=A21)*(Таб1[Товар]=B21);Таб1[Количество];»»));МАКС(ЕСЛИ((Таб1[Заказ]=A21)*(Таб1[Товар]=B21);Таб1[Количество];»»));»В заказе нет искомого Товара»)
Аналогичную формулу можно использовать для поиска цены.
Ближайшее ЧИСЛО (ДАТА)
В предыдущей задаче предполагалось, что наименование Товара присутствует в Заказе в единственном экземпляре и точно соответствует критерию поиска. Однако, существует класс задач, когда это не так. Особенно часто встречаются задачи поиска ближайшего ЧИСЛА (если точное значение в ключевом столбце не найдено, то выводится ближайшее).
Примечание . Формулы, созданные для поиска ближайших ЧИСЕЛ, работают и для ДАТ, т.к. даты в MS EXCEL хранятся в числовом формате .
Сначала вкратце напомним работу ВПР() — VLOOKUP() с аргументом интервальный_просмотр = ИСТИНА (когда ищется значение в ключевом столбце точно или приблизительно совпадающее с критерием).
Задача: Для указанной пользователем Даты найти соответствующую ей цену (см. файл примера ).
Напомним, что для ВПР() с аргументом интервальный_просмотр = ИСТИНА требуется сортированный по возрастанию ключевой столбец (Дата). При отсутствии в столбце точного совпадения выводится наибольшее значение, которое меньше искомого. При наличии нескольких одинаковых подходящих значений в ключевом столбце, учитывается, то что ниже. Поэтому Цена выведена 220р., а не 240 и не 230.
Теперь необходимо для заданной пользователем Даты найти соответствующую ей цену, но эта Цена и дата должны относится к заданному пользователем Продавцу.
Другими словами, найдем цену на дату, ближайшей к заданной, но только для определенно продавца.
В ячейках А25 и В25 введем Продавца и дату.
Прежде чем писать формулу, определимся, что значит «ближайший». На самом деле можно дать несколько определений, нужный вариант выбирается исходя из условий решаемой задачи.
Вот несколько возможных определений:
1. В терминах функции ВПР() «ближайший» — это наибольшее значение, которое меньше искомого. При наличии нескольких одинаковых подходящих значений в ключевом столбце, учитывается, то что расположено ниже в таблице. Понятно, что такое значение может быть не обязательно ближайшим.
2. Другой вариант: наименьшее значение, которое больше искомого. При наличии нескольких одинаковых значений в ключевом столбце, учитывается, то что выше.
3. Ближайшее, т.е. та дата, которая ближе, чем все остальные (м.б. раньше или позже заданной). Если таких дат несколько, то учитывается нижняя и выводится соответствующая ей цена.
Еще несколько вариантов предложено в файле примера .
Понятно, что для каждого определения ближайшего требуется своя формулы массива (все их можно найти в файле примера ).
Формулы для определения цены, как правило, используют результат формулы определяющей ближайшую дату.
Ближайшее время
Поскольку как и дата, так и время в EXCEL хранятся в числовом формате (см. здесь https://excel2.ru/articles/kak-excel-hranit-datu-i-vremya ), то формулы будут работать и для дат с указанием времени, например 03.12.2018 15:06.
В файле примера есть лист для демонстрации работы функции ВПР() для столбца данных со временем (требуется сортировка по возрастанию).
На том же листе решена и другая задача: в столбце, содержащих время в часах и минутах, выделяются строки, которые попадают в заданный интервал времени (сортировка не обязательна).
Функции MS Excel ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) – более гибкая альтернатива ВПР
Многие пользователи знают и применяют формулу ВПР. Известно также, что ВПР имеет ряд особенностей и ограничений, которые несложно обойти. Однако есть нюанс, который значительно ограничивает возможности функции ВПР.
ВПР требует, чтобы в диапазоне с искомыми данными столбец критериев всегда был первым слева. Это обстоятельство, конечно, является ограничением ВПР. Как же быть, если искомые данные находятся левее столбца с критерием? Можно, конечно, расположить столбцы в нужном порядке, что в целом, является неплохим выходом из ситуации. Но бывает так, что сделать этого нельзя, или трудно. К примеру, вы работаете в чужом документе или регулярно получаете новый отчет. В общем, нужно решение, не зависящее от расположения столбцов. Такое решение существует.
Нужно воспользоваться комбинацией из двух функций: ИНДЕКС и ПОИСКПОЗ. Формула работает следующим образом. ИНДЕКС отсчитывает необходимое количество ячеек вниз в диапазоне искомых значений. Количество отсчитываемых ячеек определяется по столбцу критериев функцией ПОИСКПОЗ. Работу комбинации этих функций удобно рассмотреть с середины, где вначале находится номер ячейки с подходящим критерием, а затем этот номер подставляется в ИНДЕКС.
Таким образом, чтобы подтянуть значение цены, соответствующее первому коду (книге), нужно прописать такую формулу.
Следует обратить внимание на корректность ссылок, чтобы при копировании формулы ничего не «съехало». Протягиваем формулу вниз. Если в таблице, откуда подтягиваются данные, нет искомого критерия, то функция выдает ошибку #Н/Д.
Довольно стандартная ситуация, с которой успешно справляется функция ЕСЛИОШИБКА. Она перехватывает ошибки и вместо них выдает что-либо другое, например, нули.
Конструкция формулы будет следующая:
Вот, собственно, и все.
Таким образом, комбинация функций ИНДЕКС и ПОИСКПОЗ является полной заменой ВПР и обладает дополнительным преимуществом: умеет находить данные слева от столбца с критерием. Кроме того, сами столбцы можно двигать как угодно, лишь бы ссылка не съехала, чего нельзя проделать с ВПР, т.к. количество столбцов там указывается конкретным числом. Посему комбинация ИНДЕКС и ПОИСКПОЗ более универсальна, чем ВПР.
Microsoft представила замену ВПР, ГПР а также ПОИСКПОЗ
Введение динамических массивов, по сути историческое обновление, которые в корне изменяет подход к построению формул, в чем то изменяет мышление работы в Excel.
С введением динамических массивов было вполне очевидно, что анонсом семи новых функций Microsoft не ограничится. В августе 2019, почти год после анонса масштабного обновления (сентябрь 2018), Microsoft представила замену одной из самых популярных функций в Excel.
Да, речь идет об ВПР или VLOOKUP в английской версии, попутно обновилась и ГПР (HLOOKUP, соответственно). На самом деле новая функция ПРОСМОТРХ (XLOOKUP) просто заменяет их обе. Кстати, в конце стоит именно латинский символ «X» (экс), а не русская «Х» (ха), что доставляет неудобств, если вы пользуетесь русской версией электронного процессора.
А вот для функции ПОИСКПОЗ (MATCH) была добавлена своя функция ПОИСКПОЗX XMATCH, которая, использует возможности динамических массивов, как и XLOOKUP.
Представление ПРОСМОТРХ (XLOOKUP)
Наименование функции намекает, на то, что она будет являться заменой, как для вертикального поиска, так и для горизонтального. Соответственно, данная функция будет заменять обе существующие и ВПР, и ГПР.
При всем при этом, синтаксис данной функции чрезвычайно прост:
- искомое_значение — значение, которые мы будем искать;
- просматриваемый_массив — массив, где будет осуществляться поиск;
- возвращаемый_массив — массив из которого нужно будет возвращать значения.
- режим_сопоставления и режим_поиска — необязательные параметры, позволяющие более точно настроить использование функции.
Для тех, кто освоил использование ВПР, освоение данной функции не составит никакого труда. Простейший пример представлен на анимированном рисунке.
Почему Microsft представила новую функцию?
Все дело в тех недостатках, которые существуют при использовании ВПР (как и ГПР):
- использование «приблизительного» поиска по умолчанию. По умолчанию (если забыть указать 4й параметр), функции будут осуществлять приблизительный поиск, а если список не будет отсортирован по возрастанию это приведет к неправильному результату;
- номер столбца, с которого следует вернуть результат, является числом, поэтому, если в искомом массиве будут удалятся или добавляться столбцы, данное значение также придется изменять;
- невозможно возвращать значения слева от искомого столбца;
- нет возможности осуществлять поиск «с конца»;
- нельзя возвращать следующий элемент большего размера. При использовании «приблизительного» поиска искомый диапазон должен быть отсортирован по возрастанию, следовательно получится вернуть только первое значение (которое соответствует искомому или наименьшее, которое больше искомого);
- использование большего объема данных, нежели нужно в вычислениях.
Последний пункт означает, что массивы в котором осуществляет поиск искомого значения и возвращаемого должны быть связаны, что самым неблагоприятным образом сказывается на производительности данной функции, при работе с большими таблицами. Также новая функция не требует, чтобы искомый и возвращаемый диапазоны были связаны, так и может работать как в горизонтальной плоскости, так и вертикальной.
Остальные пункты в той или иной мере могут быть решены умелым использованием самой ВПР или связкой функций ИНДЕКС и ПОИСКПОЗ, например, возвращение значения слева от искомого столбца.
О полных возможностях функции ПРОСМОТРХ в нашем справочнике.
Немного об ПОИСКПОЗX (XMATCH)
В дополнении к XLOOKUP была анонсирована и новая функция ПОИСКПОЗX (XMATCH), с похожим на XLOOKUP синтаксисом, однако возвращает индекс искомого значения.
Подробно об использовании данной функции, также можно узнать в справочнике.