Функция ВПР в Excel
Эта статья о функции ВПР. Он проведет вас через пошаговые инструкции для функции ВПР под названием «Функция ВПР в Excel для чайников». В этой статье мы более подробно рассмотрим описание, синтаксис и примеры функции ВПР в Excel. Мы также увидим, как использовать функцию ВПР с несколькими условиями, и проанализируем основные ошибки, для которых функция ВПР не работает.
Синтаксис и описание функции ВПР в Excel
Итак, поскольку второе название этой статьи — «Функция ВПР в Excel для чайников», давайте начнем с выяснения, что такое функция ВПР и для чего она нужна? Функция ВПР на английском языке ВПР ищет указанное значение и возвращает соответствующее значение из другого столбца.
Как работает ВПР? ВПР в Excel выполняет поиск в ваших списках данных на основе уникального идентификатора и предоставляет вам некоторую информацию, связанную с этим уникальным идентификатором.
Буква «B» в VLOOKUP означает «вертикальный». Он используется для различения функций VLOOKUP и HORIZ LOOK, которые ищут значение в верхней строке массива («G» означает «горизонтальный»).
ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.
Синтаксис функции ВПР следующий:
Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательны, последний — необязательный.
- lookup_value — это значение, которое нужно найти.
Это может быть значение (число, дата или текст), ссылка на ячейку (ссылка на ячейку, содержащую значение подстановки) или значение, возвращаемое другой функцией Excel. Например:
- Поиск числа: = ВПР (40; A2: B15; 2) — формула будет искать число 40.
- Текст поиска: = ВПР («яблоки»; A2: B15; 2) — формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
- Найдите значение из другой ячейки: = ВПР (C2; A2: B15; 2) — формула будет искать значение в ячейке C2.
- таблица состоит из двух или более столбцов данных.
Помните, что функция ВПР всегда ищет желаемое значение в первом столбце таблицы. Ваша таблица может содержать различные значения, такие как текст, дату, числа или логические значения. Значения не чувствительны к регистру, что означает, что прописные и строчные буквы считаются идентичными.
Таким образом, наша формула = ВПР (40; A2: B15; 2) будет искать «40» в ячейках с A2 по A15, потому что A — это первый столбец таблицы A2: B15.
- column_number — это номер столбца в таблице, из которого должно быть возвращено значение в соответствующей строке.
Крайний левый столбец в указанной таблице — 1, второй столбец — 2, третий — 3 и т.д.
Итак, теперь вы можете прочитать всю формулу = ВПР (40; A2: B15; 2). Формула ищет «40» в ячейках с A2 по A15 и возвращает соответствующее значение из столбца B (поскольку B является вторым столбцом в указанной таблице A2: B15).
4 interval_lookup определяет, ищете ли вы точное совпадение (FALSE) или приблизительное совпадение (TRUE или опущено). Последний параметр необязательный, но очень важный.
Функция ВПР в Excel примеры
Теперь давайте рассмотрим несколько примеров использования функции ВПР на реальных данных.
Функция ВПР на разных листах
На практике формулы ВПР редко используются для поиска данных на одном листе. В большинстве случаев вам нужно будет искать и извлекать соответствующие данные с другого листа.
Чтобы использовать ВПР из другого листа Excel, вам необходимо ввести имя листа и восклицательный знак в аргументе таблицы перед диапазоном ячеек, например = ВПР (40; Лист2! A2: B15; 2). Формула указывает, что диапазон поиска A2: B15 находится на Листе 2.
Конечно, вам не нужно вручную вводить имя листа. Начните вводить формулу, а когда дело доходит до аргумента таблицы, перейдите на рабочий лист поиска и выберите диапазон с помощью мыши.
Формула, которую вы видите на изображении ниже, выполняет поиск текста в ячейке A2 («Продукт 3») в столбце A (1-й столбец диапазона поиска A2: B9) на листе «Цены »:
Функция ВПР в Excel – Функция ВПР на разных листах
Обратите внимание, что рекомендуется использовать абсолютные ссылки на ячейки (со знаком $) в аргументе таблицы. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки.
Как использовать именованный диапазон или таблицу в формулах ВПР
Если вы собираетесь использовать один и тот же диапазон поиска в нескольких формулах ВПР, вы можете создать для него именованный диапазон и ввести имя непосредственно в аргумент таблицы своей формулы ВПР.
Чтобы создать именованный диапазон, просто выберите ячейки и введите имя в поле «Имя» слева от панели «Формула ».
Функция ВПР в Excel – Присвоение имени диапазону
Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:
Функция ВПР в Excel – Пример функции ВПР с именем диапазона
Большинство имен диапазонов в Excel применяются ко всей книге, поэтому вам не нужно указывать имя рабочего листа, даже если диапазон поиска находится на другом листе. Такие формулы намного проще понять. Кроме того, использование именованных диапазонов может быть хорошей альтернативой абсолютным ссылкам на ячейки. Поскольку именованный диапазон не изменяется при копировании формулы в другие ячейки, вы можете быть уверены, что диапазон поиска всегда будет оставаться правильным.
Если вы преобразовали диапазон ячеек в полнофункциональную электронную таблицу Excel (вкладка «Вставка» -> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, и Microsoft Excel автоматически добавит имена столбцов или имя таблицы в формулу:
Функция ВПР в Excel – Пример функции ВПР с именем таблицы
Полная формула может выглядеть так:
или также = ВПР («Продукт 1»; Таблица6; 2).
Как и именованные диапазоны, имена столбцов являются константами, и ссылки на ячейки не изменятся независимо от того, откуда копируется формула ВПР.
Функция ВПР с несколькими условиями
Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:
Функция ВПР в Excel – Таблица исходных данных
Предположим, нам нужно использовать функцию ВПР с несколькими условиями. Например, для поиска цены продукта по двум критериям: названию продукта и его типу.
- Чтобы использовать функцию ВПР с несколькими условиями, вам нужно вставить дополнительный столбец в начале, в котором будет храниться информация с названием и типом продукта.
Затем на листе «Цены» введите столбец и введите следующую формулу в ячейку A2:
С помощью этой формулы мы объединяем значения столбцов «Продукт» и «Тип». Заполняем все ячейки.
Таблица поиска теперь выглядит так:
Функция ВПР в Excel – Добавление вспомогательного столбца
- Теперь в ячейке C2 листа «Продажи» напишите следующую формулу ВПР:
Заполняем остальные ячейки, и в результате получаем цены на каждый товар по типу:
Функция ВПР в Excel – Пример ВПР с несколькими условиями
Теперь посмотрим на ошибки функции ВПР.
Почему не работает функция ВПР
В этой части статьи мы увидим, почему не работает функция ВПР и возможные ошибки функции ВПР.
Неверное положение столбца для поиска
Столбец таблицы, в котором выполняется поиск, ДОЛЖЕН быть крайним левым столбцом.
- Переместите столбец для поиска в крайнее левое положение в таблице.
- Или создайте вспомогательный повторяющийся столбец слева от таблицы.
Диапазон таблицы не заблокирован
Если первое значение отображалось правильно и после растягивания формулы ВПР некоторые ячейки содержат ошибку # Н / Д, диапазон таблицы не корректируется.
- Используйте абсолютные ссылки ($) для привязки диапазона таблицы, чтобы формула использовала тот же диапазон при компиляции.
- Или используйте именованный диапазон
Невозможно найти точное совпадение (если поиск точного значения выбран в представлении диапазона (0)
При интервальном сканировании поиск выполняется для ближайшего значения (1), и поисковая таблица не сортируется.
Отсортируйте первый столбец таблицы в возрастающем порядке по имени.
Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы.
Используйте функции ПРОБЕЛ или ОБРЕЗАТЬ.
Значение номера столбца больше, чем количество столбцов в таблице
Проверьте номер столбца, в котором содержится возвращаемое значение.
В формуле отсутствуют кавычки
Если в качестве значения поиска вы используете не ссылку на ячейку, а текст, его необходимо заключить в кавычки.
= ВПР («Продукт 1»; Цена! $ A $ 2: $ B $ 8; 2; 0)
Надеюсь, теперь и для чайников функция ВПР в Excel понятна.
Поиск и подстановка по нескольким условиям
Постановка задачи
Если вы опытный пользователь Microsoft Excel, вы должны быть знакомы с поиском и заменой ВПР или ВПР (если вы еще этого не сделали, сначала прочтите эту статью, чтобы стать им). Для понимающих, афишировать это не нужно — без этого не обходится ни один сложный расчет в Excel. Однако есть проблема: эта функция умеет искать данные только по совпадению параметра. Что, если у нас их несколько?
Допустим, у нас есть база данных цен на товары за несколько месяцев:
Вы должны найти и извлечь цену определенного продукта (нектарин) в конкретный месяц (январь), например, выйти на 152, но автоматически, например, используя формулу. ВПР в чистом виде здесь не поможет, но есть много других способов исправить это.
Способ 1. Дополнительный столбец с ключом поиска
Это наиболее очевидный и простой (хотя и не самый удобный) способ. Поскольку стандартная функция ВПР может искать только один столбец, а не несколько столбцов, нам нужно создать один среди многих!
Давайте добавим еще один столбец рядом с нашей таблицей, где мы вставим название продукта и месяц в единый набор с помощью оператора конкатенации (&), чтобы получить уникальный ключевой столбец для поиска:
Теперь вы можете использовать знакомую функцию ВПР, чтобы найти пару январских нектаринов, вставленных из ячеек H3 и J3 во вновь созданный ключевой столбец:
Плюсы: Простой способ, привычная функция, работает с любыми данными.
Минусы: вам нужно создать дополнительный столбец, а затем, возможно, также скрыть его от пользователя. При изменении количества строк в таблице, чтобы расширить формулу конкатенации на новые строки (хотя это можно упростить, используя интеллектуальную таблицу).
Способ 2. Функция СУММЕСЛИМН
Если вам нужно найти ровно одно число (в нашем случае цена — это просто число), то вместо ВПР можно использовать функцию СУММЕСЛИ, появившуюся из Excel 2007. Теоретически эта функция выбирает и суммирует числовые значения По разным (до 127!) Условиях… Но если в нашем списке нет повторяющихся товаров в течение месяца, он просто отобразит значение цены для конкретного товара и месяца:
Плюсы: не требуются дополнительные столбцы, решение легко масштабируется для большего количества условий (до 127), быстрое вычисление.
Минусы: работает только с числовыми выходными данными, неприменимо для текстового поиска, не работает в более ранних версиях Excel (2003 и ранее).
Способ 3. Формула массива
Я уже подробно описал, как использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ как более мощную альтернативу ВПР (с видео). В нашем случае вы можете использовать их для поиска по нескольким столбцам в форме формулы массива. Из-за этого:
- Выделите пустую зеленую ячейку в том месте, где вы хотите получить результат.
- Введите следующую формулу в строку формул:
- Нажмите в конце не Enter, а комбинацию Ctrl + Shift + Enter, чтобы ввести формулу не как обычную, а как формулу массива.
Как это на самом деле работает:
Функция ИНДЕКС возвращает содержимое N-й ячейки по порядку из диапазона цен C2: C161. В этом случае серийный номер нужной ячейки определяется функцией ПОИСК. Выполняет поиск набора с названием продукта и месяцем (NectarineJanuary) по очереди во всех ячейках диапазона A2: A161 и B2: B161, вставленных из двух столбцов, и возвращает порядковый номер ячейки, в которой найдено точное совпадение. Фактически, это первый способ, но ключевой столбец создается практически внутри формулы, а не в ячейках листа.
Плюсы: нет необходимости в отдельном столбце, работает как с числами, так и с текстом.
Минусы: он значительно замедляется в больших таблицах (как, кстати, и во всех формулах массива), особенно если вы указываете диапазоны «с полем» или целые столбцы сразу (т.е вместо A2: A161 введите A: A и так далее) о) Матричные формулы для многих в принципе непривычны (тогда вам сюда).