Функции ВПР (VPR) и ГПР (GPR) в Excel

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

Функция ВПР

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

=ВПР(искомое_значение; диапазон_поиска; номер_столбца; [точное_соответствие])

где:

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

Номер столбца определяет, из какого столбца в диапазоне_поиска нужно вернуть значение. Если диапазон_поиска содержит несколько столбцов, то номер_столбца соответствует номеру столбца, из которого нужно вернуть значение. Например, если диапазон_поиска содержит столбцы A, B и C, а номер_столбца равен 2, то функция вернет значение из столбца B.

Если точное_соответствие равно TRUE, то функция ВПР ищет значение, которое точно соответствует искомому значению в столбце диапазона_поиска. Если точное_соответствие равно FALSE или пропущено, то функция ищет ближайшее значение, которое меньше или равно искомому значению в столбце диапазона_поиска.

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

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

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

Например, если мы хотим найти зарплату для сотрудника с именем «Иванов», то мы можем использовать следующую формулу:

=ВПР(«Иванов»; A2:B10; 2; FALSE)

В этой формуле:

  • "Иванов" — искомое значение;
  • A2:B10 — диапазон поиска, который содержит столбцы A и B;
  • 2 — номер столбца в диапазоне поиска, из которого нужно вернуть значение, то есть столбец B с зарплатами;
  • FALSE — точное соответствие, так как мы ищем значение, которое точно соответствует искомому значению в столбце A.

Функция ВПР вернет значение зарплаты для сотрудника с именем «Иванов».

Функция ГПР

Функция ГПР (HLOOKUP) работает аналогично функции ВПР, но поиск производится в одной строке, а не в одном столбце. Формат функции ГПР выглядит следующим образом:

=ГПР(искомое_значение; диапазон_поиска; номер_строки; [точное_соответствие])

где:

  • искомое_значение — значение, которое необходимо найти в строке диапазона_поиска;
  • диапазон_поиска — диапазон ячеек, в которых ищется искомое значение;
  • номер_строки — номер строки в диапазоне_поиска, из которой нужно вернуть значение;
  • точное_соответствие (необязательный аргумент) — логическое значение, определяющее, должно ли искомое значение соответствовать значению в строке точно.

Номер строки определяет, из какой строки в диапазоне_поиска нужно вернуть значение. Если диапазон_поиска содержит несколько строк, то номер_строки соответствует номеру строки, из которой нужно вернуть значение.

Если точное_соответствие равно TRUE, то функция ГПР ищет значение, которое точно соответствует искомому значению в строке диапазона_поиска. Если точное_соответствие равно FALSE или пропущено, то функция ищет ближайшее значение, которое меньше или равно искому значению в строке диапазона_поиска.

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

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

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

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

=ГПР(«Яблоки»; A1:D2; 2; FALSE)

В этой формуле:

  • "Яблоки" — искомое значение;
  • A1:D2 — диапазон поиска, который содержит строки 1 и 2;
  • 2 — номер строки в диапазоне поиска, из которой нужно вернуть значение, то есть строка 2 с ценами;
  • FALSE — точное соответствие, так как мы ищем значение, которое точно соответствует искомому значению в строке 1.

Функция ГПР вернет значение цены для продукта «Яблоки».

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

Практическое задание по функции ВПР (VLOOKUP)

Основное Применение Функции ВПР:

  1. Используйте функцию ВПР для поиска информации о продуктах в листе ‘Таблица для ВПР’, ссылаясь на данные из листа ‘Таблица для поиска’.
  2. Для каждого ID продукта в ‘Таблице для ВПР’ найдите соответствующее название продукта, категорию и цену в ‘Таблице для поиска’.

Обработка Несоответствий и Ошибок:

  1. Убедитесь, что вы правильно обрабатываете случаи, когда ID продукта в ‘Таблице для ВПР’ не найден в ‘Таблице для поиска’.
  2. В таких случаях отобразите сообщение об ошибке или укажите на отсутствие данных.

Форматирование Результатов:

  1. Отобразите результаты ВПР в столбце ‘Искомая информация’ листа ‘Таблица для ВПР’ в формате «Название — Категория — Цена».

Инструкции по Выполнению

  1. Откройте предоставленный Excel файл.
  2. Используйте функцию ВПР для выполнения задачи, следуя указаниям в задачах.
  3. По возможности, организуйте свои формулы и результаты на листе ‘Таблица для ВПР’ для удобства анализа и проверки.
  4. Ознакомьтесь с инструкциями на отдельном листе для более подробного понимания требований задания.
Добавить комментарий