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

Оглавление:

2024 Автор: Abigail Brown | [email protected]. Последнее изменение: 2023-12-17 06:59

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

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

Использование синтаксиса формулы ВПР

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

ВПР(ВАШ ТЕРМИН ПОИСКА, ДИАПАЗОН ЯЧЕЙ, ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ, СОСТОЯНИЕ СОРТИРОВКИ)

Давайте рассмотрим каждый из них по очереди.

ВАШ ТЕРМИН ПОИСКА: В документации это называется search_key, но это термин, который вы хотите найти. Это может быть число или бит текста (например, строка). Просто убедитесь, что это текст, который вы заключаете в кавычки.
ЯЧЕЙКА ДИАПАЗОН: Называется просто диапазоном, вы используете это, чтобы выбрать, какие ячейки в вашей электронной таблице вы будете искать. Предположительно, это будет прямоугольная область с более чем большим количеством столбцов и строк, хотя формула будет работать только с одной строкой и двумя столбцами.
ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ: Значение, которое вы хотите вернуть, также называемое индексом, является наиболее важной частью функции и самой сложной для понимания. Это номер столбца со значением, которое вы хотите вернуть относительно первого столбца. Другими словами, если первый (искомый) столбец является столбцом 1, это номер столбца, для которого вы хотите вернуть значение из той же строки.
SORTED STATE: В других источниках это обозначено как is_sorted, и это значение true/false указывает на то, отсортирован ли искомый столбец (опять же, столбец 1). Это важно при поиске числовых значений. Если для этого значения установлено значение FALSE, то результатом будет первая идеально совпадающая строка. Если в столбце 1 нет значений, соответствующих поисковому запросу, вы получите сообщение об ошибке. Однако, если для этого параметра установлено значение TRUE, результатом будет первое значение, меньшее или равное поисковому запросу. Если подходящих нет, вы снова получите сообщение об ошибке.

Функция ВПР на практике

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

=ВПР("Ноутбук", A3:B9, 3, false)

Это возвращает цену, хранящуюся в столбце 3 в этом примере, который является столбцом на два правее столбца с целями поиска.

Давайте рассмотрим это шаг за шагом, чтобы подробно объяснить процесс.

Поместите курсор в ячейку, где вы хотите, чтобы результат появился. В этом примере это B11 (метка для этого находится в A11, «Цена ноутбука», хотя это не входит в формулу).
Начните формулу со знака равенства (=), затем введите функцию. Как уже упоминалось, это будет простая формула, состоящая только из этой функции. В этом случае мы используем формулу:
```
=ВПР("Ноутбук", A3:C9, 3, false)



Image
```
Нажмите Enter. Сама формула исчезнет из электронной таблицы (хотя она по-прежнему будет отображаться в строке формул выше), а вместо нее отобразится результат.
В примере формула просматривает диапазон от A3 до C9 Затем она ищет строку, содержащую «Ноутбук». Затем он ищет третий столбец в диапазоне (опять же, он включает первый столбец) и возвращает результат, который равен $1, 199 Это должен быть тот результат, который вам нужен, но если он выглядит странно, дважды проверьте введенные вами параметры, чтобы убедиться, что они верны (особенно если вы скопировали и вставили формулу из другой ячейки, потому что диапазон ячеек может измениться как результат).

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

Использование функции ВПР в разных таблицах Google

Что касается параметра CELL RANGE, вы можете выполнять ВПР не только в ячейках текущего листа, но и в других листах рабочей книги. Используйте следующую нотацию, чтобы указать диапазон ячеек на другом листе в текущей книге:

=ВПР("Ноутбук", 'Имя листа в одинарных кавычках, если более одного слова'!A1:B9, 3, false)

Вы даже можете получить доступ к ячейкам в совершенно другой книге Sheets, но вам нужно использовать функцию IMPORTRANGE. Для этого требуется два параметра: URL-адрес рабочей книги Sheets, которую вы хотите использовать, и диапазон ячеек, включая имя листа, как показано выше. Функция, содержащая все эти элементы, может выглядеть так:

=ВПР("Ноутбук", IMPORTRANGE("https://docs.google.com/spreadsheets/d/aLlThEnUmBeRsAndlEtTeRs/", "Лист1!B7:D42"), 3, false)

В этом примере вложенная функция (то есть результат функции IMPORTRANGE) становится одним из параметров функции ВПР.

Советы по использованию функции ВПР

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

Во-первых, заключите условия текстового поиска в кавычки. В противном случае Google Таблицы сочтут, что это именованный диапазон, и выдадут ошибку, если не смогут его найти.
Если вы копируете и вставляете одну из этих формул, по-прежнему применяются обычные правила обновления значения диапазона ячеек. Другими словами, если у вас есть фиксированный список данных, убедитесь, что вы привязываете диапазон ячеек к знаку доллара (например, «$A$2:$B$8» вместо «A2:B8»). В противном случае формула будет смещена в зависимости от того, куда вы их вставите (обратите внимание на скриншот в начале раздела, где номера строк сдвинуты на единицу).
Если вы сортируете свой список, не забудьте вернуться к поиску, если вы сортируете его снова. Перетасовка строк может дать неожиданные результаты, если вы установите для формулы состояние сортировки TRUE..