Использование функции ВПР в программе Excel

Во время работы в Эксель нередко требуется перенести или скопировать определенную информацию из одной таблицы в другую. Выполнить подобную процедуру, конечно же, можно вручную, когда речь идет о небольших объемах данных. Но что делать, если нужно обработать большие массивы данных? В программе Microsoft Excel на этот случай предусмотрена специальная функция ВПР, которая автоматически все сделает в считанные секунды. Давайте посмотрим, как это работает.

Использование дополнительной колонки

В большинстве случаев сложные проблемы становятся проще и более управляемыми, если их разбить на маленькие кусочки. Тоже самое касается при построении формул в Excel.

Рассмотрим классический пример. У нас имеется таблица с продажами по месяцам и городам. И нам необходимо определить значение продаж, соответствующее двум условиям: месяц – Февраль и город – Самара.

Использование функции ВПР в классическом виде нам не поможет, так как она сможет вернуть значение, соответствующее только одному условию. Из положения нам поможет выйти дополнительный столбец, в котором мы объединим значения столбцов Месяц и Город. Для этого в ячейке А2 прописываем формулу =B2&C2 и протягиваем данную формулу до ячейки А13. Теперь мы сможем использовать значения столбца А, чтобы вернуть необходимое значение. Прописываем в ячейке G3 формулу:

Данная формула объединяет два условия ячеек G1 и G2 в одну строку и просматривает его в столбце А. После того, как нужное условие было обнаружено, формула возвращает значение с четвертого столбца таблицы A1:D13, т.е. столбца Продажи.

Ближайшее ЧИСЛО (ДАТА)

В предыдущей задаче предполагалось, что наименование Товара присутствует в Заказе в единственном экземпляре и точно соответствует критерию поиска. Однако, существует класс задач, когда это не так. Особенно часто встречаются задачи поиска ближайшего ЧИСЛА (если точное значение в ключевом столбце не найдено, то выводится ближайшее).

Примечание . Формулы, созданные для поиска ближайших ЧИСЕЛ, работают и для ДАТ, т.к. даты в MS EXCEL хранятся в числовом формате .

Сначала вкратце напомним работу ВПР() — VLOOKUP() с аргументом интервальный_просмотр = ИСТИНА (когда ищется значение в ключевом столбце точно или приблизительно совпадающее с критерием).

Задача: Для указанной пользователем Даты найти соответствующую ей цену (см. файл примера ).

Напомним, что для ВПР() с аргументом интервальный_просмотр = ИСТИНА требуется сортированный по возрастанию ключевой столбец (Дата). При отсутствии в столбце точного совпадения выводится наибольшее значение, которое меньше искомого. При наличии нескольких одинаковых подходящих значений в ключевом столбце, учитывается, то что ниже. Поэтому Цена выведена 220р., а не 240 и не 230.

Теперь необходимо для заданной пользователем Даты найти соответствующую ей цену, но эта Цена и дата должны относится к заданному пользователем Продавцу.

Другими словами, найдем цену на дату, ближайшей к заданной, но только для определенно продавца.

В ячейках А25 и В25 введем Продавца и дату.

Прежде чем писать формулу, определимся, что значит «ближайший». На самом деле можно дать несколько определений, нужный вариант выбирается исходя из условий решаемой задачи.

Вот несколько возможных определений:

1. В терминах функции ВПР() «ближайший» — это наибольшее значение, которое меньше искомого. При наличии нескольких одинаковых подходящих значений в ключевом столбце, учитывается, то что расположено ниже в таблице. Понятно, что такое значение может быть не обязательно ближайшим.

2. Другой вариант: наименьшее значение, которое больше искомого. При наличии нескольких одинаковых значений в ключевом столбце, учитывается, то что выше.

3. Ближайшее, т.е. та дата, которая ближе, чем все остальные (м.б. раньше или позже заданной). Если таких дат несколько, то учитывается нижняя и выводится соответствующая ей цена.

Еще несколько вариантов предложено в файле примера .

Понятно, что для каждого определения ближайшего требуется своя формулы массива (все их можно найти в файле примера ).

Формулы для определения цены, как правило, используют результат формулы определяющей ближайшую дату.

Функция ВПР в Экселе – сравнение двух таблиц

Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).

Для удобства восприятия я разместил их на одном листе:

Функция ВПР в Экселе-11

Ваша задача сверить количество позиций и их цены.

Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.

Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.

Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:

Функция ВПР в Экселе-12

После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:

Функция ВПР в Экселе-13

Функция ВПР в Экселе-14

Обратите внимание, я закрепил диапазоны ячеек.

Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):

Функция ВПР в Экселе-15

В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).

В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).

Таким образом мы сможем увидеть разницу и в количестве и в цене.

Если значение «0», то значит всё хорошо и данные одинаковые.

Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.

Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.

Если значение #Н/Д — это значит, что в счёте вообще нет такой позиции.

Тоже самое и с ценами, если значения в плюсе, значит нам сделали скидку, а если значения в минусе — значит нам пытаются продать по завышенной цене.

Таким образом нужно просканировать всю таблицу и выяснить о причинах расхождения у поставщика:

Функция ВПР в Экселе-16

Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.

Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.

Вдруг хитрый поставщик, среди сотни позиций решил нам скрытно что-то «допродать».

Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.

И если в столбце «Количество в заказе» мы вдруг увидим значения #Н/Д это значит, что позиции с таким наименование не было в нашем заказе поставщику.

Функция ВПР в Экселе-18

Это не всегда означает, что поставщик хочет нас «нагреть», чаще всего просто может быть ошибка в названии товара. Стоит например поставить лишний пробел, как Эксель не задумываясь выдаст #Н/Д. Для него это разные названия.

Теперь всё тоже самое продемонстрирую в небольшом видео.

Использование функции ВПР в программе Excel

Во время работы в Эксель нередко требуется перенести или скопировать определенную информацию из одной таблицы в другую. Выполнить подобную процедуру, конечно же, можно вручную, когда речь идет о небольших объемах данных. Но что делать, если нужно обработать большие массивы данных? В программе Microsoft Excel на этот случай предусмотрена специальная функция ВПР, которая автоматически все сделает в считанные секунды. Давайте посмотрим, как это работает.

Описание функции ВПР

ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.

С помощью данного инструмента Excel можно отыскать совпадение значения в заданном столбце и “подтянуть” необходимые данные в выбранную ячейку из другой таблицы. Чтобы было понятнее, давайте разберем, как работает функция на практическом примере.

Применение функции ВПР на практике

Допустим, у нас есть таблица с продажами товаров. В ней отсутствуют данные по цене каждой позиции, однако, они есть в другой таблице рядом.

Наша задача – заполнить недостающие в основной таблице цены, чтобы мы могли посчитать итоговые суммы по продажам.

Порядок действий в данном случае следующий:

  1. Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
  2. В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
  3. Теперь предстоит правильно заполнить аргументы функции:
    • в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
    • переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
      Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
    • Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
    • в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
    • в значении аргумента “Интервальный_просмотр” можно указать два значения:
      • ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
      • ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
      • мы выбираем первый вариант, так как нам важна предельная точность.
    • Когда все готово, нажимаем OK.
  4. В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
  5. Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
  6. В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.
Читайте также:  Пример как пользоваться функцией МУМНОЖ в Excel

Заключение

Таким образом, функция ВПР – один из самых полезных инструментов при работе в Excel, когда нужно сопоставить данные двух таблиц и перенести значения из одной в другую при совпадении заданных значений. Правильное использование данной функции позволит сэкономить немало времени и сил, позволив автоматизировать процесс заполнения данных и исключив возможные ошибки из-за опечаток и т.д.

Функция ВПР в Excel, примеры

Более сложный пример использования функции ВПР можно посмотреть во втором прикреплённом файле после статьи, он называется «Использование функции ВПР на примере.zip». В архиве файл формата *.xlsx, в котором рассмотрен пример совмещения двух таблиц, находящихся на разных листах Эксель.

Пример представляет из себя практическую задачу объединения двух выгрузок из программы 1С:Бухгалтерия — в одной из них находится список товаров, а в другой цены. В результате к списку товаров добавляется цена, причём это может быть как цена покупки, так и цена продажи.

Также в примере задействованы и некоторые другие формулы, например функция ЕСЛИ.

Пример функции ВПР Вы можете придумать и сами: если интересуетесь применением данной формулы, то у Вас в любом случае должна быть причина, то есть исходные данные, требующие обработки. Если решите задачу самостоятельно, напишите в комментариях как именно Вы это сделали — это поможет другим пользователям лучше понять работу в Excel!

Если же всё ещё остались вопросы по применению ВПР на практике, посмотрите учебное видео к данной статье.

Excel VLOOKUP функция

Excel Функция ВПР ищет значение путем сопоставления в первом столбце таблицы и возвращает соответствующее значение из определенного столбца в той же строке.

В этом руководстве рассказывается о том, как использовать функцию ВПР, с объяснением синтаксис, Аргументы, возвращаемое значение, функциональные примечания и простые примеры в деталях.
Кроме того, в конце урока мы перечисляем множество практических примеров часто используемых формул ВПР, в том числе Vlookup на разных листах или книгах, Vlookup и сохраните форматирование ячейки результата, vlookup возвращает несколько результатов и другие формулы vlookup в общих случаях.

Синтаксис

=VLOOKUP ( lookup_value , table_array , col_index , [ range_lookup ])

аргументы

Lookup_value (обязательный): Значение, которое вы ищете. Он должен быть в первом столбце диапазона table_array.

Table_array (обязательный): Таблица содержит два или более столбца, в которых находятся столбец значения поиска и столбец значения результата.

Col_index (обязательный): Конкретный номер столбца (это целое число) table_array, из которого вы вернете совпадающее значение.

  • Номер первого столбца равен 1 для самого левого столбца table_array.

Range_lookup (необязательный): Это логическое значение, которое определяет, будет ли эта функция ВПР возвращать точное или приблизительное совпадение:

  • Если же линия индикатора range_lookup либо ИСТИНА или опущен, функция ВПР вернет точное или приблизительное совпадение. Если точное совпадение не может быть найдено, оно будет соответствовать следующему наименьшему значению.
    =VLOOKUP(lookup_value, table_array, col_index, TRUE)
    =VLOOKUP(lookup_value, table_array, col_index)

Важнo : В этом случае значения в первом столбце таблица_массив необходимо отсортировать по возрастанию;
В противном случае функция VLOOKUP может не вернуть правильное значение.

  • Если же линия индикатора range_lookup либо НЕПРАВДА или , функция ВПР вернет только точное совпадение. Если точное совпадение не может быть найдено, будет возвращено значение ошибки # Н / Д. Но если в первом столбце таблицы есть два или более значений таблица_массив что соответствует искомое_значение, vlookup будет использовать только значение первого найденного.
    =VLOOKUP(lookup_value, table_array, col_index, FALSE)
    =VLOOKUP(lookup_value, table_array, col_index, )

В этом случае значения в первом столбце таблицы таблица_массив не нужно сортировать.

Возвращаемое значение

Примечания к функциям

1. Функция ПРОСМОТР ищет значение только слева направо.
Значение поиска находится в крайнем левом столбце, а значение результата должно быть в любом столбце справа от значения поиска.
2. Есть два режима согласования для функции ВПР. Они точно совпадают и примерно совпадают.
Функция ВПР по умолчанию будет использовать приблизительное совпадение.
3. Он вернет значение ошибки # Н / Д, если искомое значение не может быть найдено.

Примеры

В этом разделе приведены примеры, показывающие, как использовать функцию VOOKUP в Excel.

Пример 1: точное совпадение VS приблизительное совпадение с функцией ВПР

1.1 Vlookup точное совпадение:

Как показано на приведенном ниже снимке экрана, как найти значение точного совпадения «Peach» и вернуть его соответствующее значение из третьего столбца в диапазоне таблицы B3: E8? Для этого вы можете сделать следующее.

1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ для получения результата.
=VLOOKUP(G4,B3:E8,3,FALSE)

1.2 Примерное совпадение Vlookup:

Как показано на скриншоте ниже, вы хотите найти номер заказа «10006», если номер не может быть найден, выполните приблизительное совпадение.

1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ для получения результата.
=VLOOKUP(H4,B3:F8,3,TRUE)

Заметки:

  • В двух приведенных выше формулах G4 и H4 ссылки на ячейки, содержащие искомые значения;
  • B3: E8 и B3: F8 — это диапазоны таблицы, в которых находятся столбец значения поиска и столбец значения результата;
  • 3 номер столбца для возврата совпадающего значения;
  • НЕПРАВДА указывает на нахождение значения точного совпадения в этом случае;
  • ИСТИНА преобразует приблизительное совпадение. Поскольку искомое значение 10006 не было найдено в первом столбце, оно будет соответствовать следующему наименьшему значению 10005 и вернет результат 9.00 долларов США из третьего столбца таблицы.
Пример 2: Vlookup возвращает пустое поле или конкретное значение вместо # N / A

При выполнении точного совпадения с функцией ВПР будет возвращено значение ошибки # Н / Д, если искомое значение не может быть найдено. Смотрите скриншот:

Как оставить ячейку результата пустой или вернуть конкретное значение вместо # N / A, если искомое значение не может быть найдено? Вот решение ниже.

1. Скопируйте приведенную ниже формулу в ячейку, в которой будет выведен результат, и нажмите Enter ключ:
=IFERROR(VLOOKUP(H4,B3:F8,3,0),»Not found»)

Внимание: В формуле «не найдено»- это содержимое, которое вы будете отображать в ячейке результата вместо ошибки # N / A. Чтобы ячейка результата оставалась пустой, замените текст «не найдено» пробелом. И вы можете изменить его в зависимости от ваших потребностей.

Больше примеров

Формулы Vlookup — в книгах / листах

Значения Vlookup на нескольких листах
Вы можете применить функцию vlookup, чтобы вернуть совпадающие значения в таблице рабочего листа. Однако, если вам нужно использовать значение vlookup на нескольких листах, как вы можете это сделать? Эта статья содержит подробные инструкции, которые помогут вам легко решить проблему.
Нажмите, чтобы узнать больше .

Vlookup для сравнения двух списков на отдельных листах
Предположим, что есть два списка имен, расположенных на разных листах «Имя-1» и «Имя-2», для сравнения этих двух списков и поиска совпадающих имен в Имена-1, если они выходят в Имена-2. Формула ВПР в этой статье окажет вам услугу.
Нажмите, чтобы узнать больше .

Vlookup к другой книге
Обычно функцию ВПР можно использовать только в одной книге. Если вы хотите применить функцию ВПР в другой книге, попробуйте формулу из этой статьи.
Нажмите, чтобы узнать больше .

Формулы Vlookup — Сохранение форматирования результатов

Сохранять форматирование ячеек при Vlookup
Обычно, применяя формулу ВПР, вы получаете результат без какого-либо форматирования. Как сохранить исходное форматирование ячейки результата? Вот решение.
Нажмите, чтобы узнать больше .

Vlookup и вернуть цвет фона вместе со значением поиска
Если вы хотите вернуть результат вместе с исходным цветом фона при применении формулы VLOKUP, может помочь метод, описанный в этой статье.
Нажмите, чтобы узнать больше .

Формат даты Vlookup и возврата вместо числа
Вы можете заметить, что дата отображается как серийный номер вместо формата даты при использовании функции ВПР, а затем вам необходимо вручную отформатировать эту ячейку результата для форматирования даты. Есть ли какой-нибудь удобный способ справиться с этим, кроме ручного изменения форматирования ячеек на формат даты? Эта статья покажет вам простой способ решить эту проблему.
Нажмите, чтобы узнать больше .

Vlookup и возврат соответствующего значения с комментарием ячейки
Когда вы применяете функцию Vlookup для возврата совпадающего значения, она вернет только значение. Иногда вам может потребоваться vlookup и вернуть совпадающее значение, включая его исходный комментарий. Как ты мог это снять?
Нажмите, чтобы узнать больше .

Читайте также:  Как убрать сетку в Excel 2010 полностью и частично

Формулы Vlookup — возврат нескольких результатов

Vlookup и возврат совпадающих значений в нескольких столбцах
Обычно применение функции Vlookup может вернуть совпадающее значение только из одного столбца. Иногда вам может потребоваться извлечь совпадающие значения из нескольких столбцов на основе критериев. Вот решение для вас.
Нажмите, чтобы узнать больше .

Vlookup возвращает несколько значений в одной ячейке
Обычно при применении функции ВПР, если есть несколько значений, соответствующих критериям, вы можете получить результат только для первого из них. Если вы хотите вернуть все совпавшие результаты и отобразить их все в одной ячейке, как этого добиться?
Нажмите, чтобы узнать больше .

Vlookup и возврат нескольких значений без дубликатов
Иногда вам может потребоваться vlookup и сразу вернуть несколько совпадающих значений в одну ячейку. Но как сохранить уникальные значения только при появлении повторяющихся результатов? Вот решение.
Нажмите, чтобы узнать больше .

Vlookup и возврат всей строки совпадающего значения
Обычно использование функции vlookup может возвращать результат только из определенного столбца в той же строке. Эта статья покажет вам, как вернуть всю строку данных на основе определенных критериев.
Нажмите, чтобы узнать больше .

Формулы Vlookup — другие распространенные случаи

С легкостью используйте vlookup и суммируйте в Excel
Сочетание функций vlookup и sum помогает найти определенный критерий и одновременно суммировать все значения на основе этого критерия. Здесь мы предоставляем подробные инструкции, которые помогут вам легко найти определенное значение и суммировать все совпадения в столбцах или строках в Excel.
Нажмите, чтобы узнать больше .

Обратный просмотр Vlookup или в обратном порядке
Как правило, функция ВПР ищет значения слева направо в таблице массивов и требует, чтобы значение поиска оставалось в левой части целевого значения. Но иногда вы можете знать целевое значение и захотеть узнать значение поиска в обратном порядке. Следовательно, вам необходимо выполнить поиск в обратном порядке в Excel. В этой статье есть несколько способов легко справиться с этой проблемой!
Нажмите, чтобы узнать больше .

Vlookup определенное значение и возвращает только истину или ложь / да или нет
Обычно при использовании формулы Vlookup для поиска точно совпадающего значения возвращается значение ошибки # N / A, если значение не может быть найдено, или правильный результат при сопоставлении значения. Однако в некоторых случаях нам нужно только вернуть Yes или No / True или False, чтобы просто напомнить себе, существует ли значение. Решение из этой статьи может помочь.
Нажмите, чтобы узнать больше .

Vlookup в двухмерной таблице
Иногда вам нужно vlookup вернуть относительное значение, основанное как на строке, так и на столбце, то есть вам нужно вернуть данные из двухмерной таблицы. В этом случае сочетание функций ВПР и ПОИСКПОЗ может помочь решить проблему.
Нажмите, чтобы узнать больше .

Лучшие инструменты для работы в офисе

Kutools for Excel — поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

Функция ВПР (Вертикальный ПРосмотр) ищет по таблице с данными и на основе критериев запроса поиска, возвращает соответствующее значение с определенного столбца. Очень часто необходимо в запросе поиска использовать сразу несколько условий. Но по умолчанию данная функция не может обработать более одного условия. Поэтому следует использовать весьма простую формулу, которая позволит расширить возможности функции ВПР по нескольким столбцам одновременно.

Для наглядности разберем формулу ВПР с примером нескольких условий. Для примера будем использовать схематический отчет по выручке торговых представителей за квартал:

В данном отчете необходимо найти показатель выручки для определенного торгового представителя в определенную дату. Учитывая условия поиска наш запрос должен содержать 2 условия:

  1. – Дата сдачи выручки в кассу.
  2. – Фамилия торгового представителя.

Для решения данной задачи будем использовать функцию ВПР по нескольким условиям и составим следующую формулу:

  1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
  2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
  3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
  4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

Результат поиска в таблице по двум условиям:

Найдена сумма выручки конкретного торгового представителя на конкретную дату.

Разбор принципа действия формулы для функции ВПР с несколькими условиями:

Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

  1. элемент – Дата.
  2. элемент – Фамилия.
  3. элемент – Выручка.

А каждый ложный элемент в памяти заменяется на 3-х элементный набор пустых текстовых значений («»). В результате создается в памяти программы новая таблица, с которой уже будет работать функция ВПР. Она игнорирует все пустые наборы данных элементов. А непустые элементы сопоставляются со значением ячейки C1, использованного в качестве первого критерия поискового запроса (Дата). Одним словом, таблица в памяти проверена функцией ВПР с одним условием поиска. При положительном результате сопоставления функция возвращает значение элемента из третьего столбца (выручка) условной таблицы. Это происходит потому, что в третьем аргументе указывается номер столбца 3 из которого берутся значения. Стоит отметить что для просмотра в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.

А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.

Число 0 в последнем аргументе функции указывает на то, то совпадение должно быть абсолютно точным.

Exceltip

Все мы знаем насколько полезной является функция ВПР. Наверное, половина всех действий в Excel выполняется с помощью нее. Однако данная функция обладает рядом ограничений. Например, ВПР просматривает только крайний левый столбец в таблице или ищет только одно условие. Но что, если нам необходимо вернуть значение, соответствующее двум условиям. В таком случае нам придется прибегнуть к некоторым хитростям. Об этих хитростях мы и поговорим в сегодняшней статье.

Итак, мы с вами рассмотрим четыре варианта создания подстановочной функции с двумя условиями:

  1. Использование дополнительной колонки
  2. Использование функции ВЫБОР для создания новой таблицы просмотра
  3. Использование функций ИНДЕКС и ПОИСКПОЗ
  4. Использование функции СУММПРОИЗВ

Ну а начнем мы с вами с самого простого.

Впр по двум условиям в excel

Почему-то не работает, выдает #Знач!
в чем может быть дело?

очень нужен отбор по двум значениям! Помогите!
Спасибо)

Формулы массива вводится нажатием Ctrl+Shift+Enter.
Об этом есть в статье.

Амперсанд и функция СЦЕПИТЬ делают абсолютно одно и тоже.

Здравствуйте! У меня по данной формуле возникла следующая ситуация:
1) в строке 32 у меня занесены следующие данные столбец B Profi Mass bag столбец С 900 g;
2) в строке 33 столбец B Profi Mass столбец C Profi Mass 2800 g.

Из анализируемого диапазона формула берет цену за Profi Mass bag 900 g и ставит ее напротив Profi Mass 2800 g?

Предложу свой вариант поиска по 2м критериям, работает безотказно:

разложу формулу для наглядности:

И(
(A6=ИНДЕКС(Данные!A1:A3500;ПОИСКПОЗ(A6;Данные!A1:A3500;0);1));
(B6=ИНДЕКС(Данные!B1:B3500;ПОИСКПОЗ(B6;Данные!B1:B3500;0);1)));

ИНДЕКС(Данные!A1:F3500;ПОИСКПОЗ(A6;Данные!A1:A3500;0);4);
“Ошибка. Проверьте формулу”)

А10 – ссылка на дату конкретную по которой ищем
А$1 – выпадающий список с выбором наименования кассы.

как мне это решать?, помогите плиз.

Мужики, спасибо за формулу, жить стало легче! =)

Только один вопрос, а как сделать так, чтоб выводилось максимальное значение. Т.к. у меня, по одному и тому же критерию поиска, несколько сумм, а нужна максимальная.

Функция ВПР с несколькими условиями

Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

Функция ВПР в Excel – Таблица исходных данных

Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.

  1. Для того чтобы использовать функцию ВПР с несколькими условиями необходимо вставить в начало дополнительный столбец, который будет хранить информацию с названием и типом товара.

Итак на листе « Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:

При помощи этой формулы мы сцепляем значение столбца « Продукт » и « Тип ». Заполняем все ячейки.

Теперь таблица для поиска выглядит следующим образом:

Функция ВПР в Excel – Добавление вспомогательного столбца
  1. Теперь в ячейке С2 на листе « Продажи » напишем следующую формулу ВПР:

Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

Функция ВПР в Excel – Пример ВПР с несколькими условиями

Теперь разберем ошибки функции ВПР.

5. Как найти частичное совпадение в Excel с VLOOKUP

Иногда мы сталкиваемся с ситуациями, когда нужно найти информацию из таблицы на основе частичного совпадения. Не всегда в данном случае изменение параметра (интервальный просмотр) выдает правильный результат.

Читайте также:  Изменение цвета таблицы в Excel

В таком случае можно сделать это с помощью подстановочных знаков в VLOOKUP.

Важное примечание: при частичном сопоставлении (4-тый аргумент — интервальный просмотр) всегда должен быть равен 0, чтобы совпадение с подстановочными знаками работало должным образом.

Мы хотим узнать подробности о сотруднике, чье имя начинается с «Ил».

Для решения этой задачи мы воспользуемся классической функцией ВПР, но преобразуем первый аргумент – искомое значение. В этом случае мы предоставляем значение поиска как part_name&”*”. Part_name — это тот кусочек, по которому нужно найти соответствие, а «*» — подстановочный знак. Машина переведет выражение part_name&”*” как «начинается с part_name». В нашем случае part_name = Ил.

В SQL такую задачу можно решить с помощью оператора LIKE “Ил%”

Вот, какая формула у нас получилась:

Для того, чтобы достать всю информацию о сотруднике, нам пришлось написать три формулы, в которых мы изменили третий параметр — номер столбца.

Как видите, мы получили все нужные нам значения, соответствующие нашему частичному поисковому значению, без указания полного имени.

Вы также можете найти значение, которое заканчивается определенным символом или имеет их в середине.

Если присоединить подстановочный знак (“*”), то функция будет искать ячейку со значением, заканчивающимся на part_name.

Если Вам нужно совпадение «содержит part_name», то нужно использовать два подстановочных знака, например, ”*” & part_name & ”*”.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Многие из вас встречались с этой полезной функцией MS Excel ВПР (VLOOKUP). Это, безусловно, очень полезный инструмент агрегации и трансформации данных. Но, к сожалению, данная функция имеет ряд ограничений. Ниже мы рассмотрим несколько трюков, которые позволят нам преодолеть эти ограничения.

Первое ограничение функции ВПР — это обязательный параметр «номер_столбца«col_index_num«).

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Он определяет какие данные функция ВПР вернет в результате. Использование в качестве номера столбца в функции числа может негативно сказаться на удобстве последующего использования и изменения формул и расчетов. Например, добавление одной колонки в искомом массиве «сломает» формулу, т.к. параметр «номер_столбца» в ней остается неизменным.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Как видно из примера выше, вместо данных из столбца Data функция вернула данные из столбца Class, т.к. он теперь стал вторым относительно столбца Name.

Также нужно иметь в виду, что если через ВПР агрегируется несколько элементов данных, то в случае внесения корректировок в массив данных потребуется вносить ручные правки во все параметры «номер_столбца».

Чтобы избежать этих трудозатратных манипуляций, можно использовать функцию ПОИСКПОЗ (MATCH). Она позволяет определить позицию искомого поля в массиве поиска.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

где бы оно не находилось.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Дополнительным бонусом от использования функции ПОИСКПОЗ в данном случае будет еще и легко изменяемое искомое поле данных. Нужно просто поменять первый параметр «искомое_значение» («lookup_value«) функции ПОИСКПОЗ (в приведенном примере это ячейка В1).

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Второе ограничение функции ВПР — обязательное требование о расположении в массиве поиска столбца с искомыми значениями строго слева от столбцов с данными. Если столбец с искомыми значениями (столбец Name в примере) не первый слева направо, то функция ВПР не вернет никаких данных из столбцов с данными (столбцы Class Data в примере).

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Чтобы обойти это ограничение, надо воспользоваться функцией СМЕЩ (OFFSET) вместо функции ВПР. Данная функция состоит из трех обязательных параметров: ссылка (reference), смещение по строкам (rows), смещение по столбцам (cols). СМЕЩ возвращает значение ячейки, расположенной в X строках (смещение по строкам) и в Y столбцах (смещение по столбцам) от указанной ячейки (ссылка). Но чтобы получить максимальный эффект от этой функции, ее следует использовать вместе с двумя функциями ПОИСКПОЗ вместо параметров смещение по строкам и смещение по столбцам.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

ВАЖНО! Пожалуйста, не забывайте, что функция ПОИСКПОЗ возвращает порядковый номер ячейки с искомым значением в массиве, когда как в функции СМЕЩ смещение по строкам и смещение по столбцам — это количество шагов от начальной ячейки. Т.е. необходимо в обязательном порядке вычесть 1 из полученных функциями ПОИСКПОЗ результатов.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Итак, получаем формулу следующего вида:

СМЕЩ(начальная ячейка; ПОИСКПОЗ(искомое значение; массив поиска; тип сопоставления) — 1; ПОИСКПОЗ(искомое значение; массив поиска; тип сопоставления) — 1)

В результате мы имеем довольно-таки гибкое решение, не зависящее от позиции столбцов в массиве данных. Также имея это решение, мы можем легко переключаться с одного столбца данных на другой.

Для того, чтобы сделать эту формулу супергибкой и чтобы легко менять не только данные с результатами (Class и Data в примере), но и искомые данные (Name в примере), нужно поместить внутрь первой функции ПОИСКПОЗ (вместо искомое значение) еще одну функцию СМЕЩ с функцией ПОИСКПОЗ внутри. Такая формула будет самостоятельно определять столбец с искомыми значениями на основании значения ячейки А1. Таким образом, модифицированная формула будет иметь следующий вид:

СМЕЩ(начальная ячейка; ПОИСКПОЗ(искомое значение; СМЕЩ(начальная ячейка; 0, ПОИСКПОЗ(искомое значение; массив поиска; тип сопоставления) — 1); массив поиска; тип сопоставления) — 1; ПОИСКПОЗ(искомое значение, массив поиска, тип сопоставления) — 1)

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Microsoft Excel, Функция, Длиннопост

Выглядит сложно, но при построении сложных гибких решений — вещь очень удобная.

Задача1. Справочник товаров

Пусть дана исходная таблица (см. файл примера лист Справочник ).

Задача состоит в том, чтобы, выбрав нужный Артикул товара, вывести его Наименование и Цену .

Примечание . Это «классическая» задача для использования ВПР() (см. статью Справочник ).

Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или = ВПР($E9;$A$13:$C$19;2;ИСТИНА) или = ВПР($E9;$A$13:$C$19;2) (т.е. значение параметра Интервальный_просмотр можно задать ЛОЖЬ или ИСТИНА или вообще опустить). Значение параметра номер_столбца нужно задать =2, т.к. номер столбца Наименование равен 2 (Ключевой столбец всегда номер 1).

Для вывода Цены используйте аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра номер_столбца нужно задать =3).

Ключевой столбец в нашем случае содержит числа и должен гарантировано содержать искомое значение (условие задачи). Если первый столбец не содержит искомый артикул , то функция возвращает значение ошибки #Н/Д. Это может произойти, например, при опечатке при вводе артикула. Чтобы не ошибиться с вводом искомого артикула можно использовать Выпадающий список (см. ячейку Е9 ).

Понятно, что в нашей задаче ключевой столбец не должен содержать повторов (в этом смысл артикула, однозначно определяющего товар). В противном случае будет выведено самое верхнее значение.

При решении таких задач ключевой столбец лучше предварительно отсортировать (это также поможет сделать Выпадающий список нагляднее). Кроме того, в случае несортированного списка, ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) работать не будет.

В файле примера лист Справочник также рассмотрены альтернативные формулы (получим тот же результат) с использованием функций ИНДЕКС() , ПОИСКПОЗ() и ПРОСМОТР() . Если ключевой столбец (столбец с артикулами) не является самым левым в таблице, то функция ВПР() не применима. В этом случае нужно использовать альтернативные формулы. Связка функций ИНДЕКС() , ПОИСКПОЗ() образуют так называемый «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)

В файле примера лист Справочник показано, что формулы применимы и для ключевых столбцов содержащих текстовые значения, т.к. артикул часто бывает текстовым значением. Также задача решена для несортированного ключевого столбца.

Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием . (см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке ).

Примечание . Никогда не используйте ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) если ключевой столбец не отсортирован по возрастанию, т.к. результат формулы непредсказуем (если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его).

Множественный ВПР() — выводим несколько значений по ключевому значению в MS EXCEL

Функция ВПР () ищет заданное значение в ключевом столбце и выводит значение из соседнего столбца. Ключевой столбец в этом случае не содержит повторов. А что если содержит? Тогда функция выведет только первое встретившееся значение. Напишем формулу, которая выводит все значения, соответствующие искомому. Назовем эту формулу множественный ВПР().

Пусть дана исходная таблица с номерами заказов и товарами. Номера заказов могут повторяться.

У заказа 2 три повтора, в строке 3, 4 и 6. Перечень заказов не сортирован.

Выведем все наименования товаров для заказа 2, а также количество этих товаров.

Так как в ключевом столбце (Заказ) теперь несколько одинаковых значений, то функция ВПР() не годится — она выведет только самое первое, т.е. товар Манго. Чтобы вывести все 3 значения у заказа 2 создадим служебный столбец рядом с исходной таблицей.

Поместим в него формулу =СЧЁТЕСЛИ($B$8:B8;B8)

Она подсчитает номера повторов для каждого заказа. Нужный заказ введем в желтую ячейку. Соответствующие позиции этого заказа подсвечиваются зеленым цветом в исходной таблицке с помощью условного форматирования.

В таблице ниже, там где будем выводить товар и его количество для выбранного заказа, с помощью формулы

определим позиции повтора в исходной таблице (оранжевый столбец в нижней таблице). Это обычная формула (не формула массива).

Наименование товара выведем с помощью простой формулы =ИНДЕКС(C$8:C$14;$B23), аналогично выведем Количество.

Изменив в желтой ячейке номер заказа на 1, нижняя табличка изменится.

Покажем как работает формула =СУММПРОИЗВ(($B$8:$B$14=$A$20)*($E$8:$E$14=A23)*(СТРОКА($B$8:$B$14)-СТРОКА($B$7))) для заказа 1:

Ссылка на основную публикацию