Особенности применения функции ВПР в Excel
Содержание
ВПР — инструмент построчного просмотра ячеек, который может использоваться для вертикального поиска требуемых значений. Данная функция принимает несколько аргументов, первый из которых — искомое значение. На практике пользователь может указывать несколько условий поиска вместо одного. Это существенно расширяет возможности данного инструмента, позволяя выполнять более точный поиск значений в больших таблицах. Способы применения ВПР с несколькими условиями рассмотрим в этом материале.
Формула массива — делаем поиск, используя 2 критерия
Это основной способ, позволяющий найти значения с использованием функции ВПР в программе Excel по двум условиям. Данный вариант предусматривает запись конструкции, в которой используется вспомогательная функция ЕСЛИ. На первый взгляд алгоритм может показаться очень сложным. Если разбираться в специфике и правилах оформления функции ВПР, можно быстро понять специфику этого метода и находить требуемые значения в любых массивах данных.
Рассмотрим на примере работу данной функции. Для этого будет использоваться таблица, в которой указаны различные модели смартфонов. Помимо названия модели указан объем памяти, цвет и стоимость. Используя данную таблицу, рассмотрим примеры поиска значений через ВПР по нескольким условиям.
К примеру, наша задача состоит в том, чтобы определить стоимость смартфона. При этом есть несколько условий: название, объем памяти и цвет устройства. В поиске нам поможет формула ВПР, связанная со вспомогательной функцией ЕСЛИ. Однако такая связка поможет только при поиске по 2 критериям. Сделать это можно следующим образом:
- Выделяем ячейку для записи результата. В нашем случае предварительно были указаны название модели и объем памяти. Эти два параметра являются условием для поиска. Задача состоит в том, чтобы определить стоимость смартфона, который соответствует этим параметрам.
Выделяем ячейку для записи результата - Далее приступаем к оформлению поисковой конструкции. В первую очередь вводим в строке формулу ВПР. Далее указываем первый критерий. В нашем случае это название модели. Его можно прописать вручную в строке, однако в этом нет никакого смысла. Проще указать ссылку на ячейку, в котором название уже записано. В нашем примере название модели указано в ячейке F Именно ее мы и укажем в качестве первого критерия.
Открываем ВПР и вводим первый критерий - Далее необходимо включить проверочную конструкцию для поиска второго условия. Для этого задействуется функция ЕСЛИ. Записываем данную формулу, открываем скобки и указываем диапазон поиска для второго критерия. Мы ищем модель по объему памяти, следовательно, это столбец B. Вписываем диапазон и далее вводим через знак равенства второй критерий — необходимый объем памяти. Он записан в ячейке G2, поэтому вписывать напрямую его ненужно — достаточно указать ссылку на эту клетку. После описания логической конструкции ставим знак — точку с запятой.
Вводим формулу ЕСЛИ - Чтобы вписанная функция ЕСЛИ работала корректно, необходимо также указать диапазон. Проще всего сделать ссылки на всю таблицу. После этого ставим двоеточие и вписываем в формулу две пустых кавычки. Это означает, что если соответствий с искомыми значениями нет, клетка останется пустой. После этого закрываем скобку — второй критерий для поиска готов.Вводим формулу ЕСЛИ
- Теперь остается только указать остальные аргументы, без которых функция ВПР в Excel работать не будет. Сначала вписываем номер столбика, где программа будет выполнять поиск значений. Нам необходимо найти стоимость, которая записана в колонке D. В нашей таблице это 4 столбец. Также нужно задать условие для интервального просмотра. Указываем число 0 — при поиске отображаются только точные совпадения. Между аргументами необходимо вписывать символ — точку с запятой. После этого закрываем скобку — формула для поиска ВПР по двум условиям готова.
Вписываем остальные аргументы ВПР - Это формула массива, поэтому при нажатии клавиши Enter выдается ошибка. В связи с этим после оформления конструкции, необходимо нажать комбинацию ++. В результате получаем искомое значение — стоимость смартфона, соответствующего заданным параметрам.
Результат выполнения
Как видим, в Эксель можно выполнить двойной поиск с помощью формулы ВПР. Однако описанный способ не является единственным возможным. Рассмотрим альтернативный вариант решения поставленной задачи.
Помощь вспомогательного столбца
Преимущество такого способа заключается в том, что можно выполнять поиск через ВПР по нескольким критериям — от двух и более. В этом случае потребуется вставить дополнительный столбец, в котором будут объединены условия для поиска. В нашем примере это название модели, объем памяти и цвет устройства. Итак, чтобы сделать поиск по 3 критериям одновременно, следуем инструкции:
- Выделяем первую ячейку исходной таблицы. Нажимаем правую кнопку мыши и в списке опций выбираем «Вставить». В выпадающем списке нажимаем «Вставить столбцы слева».
Вставляем столбец слева - Новый столбец нужен для записи значений из ячеек с критериями. Их можно объединить при помощи символа & или функции СЦЕП.
Объединяем ячейки с критериями - В результате получим ячейку, где будут записаны все 3 поисковых критерия. Это позволит, в свою очередь, выполнять множественный поиск в одной формуле ВПР. Применяем сцепление ячеек для всей колонки.
Готовый вспомогательный столбец - Теперь применяем формулу ВПР. Открываем скобки и в качестве условия для поиска вписываем ссылки на ячейки с критериями через амперсанд. Далее указываем диапазон — всю таблицу, а также номер столбца, в котором находится искомое значение. Мы ищем стоимость товара, это столбец Е, поэтому указываем цифру 5. Также ставим 0, чтобы выполнялся поиск точного совпадения.
Оформляем формулу ВПР - Указав необходимые аргументы, нажимаем . В результате выполнения формула найдет стоимость товара в столбце по трём параметрам.Результат выполнения
Используем функцию СУММЕСЛИМН
Для поиска значений по трем параметрам необязательно использовать формулу ВПР. Можно воспользоваться альтернативным вариантом — применяем функцию СУММЕСЛИМН. Она подойдет в том случае, если искомое значение является числом. Способ оформления поисковой конструкции несколько отличается от ВПР, однако его нельзя назвать очень сложным. Познакомимся с работой данной формулы на примере.
- Условие задачи остается аналогичным — нам надо найти стоимость смартфона по 3 параметрам (названию модели, объему памяти и цвету устройства). Выбираем ячейку для записи результата и вписываем формулу СУММЕСЛИМН. В качестве первого аргумента указываем диапазон ячеек, в котором нужно выполнить поиск значения. В нашем примере это стоимость — колонка Е.
Указываем диапазон суммирования - Далее нужно оформить критерии для поиска. Сначала записываем диапазон, в котором программа будет искать значение, затем ссылку на само значение. Повторяем эти шаги для всех трех критериев поиска. Отметим, что порядок записи критериев может быть произвольным. Главное, чтобы диапазон и искомое значение были указаны верно. В ином случае формула, с большой вероятностью, сработает некорректно и выдаст недостоверный результат.
Записываем условия поиска - После этого закрываем скобку и жмем . Программа находит значение из колонки «Стоимость», которое соответствует заданным параметрам поиска.
Результат выполнения
Отметим, что вспомогательный столбик А при таком варианте поиска не требуется. Он сохранился после работы в предыдущем примере. Также необходимо учитывать специфику работы функции СУММЕСЛИМН. Данная формула суммирует значения, если они соответствуют указанным критериям.
На практике это означает, что если в таблице будет два одинаковых смартфона, их стоимость в результате будет просуммирована. Однако если таблица не содержит повторяющиеся дубликаты по нескольким критериям, можно выполнять поиск числовых значений с большим числом условий. Сразу же можно сказать, что данная формула по многим показателям намного проще, чем функция ВПР. Однако у нее есть недостаток — она находит только числа, что неудобно, если необходимо подтянуть в ячейку текстовое значение.
Подводим итоги
Пользователям Excel доступны различные способы поиска значений в таблице. Один из вариантов — функция ВПР в Эксель с несколькими условиями. Реализовать данную возможность можно с помощью функции массива или путем оформления вспомогательного столбца с записанными в нем критериями поиска. Также, если нужно найти числовые значения, пользователь может воспользоваться альтернативным вариантом — функцией СУММЕСЛИМН, в которой также можно прописывать несколько условий для поиска значений.
Загрузка...
