Функции MS Excel ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) – более гибкая альтернатива ВПР
Многие пользователи знают и применяют формулу ВПР. Известно также, что ВПР имеет ряд особенностей и ограничений, которые несложно обойти. Однако есть нюанс, который значительно ограничивает возможности функции ВПР.
ВПР требует, чтобы в диапазоне с искомыми данными столбец критериев всегда был первым слева. Это обстоятельство, конечно, является ограничением ВПР. Как же быть, если искомые данные находятся левее столбца с критерием? Можно, конечно, расположить столбцы в нужном порядке, что в целом, является неплохим выходом из ситуации. Но бывает так, что сделать этого нельзя, или трудно. К примеру, вы работаете в чужом документе или регулярно получаете новый отчет. В общем, нужно решение, не зависящее от расположения столбцов. Такое решение существует.
Нужно воспользоваться комбинацией из двух функций: ИНДЕКС и ПОИСКПОЗ. Формула работает следующим образом. ИНДЕКС отсчитывает необходимое количество ячеек вниз в диапазоне искомых значений. Количество отсчитываемых ячеек определяется по столбцу критериев функцией ПОИСКПОЗ. Работу комбинации этих функций удобно рассмотреть с середины, где вначале находится номер ячейки с подходящим критерием, а затем этот номер подставляется в ИНДЕКС.
Таким образом, чтобы подтянуть значение цены, соответствующее первому коду (книге), нужно прописать такую формулу.
Следует обратить внимание на корректность ссылок, чтобы при копировании формулы ничего не «съехало». Протягиваем формулу вниз. Если в таблице, откуда подтягиваются данные, нет искомого критерия, то функция выдает ошибку #Н/Д.
Довольно стандартная ситуация, с которой успешно справляется функция ЕСЛИОШИБКА. Она перехватывает ошибки и вместо них выдает что-либо другое, например, нули.
Конструкция формулы будет следующая:
Вот, собственно, и все.
Таким образом, комбинация функций ИНДЕКС и ПОИСКПОЗ является полной заменой ВПР и обладает дополнительным преимуществом: умеет находить данные слева от столбца с критерием. Кроме того, сами столбцы можно двигать как угодно, лишь бы ссылка не съехала, чего нельзя проделать с ВПР, т.к. количество столбцов там указывается конкретным числом. Посему комбинация ИНДЕКС и ПОИСКПОЗ более универсальна, чем ВПР.
Ниже видеоурок по работе функций ИНДЕКС и ПОИСКПОЗ.
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «кузьмина» или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).
Альтернатива ВПР: функции ИНДЕКС и ПОИСКПОЗ
Здравствуйте, друзья. Когда мы собираемся что-то искать в таблице, первая функция, которая приходит в голову – это ВПР. Все её знают, любят и активно пользуются. Но что, если есть другой, не менее удачный способ найти значение в таблице и вернуть что-то ему соответствующее?
Пусть у нас есть таблица ежемесячных продаж. Поиск продаж какого-то менеджера, например, в марте, решается с помощью ВПР:
Как еще можно это сделать? Давайте размышлять.
Предположим, нам понадобились продажи Соколова в марте. Если не использовать ВПР, можем получить эти данные с помощью функции ИНДЕКС:
В функции мы вручную указали, что нужно вернуть значение на пересечении 8-й строки и 4 столбца, что соответствует искомым данным. Но такой «ручной» подход не имеет смысла. Лучше поручить определение номера строки и столбца Экселю. Сделаем это с помощью функции ПОИСКПОЗ.
Найдем порядковый номер строки, в которой расположена фамилия Соколов:
Аналогично, получим номер столбца с нужным месяцем:
Теперь подставим 2 формулы поиска в функцию ИНДЕКС. Получим такую конструкцию:
=ИНДЕКС( B2:G27 ; ПОИСКПОЗ(K2;B2:B27;0) ; ПОИСКПОЗ(K3;B2:G2;0) )
Здесь первый аргумент – ссылка на массив продаж, второй – расчет номера строки в зависимости от выбранной фамилии, третий – получение столбца по заданному месяцу.
Для более удобного выбора фамилии и месяца, я сделал в таблице итогов выбор в выпадающих списках, и теперь мне нужно писать эти данные вручную. Хотите поступить так же? Читайте эту статью.
Обратите внимание, мы получили тот же результат, что и с ВПР. Многие ученики спрашивают меня: «Зачем пользоваться такой длинной и громоздкой комбинацией функций, когда один только ВПР делает то же самое проще, лаконичнее и привычнее?». Вот вам список очевидных преимуществ использования ИНДЕКС+ПОИСКПОЗ:
Однако, справедливо утверждение, что ВПР проще, понятнее, короче. Ему легче научиться. Что использовать – выбирайте сами. Я по умолчанию использую ВПР, но легко переключаюсь на ИНДЕКС+ПОИСКПОЗ, когда это необходимо.
ВПР vs. ИНДЕКС & ПОИСКПОЗ
ВПР – классическая, даже, пожалуй, легендарная функция. Эдакий первый этап для многих на пути к становлению активным пользователем функций в Excel. Тем не менее, у ВПР есть целый ряд недостатков: во-первых, ВПР не может возвращать значения, находящиеся слева от первого указанного столбца. Во-вторых, достаточно проблематичным может оказаться перемещение столбцов в таблице, на которую уже ссылаются определенные функции ВПР.
Именно поэтому, в этом видео я хочу тебе рассказать про использование комбинации функций ИНДЕКС и ПОИСКПОЗ в качестве универсальной и более мощной замены функции ВПР. Рассматривать будем пример из предыдущего поста с прошлой недели.
Итак, при выборе значения в динамическом выпадающем списке ячейки С2, в ячейках С3:С5 должны подтягиваться значения из умной таблицы ниже (как добавляются и работают динамические выпадающие списки в Excel мы рассмотрели вот в этом посте):
Сперва пропишем формулу в ячейке С3, а в оставшихся двух затем просто вставим копию готовой формулы.
Итак, ставим знак равно и прописываем функцию ИНДЕКС:
Данная функция работает следующим образом. В ней мы выбираем определенный диапазон (в нашем случае это будет диапазон значения умной таблицы – использовать будем структурированную ссылку), и посредством указания номера строки и номера столбца мы получаем определенную ячейку нашей таблицы.
Вот визуализированный пример, в котором таким образом мы выделяем ячейку со значение «Ford Mondeo» в области значений умной таблицы, выделяя четвертую строку и второй столбец:
Так что указываем первый аргумент в нашей функции ИНДЕКС:
Ну и следующие два аргумента функции индекс служат для указания номера строки и столбца. Естественно, указывать эти номера мы будем динамическим способом, а именно при помощи функции ПОИСКПОЗ.
Вот что делает эта функция: ей мы указываем определенный диапазон (второй аргумент), и говорим, какое значение нужно искать в этом диапазоне (первый аргумент):
Найдя значение в указанном диапазоне, ПОИСКПОЗ возвращает его порядковую позицию – в случае строки эту будет индекс 7. И да, в последнем аргументе функции ПОИСКПОЗ выставляем 0 для поиска точного совпадения.
Ну и тоже самое делаем для третьего аргумента функции ИНДЕКС – с помощью функции ПОИСКПОЗ динамическим образом указываем нужный нам столбец:
Супер! Теперь просто протягиваем формулу вниз и всё готово:
Ну и теперь о плюсах этой формулы в сравнении с ВПР: во-первых, столбец искомых значений в отличии от ВПР может находиться на любой позиции, и поиск может происходить как, скажем так, влево, так и вправо от диапазона с искомыми значениями. Во-вторых, даже если на нашу таблицу ссылаются уже прописанные комбинации ИНДЕКС и ПОИСКПОЗ, мы всегда абсолютно спокойно можем изменять позиции отдельных столбцов. Вот пример с теми же формулами без единого изменения:
Вот такая интересная, и очень практичная формула!
Ну и напоследок приглашаю тебя на свой YouTube-канал, посмотреть вот это видео:
В нём я еще более углубленно и с упоминанием других важных пунктов рассказал о ИНДЕКС и ПОИСКПОЗ, а также привел пример практического применения комбинации этих функций для создания автоматических формуляров.
Дубликаты не найдены
MS, Libreoffice & Google docs
453 поста 12.5K подписчика
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
чувак, давай уже лекции по numpy..
Как-то делал калькулятор расчета стоимости и сроков перевозки.
Получился такой монстр.
=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(MAX(IF(A5>INDEX(‘прайс’!$G$2:$G$6763,MATCH(‘Расчет’!A2&’Расчет’!C2&’Расчет’!A12,’прайс’!$A$2:$A$6763&’прайс’!$B$2:$B$6763&’прайс’!$H$2:$H$6763,0)),INDEX(‘прайс’!F2:F6763,MATCH(‘Расчет’!A2&’Расчет’!C2&’Расчет’!A12,’прайс’!A2:A6763&’прайс’!B2:B6763&’прайс’!H2:H6763,0))*(ROUNDUP(A5-INDEX(‘прайс’!$G$2:$G$6763,MATCH(‘Расчет’!A2&’Расчет’!C2&’Расчет’!A12,’прайс’!$A$2:$A$6763&’прайс’!$B$2:$B$6763&’прайс’!$H$2:$H$6763,0)),0)),0)+INDEX(‘прайс’!E2:E6763,MATCH(‘Расчет’!A2&’Расчет’!C2&’Расчет’!A12,’прайс’!A2:A6763&’прайс’!B2:B6763&’прайс’!H2:H6763,0)),IF(B5*C5*D5/1000000*200>INDEX(‘прайс’!$G$2:$G$6763,MATCH(‘Расчет’!A2&’Расчет’!C2&’Расчет’!A12,’прайс’!$A$2:$A$6763&’прайс’!$B$2:$B$6763&’прайс’!$H$2:$H$6763,0)),INDEX(‘прайс’!F2:F6763,MATCH(‘Расчет’!A2&’Расчет’!C2&’Расчет’!A12,’прайс’!A2:A6763&’прайс’!B2:B6763&’прайс’!H2:H6763,0))*(ROUNDUP(B5*C5*D5/1000000*200-INDEX(‘прайс’!$G$2:$G$6763,MATCH(‘Расчет’!A2&’Расчет’!C2&’Расчет’!A12,’прайс’!$A$2:$A$6763&’прайс’!$B$2:$B$6763&’прайс’!$H$2:$H$6763,0)),0)),0)+INDEX(‘прайс’!E2:E6763,MATCH(‘Расчет’!A2&’Расчет’!C2&’Расчет’!A12,’прайс’!A2:A6763&’прайс’!B2:B6763&’прайс’!H2:H6763,0)))*1.25+SUM(C15,C18:C24),»-«)), 1, 1)
Кажется, слово силы изучено
Без файла, в котором используется этот набор функций, невозможно понять этот набор букв.
Просто хотел показать применение формулы, описанной в посте, на реальной задаче.
Потом ещё одна почти такая же
в одну ячейку не влезло)
пришлось на части бить
Ооо, как давно я этого ждал. Буду кидать ссылку интернам своим, а не сам время тратить.
Постоянно использую симбиоз этих двух функций. Но столкнулся с проблемой: индекс и поискпоз ищут первое совпадение, но что делать в том, случае, когда нужно вытащить последнее (в списке присутствуют повторяющиеся значения)?
В экселе можно использовать такое: =ПРОСМОТР(2;1/(A1:A9=C2);B1:B9)
Но вот в гуглдоке решения найти не могу.
Хм. Имею дело с многосоттысячными массивами (100-350к строк) хватает впр. Если надо что-то круче, то проще пнуть ит или самому прописать вычисляемые поля в отчётах 1с (если что-то простое).
Макросы. Изучаем редактор VBE. Академия Excel
Давайте разбираться где происходит написание макросов? В Excel существует встроенный редактор Visual Basic Editor (VBE), в котором собственно и происходит создание макросов с нуля, редактирование существующий, удаление ненужных.
Посмотрев урок, Вы научитесь настраивать редактор под себя, познакомитесь с новым понятием Модуль (узнаете основные операции с ними).
Для начала, что такое SNMP: практически любое устройство, имеющее сетевой интерфейс, умеет в этот протокол, который позволяет удалённой стороне получить набор метрик работы этого устройства и управлять им. В данном случае из всех тысяч параметров меня интересует счётчик распечатанных и отсканированных страниц.
Если в двух словах: отправляем принтеру команду с определённым идентификатором, получаем ответ.
На получение результата может уйти несколько минут.
Далее остаётся скопировать результат в блокнот (увы, поиск в SnmpB не работает) и найти текстовое значение счётчика, которое нужно предварительно подсмотреть либо через веб-интерфейс принтера, либо ещё как.
Дальше скучно: берём VBA, пишем
On Error Resume Next
Set o = CreateObject(«OlePrn.OleSNMP»)
o.Open IPaddress, «public», 2, 1000
Name = o.get(«.1.3.6.1.2.1.25.3.2.1.3.1»)
TotalPrintCounter = o.get(«.1.3.6.1.4.1.1347.43.10.1.1.12.1.1»)
TotalScanCounter = o.get(«.1.3.6.1.4.1.1347.46.10.1.1.5.3»)
o.Close
On Error GoTo 0
Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР
Этот учебник рассказывает о главных преимуществах функций ИНДЕКС и ПОИСКПОЗ в Excel, которые делают их более привлекательными по сравнению с ВПР. Вы увидите несколько примеров формул, которые помогут Вам легко справиться со многими сложными задачами, перед которыми функция ВПР бессильна.
В нескольких недавних статьях мы приложили все усилия, чтобы разъяснить начинающим пользователям основы функции ВПР и показать примеры более сложных формул для продвинутых пользователей. Теперь мы попытаемся, если не отговорить Вас от использования ВПР, то хотя бы показать альтернативные способы реализации вертикального поиска в Excel.
Зачем нам это? – спросите Вы. Да, потому что ВПР – это не единственная функция поиска в Excel, и её многочисленные ограничения могут помешать Вам получить желаемый результат во многих ситуациях. С другой стороны, функции ИНДЕКС и ПОИСКПОЗ – более гибкие и имеют ряд особенностей, которые делают их более привлекательными, по сравнению с ВПР.
Базовая информация об ИНДЕКС и ПОИСКПОЗ
Так как задача этого учебника – показать возможности функций ИНДЕКС и ПОИСКПОЗ для реализации вертикального поиска в Excel, мы не будем задерживаться на их синтаксисе и применении.
Приведём здесь необходимый минимум для понимания сути, а затем разберём подробно примеры формул, которые показывают преимущества использования ИНДЕКС и ПОИСКПОЗ вместо ВПР.
ИНДЕКС – синтаксис и применение функции
Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:
Каждый аргумент имеет очень простое объяснение:
Если указаны оба аргумента, то функция ИНДЕКС возвращает значение из ячейки, находящейся на пересечении указанных строки и столбца.
Вот простейший пример функции INDEX (ИНДЕКС):
Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2.
Очень просто, правда? Однако, на практике Вы далеко не всегда знаете, какие строка и столбец Вам нужны, и поэтому требуется помощь функции ПОИСКПОЗ.
ПОИСКПОЗ – синтаксис и применение функции
Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.
Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку «London» – это третий элемент в списке.
Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:
На первый взгляд, польза от функции ПОИСКПОЗ вызывает сомнение. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!
Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.
Как использовать ИНДЕКС и ПОИСКПОЗ в Excel
Теперь, когда Вам известна базовая информация об этих двух функциях, полагаю, что уже становится понятно, как функции ПОИСКПОЗ и ИНДЕКС могут работать вместе. ПОИСКПОЗ определяет относительную позицию искомого значения в заданном диапазоне ячеек, а ИНДЕКС использует это число (или числа) и возвращает результат из соответствующей ячейки.
Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:
Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:
Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:
Теперь давайте разберем, что делает каждый элемент этой формулы:
Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4, так как счёт начинается со второй строки.
Вот такой результат получится в Excel:
Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.
Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?
В данном случае – смысла нет! Цель этого примера – исключительно демонстрационная, чтобы Вы могли понять, как функции ПОИСКПОЗ и ИНДЕКС работают в паре. Последующие примеры покажут Вам истинную мощь связки ИНДЕКС и ПОИСКПОЗ, которая легко справляется с многими сложными ситуациями, когда ВПР оказывается в тупике.
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС/ПОИСКПОЗ намного лучше, чем ВПР. Однако, многие пользователи Excel по-прежнему прибегают к использованию ВПР, т.к. эта функция гораздо проще. Так происходит, потому что очень немногие люди до конца понимают все преимущества перехода с ВПР на связку ИНДЕКС и ПОИСКПОЗ, а тратить время на изучение более сложной формулы никто не хочет.
Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: Как находить значения, которые находятся слева покажет эту возможность в действии.
2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.
Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:
=VLOOKUP(«lookup value»,A1:C10,2)
=ВПР(«lookup value»;A1:C10;2)
Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.
Используя ПОИСКПОЗ/ИНДЕКС, Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР.
3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.
Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:
Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:
4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.
Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.
С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто совершает поиск и возвращает результат, выполняя аналогичную работу заметно быстрее.
ИНДЕКС и ПОИСКПОЗ – примеры формул
Теперь, когда Вы понимаете причины, из-за которых стоит изучать функции ПОИСКПОЗ и ИНДЕКС, давайте перейдём к самому интересному и увидим, как можно применить теоретические знания на практике.
Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС
Любой учебник по ВПР твердит, что эта функция не может смотреть влево. Т.е. если просматриваемый столбец не является крайним левым в диапазоне поиска, то нет шансов получить от ВПР желаемый результат.
Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ/ИНДЕКС, которая покажет, какое место по населению занимает столица России (Москва).
Как видно на рисунке ниже, формула отлично справляется с этой задачей:
Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:
Подсказка: Правильным решением будет всегда использовать абсолютные ссылки для ИНДЕКС и ПОИСКПОЗ, чтобы диапазоны поиска не сбились при копировании формулы в другие ячейки.
Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)
Вы можете вкладывать другие функции Excel в ИНДЕКС и ПОИСКПОЗ, например, чтобы найти минимальное, максимальное или ближайшее к среднему значение. Вот несколько вариантов формул, применительно к таблице из предыдущего примера:
1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:
2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:
3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:
О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ
Используя функцию СРЗНАЧ в комбинации с ИНДЕКС и ПОИСКПОЗ, в качестве третьего аргумента функции ПОИСКПОЗ чаще всего нужно будет указывать 1 или -1 в случае, если Вы не уверены, что просматриваемый диапазон содержит значение, равное среднему. Если же Вы уверены, что такое значение есть, – ставьте 0 для поиска точного совпадения.
В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает «Moscow», поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).
Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу
Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.
В этом примере формула ИНДЕКС/ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?
Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:
И я поздравляю тех из Вас, кто догадался!
Начнём с того, что запишем шаблон формулы. Для этого возьмём уже знакомую нам формулу ИНДЕКС/ПОИСКПОЗ и добавим в неё ещё одну функцию ПОИСКПОЗ, которая будет возвращать номер столбца.
Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).
А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.
Хорошо, давайте запишем формулу. Когда мне нужно создать сложную формулу в Excel с вложенными функциями, то я сначала каждую вложенную записываю отдельно.
Итак, начнём с двух функций ПОИСКПОЗ, которые будут возвращать номера строки и столбца для функции ИНДЕКС:
Результатом этой формулы будет 5, поскольку «2015» находится в 5-ом столбце.
Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:
Если заменить функции ПОИСКПОЗ на значения, которые они возвращают, формула станет легкой и понятной:
Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!
Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ
В учебнике по ВПР мы показывали пример формулы с функцией ВПР для поиска по нескольким критериям. Однако, существенным ограничением такого решения была необходимость добавлять вспомогательный столбец. Хорошая новость: формула ИНДЕКС/ПОИСКПОЗ может искать по значениям в двух столбцах, без необходимости создания вспомогательного столбца!
Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.
Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:
Эта формула сложнее других, которые мы обсуждали ранее, но вооруженные знанием функций ИНДЕКС и ПОИСКПОЗ Вы одолеете ее. Самая сложная часть – это функция ПОИСКПОЗ, думаю, её нужно объяснить первой.
MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)
ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)
В формуле, показанной выше, искомое значение – это 1, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:
Теперь понимаете, почему мы задали 1, как искомое значение? Правильно, чтобы функция ПОИСКПОЗ возвращала позицию только, когда оба критерия выполняются.
Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС. Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3.
И, наконец, т.к. нам нужно проверить каждую ячейку в массиве, эта формула должна быть формулой массива. Вы можете видеть это по фигурным скобкам, в которые она заключена. Поэтому, когда закончите вводить формулу, не забудьте нажать Ctrl+Shift+Enter.
Если всё сделано верно, Вы получите результат как на рисунке ниже:
ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel
Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.
Синтаксис функции ЕСЛИОШИБКА очень прост:
Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.
Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:
=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
«Совпадений не найдено. Попробуйте еще раз!») =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
«Совпадений не найдено. Попробуйте еще раз!»)
И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:
Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки («»), как значение второго аргумента функции ЕСЛИОШИБКА. Вот так:
Надеюсь, что хотя бы одна формула, описанная в этом учебнике, показалась Вам полезной. Если Вы сталкивались с другими задачами поиска, для которых не смогли найти подходящее решение среди информации в этом уроке, смело опишите свою проблему в комментариях, и мы все вместе постараемся решить её.
