Функции и формулы

VLOOKUP в Google Таблицах: как выполнить VLOOKUP с другого листа

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

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

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

Что такое функция VLOOKUP в Google Таблицах?

VLOOKUP расшифровывается как Vertical Lookup. Это функция, которая позволяет выполнять вертикальный поиск по Google таблицам. Другими словами, она позволяет искать определенное значение поиска в первом столбце диапазона ячеек.

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

Если это определение сбивает с толку, держитесь. Это станет яснее, когда мы объясним это на примере.

Многие новички в Google Таблицах считают, что VLOOKUP сложно понять и применить.

Однако это не так.

В этом уроке вы увидите, насколько легко это применить. Как только вы освоите его, вы также поймете, насколько мощным он может быть!

Когда использовать VLOOKUP в Google Таблицах

Допустим, у вас есть две таблицы.

Одна содержит информацию о сотрудниках, а другая — их общий объем продаж за определенный месяц.

Обе таблицы имеют общий столбец — идентификатор сотрудника. Более того, идентификатор сотрудника можно рассматривать как ключ-значение или уникальный идентификатор в обеих таблицах.

Допустим, вам нужно знать почасовую ставку для каждого из двух сотрудников, указанных в таблице продаж. Эти ставки уже существуют в таблице сотрудников.

Итак, вам нужно перенести почасовые ставки, соответствующие двум идентификаторам сотрудников, из таблицы сотрудников в таблицу продаж.

Теперь вы можете просто найти два идентификатора сотрудников в таблице сотрудников и скопировать их соответствующие почасовые ставки в таблицу продаж. Но это будет действительно сложно сделать, если вы имеете дело с действительно большими и сложными наборами данных (что на практике обычно и бывает).

Итак, лучший способ обойти это — использовать функцию VLOOKUP в Google Таблицах.

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

Синтаксис VLOOKUP в Google Таблицах

Синтаксис VLOOKUP в таблицах Google следующий:

VLOOKUP(search_key, range, index, [is_sorted])

Здесь,

  • search_key представляет собой уникальный идентификатор или значение ключа, которое вы хотите найти. Это может быть значение или ссылка на ячейку, содержащую значение.
  • диапазон — это диапазон ячеек (в исходной таблице), в котором функция VLOOKUP должна искать. Всегда проверяйте, чтобы этот диапазон содержал столбец, содержащий ключ поиска, а также столбец, содержащий соответствующее значение, которое нужно получить. VLOOKUP всегда выполняет поиск в первом столбце диапазона, чтобы найти ключ поиска.
  • index — это номер столбца в диапазоне, из которого должно быть получено соответствующее значение (в той же строке, что и search_key). Итак, первый столбец в диапазоне имеет индекс 1, второй столбец имеет индекс 2 и так далее.
  • is_sorted — необязательный параметр. Это может быть ИСТИНА или ЛОЖЬ.

Значение FALSE для is_sorted указывает, что первый столбец диапазона не нужно сортировать в порядке возрастания. Итак, функция VLOOKUP ищет точное совпадение search_key.

Если существует более одного значения, которое точно равно search_key, то функция VLOOKUP обращается к первому вхождению search_key.

С другой стороны, значение ИСТИНА означает, что первый столбец должен быть отсортирован в порядке возрастания.

Итак, функция VLOOKUP сначала ищет точное совпадение search_key. Если точное совпадение не найдено, то функция VLOOKUP ищет наиболее близкое совпадение.

По умолчанию для параметра is_sorted установлено значение FALSE.

Это звучит сложно?

На самом деле это не так. Применим приведенный выше синтаксис к нашему примеру.

Как выполнить VLOOKUP с одного листа в Google Таблицах

Вернемся к проблеме еще раз:

Мы хотим найти в таблице сотрудников для идентификаторов сотрудников «E010» и «E014» (ячейки E3 и E4), найти их соответствующие почасовые ставки (из столбца C) и поместить их в столбец F.

Посмотрим, какие аргументы нам нужно будет указать в формуле VLOOKUP для ячейки F3:

  • search_key: мы хотим найти значение «E010» (находится в ячейке E3). Итак, нам нужно указать E3 в качестве параметра search_key.
  • диапазон: мы хотим просматривать ячейки в диапазоне от A3 до C8 из таблицы сотрудников. Поэтому нам нужно указать A3: C8 в качестве параметра диапазона. Обратите внимание, что мы хотим, чтобы этот диапазон оставался неизменным, даже если формула VLOOKUP копируется из ячейки F3 в F4. Поэтому нам нужно заблокировать этот диапазон, нажав кнопку F4 на клавиатуре.
  • index: мы хотим, чтобы функция VLOOKUP извлекала почасовые ставки, соответствующие идентификатору сотрудника в key_value. Почасовая ставка — третий столбец диапазона. Поэтому нам нужно указать число 3 в качестве параметра индекса.
  • is_sorted: очевидно, что нам нужно точное совпадение идентификатора сотрудника, поэтому мы можем либо установить для этого параметра значение FALSE, либо просто не указывать его (поскольку он является необязательным и по умолчанию имеет значение False).

Собирая все это вместе, мы устанавливаем следующую формулу в ячейку F3:

=VLOOKUP(A3,$A$3:$C$8,3,false)

Чтобы вы получили такой результат:

В большинстве случаев. люди используют VLOOKUP для извлечения данных с одного и того же листа. Но вы можете так же легко выполнить VLOOKUP с другого листа (объединив его с функцией IMPORTRANGE).

Как выполнить VLOOKUP с другого листа в той же книге

До сих пор мы видели, что делать, если вы хотите, чтобы функция VLOOKUP извлекала данные с того же листа в Google Таблицах. Но что, если данные, которые вы хотите найти, находятся на другом листе той же книги?

В этом случае мы используем ту же функцию, но с небольшой разницей во втором параметре.

Вот как выполнить VLOOKUP с другой вкладки в Google Таблицах.

Предположим, что таблица сотрудников находится на листе под названием «Сотрудники».

… А таблица продаж находится на отдельном листе под названием «Продажи».

Мы хотим получить доступ к листу «Сотрудники», получить почасовые ставки, соответствующие идентификаторам сотрудника «E010» и «E014», и отобразить их в ячейках B3 и B4 листа «Продажи».

Вот шаги, которые вам необходимо выполнить:

  • Щелкните первую ячейку целевого столбца (в которой должны отображаться результаты VLOOKUP ). В нашем примере щелкните ячейку B3 на листе продаж.
  • Введите: = VLOOKUP, а затем открывающие скобки. В строке формул теперь должно отображаться:
  • Затем выберите ячейку, содержащую значение, которое вы хотите найти. В нашем примере выберите ячейку A3 и поставьте запятую. В строке формул теперь должно отображаться:
  • Для второго параметра выберите вкладку «Сотрудники» (чтобы открыть лист «Сотрудники»). Выберите диапазон ячеек, в котором вы хотите выполнять поиск с помощью функции VLOOKUP . В нашем примере выберите ячейки в диапазоне от A3 до C8. В строке формул теперь должно отображаться:
  • Нажмите клавишу F4 на клавиатуре, чтобы заблокировать контрольный диапазон ячеек. Это сделано для того, чтобы эти ссылки не изменились при копировании формулы в другие строки столбца. В строке формул теперь должно отображаться:
  • Поставьте запятую, а затем индекс столбца, который содержит значения, которые вы хотите получить. В нашем примере мы хотим получить почасовые ставки, которые являются третьим столбцом в диапазоне A3: C8. Итак, набираем цифру 3.
  • Наконец, закройте круглые скобки.
  • Нажмите клавишу возврата и подождите, пока VLOOKUP не завершит обработку.
  • Теперь вы должны увидеть почасовую ставку, соответствующую идентификатору сотрудника «E010» в ячейке B3 вашего листа продаж.
  • Потяните вниз маркер заполнения (расположенный в правом нижнем углу ячейки B3), чтобы скопировать формулу в другие ячейки столбца.
  • Теперь вы должны увидеть почасовые ставки, соответствующие каждому сотруднику, чей идентификатор указан в столбце A таблицы продаж!

Объяснение формулы

Синтаксис формулы VLOOKUP (при обращении к данным на другом листе) следующий:

=VLOOKUP(search_key,{sheet name}!{cell range},index,is_sorted)

Обратите внимание на восклицательный знак «!» между именем листа и диапазоном ячеек.

В нашем примере мы хотели найти данные из диапазона A3: C8, расположенные на листе с именем «Сотрудники». Итак, формула стала:

=VLOOKUP(A2,'Employees'!$A$3:$C$8, 3)

Также обратите внимание, что имя листа заключено в одинарные кавычки. Вам не нужно добавлять одинарные кавычки, если вы используете имена листов по умолчанию, которые предоставляет Google Таблицы, например Лист1, Лист2 и т. Д.

В нашем случае мы дали листу сотрудников пользовательское имя. Поэтому нам потребовалось заключить его название в одинарные кавычки.

Эта формула извлекает значение из третьего столбца листа с именем «Сотрудники» той же книги.

Как выполнить VLOOKUP с другого листа в другой книге Google Таблиц?

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

Опять же, мы используем ту же функцию, но с небольшой разницей во втором параметре.

На этот раз второй параметр будет включать функцию IMPORTRANGE.

IMPORTRANGE в Google Таблицах

Функция IMPORTRANGE используется для импорта значений из ячеек в другой электронной таблице в вашу текущую электронную таблицу.

Синтаксис формулы следующий:

IMPORTRANGE(spreadsheet_key, range_string)

Принимает два параметра:

  • spreadsheet_key : это URL-адрес электронной таблицы, из которой вы хотите импортировать. Его следует указывать в двойных кавычках.
  • range_string : это ссылка на диапазон ячеек, который вы хотите импортировать. Параметр range_string должен содержать имя листа, а также диапазон ячеек, который вам нужен. Итак, если вы хотите импортировать ячейки A3: C8 из рабочего листа с именем «Сотрудники», строка диапазона будет «Сотрудники! A3: C8». Не забудьте указать весь диапазон_строка в двойных кавычках.

Вернемся к нашему примеру.

Предположим, что таблица сотрудников находится в Рабочей книге под названием «Wb1» на листе под названием «Сотрудники», а таблица продаж находится в отдельной Рабочей книге под названием «Wb2» на листе под названием «Продажи».

Нам нужно получить доступ к листу «Сотрудники» (из книги Wb1), получить почасовые ставки, соответствующие идентификаторам сотрудника «E010» и «E014», и отобразить их в ячейках B3 и B4 листа продаж (это рабочая книга Wb2).

Вот шаги, которые вам необходимо выполнить:

  • Щелкните первую ячейку целевого столбца (в которой должны отображаться результаты VLOOKUP). В нашем примере щелкните ячейку B3 на листе продаж рабочей книги «Wb1».
  • Введите: = VLOOKUP, а затем открывающие скобки.
  • Затем выберите ячейку, содержащую значение, которое вы хотите найти. В нашем примере выберите ячейку A3 и поставьте запятую.
  • Для второго параметра введите функцию IMPORTRANGE с открывающей круглой скобкой.
  • Откройте книгу, которую вы хотите найти («Wb1»), и выберите вкладку листа («Сотрудники» в нашем примере).
  • Скопируйте URL-адрес этого рабочего листа из адресной строки браузера.
  • Вернитесь к своей текущей книге (‘Wb2’) и вставьте URL-адрес в конец формулы в строке формул (не забудьте заключить URL-адрес в двойные кавычки). Панель формул должна теперь выглядеть так:
  • Затем добавьте запятую, за которой следует имя исходного листа (в нашем примере «Сотрудники»).
  • Добавьте восклицательный знак (!) И введите диапазон ячеек, который вы хотите найти в исходной таблице. Не забудьте сделать эти ссылки на ячейки абсолютными, добавив знаки доллара.
  • Заключите весь параметр (имя исходного листа, восклицательный знак и имя листа) в двойные кавычки.
  • Поставьте запятую, а затем закрывающие круглые скобки (чтобы закрыть функцию IMPORTRANGE).
  • Поместите запятую, а затем индекс столбца, содержащего значения, которые вы хотите получить. В нашем примере мы хотим получить почасовые ставки, которые являются третьим столбцом в диапазоне A3: C8. Итак, мы вводим число 3. Теперь в строке формул должно отображаться:
  • Наконец, закройте круглые скобки для функции VLOOKUP. В строке формул теперь должно отображаться:
  • Нажмите клавишу возврата и подождите, пока VLOOKUP не завершит обработку.
  • Вам будет предложено разрешить доступ к листу «Сотрудники» для подключения. Нажмите кнопку «Разрешить доступ».
  • Теперь вы должны увидеть почасовую ставку, соответствующую идентификатору сотрудника «E010» в ячейке B3 вашего листа продаж.
  • Потяните вниз маркер заполнения (расположенный в правом нижнем углу ячейки B3), чтобы скопировать формулу в другие ячейки столбца.
  • Теперь вы должны увидеть почасовые ставки, соответствующие каждому сотруднику, чей идентификатор указан в столбце A таблицы продаж!

Объяснение формулы

Синтаксис формулы VLOOKUP (при обращении к данным в другой книге) следующий:

=vlookup(search_key, Importrange(“{sheetsURL}”,“{sheet name}!{cell range}”),index,is_sorted)

Обратите внимание на название листа, восклицательный знак «!» и диапазон ячеек заключены в двойные кавычки.

В нашем примере мы хотели найти данные из диапазона A3: C8, расположенного на листе с именем «Сотрудники» книги «Wb1». Итак, формула стала:

=VLOOKUP(A3,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1nmaT8ggc7no8NVT9U4VXrAfvcBvvvzeJNraHx365MHc/edit#gid=0","Employees!$A$3:$C$8"),3)

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

В этом уроке мы показали вам, как выполнять VLOOKUP из:

  • тот же лист
  • другой лист в той же книге
  • другой лист в другой книге.

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

Это особенно полезно, потому что любое изменение в исходном листе автоматически обновляется в связанных ячейках, что позволяет очень легко быстро ссылаться на другой лист в Google Таблицах.

Это избавит вас от лишних хлопот и позволит вам сосредоточиться на лучших способах использования функции VLOOKUP в Google Таблицах.

Вот как вы можете выполнять VLOOKUP с того же листа или с других листов в Google Таблицах.

Надеюсь, вы нашли этот совет по Google Таблицам полезным!

Вас могут заинтересовать следующие статьи: