Функция ПРОСМОТР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 (когда она выйдет). Но, рано или поздно, эта замечательная функция появится у большинства пользователей — вот тогда заживём! 🙂
Функция ПРОСМОТРХ в Excel
Итак, в Excel добавили новую функцию. Раньше мы пользовались комбинацией функций ИНДЕКС и ПОИСКПОЗ или обычной функцией ПРОСМОТР. А теперь, если у вас лицензированный Office 365 по подписке, вы можете использовать функцию ПРОСМОТРХ. Она очень гибкая и удобная.
Сегодня я продемонстрирую вам много вариантов её использования.
Функция ПРОСМОТРХ
ПРОСМОТРХ относительно новая функция, она доступна только в новейших версиях Excel.
Она очень похожа на функцию ПРОСМОТР, но с некоторыми изменениями.
Но что же она делает? Она быстро ищет значение в одном столбике, по заданным критериям, и возвращает значение из той же строки, но другого столбика.
Например, мы имеем табличку продавцов и нам нужно вычислить имя продавца, который продал больше всех. Мы можем легко сделать это с помощью функции ПРОСМОТРХ.
Чуть позже, я покажу вам более интересные варианты использования.
Как использовать ПРОСМОТРХ?
Могу ли я использовать функцию ПРОСМОТРХ?
Сейчас эта функция доступна только тем, кто пользуется Office 365 по платной подписке.
Поэтому, если вы пользуетесь другими версиями — вариантов использовать эту функцию у вас нет.
Если у вас уже оформлена платная подписка на Office 365, но функции у вас все равно нет, вам нужно активировать Office Insider.
Как только вы активируете это, функция появится в вашей программе.
Синтаксис
- критерий — критерий, с которым сравниваются ячейки;
- диапазон_поиска — диапазон ячеек, с которым будет сравниваться критерий;
- диапазон_результатов — диапазон ячеек, из которого будет выбран результат;
- [значение_если_не_найдено] — результат выполнения функции в том случае, если совпадения не найдены;
- тип_соответствия — их несколько:
- 0 — полное сравнение;
- -1 — полное совпадение, но выбор именно наименьшего значения;
- 1 — полное совпадение, но выбор именно наибольшего значения;
- 2 — частичное совпадение, этого можно добиться с помощью операторов поиска;
- [тип_поиска] — их несколько:
- 1 — стандартное значение (поиск сверху вниз);
- -1 — снизу вверх;
- 2 — бинарное сравнение, диапазон должен быть отсортирован по возрастанию;
- -2 — бинарное сравнение, диапазон должен быть отсортирован по убыванию.
Варианты использования
Итак, в этой части статьи я покажу вам много различных вариантов использования функции.
Стандартный пример вывода значения
Итак, допустим, у нас есть такая табличка, нам нужно вывести оценку конкретного ученика по конкретному предмету:
Мы можем сделать это так:
Почему же функция ПРОСМОТРХ более удобная? Например, в обычной функции ПРОСМОТР, значение выбирается из столбика правее, а в функции ПРОСМОТРХ вы можете указать все эти параметры, которые в прошлой функции указывать нельзя, так как они подставляются автоматически.
Таким образом, теперь мы без проблем можем выводить значения из столбика слева от диапазона поиска. С помощью функции ПРОСМОТР мы так делать не могли, для этого нам нужно было использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ.
Вот табличка, где необходимо «взять» результат из столбика левее:
А сделать это мы можем таким образом:
Также новая функция обновляет свои ссылки и если вы переместите какой-либо столбик, который был использован в расчетах — ничего не сломается. Если мы ранее использовали функцию ПРОСМОТР, нам всегда приходилось переписывать все формулы.
Вывод нескольких значений
Допустим, табличка у нас та же, но теперь нам нужно вывести сразу несколько значений:
Как это сделать:
Эта функция массива, так что не получится удалить ячейки с оценками по второму и третьему предмету, не забывайте об этом!
Но мы можем удалить саму ячейку, где была использована формула. Тогда удаляться все эти три оценки.
Ранее приходилось писать адреса ячеек для всех формул отдельно, теперь, с этой новой функцией — все гораздо проще.
По нескольким критериям одновременно
Допустим, у нас есть следующая задача:
Итак, мы ищем имя в первом столбике, а название предмета в первой строчке. Если нам будет необходимо поменять что-либо в нашей функции (например, название предмета) никаких проблем не будет. Мы буквально за пару секунд сделаем это и получим правильный результат.
Что здесь происходит?
Сначала мы получаем массив со всеми оценками конкретного ученика.
В нашем случае это <21,94,81>.
А затем вызываем функцию ПРОСМОТРХ еще раз и отдаем ей эти значения.
Если имя предмета математика, то в результат выполнения отдается первое значение из массива данных, созданного на прошлом этапе.
Очень похоже на использование комбинации функций ИНДЕКС и ПОИСКПОЗ.
Если ничего не подошло под критерий
Также большой плюс этой функции в том, что мы можем обрабатывать возникающую ошибку «не найдено».
Есть даже специальный аргумент [если_совпадение_не_найдено].
Итак, допустим у нас есть такая табличка:
Наша задача состоит в том, чтобы вычислить оценку по математике, а если этого сделать не выйдет — вернуть определенную фразу. Давайте сделаем это!
Формула, для нашей задачи, примет такой вид:
В этом примере мы указали значение руками, но можно также использовать ссылку на другую ячейку.
Поиск по нескольким диапазонам
Рассмотрим следующий пример.
Допустим, у нас есть такая табличка:
Нам нужно выполнить сравнение с нашим критерием сразу в нескольких диапазонах. Но как это сделать?
Итак, вот формула, которая будет сравнивать имя ученика сразу с двумя диапазонами ячеек:
Что мы сделали? В аргументе [если_совпадение_не_найдено] мы еще раз вызвали функцию ПРОСМОТРХ для сравнения с другим диапазоном. Все крайне просто.
Не могу сказать вам, сколько раз можно заново вызвать функцию, но я делал это более 10-ти раз и все прекрасно работало.
Найти последнее совпадение с критерием
Такая возможность была невероятно необходима, ведь обычная функция ПРОСМОТР не давала таких возможностей. Но в этой новой функции можно сделать и такое.
Допустим, у нас есть такая табличка:
Задача состоит в том, что вывести значения для последнего принятого на работу человека.
Формула, которую мы будем использовать:
А эта формула даст последнюю дату приема на работу, для каждого отделения компании:
Так как в новой функции у нас есть указание, каким же образом ему выполнять поиск (сверху вниз или наоборот), то никаких огромных формул или сложностей не будет, как это было в случае использования комбинации функций ИНДЕКС и ПОИСКПОЗ.
Неполное соответствие
Итак, у функции ПРОСМОТРХ 4 режима совпадений (для сравнения, у функции ПРОСМОТР их было всего 2).
Я расписывал вам их в разделе «Синтаксис».
Если вы выполняли такие задачи ранее, например, с помощью комбинации функций ИНДЕКС и ПОИСКПОЗ — вы знаете, что данные, в случае использования старых функций, должны быть отсортированы. Но если вы используете эту новую функцию — проблемы больше нет. Она сама в «бекенде» отсортирует их, вам больше не нужно думать об этом.
Итак, нам нужно вычислить комиссию продавца:
Формула примет такой вид:
Мы использовали тип поиска -1, таким образом поиск будет выполняться справа налево.
И о правильной сортировке можно забыть!
Изменение порядка горизонтального поиска
В прошлом примере мы рассмотрели вертикальный поиск, а что же с горизонтальным?
Тоже самое, вот пример:
Формула примет такой вид:
Более сложный поиск (комбинации функций)
Это более сложный пример, здесь мы будем использовать комбинацию ПРОСМОТРХ с другими функциями.
Итак, у нас есть такая табличка:
Задача состоит в том, чтобы найти ученика с максимальным количеством баллов, а также найти всех учеников кто набрал более 80 баллов.
Формула, в таком случае, примет следующий вид:
Сначала получаем массив всех оценок.
С помощью этой части функции: ПРОСМОТРХ (G1;$B$1:$D$1;$B$2:$D$15), эта часть формирует массив из всех оценок по математике. А далее используем функцию МАКС чтобы найти максимальную оценку.
А далее этот максимальный балл становит критерием: ПРОСМОТРХ (G1;$B$1:$D$1;$B$2:$D$15).
В общем-то и все!
А теперь выводим количество учеников, набравших более 80 баллов:
Сначала создается массив данных с оценками, а после, результат, используется в функции СЧЁТЕСЛИ.
Операторы поиска
Тут все также как и в функциях ПРОСМОТР и ИНДЕКС вместе с ПОИСКПОЗ.
Почти также. Небольшая разница все-таки есть.
Она заключается в том, что в ПРОСМОТРХ вам необходимо указать тот тип поиска, который подразумевает в себе использование операторов поиска.
Допустим, у нас есть такая табличка:
Ищем капитализацию компании по ее неполному названию.
Формула примет такой вид:
Мы использовали оператор “*” и с помощью него показали нашей фунции, что в конце строки может быть несколько символов, которые мы не знаем. Соответственно, функция искала указанные слова вначале строки.
Если вы хотите использовать операторы поиска, вам нужно указать тип поиска «2». И никак иначе.
Вывод последнего значения
Так как теперь у нас есть возможность изменять порядок поиска (снизу вверх), мы можем также найти последнее значение в диапазоне поиска.
Допустим, у нас есть такая табличка:
Наша задача заключается в том, чтобы вывести имя последней компании в списке и ее капитализацию.
Формула для получения имени примет такой вид:
А эта формула вывода капитализации:
Так как мы снова используем оператор поиска, не забудьте установить тип поиска — «2».
Так как мы изменили порядок поиска на «снизу вверх», мы получаем первое значение с начала поиска, а поиск выполняется снизу вверх. Таким образом, мы получаем последнее значение.
Как быть, если функции ПРОСМОТРХ у вас нет?
На данный момент единственный вариант — купить платную подписку на Office 365.
Если вы уже сделали это, то просто активируйте Office Insider.
Щелкните на «Файл» и там увидите «Office Insider».
К сожалению, других вариантов использовать эту прекрасную функцию на данный момент нет. Придется пользоваться ПРОСМОТР и ИНДЕКС с ПОИСКПОЗ.
Минус функции ПРОСМОТРХ
Как это говорят, функция не имеет совместимости с другими версиями Excel.
Суть в том, что если вы используете эту функцию в вашей табличке, а после открываете эту табличку в других версиях Excel — он просто покажет вам ошибки. Так как не поймет что за функцию вы пытаетесь вызвать.
Функция довольно новая, поэтому её еще много где нет. Придется подождать прежде чем она будет у всех «в ходу». Хоть она и крайне удобна уже сейчас.
Итак, мы рассмотрели большое количество примеров, а также разобрали плюсы и минусы функции!
Функция ПРОСМОТРХ (XLOOKUP)
Старый синтаксис — ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возвращаемый_массив; [режим_сопоставления]; [режим_поиска] Новый синтаксис — ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возвращаемый_массив; [если_ничего_не_найдено];[режим_сопоставления]; [режим_поиска]
Описание функции ПРОСМОТРХ
Воспользоваться новыми возможностями смогут пользователи с подпиской Office 365, а в будущем пользователи Office 2021 (следующей версией, которая следует за Office 2019), если к тому времени Microsoft попросту не оставит только вариант с подпиской.
Данная функция своим функционалом полностью замещает возможности ВПР, ГПР, как и связку ИНДЕС + ПОИСКПОЗ, которая использовалась как замена ВПР, если нужно было обойти некоторые ее ограничения.
Синтаксис ПРОСМОТРХ
Аргументы
- 0 — точное совпадение. Если ничего не найдено будет возвращена ошибка #N/А. Данный параметр используется по умолчанию.
- -1 — точное совпадение. Если ничего не найдено, будет возвращено следующий меньший элемент.
- 1 — точное совпадение. Если ничего не найдено, будет возвращено следующий более крупный элемент.
- 2 — использование подстановочных знаков при поиске.
- 1 — стандартный поиск с начала списка. Данный параметр используется по умолчанию.
- -1 — обратный поиск с конца списка.
- 2 — двоичный поиск (поиск по числам). Список будет должен будет отсортирован по возрастанию
- -2 — двоичный поиск (поиск по числам). Список будет должен будет отсортирован по убыванию
Замечания
- Функция ВПР по умолчанию, возвращала приблизительное совпадение, если пользователь не передал необязательный параметр «интервальный_просмотр». По заявлению Microsoft, непредвиденные результаты вводили в заблуждение неопытных пользователей, ведь для приблизительного соответствия массив должен быть отсортирован. Новая функция по умолчанию возвращает точное соответствие, это такая особенность работы и ее следует учитывать.
- если в поле «режим_сопоставления» ввести значение 2, то в в поле «искомое_значение» можно будет использовать подстановочные знаки, где «?» — означает замену одного символа, а «*» — любое количество символов. На самом деле, по умолчанию передавать подстановочные знаки в ВПР можно, в новой функции для этого есть специальный переключатель. Скорее всего, это сделано по соображениям повышения производительности.
- Размерность массивов, которые передаются в параметры «просматриваемый_массив» и «возвращаемый_массив» должна быть одинаковой, иначе функция вернет ошибку #ЗНАЧ!. В случае использование ВПР (ГПР) и поиск и возврат значения осуществлялся по сути из одного массива, поэтому не правильно указать размерность попросту было невозможно, здесь же на это следует обратить внимание.
- ПРОСМОТРX как и ВПР, ищет первое значение в списке, если нужно найти все совпадения используйте функцию ФИЛЬТР
Основные преимущества перед ВПР (и ГПР)
Раз данная функция представлена как более совершенная замена такой популярной функции как ВПР (заодно и ГПР), будет правильно рассмотреть чем новая функция выгодно отличается от уже знакомых пользователям аналогов.
Одним из популярных недостатков ВПР — это поиск значения исключительно в первом столбце диапазона. Решение в виде изменения источника данных таким образом, чтобы искомый столбец находился первым рассматривать не правильно.
Есть решение в виде использования связки из функций ИНДЕКС и ПОИСКПОЗ. Единственным недостатком такого решения (если опустить вопрос производительности) является трудность в освоении для неподготовленного пользователя и относительная громоздкость решения. Громоздкость в последствии сможет негативно сказать на читаемости формул.
В функциях ВПР и ГПР столбцы в котором осуществляется поиск и с которого возвращается результат должны быть составляющими одного массива. Это негативно сказываться на производительности ведь в формулу всегда нужно передавать кучу данных, которая попросту не используется.
Справедливости ради, следует отметить, что Microsoft буквально за месяц перед анонсом новой функции произвела усовершенствование производительности алгоритмов ВПР (ГПР). Но это не отменяет того факта, что всегда нужно передавать «бесполезный» пласт информации, который, к тому же, может затруднить читаемость функций.
Возвращаемый массив в функцию должен передаваться числом, т.е. функция ВПР читается как «найти значение «А» в первом столбце и вернуть соответствующее ему значение из столбца «n», где n — это число, например, 3″.
Для больших таблиц это просто не удобно для пользователя высчитывать номер столбца с которого нужно вернуть результат, например, если вместо 3 будет 23 или 33. Но еще большее неудобство возникает, если в исходный диапазон будет добавлен еще столбец, или удален существующий. Если новый/удаленный столбец находятся между первым и искомым столбцом, придется изменить формулу.
В новой функции ПРОСМОТРX диапазон с которого возвращается результат указывается диапазоном и добавление/удаление столбцов никак не скажется на результатах вычисления формулы.
Использую ВПР вы сможете найти, например, сотрудника и вернуть его зарплату, если нужно еще вернуть подразделение в котором он работает, придется написать еще одну формулу. С помощью ПРОСМОТРX можно вернуть сразу же двумерный массив.
Опять таки, с в версиях Excel, с поддержкой динамических массивов, в ВПР тоже можно указать не просто номер столбца, а передать туда массив чисел и функция вернет несколько значений. Но это появилось с поддержкой динамических массивов.
Поскольку ПРОСМОТРX призвана заменить сразу 2 функции, то очевидно, что она должна работать с массивами любой ориентации.
Напомним, что первая буква в функциях ВПР и ГПР и обозначала как должен быть организован массив «В» — вертикально, «Г» — горизонтально.
В случае использования ВПР (ГПР) поиск всегда осуществлялся с начала списка и обойти это ограничение без переформатирования самого исходного диапазона было невозможно.
Теперь же поиск можно произвести с «конца списка», например, если список отсортирован по показателям продаж по дням, можно сразу же найти и лучший, и худший результат по определенному сотруднику.
В случае использования старых функций был вариант не точного поиска, если диапазон отсортирован и точного значения не находилось, то возвращалось максимальное значение, которое меньше искомого. В случае использование ПРОСМОТРX можно найти как значение меньше искомого, так и больше, если точного совпадения не будет.
Более того, предварительная сортировка в случае не точно соответствия не требуется, функция корректно отработает в любом случае.
Функция ПРОСМОТРX в Excel
Долгожданная функция ПРОСМОТРX (XLOOKUP) стала доступна пользователям Microsoft Excel.
О предстоящем появлении функции было объявлено еще в прошлом году. В начале этого года она стала постепенно появляться у пользователей.
Функцию сразу же окрестили новой версией ВПР.
К сожалению, функция доступна не всем. Только пользователи Office 365 могут ей воспользоваться.
Давайте разберемся, в чем суть функции. Начнем с синтаксиса:
ПРОСМОТРX(искомое_значение; просматриваемый_массив;
возвращаемый_массив; [если_ничего_не_найдено];
[режим_сопоставления]; [режим_поиска])- искомое значение – то, что мы хотим найти в массиве данных;
- просматриваемый массив – строка или столбец, в которых мы будем искать наше значение. Сразу отличие от ВПР: функция ПРОСМОТРX работает и в вертикальных, и в горизонтальных таблицах;
- возвращаемый массив – строка или столбец, из которых мы возьмем результат. Важное отличие в том, что теперь возвращаемый массив (в отличие от ВПР/ГПР) может располагаться слева от просматриваемого массива;
- если ничего не найдено – [необязательный элемент функции]. Не обнаружив искомое значение в просматриваемом массиве Excel вернет нам ошибку #Н/Д. Если такой вариант нам не подходит, то вместо стандартной ошибки мы можем вывести что-то свое. Например: “не найдено” или “” – если мы хотим видеть пустую ячейку;
- режим сопоставления – [необязательный элемент функции]. По умолчанию функция производит точное сопоставление (в ВПР для этого нам нужно было ставить 0). Теперь можно выбирать один из вариантов:
Соответствие с учетом подстановочных знаков позволяет указать только часть строки, заменив на ? любой отсутствующий символ или на * – несколько отсутствующих символов.
- режим поиска – [необязательный элемент функции]. Позволяет указать, что поиск должен идти не от первого элемента к последнему, а в обратной последовательности:
Важное замечание: при написании функции последний символ – это латинская буква икс, а не русская ха.
Мы уже добавили данную функцию в наш курс “Продвинутый пользователь Excel” . Записывайтесь, будем разбираться вместе!
Расписание ближайших групп:
Читайте также:
Удобная работа с длинными формулами
— Sreda31 · Published 23.03.2021 · Last modified 07.01.2023