Как улучшить работу функций LOOKUP

Для поиска информации в массиве данных можно использовать функции LOOKUP:

• VLOOKUP
• HLOOKUP
• MATCH
• INDEX

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

Сортировка диапазона с помощью функции LOOKUP

Чтобы повысить эффективность, используйте сортировку диапазона вместо функции SORT.

1. Выберите ячейки или столбцы.
2. На панели задач вверху нажмите ДанныеСортировать диапазон.
3. Выберите один из следующих вариантов:
   • Сортировать диапазон по [крайнему левому столбцу] (А–Я).
   • Сортировать диапазон по [крайнему левому столбцу] (Я–А).
   • Расширенные настройки сортировки диапазонов: сортировать по нескольким столбцам одновременно в иерархическом порядке.

Примечание. Функция "Сортировать диапазон" сначала сортирует данные, а затем отправляет их для выполнения функции VLOOKUP.

Не рекомендуется

Важно! В следующем примере показано, как не надо использовать функцию SORT:

=VLOOKUP(search_key, SORT(A1:B10, 1), 2)

В этом примере функция SORT вложена в функцию VLOOKUP. При каждом изменении значений в сортируемом столбце функция SORT выполняет ещё один избыточный просмотр всех данных.

Совет. Не стоит применять функцию SORT к набору данных больше одного раза. Если необходимо, можно добавить ссылку на нее.

Прежде чем использовать функцию LOOKUP, удалите повторяющиеся данные

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

1. Выберите все необходимые столбцы и строки.
2. На панели задач вверху нажмите ДанныеОчистка данныхУдалить повторы.
3. Выберите столбцы, которые необходимо проанализировать.
4. Нажмите Удалить повторы.

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

Функции LOOKUP:

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

Выполните функцию LOOKUP в той же таблице, в которой содержатся исходные данные

Чтобы выполнить функцию LOOKUP на локальном компьютере, сначала импортируйте данные в свою таблицу:

1. Используйте функцию IMPORTRANGE, чтобы поместить данные в пустой диапазон в той же таблице, в которой выполняется функция LOOKUP. Подробнее о функции IMPORTRANGE…
2. Добавьте ссылку на импортированные данные в функцию LOOKUP в качестве диапазона.

Не рекомендуется

Важно! В следующем примере показано, как не надо использовать функцию IMPORTRANGE:

=VLOOKUP(search_key, IMPORTRANGE(spreadsheet_url, range_string), index, [is_sorted])

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

Совет. Не добавляйте в LOOKUP вложенные функции. В противном случае функция, вложенная в LOOKUP, будет выполнять дополнительные вычисления при каждом запуске LOOKUP.

Используйте оператор IFERROR() или IF(), чтобы пропускать определенные запросы

Чтобы ускорить вычисления в Таблицах, используйте оператор IF для пропуска повторяющихся значений, например N/A, #ERROR, REF# или пустых строк.

Не рекомендуется

Важно! В следующем примере показано, как не надо использовать функцию VLOOKUP:

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

Сервис "Google Таблицы" выполняет вычисления в ячейках B2–B10 по всем запросам, указанным в столбце A. При этом в ячейках B3, B7 и B9 функция VLOOKUP выполняет поиск пустых значений. Такой поиск не даст результатов.

Рекомендуется

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

Функция IF означает следующее: "Если запрос не равен пустой строке, нужно выполнить формулу. Если равен, не выполнять формулу и вернуть значение N/A".

Можно использовать любую формулу: пропускаемое значение не обязательно должно быть пустой строкой. Эта методика позволяет сэкономить время. Если среди данных есть бесполезные значения, можно исключить их при вычислении результата.

Если вы не обозначите данные как бесполезные с помощью функции IF, Google Таблицы не смогут это определить.

=IF(A2 <> value_to_skip, your_formula, "N/A")

Совет. Функция IF выше означает следующее: "Если A2 не равно пропускаемое_значение, необходимо применить формулу. Если A2 равно пропускаемое_значение, не применять формулу и вернуть N/A".

Подробнее о функции IF…

Использование INDEX и MATCH вместо VLOOKUP

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

Не рекомендуется

Пример: для того, чтобы функция VLOOKUP нашла цену и количество параметра "Apple" в таблице, вам необходимо использовать две отдельные формулы VLOOKUP:

=VLOOKUP("Apple", $A$1:$C$4, 2, FALSE)

Эта функция VLOOKUP выполняет два действия:

1. определяет, где находится параметр "Apple";
2. ищет цену яблок во втором столбце.

=VLOOKUP("Apple", $A$1:$C$4, 3, FALSE)

Эта функция VLOOKUP выполняет два действия:

1. определяет, где находится параметр "Apple";
2. ищет количество яблок во третьем столбце.

В обеих формулах действие 1 совпадает: выполняется поиск параметра "Apple" в списке. Но действия 2 не могут выполняться одновременно, поскольку функция VLOOKUP возвращает только одно значение.

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

Рекомендуется

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

1. Выполните поиск параметра "Apple" с помощью оператора MATCH:

=MATCH("Apple", $A$2:$A$4, FALSE)

Вы получите значение "1", поскольку строка "Apple" находится в первой позиции диапазона.

2. Найдите цену яблок во втором столбце с помощью оператора INDEX:

=INDEX($A$2:$C$4, cell_with_MATCH_formula, 2)

Вы получите значение "$1".

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

3. Найдите количество яблок в третьем столбце с помощью оператора INDEX:

=INDEX($A$2:$C$4, cell_with_MATCH_formula, 3)

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

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

Информация по теме

• VLOOKUP
• HLOOKUP
• MATCH
• INDEX