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

Функция ВПР (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, а теперь изучим приёмы, которые помогут вам использовать её максимально эффективно. Каждый пример использования функции ВПР в ваших…

Как найти данные в Google Таблицах с помощью ВПР

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

В отличие от Microsoft Excel, в Google Таблицах нет мастера VLOOKUP, который поможет вам, поэтому вам придется вводить формулу вручную.

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

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

Первое — это значение ключа поиска, которое вы ищете, а второе — это диапазон ячеек, который вы ищете (например, от A1 до D10). Третий аргумент — это порядковый номер столбца из вашего диапазона для поиска, где первый столбец в вашем диапазоне имеет номер 1, следующий — номер 2 и так далее.

Четвертый аргумент — был ли столбец поиска отсортирован или нет.

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

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

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

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

Использование ВПР на одном листе

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

Электронная таблица Google Sheets с двумя таблицами информации о сотрудниках.

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

Соответствующая формула ВПР для этого: = ВПР (F4, A3: D9, 4, FALSE).

Функция ВПР в Google Таблицах, используемая для сопоставления данных из таблицы A и таблицы B.

Чтобы разбить это, ВПР использует значение ячейки F4 (123) в качестве ключа поиска и выполняет поиск в диапазоне ячеек от A3 до D9. Он возвращает данные из столбца номер 4 в этом диапазоне (столбец D, «День рождения»), и, поскольку мы хотим точного совпадения, последний аргумент — ЛОЖЬ.

В этом случае для идентификатора 123 функция ВПР возвращает дату рождения 19.12.1971 (в формате ДД / ММ / ГГ). Мы расширим этот пример дальше, добавив в таблицу B столбец для фамилий, чтобы он связывал даты рождения с реальными людьми.

Для этого требуется лишь простое изменение формулы. В нашем примере в ячейке H4 = VLOOKUP (F4, A3: D9, 3, FALSE) ищет фамилию, которая соответствует ID-номеру 123.

ВПР в Google Таблицах, возвращая данные из одной таблицы в другую.

Вместо того, чтобы возвращать дату рождения, он возвращает данные из столбца номер 3 («Фамилия»), соответствующие значению идентификатора, расположенному в столбце номер 1 («ID»).

Используйте ВПР для нескольких листов

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

Вместо использования обычного диапазона ячеек, такого как A3: D9, вы можете щелкнуть пустую ячейку и затем ввести: = ВПР (A4, Сотрудники! A3: D9, 4, FALSE).

ВПР в Google Таблицах, возврат данных с одного листа на другой.

Когда вы добавляете имя листа в начало диапазона ячеек (Сотрудники! A3: D9), формула ВПР может использовать данные из отдельного листа при поиске.

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

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

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

Формула VLOOKUP для поиска фамилий с использованием частичного имени: = VLOOKUP (B12, A3: D9, 2, FALSE); значение вашего ключа поиска помещается в ячейку B12.

В приведенном ниже примере «Chr *» в ячейке B12 соответствует фамилии «Компьютерщик» в образце справочной таблицы.

Результаты поиска ВПР по подстановочному знаку фамилии, используемому в Google Таблицах.

Поиск ближайшего совпадения с помощью ВПР

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

Если вы хотите найти ближайшее совпадение со значением, измените последний аргумент ВПР на ИСТИНА. Поскольку этот аргумент указывает, отсортирован ли диапазон или нет, убедитесь, что ваш столбец поиска отсортирован от А до Я, иначе он не будет работать правильно.

В нашей таблице ниже у нас есть список товаров для покупки (от A3 до B9), а также их названия и цены. Они отсортированы по цене от наименьшей к наибольшей. Наш общий бюджет, который мы можем потратить на один предмет, составляет 17 долларов (ячейка D4). Мы использовали формулу VLOOKUP, чтобы найти самый доступный элемент в списке.

Соответствующая формула ВПР для этого примера: = ВПР (D4, A4: B9, 2, ИСТИНА). Поскольку эта формула ВПР настроена на поиск ближайшего соответствия ниже, чем само значение поиска, она может искать только элементы, которые дешевле установленного бюджета в 17 долларов США.

В этом примере самый дешевый предмет стоимостью менее 17 долларов — это сумка, которая стоит 15 долларов, и это предмет, который формула ВПР вернула в качестве результата в D5.

Как использовать ВПР в 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 Таблицах.

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *