Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выполнить vlookup и вернуть целую / целую строку сопоставленного значения в Excel?

Функция ПРОСМОТРX (XLOOKUP) вместо ВПР, ГПР и других функций в Excel

Вы сможете использовать все прикладные инструменты MS Excel.

V LOOKUP, функция VRP, скоро отметит свое десятилетие, что для программного цикла эквивалентно выходу на пенсию.

Перед разработчиками Microsoft стояла задача найти подходящую замену, и они справились с этой задачей, выпустив функцию PROSWER (XLOOKUP), которая заменила не только GPR и INDEX, но и GPR, INDEX и SEARCH. Если вы хотите узнать о других полезных функциях Excel, рекомендуем записаться на бесплатный онлайн-курс «Аналитика в Excel».

Пару слов про функцию ПРОСМОТРX

Прежде чем приступить к работе с функцией ПРОСМОТР, следует помнить о трех моментах:

Версия Excel: В настоящее время, осенью 2020 года, PROSWERX доступен только для пользователей Office 365, хотя планируется устранить этот пробел в автономной версии Microsoft Office 2021.

Поскольку имя функции начинается с английской буквы, велика вероятность неправильного ввода; лучше всего использовать клавишу Tab, чтобы повторно ввести имя функции.

3. Гораздо быстрее просматривать аргументы и диапазоны (не только в PROCESS, но и в других функциях), нажимая клавишу Ctrl, чем вводить точки с запятой вручную.

Мы поговорим об атрибутах и возможностях функции в боевых условиях, выясним, что может PROSWER.

Левый ВПР? Я могу орудовать, где угодно!

Большинство из вас, вероятно, знают, что VPR выдает ошибку, когда нужно взять данные слева от искомой ячейки. Это не проблема для PROSWER, потому что функция считает там, где хочет.

пример данных на одном листе

Один лист содержит имена и зарплаты сотрудников, а второй — данные, на основе которых должны быть рассчитаны премии.

Читайте так же:
Как вставить определенное количество строк через фиксированные интервалы в Excel?

данные на другом листе

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

» Заминка? !» — удивился PROSWATCH. «О, да, это так!»

Используйте комбинацию «Мастер функций» Shift+F3 (или кнопку Fx слева от строки ввода формулы), чтобы вывести аргументы.

используем функцию ПРОСМОТРХ

Значение, которое мы ищем, находится в ячейке A2, это Gary Miller, с которой мы начинаем выплачивать премию.

Отображаемый массив является столбцом персонала во втором листе, в котором функция ищет искомое значение. Безопасно при наличии абсолютных ссылок.

Возвращаются массивы в виде столбцов с бонусами, которые мы хотели бы получить. Мы также берем его со второго листа и исправляем с помощью F4.

Если ничего не найдено, это единственный необязательный аргумент, который позволяет дать любое значение при неудачном поиске, например, может быть опущен пробел (» «) или 0.

Режим соответствия — это классический указатель Excel для поиска точных или приблизительных значений. В данном случае мы выбираем 0 — точный поиск.

Обратите внимание на результат в следующей ячейке, который будет равен 0 — это сотрудники, которых PROSWATCH не нашел в списке.

Кстати, если изначально функция везде выдает нули, то следует проверить формат ячеек и поставить туда числительное

Чтобы рассчитать годовую зарплату, разделите наши результаты на цифры в колонке B, то есть подставьте в формулу *B2:

Предоставьте своим сотрудникам максимальную премию.

Иди домой, ГПР!

Поскольку мы уже говорили, что PROSWATCH может работать во всех направлениях, заменить счетчик PEA на диапазон также не составит труда.

замена гпр на ПРОСМОТРХ

Здесь приведены аналогичные данные о премиях для сотрудников, как и ранее, но теперь мы обратимся к горизонтально развернутой таблице на другом листе. В результате строка с премией оказалась выше строки с сотрудником, что в стандартной MRS было бы ошибкой. Теперь действуем по накатанной схеме:

Читайте так же:
Как выполнить трехсторонний поиск в Excel?

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

Затем укажите строку с бонусами, также задаваемую с помощью F4.

Если ничего не найдено, то ставим 0.

Точность поиска также равна 0, что означает, что поиск корректен.

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

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

Кажется, на нём были очки…

Например, предположим, что нам нужно получить все данные о конкретном сотруднике, но мы знаем только его фамилию (или какую-то другую отличительную черту) Мы хотели бы получить информацию о человеке по имени Уиллард в другую ячейку.

извлечение данных

В данном случае, начиная с ячейки, содержащей только фамилию Willard, выбирая столбец employees в качестве результата поиска и возвращая массив. мы получаем ошибку. В идеале мы должны использовать эту формулу, см:

В первый аргумент, перед искомой ячейкой G2, дописываем “*”. Звёздочка – это служебный символ, которого мы сцепляем амперсандом (&) с ячейкой G2 и таким образом говорим программе, чтобы осуществлялся поиск не только Willard, но и всего остального содержимого ячейки до Willard. То есть первый аргумент у нас примет вид: «*»&G2

Второй аргумент — таблица для поиска, т.е. имена колонок: A2:A19

Третий аргумент — возвращаемый массив, который снова представляет собой столбец с именами: A2:A19

Четвертый аргумент — оставьте его в стороне.

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

Читайте так же:
Как вывести список всех листов и переключить лист с помощью ярлыков в Excel?

Ото Джеймс Уиллард

«Двойной ПРОСМОТРХ» или «Зависимые выпадающие списки»

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

Давайте добавим в нашу таблицу новый столбец под названием «Total Payment», который будет суммировать столбцы В и С.

Теперь добавьте раскрывающийся список в ячейке G7.

Вкладка «Данные» — «Валидация данных». Тип данных — список.

Пожалуйста, укажите диапазон в столбце A с нашими сотрудниками. Нажмите OK.

Проверьте. Список работает.

Ниже, в ячейке G8, мы создаем второй выпадающий список, но в поле «Источник» указываем заголовки столбцов из шапки таблицы, то есть проводим линию от Годовой зарплаты до Итоговой выплаты. Нажмите OK.

Это тоже работает. Давайте продолжим.

Все готово к монтажу двойной системы PROSWER.

Перейдите к ячейке G9, но вы можете выбрать любую ячейку.

Начинайте вводить формулу.

Узнайте, как использовать все инструменты для применения функциональности MS Excel.

В раскрывающемся списке выше ячейка G7 является первым аргументом.

Вы можете выбрать круг сотрудников — это второй аргумент.

Выберите все остальные столбцы в качестве таблицы возврата, так как нам понадобятся все данные. А здесь — осторожно!

Подставив в третий аргумент PROSWATCH другой PROSWATCH, мы получим еще один PROSWATCH. Первый аргумент первой функции — F8 (именно здесь происходит поиск заголовков столбцов).

Во-вторых, передается шапка таблицы.

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

Давайте откажемся от поразительной точности и закроем формулу.

Это сделано. Теперь, в зависимости от выбранного в первом списке сотрудника, он меняет свои показатели в разных колонках второго списка. Отлично!

Читайте так же:
Как вставить разрыв страницы каждые x строк в Excel?

Как насчёт подсветки выбираемых имён?

В качестве бонуса здесь можно добавить условное форматирование, чтобы строка была выделена выбранным именем. Мы изучаем отчеты, это было бы очень полезно!

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

Обойдите наш стол. Перейдите на вкладку «Главная», затем на «Условное форматирование» и, наконец, на «Создать правило».

Чтобы задать форматированные ячейки, выберите «Использовать формулу. «.

Сначала установите формат. Заполните цветом. Цвет шрифта. Это должно выглядеть хорошо. Теперь перейдем к написанию формулы.

Мы фиксируем столбец только в первом аргументе (желаемое значение), поскольку хотим, чтобы функция перемещалась только по строкам.

F$7 — это наша фиксированная ячейка с названием выпадающего списка.

0 — это просматриваемый массив, т.е. ПОИСК возвращает ПЕРВОЕ значение, равное искомому.

Нажимаем ОК и ОК далее.

Имена были изменены, а изюминкой восхищались.

ПРОСМОТРХ вместо задания условий

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

Если зарплата 10 000 — премии нет, 30 000 — премия 5%, 50 000 — премия 8% и т.д. Мы используем систему PROSWATCH, чтобы решить, кто имеет право на какую премию.

Создайте новую колонку «Новая премия» в основном листе.

Введите формулу в первой ячейке нового столбца:

В качестве первого аргумента выберите ячейку B2 из столбца с зарплатами сотрудников.

В качестве второго аргумента укажите столбец, содержащий зарплату таблицы премий другого листа, т.е. у нас это будет Sheet2!F13:$F$17.

Читайте так же:
Как выбрать один и тот же диапазон ячеек на разных листах в Excel?

Возвращаемая матрица — это уже процент бонуса, т.е. бонусный столбец таблицы бонусов другого листа — Sheet2!G13:$G$17.

Мы пропускаем спор, если ничего не находим, в данный момент это не нужно.

И самый важный, пятый аргумент, который отвечает за релевантность поиска. Здесь мы указываем -1, т.е. точное совпадение, или другое меньшее значение.

Теперь необходимо проверить результаты.

Гэри Миллер получает премию в размере 10%. Давайте перейдем к таблице бонусов и посмотрим на нее.

Их зарплата составляет 60 000, что составляет 10%. Следующая зарплата для премий составляет уже 100 000 и 15% соответственно. Что сделал Excel: он нашел 60 000, а затем выбрал следующее меньшее значение, т.е. 10%.

Никаких условий и никаких подборов. Сама по себе функция PROSWATCH помогла нам решить ряд проблем.

Узнайте, как использовать все инструменты для применения функций MS Excel.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector