Содержание:
Вчера на марафоне 30 функций Excel за 30 дней мы получили удовольствие от этой функции ПОВТОР (ПОВТОР), создавая диаграммы внутри ячейки и используя их для простого подсчета. Сегодня понедельник, и нам снова пора надеть шляпы мыслителей.
На 16 день марафона мы изучим функцию LOOKUP (ВИД). Это близкий друг ВПР (ВПР) и ГПР (георадар), но работает немного по-другому.
Итак, изучим теорию и проверим функцию на практике. LOOKUP (ВИД). Если у вас есть дополнительная информация или примеры использования этой функции, поделитесь ими в комментариях.
Функция 16: ПРОСМОТР
Функция LOOKUP (ПРОСМОТР) возвращает значение из одной строки, одного столбца или из массива.
Как я могу использовать функцию ПРОСМОТР?
Функция LOOKUP (ПРОСМОТР) возвращает результат в зависимости от искомого значения. С его помощью вы сможете:
- Найдите последнее значение в столбце.
- Найдите последний месяц с отрицательными продажами.
- Преобразуйте достижения учащихся из процентов в буквенные оценки.
ПОИСК синтаксиса
Функция LOOKUP (LOOKUP) имеет две синтаксические формы — векторную и массивную. В векторной форме функция ищет значение в заданном столбце или строке, а в форме массива она ищет значение в первой строке или столбце массива.
Векторная форма имеет следующий синтаксис:
LOOKUP(lookup_value,lookup_vector,result_vector)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)
- искомое_значение (искомое_значение) — может быть текстом, числом, логическим значением, именем или ссылкой.
- вектор_поиска (lookup_vector) – диапазон, состоящий из одной строки или одного столбца.
- вектор_результата (result_vector) – диапазон, состоящий из одной строки или одного столбца.
- диапазоны аргументов вектор_поиска (просматриваемый_вектор) и вектор_результата (result_vector) должен быть одинакового размера.
Форма массива имеет следующий синтаксис:
LOOKUP(lookup_value,array)
ПРОСМОТР(искомое_значение;массив)
- искомое_значение (искомое_значение) — может быть текстом, числом, логическим значением, именем или ссылкой.
- поиск осуществляется по размерности массива:
- если в массиве столбцов больше, чем строк, то поиск происходит в первой строке;
- если количество строк и столбцов одинаково или строк больше, то поиск происходит в первом столбце.
- функция возвращает последнее значение из найденной строки/столбца.
Ловушки ПРОСМОТР (ПРОСМОТР)
- В функции LOOKUP (ОБЗОР) нет возможности поиска точного совпадения, которое находится в ВПР (ВПР) и в ГПР (георадар). Если искомого значения нет, то функция вернет максимальное значение, не превышающее искомое значение.
- Искомый массив или вектор необходимо отсортировать по возрастанию, иначе функция может вернуть неверный результат.
- Если первое значение в искомом массиве/векторе больше искомого значения, функция выдаст сообщение об ошибке. #В (#Н/Д).
Пример 1. Поиск последнего значения в столбце
В виде функции массива LOOKUP (ПРОСМОТР) можно использовать для поиска последнего значения в столбце.
Значение кавычек в справке Excel 9,99999999999999E + 307 как наибольшее число, которое можно записать в ячейку. В нашей формуле оно будет задано как желаемое значение. Предполагается, что такое большое число не будет найдено, поэтому функция вернет последнее значение в столбце D.
В этом примере числа в столбце D разрешено не сортировать, кроме того могут попадаться текстовые значения.
=LOOKUP(9.99999999999999E+307,D:D)
=ПРОСМОТР(9,99999999999999E+307;D:D)
Пример 2. Найдите последний месяц с отрицательным значением.
В этом примере мы будем использовать векторную фигуру LOOKUP (ВИД). Столбец D содержит значения продаж, а столбец E содержит названия месяцев. В некоторые месяцы дела шли не очень хорошо, и в ячейках со значениями продаж появлялись отрицательные числа.
Чтобы найти последний месяц с отрицательным числом, используйте формулу с LOOKUP (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/(Д2:Д8<0), который формирует в оперативной памяти компьютера массив, состоящий из 1 и значения ошибок # DIV / 0 (#РАЗДЕЛ/0). 1 указывает, что соответствующая ячейка в диапазоне D2:D8 содержит значение меньше 0, и ошибка # DIV / 0 (#DIV/0) – что больше или равно 0. В результате наша задача – найти последнее 1 в созданный виртуальный массив и на основе этого вернуть название месяца из диапазона E2:E8.
Пример 3: Преобразование успеваемости учащихся из процентов в буквенные оценки
Ранее мы уже решали подобную задачу с помощью функции ВПР (ВПР). Сегодня мы будем использовать функцию LOOKUP (VIEW) в векторной форме для преобразования успеваемости учащихся из процентов в буквенные оценки. В отличие от ВПР (ВПР) для функции LOOKUP (ПРОСМОТР) Не имеет значения, находятся ли проценты в первом столбце таблицы. Вы можете выбрать абсолютно любую колонку.
В следующем примере оценки находятся в столбце D и отсортированы по возрастанию, а соответствующие им буквы находятся в столбце C слева от столбца, в котором выполняется поиск.
=LOOKUP(C10,D4:D8,C4:C8)
=ПРОСМОТР(C10;D4:D8;C4:C8)