Функция ПРОСМОТР в Excel и особенности ее использования
Функция ПРОСМОТР упрощает поиск данных в строке, столбце таблицы и массиве данных вместе с ее аналогами:
- ВПР;
- Георадар;
- ПОИСК
Обратите внимание, что результат функции ПРОСМОТР может быть неверным, если данные в массиве или столбце таблицы не отсортированы в числовом или алфавитном порядке. Если сортировка невозможна по разным причинам, рекомендуется использовать аналоги этой функции, перечисленные выше.
Эта функция может быть записана в двух вариантах синтаксиса:
1. Векторная форма письма. Вектор данных в Excel считается диапазоном данных, который содержит только одну строку или столбец в таблице. Соответственно функция ПРОСМОТР используется для поиска определенного значения в одной строке или одном столбце. Синтаксис:
=ПРОСМОТР(искомое значение; искомый вектор; результирующий вектор)
Должны быть указаны первые два аргумента функции.
Описание версии с 3 аргументами:
- Искомое_значение — это объект с числовыми, текстовыми, справочными или логическими данными. Функция ПРОСМОТР ищет значение этих данных в векторе данных.
- Lookup_vector — это диапазон данных, представляющий столбец или строку таблицы. Вектор данных может содержать числовые, текстовые и логические значения. Все значения вектора данных должны быть отсортированы по возрастанию (AZ; FALSE, TRUE; -2, 0, 103, 1000).
- Result_vector — необязательный аргумент, представляющий диапазон данных из одной строки или столбца таблицы. Размеры поиска и результирующего вектора должны быть идентичными.
- Форма матрицы. В Excel массив представляет собой группу ячеек или значений, рассматриваемых как единое целое. Некоторые функции Excel принимают массивы в качестве аргументов или возвращают результаты в виде массивов данных. Синтаксис:
=ПРОСМОТР(искомое значение; массив)
Все аргументы в этой нотации являются обязательными.
Описание версии с двумя аргументами:
- Искомое_значение — это объект текстовых, логических, числовых или справочных данных, значение которых функция ПРОСМОТР ищет в определенном массиве данных. Если искомое_значение отсутствует в указанном массиве, функция выбирает наибольшее значение в массиве, которое меньше или равно искомому значению. #Н/Д будет возвращена ошибка, если значение первого элемента массива больше, чем искомое_значение.
- Массив — массив данных в Excel (текстовых, числовых, логических), сравниваемых с искомым значением. Функция ПРОСМОТР выполняет поиск по размерам массива, то есть в первой строке или первом столбце таблицы, если она содержит больше столбцов, чем строк, или больше строк, чем столбцов, соответственно.
Обратите внимание, что форма массива функции ПРОСМОТР была предоставлена только для совместимости с различными программными продуктами для работы с электронными таблицами, подобными Excel. Это обозначение может дать неправильные результаты и не рекомендуется. При работе с матрицами данных рекомендуется использовать аналоги: ГПР и ВПР.
Описание
Функция ПРОСМОТР Microsoft Excel возвращает значение из диапазона (одна строка или один столбец) или из массива.
Функция ПРОСМОТР — это функция, встроенная в Excel, которая классифицируется как функция поиска и ссылки.
Его можно использовать как функцию электронной таблицы (WS) в Excel.
В качестве функции электронной таблицы функцию OPSLAKK можно ввести как часть формулы в ячейке электронной таблицы. Существует два различных синтаксиса функции OPSLAKK: в векторной форме и в матричной форме.
Читайте также: Точность как на экране в Excel
Синтаксис функции
Существует 2 формы аргументов функции ПРОСМОТР(): форма массива и векторная форма.
Матричная форма
Массивная форма функции ПРОСМОТР() сканирует первый (левый) столбец таблицы и, если находит нужное значение, возвращает значение из соответствующей строки в крайнем правом столбце таблицы (массиве).
ПРОСМОТР (искомое значение; массив)
Формула =ПРОСМОТР(«яблоки», A2:B10) сканирует диапазон ячеек A2:A10 . Если, например, ячейка А5 содержит искомое значение «яблоки», то формула возвращает значение из ячейки В5, т.е из соответствующей ячейки в крайнем правом столбце таблицы (В2:В10). Обратите внимание на следующее! Значения в диапазоне A2:A10 должны быть отсортированы по возрастанию.
Если функция ПРОСМОТР() не находит искомое значение, выбирается наибольшее значение, которое меньше или равно искомому значению.
Функция VIEW() также имеет векторную форму. Вектор представляет собой диапазон ячеек, расположенных в один столбец или одну строку.
ПРОСМОТР (искомое значение; искомый вектор; результирующий вектор)
Формула =ПРОСМОТР(«яблоки», A2:A10, B2:B10) сканирует диапазон ячеек A2:A10 . Если, например, ячейка А5 содержит искомое значение «яблоки», то формула возвращает значение из ячейки В5, т.е из соответствующей ячейки в крайнем правом столбце таблицы (В2:В10). Обратите внимание на следующее! Значения в диапазоне A2:A10 должны быть отсортированы по возрастанию. Если функция ПРОСМОТР() не находит искомое значение, выбирается наибольшее значение, которое меньше или равно искомому значению.
Функция ПРОСМОТР() не различает случаи при сравнении текстов.
Если функция ПРОСМОТР() не находит подходящего значения, возвращается значение ошибки #Н/Д.
Варианты записи функции ПРОСМОТР
Изначально функция ПРОСМОТР имеет две формы записи: вектор и массив. Когда вы вводите функцию на листе, Excel напоминает вам об этом следующим образом:
Форма массива
Форма массива очень похожа на функции ГПР и ВПР. Основное отличие состоит в том, что ГПР ищет значение в первой строке диапазона, ВПР — в первом столбце, а ПРОСМОТР либо в первом столбце, либо в первой строке, в зависимости от размерности массива. Есть и другие отличия, но они менее значительны.
Мы не будем подробно разбирать эту форму приема, так как она давно устарела и оставлена только в Excel для совместимости с предыдущими версиями программы. Вместо этого рекомендуется использовать функции ВПР или ГПР.
Векторная форма
Функция ПРОСМОТР (в векторной форме) просматривает диапазон, состоящий из одной строки или одного столбца. Находит в нем заданное значение и возвращает результат из соответствующей ячейки второго диапазона, который также состоит из строки или столбца.
Ух ты! Что ж, это надо написать… Чтобы было понятнее, рассмотрим небольшой пример.
Примеры использования функции ПРОСМОТР для быстрого поиска в таблицах Excel
Пример 1. Физик определил ускорение свободного падения эмпирическим путем: с некоторой высоты он запустил обтекаемый объект и измерил время прохождения от точки запуска до момента контакта с поверхностью Земли. Затем по формуле g=2S/t2 определяли искомое значение. После нескольких экспериментов были получены следующие результаты:
Необходимо определить, есть ли среди полученных результатов значение, соответствующее общепринятому значению g = 9,81.
Для решения введите следующую формулу в ячейку D2:
=ОТКРЫТЬ(9;81;B2:B7)
Описание значений:
- C2 (9.81) — явно заявленное желаемое значение;
- B2:B7 – массив данных, среди которых осуществляется поиск.
Результат расчета:
То есть среди результатов расчетов было искомое значение.
Примечание. Значения в столбце результатов не отсортированы по возрастанию. Как упоминалось ранее, функция возвращает наибольшее значение из массива, которое меньше или равно искомому значению. Если бы поиск производился, например, по числу 10, то в этом случае было бы возвращено значение 9,4, что не является корректным результатом (максимальное значение в столбце — 9,5). Для корректной работы функции необходимо отсортировать массив данных.
Пример 1
На рисунке ниже представлена таблица с номерами телефонов и фамилиями сотрудников. Наша задача определить номер телефона на основе фамилии сотрудника.
В этом примере функция ВПР не используется, поскольку отображаемый столбец не является самым левым столбцом. Именно в таких случаях можно использовать функцию VIS. Формула будет выглядеть так:
Первым аргументом функции ПРОСМОТР является ячейка С1, где мы указываем искомое значение, т.е фамилию. Область B1:B7 является областью поиска, также называемой вектором поиска. Из соответствующей ячейки в диапазоне A1:A7 функция OPSLAKK возвращает результат, такой диапазон также называется результирующим вектором. Нажимая Enter, убеждаемся, что все правильно.
Пример 2
Функцию ВИД в Excel удобно использовать, когда векторы вида и результата принадлежат разным таблицам, расположены в удаленных частях листа или даже на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.
На рисунке ниже вы можете видеть такой пример:
Как видите, диапазоны смещены друг относительно друга как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размеры векторов совпадали. Нажимая Enter получаем нужный результат:
При использовании функции ПРОСМОТР в Excel значения в векторе поиска должны быть отсортированы в порядке возрастания, иначе он может вернуть неверный результат.
Итак, кратко и на примерах мы познакомились с функцией ВИД и научились использовать ее в книгах Excel. Надеюсь, эта информация оказалась для вас полезной и вы обязательно найдете ей применение. Всего вам доброго и успехов в изучении Excel.
Вторая версия функции ПРОСМОТР в Excel
Пример 2. 5 клиентов обратились в банк за кредитом на несколько разных сумм. Банк определяет процент за пользование кредитом с учетом суммы запрашиваемых средств в долг. Каждый клиент должен вернуть банку сумму, составляющую основную часть кредита и процентов, в денежной форме. Вносим в таблицу первые данные:
Задача состоит в том, чтобы найти процент возврата, учитывая взаимосвязь между процентом и суммой кредита, а также рассчитать сумму возврата. Определим желаемые значения для клиента с фамилией Иванов. Для этого в ячейку С2 введите следующую формулу:
=ОТКРЫТЬ(B2,$A$12:$A$17,$B$12:$B$17)
Описание значений:
- B2 — сумма кредита, который взял клиент.
- $A$12:$A$17 – ряд сумм, в которых ищется соответствующее или ближайшее значение к искомому.
- $B$12:$B$17 — это диапазон соответствующих процентов.
Примечание. Символ «$» используется для «исправления» ссылок на ячейки.
Результат расчета:
То есть Иванов получил кредит под 6% годовых.
Для определения суммы возврата введите формулу:
=В2+В2*С2
Описание значений:
- Б2 — сумма (тело) кредита, взятого Ивановым;
- B2*C2 — сумма процентов за пользование в денежном выражении.
Результат расчета:
То есть клиент Иванов обязан вернуть 127,2 денежных единиц. Аналогичный метод используется для расчета долга для других клиентов.
Пример 3. В офисе 5 сотрудников разного возраста. Необходимо найти возраст Виталия.
Вносим в таблицу первые данные:
Чтобы определить возраст самого молодого сотрудника, введите в ячейку E3 формулу:
=ПРОСМОТР(D3;A2:A6;B2:B6)
Описание значений:
- D3 – фамилия сотрудника, возраст которого необходимо определить;
- A2:A6 — вектор поиска имен;
- B2:B6 — соответствующий вектор возраста.
Результат расчета:
Это означает, что сотруднику Виталия 43 года.
В этом примере мы видели две версии функции ПРОСМОТР с 2 и 3 аргументами для заполнения входящими данными.