Просмотр x excel как включить
Функция 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 использует массив подстановки и возвращаемый массив, в то время как функция ВПР использует один массив таблиц, за которым следует номер индекса столбца. Эквивалентная формула ВПР в этом случае будет: =ВПР(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: =СУММ(XLOOKUP(B3,B6:B10;E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Как это работает? Функция XLOOKUP возвращает диапазон, поэтому при вычислении формула выглядит следующим образом: =СУММ($E$7:$E$9) . Чтобы увидеть, как это работает, выделите ячейку с формулой XLOOKUP, похожей на эту, а затем выберите формулы > Formula Auditing > Evaluate Formula и нажмите кнопку «Оценить», чтобы выполнить вычисление.
Примечание: Благодарим MVP Microsoft Excel , Bill Jelen, for suggesting this example.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функция ПРОСМОТРX в Excel
Долгожданная функция ПРОСМОТРX (XLOOKUP) стала доступна пользователям Microsoft Excel.
О предстоящем появлении функции было объявлено еще в прошлом году. В начале этого года она стала постепенно появляться у пользователей.
Функцию сразу же окрестили новой версией ВПР.
К сожалению, функция доступна не всем. Только пользователи Office 365 могут ей воспользоваться.
Давайте разберемся, в чем суть функции. Начнем с синтаксиса:
ПРОСМОТРX(искомое_значение; просматриваемый_массив;
возвращаемый_массив; [если_ничего_не_найдено];
[режим_сопоставления]; [режим_поиска])
- искомое значение — то, что мы хотим найти в массиве данных;
- просматриваемый массив — строка или столбец, в которых мы будем искать наше значение. Сразу отличие от ВПР: функция ПРОСМОТРX работает и в вертикальных, и в горизонтальных таблицах;
- возвращаемый массив — строка или столбец, из которых мы возьмем результат. Важное отличие в том, что теперь возвращаемый массив (в отличие от ВПР/ГПР) может располагаться слева от просматриваемого массива;
- если ничего не найдено — [необязательный элемент функции]. Не обнаружив искомое значение в просматриваемом массиве Excel вернет нам ошибку #Н/Д. Если такой вариант нам не подходит, то вместо стандартной ошибки мы можем вывести что-то свое. Например: «не найдено» или «» — если мы хотим видеть пустую ячейку;
- режим сопоставления — [необязательный элемент функции]. По умолчанию функция производит точное сопоставление (в ВПР для этого нам нужно было ставить 0). Теперь можно выбирать один из вариантов:
Соответствие с учетом подстановочных знаков позволяет указать только часть строки, заменив на ? любой отсутствующий символ или на * — несколько отсутствующих символов.
- режим поиска — [необязательный элемент функции]. Позволяет указать, что поиск должен идти не от первого элемента к последнему, а в обратной последовательности:
Важное замечание: при написании функции последний символ — это латинская буква икс, а не русская ха.
Мы уже добавили данную функцию в наш курс «Продвинутый пользователь Excel» . Записывайтесь, будем разбираться вместе!
Расписание ближайших групп:
Читайте также:
Статистические функции в Excel
Автор: Sreda31 · Published 07.09.2018 · Last modified 11.09.2022
Основы работы с файлом формата Excel
Автор: Sreda31 · Published 02.11.2016 · Last modified 11.09.2022
Горячие клавиши Excel
Автор: Sreda31 · Published 19.09.2017 · Last modified 11.09.2022
Функция ПРОСМОТРX (XLOOKUP) вместо ВПР, ГПР и других функций в Excel
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Функция ВПР (VLOOKUP) скоро отпразднует юбилей 10 лет, что для временного цикла программного обеспечения сопоставимо с выходом на пенсию.
Перед разработчиками из Microsoft стояла задача подобрать достойную замену, и они не ударили в грязь лицом, выпустив функцию ПРОСМОТРX (XLOOKUP), которая представляет собой не только улучшенную версию ВПР, но ещё и замену ГПР, ИНДЕКС и ПОИСКПОЗ. Если хотите узнать о других полезных функциях Excel, то рекомендуем записаться на бесплатный онлайн-курс «Аналитика в Excel».
Пару слов про функцию ПРОСМОТРX
Прежде чем приступать к работе с функцией ПРОСМОТРХ, нужно учесть 3 момента:
1. Версию Excel: на данный момент, осень 2020 года, ПРОСМОТРX доступен только пользователям Office 365, хотя поговаривают, что эту брешь залатают в Standalone-версии Microsoft Office 2021.
2. Буква Х в названии функции – английская, поэтому велика вероятность ошибки при дотошном вводе; лучше всего использовать автозавершение ввода функций с помощью клавиши Tab.
3. Перебирать аргументы и диапазоны (не только в ПРОСМОТРХ, но и в других функциях) намного быстрее с зажатой клавишей Ctrl, чем с помощью ручного ввода точек с запятой.
Про атрибуты и возможности функции мы поговорим уже в боевых условиях, разбираясь с тем, что же всё-таки умеет делать ПРОСМОТРХ.
Левый ВПР? Я могу орудовать, где угодно!
Наверняка большинство из вас знает, что ВПР выдаёт ошибку, когда нужно забрать данные левее искомой ячейки. Для ПРОСМОТРХ это не проблема, ведь функция считает везде, где только заблагорассудится.
К примеру, на одном листе у нас имеется список сотрудников и годовые оклады, при этом нужно посчитать их бонусы, забрав желаемые данные со второго листа.
Загвоздка заключается в том, что столбец с процентами бонусов находится левее столбца с реквизитами сотрудников, по которым как раз и будет осуществляться поиск.
«Загвоздка?!» – удивляется ПРОСМОТРХ. «Да ещё чего!».
Специально используем «Мастер функций» комбинацией Shift+F3 (или кнопочка Fx слева от строки ввода формул), чтобы показать аргументы.
Искомое значение – это ячейка А2, то есть Gary Miller, с него мы начнём выводить бонусы.
Просматриваемый массив – это столбец с сотрудниками на втором листе, то есть где функция ищет искомое значение. Закрепляем абсолютными ссылками.
Возвращаемый массив – тот самый столбец бонусов, которые мы хотим достать. Тоже забираем его со второго листа, зафиксировав с помощью F4.
Если ничего не найдено – уникальный и необязательный аргумент, позволяющий задать любое значение при неудачных результатах поиска, например, пробел (“ “) или 0, можно пропустить.
Режим сопоставления – классическая указка Excel для поиска точных или приблизительных значения. В данном случае выбираем 0 – точный поиск.
Протягиваем результат в ячейке ниже и сразу обращаем внимание на 0 – это те сотрудники, которых ПРОСМОТРХ не обнаружил в списке.
Кстати, если изначально функция везде выдаёт вам нули, то нужно проверить формат ячеек и проставить там числовой
Чтобы посчитать годовой оклад, можно умножить наши результаты на показатели столбца В, то есть дописываем в формулу *B2:
Протягиваем и получаем максимальный бонус сотрудников.
Иди домой, ГПР!
Раз мы сказали о том, что ПРОСМОТРХ умеет работать в любых направлениях, то и с заменой ГПР, которая считает по строкам, тоже проблем не будет.
Заберём аналогичные данные для бонуса по сотрудникам, как делали ранее, но теперь обратимся к горизонтально развёрнутой табличке на другом листе. Естественно, строка с бонусом находится выше строки с сотрудниками, что в случае с обычным ГПР нам принесло бы ошибку. Сейчас же мы действуем по накатанной схеме:
Указываем ячейку с сотрудником. Затем выбираем диапазон с сотрудниками, в котором ПРОСМОТРХ находит искомое значение. Фиксируем по нажатию F4.
Далее указываем строку с бонусами, тоже фиксируем через F4.
Если ничего не найдено, ставим 0.
Точность поиска – тоже 0, точный поиск.
Получаем идентичный результат, который мы можем с чистой совестью перемножить на годовое жалованье сотрудников. Добавляем к формуле *B2:
В итоге мы должны получить те же самые значения, что и с прошлой таблицей. Тоже перемножаем их на годовой оклад, дабы узнать максимальный бонус.
Кажется, на нём были очки…
Представим, что необходимо достать все реквизиты сотрудника, но мы знаем лишь его фамилию (или какие-то другие отличительные знаки). Попробуем извлечь данные человека по фамилии Willard в отдельной ячейке.
Если мы начнём поиск с ячейки, содержащей одну только фамилию Willard, выберем столбец с сотрудниками в качестве искомого и возвращаемого массива, то… получим ошибку. В идеале наша формула должна выглядеть следующим образом, смотрите:
В первый аргумент, перед искомой ячейкой G2, дописываем “*”. Звёздочка – это служебный символ, которого мы сцепляем амперсандом (&) с ячейкой G2 и таким образом говорим программе, чтобы осуществлялся поиск не только Willard, но и всего остального содержимого ячейки до Willard. То есть первый аргумент у нас примет вид: «*»&G2
Второй аргумент – искомый массив, то есть столбец с именами: A2:A19
Третий аргумент – возвращаемый массив, то есть опять столбец с именами: A2:A19
Четвёртый аргумент – пропускаем
Пятый аргумент – крайне важный в нашем случае. Сейчас мы должны выбрать цифру 2, которая учитывает поиск по служебным символам, то есть обращает внимание на введённую звёздочку в первом аргументе
Вот мы и получили James Willard.
«Двойной ПРОСМОТРХ» или «Зависимые выпадающие списки»
Для создания зависимых выпадающих списков, то есть, когда по значению из одного списка открываются соответствующие значения другого, больше не нужно хитрых комбинаций с формулами. Хватит ПРОСМОТРХ и парочки списков.
Сперва добавим в нашу таблицу новый столбец под названием «Итоговый платёж», в котором будет осуществляться суммирование столбцов B и C.
Теперь добавим выпадающий список в ячейку G7.
Вкладка «Данные» – «Проверка данных». Тип данных – список.
В поле «Источник» указываем диапазон с нашими сотрудниками из столбца А. Жмём ОК.
Проверяем. Список работает.
Ниже, в ячейке G8, создаём второй выпадающий список, вот только в поле «Источник» указываем заголовки столбцов из шапки таблицы, то есть протягиваем строку от Годового оклада до Итогового платежа. Нажимаем ОК.
Тоже работает. Едем дальше.
Всё готово для встраивания двойного ПРОСМОТРХ.
Переходим в ячейку G9, хотя вы можете выбрать абсолютно любую ячейку.
Начинаем вводить формулу.
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Мы ищем имя из выпадающего списка выше, то есть ячейка G7 – это первый аргумент.
Затем выбираем диапазон сотрудников – это второй аргумент.
В качестве возвращаемого массива выбираем все остальные столбцы, потому что нам понадобятся все данные. И здесь – внимание!
На место третьего аргумента первой функции ПРОСМОТРХ мы пишем ещё один ПРОСМОТРХ. Уже в нём указываем первым аргументом ячейку F8 (там перебираются заголовки столбцов).
Второй аргумент – это шапка таблицы.
Третий аргумент – уже обозначенный диапазон всех столбцов, кроме сотрудников (разумеется, без шапки).
Обойдёмся без поразительной точности и закрываем формулу.
Готово. Теперь, в зависимости от выбранного сотрудника в первом списке, переключаются его показатели в различных столбцах из второго. Шикарно!
Как насчёт подсветки выбираемых имён?
В качестве бонуса можете прикрутить сюда условное форматирование, чтобы оно подсвечивало строку по выбранному имени. Мы же смотрим отчётность, это было бы крайне удобно!
Если мы должны найти строку по определённому показателю, то, естественно, выбор падёт на функцию ПОИСКПОЗ. Искать мы будем позицию по имени сотрудника в ячейке F7 и подсвечивать ту строку таблицы, в которой это имя/фамилия находится. Получится очень эффектно.
Обводим всю нашу таблицу. Переходим на вкладку «Главная», потом «Условное форматирование», затем «Создать правило».
Выбираем «Использовать формулу для определения форматируемых ячеек».
Сперва зададим формат. Зальём каким-нибудь цветом. Окрасим шрифт. Должно выйти неплохо. Теперь переходим к прописыванию формулы.
Обратите внимание, что в первом аргументе (искомое значение) мы фиксируем только столбец, поскольку нам нужно, чтобы функция перемещалась только по строкам.
$F$7 – наша зафиксированная ячейка с именем из выпадающего списка.
0 – просматриваемый массив, то есть ПОИСКПОЗ находит первое значение, равное искомому.
Нажимаем ОК и ОК далее.
Переключаем имена и любуемся подсветкой.
ПРОСМОТРХ вместо задания условий
Возникла ситуация, когда следует начислить определённые бонусы в зависимости от жалования сотрудников. Отдельная маленькая табличка уже находится на листе. В одном столбце – ориентировочное жалование, в другом – бонусы в процентах.
Если зарплата равна 10 000 – никакого бонуса, 30 000 – 5% бонусом, 50 000 – 8% бонусом и так далее. Определим, кому какой бонус полагается с помощью ПРОСМОТРХ.
Создадим новый столбец «Новый бонус» на основном листе.
Пишем в первой ячейке нового столбца формулу:
Первым аргументом выбираем ячейку В2 из столбца с окладами сотрудников.
В качестве второго аргумента указываем столбец с жалованием из бонусной таблички с другого листа, то есть у нас будет Лист2!F13:$F$17.
Возвращаемый массив – уже бонусный проценты, то есть бонусный столбец из бонусной таблички с другого листа – Лист2!G13:$G$17.
Пропускаем аргумент, если мы ничего не находим, сейчас это необязательно.
И самое главное – пятый аргумент, который отвечает за точность поиска. Указываем там -1, то есть точное совпадение или следующее меньшее значение.
Теперь нужно проверить результаты.
Гари Миллеру досталось 10% бонусов. Идём на бонусную табличку и смотрим.
Его оклад составляет 60 000 – это 10%. Следующий оклад для бонусов уже 100 000 и 15% соответственно. Что сделал Excel: он нашёл 60 000 и затем отобрал следующее минимальное значение, то есть 10%.
Никаких условий и никаких подборов. Одна только функция ПРОСМОТРХ помогла нам решить целую вереницу задач.
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Загрузка...
