ВПР (VLOOKUP) по нескольким условиям
Друзья, нас очень часто спрашивают — а как все-таки сделать ВПР по нескольким условиям. Давайте разбираться.
Объединяем условия и диапазоны для поиска
Как работает функция ВПР? Вы задаете условие поиска и функция ищет его в первом столбце выбранного диапазона данных, в случае успеха выводится тот столбец из таблицы, который вы указали.
Наша таблица — ниже, из нее нужно отобрать ячейку по двум условиям, “а” и “б”.
Для этого в функции ВПР нужно объединить оба условия поиска и диапазоны поиска. Объединим и выведем для наглядности.
Добавляем все в ВПР. Формула массива (arrayformula) нужна, чтобы “на лету” объединить столбцы.
Функция уже работает но в ней всего один столбец данных, добавляем к диапазону тот столбец, который будем выводить, C2:C3.
Конструкцией ниже мы создали массив из двух столбцов (объединенного A2:A3 и B2:B3 и C2:C3), обратная косая черта (“\”) — отделяет столбцы, фигурные скобки <> — создают массив.
Итак, формула:
Поиск по двум условиям, FILTER
ВПР по нескольким условиям возможен, но непрост, особенно для новичков, плюс из-за формулы массива это не самая быстрая формула.
Тоже самое можно сделать с помощью функции FILTER:
Если значений большего одного
FILTER, в отличии от ВПР выводит все значения, которые отвечают нашему условию, чтобы оставить одно (например верхнее), добавляем INDEX:
=INDEX(FILTER(C2:C4;A2:A4=E2;B2:B4=F2);1)
Мы много писали и про ВПР и про FILTER, все статьи — в оглавлении нашего канала.
9 функций Google Таблиц, которые пригодятся SEO-специалисту
Google Spreadsheets (Таблицы) — это удобный и бесплатный сервис для работы с таблицами в онлайн и офлайн режиме. Инструмент обладает широким функционалом, благодаря чему пользуется популярностью у специалистов различных сфер, в том числе SEO. С помощью Google Таблиц можно быстро и удобно работать с большим количеством данных, автоматизировать рабочие процессы, собирать информацию с сайтов и многое другое — главное знать, какие формулы использовать для этого.
И в этом посте я собрала 9 функций в Google Таблицах, которые помогут SEO-специалистам (и не только) автоматизировать и упростить повседневные задачи.
- 1. REGEXEXTRACT
- 2. VLOOKUP
- 3. IMPORTXML
- 4. IFERROR
- 5. QUERY
- 6. SPLIT
- 7. UNIQUE
- 8. IMPORTRANGE
- 9. SPARKLINE
Обратите внимание , что в зависимости от региональных настроек Google Таблиц может быть разный разделитель, в русскоязычном интерфейсе — это точка запятой, в англоязычной версии — запятая.
1. REGEXEXTRACT
Эта функция спасает, когда вам нужно вытащить из ячейки с текстом только один фрагмент, например, из URL-адресов извлечь только доменное имя.
В данном случае text — это будет название ячейки, а regular_expression (регулярное выражение) будет следующим:
Чтобы извлечь домены из других ячеек, переместите курсор в правый угол ячейки с формулой и потяните вниз. И вуаля — всего за несколько минут мы извлекли нужные фрагменты.
Также можно воспользоваться функцией =ARRAYFORMULA, которая работает с массивом данных. Чтобы её использовать, оберните свою формулу в неё:
Чтобы извлечь конкретный текст, добавьте в формулу этот текст, например:
Вы можете применять и другие регулярные выражения → эта шпаргалка по синтаксису вам поможет.
2. VLOOKUP
VLOOKUP (ВПР) для Google Таблиц — это одна из самых популярных функций среди SEO-специалистов. Она выполняет поиск в строках по вертикали (поэтому и ВПР — вертикальный просмотр) и возвращает значение из определённой ячейки в указанном диапазоне. С помощью VLOOKUP можно переносить и объединять данные из одной таблицы в другую, а также проверять наличие значения в других наборах данных.
- запрос — критерий, по которому производится поиск;
- диапазон — диапазон данных;
- индекс — номер столбца, из которого будет извлекаться нужное значение. Важно, чтобы номер столбца не выходил за рамки диапазона;
- отсортировано — показывает, отсортированные ли значения в столбце, по которому выполняется поиск. Чаще всего указывается значение ЛОЖЬ — это означает точное совпадение, поэтому в формуле ставится «0».
Например, у вас есть таблица с двумя вкладками, на которых есть данные. Вам необходимо перенести данные из второй вкладки в первую.
Чтобы перенести содержимое ячеек в столбце «Заголовок» я буду использовать формулу:
Для переноса данных из столбца «Описание» будет использоваться следующая формула:
Также есть аналогичная функция, только для горизонтального поиска (HLOOKUP), если вдруг у вас всё повернётся на 90 градусов.
3. IMPORTXML
IMPORTXML — это функция, которая позволяет импортировать данные формата XML, HTML, RSS, CSV и других. С её помощью вы можете парсить данные с сайтов не покидая Google Таблиц, например, извлекать метаданные или контактные данные со страниц.
Рассмотрим на примере. Допустим, нам нужно извлечь заголовок страницы. Тогда формула будет выглядеть так:
Если у вас уже есть список URL в таблице, вместо значения URL впишите название первой ячейки, в которой находится этот URL, и протяните вниз.
Также эту формулу можно использовать для извлечения абсолютно разных данных, главное — подобрать XPath. Например, чтобы узнать количество внешних ссылок со страницы, на место xpath_запрос подставьте «//a».
А чтобы быстро проверить коды ответа сервера страниц, подставьте xpath «//div[@class=’ip-entry’]//big» и введите URL онлайн-сервиса, который определяет код ответа сервера страниц. Формула будет выглядеть так:
Да, можно и коротенькую функцию написать для этого, но об этом в другом посте 😉
4. IFERROR
Иногда формулы в Google Таблицах возвращают ошибку, и здесь пригодится функция IFERROR (ЕСЛИОШИБКА), которая позволяет задать значение по умолчанию, если значение содержит ошибку.
Например, у нас есть лист с данными о просмотрах страниц с разных стран, и мы хотим объединить их в одну таблицу, чтобы посчитать общую сумму просмотров по каждому URL. Однако если какая-то из страниц не получала показы, то при суммировании формула СУММ будет отдавать ошибку.
Поэтому в случае отсутствия данных мы заменим значение на 0, применив формулу с функциями ВПР и ЕСЛИОШИБКА:
5. QUERY
QUERY — функция, позволяющая запрашивать нужные данные из таблицы, по структуре запросов немного похожая на SQL.
- данные — это диапазон, по которому будет выполняться запрос;
- запрос — запрос на языке API визуализации Google;
- заголовки — количество строк с заголовками в верхней части раздела данных. Является необязательным аргументом.
Рассмотрим пример. У нас есть таблица с результатами данных сканирования из Netpeak Spider.
Нам необходимо из неё вытащить только те страницы, у которых описания длиной менее 50 символов или заголовки — менее 40. Для этого применяем формулу:
У этой функции много других возможностей, подробнее об этом рассказали наши друзья — специалисты из агентства Netpeak в статье «Всемогущая функция Query — подробное руководство».
6. SPLIT
Функция SPLIT выводит разделённый символами текст в отдельные ячейки.
Она помогает при работе с массивными данными, так как позволяет разбивать их по отдельным столбцам. Разделителем вы можете выбрать любой символ.
Представим, что у нас есть список контактов с почтой и телефоном, и они находятся в одной ячейке.
Чтобы распределить почту и номер телефон по разным ячейкам, примените формулу:
Обратите внимание, что в скобках должен быть пробел.
Ну а соединить можно или с помощью JOIN или CONCATENATE, в зависимости от того, насколько у вас интересная задача.
7. UNIQUE
UNIQUE помогает избавиться от дубликатов (повторений) и возвращает уникальные строки в заданном диапазоне данных.
Пример. У нас есть список ключевых слов, и он содержит повторения.
Чтобы их удалить, вписываем формулу :
Согласитесь, довольно простая формула, но она значительно упрощает рабочий процесс, а на что способна COUNTUNIQUE, я боюсь даже говорить 🤭
8. IMPORTRANGE
IMPORTRANGE позволяет импортировать данные из любой другой Google Таблицы, причём необязательно эта таблица должна быть именно на вашем Google Диске, необходимо просто иметь доступ.
Пример покажу сразу на скрине, тут всё понятно и без слов 😃
9. SPARKLINE
С помощью функции SPARKLINE можно создавать мини-диаграммы прямо внутри ячейки.
- данные — диапазон данных, на основе которых необходимо построить диаграмму.
- опции — диапазон с дополнительными параметрами, которые используются для настройки диаграмм. Диапазон должен составлять две ячейки, в одно должно содержаться название параметра, в другой — его значение. Например, можно задать столбчатый тип диаграммы и максимальное количество столбцов — 10.
Все опции для настройки диаграмм вы найдёте в справке Google.
Теперь пример. У нас есть данные по количеству посещений сайта за определённый период. Мы хотим сделать миниатюрную диаграмму, чтобы посмотреть динамику. Для этого вписываем параметр и значение, а в отдельной ячейке, где будет находиться график, применяем формулу:
Вот что получилось:
Подводим итоги
Google Таблицы — это мощный инструмент, который способен во много раз облегчить работу специалистов многих сфер. В этой статье мы рассмотрели лишь небольшую часть функций Google Таблиц. Если вы хотите ещё одну подборку, но уже с другими полезными функциями, пишите в комментариях и предлагайте свои идеи 😊
ВПР в Excel и Гугл Таблицах – функция VLOOKUP
ВПР позволяет быстро подтаскивать данные с одного листа на другой в рамках одной таблицы. Эту функцию еще часто называют VLOOKUP, потому что в английском она так и называется. ВПР в Excel – очень мощная функция, которая может сэкономить вам много времени. Но, как и любая функция Экселя, ВПР обладает крайне непонятным синтаксисом и работает, мягко говоря, с особенностями. Ниже – о том, как ВПР в Эксель реализована, как ее правильно использовать и как эта же функция выглядит в таблицах Гугла.
ВПР в Excel
Алгоритм работы функции:
- Указываем, что искать (абсолютное значение или что-то в ячейке).
- Указываем, где искать (некий диапазон в другой таблице).
- Указываем, какое значение возвращать. Это – самая сильная сторона ВПР. Когда функция находит совпадение (например, фамилию работника), она возвращает не саму фамилию, а значение, которое находится в той же строке. То есть мы можем вернуть зарплату этого работника, его фамилию, дату рождения – в общем, все, что захотим, при условии, что все данные в одной строке принадлежат одному работнику (данные упорядочены).
- Указываем точность совпадения. Эксель позволяет вернуть как точное совпадение, так и частичное. Рекомендуем всегда пользоваться точным совпадением, потому что «частичное совпадение» – вещь весьма расплывчатая, Эксель может взять вообще не те данные, которые вам на самом деле нужны. Как-то повлиять на алгоритм выбора простыми методами нельзя.
На практике это выглядит вот так: ВПР(B2;’Дневная потребность’!$A$3:$B$12;2;0), где:
- B2 – что мы будем искать.
- ‘Дневная потребность’!$A$3:$B$12 – где мы будем искать. Указываем таблицу на другом листе.
- 2 – из какого столбца мы будем возвращать данные, когда найдем соответствие.
- 0 – используем точное соответствие (1 – использовать приблизительное, не рекомендуется).
Примеры
Разберем несколько задач с использованием функции ВПР в Excel.
1. Поиск неизвестного в общей таблице.
Это простейшая задача научит быстро обнаруживать необходимые данные в больших таблицах.
Исходная информация:
Есть таблица в Excel с перечнем лекарственных препаратов, их производителем и стоимостью.
Задача: найти стоимость препарата Хепилор.
Решение состоит в последовательности следующих действий:
- Выбор критерия: в ячейку В12 вводим название Лекарственного препарата «Хепилор».
- Выбор массива: выбираем диапазон начала и конца таблицы, где должен осуществляться поиск: В3:D10.
- Выбор номера столбца: указываем номер столбца, из которого должна быть считана информация в одной строке с названием препарата. В нашем примере это 3, т. к. столбец №1 расположен вне области нужного нам диапазона.
- Ставим функцию «0» или «Ложь».
Аналогично можно произвести поиск производителя Хепилора. Для этого потребуется заменить номер столбца, где расположены необходимые данные, т. е. 3 на 2.
2. Пример расчета неизвестного показателя из исходных данных.
Предположим, что у нас есть приют для котиков. Нам нужно посчитать, сколько пакетиков корма нам нужно купить на завтра и сколько денег на это нужно. У нас есть 2 таблицы на разных страницах:
В первой таблице указано имя кота и его вес, сюда же мы будем вносить количество пакетиков, стоимость и итоговые значения.
В строке 2 указаны номера колонок – колонки обязательно нужно нумеровать, без этого функция работать не будет.
Итак, щелкаем на первое пустое поле в основной таблице, нажимаем на значок создания функции и выбираем ВПР.
Нам нужно заполнить аргументы функции:
Искомое значение – это то значение, по которому мы будем искать. В нашем случае это «Вес кота», поэтому указываем B2. Таблица – это место, где мы будем искать. Нужно выбрать всю таблицу за исключением заголовка и номеров колонок.
Кроме того, нужно зафиксировать значения диапазона таблицы, чтобы она не «поехала», когда мы будем распространять формулу на другие ячейки основной таблицы. Для этого выделяем диапазон в конструкторе формулы и жмем F4.
Далее указываем номер столбца, из которого нужно брать данные. В нашем случае – второй столбец, поскольку в нем указано количество пакетиков. В «Интервальный просмотр» ставим 0, чтобы искать по точному соответствию.
Применяем – получаем результат. Растягиваем его на остальные ячейки.
Пакетик корма стоит 60 рублей, поэтому высчитываем стоимость как D2=C2*60 (и растягиваем на остальные пустые ячейки), в C9 пишем =СУММ(C2:C8), в D10 пишем =СУММ(D2:D8).
Получаем 14.5 пакетиков в день на сумму 870 рублей, по факту придется купить 15 на сумму 900 рублей, ибо половину пакетика никто не продаст.
3. Комбинирование таблиц с ВПР.
Исходные данные: имеем 2 таблицы.
«Отчет о количестве товара» и «Отчет о цене за единицу товара».
Задача: объединить данные двух таблиц.
Порядок действий:
Выбираем ячейку для вставления данных (D3) и пишем функцию: ВПР (В3;F3:G14;2;0), где:
- выбор критерия:В3;
- выбор диапазона: F3:G14;
- № столбца: 2;
- стандартно: «0» или «Ложь».
Алгоритм решения:
- Поиск совпадений с верхней ячейки первого столбца.
- Поиск соответствия установленному критерию сверху вниз.
- После того, как найден Хепилор, производится отсчет столбцов вправо.
- ВПР выдает искомое значение, в нашем случае это цена – 86,90.
Чтобы в столбец D первой таблицы вставить данные не по одной строке, а в целом, нужно скопировать функцию до последнего критерия. Но, для избежания «съезжания» массива вниз, нужно использовать абсолютные ссылки для диапазона в ячейке D3. Для этого нужно выделить диапазон F3:G14 и нажать клавишу F4, далее завершить копирование формулы.
Итоговая таблица будет такая:
ВПР в Гугл Таблицах
У Гугла все работает абсолютно так же. Функция называется VLOOKUP, но вы можете написать ВПР, и Гугл автоматически поменяет название после того, как вы примените функцию. Единственная особенность – формулу нужно вводить непосредственно в поле, конструктор недоступен.
Примеры с пошаговыми инструкциями
1. Пример. Осуществляем поиск данных из списка.
Дана таблица с именами и оценками учащихся.
Требуется быстро найти оценку конкретного ученика, например, Martha.
Алгоритм: =VLOOKUP(E2,$A$2:$B$10,2,False)
При введении в ячейку Е2 любого имени, в Е3 будет отображаться значения его оценки.
Это очень удобно, когда исходных данных много, таблицы большие, а информация нужна срочно.
2. Пример.
Создаем 2 таблицы – основную и ту, в которой мы будем искать информацию.
Выбираем первую ячейку, пишем =ВПР(. После этого Гугл предлагает нам ввести или выбрать данные. Данные разделяются знаком ;.
Чтобы указать диапазон таблицы, переходим на нужную страницу и выделяем таблицу. Выделили – не забудьте нажать на F4, чтобы зафиксировать диапазон.
Вводим номер столбца и 0, чтобы искать точное совпадение. Распространяем формулу на остальные ячейки, считаем цену для каждого кота и общие суммы.
Возможные ошибки новичков
- Не фиксирован диапазон. Если вы не зафиксируете диапазон при указании границ таблицы с данными, в первой ячейке формула применится верно, а вот в остальных будут неправильные значения, потому что диапазон таблицы «поедет» вслед за смещением. Поэтому не забывайте поставить $ перед каждой координатой или просто выберите все и нажмите F4.
- Неправильно выбран диапазон таблицы. В диапазоне нужно указывать всю таблицу, за исключением заголовка и номеров столбцов. Если у вас выскакивает ошибка, связанная с неправильной ссылкой – поищите проблему в диапазоне таблицы.
- Поиск происходит не по первому столбцу таблицы. В этом случае результат может быть непредсказуем – всегда ищите совпадение именно в первом столбце.
Что почитать по теме
Какой результат выдаст функция, если найдет несколько вхождений в таблице, в которой мы ищем данные?
Функция вернет результат из первого найденного вхождения.
Можно ли писать внутри функции формулы?
Да, вы можете как написать формулу внутри одного из параметров функции, так и передать результаты работы функции в формулу. Например, =ECЛИ(ВПР(“Иванов”;’сотрудники’!$B$3:$B$203;3;0)=1;”Есть”;”Не найден”) будет писать «Есть», если такой сотрудник есть в базе (и в специальной колонке ему присвоено значение 1), и «Не найден» в противном случае.
Функция ВПР (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 мы используем:
1 аргумент – ссылка на файл Google таблиц. Ее можно получить из адресной строки браузера, открыв эту таблицу в новом окне.
2 аргумент – обычная ссылка на диапазон данных, которая обязательно включает в себя наименование листа (в нашем примере – лист “1”).
Все остальные действия ничем не отличаются от того, что мы с вами уже рассмотрели.
Итак, мы познакомились с синтаксисом функции ВПР, попытались понять логику ее работы и научились составлять формулы.
В последующих материалах мы рассмотрим наиболее типичные ошибки, по причине которых функция не работает, а также разберём несколько примеров эффективной работы.
Другие примеры использования функций Google таблиц:
Функция ЕСЛИ в Google таблице – примеры использования — Функция ЕСЛИ в Google таблицах – это одна из самых простых функций, но при этом она очень полезна. Она относится к десятку функций, которые нужно обязательно знать и уметь применять.…
Как использовать функцию СЧËТЕСЛИ в Google таблицах — Функция СЧЕТËСЛИ в Google таблицах – это одна из самых простых функций, но при этом она очень полезна. Здесь мы подробно рассмотрим, как применяется функция СЧËТЕСЛИ при работе в Google…
Почему функция ВПР (VLOOKUP) не работает в Google таблицах? — Рассматривая синтаксис функции ВПР (VLOOKUP), мы уже отмечали, что в случае, если поиск завершится неудачей, функция возвратит ошибку «#Н/Д» (#N/A). Давайте постараемся вместе попробовать ответить на вопрос: «Почему функция ВПР не…
Полезные примеры использования функции ВПР в Google таблицах — Ранее мы уже рассмотрели, что такое функция поиска ВПР в таблицах Google, а теперь изучим приёмы, которые помогут вам использовать её максимально эффективно. Каждый пример использования функции ВПР в ваших…
