Excel поискпоз в обратном порядке
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как "кузьмина" или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку "Перенос текста" (вкладка "Главная", группа "Выравнивание").
Все классические функции поиска и подстановки типа ВПР (VLOOKUP) , ГПР (HLOOKUP) , ПОИСКПОЗ (MATCH) и им подобные имеют одну важную особенность - они ищут от начала к концу, т.е. слева-направо или сверху-вниз по исходным данным. Как только находится первое подходящее совпадение - поиск останавливается и найденным оказывается только первое вхождение нужного нам элемента.
Что же делать, если нам требуется найти не первое, а последнее вхождение? Например, последнюю сделку по клиенту, последний платёж, самую свежую заявку и т.д.?
Способ 1. Поиск последней строки формулой массива
Если в исходной таблице нет столбца с датой или порядковым номером строки (заказа, платежа. ), то наша задача сводится, по сути, к поиску последней строки, удовлетворяющей заданному условию. Реализовать подобное можно вот такой формулой массива:
- Функция ЕСЛИ(IF) проверяет по очереди все ячейки в столбце Клиент и выводит номер строки, если в ней лежит нужное нам имя. Номер строки на листе нам даёт функция СТРОКА(ROW) , но поскольку нам нужен номер строки в таблице, то дополнительно приходится вычитать 1, т.к. у нас в таблице есть шапка.
- Затем функция МАКС(MAX) выбирает из сформированного набора номеров строк максимальное значение, т.е. номер самой последней строки клиента.
- Функция ИНДЕКС(INDEX) выдаёт содержимое ячейки с найденным последним номером из любого другого требуемого столбца таблицы (Код заказа).
Всё это нужно вводить как формулу массива, т.е.:
- В Office 365 с последними установленными обновлениями и поддержкой динамических массивов - можно просто жать Enter .
- Во всех остальных версиях после ввода формулы придется нажимать сочетание клавиш Ctrl + Shift + Enter , что автоматически добавит к ней фигурные скобки в строке формул.
Способ 2. Обратный поиск новой функцией ПРОСМОТРХ
Я уже писал большую статью с видео про новую функцию ПРОСМОТРХ (XLOOKUP) , которая появилась в последних версиях Office на замену старушке ВПР (VLOOKUP) . При помощи ПРОСМОТРХ наша задача решается совершенно элементарно, т.к. для этой функции (в отличие от ВПР) можно явно задавать направление поиска: сверху-вниз или снизу-вверх - за это отвечает её последний аргумент (-1):
Способ 3. Поиск строки с последней датой
Если в исходных данных у нас есть столбец с порядковым номером или датой, играющей аналогичную роль, то задача видоизменяется - нам требуется найти уже не последнюю (самую нижнюю) строку с совпадением, а строку с самой поздней (максимальной) датой.
Как это сделать с помощью классических функций я уже подробно разбирал, а теперь давайте попробуем использовать мощь новых функций динамических массивов. Исходную таблицу для пущей красоты и удобства тоже заранее преобразуем в "умную" с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) .
С их помощью этой "убойной парочки" наша задача решается весьма изящно:
- Сначала функция ФИЛЬТР(FILTER) отбирает только те строки из нашей таблицы, где в столбце Клиент - нужное нам имя.
- Потом функция СОРТ(SORT) сортирует отобранные строки по убыванию даты, чтобы самая последняя сделка оказалась сверху.
- Функция ИНДЕКС(INDEX) извлекает первую строку, т.е. выдает нужную нам последнюю сделку.
- И, наконец, внешняя функция ФИЛЬТР убирает из результатов лишние 1-й и 3-й столбцы (Код заказа и Клиент) и оставляет только дату и сумму. Для этого используется массив констант >, определяющий какие именно столбцы мы хотим (1) или не хотим (0) выводить.
Способ 4. Поиск последнего совпадения в Power Query
Ну, и для полноты картины, давайте рассмотрим вариант решения нашей задачи обратного поиска с помощью надстройки Power Query. С её помощью всё решается очень быстро и красиво.
1 . Преобразуем нашу исходную таблицу в "умную" с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) .
2 . Загружаем её в Power Query кнопкой Из таблицы/диапазона на вкладке Данные (Data - From Table/Range) .
3 . Сортируем (через выпадающий список фильтра в шапке) нашу таблицу по убыванию даты, чтобы самые последние сделки оказались сверху.
4 . На вкладке Преобразование выбираем команду Группировать по (Transform - Group By) и задаем группировку по клиентам, а в качестве агрегирующей функции выбираем вариант Все строки (All rows) . Назвать новый столбец можно как угодно - например Подробности.
После группировки получим список уникальных имен наших клиентов и в столбце Подробности - таблицы со всеми сделками каждого из них, где первой строкой будет идти самая последняя сделка, которая нам и нужна:
5 . Добавляем новый вычисляемый столбец кнопкой Настраиваемый столбец на вкладке Добавить столбец (Add column - Add custom column) и вводим следующую формулу:
Здесь Подробности - это столбец, откуда мы берем таблицы по клиентам, а - это номер строки, которую мы хотим извлечь (нумерация строк в Power Query начинается с нуля). Получаем столбец с записями ( Record ), где каждая запись - первая строка из каждой таблицы:
Осталось развернуть содержимое всех записей кнопкой с двойными стрелками в шапке столбца Последняя сделка, выбрав нужные столбцы:
. и удалить потом ненужный более столбец Подробности щёлкнув по его заголовку правой кнопкой мыши - Удалить столбцы (Remove columns) .
После выгрузки результатов на лист через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to. ) получим вот такую симпатичную таблицу со списком последних сделок, как и хотели:
При изменении исходных данных результаты нужно не забыть обновить, щёлкнув по ним правой кнопкой мыши - команда Обновить (Refresh) или сочетанием клавиш Ctrl + Alt + F5 .
Банальная, на первый взгляд, задача, периодически встречающаяся в работе почти любого пользователя Microsoft Excel – расположить элементы списка в обратном порядке. При всей кажущейся простоте, здесь есть свои "фишки" - давайте разберем несколько вариантов ее решения.
Способ 1. Ручная сортировка по доп.столбцу
Это обычно первое, что приходит в голову. Добавляем рядом с нашим списком еще один столбец с порядковыми номерами и сортируем по этому столбцу по убыванию:
| |
Очевидный плюс такого подхода в простоте. Очевидный же минус в том, что нужно руками проделать энное количество операций. Если это разовая задача - ОК, но если данные меняются каждый день, то сортировать список постоянно вручную уже напрягает. Выходом может стать использование формул.
Способ 2. Обратный порядок формулой
Поскольку формулы в Excel пересчитываются автоматически (если включен ручной режим пересчета), то и сортировка, реализованная формулами, будет происходить "на лету", без какого либо участия пользователя.
Нужная нам формула, размещающая элементы списка в обратном порядке может выглядеть так:
Недостаток этой формулы в том, что в ней должны жестко задаваться начало и конец списка (ячейки A2 и A9 в нашем случае). Если заранее точно не известно, сколько именно элементов будет в списке, то лучше использовать другой подход:
В этой формуле номер последней занятой ячейки подсчитывается с помощью функции СЧЁТЗ (COUNTA) , т.е. количество элементов в исходном списке может впоследствии меняться.
Минус этого варианта - в исходном списке не должно быть пустых ячеек, т.к. функция СЧЁТЗ тогда неправильно вычислит номер строки последнего элемента. Выходом может стать использование динамического именованного диапазона с автоподстройкой размеров либо хитрой формулы массива:
Как легко заметить, это вариация первого способа, где диапазон взят «с запасом» сразу до сотой строки и номер строки последней заполненной ячейки задается не жестко, а вычисляется с помощью фрагмента МАКС(($A$2:$A$100<>"")*СТРОКА($A$2:$A$100))
Каждая ячейка в диапазоне A2:A100 проверяется на заполненность с помощью выражения ($A$2:$A$100<>""), что даст на выходе массив значений ИСТИНА и ЛОЖЬ. Затем этот массив поэлементно умножается на массив номеров строк, получаемый с помощью функции СТРОКА($A$2:$A$100). Поскольку логическую ИСТИНУ Excel интерпретирует как 1, а ЛОЖЬ – как 0, то после умножения мы получим массив номеров заполненных ячеек. А уже из него функция МАКС (MAX) выбирает самое большое число, т.е. номер последней заполненной строки.
И, само-собой, не забудьте после ввода этой формулы нажать не обычный Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести ее как формулу массива.
Способ 3. Макрос
Если хочется реализовать перекладывание значений ячеек в обратном порядке без дополнительного столбца с формулами, т.е. прямо в исходных ячейках, то не обойтись без простого макроса.
Нажмите сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) . Вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст макроса:
Теперь, если выделить столбец-список с данными и запустить наш макрос с помощью сочетания Alt+F8 или команды Разработчик - Макросы (Developer - Macros) , то список развернется в обратном порядке прямо в тех же ячейках, т.е. на месте.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Совет: Попробуйте использовать новую функцию XMATCH , улучшенную версию функции MATCH, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.
Совет: Функцией ПОИСКПОЗ следует пользоваться вместо одной из функций ПРОСМОТР, когда требуется найти позицию элемента в диапазоне, а не сам элемент. Например, функцию ПОИСКПОЗ можно использовать для передачи значения аргумента номер_строки функции ИНДЕКС.
Синтаксис
Аргументы функции ПОИСКПОЗ описаны ниже.
Искомое_значение. Обязательный аргумент. Значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а нужным значением будет номер телефона.
Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.
Просматриваемый_массив Обязательный аргумент. Диапазон ячеек, в которых производится поиск.
Тип_сопоставления. Необязательный аргумент. Число -1, 0 или 1. Аргумент тип_сопоставления указывает, каким образом в Microsoft Excel искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. По умолчанию в качестве этого аргумента используется значение 1.
В приведенной ниже таблице описано, как функция находит значения в зависимости от аргумента тип_сопоставления.
Тип_сопоставления
Функция ПОИСКПОЗ находит наибольшее значение, которое меньше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА.
Функция ПОИСКПОЗ находит первое значение, равное аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.
Функция ПОИСКПОЗ находит наименьшее значение, которое больше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z — A, . 2, 1, 0, -1, -2, . и т. д.
Функция ПОИСКПОЗ возвращает не само значение, а его позицию в аргументе просматриваемый_массив. Например, функция ПОИСКПОЗ("б";а";"б";"в">;0) возвращает 2 — относительную позицию буквы "б" в массиве .
Функция ПОИСКПОЗ не различает регистры при сопоставлении текста.
Если тип_сопоставления равен 0 и искомое_значение является текстом, то искомое_значение может содержать подстановочные знаки: звездочку (*) и вопросительный знак (?). Звездочка соответствует любой последовательности знаков, вопросительный знак — любому одиночному знаку. Если нужно найти сам вопросительный знак или звездочку, перед ними следует ввести знак тильды (~).
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Ранее мы уже объясняли новичкам, как использовать базовые функции ВПР (англ. VLOOKUP, аббревиатура расшифровывается как “функция вертикального просмотра”). А опытным пользователям показали несколько формул посложнее.
А в этой статье мы постараемся дать информацию про другой метод работы с вертикальным поиском.
У Вас может возникнуть вопрос: «Зачем это нужно?». А нужно это для того, чтобы показать все возможные способы поиска. К тому же, многочисленные ограничения ВПР часто мешают получить нужный результат. В этом отношении ИНДЕКС( ) ПОИСКПОЗ( ) гораздо функциональнее и разнообразнее, а также в них меньшее количество ограничений.
Основы ИНДЕКС ПОИСКПОЗ
Так как цель этого руководства — показать, чем хороша эта функция, мы рассмотрим базовую информацию касательно принципов её работы. И покажем примеры, а также рассмотрим почему, она лучше ВПР().
Функция ИНДЕКС: синтаксис и применение
Эта функция помогает в поиске требуемого значения среди указанных областей поиска, основываясь на номере колонки или строки. Синтаксисы:
=ИНДЕКС(массив, номер строки, номер столбца):
- массив – область в которой будет проходить поиск;
- номер строки – номер строки, которую нужно искать в указанном массиве. Если номер строки неизвестен, нужно указать номер столбца;
- номер столбца – номер столбца, который надо найти в указанном массиве. Если значение неизвестно, требуется указать номер строки.
Пример простой формулы:
=ИНДЕКС(А1:С10,2,3)
Функция будет производить поиск в диапазоне от А1 до С10. Цифры показывают, из какой строки (2) и колонки (3) показать искомое значение. Результатом будет ячейка С2.
Довольно просто, верно? Но когда вы работаете с настоящими документами, вы вряд ли будете располагать информацией касательно номеров колонок или ячеек. Именно для этого и существует функция ПОИСКПОЗ().
Функция ПОИСКПОЗ: синтаксис и использование
Функция ПОИСКПОЗ() ищет нужное значение и показывает его примерный номер в указанной области поиска.
Синтаксис Поискпоз() выглядит так:
=ПОИСКПОЗ(искомое значение, просматриваемый массив, тип сопоставления)
- искомое значение – цифра или текст, который нужно найти;
- просматриваемый массив – область, где будет проходить поиск;
- тип сопоставления – уточняет, искать точное значение или ближайшие к нему значения:
- 1 (или значение не указано) – выдает самое большое значение, которое равно или меньше значения, которое было указано;
- 0 – показывает точное совпадение с искомым значением. В комбинации ИНДЕКС() ПОИСКПОЗ() вам практически всегда понадобится точное совпадение, так что прописываем 0;
- -1 – показывает наименьшее значение, которое больше или равно указанному в формуле. Сортировка проводится по убыванию.
Например, в диапазоне В1:В3 прописаны Нью-Йорк, Париж, Лондон. Указанная ниже формула покажет номер 3, потому что Лондон является третьим по списку:
=ПОИСКПОЗ(Лондон,В1:В3,0)
Как работать с функцией ИНДЕКС ПОИСКПОЗ
Вы наверняка уже начали понимать, по какому принципу строится совместная работа этих функций. Если вкратце, то ИНДЕКС() проводит поиск нужного значения среди указанных строк и колонок. А ПОИСКПОЗ() показывает номера этих значений:
=ИНДЕКС(колонка из которой возвращается значение, ПОИСКПОЗ(значение для поиска, колонка в которой искать, 0))
Все еще сложно понять, как это работает? Может быть на примере получится объяснить лучше. Предположим, у Вас есть список мировых столиц и численность их населения:
Для того, чтобы выяснить размеры населения какой-то определенной столицы, например, столицы Японии, воспользуемся такой формулой:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(Япония, А2:А10,0))
- Функция ПОИСКПОЗ() ищет значение – «Япония» в массиве А2:А10 и выдает цифру 3, потому что Япония — это третье значение по списку.
- Эта цифра идет в « номер строки » в формуле ИНДЕКС() и указывает функции на необходимость вывести значение из этой строки.
Таким образом, вышеуказанная формула становится стандартной формулой ИНДЕКС(С2:С10,3). Формула проводит поиск в ячейках от С2 до С10 и выдает данные из третьей ячейки этого диапазона, то есть С4, потому что отсчет начинается со второй строки.
Не хотите прописывать название города в формуле? Тогда напишите его в любой ячейке, скажем, F1, и используйте её как ссылку в формуле ПОИСКПОЗ(). И у вас получится динамическая формула поиска:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Важно! Количество строк в массиве ИНДЕКС() должно быть такое же, как и количество строк в рассматриваемом массиве в ПОИСКПОЗ(), иначе вы получите неправильный результат.
Подождите-ка, почему бы просто не использовать формулу ВПР()?
=ВПР(F1, A2:C10, 3, Ложь)
Какой смысл тратить время в попытках разобраться во всех этих сложностях ИНДЕКС ПОИСКПОЗ?
В этом случае нет разницы, какую функцию использовать. Это просто пример, чтобы был понятен принцип совместной работы функций ИНДЕКС() и ПОИСКПОЗ(). Другие примеры покажут, на что способны эти функции в ситуациях, когда ВПР оказывается бессильным.
ИНДЕКС ПОИСКПОЗ или ВПР
Решая, какую использовать формулу для поиска, многие соглашаются что ИНДЕКС() и ПОИСКПОЗ() значительно лучше ВПР. Однако, многие люди все еще пользуются ВПР(). Во-первых, ВПР() проще, во-вторых, пользователи до конца не понимают все плюсы работы с ИНДЕКС() и ПОИСКПОЗ(). Не имея этих знаний, никто не согласится тратить свое время на изучение сложной системы.
Ниже мы приведем ключевые преимущества ИНДЕКС() и ПОИСКПОЗ() над ВПР():
- Безопасное добавление или удаление колонок. Формула ВПР() показывает неправильные результаты при удалении или добавлении колонок, потому что ВПР() нуждается в точных указаниях номера колонки для успешного поиска. Естественно, при добавлении или удалении колонок, меняются и их номера.
А в формуле ИНДЕКС() и ПОИСКПОЗ() указывается диапазон колонок, а не отдельные колонки. В результате, можно безопасно добавлять и удалять колонки, без необходимости каждый раз обновлять формулу.
Влияние ВПР() на производительность будет особенно заметным, если ваш рабочий лист содержит большое количество формул вроде ВПР() и СУММ(). Для анализа каждого значения в массиве требуются отдельные проверки функций ВПР(). Так что Excel приходится обрабатывать огромное количество информацию, и это значительно замедляет работу.
Примеры формул
Мы уже выяснили полезность этих функций, так что можно перейти к самой интересной части: к применению знаний на практике.
Формула для поиска справа налево
Как уже было сказано, ВПР не может проводить такую форму поиска. Так что, если нужные значения расположены не в самой левой колонке, ВПР() не выдаст результат. Функции ИНДЕКС() и ПОИСКПОЗ() более универсальны, и для их работы расположение значений не играет большой роли.
Для примера, мы добавим колонку ранга в левую часть нашей таблицы и попробуем разобраться, какой ранг по численности населения занимает столица России.
В ячейке G1 прописываем значение, которое нужно найти, а потом используем указанную ниже формулу для поиска в диапазоне С1:С10 и возвращаем соответствующее значение из А2:А10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Подсказка. Если вы планируете использовать эту формулу для нескольких ячеек, убедитесь, что вы зафиксировали диапазоны с помощью абсолютной адресации (например, $А$2: $А$10 и $С$2:4С$10 ).
ИНДЕКС ПОИСКПОЗ ПОИСКПОЗ для поиска в колонках и строках
В вышеуказанных примерах мы использовали эти функции как замену для ВПР(), чтобы вернуть значения из заранее определенного диапазона строк. Но что, если вам нужно провести матричный или двусторонний поиск?
Звучит сложно, но формула для таких расчетов похожа на стандартную формулу ИНДЕКС() ПОИСКПОЗ(), но только с одним различием: формулу ПОИСКПОЗ() нужно использовать дважды. Первый раз, чтобы получить номер строки, и второй раз — чтобы получить номер колонки:
=ИНДЕКС(массив, ПОИСКПОЗ(вертикальное искомое значение, искомая колонка, 0), ПОИСКПОЗ(горизонтальное искомое значение, искомая строка,0))
Посмотрим на таблицу внизу и попробуем составить формулу ИНДЕКС() ПОИСКПОЗ() ПОИСКПОЗ() для того, чтобы отобразить демографию в определенной стране за выбранный год.
Целевая страна указана в ячейке G1 (вертикальное искомое значение), а целевой год — в ячейке G2 (горизонтальное искомое значение). Формула будет выглядеть так:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Как работает эта формула
Как и с любыми другими сложными формулами, в них легче разобраться, разбив их на отдельные уравнения. И тогда вы сможете понять, что делает каждая индивидуальная функция:
- ПОИСКПОЗ(G1,A2:A11,0) – ищет значение (G1) в диапазоне A2:A11 и показывает номер этого значения, в нашем случае это 2;
- ПОИСКПОЗ(G2,B1:D1,0) – ищет значение (G2) в диапазоне B1:D1. В данном случае результат был 3.
Найденные номера строк и колонок отправляются в соответствующее значение в формуле ИНДЕКС():
=ИНДЕКС(B2:D11,2,3)
В результате, имеем значение, которое находится в ячейке на пересечении 2 строки и 3 колонки в диапазоне B2:D11. И формула показывает искомое значение, которое находится в ячейке D3.
Поиск по нескольким условиям с помощью ИНДЕКС и ПОИСКПОЗ
Если вы читали наше руководство по ВПР(), вы наверняка пробовали формулу поиска по нескольким условиям. Но этот способ поиска имеет одно значительное ограничение – необходимость добавлять вспомогательную колонку.
Но хорошая новость заключается в том, что с помощью ИНДЕКС() и ПОИСКПОЗ() можно проводить поиск по нескольким условиям без необходимости редактировать или менять вашу рабочую таблицу.
Вот общая формула поиска по нескольким условиям для ИНДЕКС() ПОИСКПОЗ():
Заметка: эту формулу нужно использовать вместе с сочетанием клавиш CTRL+SHIFT+ENTER.
Предположим, вам нужно найти искомое значение, основанное на 2 условиях: Покупатель и Продукт.
Для этого нужна следующая формула:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
В этой формуле С2:С10 — диапазон в котором будет проходить поиск, F1 – это условие, А2:А10 — это диапазон для сравнения условия, F2 – условие 2, В2:В10 – диапазон для сравнения условия 2.
Не забывайте в конце работы с формулой нажать сочетание CTRL+SHIFT+ENTER – Excel автоматически закроет формулу фигурными скобками, как указано в примере:
Если же вы не хотите использовать формулу массива для вашей работы, то добавьте еще один ИНДЕКС() к формуле и нажмите ENTER, выглядеть это будет как на примере:
Как работают эти формулы
Эта формула работает по тому же принципу, что и стандартная формула ИНДЕКС() ПОИСКПОЗ(). Для того, чтобы искать по нескольким условиям, вы просто создаете несколько Ложных и Истинных условий, которые представляют правильные и неправильные индивидуальные условия. А потом эти условия распространяются на все соответствующие элементы массива. Формула конвертирует Ложные и Истинные аргументы в 0 и 1 соответственно и выводит массив, в котором 1 — это соответствующие условию значения, которые были найдены в строке. ПОИСКПОЗ() найдет первое соответствующее 1 значение и передаст его в формулу ИНДЕКС(). А она, в свою очередь, возвратит уже искомое значение в указанную строку из нужной колонки.
Формула без массива зависит от способности ИНДЕКС() самостоятельно с ними справляться. Второй ИНДЕКС() в формуле соответствует ложному значению (0), так что он передает весь массив с такими значениями в формулу ПОИСКПОЗ().
Это довольно пространное объяснение логики, по которой работает эта формула. Для более детальной информации прочтите статью « ИНДЕКС ПОИСКПОЗ с несколькими условиями ».
СРЗНАЧ, МАКС и МИН в ИНДЕКС и ПОИСКПОЗ
В Excel есть свои специальные функции для поиска средних, максимальных и минимальных значений. Но что, если нужно получить данные из ячейки, связанной с этими значениями? В этом случае СРЗНАЧ, МАКС и МИН нужно использовать вместе с ИНДЕКС и ПОИСКПОЗ.
ИНДЕКС ПОИСКПОЗ и МАКС
Чтобы найти наибольшее значение в колонке D и показать его в колонке C, используем формулу:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
ИНДЕКС ПОИСКПОЗ и МИН
Чтобы обнаружить наименьшее значение в колонке D и вывести его в колонке С, используется такая формула:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
ИНДЕКС ПОИСКПОЗ и СРЗНАЧ
Чтобы найти среднее значение в колонке D и вывести это значение в С:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
В зависимости от того, как записаны ваши данные, в качестве третьего аргумента для ПОИСКПОЗ() используется либо 1, либо 0, либо -1:
- если колонки отсортированы по возрастанию – ставьте 1 (тогда формула рассчитает максимальное значение, которое меньше или равняется среднему значению);
- если сортировка по убыванию, тогда -1 (формула выведет минимальное значение, которое больше или равняется среднему);
- если же массив поиска содержит значение, которое в точности равняется среднему, тогда ставьте 0.
В нашем примере, численность населения отсортирована в убывающем порядке, поэтому мы ставим -1. И в результате получаем Токио, так как значение населения (13,189, 000) самое близкое к среднему значению (12,269,006).
ВПР() тоже может проводить такие расчеты, но только как формула массива: ВПР со СРЗНАЧ, МИН и МАКС .
ИНДЕКС ПОИСКПОЗ и ЕСНД/ЕСЛИОШИБКА
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Если же хотите отловить все ошибки, тогда кроме ЕСНД можно использовать ЕСЛИОШИБКА :
=ЕСЛИОШИБКА(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)), «Что-то пошло не так!»)
Но помните, что маскировать ошибки таким образом – не лучшая идея, ведь стандартные ошибки сообщают о нарушениях в формуле.
Надеемся, наше руководство пользования функцией ИНДЕКС ПОИСКПОЗ() оказалось полезным.
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Читайте также: