Используйте Excel или Google Таблицы достаточно долго, и вы обязательно встретите термин ВПР. Что именно это такое и что оно делает? В этом руководстве мы обсудим, что делает VLOOKUP, как вы можете использовать его, чтобы облегчить свою работу, и предоставим вам инструкции о том, как лучше всего использовать его как в Excel, так и в Google Таблицах.
Мы также решаем различные вопросы, связанные с VLOOKUP, и типичные ошибки, с которыми вы можете столкнуться при использовании этой команды.
Что такое ВПР?
ВПР – сокращение от «вертикальный поиск» – это функция, созданная в Microsoft Excel, которая позволяет вам искать определенное значение в столбце, а затем использует эту информацию для поиска другого значения в той же строке.
ВПР.
Например: у вас может быть три столбца с пометками «имя», «номер» и «адрес», и эти столбцы мо
Contents
Что такое ВПР?
рмацией. Используя ВПР, вы сможете искать определенное имя в столбце «имя», а затем использовать эту информацию для отображения соответствующего номера или адреса, который можно найти в той же строке, что и это имя. Однако имейте в виду, что ВПР не учитывает регистр.
Хотя это может показаться не особенно полезным при использовании в небольшом пуле данных, как в вышеупомянутом примере, оно очень удобно, когда на вашем листе имеется большой объем информации и вы хотите использовать определенные значения в других областях..
Вы можете создать основной список информации на одном листе, а затем просто использовать VLOOKUP на последующих листах для извлечения данных из основного списка. Таким образом, вам нужно обновить только один лист, а значения будут автоматически распространяться на остальные.
Проще говоря, строка ВПР выглядит следующим образом:
=VLOOKUP( the value you want to lookup, the range of cells you want to look for it in, the column number of the value you want to display, whether you want an exact or approximate match )
Как использовать ВПР в Excel и Google Таблицах
На первый взгляд строка VLOOKUP может показаться сложной, но на самом деле она намного проще, чем кажется, как показывают эти подробные инструкции.
- Вам понадобится таблица данных, из которой можно будет извлечь информацию. Мы используем ту же идею, что и в приведенном ранее примере, и добавляем информацию в три столбца: имя, адрес и номер телефона.
<старый старт="2">
- Начните строку, дважды щелкнув пустую ячейку и введя
=VLOOKUP(. Вы увидите, что первое требуемое значение — «искомое_значение». какую информацию вы будете использовать для поиска номера телефона.
- Поскольку мы уже добавили имя «Айрис Уотсон» в ячейку, предшествующую нашему ВПР, именно эту ячейку вы будете использовать в качестве искомого_значения. В настоящее время ваша строка должна выглядеть примерно так:
=VLOOKUP(e12,
- Далее идет table_array. Это вся таблица данных, из которой вы извлекаете данные. Просто выделите всю таблицу, в которой находятся ваши данные. На этом этапе ваша строка должна выглядеть примерно так:
=VLOOKUP(e12,A1:C5,
- Третье значение в вашей строке — это «col_index_number» или номер индекса столбца. Это номер столбца, из которого вы пытаетесь извлечь информацию. В выбранном нами массиве таблицы «Имя» будет считаться 1, «Адрес» — 2, а «Номер телефона» — 3. Поскольку мы пытаемся выполнить ВПР номер телефона, наша строка выглядит следующим образом:
=VLOOKUP(e12,A1:C5,3,
- Наконец, последняя часть строки определяет, хотите ли вы получить точное или приблизительное совпадение для значения, по которому вы ищете. Вам нужно будет ввести ИСТИНА для приблизительного значения и ЛОЖЬ для точного значения. В нашем случае мы выбрали последнее. Наша последняя строка —
=VLOOKUP(E12,A1:C5,3,FALSE)
- Как видите, наш VLOOKUP успешно нашел соответствующий номер телефона для «Айрис Уотсон».
Примечание. В этом руководстве для выполнения ВПР используется Microsoft Excel, но вы можете использовать тот же метод с Google Таблицами.
Как фильтровать данные по нескольким критериям
ВПР была разработана для получения только одного значения, но существуют и другие функции для поиска в нескольких источниках информации — это не значит, что вы не можете использовать ВПР для поиска по нескольким критериям. Используя вспомогательный столбец, вы можете создавать уникальные идентификаторы, в которых будет храниться информация из нескольких ячеек, а затем настроить VLOOKUP для поиска этих уникальных идентификаторов.
Это полезно в тех случаях, когда у вас есть несколько ячеек с одинаковым значением. Допустим, например, что в нашей таблице есть несколько ячеек с именем «Айрис Уотсон». В обычных обстоятельствах VLOOKUP выберет только первую найденную в списке «Айрис Уотсон», но вы можете искать другую.
Благодаря использованию вспомогательного столбца у вас будет уникальный идентификатор, который поможет VLOOKUP различать разные «Айрис Уотсон» на вашем листе.
В этом примере мы используем VLOOKUP для отображения адреса вместо номера телефона. Мы также добавили на лист вторую «Айрис Уотсон» с другим адресом и номером телефона.
- Первое, что мы сделали, — это создали вспомогательный столбец, который объединяет ячейки «Имя» и «Номер телефона» для создания уникального идентификатора. Мы использовали функцию СЦЕПИТЬ, которая просто объединяет строки из разных ячеек. Ваша строка должна выглядеть примерно так:
=CONCATENATE(B2," | ",D2)Мы добавили символ вертикальной черты"|"и несколько пробелов между именем и номером телефона, чтобы ее было легче понять. Если вы хотите узнать больше об этой функции, прочтите нашу статью на как использовать СЦЕПИТЬ .
- После успешного объединения щелкните правый нижний угол выделенной ячейки и перетащите ее вниз к другим ячейкам вспомогательного столбца. К ним будет применена такая же конкатенация.
- Для VLOOKUP вам понадобятся два поля. Мы добавили поля поиска «Имя» и «Номер», а также поле «Адрес», в котором будет отображаться результат ВПР.
<старый старт="4">
Вот как наша строка выглядела изначально: =VLOOKUP(F9&" | "&F10,
F9 и F10 — это наши поля поиска «Имя» и «Адрес» соответственно, а символ & работает как объединение, поскольку объединяет оба поля вместе. « | « Часть строки — это тот же разделитель, который мы использовали при объединении в нашем вспомогательном столбце.
- Теперь вам нужно следовать только остальной части формулы ВПР. Вам нужно будет выб
Как фильтровать данные по нескольким критериям
новить для него ТОЧНОЕ совпадение. Это была наша конечная строка:=VLOOKUP(F9&" | "&F10,A1:D6,3,FALSE)
- Вы увидите, что недостаточно ввести в поля поиска просто имя или адрес. Оба должны быть заполнены для успешного поиска. Ниже вы можете увидеть два разных результата ВПР, поскольку у нас есть два разных «Айрис Уотсон». Вы получите разные результаты в зависимости от того, какой номер телефона вы введете во второе поле поиска.
ВПР лучше, чем ИНДЕКС-ПОИСКПОЗ?
Этот вопрос активно обсуждается со времен появления Microsoft Excel. Прежде чем мы ответим на этот вопрос, важно понять, что такое INDEX-MATCH. ИНДЕКС и ПОИСКПОЗ — это две отдельные функции, которые часто объединяются для создания более универсальной системы поиска, чем ВПР.
Функция ПОИСКПОЗ используется, когда вы хотите найти номер позиции определенного значения в диапазоне ячеек. INDEX, с другой стороны, использует два возможных формата для отображения значения из массива таблицы или диапазона ячеек. Комбинируя оба варианта, можно использовать функцию ПОИСКПОЗ для определения номеров позиций предоставленной информации, а затем использовать ИНДЕКС для использования этих номеров позиций для возврата значений.
Какой из двух вариантов лучше, зависит от того, кто использует эту функцию. VLOOKUP гораздо более удобен для новичков и пользователей Excel или Google Sheet среднего уровня, учитывая, насколько проще его настроить и использовать. Однако INDEX-MATCH гораздо более гибок и может использоваться в более широком спектре ситуаций.
В конце концов, гораздо больше людей знают, как использовать ВПР по сравнению с ИНДЕКС-ПОИСКПОЗ, поэтому первый вариант, вероятно, является лучшим выбором, если к листу будут иметь доступ многие неопытные пользователи. Но если таблица предназначена для экспертов, то, вероятно, подойдет INDEX-MATCH.
Что можно и чего нельзя делать при использовании ВПР
Нет ничего постыдного в том, чтобы допустить несколько ошибок, если вы новичок в использовании функции ВПР. Большинство ветеранов Excel побывали там в какой-то момент своей жизни. Вот несколько вещей, которые следует учитывать при использовании ВПР.
1. ОБЯЗАТЕЛЬНО убедитесь, что Lookup_Value является первым столбцом в вашей таблице.
ВПР работает в предположении, что искомое_значение находится в первом столбце массива таблицы. Он может отображать информацию только в том случае, если это значение находится в последующей ячейке той же строки. Поместите искомое_значение в любое место, кроме начала, и функция завершится ошибкой.
2. НЕ забывайте использовать FALSE для точных совпадений.
Последняя часть строки VLOOKUP позволяет указать, будет ли значение FALSE для точного совпадения или TRUE для частичного совпадения. Многие пользователи ошибочно используют TRUE, что может привести к неточным результатам, или вообще забывают установить значение.
3. ОБЯЗАТЕЛЬНО проверьте порядковый номер столбца.
То, что отображает VLOOKUP, во многом зависит от того, правильно ли вы установили «col_index_num» при вводе строки. «col_index_num» или номер индекса столбца — это числовое значение, установленное для каждого столбца в массиве таблицы. Значение первого столбца считается 1, второго — 2 и так далее. Введите в строку неправильное значение «col_index_num», и функция покажет совершенно другой результат.
4. ОБЯЗАТЕЛЬНО используйте F4 при копировании формулы в другие ячейки.
Одна из самых полезных особенностей ВПР — то, что вы можете перетаскивать формулу вниз, чтобы скопировать ее в разные ячейки. Проблема в том, что значения, указанные в строке функции, также сдвинутся вниз, разрушая всю формулу. Чтобы предотвратить это, поместите курсор на значения в формуле и нажмите клавишу F4. Это преобразует их в абсолютные значения, которые не изменятся при копировании формулы.
Обычные ошибки ВПР и способы их исправления
Наиболее распространенной ошибкой, с которой вы можете столкнуться при использовании ВПР, является ошибка «#NA», хотя стоит отметить, что существует множество причин появления этой ошибки.
1. Ваше Lookup_value не находится в первом столбце массива таблицы.
Одним из самых больших ограничений VLOOKUP является то, что он может искать значения только в самом первом столбце массива таблиц. Если вашего искомого_значения там нет, это приведет к ошибке #NA. Чтобы исправить это, вы можете настроить формулу так, чтобы она ссылалась на другой столбец, или переместить столбцы так, чтобы искомое_значение было в нужном месте.
2. ВПР не находит точного совпадения.
Последнее значение в вашей формуле VLOOKUP — это аргумент range_lookup, которому вы либо присваиваете значение TRUE для приблизительного совпадения, либо FALSE для точного совпадения. Если для этого аргумента установлено значение FALSE и VLOOKUP не может найти точное совпадение, вы получите ошибку #NA.
Если вы абсолютно уверены, что в вашем искомом_значении должно быть совпадение, вам следует дважды проверить информацию в массиве таблиц, чтобы убедиться, что все правильно отформатировано и не содержит нежелательных пробелов. Непечатаемые символы также могут помешать VLOOKUP правильно находить элементы.
3. Чрезмерно большие числа с плавающей запятой
По сути, числа с плавающей запятой — это числа, имеющие десятичную точку. При использовании ВПР, если у вас есть число, в котором после десятичной точки слишком много цифр, вы получите ошибку #NA. Исправить это довольно легко: округлите число максимум до пяти знаков после запятой, и все должно сработать. Вы можете сделать это с помощью функции ROUND.
Часто задаваемые вопросы
1. Что произойдет, если оставить аргумент range_lookup пустым?
Четвертый аргумент в строке ВПР, которому должно быть присвоено значение «ИСТИНА» или «ЛОЖЬ», считается необязательным. Установка значения «ИСТИНА» означает, что ваш ВПР будет искать приблизительное совпадение, а значение «ЛОЖЬ» требует, чтобы значение было точным. Дело в том, что если вы оставите этот аргумент пустым, ВПР по умолчанию присвоит ему значение «ИСТИНА», что может испортить желаемые результаты.
2. Каковы альтернативы использованию VLOOKUP?
Лучшей альтернативой использованию функции ВПР является комбинация ИНДЕКС-ПОИСКПОЗ. Однако имейте в виду, что эту комбинацию функций немного сложнее изучить, поскольку вам нужно освоить две разные функции и убедиться, что они правильно работают вместе.
3. Можете ли вы искать значения по строкам, а не по столбцам?
Да. Это можно сделать с помощью HLOOKUPили «горизонтального поиска» в Excel и Google Sheet
ВПР лучше, чем ИНДЕКС-ПОИСКПОЗ?
в определенной строке, а затем отображает значение из другой строки, но в том же столбце.Если вы хотите ограничить поиск одной строкой или столбцом, вы можете использовать функцию ПРОСМОТР.
