Функция ПРОСМОТРХ в Excel

Итак, в Excel добавили новую функцию. Раньше мы пользовались комбинацией функций ИНДЕКС и ПОИСКПОЗ или обычной функцией ПРОСМОТР. А теперь, если у вас лицензированный Office 365 по подписке, вы можете использовать функцию ПРОСМОТРХ. Она очень гибкая и удобная.

Сегодня я продемонстрирую вам много вариантов её использования.

Итак, начнём!

Функция ПРОСМОТРХ

ПРОСМОТРХ относительно новая функция, она доступна только в новейших версиях Excel.

Она очень похожа на функцию ПРОСМОТР, но с некоторыми изменениями.

Но что же она делает? Она быстро ищет значение в одном столбике, по заданным критериям, и возвращает значение из той же строки, но другого столбика.

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

Чуть позже, я покажу вам более интересные варианты использования.

Как использовать ПРОСМОТРХ?

Могу ли я использовать функцию ПРОСМОТРХ?

Сейчас эта функция доступна только тем, кто пользуется Office 365 по платной подписке.

Поэтому, если вы пользуетесь другими версиями — вариантов использовать эту функцию у вас нет.

Если у вас уже оформлена платная подписка на Office 365, но функции у вас все равно нет, вам нужно активировать Office Insider.

Как только вы активируете это, функция появится в вашей программе.

Синтаксис

Синтаксис функции:

=ПРОСМОТРХ(критерий, диапазон_поиска, диапазон_результатов, [значение_если_не_найдено, тип_соответствия, [тип_поиска])

Аргументы:

1. критерий — критерий, с которым сравниваются ячейки;
2. диапазон_поиска — диапазон ячеек, с которым будет сравниваться критерий;
3. диапазон_результатов — диапазон ячеек, из которого будет выбран результат;
4. [значение_если_не_найдено] — результат выполнения функции в том случае, если совпадения не найдены;
5. тип_соответствия — их несколько:
   • 0 — полное сравнение;
   • -1 — полное совпадение, но выбор именно наименьшего значения;
   • 1 — полное совпадение, но выбор именно наибольшего значения;
   • 2 — частичное совпадение, этого можно добиться с помощью операторов поиска;

• [тип_поиска] — их несколько:
  • 1 — стандартное значение (поиск сверху вниз);
  • -1 — снизу вверх;
  • 2 — бинарное сравнение, диапазон должен быть отсортирован по возрастанию;
  • -2 — бинарное сравнение, диапазон должен быть отсортирован по убыванию.

Варианты использования

Итак, в этой части статьи я покажу вам много различных вариантов использования функции.

Начнём!

Стандартный пример вывода значения

Итак, допустим, у нас есть такая табличка, нам нужно вывести оценку конкретного ученика по конкретному предмету:

Мы можем сделать это так:

=ПРОСМОТРХ(F2;A2:A15;B2:B15). (или XLOOKUP(F2,A2:A15,B2:B15) в англ. версии программы).

Готово!

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

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

Вот табличка, где необходимо «взять» результат из столбика левее:

А сделать это мы можем таким образом:

=ПРОСМОТРХ(F2;D2:D15;A2:A15). (или =XLOOKUP(F2,D2:D15,A2:A15) в англ. версии программы).

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

Вывод нескольких значений

Допустим, табличка у нас та же, но теперь нам нужно вывести сразу несколько значений:

Как это сделать:

=ПРОСМОТРХ(F2;A2:A15;B2:D15). (или =XLOOKUP(F2,A2:A15,B2:D15) в англ. версии программы).

Готово!

Эта функция массива, так что не получится удалить ячейки с оценками по второму и третьему предмету, не забывайте об этом!

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

Ранее приходилось писать адреса ячеек для всех формул отдельно, теперь, с этой новой функцией — все гораздо проще.

По нескольким критериям одновременно

Допустим, у нас есть следующая задача:

Итак, мы ищем имя в первом столбике, а название предмета в первой строчке. Если нам будет необходимо поменять что-либо в нашей функции (например, название предмета) никаких проблем не будет. Мы буквально за пару секунд сделаем это и получим правильный результат.

Вот формула:

=ПРОСМОТРХ(G1;B1:D1;ПРОСМОТРХ(F2;A2:A15;B2:D15))

Что здесь происходит?

Сначала мы получаем массив со всеми оценками конкретного ученика.

В нашем случае это {21,94,81}.

А затем вызываем функцию ПРОСМОТРХ еще раз и отдаем ей эти значения.

Если имя предмета математика, то в результат выполнения отдается первое значение из массива данных, созданного на прошлом этапе.

Очень похоже на использование комбинации функций ИНДЕКС и ПОИСКПОЗ.

Если ничего не подошло под критерий

Также большой плюс этой функции в том, что мы можем обрабатывать возникающую ошибку «не найдено».

Есть даже специальный аргумент [если_совпадение_не_найдено].

Итак, допустим у нас есть такая табличка:

Наша задача состоит в том, чтобы вычислить оценку по математике, а если этого сделать не выйдет — вернуть определенную фразу. Давайте сделаем это!

Формула, для нашей задачи, примет такой вид:

=ПРОСМОТРХ(F2;A2:A15;B2:B15; "Did not appear"). (или =XLOOKUP(F2,A2:A15,B2:B15, "Did not appear") в англ. версии программы).

В этом примере мы указали значение руками, но можно также использовать ссылку на другую ячейку.

Поиск по нескольким диапазонам

Рассмотрим следующий пример.

Допустим, у нас есть такая табличка:

Нам нужно выполнить сравнение с нашим критерием сразу в нескольких диапазонах. Но как это сделать?

Итак, вот формула, которая будет сравнивать имя ученика сразу с двумя диапазонами ячеек:

=ПРОСМОТРХ(A12;A2:A8;B2:B8;ПРОСМОТРХ(A12;F2:F8;G2:G8)). (или =XLOOKUP(A12,A2:A8,B2:B8,XLOOKUP(A12,F2:F8,G2:G8)) в англ. версии программы).

Что мы сделали? В аргументе [если_совпадение_не_найдено] мы еще раз вызвали функцию ПРОСМОТРХ для сравнения с другим диапазоном. Все крайне просто.

Не могу сказать вам, сколько раз можно заново вызвать функцию, но я делал это более 10-ти раз и все прекрасно работало.

Найти последнее совпадение с критерием

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

Допустим, у нас есть такая табличка:

Задача состоит в том, что вывести значения для последнего принятого на работу человека.

Формула, которую мы будем использовать:

РОСМОТРХ(F1;$B$2:$B$15;$A$2:$A$15;;;-1). (или =XLOOKUP(F1,$B$2:$B$15,$A$2:$A$15,,,-1) в англ. версии программы).

А эта формула даст последнюю дату приема на работу, для каждого отделения компании:

=ПРОСМОТРХ(F1;$B$2:$B$15;$C$2:$C$15;;;-1). (или =XLOOKUP(F1,$B$2:$B$15,$C$2:$C$15,,,-1) в англ. версии программы).

Так как в новой функции у нас есть указание, каким же образом ему выполнять поиск (сверху вниз или наоборот), то никаких огромных формул или сложностей не будет, как это было в случае использования комбинации функций ИНДЕКС и ПОИСКПОЗ.

Неполное соответствие

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

Я расписывал вам их в разделе «Синтаксис».

Если вы выполняли такие задачи ранее, например, с помощью комбинации функций ИНДЕКС и ПОИСКПОЗ — вы знаете, что данные, в случае использования старых функций, должны быть отсортированы. Но если вы используете эту новую функцию — проблемы больше нет. Она сама в «бекенде» отсортирует их, вам больше не нужно думать об этом.

Итак, нам нужно вычислить комиссию продавца:

Формула примет такой вид:

=ПРОСМОТРХ(B2;$E$2:$E$6;$F$2:$F$6;0;-1)*B2. (или =XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,0,-1)*B2 в англ. версии программы).

Мы использовали тип поиска -1, таким образом поиск будет выполняться справа налево.

И о правильной сортировке можно забыть!

Изменение порядка горизонтального поиска

В прошлом примере мы рассмотрели вертикальный поиск, а что же с горизонтальным?

Тоже самое, вот пример:

Формула примет такой вид:

=ПРОСМОТРХ(B7;B1:O1;B2:O2). (или =XLOOKUP(B7,B1:O1,B2:O2) в англ. версии программы).

Ничего сложного!

Более сложный поиск (комбинации функций)

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

Итак, у нас есть такая табличка:

Задача состоит в том, чтобы найти ученика с максимальным количеством баллов, а также найти всех учеников кто набрал более 80 баллов.

Формула, в таком случае, примет следующий вид:

=ПРОСМОТРХ(МАКС(ПРОСМОТРХ(G1;$B$1:$D$1;$B$2:$D$15));ПРОСМОТРХ(G1;$B$1:$D$1;$B$2:$D$15);$A$2:$A$15). (или =XLOOKUP(MAX(XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15)),XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15),$A$2:$A$15) в англ. версии программы).

Сначала получаем массив всех оценок.

С помощью этой части функции: ПРОСМОТРХ (G1;$B$1:$D$1;$B$2:$D$15), эта часть формирует массив из всех оценок по математике. А далее используем функцию МАКС чтобы найти максимальную оценку.

А далее этот максимальный балл становит критерием: ПРОСМОТРХ (G1;$B$1:$D$1;$B$2:$D$15).

В общем-то и все!

А теперь выводим количество учеников, набравших более 80 баллов:

=СЧЁТЕСЛИ(ПРОСМОТРХ(G1;$B$1:$D$1;$B$2:$D$15);">80"). (или =COUNTIF(XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15),">80") в англ. версии программы).

Сначала создается массив данных с оценками, а после, результат, используется в функции СЧЁТЕСЛИ.

Операторы поиска

Тут все также как и в функциях ПРОСМОТР и ИНДЕКС вместе с ПОИСКПОЗ.

Почти также. Небольшая разница все-таки есть.

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

Допустим, у нас есть такая табличка:

Ищем капитализацию компании по ее неполному названию.

Формула примет такой вид:

=ПРОСМОТРХ("*"&D2&"*";$A$2:$A$11;$B$2:$B$11;;2). (или =XLOOKUP("*"&D2&"*",$A$2:$A$11,$B$2:$B$11,,2) в англ. версии программы).

Мы использовали оператор “*” и с помощью него показали нашей фунции, что в конце строки может быть несколько символов, которые мы не знаем. Соответственно, функция искала указанные слова вначале строки.

Если вы хотите использовать операторы поиска, вам нужно указать тип поиска «2». И никак иначе.

Вывод последнего значения

Так как теперь у нас есть возможность изменять порядок поиска (снизу вверх), мы можем также найти последнее значение в диапазоне поиска.

Допустим, у нас есть такая табличка:

 Наша задача заключается в том, чтобы вывести имя последней компании в списке и ее капитализацию.

Формула для получения имени примет такой вид:

=ПРОСМОТРХ("*";A2:A11;A2:A11;;2;-1). (или =XLOOKUP("*",A2:A11,A2:A11,,2,-1) в англ. версии программы).

А эта формула вывода капитализации:

=ПРОСМОТРХ("*";A2:A11;B2:B11;;2,-1). (или =XLOOKUP("*",A2:A11,B2:B11,,2,-1) в англ. версии программы).

Так как мы снова используем оператор поиска, не забудьте установить тип поиска — «2».

Так как мы изменили порядок поиска на «снизу вверх», мы получаем первое значение с начала поиска, а поиск выполняется снизу вверх. Таким образом, мы получаем последнее значение.

Как быть, если функции ПРОСМОТРХ у вас нет?

На данный момент единственный вариант — купить платную подписку на Office 365.

Если вы уже сделали это, то просто активируйте Office Insider.

Щелкните на «Файл» и там увидите «Office Insider».

К сожалению, других вариантов использовать эту прекрасную функцию на данный момент нет. Придется пользоваться ПРОСМОТР и ИНДЕКС с ПОИСКПОЗ.

Минус функции ПРОСМОТРХ

Как это говорят, функция не имеет совместимости с другими версиями Excel.

Суть в том, что если вы используете эту функцию в вашей табличке, а после открываете эту табличку в других версиях Excel — он просто покажет вам ошибки. Так как не поймет что за функцию вы пытаетесь вызвать.

Функция довольно новая, поэтому её еще много где нет. Придется подождать прежде чем она будет у всех «в ходу». Хоть она и крайне удобна уже сейчас.

Итак, мы рассмотрели большое количество примеров, а также разобрали плюсы и минусы функции!

Надеюсь, эта статья была вам полезна!