Как работает впр в гугл таблицах
Перейти к содержимому

Как работает впр в гугл таблицах

  • автор:

Функция VLOOKUP (ВПР) в Google таблице

Функция VLOOKUP (ВПР) в Google таблице

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

Синтаксис данной функции:

  • Запрос (search_key) — наше искомое значение или текст (часть текста). Если ищем текст — помещаем его в кавычки. Так же, искомым значение может выступать другая функция.
  • Диапазон (range) — или таблица/столбец/строка, где мы планируем искать наш запрос. Так же можно искать данные из других документов google, указывая вместо диапазона ссылку на таблицу.
  • Номер_столбца (index) — порядковый номер столбца в google документе или excel таблице от столбца, по которому мы хотим отображать информацию нашего запроса .
  • [Отсортировано] ([is_sorted]) — тип поиска нашего запроса: где точное совпадение искомой информации — 0 (либо FALSE («ЛОЖЬ»)); или приблизительное — 1 ( либо TRUE «ИСТИНА»)).

Как найти нужное значение или текст в Google (Excel) таблице?

Чаще всего стоит задача найти в большом массиве данных конкретную информацию по заданным параметрам — нашему запросу . Это какой-нибудь SKU (Артикул) в прайс-листе или цены на конкретные товары. Есть ситуации, где поставщики часто изменяют какие-нибудь описания в товарах/услугах и т.д., и нам нужно так же быстро изменить эту информацию в нашей таблице. В этих случаях нам поможет функция VLOOKUP (ВПР).

Простой пример поиска данных через ВПР в Google (Excel) таблицах.

Рассмотрим реальный пример из действующего интернет-магазина. Поставщик прислал прайс лист, в котором нам нужно накидать заказ. Но у нас:

  • во-первых , не все товары из прайса представлены в нашем интернет-магазине;
  • во-вторых , SKU (Артикулы) / EAN / Штрихкоды могут отличаться или иметь свои внутренние дополнения. Нам нужно как-то состыковать имеющийся у нас ассортимент с ассортиментом поставщика;
  • в-третьих , сортировка наименований у нас в базе магазина и у поставщика может (чаще всего так и есть) отличаться. Это когда у нас товары отсортированы по категориям, а у поставщика по алфавиту.
Сопоставляем данные

В том же самом файле поставщика вы вставляете данные своей базы склада интернет-магазина или любой другой базы данных, по которым будете сопоставлять данные поставщика. У меня это SKU (Артикул). В том случае, если в вашей базе данных склада SKU имеют внутренние дополнения — их стоит привести к виду, какой изначально вам задавал поставщик, либо наоборот.

В ячейке K2 пишем формулу, поставив знак «равно» («=»):

Пример формулы ВПР в гугл таблицах

Виды ошибок в работе с функцией VLOOKUP (ВПР)

Вы заметили, что некоторые ячейки выдали ошибку #N/A (#Н/Д). Это потому, что в искомом столбце не нашлись некоторые артикулы, которые есть у нас в базе данных склада. Либо мы неверно написали артикул у себя в базе, либо у поставщика на данный момент просто такого товара нет в наличии и он его не отобразил, либо искомая информация у нас или у поставщика записана в другом формате (вместо числа — текст, или наоборот).

  1. В первом случае (если мы ошибочно написали SKU у себя) — просто ищем и исправляем ошибку у себя (или у поставщика, если уверены, что ошибку допустил он).
  2. Во втором (когда у поставщика просто временно нет этого товара и он его просто не указал в файле) — внедряем в конструкцию формулы проверку на ошибку. Дописав в конструкцию: IFERROR (ЕОШИБКА).
  3. В третьем (когда форматы ячеек отличаются) — внедряем в конструкцию формулы параметр, преобразующий все значения в текст. TEXT (ТЕКСТ).

Давайте внедрим сразу все дополнения в формулу функции VLOOKUP (ВПР), чтобы избавиться от всех ошибок сразу. Формула будет иметь следующий синтаксис:

Доработанная и преобразованная формула функции ВПР в google таблицах

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

Сложный пример поиска данных через ВПР в Google (Excel) таблицах

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

Задача стоит следующая: по определенным параметрам из списка статусов, находящегося в специальном столбце и его (списка статусов) положения в строках — нужно отобразить название товара из соседней вкладки со списком товаров, ссылаясь на его артикул:

  1. формула ищет этот ключевой статус «Facebook» во вкладке склада в определяемом формулой столбце;
  2. узнает номер строки, на пересечении ячейки (столбца) которой стоит этот статус;
  3. переходит в нужную вкладку формирования фида для массового импорта товаров в магазин на Facebook;
  4. считывает артикул в столбце A;
  5. переходит обратно во вкладку со списком товаров интернет-магазина;
  6. и динамически отображает название товара, соответствующее считанному артикулу из вкладки фида. Т.е., если статус изменится, отображаться в ячейке будет другой товар со статусом «Facebook», находящийся выше или ниже по строкам в списке товаров в базе данных склада магазина.

В итоге формируется динамический фид данных google таблиц, который передается через API в Facebook и формирует там магазин с товарами, который, в свою очередь, передает данные в Instagram, и там тоже формируется магазин с товарами.

Синтаксический вид этой формулы может изначально озадачить, но я вас уверяю, со временем вы тоже сможете разобраться в сложных составных формулах:

Пример сложной составной формулы, в которой есть элемент ВПР в гугл таблицах

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

Формула VLOOKUP (ВПР) довольно сложная в освоении, но очень полезная для повседневной работы. С ее помощью можно найти часть текста, число или значение ячейки. Поделитесь в комментариях, получилось ли у вас воспользоваться данной функцией и при решении каких задач вы ее использовали?

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

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

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

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

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

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

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

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

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

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

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

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

  1. Введите заголовки «Заказ на работу» и «Дата работы» где-нибудь на листе.

  1. Выберите ячейку справа от даты работы и начните вводить формулу = ВПР. По мере того, как мы набираем текст, всплывает окно справки, показывая, что доступны Функции Google Sheet соответствуют тому, что мы набираем. Когда он покажет VLOOKUP, нажмите Enter, и он завершит набор.

  1. Чтобы указать, где VLOOKUP найдет ключ поиска, щелкните ячейку прямо над ним.

  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. В этом калькуляторе может быть много пустых ячеек Food, и мы не хотим видеть #REF повсюду.

  1. Поместим формулу ВПР в функцию ЕСЛИОШИБКА. ЕСЛИОШИБКА сообщает Таблицам, что если с формулой что-то пойдет не так, верните пустое поле.

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

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

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

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

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

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

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

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Руководство по функции ВПР (VLOOKUP) в Google Таблицах (с примерами)

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

Теперь, если это описание звучит скучно и сложно, вот еще один способ понять, что делает эта функция.

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

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

Именно так работает функция VLOOKUP (ВПР) в Google Таблицах.

Если идет вниз по столбцу списков / элементов, ищет указанный элемент и затем возвращает соответствующее значение из той же строки.

Но как именно работает ВПР? Если вас все еще смущает функция ВПР в Google Таблицах, подождите, пока мы не дойдем до раздела с примерами.

Но перед этим давайте быстро взглянем на синтаксис функции Google Таблиц Vlookup:

Синтаксис функции ВПР ( VLOOKUP ) в Google Таблицах

Вот как выглядит формула VLOOKUP :

  • search_key — это значение или элемент, который вы ищете. Например, в случае с рестораном это будет бургер или пицца.
  • диапазон — это диапазон, который будет использоваться в функции Vlookup. В крайнем левом столбце этого диапазона будет выполняться поиск search_key.
  • index — это номер столбца, из которого вы хотите получить результат. Первый столбец в диапазоне — 1, второй столбец — 2 и так далее. Обратите внимание, что это значение должно быть от 1 до общего количества столбцов. В противном случае будет возвращено #VALUE! Ошибка.
  • is_sorted — [ ИСТИНА по умолчанию] — в этом аргументе вы можете указать, ищете ли вы точное или приблизительное совпадение. Вы можете использовать FALSE для точного совпадения и TRUE для приблизительного совпадения. Когда вы используете ИСТИНА, список необходимо отсортировать по возрастанию. Если вы не укажете здесь значение, по умолчанию будет ИСТИНА. Обратите внимание, что для использования

Теперь давайте рассмотрим несколько примеров, чтобы понять, как использовать функцию Google Sheets Vlookup в реальных сценариях.

ВПР для чайников: как работает VLOOKUP в реальном мире?

Пример 1: поиск оценок учащихся из списка

В приведенном ниже примере у меня есть имена студентов и их оценка по предмету (скажем, по математике).

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

В таких случаях полезно знать ВПР.

Вот формула, по которой вы получите оценки указанных учеников.

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

Совет от профессионала: вы также можете создать раскрывающийся список студентов, чтобы вам не приходилось вводить имя вручную.

Пример 2: Найдите оценку учащегося с помощью функции ВПР в Google Таблицах

В примере 1 вы искали точное совпадение имени, чтобы получить отметки.

В этом примере позвольте мне показать вам, как использовать приблизительное соответствие, чтобы получить оценки учащихся на основе их оценок.

Ниже приведена таблица оценок, по которой определяется оценка ученика:

В этом примере нам нужно найти оценки в столбце C на основе оценок (в столбце B). Шкала оценок — E2: F7.

Теперь, прежде чем использовать это, вам нужно знать, что диапазон оценок должен быть в заданном формате. Например, здесь не может быть 0-33, 33-50, 50-70 и т. д. Вам нужно, чтобы числа были отсортированы в порядке возрастания.

Вот формула, по которой вы получите оценку:

Как это работает: функция ВПР (VLOOKUP) ищет указанную оценку (которая в данном случае является ключом поиска) и ищет ее в столбце «Диапазон оценок» (который является крайним левым столбцом диапазона поиска). Он идет сверху вниз и, когда находит число, которое больше его самого, возвращает оценку из предыдущей строки. Например, если оценка равна 44, функция будет просматривать числа в E2: E7. Поскольку 0 меньше 44, он переходит к 33, который снова ниже 44, поэтому он переходит к 50, что выше. Таким образом, он возвращается к предыдущему значению (33) и возвращает свою оценку (то есть E).

Пример 3: двусторонний поиск с использованием функции Vlookup в Google Таблицах

До сих пор мы видели использование Vlookup для возврата значения из одного столбца, поскольку мы жестко запрограммировали значение. Например, в случае примера 1 он всегда будет возвращать оценку из столбца 2, поскольку мы жестко запрограммировали значение 2 в формуле.

Однако предположим, что у вас есть набор данных, как показано ниже:

Здесь вы можете использовать технику двустороннего поиска, чтобы получить оценки для Брэда (в ячейке F4) в математике (в ячейке G3).

Вот формула, которая сделает это:

Как это работает: в этом случае, чтобы сделать предметную часть динамической, мы использовали функцию MATCH (ПОИСКПОЗ) в функции VLOOKUP (ВПР). Функция MATCH ищет имя субъекта в A1: D1 и возвращает номер столбца, в котором находит совпадение. Этот номер столбца затем используется в функции ВПР для получения оценок указанного учащегося по этому предмету.

Мы надеемся, что эта статья ВПР( VLOOKUP ) для чайников помогла объяснить, как ВПР работает в Google Таблицах.

Функция ВПР (VLOOKUP) в Google таблицах – синтаксис и примеры

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

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

Мы начнем с самого простого и постепенно будем рассматривать все более сложные возможности применения этой замечательной функции.

На примерах мы рассмотрим, как наиболее правильно и эффективно использовать её в ваших Google таблицах.

Особое внимание мы уделим ошибкам, возникающим при использовании ВПР (VLOOKUP), а также приёмам, которые позволят сделать её использование простым и вместе с тем эффективным.

Синтаксис функции ВПР (VLOOKUP) в Google таблицах

Запомнить назначение её просто: ВПР (VLOOKUP) означает сокращение “Vertical Look Up” или «Вертикальный ПРосмотр».

Очень часто случается, что у вас есть таблица с перечнем наименований чего-либо (прайс-лист, список сотрудников и т.д). При этом для каждого наименования имеются какие-то значения, которые ему принадлежат (например, цена, вес, оклад, размер, объём и т.п.). Как правило, наименования располагаются в первом столбце таблицы, а рядом с каждым из них в строке находятся значения.

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

Синтаксис функции позволят нам применять ее для очень большого круга задач, при котором необходим поиск и возврат определённого значения.

=ВПР(запрос; диапазон; номер_столбца; [отсортировано])

=VLOOKUP(search_key, range, index, [is_sorted])

запрос (search_key) : это то наименование, которое ВПР будет искать в первом столбце того диапазона (таблицы), который мы ей укажем.

диапазон (range): это та таблица или диапазон данных, в котором будет происходить поиск. Именно в первом столбце этого диапазона мы и будем искать наш запрос.

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

номер_столбца (index): номер столбца, значение из которого вы хотите получить, когда будет найден запрос. Нумерация столбцов всегда начинается с 1. Отсчет начинается слева направо. Слолбец 1 – это всегда столбец , в котором происходит поиск. Столбец 2 – это столбец, находящийся справа от него, и так далее.

отсортировано (is_sorted): необязательный параметр. Он указывает, отсортирован ли первый столбец диапазона, в котором мы будем искать наш запрос. Может принимать два значения – ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Если вы ничего не укажете, то по умолчанию устанавливается значение TRUE.

Если диапазон не отсортирован (FALSE) и в этом случае функция будет искать точное совпадение параметра «запрос» с одним из значений первого столбца диапазона. Как только будет найдено точное совпадение, поиск прекращается.

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

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

Значение, которое находится на пересечении строки, в которой функция нашла запрос, и столбца, номер которого указан в «номер столбца», в результате будет возвращено функцией ВПР. Возвращено – значит, записано в ту ячейку, из которой мы вызвали эту функцию.

Если точного совпадения не удастся обнаружить, то будет возвращено сообщение об ошибке (#N/A).

По умолчанию считается, что диапазон отсортирован (TRUE). В этом случае поиск будет производиться до первого приблизительного совпадения, то есть будем искать похожие значения, а не точные.

Если ваш диапазон поиска не отсортирован, а вы по ошибке указали значение ИСТИНА (TRUE) (либо вообще ничего не указали по забывчивости, что также означает TRUE), то очень велика вероятность, что функция ВПР ничего не найдет и вернет ошибку.

К примеру, если ваш критерий поиска начинается с буквы “A”, а в начале списка находится наименование, начинающееся с буквы “C”, то, оценив это первое наименование, функция решит, что если встретилась буква “C”, то в отсортированном списке букву “A” дальше искать бессмысленно. Поиск прекратится и будет возвращена ошибка (#N/A), несмотря на то, что правильное наименование в вашем диапазоне было. Но вы об этом даже не узнаете.

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

Поэтому рекомендуется всегда указывать значение ЛОЖЬ (FALSE) в качестве параметра «отсортировано» (is_sorted).

Вы спросите – а зачем же тогда этот параметр, если его значение ИСТИНА (TRUE) приводит к таким проблемам. Ответ заключается в том, что если всё же вы будете применять формулу ВПР (VLOOKUP) на отсортированном массиве, то производительность и скорость поиска возрастут по разным оценкам примерно в 50 (пятьдесят!) раз. При работе с большими таблицами это будет очень заметно.

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

Мы познакомились с синтаксисом функции ВПР (VLOOKUP), теперь рассмотрим особенности её применения на примерах.

Как эффективно использовать ВПР (VLOOKUP) в гугл таблицах?

Давайте начнем с самого простого применения формулы ВПР в Google таблице. Предположим, у нас есть две таблицы. Первая – это прайс лист с наименованиями и ценами. Вторая – это заказ на покупку некоторых из этих товаров. Искать в прайс листе нужный товар и руками вписывать в заказ его цену – занятие очень утомительное. Ведь он может насчитывать сотни строк. Нам необходимо сделать, чтобы всё происходило автоматически.

исходные данные

В ячейке F3 пишем знак равно (=) и начинаем вводить формулу с ее первых букв «вп». Обычно в этот момент появляется подсказка и мы можем просто выбрать необходимую нам функцию. Далее, как обычно, появляются подсказки, которые позволяют нам определить, какой аргумент функции мы сейчас вводим.

Первым аргументом введём “бананы”. Обратите внимание, что любой текст, который мы вводим, должен быть в кавычках.

Ставим запятую, и подсказка нам покажет, что теперь нужно ввести диапазон со значениями (таблица). В нашем примере нужно ввести это A3:B21. Как и обычно, мы можем просто выделить нужный диапазон мышкой, и он вставится в формулу сам, или ввести его координаты с клавиатуры.

После этого нужно опять поставить запятую и указать номер столбца, значение из которого нам нужно вернуть. В нашем случае это 2.

Последняя запятая, и пишем ЛОЖЬ (FALSE), то есть искать будем точное совпадение.

Наша функция в ячейке F3 будет выглядеть так:

И она должна вернуть цену 1.9.

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

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

как работает функция ВПР

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

2 – ищем в этом столбце слово “бананы” с точным соотвествием. Оно находится в 5-й строке.

3 – двигаемся вправо по строке, в которой нашлось искомое слово, до второго столбца (столбец поиска считаем первым).

4 – значение, указанное во втором столбце пятой строки нашего диапазона (1.9), вставляем в ячейку F3, в которую мы ранее вписали формулу ВПР.

Как видите, всё довольно просто.

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

А сейчас изменим нашу формулу в ячейке F3:

Теперь процесс поиска будет выглядеть следующим образом:

как работает ВПР

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

Поиск по части значения аргумента при использовании ВПР (VLOOKUP).

Если нам нужно найти значение, но мы знаем лишь часть от него, нам нужно использовать знаки подстановки.

Это знакомые нам вопросительный знак (?) и звездочка (*). Напомню, что вопросительный знак заменяет собой любой символ, а звездочка – любое количество символов (в том числе и ноль).

Предположим, нам нужно узнать цену на товар, название которого начинается с “пер”. Мы хотели бы получить цену персика.

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

регулярные выражения в функции ВПР

Как видим, функция искала в столбце “Товар” значение, начинающееся с “пер”.

Вы спросите: «А почему был выбран “персик”, а не “перец”? Ведь первые три буквы у них одинаковы?». Дело в том, что, как мы уже отмечали, функция ВПР (VLOOKUP) ищет подходящее значение, двигаясь сверху вниз. И как только подходящее совпадение было найдено, дальнейший поиск был прекращён. Поэтому вместо цены персика мы получили цену перца.

Это очень важное ограничение функции ВПР, которое нужно обязательно учитывать.

Как использовать ВПР с данными другой таблицы

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

Продолжим рассматривать наш пример. Заполним таблицу «Заказ» ценами из таблицы «Прайс лист» и рассчитаем стоимость товаров.

Мы уже нашли в прайс-листе цену для бананов. Напомню, в ячеейке F3 мы записали

Но для того, чтобы использовать эту формулу для заполнения таблицы, её нужно немного изменить. Добавим абсолютные ссылки, чтобы при копировании формулы получить правильные ссылки.

Теперь наша формула в F3 выглядит следующим образом:

Теперь ссылка на диапазон поиска не будет меняться при копировании и перемещении формулы.

В столбце G введите формулу, которая рассчитает итог. К примеру, в ячейке G3

Теперь скопируйте формулы в ячейки, расположенные ниже. Можно использовать комбинацию клавиш Ctrl+C Ctrl+V, а можно зацепить мышкой правый нижний угол ячейки и перетащить вниз.

ВПР с несколькими таблицами

Таким образом, таблица “Заказ” оказалась связанной с таблицей “Прайс лист”. При помощи ВПР мы получаем из нее цены заказанных товаров.

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

функция ВПР работа с разными листами

В нашей формуле мы должны изменить адрес диапазона, в котором будет происходить поиск.

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

Важно! Не забудьте в ссылке на диапазон поиска использовать абсолютные ссылки ($)!

Ну и, наконец, рассмотрим случай, когда таблица с ценами находится не просто на другом листе в том же самом файле, а расположена в другом файле Google таблиц. К примеру, мы собрали все прайс-листы в отдельный файл таблиц – так легче с ними работать.

Здесь нам на помощь придёт функция IMPORTRANGE, которая позволяет получать данные из других файлов Google таблиц.

Вот как будет выглядеть теперь наша функция поиска цены товара в ячейке С3:

ВПР и IMPORTRANGE

Как видите, вновь изменилась только ссылка на диапазон данных.

В качестве аргументов функции IMPORTRANGE мы используем:

1 аргумент – ссылка на файл Google таблиц. Ее можно получить из адресной строки браузера, открыв эту таблицу в новом окне.

2 аргумент – обычная ссылка на диапазон данных, которая обязательно включает в себя наименование листа (в нашем примере – лист “1”).

Все остальные действия ничем не отличаются от того, что мы с вами уже рассмотрели.

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

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

Другие примеры использования функций Google таблиц:

Функция ЕСЛИ в Google таблице – примеры использования — Функция ЕСЛИ в Google таблицах – это одна из самых простых функций, но при этом она очень полезна. Она относится к десятку функций, которые нужно обязательно знать и уметь применять.…
Как использовать функцию СЧËТЕСЛИ в Google таблицах — Функция СЧЕТËСЛИ в Google таблицах – это одна из самых простых функций, но при этом она очень полезна. Здесь мы подробно рассмотрим, как применяется функция СЧËТЕСЛИ при работе в Google…
Почему функция ВПР (VLOOKUP) не работает в Google таблицах? — Рассматривая синтаксис функции ВПР (VLOOKUP), мы уже отмечали, что в случае, если поиск завершится неудачей, функция возвратит ошибку «#Н/Д» (#N/A). Давайте постараемся вместе попробовать ответить на вопрос: «Почему функция ВПР не…
Полезные примеры использования функции ВПР в Google таблицах — Ранее мы уже рассмотрели, что такое функция поиска ВПР в таблицах Google, а теперь изучим приёмы, которые помогут вам использовать её максимально эффективно. Каждый пример использования функции ВПР в ваших…

Добавить комментарий

Ваш адрес email не будет опубликован.