Всё об операционных системах — подробное руководство для пользователей операционных систем

Функции ИНДЕКС и ПОИСКПОЗ в Excel

Поиск значений в таблице – одна из наиболее часто встречающихся задач, с которыми сталкиваются практически все пользователи при работе с таблицами. В программе для выполнения таких операций предусмотрены различные инструменты. К наиболее эффективным средствам поиска можно отнести функции ИНДЕКС и ПОИСКПОЗ. В англоязычной версии Excel они называются INDEX MATCH соответственно. В данной статье рассмотрим, как работает связка этих формул и чем они лучше других инструментов для поиска.

Как работают Индекс и ПОИСКПОЗ

Совместное применение функций INDEX MATCH является наиболее удобным и гибким средством для поиска значений в таблице. Формулы дополняют друг друга, создавая в итоги инструмент для возврата искомых значений, который намного превосходит ВПР и любые другие функции. Чтобы разобраться в специфике данной связки, сначала необходимо разобраться, как работают ИНДЕКС и ПОИСКПОЗ.

Описание функции ИНДЕКС

Формула ИНДЕКС в Excel отвечает за возврат значения из указанной ячейки. Она принимает аргументы, необходимые для поиска требуемой ячейки. Аргументы функции бывают следующими:

  • Массив. Представляет собой диапазон ячеек, в котором ведется поиск значений. Это может быть как один фрагмент таблицы, так и целый лист либо именованный диапазон.
  • Номер строки. Это номер строчки из которой нужно получить значение. Обратите внимание на то, что при этом используется нумерация для диапазона, а всей таблицы.
  • Номер столбца. Это необязательный аргумент. Его указывают в том случае, если заданный диапазон или массив состоит из множества колонок.

Рассмотрим, как пользоваться формулой ИНДЕКС на небольшом примере. Нам необходимо получить значение, которое содержится в таблице с данными о продажах мобильных устройств. Сначала вводим функцию и указываем диапазон, в котором будет осуществляться поиск.

Вводим адрес массива
Вводим адрес массива

Для примера, вернем значение из 8 строки второго столбца диапазона. Указываем эти адреса в качестве аргументов для функции.

Добавляем адрес строки и столбца
Добавляем адрес строки и столбца

Нажимаем Enter и получаем значение по заданному адресу. В нашем случае функция ИНДЕКС вернет название найденного устройства из списка товаров.

Получаем значение
Получаем значение

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

Описание функции ПОИСКПОЗ

Данная формула используется для возврата относительного адреса клетки с искомым значением. Иными словами, она позволяет найти координаты ячейки, в которой содержится то, что ищет пользователь. На вход принимаются такие аргументы:

  • Искомое значение. Это текст или число, позицию которого нужно отыскать.
  • Диапазон поиска. Представляет собой фрагмент таблицы, в котором будет выполнен поиск.
  • Тип сопоставления. Можно искать точные (1) или относительные (0) совпадения. Если тип не указывать, программа автоматически будет выполнять поиск точных вхождений.

Для примера, отыщем позицию товара «Realme C33». Для поиска позиции вводим формулу ПОИСКПОЗ. В английском варианте Excel – это функция MATCH. Сначала указываем искомое значение. Его необязательно нужно вводить в виде текста. Можно использовать в качестве аргумента ссылку на ячейку, в которой оно содержится. Далее задаем диапазон для выполнения поиска и указываем тип сопоставления.

Применяем формулу ПОИСКПОЗ
Применяем формулу ПОИСКПОЗ

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

Возвращаем номер строки
Возвращаем номер строки

Обратите внимание на то, что в Excel также присутствует формула ПОИСК. Она работает совершенно иным способом и не может расцениваться, как аналог ПОИСКПОЗ.

Связка формул ИНДЕКС и ПОИСКПОЗ

Разобравшись, как работают INDEX MATCH по отдельности, нетрудно определить принцип их совместного использования. Основную задачу выполняет функция ИНДЕКС в Excel, которая возвращает значение, исходя из заданного номера строки. В свою очередь, ПОИСКПОЗ подставляет эти аргументы, выполняя вспомогательную функцию.

На первый взгляд связка INDEX и MATCH в Excel кажется очень сложной. Однако на самом деле процесс поиска с использованием этой связки несложный. К тому же, используя данную связку формул, пользователь получает более гибкий инструмент, способный находить нужные значения.

Рассмотрим способ применения ИНДЕКС и ПОИСКПОЗ на примере. В таблице содержатся данные о продажах мобильных устройств. По условию задачи, нужно вернуть количество проданных единиц товара, зная его название. Сделать это можно следующим образом:

  1. Сначала вводится формула ИНДЕКС. Туда же указывается первый аргумент – массив, из которого нужно получить значение. В нашем случае нужно найти количество проданных единиц товара. Поэтому указываем диапазон того столбца, в котором записаны эти значения. В нашем случае – это D1-D21.

    Вводим формулу индекс
    Вводим формулу индекс
  2. Следующий аргумент формулы ИНДЕКС – номер строки, в которой содержится значение. По условию задачи этот номер неизвестен. Поэтому нужно сделать поиск, используя ПОИСКПОЗ. Вводим эту функцию, потом в скобках указываем искомое значение и диапазон ячеек, в котором он будет просматриваться. В нашем случае в качестве искомого значения указывается ссылка на ячейку, где записано название товара. Также указываем тип сопоставления – 0.

    Вводим ПОИСКПОЗ
    Вводим ПОИСКПОЗ
  3. Нажимаем Enter и в результате этого связка INDEX MATCH вернет количество проданных единиц того товара, чье название было задано. Формула ПОИСКПОЗ возвращает номер строки, из которой нужно получить значение, а ИНДЕКС, зная номер строки, отображает это значение.

    Результат выполнения
    Результат выполнения

Как видно, связка INDEX MATCH или ИНДЕКС ПОИСКПОЗ на русском, на самом деле не так сложна, как может показаться на первый взгляд. Поэтому она является достойной заменой для любых других поисковых инструментов в Экселе.

Основные преимущества перед ВПР

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

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

Связка функций ИНДЕКС и ПОИСКПОЗ лишена данного недостатка. Можно получить значение из любого столбца. Например, определим ID товара. В нашей таблице столбец ID расположен левее колонки с искомым значением. Тем не менее связка функций работает бесперебойно.

Поиск значений слева
Поиск значений слева

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

В связке ИНДЕКС и ПОИСКПОЗ такого не происходит. При добавлении новых столбцов программа сможет автоматически поставить адрес диапазона, в котором выполняется поиск. Благодаря этому результаты будут точными, независимо от того, добавлялись ли в таблицу новые колонки или удалялись ранее существующие. Никаких ошибок при этом не возникнет.

Изменение аргументов ПОИСКПОЗ при добавлении новых столбцов
Изменение аргументов ПОИСКПОЗ при добавлении новых столбцов


С помощью формул ИНДЕКС и ПОИСКПОЗ пользователи могут создать эффективный инструмент для извлечения значений из таблицы. Такая связка может считаться идеальной альтернативой ВПР, так как лишена целого ряда недостатков. К тому же синтаксис данных формул несложный, а потому освоить данную связки смогут даже начинающие пользователи Excel.

Adblock
detector