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

Опубликовано: 2021-07-10

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

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

Оглавление

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

    Объяснение функции ВПР

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

    Эта функция является частью =VLOOKUP() . Внутри функции находятся:

    • Ключ поиска — сообщает функции ВПР, что нужно найти.
    • Диапазон — сообщает функции ВПР, где его искать. ВПР всегда будет искать в крайнем левом столбце диапазона.
    • Индекс — сообщает функции ВПР, сколько столбцов справа от крайнего левого столбца в диапазоне нужно искать, если он находит совпадение с ключом поиска. Крайний левый столбец всегда равен 1, следующий справа — 2 и так далее.
    • Сортируется? – Сообщает ВПР, если первый столбец отсортирован. По умолчанию это значение TRUE, что означает, что функция ВПР найдет ближайшее совпадение с ключом поиска. Это может привести к менее точным результатам. ЛОЖЬ сообщает ВПР, что это должно быть точное совпадение, поэтому используйте ЛОЖЬ.

    Приведенная выше функция ВПР будет использовать любое значение в ячейке E1 в качестве ключа поиска. Когда он находит совпадение в столбце A диапазона ячеек от A1 до C5 , он будет искать в третьем столбце той же строки, в которой было найдено совпадение, и возвращать любое значение в нем. На изображении ниже показаны результаты ввода 4 в ячейку E1 . Далее давайте рассмотрим несколько способов использования функции ВПР в Google Таблицах.

    Пример 1. Использование функции ВПР для отслеживания заданий

    Допустим, у вас есть сервисный бизнес, и вы хотите узнать, когда начинается заказ на работу. У вас может быть один рабочий лист, прокрутите вниз до номера заказа на работу, а затем просмотрите строку, чтобы узнать, когда он начинается. Это может стать утомительным и склонным к ошибкам.

    Или вы можете использовать ВПР .

    1. Введите заголовки « Заказ на работу» и « Дата работы» где-нибудь на рабочем листе.
    1. Выберите ячейку справа от даты работы и начните вводить формулу = ВПР . Когда мы печатаем, появится окно справки, показывающее нам доступные функции Google Sheet, которые соответствуют тому, что мы печатаем. Когда он покажет VLOOKUP , нажмите Enter, и он завершит ввод.
    1. Чтобы указать, где функция ВПР будет находить ключ поиска , щелкните ячейку прямо над ним.
    1. Чтобы выбрать диапазон данных для поиска, щелкните и удерживайте заголовок столбца A и перетащите его, чтобы выбрать все, включая столбец H .
    1. Чтобы выбрать индекс или столбец, из которого мы хотим извлечь данные, посчитайте от A до H . H — это 7-й столбец, поэтому введите 7 в формулу.
    1. Теперь мы указываем, как мы хотим искать первый столбец диапазона. Нам нужно точное совпадение, поэтому введите FALSE .

    Обратите внимание, что он хочет поставить открывающую изогнутую скобку после FALSE. Нажмите Backspace, чтобы удалить это.

    Затем введите изогнутую закрывающую скобку ) и нажмите Enter , чтобы завершить ввод формулы.

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

    Чтобы протестировать формулу ВПР, введите номер первого заказа на работу в ячейку над формулой и нажмите Enter . Возвращаемая дата совпадает с датой в столбце WorkDate для рабочего задания A00100.

    Чтобы увидеть, как это упрощает жизнь, введите номер заказа на работу, который не отображается на экране, например A00231.

    Сравните возвращенную дату и дату в строке для A00231, и они должны совпадать. Если да, то формула хороша.

    Пример 2. Использование функции ВПР для расчета суточных калорий

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

    1. Выберите все данные в списке продуктов питания и калорий.
    1. Выберите Данные > Именованные диапазоны .
    1. Назовите диапазон FoodRange . Именованные диапазоны легче запомнить, чем Sheet2!A1:B:29 , который является фактическим определением диапазона.
    1. Вернитесь к рабочему листу, где отслеживается еда. В первой ячейке, в которой мы хотим показать калории, мы можем ввести формулу =ВПР(A3,FoodRange,2,False) .

    Это сработает, но поскольку в A3 ничего нет, будет уродливая ошибка #REF . В этом калькуляторе может быть много ячеек с едой, оставленных пустыми, и мы не хотим видеть повсюду #REF.

    1. Поместим формулу ВПР внутрь функции ЕСЛИОШИБКА . ЕСЛИОШИБКА сообщает Sheets, что если что-то пойдет не так с формулой, вернуть пустое значение.
    1. Чтобы скопировать формулу вниз по столбцу, выберите маркер в правом нижнем углу ячейки и перетащите его вниз на столько ячеек, сколько необходимо.

    Если вы думаете, что формула будет использовать A3 в качестве ключа вниз по столбцу, не беспокойтесь. Таблицы настроят формулу для использования ключа в строке, в которой находится формула. Например, на изображении ниже вы можете видеть, что ключ изменился на A4 при перемещении в 4-ю строку. Формулы также автоматически изменяют ссылки на ячейки при перемещении из столбца в столбец.

    1. Чтобы сложить все калории за день, используйте функцию =СУММ в пустой ячейке рядом с Итого и выберите все строки калорий над ней.

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

    1. Выберите столбец калорий за понедельник и вставьте его в столбец калорий за вторник , среду и т. д.

    Сделайте то же самое для ячейки Total ниже Monday. Итак, теперь у нас есть еженедельный счетчик калорий.

    Подведение итогов ВПР

    Если это ваше первое погружение в Google Sheets и функции, вы можете увидеть, насколько полезными и мощными могут быть такие функции, как VLOOKUP. Сочетание его с другими функциями, такими как ЕСЛИОШИБКА, или многими другими, поможет вам делать все, что вам нужно. Если вам это понравилось, вы можете даже подумать о переходе из Excel в Google Таблицы.