Poldet1.ru

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

Как применить функцию обратного поиска или поиска в Excel?

Используем функцию ВПР в Excel для поиска и отбора значений!

VPR dlay poiska 1 Используем функцию ВПР в Excel для поиска и отбора значений! Доброго времени суток друзья!

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

Думаю, вы согласитесь со мной, что необходимость найти и извлечь данные из одной таблицы в другую возникает очень часто. И если вы еще не знаете, как это сделать, я исправлю эту ситуацию к лучшему. Давайте рассмотрим множество эффективных приемов поиска информации в сочетании с функцией БПФ. Хотя вы знаете и работаете с этой функцией, статья все равно будет вам полезна, так как в ее использовании есть много нюансов, и важно хорошо понимать и знать ее.

Функция ВПР имеет темную сторону, это младшая сестра, которой не суждены лавры старшей — функция ГПР. Она является антиподом БПФ и используется для поиска по строкам, а это случается крайне редко. Если она является вашим основным инструментом, значит, вы где-то свернули не туда и должны вернуться назад.

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

Фильтрация данных

Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):

Список сотрудников

Для начала добавляем фильтр на таблицу (выбираем вкладку Главная ->Редактирование ->Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

Применение фильтра

Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

Фильтрация данных

В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, различные словоформы, числа и т.д.).
К примеру, чтобы показать все варианты фамилий, которые начинаются на букву «к» и содержат букву «в», то применим фильтр «к*в*» (т.е. фраза начинается на «к», затем идет произвольный текст, потом «в», а затем еще раз произвольный текст).
Или поиск по «п?т*» найдет фамилии с первой буквой «п» и третьей буквой «т» (т.е. фраза начинается на «п», затем идет один произвольный символ, затем «т», и в конце опять произвольный текст).

Читайте так же:
Как проверить, существует ли в книге сводная таблица?

Поиск последнего вхождения (инвертированный ВПР)

Все классические функции поиска и подстановки, такие как VLOOKUP, HLOOKUP, MATCH и им подобные, обладают одним важным свойством — они осуществляют поиск от начала до конца, то есть слева направо или сверху вниз по исходным данным. Как только найдено первое совпадение, поиск прекращается и находится только первое вхождение нужного элемента.

Можно ли найти последнюю запись не первой? Среди прочего, последняя транзакция для клиента, последний платеж, последний запрос и т.д. ?

Способ 1. Поиск последней строки формулой массива

Если в исходной таблице нет столбца с датой или номером строки (заказ, платеж. ), наша задача — найти последнюю строку, удовлетворяющую заданному условию. Это можно сделать с помощью данной матричной формулы:

Формула массива для поиска последнего вхождения

  • Функция IF проверяет все ячейки в столбце Customer и печатает номер строки, если в ней содержится искомое имя. Номер строки в электронной таблице задается функцией ROW(ROW) , но поскольку нам нужен номер строки в таблице, нам нужно вычесть 1, так как у нас есть заголовок в таблице. Затем функция
  • MAX(MAX) выбирает из сгенерированного набора номеров строк максимальное значение, которое является номером последней строки клиента.
  • Функция INDEX(INDEX) выводит содержимое ячейки с последним номером, найденным в другом требуемом столбце таблицы (код заказа).

Все это следует ввести как формулу массива, т.е:

  • В Office 365 с последними обновлениями и поддержкой динамических массивов можно просто нажать Enter.
  • Во всех других версиях после ввода формулы необходимо нажать Ctrl + Shift + Enter, что автоматически добавит круглые скобки в строку формул.

Способ 2. Обратный поиск новой функцией ПРОСМОТРХ

В начале этого года я написал длинную статью с видео о новой функции XLOOKUP, которая заменила старую ВПР (VLOOKUP) в последних версиях Office. С помощью PROSLOOKUP наша задача решается довольно элементарно, ведь для этой функции (в отличие от ВПР) можно явно указать направление поиска: сверху вниз или снизу вверх — за это отвечает ее последний аргумент (-1):

Читайте так же:
Как преобразовать разницу во времени между двумя значениями времени в число (часы / минуты / секунды) в Excel?

Обратный поиск с ПРОСМОТРХ

Способ 3. Поиск строки с последней датой

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

Как это сделать с помощью классических функций я уже подробно описал, а теперь попробуем использовать мощь новых функций динамических массивов. Исходная таблица для большего удобства тоже преобразуется в «умную» при использовании Ctrl + T или Home — Format as Table (Home — Format as Table).

Эта «убийственная пара» делает нашу задачу весьма приятной:

Обратный поиск на динамических массивах в Excel

  • Сначала функция ФИЛЬТР(FILTER) отбирает только те строки из нашей таблицы, где в столбце Клиент — нужное нам имя.
  • Потом функция СОРТ(SORT) сортирует отобранные строки по убыванию даты, чтобы самая последняя сделка оказалась сверху.
  • Функция ИНДЕКС(INDEX) извлекает первую строку, т.е. выдает нужную нам последнюю сделку.
  • И, наконец, внешняя функция ФИЛЬТР убирает из результатов лишние 1-й и 3-й столбцы (Код заказа и Клиент) и оставляет только дату и сумму. Для этого используется массив констант <0;1;0;1>, определяющий какие именно столбцы мы хотим (1) или не хотим (0) выводить.

Способ 4. Поиск последнего совпадения в Power Query

И для полноты картины давайте посмотрим, как решить нашу проблему обратного поиска с помощью дополнения Power Query. Он решает все очень быстро и красиво.

1 . Преобразуйте нашу исходную таблицу в смарт-таблицу с помощью Ctrl+T или Главная — Форматы как таблица.

2 . Используйте кнопку From Table/Range на вкладке Data (Data — From Table/Range), чтобы загрузить его в Power Query.

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

Читайте так же:
Как разрешить ввод только да или нет в Excel?

4 . На вкладке Преобразование выберите Преобразование — Группировать по и определите группировку по клиентам, а в качестве функции агрегирования выберите Все строки. Вы можете назвать новый столбец как угодно, например, Details.

Группировка строк в Power Query

С помощью группировки мы получим список уникальных имен клиентов, а в колонке details — таблицы со всеми их транзакциями, где первая строка — это самая последняя нужная нам транзакция:

Результаты группировки

5 . Добавьте новый вычисляемый столбец с помощью кнопки Добавить столбец на вкладке Добавить столбец и введите следующую формулу:

Добавляем вычисляемый столбец для извлечения 1 строки

Здесь Подробности — это столбец, откуда мы берем таблицы по клиентам, а — это номер строки, которую мы хотим извлечь (нумерация строк в Power Query начинается с нуля). Получаем столбец с записями ( Record ), где каждая запись — первая строка из каждой таблицы:

Полученные записи

По-прежнему можно развернуть содержимое всех записей, используя кнопку с двойной стрелкой в заголовке столбца «Последняя транзакция» и выбрав нужные столбцы:

Разворачиваем записи в столбцы

. а затем удалите ненужный столбец Подробности, щелкнув правой кнопкой мыши на его заголовке — Удалить столбцы.

После загрузки результатов через Home — Close & Load — Close & Load to появится рабочая таблица. ‘(Главная — Закрыть и загрузить — Загрузить в. » Мы получаем красивую таблицу с последними сделками, как раз то, что нам было нужно:

Результаты запроса Power Query

При редактировании входных данных не забывайте обновлять результаты, щелкнув на них правой кнопкой мыши — команда Обновить или нажав Ctrl + Alt + F5.

Синтаксис

Функция VIEWX выполняет поиск в диапазоне или массиве, а затем возвращает элемент, соответствующий первому совпадению. Если совпадения нет, XLOOKUP может вернуть ближайшее (приблизительное) совпадение.

=PROSWERX(искомое_значение; просмотренный_массив; возвращенный_массив; [если_ничего_не_найдено]; [режим_соответствия]; [режим_ поиска])

Научная ценность.

* Если этот пробел опущен, поиск производится в ячейках, найденных в lookup_array, lookup_array.

Матрица видимая

Таблица или область, в которой можно осуществлять поиск

Таблица возвратов.

Таблица или диапазон возвращенных данных

[если_нет_финансирования]

Он возвращает if_not_found, если не найдено ни одного правильного соответствия.

Если правильное соответствие не найдено и [if_not_found] отсутствует, возвращается #N/D.

Укажите тип корреспонденции :

0 . Точное совпадение. Если ничего не найдено, #N/A. По умолчанию параметр имеет это значение.

-1 — это точное совпадение. Если ничего не найдено, возвращается следующий меньший элемент.

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

1. точное совпадение. Если ничего не найдено, возвращается следующий больший элемент.

2. Узурпаторская переписка, где *, ?

[режим поиска].

Определяет режим поиска:

1. поиск с первого пункта. Этот параметр используется по умолчанию.

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

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

-2. Выполняет бинарный поиск, который зависит от сортировки lookup_array в порядке убывания. Если сортировка не была выполнена, возвращаются недопустимые результаты.

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

Этот инструмент является простой и прямой альтернативой функции БПФ в Excel. Это работает следующим образом:

  1. Выберите свой главный стол. Допустим, это таблица с данными о продажах.
  1. Выберите таблицу поиска. Это может быть список менеджеров, назначенных отдельным клиентам.
  1. Выберите один или несколько общих столбцов в качестве уникальных идентификаторов. В нашем случае это будет имя и фамилия клиента.
  1. Укажите столбцы, которые необходимо обновить. В данном случае ничего. Пропустите этот шаг.
  2. Выберите столбцы, которые вы хотите добавить, если хотите. Добавьте к основной таблице столбец с именами менеджеров, работающих с данным клиентом. Не имеет значения, где должны быть добавлены эти колонки: слева или справа от колонки поиска.
  1. На следующем этапе можно задать дополнительные параметры слияния — выделить добавленное цветом, добавить противоречивые значения в конец основной таблицы, вставить колонку состояния и т.д. В нашем случае, однако, в этом нет необходимости. В конце концов, мы просто хотим найти и добавить имена менеджеров в основную таблицу. Поэтому мы просто нажимаем кнопку Finish. Теперь дайте мастеру слияния таблиц несколько секунд на обработку….. и наслаждайтесь результатом 🙂

На самом деле, именно это и делает функция БПФ — она выбирает из таблицы поиска значения, соответствующие значениям в основной таблице. Но в данном случае мы прекрасно обошлись без формул.

Чтобы узнать больше об инструменте слияния таблиц, щелкните здесь.

Несколько дополнительных материалов об УПО:

Читайте так же:
Как просмотреть следующее по величине значение в Excel?

Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…

ВПР с несколькими таблицами

2 способа извлечь данные из разных таблиц при помощи ВПР. — Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном…
/> Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и…
Почему не работает ВПР в Excel? — Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…
ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами — ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска…
Формула ВПР в Excel — 22 факта, которые нужно знать. — В процессе работы в Excel часто возникает задача извлечения нужных данных из рабочих таблиц. Для этой цели в Excel предусмотрена формула ВПР (VLOOKUP в английском варианте). И хотя ВПР относительно…

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