Как использовать VLOOKUP в Microsoft Excel

Отредактировано 2 несколько месяцев назад от ExtremeHow Редакционная команда

Microsoft ExcelФормулыПродуктивностьЭлектронная таблицаБизнесMacWindowsУправление данными

VLOOKUP — одна из самых популярных функций в Microsoft Excel. Эта функция означает "вертикальный поиск" и используется для поиска данных в вертикально расположенной таблице. Независимо от того, работаете ли вы с небольшим набором данных или с большой сложной таблицей, VLOOKUP может быть мощным инструментом для быстрого и точного поиска и извлечения информации.

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

Понимание функции VLOOKUP

Синтаксис VLOOKUP следующий:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Lookup_value: Это значение, которое вы хотите найти. Оно должно находиться в первом столбце таблицы, которую вы хотите искать.
• table_array: Это диапазон ячеек, содержащих данные. Он включает в себя столбец с lookup_value и столбец с возвращаемым значением.
• col_index_num: Это номер столбца в table_array, из которого должно быть возвращено совпадающее значение. Первый столбец имеет номер 1.
• range_lookup: (Необязательный) Логическое значение, указывающее, хотите ли вы, чтобы VLOOKUP находил точное совпадение или приблизительное совпадение. Если TRUE или опущен, будет возвращено приблизительное совпадение. Если FALSE, VLOOKUP найдет точное совпадение.

Простой пример VLOOKUP

Предположим, у вас есть простая таблица с именами учащихся и их оценками. Таблица находится в диапазоне A2:B5, как показано ниже:

 | Name | Grade | | Джон | 85 | | Джейн | 92 | | Лиза | 78 | | Марк | 88 | 

Если вы хотите найти оценку Джейн, используя VLOOKUP, вы можете использовать следующую формулу:

=VLOOKUP("Джейн", A2:B5, 2, FALSE)

в этом случае:

• lookup_value — это "Джейн".
• table_array — это A2:B5.
• col_index_num — это 2, потому что оценки находятся во втором столбце таблицы.
• range_lookup — это false, что означает, что вы хотите получить точное совпадение.

Формула вернет "92", что является оценкой Джейн.

Как использовать VLOOKUP с приблизительным совпадением

Существуют ситуации, когда точное совпадение не может быть найдено, или вы можете захотеть получить ближайшее совпадение. Именно здесь полезно приблизительное совпадение. Чтобы разрешить приблизительное совпадение, установите аргумент range_lookup в TRUE или оставьте его пустым.

Рассмотрим другую таблицу с ценами на предметы на основе прайс-листа:

 | Прайс-лист | | Диапазон | Цена | | 0-99 | 100 | | 100-199 | 200 | | 200-299 | 300 | | 300-399 | 400 | 

Если вы хотите узнать стоимость покупки 150 единиц, настройте формулу следующим образом:

=VLOOKUP(150, A2:B5, 2, TRUE)

Здесь:

• lookup_value — это 150.
• The table_array — это A2:B5, содержащий нижний предел диапазона.
• col_index_num — это 2, указывая на то, что значение находится во втором столбце.

Формула вернет "200", так как 150 попадает в диапазон 100-199.

Использование VLOOKUP с несколькими листами

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

 Лист товаров: | Код | | А1 | | B2 | | C3 | Лист цен: | Код | Цена | | А1 | 50 | | B2 | 60 | | C3 | 70 | 

На листе "Товары", предположим, вы хотите найти цену продукта с кодом A1. Используйте функцию VLOOKUP следующим образом:

=VLOOKUP(A2, Prices!A2:B4, 2, FALSE)

Здесь:

• The lookup_value — это ячейка A2, содержащая код "A1".
• table_array относится к листу "Цены" значениями!A2:B4.
• col_index_num — это 2, чтобы найти столбец с ценой.
• range_lookup — это false для точного совпадения.

Эта формула вернёт значение "50" для кода продукта A1.

Общие ошибки и их устранение

Хотя VLOOKUP — мощный инструмент, часто встречаются ошибки при его использовании. Вот некоторые распространенные ошибки и способы их исправления:

• Ошибка #N/A: Эта ошибка возникает, когда VLOOKUP не может найти совпадение для lookup_value. Убедитесь, что lookup_value существует в первом столбце table_array. Дважды проверьте орфографию и формат lookup_value, чтобы они совпадали с теми, что в диапазоне данных.
• Ошибка #REF!: Эта ошибка возникает, если col_index_num больше, чем количество столбцов в table_array. Проверьте диапазон table_array и col_index_num, чтобы убедиться, что вы ссылаетесь на правильный столбец.
• Ошибка #VALUE!: Это может произойти, если один из аргументов неверен, например, если col_index_num является текстом вместо числа. Дважды проверьте синтаксис формулы, чтобы убедиться, что все значения правильные.

Повышение эффективности VLOOKUP

VLOOKUP также можно комбинировать с другими функциями для увеличения его полезности и эффективности. Например, сочетание VLOOKUP с IFERROR может помочь более изящно обрабатывать ошибки. Вместо вывода ошибки вы можете вернуть пользовательское сообщение:

=IFERROR(VLOOKUP("Лиза", A2:B5, 2, FALSE), "Не найдено")

Если VLOOKUP не находит значение, эта формула вернёт "Не найдено" вместо ошибки.

Другой способ повысить эффективность VLOOKUP — использовать функцию CHOOSE при работе с несмежными данными. Функция CHOOSE помогает мгновенно создать виртуальный диапазон:

=VLOOKUP("B2", CHOOSE({1,2}, Indirect("Sheet1!B:B"), Indirect("Sheet3!D:D")), 2, FALSE)

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

Заключение

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

  Поделиться этой статьей

Если вы найдете что-то неправильное в содержании статьи, вы можете запросить обновление