30 функций Excel за 30 дней: ПРОСМОТР (LOOKUP)

Вчера в марафоне 30 функций Excel за 30 дней мы развлекались с функцией REPT (ПОВТОР), создавая диаграммы внутри ячейки и используя ее для простого подсчета. Сегодня понедельник, и нам в очередной раз пора надеть свои шляпы мыслителей.

В 16-й день марафона мы займёмся изучением функции LOOKUP (ПРОСМОТР). Это близкий друг VLOOKUP (ВПР) и HLOOKUP (ГПР), но работает она немного по-другому.

Итак, давайте изучим теорию и испытаем на практике функцию LOOKUP (ПРОСМОТР). Если у Вас есть дополнительная информация или примеры по использованию этой функции, пожалуйста, делитесь ими в комментариях.

Функция 16: LOOKUP (ПРОСМОТР)

Функция LOOKUP (ПРОСМОТР) возвращает значение из одной строки, одного столбца или из массива.

Как можно использовать функцию LOOKUP (ПРОСМОТР)?

Функция LOOKUP (ПРОСМОТР) возвращает результат, в зависимости от искомого значения. С ее помощью Вы сможете:

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

Синтаксис LOOKUP (ПРОСМОТР)

Функция LOOKUP (ПРОСМОТР) имеет две синтаксические формы – векторную и массива. В векторной форме функция ищет значение в заданном столбце или строке, а в форме массива – в первой строке или столбце массива.

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

LOOKUP(lookup_value,lookup_vector,result_vector)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)

• lookup_value (искомое_значение) – может быть текстом, числом, логическим значением, именем или ссылкой.
• lookup_vector (просматриваемый_вектор) – диапазон, состоящий из одной строки или одного столбца.
• result_vector (вектор_результатов) – диапазон, состоящий из одной строки или одного столбца.
• диапазоны аргументов lookup_vector (просматриваемый_вектор) и result_vector (вектор_результатов) должны быть одного размера.

Форма массива имеет вот такой синтаксис:

LOOKUP(lookup_value,array)
ПРОСМОТР(искомое_значение;массив)

• lookup_value (искомое_значение) – может быть текстом, числом, логическим значением, именем или ссылкой.
• поиск выполняется в соответствии с размерностью массива:
  • если в массиве больше столбцов, чем строк, то поиск происходит в первой строке;
  • если количество строк и столбцов одинаково или строк больше, то поиск происходит в первом столбце.
• функция возвращает последнее значение из найденной строки/столбца.

Ловушки LOOKUP (ПРОСМОТР)

• В функции LOOKUP (ПРОСМОТР) нет опции для поиска точного совпадения, которая есть в VLOOKUP (ВПР) и в HLOOKUP (ГПР). Если искомое значения отсутствует, то функция возвратит максимальное значение, не превышающее искомое.
• Массив или вектор, в котором выполняется поиск, должен быть отсортирован по возрастанию, иначе функция может вернуть неправильный результат.
• Если первое значение в просматриваемом массиве/векторе больше, чем искомое значение, то функция выдаст сообщение об ошибке #N/A (#Н/Д).

Пример 1: Находим последнее значение в столбце

В форме массива функция LOOKUP (ПРОСМОТР) может быть использована для поиска последнего значения в столбце.

Справка Excel приводит значение 9,99999999999999E+307 как наибольшее число, которое может быть записано в ячейке. В нашей формуле оно будет задано, как искомое значение. Предполагается, что такое большое число найдено не будет, поэтому функция возвратит последнее значение в столбце D.

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

=LOOKUP(9.99999999999999E+307,D:D)
=ПРОСМОТР(9,99999999999999E+307;D:D)

Пример 2: Находим последний месяц с отрицательным значением

В этом примере мы будем использовать векторную форму LOOKUP (ПРОСМОТР). В столбце D записаны значения продаж, а в столбце E – названия месяцев. В некоторые месяцы дела шли не очень хорошо, и в ячейках со значениями продаж появились отрицательные числа.

Чтобы найти последний месяц с отрицательным числом, формула с LOOKUP (ПРОСМОТР) будет проверять для каждого значения продаж справедливость утверждения, что оно меньше 0 (неравенство в формуле). Далее мы делим 1 на полученный результат, в итоге имеем либо 1, либо сообщение об ошибке #DIV/0 (#ДЕЛ/0).

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

=LOOKUP(2,1/(D2:D8<0),E2:E8)
=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)

Пояснение: В данной формуле вместо аргумента lookup_vector (просматриваемый_вектор) подставлено выражение 1/(D2:D8<0), которое образует в оперативной памяти компьютера массив, состоящий из 1 и значений ошибки #DIV/0 (#ДЕЛ/0). 1 говорит о том, что в соответствующей ячейке диапазона D2:D8 находится значение меньше 0, а ошибка #DIV/0 (#ДЕЛ/0) – что больше или равное 0. В итоге наша задача сводится к тому, чтобы найти последнюю 1 в созданном виртуальном массиве, и на основании этого вернуть название месяца из диапазона E2:E8.

Пример 3: Преобразовываем успеваемость учащихся из процентов в буквенную систему оценок

Ранее мы уже решали похожую задачу с помощью функции VLOOKUP (ВПР). Сегодня воспользуемся функцией LOOKUP (ПРОСМОТР) в векторной форме, чтобы преобразовать успеваемость учащихся из процентов в буквенную систему оценок. В отличие от VLOOKUP (ВПР) для функции LOOKUP (ПРОСМОТР) не важно, чтобы проценты находились в первом столбце таблицы. Вы можете выбрать абсолютно любой столбец.

В следующем примере баллы указаны в столбце D, они отсортированы в порядке возрастания, а соответствующие им буквы – в столбце C, слева от столбца, по которому производится поиск.

=LOOKUP(C10,D4:D8,C4:C8)
=ПРОСМОТР(C10;D4:D8;C4:C8)