ПРОСМОТР (функция ПРОСМОТР)
Используйте функцию ПРОСМОТР, одну из функций поиска и работы со ссылками, когда нужно взять одну строку или столбец и найти значение, находящееся в той же позиции в другой строке или столбце.
Предположим, что вы знаете артикул детали автомобиля, но не знаете ее цену. Тогда, используя функцию ПРОСМОТР, вы сможете вернуть значение цены в ячейку H2 при вводе артикула в ячейку H1.
Используйте функцию ПРОСМОТР для поиска в одной строке или одном столбце. В приведенном выше примере рассматривается поиск цен в столбце D.
Советы: Рассмотрите одну из новых функций подстановки в зависимости от используемой версии.
- Используйте функцию ВПР для поиска данных в одной строке или столбце, а также для поиска в нескольких строках и столбцах (например, в таблице). Это расширенная версия функции ПРОСМОТР. Посмотрите видеоролик о том, как использовать функцию ВПР.
- Если вы используете Microsoft 365, используйте XLOOKUP — это не только быстрее, но и позволяет выполнять поиск в любом направлении (вверх, вниз, влево, вправо).
Функцию ПРОСМОТР можно использовать двумя способами: в векторной форме и в форме массива.
- Векторная форма. Используйте эту форму LOOKUP для поиска значения в одной строке или одном столбце. Используйте форму вектора, если требуется указать диапазон, содержащий значения, которые нужно сопоставить. Например, если вы хотите найти значение в столбце A, до строки 6.
Форма массива. Настоятельно рекомендуется использовать VLOOKUP или HLOOKUP вместо формы массива. Просмотрите это видео об использовании ВПР. Форма массива предоставляется для совместимости с другими программами электронной таблицы, но ее функциональность ограничена. Массив — это набор значений в строках и столбцах (например, в таблице), в которых выполняется поиск. Например, если вам нужно найти значение в первых шести строках столбцов A и B, это и будет поиском с использованием массива. Функция ПРОСМОТР вернет наиболее близкое значение. Чтобы использовать форму массива, сначала необходимо отсортировать данные.
Векторная форма
При использовании векторной формы функции ПРОСМОТР выполняется поиск значения в пределах только одной строки или одного столбца (так называемый вектор) и возврат значения из той же позиции второго диапазона.
Синтаксис
ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])
Функция ПРОСМОТР в векторной форме имеет аргументы, указанные ниже.
- Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
- Просматриваемый_вектор Обязательный аргумент. Диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.
Важно: Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.
Замечания
- Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.
- Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, функция ПРОСМОТР возвращает значение ошибки #Н/Д.
Примеры векторов
Чтобы лучше разобраться в работе функции ПРОСМОТР, вы можете сами опробовать рассмотренные примеры на практике. В первом примере у вас должна получиться электронная таблица, которая выглядит примерно так:
-
Скопируйте данные из таблицы ниже и вставьте их в новый лист Excel.
| Скопируйте эти данные в столбец A | Скопируйте эти данные в столбец B |
|---|---|
| Частота 4,14 | Цвет красный |
| 4,19 | оранжевый |
| 5,17 | желтый |
| 5,77 | зеленый |
| 6,39 | синий |
| Скопируйте эту формулу в столбец D | Ниже описано, что эта формула означает | Предполагаемый результат |
|---|---|---|
| Формула | ||
| =ПРОСМОТР(4,19; A2:A6; B2:B6) | Поиск значения 4,19 в столбце A и возврат значения из столбца B, находящегося в той же строке. | оранжевый |
| =ПРОСМОТР(5,75; A2:A6; B2:B6) | Поиск значения 5,75 в столбце A, соответствующего ближайшему наименьшему значению (5,17), и возврат значения из столбца B, находящегося в той же строке. | желтый |
| =ПРОСМОТР(7,66; A2:A6; B2:B6) | Поиск значения 7,66 в столбце A, соответствующего ближайшему наименьшему значению (6,39), и возврат значения из столбца B, находящегося в той же строке. | синий |
| =ПРОСМОТР(0; A2:A6; B2:B6) | Поиск значения 0 в столбце A и возврат значения ошибки, так как 0 меньше наименьшего значения (4,14) в столбце A. | #Н/Д |
Форма массива
Совет: Настоятельно рекомендуется использовать VLOOKUP или HLOOKUP вместо формы массива. См. это видео о ВПР, в нем приведены примеры. Форма массива LOOKUP предоставляется для совместимости с другими программами для электронных таблиц, но ее функциональность ограничена.
Форма массива функции ПРОСМОТР просматривает первую строку или первый столбец массив, находит указанное значение и возвращает значение из аналогичной позиции последней строки или столбца массива. Эта форма функции ПРОСМОТР используется, если сравниваемые значения находятся в первой строке или первом столбце массива.
Синтаксис
Функция ПРОСМОТР в форме массива имеет аргументы, указанные ниже.
- Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в массиве. Аргумент искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
- Если функции ПРОСМОТР не удается найти искомое_значение, то в массиве выбирается наибольшее значение, которое меньше искомого_значения или равно ему.
- Если искомое_значение меньше, чем наименьшее значение в первой строке или первом столбце (в зависимости от размерности массива), то функция ПРОСМОТР возвращает значение ошибки #Н/Д.
Важно: Значения в массиве должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.
ПРОСМОТР (LOOKUP), да не X
Несколько слов о старой функции LOOKUP / ПРОСМОТР. Все сказанное актуально и для Google Таблиц, и для Excel (и для отечественного Р7, где есть и старая ПРОСМОТР, и новая ПРОСМОТРX). Скриншоты сделаны в Excel.
Функция внешне похожа на новую XLOOKUP / ПРОСМОТРX. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы (она требует постоянной сортировки данных, не особо подходит для поиска текста), хотя она и используется иногда в составе формул массива — эта функция умеет работать с массивами без Ctrl+Shift+Enter даже в старых версиях (пример будет ниже) — как СУММПРОИЗВ / SUMPRODUCT.
ПРОСМОТР решает задачу по интервальному поиску, как ВПР / VLOOKUP (по умолчанию, когда четвертый аргумент пропущен) — когда нужно определить, в какой интервал попадает число:
=ПРОСМОТР(что ищем; где ищем; откуда возвращаем результат)
В отличие от ВПР, в случае с ПРОСМОТРом результат может быть в другой строке:
Конечно, с ВПР такое тоже можно провернуть, но не из коробки: сначала придется собрать данные в один виртуальный массив с помощью фигурных скобок или HSTACK
ПРОСМОТР может работать с горизонтальными массивами (ВПР, например, не умеет, это делает схожая функция ГПР / HLOOKUP):
У ПРОСМОТРа есть еще одна форма, когда аргументов два, а не три. В таком случае поиск ведется в первом столбце/первой строке массива (второго аргумента), а значение возвращается из последнего столбца (строки) массива:
=ПРОСМОТР(что ищем; массив)
Для поиска текста (то, что делает ПРОСМОТРХ / XLOOKUP по умолчанию и ВПР с последним аргументом, равным нулю или FALSE/ЛОЖЬ) ПРОСМОТР не очень хорош, мягко говоря. Он будет работать, опять-таки, только при сортировке данных (ключевой столбец — тот, в котором мы ищем — должен быть отсортирован по возрастанию). То есть мы всегда должны поддерживать сортировку (по алфавиту в случае с текстом)!
Да, кстати, ПРОСМОТР не учитывает регистр, как и другие функции поиска.
Но это еще полбеды. Если у вас будут отсортированные данные, то все будет работать. Но при несовпадении (достаточно даже лишнего пробела) в ключе для поиска ПРОСМОТР не будет сигнализировать ошибкой #Н/Д / #N/A), КАК ПОИСКПОЗ / MATCH, ПОИСКПОЗX / MATCHX, ПРОСМОТРХ / XLOOKUP, ВПР / VLOOKUP и ГПР / HLOOKUP. Она принесет неверные данные!
Итого. Не лучшая это функция для поиска текста. Используйте ВПР / VLOOKUP или ИНДЕКС+ПОИСКПОЗ / INDEX+MATCH везде (эти варианты требуют сортировки только при поиске ближайших чисел), в Excel 2021 и Google Таблицах можно пользоваться новой функцией ПРОСМОТРX / XLOOKUP (работает без сортировки даже с числами).
Но, как я писал выше, бывают прикольные примеры использования ПРОСМОТРа. Вот парочка.
Поиск последнего значения в столбце
Эту задачу решает вот такая загадочная формула:
=ПРОСМОТР(2;1/(столбец<>""); столбец)
Что тут происходит?
столбец <> «» — это мы проверяем, равно ли каждое значение в столбце пустоте. На выходе получаем массив логических значений
В Excel 2021 такое можно прямо в ячейках, в прошлых версиях можно выделить кусочек формулы в ней самой и нажать F9, чтобы полюбоваться на возвращаемый массив значений
Дальше мы делим единицу на каждое значение в этом массиве. Получаем единицу и ошибки (из-за деления на ноль):
И в этом массиве (выше он выведен для демонстрации, а вообще он существует виртуально внутри функции, конечно) мы ищем двойку (или другое число, которое в нем априори не появится), а возвращаем значение из самого столбца. Двойку мы не найдем и ПРОСМОТР в итоге найдет последнее числовое значение и вернет соответствующее ему значение из столбца A.
Нечеткий текстовый поиск
Пример от Николая Павлова — из его замечательной книги » Мастер формул » (там еще есть мощные примеры применения ПРОСМОТРа).
Допустим, у вас кривоватый список названий, и нужно его исправить. Компания «Дичайший Лемур» может быть записана и как «Лемур», и как «Дикий Лемур», и как-то еще. Если находим слово «Лемур» — мы должны возвращать соответствующее этому слову полное название компании. Слова для поиска и полные названия у нас в отдельной табличке.
Будем искать ключевые слова функцией ПОИСК / SEARCH в названии компании:
ПОИСК(список слов для поиска;название)
Допустим, у нас список слов для поиска такой:
А название очередной компании — «ИП Барсик».
Такая конструкция выдаст массив вида:
Потому что «Лемура» и «Котозавра» не найдет, а «Барсик» в названии на 4 позиции.
Далее мы ПОИСК засунем в ПРОСМОТР и будем искать в этом массиве число — допустим, 32768 (потому что это максимально возможное число знаков в ячейке, больше не понадобится). Ближайшее к нему — это 4 в нашем случае, так что ПРОСМОТР выдаст второе по порядку значение из массива результатов — «ИП Барсик».
=ПРОСМОТР(32768; ПОИСК(слова для поиска;текст, в котором ищем); список для подстановки)
Функция ПРОСМОТРX
Используйте функцию XLOOKUP для поиска объектов в таблице или диапазоне по строкам. Например, найдите цену автомобильной части по номеру части или найдите имя сотрудника по идентификатору сотрудника. С помощью XLOOKUP можно искать в одном столбце условие поиска и возвращать результат из той же строки в другом столбце независимо от того, на какой стороне находится возвращаемый столбец.
Примечание: XLOOKUP недоступна в Excel 2016 и Excel 2019, однако вы можете столкнуться с ситуацией использования книги в Excel 2016 или Excel 2019 с функцией XLOOKUP в ней, созданной другим пользователем с помощью более новой версии Excel.
Синтаксис
Функция XLOOKUP выполняет поиск диапазона или массива, а затем возвращает элемент, соответствующий первому совпадению, который она находит. Если совпадения не существует, XLOOKUP может вернуть ближайшее (приблизительное) соответствие.
=ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возращаемый_массив; [если_ничего_не_найдено]; [режим_сопоставления]; [режим_поиска])
искомое_значение
Значение для поиска
*Если этот параметр опущен, функция XLOOKUP возвращает пустые ячейки, которые он находит в lookup_array.
просматриваемый_массив
Массив или диапазон для поиска
return_array
Возвращаемый массив или диапазон
[if_not_found]
Если допустимое совпадение не найдено, верните текст [if_not_found], который вы указали.
Если допустимое совпадение не найдено и [if_not_found] отсутствует, возвращается #N/A .
[режим_сопоставления]
Укажите тип сопоставления:
0 — точное совпадение. Если ни один из них не найден, верните #N/A. Этот параметр используется по умолчанию.
-1 — точное совпадение. Если ни один элемент не найден, верните следующий элемент меньшего размера.
1 — точное совпадение. Если ни один элемент не найден, верните следующий более крупный элемент.
2 — совпадение с использованием особого значения подстановочных знаков: *, ?, ~.
[режим_поиска]
Укажите используемый режим поиска:
1. Выполните поиск, начиная с первого элемента. Этот параметр используется по умолчанию.
-1 — выполнение обратного поиска, начиная с последнего элемента.
2. Выполните двоичный поиск, который зависит от lookup_array сортировки по возрастанию . Если сортировка не выполнена, будут возвращены недопустимые результаты.
-2 — выполнение двоичного поиска на основе сортировки просматриваемого_массива по убыванию. Если сортировка не выполнена, будут возвращены недопустимые результаты.
Примеры
В примере 1 используется XLOOKUP для поиска названия страны в диапазоне, а затем возврата ее телефонного кода страны. Он включает аргументы lookup_value (ячейка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Он не включает аргумент match_mode , так как по умолчанию XLOOKUP создает точное совпадение.
Примечание: XLOOKUP использует массив подстановки и возвращаемый массив, тогда как ВПР использует один массив таблиц, за которым следует номер индекса столбца. Эквивалентная формула ВПР в этом случае будет: =VLOOKUP(F2;B2:D11;3;FALSE)
В примере 2 выполняется поиск сведений о сотрудниках на основе идентификатора сотрудника. В отличие от ВПР, XLOOKUP может возвращать массив с несколькими элементами, поэтому одна формула может возвращать имя сотрудника и отдел из ячеек C5:D14.
В примере 3 к предыдущему примеру добавляется аргумент if_not_found .
В примере 4 в столбце C выполняется поиск личного дохода, указанного в ячейке E2, и поиск соответствующей налоговой ставки в столбце B. Он задает аргумент if_not_found для возврата 0 (ноль), если ничего не найдено. Аргумент match_mode имеет значение 1 , что означает, что функция будет искать точное совпадение, а если не удается найти его, она возвращает следующий более крупный элемент. Наконец, аргумент search_mode имеет значение 1, что означает, что функция будет выполнять поиск от первого элемента к последнему.
Примечание: Lookup_array столбец XARRAY находится справа от return_array столбца, тогда как ВПР может смотреть только слева направо.
Пример 5 использует вложенную функцию XLOOKUP для выполнения вертикального и горизонтального совпадения. Сначала выполняется поиск валовой прибыли в столбце B, затем выполняется поиск Qtr1 в верхней строке таблицы (диапазон C5:F5) и, наконец, возвращается значение на пересечении двух. Это аналогично совместному использованию функций INDEX и MATCH .
Совет: Для замены функции HLOOKUP можно также использовать XLOOKUP.
Примечание: Формула в ячейках D3:F3: =XLOOKUP(D2,$B 6:$B 17;XLOOKUP($C 3,$C 5:$G 5;$C 6:$G 17)).).
Пример 6 использует функцию СУММ и две вложенные функции XLOOKUP для суммирования всех значений между двумя диапазонами. В этом случае мы хотим суммировать значения для винограда, бананов и включить груши, которые находятся между ними.
Формула в ячейке E3: =SUM(XLOOKUP(B3;B6:B10;E6:E10):XLOOKUP(C3;B6:B10;E6:E10))
Как это работает? XLOOKUP возвращает диапазон, поэтому при вычислении формула выглядит следующим образом: =SUM($E$7:$E$9) . Вы можете увидеть, как это работает самостоятельно, выбрав ячейку с формулой XLOOKUP, аналогичную этой, а затем выберите Формулы > Аудит формул > Вычислить формулу, а затем выберите Оценить, чтобы выполнить вычисление.
Примечание: Благодаря Microsoft Excel MVP , Билл Елен, за то, что он предложил этот пример.
См. также
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Функция ПРОСМОТРX в Excel
Долгожданная функция ПРОСМОТРX (XLOOKUP) стала доступна пользователям Microsoft Excel.
О предстоящем появлении функции было объявлено еще в прошлом году. В начале этого года она стала постепенно появляться у пользователей.
Функцию сразу же окрестили новой версией ВПР.
К сожалению, функция доступна не всем. Только пользователи Office 365 могут ей воспользоваться.
Давайте разберемся, в чем суть функции. Начнем с синтаксиса:
ПРОСМОТРX(искомое_значение; просматриваемый_массив;
возвращаемый_массив; [если_ничего_не_найдено];
[режим_сопоставления]; [режим_поиска])- искомое значение – то, что мы хотим найти в массиве данных;
- просматриваемый массив – строка или столбец, в которых мы будем искать наше значение. Сразу отличие от ВПР: функция ПРОСМОТРX работает и в вертикальных, и в горизонтальных таблицах;
- возвращаемый массив – строка или столбец, из которых мы возьмем результат. Важное отличие в том, что теперь возвращаемый массив (в отличие от ВПР/ГПР) может располагаться слева от просматриваемого массива;
- если ничего не найдено – [необязательный элемент функции]. Не обнаружив искомое значение в просматриваемом массиве Excel вернет нам ошибку #Н/Д. Если такой вариант нам не подходит, то вместо стандартной ошибки мы можем вывести что-то свое. Например: “не найдено” или “” – если мы хотим видеть пустую ячейку;
- режим сопоставления – [необязательный элемент функции]. По умолчанию функция производит точное сопоставление (в ВПР для этого нам нужно было ставить 0). Теперь можно выбирать один из вариантов:
Соответствие с учетом подстановочных знаков позволяет указать только часть строки, заменив на ? любой отсутствующий символ или на * – несколько отсутствующих символов.
- режим поиска – [необязательный элемент функции]. Позволяет указать, что поиск должен идти не от первого элемента к последнему, а в обратной последовательности:
Важное замечание: при написании функции последний символ – это латинская буква икс, а не русская ха.
Мы уже добавили данную функцию в наш курс “Продвинутый пользователь Excel” . Записывайтесь, будем разбираться вместе!
Расписание ближайших групп: