Содержание:
Когда вам нужно обратиться к данным из нескольких листов или таблиц, их физический поиск может быть утомительным. Google Таблицы помогают решить эту проблему с помощью функции VLOOKUP.
Используя функцию VLOOKUP или вертикального просмотра, вы можете заставить Google Таблицы автоматически искать значения и извлекать совпадающие данные из другой таблицы. Эта таблица может находиться на том же листе или на другом листе.
Единственная проблема заключается в том, что синтаксис функции VLOOKUP позволяет просматривать только один столбец за раз.
Поэтому, если у вас есть несколько критериев или вы хотите проверить несколько взаимосвязанных столбцов одновременно, вам нужно найти способ обойти это ограничение. Это можно сделать, объединив функцию VLOOKUP с другими функциями Google Таблиц.
Синтаксис обычной функции VLOOKUP
Как правило, функция VLOOKUP имеет следующий синтаксис:
=VLOOKUP(search_key, range, index, [is_sorted])
Здесь,
- search_key — это ключевое значение, которое вы хотите найти в другой таблице. Это может быть значение или ссылка на ячейку, содержащую значение.
- range — это диапазон ячеек (в исходной таблице), в котором вы хотите, чтобы функция VLOOKUP выполняла поиск search_key. Не забудьте убедиться, что этот диапазон содержит столбец с search_key в качестве первого столбца. Также убедитесь, что он содержит столбец с вашим целевым значением (которое вы хотите получить).
- index — это номер столбца в диапазоне, содержащий целевое значение, которое вы хотите получить. Обратите внимание, что первый столбец в диапазоне имеет индекс 1, второй столбец имеет индекс 2 и так далее.
- is_sorted — необязательный параметр. Это может быть ИСТИНА или ЛОЖЬ. Он указывает, нужно ли сортировать столбец поиска.
Например, на следующем изображении у нас есть две таблицы. Один содержит личную информацию о сотрудниках, а другой — их общий объем продаж за определенный месяц.
Чтобы отобразить почасовую ставку, скажем, идентификатора сотрудника « E010 », вам необходимо получить его из таблицы «Сотрудник» с помощью функции VLOOKUP. Вот что вы введете в ячейку E3:
=VLOOKUP(A3,$A$3:$C$8,3,false)
Чтобы вы получили следующий результат:
Когда нам нужно VLOOKUP по нескольким критериям в Google Таблицах?
Вышеупомянутый случай включал поиск только одного критерия, совпадающего с идентификатором сотрудника. Однако в большинстве случаев критерий более сложен.
Множественность критериев VLOOKUP может иметь несколько причин. Вот некоторые примеры:
- У вас могут быть отдельные столбцы для имени и фамилии в исходной таблице. Таким образом, вам нужно будет просмотреть оба столбца, чтобы получить соответствующее значение, как показано ниже:
- Для получения значения может потребоваться проверка выполнения 2 или более условий. Например, вам может понадобиться сумма баллов только тех студентов, которые сдали тест и изучали французский язык, как показано ниже:
- Вам может потребоваться поискать отдельную таблицу, чтобы найти зарплату сотрудника определенного отдела в определенном коде региона.
Есть множество других ситуаций, подобных перечисленным выше. Но суть вы поняли.
Вы можете возразить, что использование ФИЛЬТРА было бы более простым вариантом для вышеперечисленных ситуаций. Однако ФИЛЬТР не может получить данные с другого листа.
Использование функций IF также будет излишним, потому что тогда вам придется иметь дело с несколькими вложенными IF (а это никому не нравится!).
Как выполнить VLOOKUP по нескольким критериям в Google Таблицах
Давайте возьмем следующий пример, где у вас есть одна таблица, содержащая бонусы, соответствующие каждому отделу и коду области.
Это можно сделать двумя способами с помощью функции VLOOKUP:
- Использование вспомогательного столбца
- Использование функции ARRAYFORMULA
Использование вспомогательного столбца для VLOOKUP по нескольким критериям в Google Таблицах
Первый метод предполагает использование дополнительного столбца «Помощник», который будет содержать комбинацию ячеек в критериях.
В нашем примере мы можем вставить столбец «Помощник» прямо перед столбцом «Отдел», чтобы он мог быть первым столбцом диапазона поиска.
Столбец «Помощник» может содержать комбинацию кода отдела и района для каждой строки, разделенных пробелом.
Давайте посмотрим, как создать и использовать этот вспомогательный столбец с функцией VLOOKUP, чтобы получить соответствующее бонусное значение для таблицы 2:
- Вставьте новый столбец прямо перед первым столбцом таблицы 1. Он будет действовать как наш вспомогательный столбец. Для этого вам нужно щелкнуть правой кнопкой мыши заголовок первого столбца (столбец A) и выбрать «Вставить 1 слева» в появившемся контекстном меню.
- Выберите первую ячейку вновь созданного столбца (ячейка A4) и введите формулу: = B4 & ”“ & C4.
- Нажмите клавишу возврата. Ячейка A4 должна теперь содержать содержимое ячеек B4 и C5 вместе, разделенных только пробелом.
- Дважды щелкните маркер заполнения ячейки A4, чтобы скопировать формулу в остальные ячейки столбца A. Вот как столбец A должен выглядеть в этот момент:
- Теперь, когда вспомогательный столбец готов, мы можем продолжить и использовать функцию VLOOKUP. Вы заметите, что добавление нового столбца сдвинуло содержимое таблицы 2 на одну ячейку вправо. При желании вы можете выделить содержимое и переместить его на одну ячейку слева.
- Затем выберите ячейку E17 (бонусный столбец таблицы 2) и введите формулу: =VLOOKUP(B17&” “&7,$A$4:$D$12,4, false).
- Нажмите клавишу возврата.
- Теперь вы должны увидеть значение бонуса, соответствующее “Operations BH12” из Таблицы 1.
- Дважды щелкните маркер заполнения ячейки E17, чтобы скопировать формулу в остальные ячейки столбца A.
- Теперь вы должны увидеть все значения бонусов, соответствующие каждому отделу и региональному коду в таблице 2.
Примечание . При вводе формулы VLOOKUP не забудьте заблокировать ссылки во втором параметре, нажав клавишу F4. Это гарантирует, что диапазон поиска не сместится при копировании формулы в остальные ячейки.
Объяснение формулы
Давайте разберем приведенную ниже формулу VLOOKUP, чтобы понять каждый используемый параметр:
=VLOOKUP(B17&" "&C17,$A$4:$D$12,4, false)
- search_key : ключ поиска, который мы использовали здесь, представляет собой комбинацию значений кода отдела и района, которые мы хотим найти, разделенных пробелом (это именно тот формат, который мы использовали в нашем столбце Helper).
- range : мы знаем, что в нашем диапазоне поиска всегда должен быть столбец подстановки в качестве первого столбца. Диапазон поиска теперь начинается с A4 до D12, поскольку A4 — первая ячейка нашего вспомогательного столбца.
- index : из-за добавления дополнительного столбца слева наш целевой столбец теперь сдвинулся на одну ячейку вправо. Таким образом, столбец «Бонус» теперь находится в индексе 4 нашего диапазона поиска.
- is_sorted : значение FALSE для этого параметра указывает, что первый столбец диапазона поиска не нужно сортировать в порядке возрастания.
Поскольку столбцы search_key и Helper имеют один и тот же формат (с отделением, за которым следует пробел, за которым следует код области), функция VLOOKUP может легко найти соответствующий бонус и вернуть его.
Использование функции ARRAYFORMULA для VLOOKUP по нескольким критериям в Google Таблицах
Этот метод выполняет более или менее то же самое, что и первый метод. Единственное отличие состоит в том, что на этот раз вспомогательный столбец создается динамически, вместо того, чтобы физически создавать для него дополнительный столбец.
Метод использует функцию ARRAYFORMULA для создания «виртуальной» таблицы, содержащей следующие столбцы:
- Столбец, содержащий комбинацию ячеек в критериях.
- Целевой столбец из диапазона поиска
Применим это к нашему случаю. Вот шаги по созданию и использованию функции ARRAYFORMULA с функцией VLOOKUP, чтобы получить соответствующее бонусное значение для таблицы 2:
- Выберите ячейку E17 (столбец бонусов в таблице 2) и введите формулу:
=ARRAYFORMULA(VLOOKUP(B17&" "&C17,{$A$4:$A$12&" "&$B$4:$B$12,$C$4:$C$12},2,false)) - Нажмите клавишу возврата.
- Теперь вы должны увидеть значение бонуса, соответствующее Отделу: Операции и Телефонному коду: BH12 из Таблицы 1.
- Дважды щелкните маркер заполнения ячейки E17, чтобы скопировать формулу в остальные ячейки столбца A.
- Теперь вы должны увидеть все значения бонусов, соответствующие каждому отделу и коду региона в таблице 2.
Примечание . При вводе формулы VLOOKUP не забудьте заблокировать ссылки во втором параметре, нажав клавишу F4. Это гарантирует, что диапазон поиска не сместится при копировании формулы в остальные ячейки.
Объяснение формулы
Давайте разберем формулу, которую мы использовали, чтобы понять, что именно произошло:
=ARRAYFORMULA(VLOOKUP(B17&" "&C17,{$A$4:$A$12&" "&$B$4:$B$12,$C$4:$C$12},2,false))
- Во-первых, мы используем ARRAYFORMULA для создания своего рода виртуальной таблицы. Эта виртуальная таблица должна содержать следующие столбцы:
- Столбец, содержащий комбинацию значений из ячеек $ A $ 4: $ A $ 12 и ячеек $ B $ 4: $ B $ 12 , разделенных пробелом между значениями каждой ячейки: A$2:$A$9&” “&$B$2:$B$9
- Столбец, содержащий значения из столбца «Бонус» в таблице 1: C $ 4: $ C $ 12.
- Это указано в фигурных скобках, потому что мы хотим вернуть массив или виртуальную таблицу ячеек: = ArrayFormula ({$ A $ 2: $ A $ 9 & »« & $ B $ 2: $ B $ 9, $ C $ 2: $ D $ 9. })
Обратите внимание, что мы использовали «,» в качестве разделителя для формулы массива, потому что мы хотим обрабатывать части как столбцы, расположенные рядом друг с другом, как показано ниже:
- Если бы мы хотели, чтобы они располагались друг над другом, мы бы использовали точку с запятой «;» вместо запятой ‘,’.
- Затем мы применяем функцию VLOOKUP, используя приведенную выше формулу массива в качестве второго параметра. Остальные параметры такие же, как и раньше:
- search_key: ключ поиска снова представляет собой комбинацию значений кода отдела и района, которые мы хотим найти, разделенных пробелом.
- range: теперь диапазон представляет собой массив или виртуальную таблицу: {$ A $ 2: $ A $ 9 & ”“ & $ B $ 2: $ B $ 9, $ C $ 2: $ D $ 9}.
- index: поскольку созданная виртуальная таблица состоит всего из двух столбцов, где Bonus — второй столбец, мы используем 2 в качестве значения индекса.
- is_sorted: значение FALSE для этого параметра указывает, что первый столбец диапазона поиска не нужно сортировать в порядке возрастания.
Поскольку и search_key, и первый столбец массива, возвращаемого в диапазоне, имеют точно такой же формат (с отделом, за которым следует пробел, за которым следует код области), функция VLOOKUP может легко найти соответствующий бонус и вернуть его.
Это был лишь краткий обзор двух способов использования функции VLOOKUP, когда необходимо учитывать несколько критериев.
Мы рекомендуем вам самостоятельно опробовать примеры, которые мы обсуждали в этом руководстве. Это поможет вам понять, как работают формулы, и даст вам более глубокое понимание этого.
Надеюсь, вы нашли этот урок полезным!