Просмотр х эксель как сделать

ПРОСМОТР (функция ПРОСМОТР)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

Предположим, что вы знаете артикул детали автомобиля, но не знаете ее цену. Тогда, используя функцию ПРОСМОТР, вы сможете вернуть значение цены в ячейку H2 при вводе артикула в ячейку H1.

Пример способов использования функции ПРОСМОТР

Используйте функцию ПРОСМОТР для поиска в одной строке или одном столбце. В приведенном выше примере рассматривается поиск цен в столбце D.

Советы: Рассмотрим одну из новых функций подстановки в зависимости от используемой версии.

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

Функцию ПРОСМОТР можно использовать двумя способами: в векторной форме и в форме массива.

Векторная форма: используйте эту форму LOOKUP для поиска значения в одной строке или в одном столбце. Используйте векторную форму, если нужно указать диапазон, содержащий значения, которые необходимо сопоставить. Например, если вы хотите найти значение в столбце A, вниз до строки 6.
Форма массива. Мы настоятельно рекомендуем использовать ВПР или ГПР вместо формы массива. Посмотрите это видео об использовании ВПР. Форма массива предоставляется для совместимости с другими программами электронной таблицы, но ее функциональные возможности ограничены. Массив — это набор значений в строках и столбцах (например, в таблице), в которых выполняется поиск. Например, если вам нужно найти значение в первых шести строках столбцов A и B, это и будет поиском с использованием массива. Функция ПРОСМОТР вернет наиболее близкое значение. Чтобы использовать форму массива, сначала необходимо отсортировать данные.

Векторная форма

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

Синтаксис

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

Функция ПРОСМОТР в векторной форме имеет аргументы, указанные ниже.

Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
Просматриваемый_вектор Обязательный аргумент. Диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.

Важно: Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.

Замечания

Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.
Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, функция ПРОСМОТР возвращает значение ошибки #Н/Д.

Примеры векторов

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

Пример использования функции ПРОСМОТР

Скопируйте эти данные в столбец 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.	#Н/Д

Форма массива

Совет: Мы настоятельно рекомендуем использовать ВПР или ГПР вместо формы массива. См. это видео о функции ВПР. В нем приведены примеры. Форма поиска массива предоставляется для обеспечения совместимости с другими программами электронной таблицы, но ее функциональные возможности ограничены.

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

Синтаксис

Функция ПРОСМОТР в форме массива имеет аргументы, указанные ниже.

Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в массиве. Аргумент искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
- Если функции ПРОСМОТР не удается найти искомое_значение, то в массиве выбирается наибольшее значение, которое меньше искомого_значения или равно ему.
- Если искомое_значение меньше, чем наименьшее значение в первой строке или первом столбце (в зависимости от размерности массива), то функция ПРОСМОТР возвращает значение ошибки #Н/Д.
Важно: Значения в массиве должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.

Функция ПРОСМОТР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 или получить поддержку в сообществах.

Просмотр х эксель как сделать

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949 ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Функция ПРОСМОТРX — наследник ВПР

В мае 2019 года руководитель команды разработчиков Microsoft Excel Joe McDaid анонсировал выход новой функции, которая должна прийти на замену легендарной ВПР (VLOOKUP). Новая функция получила сочное английское название XLOOKUP и не очень внятное русское ПРОСМОТРX (причем последняя буква тут именно английская «икс», а не русская «ха» — забавно). Полгода Microsoft тренировалась на кошках тестировала эту функцию на своих сотрудниках и добровольцах-инсайдерах и, наконец, в январе 2020 года было объявлено, что XLOOKUP готова к использованию и будет в ближайшее время разослана с обновлениями всем подписчикам Office 365. Давайте разберёмся, в чем её преимущества перед классической ВПР (VLOOKUP), и как она может нам помочь в повседневной работе с данными в Microsoft Excel.

Старый добрый ВПР

Предположим, перед нами стоит задача найти в прайс-листе цену, например, для гречки. При помощи привычно функции ВПР (VLOOKUP) это решалось бы примерно так:
На всякий случай, напомню:
- Первый аргумент здесь — искомое значение («гречка» из H4).
- Второй — область поиска, причем обязательно начиная со столбца, где хранятся искомые данные, т.е. с товара, а не с артикула.
- Третий — порядковый номер столбца в таблице, из которого мы хотим извлечь нужное нам значение (цена в четвертом столбце).
- Последний аргумент отвечает за режим поиска: 0 — точный поиск, 1 — поиск ближайшего наименьшего значения (для чисел). Причем 0 не подразумевается по умолчанию — нужно вводить его явно.
Привычно, знакомо и делается многими на автомате, не приходя в сознание. ОК.

Теперь посмотрим как то же самое можно вычислить с помощью новой функции ПРОСМОТРX (XLOOKUP) .

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

Сначала, для порядка, давайте озвучим официальный синтаксис. У нашей новой функции 6 аргументов:

=ПРОСМОТРX( искомое_значение ; просматриваемый_массив ; возвращаемый_массив ; [если_ничего_не_найдено] ; [режим_сопоставления] ; [режим_поиска] )

Выглядит немного громоздко, но последние три аргумента [в квадратных скобках] не являются обязательными (мы разберёмся с ними чуть позже). Так что, на самом деле, всё проще:
- Первый аргумент (искомое_значение) — что мы ищем («гречка» из ячейки H4)
- Второй аргумент (просматриваемый_массив) — диапазон ячеек, где мы ищем (столбец Товар в прайс-листе).
- Третий аргумент (возвращаемый_массив) — диапазон, откуда хотим получить результаты (столбец Цена в прайс-листе).
- По умолчанию используетсяточный поиск, т.е. не нужно это явно прописывать как в ВПР (последний нолик).
- Не нужно отсчитывать и задавать номер столбца (третий аргумент ВПР). В больших таблицах это бывает непросто (особенно с учетом наличия скрытых столбцов).
- Из предыдущего пункта автоматом следует, что вставка/удаление столбцов в прайс не ломают формулу (как было бы с ВПР).
- Нет проблемы«левого ВПР», когда нужно извлечь значение левее просматриваемого столбца (например, артикул в нашем случае) — просматриваемый и возвращаемый массивы в ПРОСМОТРX могут располагаться как угодно (даже на разных листах, в общем случае!)
- В общем и целом синтаксис гораздо проще и понятнее, чем у ВПР.
Также приятно, что ПРОСМОТРX отлично работает и в горизонтальном варианте без каких-либо доработок:

Раньше для этого нужно было использовать уже функцию ГПР (HLOOKUP) вместо ВПР (VLOOKUP) .

Перехват ошибок #Н/Д

Если искомое значение отсутствует в списке, то функция ПРОСМОТРX, как и ВПР, выдаёт знакомую ошибку #Н/Д (#N/A) :

Раньше для перехвата таких ошибок и замены их на что-нибудь более осмысленное применяли вложнную конструкцию из функций ЕСЛИОШИБКА (IFERROR) и ВПР (VLOOKUP) . Теперь же можно сделать всё «на лету», используя 4-й аргумент [если_ничего_не_найдено] нашей новой функции :

Приблизительный поиск

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

В старой ВПР за это отвечал последний аргумент [интервальный_просмотр] — если задать его равным 1, то ВПР переходила в режим поиска ближайшего наименьшего значения. В ПРОСМОТРХ за этот функционал отвечает 5-й аргумент [режим_сопоставления] :

Он может работать по четырём различным сценариям:
- 0 — точный поиск (это режим по-умолчанию)
- -1 — поиск предыдущего, т.е. ближайшего наименьшего значения (для 29 шт. товара это будет скидка 5%)
- 1 — поиск следующего, т.е. ближайшего наибольшего (для 29 шт. товара это будет уже 10% скидки)
- 2 — неточный поиск текста с использованием подстановочных символов
Если с первыми тремя вариантами тут всё более-менее понятно, то последний стоит прокомментировать дополнительно. Имеется ввиду ситуация, когда мы ищем значение, где помимо букв и цифр использованы подстановочные символы * (звёздочка = любое количество любых символов) и ? (вопросительный знак = один любой символ).

На практике это может использоваться, например, так:

Заметьте, что, например, капуста в прайс-листе и бланке заказа здесь записана по-разному, но ПРОСМОТРX всё равно её находит, т.к. ищем мы уже не просто капусту, а капусту с приклеенными в начале и конце звёздочками и четвёртый аргумент нашей функции равен 2.

Функция ВПР, кстати говоря, всегда умела такое «из коробки», так что особого преимущества у ПРОСМОТРX здесь нет. Но важен другой нюанс: функция ВПР при включенном приблизительном поиске (последний аргумент =1) строго требовала сортировки искомой таблицы по возрастанию. Новая функция прекрасно ищет ближайшее наибольшее или наименьшее и в неотсортированном списке.

Направление поиска

Если в таблице есть не одно, а несколько совпадений с искомым значением, то функция ВПР всегда выдает первое, т.к. ведёт поиск исключительно сверху-вниз. ПРОСМОТРX может искать и в обратном направлении (снизу-вверх) — за это отвечает последний 6-й её аргумент [режим_поиска] :

Благодаря ему, поиск первого и (главное!) последнего совпадения больше не представляет сложности — различие будет только в значении этого аргумента:

Раньше для поиска последнего совпадения приходилось неслабо шаманить с формулами массива и несколькими вложенными функциями типа ИНДЕКС, НАИБОЛЬШИЙ и т.п.

Резюме

Если вы дочитали до этого места, то выводы, я думаю, уже сделали сами 🙂 На мой взгляд, у Microsoft получилось создать очень достойного наследника легендарной функции ВПР, добавив мощи и красоты и сохранив, при этом, простоту и наглядность использования.

Минус же пока только в том, что эта функция в ближайшее время появится только у подписчиков Office 365. Пользователи standalone-версий Excel 2013, 2016, 2019 эту функцию не получат, пока не обновятся до следующей версии Office (когда она выйдет). Но, рано или поздно, эта замечательная функция появится у большинства пользователей — вот тогда заживём! 🙂

Ссылки по теме
- Как использовать функцию ВПР (VLOOKUP) для поиска и подстановки значений
- Левый ВПР
- Связка функций ИНДЕКС и ПОИСКПОЗ как аналог ВПР
Похожие публикации: