Как использовать ВПР в Google Таблицах

Как

ПоРайан Дьюб

Последнее обновление12 октября 2020 г.

Если ты когда-нибудь использовали функцию ВПР в Excel, тогда вы знаете, насколько это мощная функция. Если вы не знаете, что делает ВПР в Google Таблицах, она работает так же, как и в Excel.

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

В этой статье вы узнаете больше о том, как работает функция ВПР в Google Таблицах и как ее использовать, с несколькими полезными примерами.

Что такое ВПР в Google Таблицах?

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

Точно так же это работает в Google Таблицах. Функция ВПР имеет четыре параметра, один из которых является необязательным. Эти параметры следующие:

• search_key: Это конкретное значение, которое вы ищете. Это может быть строка или число.
• диапазон: Любой диапазон столбцов и ячеек, который вы хотите включить в поиск.
• индекс: Это номер столбца (в выбранном диапазоне), в котором вы хотите получить возвращаемое значение.
• отсортировано (необязательно): если установлено значение ИСТИНА, вы сообщаете функции ВПР, что первый столбец отсортирован.

Об этих параметрах следует помнить несколько важных вещей.

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

Во-вторых, «индекс» должен быть от 1 до максимального количества столбцов в выбранном диапазоне. Если вы введете число, превышающее количество столбцов в диапазоне, вы получите сообщение об ошибке.

Использование ВПР в Google Таблицах

Теперь, когда вы понимаете, как работает ВПР, давайте рассмотрим несколько примеров.

Пример 1: простой поиск информации

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

Затем, возможно, у вас есть Google Sheet с зарегистрированными продажами сотрудников. Поскольку вы рассчитываете их комиссионные на основе даты их начала, вам потребуется ВПР, чтобы получить его из поля Дата начала.

Для этого в первом поле «Стаж» вы начнете вводить функцию ВПР, набрав «= ВПР («.

Первое, что вы заметите, - это появится окно справки. Если этого не произошло, нажмите синий значок «?» значок слева от ячейки.

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

Окно справки исчезнет, ​​но когда вы введете следующую запятую, оно появится снова.

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

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

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

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

Следующий параметр - это индекс. Мы знаем, что дата начала для сотрудника - это третий столбец в выбранном диапазоне, поэтому вы можете просто ввести 3 для этого параметра.

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

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

Заполните остальные поля под ним, и все готово!

Пример 2: извлечение данных из справочной таблицы

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

Чтобы найти правильную буквенную оценку в ячейке C2, просто выберите ячейку и введите: «= VLOOKUP (B2, $ E $ 1: $ F $ 6,2, TRUE)»

Вот объяснение того, что означают эти параметры.

• Би 2: Ссылка на числовую оценку теста для поиска
• $ E $ 1: $ F $ 6: Это буквенная таблица оценок с символами доллара, чтобы диапазон не изменялся, даже когда вы заполняете остальную часть столбца.
• 2: Ссылается на второй столбец таблицы поиска - Letter Grade.
• ПРАВДА: Сообщает функции ВПР, что оценки в таблице поиска отсортированы

Просто заполните оставшуюся часть столбца C, и вы увидите, что применяются правильные буквенные оценки.

Как видите, способ, которым это работает с отсортированными диапазонами, заключается в том, что функция ВПР получает результат для меньшего конца отсортированного диапазона. Таким образом, все от 60 до 79 возвращает D, от 80 до 89 возвращает C и так далее.

Пример 3: двусторонний поиск ВПР

Последний пример - использование функции ВПР с вложенной функцией ПОИСКПОЗ. Вариант использования для этого - когда вы хотите искать в таблице разные столбцы или строки.

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

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

Теперь выберите пустое поле «Результат» и введите «= ВПР (I2, A1: F31, MATCH (I3, A1: F1,0), FALSE)» и нажмите Войти.

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

• I2: Это имя, которое вы ввели в поле поиска имени, которое система ВПР попытается сопоставить с именем в крайнем левом столбце диапазона.
• A1: F31: Это весь диапазон имен, включая всю связанную информацию.
• ПОИСКПОЗ (I3; A1: F1,0): Функция сопоставления будет использовать введенное вами поле поиска, найдет его в диапазоне заголовков и вернет номер столбца. Этот номер столбца затем передается в параметр индекса функции ВПР.
• ЛОЖНЫЙ: Порядок данных в левом столбце не отсортирован.

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

Как видите, набрав имя и поле для возврата (Электронная почта), вы можете найти любую понравившуюся информацию.

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

Использование ВПР в Google Таблицах

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

Если у вас есть какие-либо проблемы с функцией, многие из советы по устранению неполадок, которые работают с ВПР в Excel будет работать и в Google Таблицах.