Функция ВПР в excel для чайников, без воды и пространных рассуждений

Что такое функция ВПР в Эксель – область применения

При обработке нескольких таблиц, часто расположенных в разных книгах, возникает необходимость переноса данных из одной в другую, следя за тем, чтобы значения не теряли своего значения и процесс автоматизировался. Механизм в Excel должен работать быстро и легко.

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

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

Примеры для наглядности: в таблицах 1, 2 — исходные данные, в таблице 3 — что должно быть достигнуто.

Таблица исходных данных 1

ИМЯ И ФАМИЛИЯ.	Хороший
Петров	12 000,00 ₽
Сидоров	200.00 ₽
Иванов	38 000,00 ₽

Таблица исходных данных 2

ИМЯ И ФАМИЛИЯ.	ZP
Иванов	20 000
Петров	19 000 евро
Сидоров	21 000 евро

Объединенная таблица данных 3

ИМЯ И ФАМИЛИЯ.	ZP	Хороший
Иванов	20 000	38 000 евро
Петров	19 000 евро	12 000
Сидоров	21 000 евро	200

Если вы вдруг еще не узнали, рекомендуем прочитать, как объединить ячейки в Excel.

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень важно для тех, кто занимается закупками и отправляет заказы поставщику.

Обычно возникает следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счета-фактуры и сверяете заказ со счетом-фактурой.

все это указано в счете, в нужном количестве, по правильной цене и т.д.

Распространенные неполадки

Проблема

Возможная причина

Неверное возвращаемое значение	Если range_view имеет значение ИСТИНА или не указан, первый столбец должен быть отсортирован по алфавиту или числам. Если первый столбец не отсортирован, возвращаемое значение может быть неожиданным. Отсортируйте первый столбец или используйте FALSE для точного совпадения.
# Н / Д в ячейке	Если диапазон_просмотра имеет значение ИСТИНА, а значение_просмотра меньше наименьшего значения в первом столбце таблицы, возвращается значение ошибки # Н / Д. Если interval_lookup имеет значение FALSE, значение ошибки # N / A означает, что точное число не может быть найдено. Дополнительные сведения о том, как исправить ошибки N / A # в функции VLOOKUP, см. В разделе Исправление ошибок N / A # в функции VLOOKUP.
#СВЯЗЬ! в камере	Если значение column_number больше, чем количество столбцов в таблице, отображается значение ошибки #REF!. Для получения дополнительной информации о #REF! в функции ВПР см. Устранение ошибки # ССЫЛКА!.
#ЦЕНИТЬ! в камере	Если таблица меньше 1, отображается значение ошибки #VALUE!. Подробнее об устранении неполадок #VALUE! в разделе ВПР см. Правильное # ЗНАЧЕНИЕ! в функции ВПР.
#ИМЯ? в камере	#NAME значение ошибки? чаще всего появляется, когда в формуле отсутствуют кавычки. При поиске имени сотрудника обязательно заключите имя в формуле в кавычки. Например, в функции = ВПР («Иванов»; B2: E7; 2; FALSE) имя должно быть указано в формате «Иванов» и ничего больше. Дополнительные сведения см. В разделе «Как исправить ошибку #NAME?.
Ошибки # TRANSFER! в камере	Этот конкретный # РАЗЛИВ! обычно это означает, что формула использует неявное пересечение для значения поиска и использует весь столбец в качестве ссылки. Например, = VKP (A: A; A: C; 2; FALSE). Вы можете решить эту проблему, связав ссылку на оператор @: = VIEW (@A: A, A: C; 2; FALSE). В качестве альтернативы вы можете использовать традиционный метод FLPC и ссылаться на одну ячейку вместо всего столбца: = FIND IR (A2; A: C; 2; FALSE).

Специальные инструменты для ВПР в Excel.

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

Подстановка данных из одной таблицы Excel в другую

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

И в ячейке G3 введите функцию ВПР. Снова берем диапазон с соседнего листа.

В результате столбец из второй таблицы будет скопирован в первую.

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

Объединение данных из нескольких таблиц на один таблицу с помощью ВЛКП

Функцию VLOP можно использовать для объединения нескольких таблиц в одну, если в одной из таблиц есть поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться своей книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных на новый. В таблице данные функции можно использовать в более ранних версиях Excel (при условии, что функция данных поддерживается более ранней версией).

Здесь столбцы A — F и H содержат значения или формулы, которые используют только значения на этом сайте, в то время как остальные столбцы используют IN LOOKUP и значения столбцов A (идентификатор клиента) и B (доверенность) для получения данных из других таблиц.

1. Скопируйте таблицу с общими полями в новую и дайте ей имя.

2. Чтобы открыть диалоговое окно «Диспетчер отношений»> В> Диспетчере отношений, нажмите кнопку «Данные»> «Инструменты данных).

   

3. Для каждой из этих ссылок обратите внимание на следующее:

   • Поле, связывающее таблицы (в скобках в диалоговом окне). Это первое значение lookup_value для формулы FLPC.

   • Имя связанной таблицы поиска. Это первый table_array в формуле VLIO.

   • Поле (столбец) в таблице поиска, связанное с данными, которые нужно вставить в новый столбец. Эта информация не отображается в диалоговом окне «Управление ссылками». Чтобы узнать, какое поле нужно получить, вам необходимо выполнить поиск в связанной таблице подсписок. Обратите внимание на номер столбца (A = 1): это формула col_index_num.

4. Чтобы добавить поле в новую таблицу, введите формулу в первый пустой столбец CPTT, используя информацию, собранную на шаге 3.

   В нашем примере столбец G для извлечения данных о тарифах на выставление счетов из четвертого столбца (col_index_num = 4) из таблицы доверенности, мы используем lookup_value (table_array) столбца доверенности с формулой = VLP (@ Attorney; tbl_Avvocati; 4; ПОКА).

   Вы также можете использовать ссылку на ячейку и ссылку на диапазон в своей формуле. В нашем примере это будет = VHDL (A2; ‘Адвокаты’! A: D, 4; ЛОЖЬ).

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

Начало работы

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

1. Значение, которое вам нужно найти, т.е значение, которое вы ищете.

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

3. Номер столбца в диапазоне, который содержит возвращаемое значение. Например, если вы укажете B2: D11 в качестве диапазона, вы будете считать B первым столбцом, C — вторым и так далее.

4. При желании вы можете указать слово ИСТИНА, если приблизительное совпадение достаточно, или слово ЛОЖЬ, если требуется точное совпадение возвращаемого значения. Если ничего не указано, всегда предполагается, что значение по умолчанию — ИСТИНА, что является приблизительным совпадением.

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

= ВLOOKUP (значения поиска, диапазон, содержащий значения поиска, номер столбца в диапазоне, содержащем возвращаемое значение, приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)).

Вопросы от новичков

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

Как сравнить таблицы с помощью ВПР?

Сравнение двух или более таблиц выполняется почти так же, как добавление нового ряда данных:

• Откройте обе таблицы и при необходимости перенесите их на лист.

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

• Введите формулу ВПР в первую ячейку под заголовком нового столбца, как описано выше. В первой строке диалогового окна указывается название продукта первой таблицы, во второй — вставляется вся вторая таблица целиком, включая заголовки, в третьей — номер сравниваемой строки, а в четвертый — снова значение «0».

• Когда нужное значение появится в ячейке, просто «растяните» его во всех позициях: данные будут заменены автоматически.

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

Быстрое сравнение двух таблиц с помощью ВПР

Функция помогает сопоставить значения в огромных таблицах. Допустим, цена изменилась. Мы должны сравнить старые цены с новыми ценами.

1. В старом прайс-листе создаем столбец «Новая цена».
2. Выделите первую ячейку и выберите функцию ВПР. Зададим аргументы (см. Выше). Для нашего примера:. Это означает, что вам нужно взять название материала из диапазона A2: A15, посмотреть его в «Новая цена» в столбце A. Затем взять данные из второго столбца новой цены (новая цена) и заменить их в ячейке C2.

Представленные таким образом данные можно сравнивать. Найдите числовую и процентную разницу.

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

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

Предварительная сортировка

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

Чтобы отсортировать первый столбец:

• выделить названия первой колонки от Морозова до Петровой (очевидно визуальное отсутствие выделения фамилии Морозов);
• в контекстном меню первого выделенного столбца установите курсор на строку «Сортировка»;
• в раскрывающемся списке щелкните «Сортировать от А до Я».

В следующем окне Excel предлагает пользователю интеллектуальную сортировку. «Другими словами, будет отсортирован не только первый столбец слева, но и остальные три, относящиеся к нему. Очевидно, что если этого не сделать, список сотрудников будет абсурдным. Поэтому в этом окне, не меняя что-нибудь, вы должны нажать на запись «Заказать».

ВПР пример 1

1. После создания упорядоченного тренировочного файла щелкните любую свободную ячейку (например, F9), и результат будет возвращен внутри нее.

1. Щелкните fx в строке формул.

3. В выпадающем списке «Категория» выберите категорию «Полный алфавитный список» или «Связи и массивы» — они будут содержать нужную нам функцию ВПР в алфавитном порядке. Если эта функция вызывалась недавно, она будет в списке «10 недавно использованных».

4. Выделите функцию ВПР и нажмите ОК.

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

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

6. Как видите, указанное значение или ключ в окне аргументов называется «Разыскивается». Мне это имя не кажется хорошим: оно изначально присваивается, не ищется и используется как ключ для поиска. Допустим, нас интересует зарплата, которую получает сотрудник Ирина Соколова. Самый простой способ установить верхний параметр — щелкнуть ячейку A8, а затем щелкнуть строку параметра «Таблица».

7. В качестве параметра «Таблица» выбирается вся заполненная область Excel, за исключением заголовков столбцов. А в строке «Номер столбца» введите номер столбца, в котором находится список зарплат, например. 2. Как видите, правильное значение заработной платы для сотрудницы Ирины Соколовой появляется прямо в окне темы.

8. После нажатия кнопки ОК значение, возвращаемое функцией, появится в ячейке, зарезервированной для результата.

ВПР пример 2

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

После нажатия кнопки ОК он также появится в ячейке результатов.

Опытные пользователи Excel знают, что эта программа хранит даты (и время) в числовом формате, начиная с общепринятой даты 0 января 1900 года. Чтобы отобразить полученное значение в знакомой форме, просто задайте ячейке результата формат даты. Для этого сначала откройте его контекстное меню и щелкните пункт «Формат ячеек».

В появившемся одноименном окне:

• нажмите «Дата»;

• в списке справа при необходимости измените вариант отображения;
• нажмите ОК.

После этого дата, отображаемая в ячейке результата, примет «человеческий» вид.

Автозаполнение

В итоге растягиваем формулу до конца, в результате чего происходит автозаполнение.

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

• В нашем случае желаемое значение — A2. Это относительная ссылка на ячейку, поскольку она не содержит символов «$». По этой причине ссылка на желаемое значение изменяется по отношению к каждой строке, когда автозаполнение происходит в других ячейках: A2 → A3 →… → A11. Это полезно, когда вам нужно повторить многострочную формулу, потому что ее не нужно переписывать.

• Таблица зафиксирована с абсолютной ссылкой «$ G $ 2: $ H $ 11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Поэтому расчет будет каждый раз правильным и будет основываться на таблице.

Экспресс-сравнение двух диапазонов

Предположим, вы изменили цену, а затем вам нужно сравнить старую и новую цены. Сделать это вручную проблематично. Хорошо, что есть функция ВПР.

восемь

А вот простая инструкция, как это сделать:

1. Создайте столбец «Новая цена» в старом прайс-листе.
   девять
2. Щелкните первую ячейку и войдите в функцию ВПР, как описано выше (с помощью кнопки fx), так как это удобнее для новичка. По мере того, как вы становитесь более профессиональным, вы можете вводить формулу вручную. В нашем случае это будет выглядеть так. = ВПР ($ A $ 2: $ A $ 15; ‘новая цена’! $ A $ 2: $ B $ 15; 2; ЛОЖЬ). Проще говоря, нам нужно сравнить диапазон A2: A15 в двух ценах. Затем введите новую информацию в старую в столбце «Новая цена».
   10

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

Функция ВПР, пошаговая инструкция

Итак, изначально у нас есть две таблицы, которые вы можете увидеть на скриншоте ниже. Общий столбец для обеих таблиц — «Заголовок».

Цель: добавить данные из аналогичного столбца второй (нижней) таблицы в первую (верхнюю) таблицу в «Зарплата.

Если вы думали, что это можно сделать вручную, то это большая ошибка, поскольку в таблицах может быть много тысяч строк, и их порядок в обеих таблицах не обязательно должен быть одинаковым!

С помощью функции ВПР мы можем использовать Excel для объединения двух таблиц и очень быстро. Начнем с ячейки C4 в первой таблице и на ее примере покажем простую инструкцию по вставке функции ВПР.

Функция ВПР, инструкция

• Добавим в ячейку ВПР.
  Вот ячейка C4. Вы можете ввести формулу вручную (следуя синтаксису, описанному выше) или с помощью мастера.
• Укажите параметр «Желаемое значение»
  В нашем примере нам нужно указать Excel, что мы будем искать во второй таблице. Поскольку общим столбцом в обеих таблицах является Location, мы укажем адрес ячейки, содержащей местоположение, в качестве аргумента (в примере это B4).
• Указываем параметр «Таблица».
  Указываем диапазон ячеек, в котором искать позицию из первой таблицы. В нашем примере это будет «A $ 10: B $ 12». Еще раз обращаем ваше внимание на то, что заголовки таблиц не должны попадать в диапазон.
• Задайте параметр «Номер столбца».
  Поскольку мы хотим добавить значение зарплаты в первую таблицу, мы укажем номер столбца 2 («Должность» — это первый столбец таблицы, указанный в предыдущем аргументе, а «Зарплата» — второй столбец).
• Указываем параметр «Интервал предпросмотра».
  В этом случае нас интересует точное совпадение заголовка позиции в обеих таблицах, поэтому мы укажем представление диапазона 0.

Обратите внимание на символ «$» перед номерами строк в диапазоне (номер аргумента ВПР 2). Это необходимо для того, чтобы номера строк оставались неизменными при копировании формулы в другие ячейки.

Следовательно, для ячейки C4 мы получаем следующую формулу:
= ВПР (B4; A $ 10: B $ 12,2,0)

Как работает функция ВПР

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

Поскольку мы указали 2 в качестве параметра «Номер столбца», функция ВПР в результате вернет то, что указано в столбце «Зарплата».

Общий результат получен: «40 000»

Это значение будет результатом выполнения функции ВПР в данном конкретном случае. На скриншоте выше вы можете видеть, что значение «40 000» записано в ячейку C4 первой таблицы.

Вы можете скачать файл Excel (* .xlsx) с этим примером после статьи.

Заполним аналогичными формулами остальные ячейки

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

В этом случае просто нужно учесть, что диапазон ячеек, в котором производится поиск (аргумент 2), не должен изменяться. Вот почему номерам строк в диапазоне предшествуют символы «$». Для первого аргумента (желаемое значение), наоборот, в этом нет необходимости, поскольку для каждой строки первой таблицы желаемое значение будет в отдельной ячейке (в нашем примере: A4, A5, A6).

Функция ВПР в Excel – как пользоваться

Чтобы таблица 1 достигла своего окончательного вида, мы вводим заголовок столбца, например «Штраф». На самом деле это необязательно, вы можете написать любой текст или оставить поле пустым. Функция также будет работать, щелкнув мышью в поле, где должно появиться значение, найденное в другой таблице.

Теперь нам нужно вызвать функцию. Сделать это можно несколькими способами:

1. 
   Зарегистрируйте функцию вручную.
2. Нажмите «Вставить функцию», выберите «Связи и массивы», выберите ВПР.
3. Откройте вкладку «Формулы», выберите «Ссылки и массивы», затем выберите «ВПР.

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

Необходимо заполнить значения для функции ВПР

1. 
   Lookup_value — это то, что Excel будет искать в другой таблице. В нашем случае Иванов, Петров, Сидоров. Щелкаем по названию в списке, это ячейка А2. Удерживая нажатой клавишу Shift на клавиатуре, щелкните фамилию в столбце, ячейка A4. В результате мы получаем выбранный список фамилий, с которыми будет работать ВПР.
2. 
   Таблица — место, где будет производиться поиск. В нашем случае вторая таблица находится на листе 2. Переходим к ней. Здесь нужно выделить весь диапазон поиска. Щелкните верхнюю левую ячейку диапазона. Удерживая нажатой клавишу Shift на клавиатуре, щелкните левой кнопкой мыши нижнюю правую ячейку. Нажмите F4. Диапазон поиска установлен.
3. Column_number — это номер столбца таблицы, в которой выполняется поиск данных. В нашем случае — 2.
4. 
   Range_view — это логическое значение. В нашем случае необходимо написать «ЛОЖЬ».
5. Нажмите «ОК»
6. Теперь вам нужно скопировать функцию в каждую пустую ячейку. Вы можете, например, сделать это с помощью копирования — вставки, или вы можете щелкнуть ячейку с результатом функции и в правом нижнем углу щелкнуть жирный квадрат левой кнопкой мыши, не отпуская левую кнопку, потяните его вниз.

Мастер ВПР — простой способ писать сложные формулы

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

Вот что вам нужно сделать, чтобы получить формулу решения вашей проблемы:

1. Запустите мастер, нажав кнопку Vlookup Wizard на ленте Ablebits Data.

1. Выберите вашу таблицу (вашу таблицу и таблицу поиска).
2. Укажите следующие столбцы (во многих случаях они выбираются автоматически):
   • Ключевой столбец: расположен в основной таблице, он содержит значения для поиска.
   • Колонка поиска — в которой будем искать.
   • Столбец возврата: мы будем получать из него значения.
3. Нажмите кнопку Вставить).

Посмотрим все в действии.

Стандартный ВПР.

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

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

Необязательно ничего писать руками.

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

Как работает функция ВПР в Excel: несколько примеров для «чайников».

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

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

Использование точного и приблизительного поиска.

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

Обратите внимание, что четвертый параметр — 1.

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

Поиск цен на египетские бананы завершился на первой позиции, а сливы зарегистрированы на второй. И это слово по правилам алфавита уступает «Бананам Египта». Это означает, что вам не нужно искать дальше. Получил 145. И не важно, что это цена на абрикосы. Поиск цен на сливы продолжался до тех пор, пока D15 не нашел слово с наименьшей по алфавиту: яблоки. Остановились и взяли цену предыдущей линии.

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

Спросите: «К чему тогда такой неточный взгляд, если так много проблем?»

он отлично подходит для выбора значений из определенных диапазонов.

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

Если у нас есть количество 11 единиц, мы проходим по столбцу D, пока не найдем число больше 11. Это 20, и оно находится в четвертой строке. Давай остановимся здесь. Это означает, что наша скидка находится в 3-м ряду и составляет 3%.

При работе с диапазонами «от — до» этот прием вполне подходит.

И еще небольшой совет.

Применяйте именованный диапазон.

Чтобы упростить использование формул, вы можете создать именованный диапазон, а затем ссылаться на него. В нашем случае назовем его «EmployeeData» (помните, что здесь нельзя использовать пробелы).

В ячейке B2 мы введем требуемую фамилию, а в ячейки C2: F2 запишем формулы:

= ВПР ($ B $ 2; EmployeeData; 2; ЛОЖЬ)

= ВПР ($ B $ 2; EmployeeData; 3; ЛОЖЬ)

= ВПР ($ B $ 2; EmployeeData; 4; ЛОЖЬ)

= ВПР ($ B $ 2; EmployeeData; 5; ЛОЖЬ)

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

Какие здесь преимущества?

1. Разве вас не ослепляют буквы, цифры и знаки доллара в обычных адресах?

Формула именованного диапазона выглядит намного удобнее, нагляднее и понятнее. Вместо скучных безликих координат вы видите идентификаторы, которые вызывают у вас какие-то ассоциации. Согласитесь, «цена» или «цена» — это определенно информация о цене.

1. Если по какой-то причине вам нужно изменить координаты диапазона поиска, который вы использовали в большом количестве формул, вам нужно исправить каждую формулу или использовать функцию «Найти и заменить»? Согласитесь, это очень долго, кропотливо, возможны ошибки.

Используя именованный диапазон, просто нажмите

Меню — Формула — Менеджер имен.

Затем в списке диапазонов найдите нужный диапазон и настройте его. Изменения будут автоматически применены ко всем формулам.

1. При использовании обычных адресов мы всегда должны думать о том, какую адресацию применить: относительную или абсолютную. Эта проблема не возникает при использовании именованных диапазонов.

Использование символов подстановки и другие тонкости критерия поиска.

Как и в предыдущих примерах, при вводе фамилии выполняется точный поиск. Но есть некоторые вещи, о которых мы раньше не упоминали.

1. Регистр символов не влияет на результат. Можно вводить все заглавными буквами — ничего не изменится. См. Пример ниже.

1. Если в списке есть люди с одинаковой фамилией, будет найден только первый. Как мы уже говорили, как только что-то подходящее найдено, процесс останавливается.
2. вы можете использовать подстановочные знаки * и ?. Напомню, что вопросительный знак обозначает любой символ, а звездочка обозначает любое количество символов (включая ноль). Мы упомянули о них в начале.

это хорошая идея, если вам известна только часть значения аргумента.

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

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

Формула в ячейке F2 выглядит так:

= ВПР («*» & D2 & «*»; $ A $ 2: $ B $ 7; 2; 0)

Здесь мы используем оператор конкатенации строк &.

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

Использование нескольких условий.

Еще один простой пример для чайников: как использовать несколько условий при выборе желаемого значения?

Предположим, у нас есть список имен и фамилий. Нам нужно найти подходящего человека и посмотреть размер его дохода.

В F2 мы используем следующую формулу:

= ВПР (D2 & «» & E2; $ A $ 2: $ B $ 21; 2; 0)

Разберем пошагово, как работает ВПР в этом случае.

Сначала формируем условие. Для этого используйте оператор &, чтобы «вставить» имя и фамилию вместе и вставить между ними пробел.

Не забудьте заключить пробел в кавычки, иначе Excel не воспримет его как текст.

Затем в таблице доходов найдите ячейку с именем и фамилией, разделенными пробелом.

Так что все происходит по уже отработанной схеме.

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

Примечательно так — D2 & «*» & E2

Но при этом учтите, что совпадение имени и фамилии уже не будет совсем точным. Мы рассмотрели аналогичный пример чуть выше.

Отдельно рассмотрим более сложные и точные способы работы с множеством условий. См. Ссылки в конце.

«Умная» таблица.

И еще одна рекомендация — используйте умный стол.

Может быть очень удобно сначала преобразовать справочную таблицу (прайс-лист) в смарт с помощью команды Home — Format as table (в англоязычной версии Excel), затем указать имя созданной таблицы во втором аргументе. Кстати, оно будет ему присвоено автоматически.

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

Интервальный просмотр в функции ВПР

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

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

Легче понять на примере. По результатам выполнения плана продаж каждый торговый агент должен получить заслуженную премию (в процентах от заработной платы). Если план выполнен менее чем на 100%, премия не требуется, если план выполняется от 100% до 110% (без учета 10%) — премия 20%, от 110% до 120% (120%). % не входит) — 40%, 120% и более — 60% премии. Данные представлены в следующей форме.

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

= ВПР (B2; $ E $ 2: $ F $ 5; 2; 1)

и скопируйте его.

На следующем рисунке показана диаграмма того, как работает режим просмотра диапазона функции ВПР.

Джеки Чан выполнил план на 124%. Это означает, что ВПР в качестве критерия ищет ближайшее нижнее значение во второй таблице. Это 120%. Таким образом, он считает 2 столбца и возвращает 60% премию. Брюс Ли не выполнил план, поэтому его ближайший нижний критерий — 0%.

Предлагаю посмотреть видеоурок о том, как работает ВПР, из курса «Основные функции Excel».

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

Пока что мы предложили только одно условие для анализа: название материала. На практике часто бывает необходимо сравнить разные диапазоны с данными и выбрать значение на основе критериев 2, 3 и так далее.

Допустим, нам нужно найти цену, по которой профнастил привез ОАО «Восток». Необходимо установить два условия для поиска по названию материала и по поставщику.

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

1. Добавьте крайний левый столбец в таблицу (важно!), Объединив «Поставщики» и «Материалы».
2. Точно так же комбинируем необходимые критерии запроса:
3. Теперь поместим курсор в нужное место и зададим аргументы функции:. Excel найдет нужную цену.

Использование нескольких условий для формулы ВПР

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

Вот небольшая таблица для наглядности.

одиннадцать

Допустим, перед нами стоит задача определить, по какой цене был импортирован профнастил от производителя ОАО «Восток». В этом случае нам нужно два условия:

1. Материал.
2. Режиссер.

Но это еще не все, ведь каждый производитель одновременно везет несколько товаров. Как выйти из этого положения? Вот как:

1. Крайний левый столбец добавляется в таблицу, чтобы поставщики и материалы находились в одной группе.
   12
2. Критерии также должны быть объединены.
   13
3. Курсор устанавливается в нужную позицию, а аргументы функции указываются в скобках (или через соответствующее диалоговое окно). = ВПР (I6; $ A $ 2: $ D $ 15; 4; ЛОЖЬ). Затем Excel определит требуемую стоимость.