VLOOKUP, или «Вертикальный поиск», — это полезная функция, которая выходит за рамки использования ваших электронных таблиц в качестве прославленных калькуляторов или списков дел и выполняет некоторый реальный анализ данных. В частности, VLOOKUP выполняет поиск значений в ячейках по столбцам, а затем возвращает соответствующее значение из той же строки. Знание того, что означает «соответствующий» в этом контексте, является ключом к пониманию VLOOKUP, поэтому давайте углубимся и рассмотрим использование VLOOKUP в Google Sheets .
Эти инструкции распространяются на Google Sheets на всех платформах.
Использование синтаксиса формулы VLOOKUP
VLOOKUP — это функция, которую вы используете в формуле , хотя самая простая формула — просто использовать ее самостоятельно. Вам нужно предоставить пару частей информации для функции, разделенных запятыми, следующим образом:
VLOOKUP (ваш срок поиска, диапазон ячеек, возвращаемое значение, отсортированное состояние)
Давайте посмотрим на каждый из них по очереди.
- СРОК ПОИСКА : в документации это называется search_key , но это термин, который вы хотите найти. Это может быть число или бит текста (то есть строка). Просто убедитесь, что это текст, который вы заключаете в кавычки.
- CELL RANGE : Именуется просто диапазон , можно использовать эту функцию, чтобы выбрать , какие ячейки в электронной таблице вы будете искать через. Предположительно, это будет прямоугольная область с большим количеством столбцов и строк, хотя формула будет работать всего с одной строкой и двумя столбцами.
- ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ : значение, которое вы хотите вернуть, также называемое индексом , является самой важной частью функции и самой сложной для понимания. Это номер столбца со значением, которое вы хотите вернуть относительно первого столбца. Другими словами, если первый (искомый) столбец — это столбец 1, это номер столбца, для которого вы хотите вернуть значение из той же строки.
- СОСТОЯНИЕ СОРТИРОВКИ : это обозначается как is_sorted в других источниках, и это значение true / false в зависимости от того, сортируется ли искомый столбец (опять же, столбец 1). Это важно при поиске числовых значений. Если это значение установлено в FALSE , то результат будет для первой идеально совпадающей строки . Если в столбце 1 нет значений, соответствующих поисковому запросу, вы получите сообщение об ошибке. Однако, если для этого параметра установлено значение TRUE , результатом будет первое значение, которое меньше или равно поисковому запросу. Если нет ни одного совпадения, вы снова получите ошибку.
Функция VLOOKUP на практике
Предположим, у вас есть короткий список продуктов, с каждым из которых связана цена. Затем, если вы хотите заполнить ячейку ценой ноутбука, вы должны использовать следующую формулу:
= ВПР ( "Laptop", A3: B9,3, ложь)
Это возвращает цену, сохраненную в столбце 3 в этом примере, который является вторым столбцом справа от столбца с целями поиска.
Давайте посмотрим на этот шаг за шагом, чтобы объяснить процесс подробно.
-
Сначала поместите курсор в ячейку, где вы хотите, чтобы результат появился. В данном примере это B11 (обозначение для него на A11, «Цена ноутбука», хотя это не входит в формулу).
-
Затем запустите формулу со знаком равенства ( = ), затем введите функцию. Как уже упоминалось, это будет простая формула, которая состоит только из этой функции. В этом случае мы используем формулу:
= ВПР ( "Laptop", A3: C9,3, ложь)
-
Как только вы закончите, нажмите Enter . Сама формула исчезнет в электронной таблице (хотя она все равно будет отображаться в строке формул выше), и вместо этого будет показан результат.
-
В примере формула смотрит на диапазон от A3 до C9 . Затем он ищет строку, содержащую «Ноутбук». Затем он ищет третий столбец в диапазоне (опять же, он включает в себя первый столбец) и возвращает результат, который составляет $ 1199 . Это должен быть желаемый результат, но если это выглядит странно, дважды проверьте введенные параметры, чтобы убедиться, что они правильные (особенно если вы скопировали и вставили формулу из другой ячейки, поскольку диапазон ячеек может изменяться как результат).
Когда вы поймете, как выбрать диапазон и его относительное возвращаемое значение, вы увидите, как эта удобная функция позволяет находить значения даже в очень больших наборах данных.
Использование VLOOKUP в разных листах Google
Что касается параметра CELL RANGE, вы можете выполнять VLOOKUP не только в ячейках текущего листа, но и в других листах рабочей книги. Используйте следующие обозначения, чтобы указать диапазон ячеек на другом листе в текущей рабочей книге:
= VLOOKUP («Портативный компьютер», «Имя листа в одинарных кавычках, если более одного слова»! A1: B9,3, false)
Вы можете даже попасть в ячейки совершенно другой книги Sheets, но вам нужно использовать функцию IMPORTRANGE . Это принимает два параметра: URL-адрес рабочей книги Sheets, которую вы хотите использовать, и диапазон ячеек, включая имя Sheet, как показано выше. Функция, содержащая все эти элементы, может выглядеть так:
= ВПР ( "Laptop", IMPORTRANGE ( "https://docs.google.com/spreadsheets/d/aLlThEnUmBeRsAnDlEtTeRs/", "Лист1 B7: D42"), 3, ложь)
Обратите внимание, что в этом примере вложенная функция (т. Е. Результат функции IMPORTRANGE) становится одним из параметров функции VLOOKUP.
Советы по использованию функции VLOOKUP
Чтобы убедиться, что вы получите правильные результаты из своей формулы, имейте в виду следующие моменты.
- Сначала заключите текстовые поисковые термины в кавычки. В противном случае Google Sheets определит, что это именованный диапазон , и выдаст ошибку, если не сможет его найти.
- Если вы копируете и вставляете одну из этих формул, все еще применяются обычные правила обновления значения диапазона ячеек. Другими словами, если у вас есть фиксированный список данных, убедитесь, что вы привязали диапазон ячеек со знаком доллара (т.е. «$ A $ 2: $ B $ 8» вместо «A2: B8»). В противном случае формула будет смещена в зависимости от того, где вы их вставили (обратите внимание на скриншот в начале раздела, где номера строк выключены на единицу).
- Если вы сортируете свой список, не забывайте повторно посещать ваши поиски в случае, если вы повторно сортируете его. Перестановка строк может дать неожиданные результаты, если вы установите для отсортированного состояния формулы значение ИСТИНА .