ВПР (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, все статьи — в оглавлении нашего канала.
Функция ВПР (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, а теперь изучим приёмы, которые помогут вам использовать её максимально эффективно. Каждый пример использования функции ВПР в ваших…
Функция 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 (#Н/Д). Это потому, что в искомом столбце не нашлись некоторые артикулы, которые есть у нас в базе данных склада. Либо мы неверно написали артикул у себя в базе, либо у поставщика на данный момент просто такого товара нет в наличии и он его не отобразил, либо искомая информация у нас или у поставщика записана в другом формате (вместо числа — текст, или наоборот).
- В первом случае (если мы ошибочно написали SKU у себя) — просто ищем и исправляем ошибку у себя (или у поставщика, если уверены, что ошибку допустил он).
- Во втором (когда у поставщика просто временно нет этого товара и он его просто не указал в файле) — внедряем в конструкцию формулы проверку на ошибку. Дописав в конструкцию: IFERROR (ЕОШИБКА).
- В третьем (когда форматы ячеек отличаются) — внедряем в конструкцию формулы параметр, преобразующий все значения в текст. TEXT (ТЕКСТ).
Давайте внедрим сразу все дополнения в формулу функции VLOOKUP (ВПР), чтобы избавиться от всех ошибок сразу. Формула будет иметь следующий синтаксис:
Получили итоговую конструкцию с пустыми ячейками напротив тех товаров, которых на данный момент в наличии у поставщика нет. Можно формировать заказ товаров и отправлять список обратно поставщику.
Сложный пример поиска данных через ВПР в Google (Excel) таблицах
Рассмотрим довольно сложную ситуацию, в которой принимает участие формула VLOOKUP (ВПР). Она является элементом сложной многомерной формулы и выводит нужный отображаемый результат в указанной ячейке.
Задача стоит следующая: по определенным параметрам из списка статусов, находящегося в специальном столбце и его (списка статусов) положения в строках — нужно отобразить название товара из соседней вкладки со списком товаров, ссылаясь на его артикул:
- формула ищет этот ключевой статус «Facebook» во вкладке склада в определяемом формулой столбце;
- узнает номер строки, на пересечении ячейки (столбца) которой стоит этот статус;
- переходит в нужную вкладку формирования фида для массового импорта товаров в магазин на Facebook;
- считывает артикул в столбце A;
- переходит обратно во вкладку со списком товаров интернет-магазина;
- и динамически отображает название товара, соответствующее считанному артикулу из вкладки фида. Т.е., если статус изменится, отображаться в ячейке будет другой товар со статусом «Facebook», находящийся выше или ниже по строкам в списке товаров в базе данных склада магазина.
В итоге формируется динамический фид данных google таблиц, который передается через API в Facebook и формирует там магазин с товарами, который, в свою очередь, передает данные в Instagram, и там тоже формируется магазин с товарами.
Синтаксический вид этой формулы может изначально озадачить, но я вас уверяю, со временем вы тоже сможете разобраться в сложных составных формулах:
Стоит помнить, что функция ВПР совершает поиск только до первого удовлетворяющего условию результата и только правее столбца с искомыми значениями. Если искомых одинаковых запросов будет несколько — функция дойдет только до первого совпадения, а остальные будет просто игнорировать.
Чтобы отображать данные из выбранного диапазона без позиционной привязки столбцов, можно воспользоваться функцией QUERY.
Формула VLOOKUP (ВПР) довольно сложная в освоении, но очень полезная для повседневной работы. С ее помощью можно найти часть текста, число или значение ячейки. Поделитесь в комментариях, получилось ли у вас воспользоваться данной функцией и при решении каких задач вы ее использовали?
Руководство по функции ВПР (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 Таблицах.
