Если вы опытный пользователь Microsoft Excel, вы должны быть знакомы с ВПР или ВПР (если вы еще этого не сделали, сначала прочитайте эту статью, чтобы стать одним из них). Для тех, кто понимает, нет нужды объявлять об этом; без него не обходится ни один сложный расчет в Excel. Однако есть проблема: эта функция может искать данные только по совпадению с параметром. А если нас несколько?
Допустим, у нас есть база цен на товары за разные месяцы:
Необходимо найти и извлечь цену на определенный товар (Нектарин) в определенный месяц (январь), то есть получить выход152, но автоматически, то есть по формуле. ВПР в чистом виде здесь не поможет, но есть и другие способы решить эту проблему.
Способ 1. Дополнительный столбец с ключом поиска
Это самый очевидный и простой способ (хотя и не самый удобный). Поскольку обычная функция ВПР может искать только в одном столбце, а не во многих, нам нужно сделать один из многих!
Давайте добавим еще один столбец сбоку от нашей таблицы, где мы объединим название продукта и месяц в одно целое число, используя оператор конкатенации (&), чтобы получить уникальный ключевой столбец для поиска:
Теперь вы можете использовать знакомую функцию ВПР, чтобы найти вставленную пару NectarineJanuary из ячеек 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 и т.д.) многие непривычны формулы массива в принципе (тогда вам сюда).
Функция ВПР с несколькими условиями критериев поиска в Excel
Функция ВПР (вертикальный поиск) выполняет поиск в таблице данных и на основе критериев поискового запроса возвращает соответствующее значение из указанного столбца. Часто бывает необходимо использовать несколько условий в поисковом запросе одновременно. Но по умолчанию эта функция не может обрабатывать более одного условия. Поэтому вам нужно использовать очень простую формулу, которая позволит вам расширить возможности функции ВПР по нескольким столбцам одновременно.
Работа функции ВПР по нескольким критериям
Для ясности мы обсудим формулу ВПР на примере нескольких условий. Например, мы будем использовать схематический отчет о доходах торговых представителей за квартал:
В этом отчете вам нужно найти показатель дохода для конкретного торгового представителя на определенную дату. Учитывая условия поиска, наш запрос должен содержать 2 условия:
- — Дата поступления товара в кассу.
- — Имя торгового представителя.
Для решения этой задачи воспользуемся функцией ВПР по различным условиям и составим следующую формулу:
- В ячейке C1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
- В ячейку С2 введите имя торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
- В ячейке С3 мы получим результат поиска, для этого мы должны ввести туда формулу:
- После ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter для подтверждения, так как формулу необходимо выполнить на массиве.
Результат поиска в таблице по двум условиям:
Вы нашли сумму дохода для конкретного торгового представителя на определенную дату.
Анализ принципа работы формулы функции ВПР при различных условиях:
Первый аргумент функции =ВПР() является первым условием для поиска значения в таблице отчета о прибылях и убытках торгового представителя. Второй аргумент содержит виртуальную таблицу, созданную в результате массивного вычисления логической функции =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Поэтому в памяти создается массив условных данных с элементами значений ИСТИНА и ЛОЖЬ.
Затем, благодаря формуле, в памяти программы каждый истинный элемент заменяется набором данных из 3-х элементов:
- элемент Дата.
- элемент — Фамилия.
- элемент — Доход.
И каждый ложный элемент в памяти заменяется набором из 3-х элементов пустых текстовых значений («»). В результате в памяти программы создается новая таблица, с которой уже будет работать функция ВПР. Игнорирует все пустые наборы данных элементов. А непустые элементы сравниваются со значением ячейки C1, используемой в качестве первого критерия поискового запроса (Дата). Одним словом, функция ВПР проверяет таблицу в памяти с условием поиска. Если результат совпадения положительный, функция возвращает значение элемента в третьем столбце (доход) условной таблицы. Это связано с тем, что третий аргумент указывает столбец номер 3, из которого берутся значения.
Подробнее: формулы VPR в примерах Excel
А из какого столбца брать возвращаемое значение, уже указано в третьем аргументе.
Число 0 в последнем аргументе функции указывает на то, что совпадение должно быть абсолютно точным.
ВПР (функция ВПР)
ФУНКЦИЯ ВПР используется, когда вы хотите искать элементы в таблице или в диапазоне построчно. Например, вы можете найти цену детали автомобиля по номеру детали или получить имя сотрудника на основе его кода сотрудника.
Совет. Чтобы получить дополнительные сведения о функции ВПР, посмотрите эти видео на YouTube от экспертов сообщества Excel!
Простейшая функция ВПР означает следующее:
=ВПР(необходимы условия поиска, номер столбца в диапазоне, содержащем возвращаемое значение, возвращено приблизительное или точное совпадение, обозначенное как 1/истина или 0/ложь).
Совет. Секрет функции ВПР заключается в том, чтобы упорядочить данные таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения для поиска (Количество).
Используйте функцию ВПР для поиска значения в таблице.
ВПР(искомое_значение, таблица, номер_столбца, [искание])
= ВПР(A2, A10: C20, 2, ИСТИНА)
Значение для поиска. Значение, которое вы хотите найти, должно находиться в первом столбце диапазона ячеек, указанного в аргументе info_table .
Например, если матричная таблица охватывает ячейки B2:D7, искомое_значение должно находиться в столбце B.
Искомое_значение может быть значением или ссылкой на ячейку.
Диапазон ячеек, в которых функция ВПР будет искать искомое_значение и возвращать значение.
Первый столбец диапазона ячеек должен содержать Lookup_Value. Диапазон ячеек также должен содержать возвращаемое значение, которое необходимо найти.
Номер столбца (начиная с 1 для крайнего левого столбца массива info_table), который содержит возвращаемое значение.
Логическое значение, определяющее, должна ли функция ВПР находить приблизительное или точное совпадение.
Приблизительное совпадение: 1/true предполагает, что первый столбец в таблице отсортирован в числовом или алфавитном порядке, а затем находит ближайшее значение. Это метод по умолчанию, если не указано иное. Например, =ВПР(90, a1: B100, 2, истина).
Точное совпадение: 0/false находит точное значение в первом столбце. Например, =ВПР(«Кузнец»; a1: B100; 2; ложь).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть значение, которое вы ищете.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, который содержит возвращаемое значение. Например, если задан диапазон B2:D11, число B должно быть в первом столбце, а второе должно быть «C» и т д
При желании вы можете указать слово ИСТИНА, если вам нужно приблизительное совпадение, или слово ЛОЖЬ, если вы хотите точное совпадение возвращаемого значения. Если вы ничего не укажете, значение по умолчанию всегда равно TRUE, что является приблизительным совпадением.
Теперь объедините все приведенные выше аргументы следующим образом:
=ВПР(искомое значение, диапазон с искомым значением, номер столбца в возвращаемом диапазоне, приблизительное совпадение (истина) или точное совпадение (ложь)).
Вот несколько примеров использования функции ВПР.
Функция ВПР в Excel
Эта статья о функции ВПР. Вы увидите пошаговую инструкцию для функции ВПР под названием «Функция ВПР в Excel для чайников». В этой статье мы подробно рассмотрим описание, синтаксис и примеры функции ВПР в Excel. А также рассмотрим, как использовать функцию ВПР при множественных условиях и обсудим основные ошибки, почему не работает функция ВПР.
Синтаксис и описание функции ВПР в Excel
Итак, поскольку второе название этой статьи — «Функция Excel VLOOKUP для чайников», давайте начнем с изучения того, что такое функция VLOOKUP и что она делает. Функция ВПР на английском языке ВПР ищет указанное значение и возвращает соответствующее значение из другого столбца.
Как работает функция ВПР? Функция ВПР в Excel выполняет поиск в ваших списках данных на основе уникального идентификатора и предоставляет вам информацию, связанную с этим уникальным идентификатором.
Буква «В» в ВПР означает «вертикальный». Он используется для различения функций ВПР и ГПР, которые ищут значение в верхней строке массива («H» означает «горизонтальный»).
Функция ВПР доступна во всех версиях 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.
- номер_столбца — это номер столбца в таблице, из которого должно быть возвращено значение соответствующей строки.
Подробнее: Формула умножения в Excel
Крайний левый столбец в указанной таблице — 1, второй столбец — 2, третий — 3 и т д
Итак, теперь вы можете прочитать полную формулу =VLOOKUP(40;A2:B15;2). Формула ищет «40» в ячейках с A2 по A15 и возвращает соответствующее значение из столбца B (поскольку B — второй столбец в указанной таблице A2:B15).
4 range_lookup определяет, ищете ли вы точное совпадение (FALSE) или приблизительное совпадение (TRUE или опущено). Этот последний параметр является необязательным, но очень важным.
Функция ВПР в Excel примеры
Теперь давайте рассмотрим несколько примеров использования функции ВПР на реальных данных.
Функция ВПР на разных листах
На практике формулы ВПР редко используются для поиска данных на одном листе. В большинстве случаев вам придется находить и извлекать соответствующие данные с другого листа.
Чтобы использовать функцию ВПР с другого листа Excel, необходимо ввести имя рабочего листа и восклицательный знак в аргументе таблицы перед диапазоном ячеек, например =ВПР(40,Лист2!A2:B15;два). Формула указывает, что диапазон поиска A2:B15 находится на Листе2.
Конечно, вам не нужно вводить имя листа вручную. Просто начните вводить формулу, а когда дело доходит до аргумента таблицы, переключитесь на лист поиска и выберите диапазон с помощью мыши.
Формула, которую вы видите на изображении ниже, ищет текст в ячейке A2 («Товар 3») в столбце A (первый столбец диапазона поиска A2:B9) на листе «Цены »:
Функция ВПР в Excel – Функция ВПР на разных листах
Обратите внимание, что рекомендуется использовать абсолютные ссылки на ячейки (со знаком $) в аргументе таблицы. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки.
Как использовать именованный диапазон или таблицу в формулах ВПР
Если вы собираетесь использовать один и тот же диапазон поиска в нескольких формулах ВПР, вы можете создать для него именованный диапазон и ввести имя непосредственно в аргументе таблицы формулы ВПР.
Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле «Имя» слева от «панели формул ».
Функция ВПР в Excel – Присвоение имени диапазону
Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:
Функция ВПР в Excel – Пример функции ВПР с именем диапазона
Большинство имен диапазонов в Excel применяются ко всей книге, поэтому вам не нужно указывать имя листа, даже если ваш диапазон поиска находится на другом листе. Такие формулы гораздо более понятны. Кроме того, использование именованных диапазонов может быть хорошей альтернативой абсолютным ссылкам на ячейки. Поскольку именованный диапазон не изменяется при копировании формулы в другие ячейки, вы можете быть уверены, что ваш диапазон поиска всегда будет действительным.
Если вы преобразовали диапазон ячеек в полнофункциональную электронную таблицу Excel (вкладка «Вставка» -> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, и Microsoft Excel автоматически добавит имена столбцов или имя таблицы в формулу:
Функция ВПР в Excel – Пример функции ВПР с именем таблицы
Полная формула может выглядеть так:
или даже =ВПР(«Товар 1»;Таблица6;2).
Как и именованные диапазоны, имена столбцов являются постоянными, а ссылки на ячейки не изменяются независимо от того, куда копируется формула ВПР.
Функция ВПР с несколькими условиями
Рассмотрим пример функции ВПР с несколькими условиями. Имеем следующие исходные данные:
Функция ВПР в Excel – Таблица исходных данных
Допустим, нам нужно использовать функцию ВПР с различными условиями. Например, для поиска цены товара по двум критериям: название товара и его тип.
- Чтобы использовать функцию ВПР с несколькими условиями, необходимо вставить в начало дополнительный столбец, в котором будет храниться информация о названии и типе продукта.
Итак, на листе «Цены» вставьте столбец, а в ячейку А2 введите следующую формулу:
С помощью этой формулы мы объединяем значения столбцов «Продукт» и «Тип». Заполните все ячейки.
Теперь таблица поиска выглядит так:
Функция ВПР в Excel – Добавление вспомогательного столбца
- Теперь в ячейке C2 листа «Продажи» введите следующую формулу ВПР:
Заполняем оставшиеся ячейки и в результате получаем цены на каждый товар по типу:
Функция ВПР в Excel – Пример ВПР с несколькими условиями
Теперь давайте посмотрим на ошибки в функции ВПР.
Почему не работает функция ВПР
В этой части статьи мы рассмотрим, почему не работает функция ВПР и возможные ошибки в работе функции ВПР.
Функция ВПР в Excel и её секреты. Поиск VLOOKUP в базах данных
Функция ВПР в Excel позволяет работать с большими таблицами и базами данных. В статье мы рассмотрим примеры использования функции ВПР в Excel. Кроме того, с помощью функции Excel VLOOKUP мы будем анализировать динамический индекс Col index num, IF и выполнять поиск в нескольких столбцах или в базе данных INDIRECT INDIRECT.
ВПР ищет значение, указанное вами в электронной таблице, и возвращает ответ на ваш запрос из другого столбца. Формула Excel может использоваться для поиска любых данных на основе уникального идентификатора во время работы в программе.
Это экономит много времени и позволяет избежать потенциальных ошибок при сравнении больших таблиц и важных баз данных. В вашем распоряжении две функции: ВПР и ГПР. Единственная разница между этой парой выражений заключается в том, что первое ищет информацию по вертикали, а второе — по горизонтали. Функция ВПР в Excel имеет 4 аргумента:
- Критерии поиска (поисковое значение)
- Массив (Таблица_массив)
- индекс (Col_index_num)
- Порядок сортировки (Range_lookup).
Для изучения каждого из этих аргументов будет использовано наглядное пособие: расчет заработной платы сотрудников.
Структура формулы ВПР в Excel
Первая строка — это значение, которое вы хотите найти в базе данных Excel. В примере необходимая информация находится в ячейке B3. Если вы введете свой адрес в первое поле, вы можете использовать выражение, чтобы найти нужную информацию в штатном расписании.
Подробнее: Подпись Excel в формулах
Кроме того, указывается весь диапазон данных (то есть вся таблица), и нужно учитывать, что критерии отбора должны быть в первой колонке.
В третьей строке нужно указать только номер столбца в матрице, из которого через выражение будет извлекаться информация.
Существует два варианта значения параметра «Порядок сортировки». Введите 0; то результатом вставки будет ошибка #N/A, если он не может найти результаты запроса в базе данных Excel.
Результат отображается только в том случае, если найдено абсолютное совпадение между указанными критериями выбора (Lookup_value) и результатами в первом столбце массива (Table_array.
Введите 1, и формула также вернет результат, если найдет полное совпадение между Lookup_value и содержимым любой строки в первом столбце массива (Table_array).
Но если совпадения нет, будет выбрано значение, близкое к этому числу. Другими словами, если вы введете 12 вместо B3 в примере, функция вернет ответ «Гвенди», потому что 10 — ближайшее число к 12. Первый столбец таблицы должен быть предварительно отсортирован в порядке возрастания, если вы хотите чтобы вставить 1 в Range_lookup.
Поиск приблизительного значения ВПР в Excel
В примере (изображение №2) ВПР используется для расчета надбавки на основе текущей зарплаты. Критерий отбора — заработная плата, написанная в F3. Массив представляет собой диапазон $I$3:$J$10 (весь массив).
Адрес абсолютный (на это указывают знаки $), поэтому по-прежнему можно скопировать всю полученную структуру без изменения диапазона. Col_index_num равен 2 (процент повышения находится в соседнем массиве).
Параметр порядка сортировки Range_lookup равен 1, потому что мы хотим, чтобы формула выдавала результат, даже если нет идеального совпадения. Если Искомое_значение равно 35 850, ВПР не может найти идеальное совпадение в массиве, но ближайшее минимальное число равно 35 000, поэтому ответ на выражение равен 4%. На этом ликбез «ВПР в Excel для чайников» заканчивается.
Сравнение двух списков с помощью ВПР в Excel
Вы можете использовать функцию ВПР в Excel для сравнения двух баз данных. Критерием выбора в этом примере (скриншот №3) является идентификатор персонала (должно быть уникальным значением) в первом столбце.
Массив представляет собой диапазон идентификаторов персонала во втором списке или массиве. Сделайте ссылку на массив абсолютной, используя знаки $ в адресах ячеек ($I$3:$I$9), чтобы можно было скопировать выражение, не изменяя ссылки на ячейки диапазона.
Введите 0 в качестве аргумента Порядок сортировки; это необходимо, потому что вам нужно только точное пересечение. Скопируйте результат, и если в ответе отображается #N/A, это означает, что искомой записи нет ни во втором списке, ни в базе данных.
Динамический индекс (Col_index_num) с использованием нумерованных столбцов
Если вам нужно найти информацию в нескольких столбцах, вы можете использовать относительные ссылки на ячейки в поле «Индекс.
Это позволяет сэкономить массу времени и не вводить каждый раз номер столбца. На изображении №4 столбцы пронумерованы в строке 4. В поле Col_index_num нужно ввести C4, и вы получите первый сводной индекс.
Теперь очень просто и быстро добавлять или удалять столбцы из матрицы, а также легко менять их порядок в строке 3.
Динамический индекс со вложенными IF (ЕСЛИ)
Вы можете использовать формулы ЕСЛИ (IF) в Excel для создания динамического индекса. В примере (скриншот №5) премия рассчитывается на основании отдела и категории.
Если сотрудник, работающий в отделе продаж, находится в группе премирования 1, формула ВПР должна вернуть 3%, если он находится в группе премирования 5, то 4%, а если он находится в группе 10, то 5%.
Оператор IF с двойной связью в Col_index_num проверяет информацию в столбце G (категория бонусов), чтобы помочь ВПР выбрать значение из массива справа.
Критерий поиска: квартира, Е3. Массив представляет собой диапазон $J$3:$M$7. В поле «Индекс» указана конструкция типа ЕСЛИ(G3=»Группа 1″,2,ЕСЛИ(G3=»Группа 5″,3,4)).
С помощью первой функции ЕСЛИ вы можете узнать, соответствует ли G3 запросу «Группа 1». Если это так, формула ВПР выберет бонус из второго столбца. Если это не так, второй оператор IF проверяет, соответствует ли G3 запросу «Группа 5».
Если это так, функция ВПР выбирает бонус из третьего столбца. Если это также ложно, анализируется четвертый. Обязательно введите 0 в строке Range_lookup, потому что вы хотите найти только буквальное совпадение.
Поиск в нескольких столбцах или базе Excel с INDIRECT (ДВССЫЛ)
В примере (фото №6) 3 таблицы. Диапазоны данных в каждом из них называются «Финансы», «Производство» и «Продажи».
В этом примере нужно найти сотрудника отдела продаж с id 3. Название диапазона вводится в C19, id в E19. Ячейка B22 связана с C19, а B22(id) является значением поиска. Выражение с INDIRECT находится в таблице: INDIRECT обрабатывает содержимое C19 как имя диапазона.
Далее вы указываете номер столбца в массиве, из которого функция должна извлечь информацию и отобразить ее в ячейке с помощью ВПР. Введите 0 в Range_lookup, потому что здесь требуется только точное пересечение.
ВПР — один из самых полезных и важных инструментов поиска в Microsoft Excel. Он обычно используется для обозначения огромных рабочих листов с тоннами информации, когда ручная работа занимает слишком много времени. «V» в VLOOKUP означает «вертикальный», поэтому его также иногда называют формулой вертикального выбора.
Особенности функции вертикального поиска в Excel
- Запросы не чувствительны к регистру.
- В строке «Порядок сортировки» значение по умолчанию — 1 (ИСТИНА). Поэтому лучше не опускать этот аргумент, если вам нужны только точные совпадения.
- Подстановочные знаки допускаются в первом поле (где пишется запрос.
Выше были рассмотрены лишь некоторые из способов использования, казалось бы, обычной функции поиска ВПР. Все эти примеры ВПР в Excel демонстрируют, насколько она полезна при работе с базами данных и упрощает анализ больших электронных таблиц.