В этом мире, управляемом данными, нужны различные инструменты для управления данными. Данные в реальном времени огромны, и получение подробностей о некоторых конкретных данных определенно было бы утомительной задачей, но с функцией VLOOKUP в Excel , эту задачу можно выполнить с помощью одной командной строки. В этой статье вы узнаете об одном из важных Функции Excel т.е. функция ВПР.
Прежде чем двигаться дальше, давайте кратко рассмотрим все обсуждаемые здесь темы:
- Что такое ВПР в Excel?
- Как это работает?
- Полное совпадение
- Приблизительное совпадение
- Первый матч
- Чувствительность к регистру
- Ошибки
- Двусторонний поиск
- Использование подстановочных знаков
- Множественные таблицы поиска
Что такое ВПР в Excel?
В Excel ВПР - это встроенная функция который используется для поиска и извлечения определенных данных из таблицы Excel. V означает «Вертикаль», и для использования функции ВПР в Excel данные должны быть расположены вертикально. Эта функция очень удобна, когда у вас огромный объем данных и практически невозможно вручную найти некоторые конкретные данные.
Как это работает?
Функция ВПР принимает значение, т.е. значение поиска, и начинает его поиск в крайнем левом столбце. Когда найдено первое вхождение искомого значения, оно начинает перемещаться прямо в этой строке и возвращает значение из указанного вами столбца. Эта функция может использоваться для получения как точных, так и приблизительных совпадений (совпадение по умолчанию - приблизительное совпадение).
Синтаксис:
Синтаксис этой функции следующий:
ВПР (lookup_value, table_array, col_index_num, [range_lookup])
где,
- lookup_value это значение, которое нужно искать в первом столбце данной таблицы
- table_index это таблица, из которой должны быть получены данные
- col_index_num столбец, из которого должно быть извлечено значение
- range_lookup - логическое значение, определяющее, должно ли искомое значение совпадать полностью или приблизительно ( ПРАВДА найдет самое близкое совпадение ЛОЖНЫЙ проверяет точное совпадение)
Полное совпадение:
Если вы хотите, чтобы функция ВПР выполняла поиск точного соответствия искомому значению, вам нужно будет установить range_lookup значение FALSE. Взгляните на следующий пример, который представляет собой таблицу, содержащую данные о сотрудниках:
Если вы хотите узнать назначение любого из этих сотрудников, вы можете сделать следующее:
- Выберите ячейку, в которой вы хотите отобразить результат, и введите знак «=»
- Используйте функцию ВПР и укажите lookup_value (Здесь это будет идентификатор сотрудника)
- Затем передайте другие параметры, т.е. table_array , col_index_num и установите range_lookup значение FALSE
- Следовательно, функция и ее параметры будут: = ВПР (104, A1: D8, 3, FALSE)
Функция ВПР начинает поиск идентификатора 104 сотрудника, а затем перемещается вправо в строке, где найдено значение. Это продолжается до col_index_num и возвращает значение, присутствующее в этой позиции.
Примерное совпадение:
Эта функция функции VLOOKUP позволяет вам извлекать значения, даже если у вас нет точного совпадения для loopup_value. Как упоминалось ранее, чтобы функция ВПР находила приблизительное совпадение, вам необходимо установить range_lookup значение ИСТИНА. Взгляните на следующий пример, где оценки отображаются вместе с их оценками и классом, к которому они принадлежат.
- Как и для точного совпадения, выполните те же действия.
- Вместо значения range_lookup используйте TRUE вместо FALSE
- Следовательно, функция вместе с ее параметрами будет: = VLOOKUP (55, A12: C15, 3, TRUE)
В таблице, отсортированной в порядке возрастания, функция ВПР начинает поиск приблизительного совпадения и останавливается на следующем наибольшем значении, которое меньше введенного вами значения поиска. Затем он перемещается прямо в этой строке и возвращает значение из указанного столбца. В приведенном выше примере значение поиска составляет 55, а следующее по величине значение поиска в первом столбце - 40. Следовательно, на выходе будет второй класс.
Первый матч:
Если у вас есть таблица, состоящая из нескольких значений поиска, функция ВПР останавливается при первом совпадении с ней и извлекает значение из этой строки в указанном столбце.
Взгляните на изображение ниже:
ID 105 повторяется, и когда значение поиска указано как 105, функция VLOOKUP вернула значение из строки, которая имеет первое вхождение значения поиска.
Чувствительность к регистру:
Функция ВПР не чувствительна к регистру. Если у вас есть значение подстановки в верхнем регистре, а значение в таблице небольшое, функция VLOOKUP все равно будет извлекать значение из строки, в которой это значение присутствует. Взгляните на изображение ниже:
Как вы можете видеть, значение, которое я указал в качестве параметра, - «RAFA», тогда как значение, представленное в таблице, - «Rafa», но функция ВПР все еще вернула указанное значение. Если у вас есть точное совпадение даже с регистром, функция VLOOKUP все равно вернет первое совпадение искомого значения независимо от используемого регистра. Взгляните на изображение ниже:
Ошибки:
Когда мы используем функции, естественно сталкиваться с ошибками. Точно так же вы можете столкнуться с ошибками при использовании функции VLOOKUP, и некоторые из наиболее распространенных ошибок:
- #ИМЯ
- # N / A
- #REF
- #ЦЕННОСТЬ
#NAME Ошибка:
Эта ошибка в основном состоит в том, чтобы сообщить вам, что вы допустили ошибку в синтаксисе. Чтобы избежать синтаксических ошибок, лучше использовать Мастер функций, предоставляемый Excel для каждой функции. Мастер функций поможет вам получить информацию о каждом параметре и типе значений, которые вам нужно ввести. Взгляните на изображение ниже:
Как видите, Мастер функций информирует вас о необходимости ввода любого типа значения вместо параметра lookup_value, а также дает его краткое описание. Точно так же, когда вы выбираете другие параметры, вы также увидите информацию о них.
# Н / Д Ошибка:
Эта ошибка возвращается, если для заданного значения поиска не найдено совпадение. Например, если я введу «AFA» вместо «RAFA», я получу ошибку # N / A.
Чтобы определить какое-либо сообщение об ошибке для двух вышеуказанных ошибок, вы можете использовать функцию IFNA. Например:
#REF Ошибка:
Эта ошибка возникает, когда вы даете ссылку на столбец, которого нет в таблице.
#VALUE Ошибка:
Эта ошибка возникает, когда вы вводите неправильные значения в параметры или пропускаете некоторые обязательные параметры.
хэш-карта и хеш-таблица в java
Двусторонний поиск:
Двусторонний поиск означает получение значения из двумерной таблицы из любой ячейки указанной таблицы. Чтобы выполнить двусторонний поиск с помощью ВПР, вам необходимо использовать вместе с ним функцию ПОИСКПОЗ.
Синтаксис MATCH следующий:
ПОИСКПОЗ (искомое_значение, искомое_массив, совпадение_типа)
- lookup_value это значение, которое нужно искать
- lookup_array это диапазон ячеек, которые содержат значения поиска
- match_type может быть числом, то есть 0, 1 или -1, представляющим точное совпадение, меньше и больше соответственно
Вместо использования жестко запрограммированных значений с ВПР вы можете сделать его динамическим обходом в ссылках на ячейки. Рассмотрим следующий пример:
Как вы можете видеть на изображении выше, функция ВПР принимает ссылку на ячейку как F6 для значения поиска, а значение индекса столбца определяется функцией ПОИСКПОЗ. Когда вы вносите изменения в любое из этих значений, выходные данные также изменятся соответствующим образом. Взгляните на изображение ниже, где я изменил значение, представленное в F6, с Криса на Лео, и результат также был обновлен соответствующим образом:
Если я изменю значение G5 или одновременно F6 и G5, эта формула будет работать соответственно, отображая соответствующие результаты.
Вы также можете создавать раскрывающиеся списки, чтобы облегчить задачу изменения значений. В приведенном выше примере это нужно сделать с F6 и G5. Вот как можно создавать раскрывающиеся списки:
- Выберите данные на вкладке ленты
- В группе «Инструменты для работы с данными» выберите «Проверка данных».
- Откройте панель настроек и в разделе Разрешить выберите Список.
- Укажите массив исходного списка
Вот как это выглядит после того, как вы создали раскрывающийся список:
Использование подстановочных знаков:
Если вы не знаете точное значение поиска, а знаете только его часть, вы можете использовать подстановочные знаки. В Excel символ «*» представляет собой подстановочный знак. Этот символ сообщает Excel, что последовательность, которая идет до, после или между ними, должна быть найдена, и что до или после них может быть любое количество символов. Например, в созданной мной таблице, если ввести «erg» вместе с подстановочными знаками с обеих сторон, функция ВПР вернет результат для «Sergio», как показано ниже:
Несколько таблиц поиска:
Если у вас есть несколько таблиц поиска, вы можете использовать функцию IF вместе с ней, чтобы просмотреть любую из таблиц на основе некоторого заданного условия. Например, если есть таблица с данными о двух супермаркетах, и вам нужно узнать прибыль, полученную каждым из них на основе продаж, вы можете сделать следующее:
Создайте основную таблицу следующим образом:
Затем создайте две таблицы, из которых нужно получить прибыль.
Как только это будет сделано, создайте именованный диапазон для каждой из вновь созданных таблиц. Чтобы создать именованный диапазон, выполните следующие действия:
- Выберите таблицу всю таблицу, которой вы хотите присвоить имя
- На вкладке ленты выберите «Формулы», а затем в группе «Определенные имена» выберите «Определить имя».
- Вы увидите следующее диалоговое окно
- Дайте любое имя на ваш выбор
- Нажмите ОК
Как только это будет сделано для обеих таблиц, вы можете использовать эти именованные диапазоны в функции ЕСЛИ следующим образом:
Как видите, функция ВПР вернула соответствующие значения для заполнения столбца «Прибыль» в зависимости от того, к какому супермаркету они принадлежат. Вместо того, чтобы писать формулу в каждой ячейке столбца Прибыль, я просто скопировал формулу в целях экономии времени и энергии.
На этом мы подошли к концу статьи о ВПР в Excel. Надеюсь, вы понимаете все, что вам поделились. Убедитесь, что вы как можно больше тренируетесь и верните свой опыт.
Есть вопрос к нам? Пожалуйста, укажите это в разделе комментариев этого блога «ВПР в Excel», и мы свяжемся с вами как можно скорее.
Чтобы получить глубокие знания о любых трендовых технологиях и их различных приложениях, вы можете зарегистрироваться в прямом эфире с круглосуточной поддержкой и пожизненным доступом.