В этом руководстве вы найдете ряд примеров формул, демонстрирующих наиболее эффективное использование функции ИНДЕКС в Excel.
Из всех функций Excel, возможности которых часто недооцениваются и используются недостаточно, ИНДЕКС определенно занимает место в первой десятке. Между тем, эта функция умна, гибка и универсальна.
Итак, что такое функция ИНДЕКС в Excel? По сути, формула ИНДЕКС (в английской версии – INDEX) возвращает ссылку на ячейку из заданного массива или диапазона. Другими словами, вы используете её, когда знаете (или можете определить) положение элемента в диапазоне и хотите затем получить значение этого элемента.
Если вы оцените реальный потенциал функции ИНДЕКС, это может кардинально изменить способы расчета, анализа и представления данных в ваших таблицах.
Функция ИНДЕКС в Excel — синтаксис и основные способы использования
В Excel есть две версии функции ИНДЕКС — форма массива и форма ссылки. Обе их можно использовать во всех версиях Microsoft Excel 365, 2019, 2016, 2013, 2010, 2007 и 2003.
Форма массива ИНДЕКС
В данном случае функция ИНДЕКС возвращает значение элемента в таблице или массиве на основе указанных вами номеров строк и столбцов.
ИНДЕКС(массив,номер_строки,[номер_столбца])
- массив - это диапазон ячеек, именованный диапазон или таблица.
- Номер_строки — это номер строки в массиве, из которого нужно вернуть значение. Если этот аргумент опущен, требуется следующий – номер_столбца.
- Номер_столбца — это номер столбца, из которого нужно вернуть значение. Если он опущен, требуется номер_строки.
Например, формула =ИНДЕКС(C2:F11;4;3) возвращает значение на пересечении четвертой строки и третьего столбца в диапазоне C2:F11, что является значением в ячейке D4.
Чтобы получить представление о том, как формула ИНДЕКС работает с реальными данными, взгляните на следующий пример:
Вместо того, чтобы вводить в формулу номера строк и столбцов, вы можете указать ссылки на ячейки, чтобы получить более универсальную формулу:
=ИНДЕКС(C2:F11;I1;I2)
Итак, эта формула ИНДЕКС возвращает количество товаров точно на пересечении номера товара, указанного в ячейке I1 (номер_строки), и номера недели, введенного в ячейке I2 (номер_столбца).
Примечание. Использование абсолютных ссылок ($C$2:$F$11) вместо относительных ссылок (C2:F11) в аргументе массива упрощает копирование формулы в другие ячейки. Кроме того, вы можете преобразовать диапазон в таблицу (Ctrl + Т) и обращаться к нему по имени таблицы.
Что нужно помнить
- Если аргумент массива состоит только из одной строки или столбца, вы можете указать или не указать соответствующий аргумент номер_строки или номер_столбца.
- Если аргумент массива включает более одной строки, а номер_строки опущен или равен 0, функция ИНДЕКС возвращает массив всего столбца. Точно так же, если массив включает более одного столбца, а аргумент номер_столбца опущен или равен 0, формула ИНДЕКС возвращает всю строку. Вот пример формулы, демонстрирующий такое поведение.
- Аргументы номер_строки и номер_столбца должны ссылаться на ячейку в массиве; в противном случае формула ИНДЕКС вернет ошибку #ССЫЛКА!.
Форма ИНДЕКС в виде ссылки
Ссылочная форма функции ИНДЕКС Excel возвращает ссылку на ячейку на пересечении указанной строки и столбца.
ИНДЕКС(ссылка, номер_строки, [номер_столбца], [номер_области] )
- ссылка - это один или несколько диапазонов.
Если вы вводите более одного диапазона, разделите диапазоны точкой с запятой и заключите аргумент ссылки в круглые скобки, например (A1:B5; D1:F5).
Если каждый диапазон в ссылке содержит только одну строку или столбец, соответствующий аргумент номер_строки или номер_столбца является необязательным.
- номер_строки – порядковый номер строки в диапазоне, из которого возвращается ссылка на ячейку, аналогично форме массива.
- номер_столбца — порядковый номер столбца, из которого возвращается ссылка на ячейку, также работает аналогично форме массива.
- номер_области — необязательный параметр, указывающий, какой диапазон из ссылочного аргумента использовать. Если он не указан, формула ИНДЕКС вернет результат для первого диапазона, указанного в ссылке.
Например, формула =ИНДЕКС((B2:F4;B8:F11);3;2;2) возвращает значение ячейки С10, которая находится на пересечении 3-й строки и 2-го столбца во второй области (B8:F11).
Что нужно помнить о функции ИНДЕКС в форме ссылки
- Если аргумент номер_строки или номер_столбца равен нулю (0), формула ИНДЕКС возвращает ссылку для всего столбца или строки соответственно.
- Если и номер_строки, и номер_столбца опущены, функция ИНДЕКС возвращает область, указанную в аргументе номер_области.
- Все аргументы (номер_строки, номер_столбца и номер_области) должны ссылаться на ячейку в пределах заданного диапазона. В противном случае формула ИНДЕКС вернет ошибку #ССЫЛКА!
Обе формулы ИНДЕКС, которые мы обсуждали до сих пор, очень просты и служат только для иллюстрации. Ваши реальные формулы, вероятно, будут намного сложнее. Поэтому давайте рассмотрим несколько наиболее эффективных способов использования ИНДЕКС в Excel.
Как использовать функцию ИНДЕКС в Excel - примеры формул
Возможно, сама по себе функция ИНДЕКС в Excel не имеет большого практического применения, но в сочетании с другими функциями, такими как ПОИСКПОЗ или СЧËТ, она может создавать очень мощные формулы.
Далее во всех наших формулах ИНДЕКС (кроме последней) мы будем использовать приведенные ниже данные.
1. Получение N-го элемента из списка
Это основное использование функции ИНДЕКС и простейшая её формула. Чтобы получить определенный элемент из списка, вы просто пишете: =ИНДЕКС(диапазон; n), где диапазон — это диапазон ячеек или именованный диапазон, а n — позиция элемента, который вы хотите из него получить.
Чтобы получить значение ячейки на пересечении определенной строки и столбца, вы используете тот же подход с той лишь разницей, что вы указываете и номер строки, и номер столбца. На самом деле, вы уже видели такую формулу в действии, когда мы обсуждали форму массива функции ИНДЕКС .
И вот еще один пример. В нашем образце таблицы, чтобы найти четвертую по величине планету в Солнечной системе, вы сортируете таблицу по столбцу Диаметр и используете следующую формулу ИНДЕКС:
=ИНДЕКС(A5:E13;4;1)
- A5:E13 — это ссылка на диапазон с данными.
- Номер_строки равен 4, потому что вы ищете четвертый элемент в списке,
- Номер_столбца равен 1, потому что мы хотим извлечь имя планеты.
Естественно, вы можете использовать ссылку на ячейку в аргументах номер_строки и/или номер_столбца, чтобы сделать вашу формулу более универсальной, как показано на скриншоте ниже:
Кроме того, вы можете не сортировать таблицу вручную, а сделать это при помощи формулы.
=ИНДЕКС(СОРТ(A5:E13;3;-1);B1;1)
Функция СОРТ сортирует таблицу по третьему столбцу в порядке убывания. Ну а затем ИНДЕКС делает всё остальное.
2. Получение всех значений в строке или столбце
Помимо извлечения одной ячейки, функция ИНДЕКС может возвращать массив значений из всей строки или столбца . Чтобы получить все значения из определенного столбца, вы должны опустить аргумент номер_строки или установить его равным 0. Аналогичным образом, чтобы получить всю строку, вы передаете пустое значение или 0 в номер_столбца.
Такие формулы ИНДЕКС вряд ли можно использовать сами по себе, потому что Excel не может уместить массив значений, возвращаемых формулой, в одну ячейку, и вы получите ошибку #ЗНАЧ!. Однако, если вы используете ИНДЕКС в сочетании с другими функциями, такими как СУММ или СРЗНАЧ, вы получите отличные результаты.
Например, вы можете использовать следующую формулу для расчета средней температуры планет в Солнечной системе:
=СРЗНАЧ(ИНДЕКС(A4:E12;;4))
В приведенной выше формуле аргумент номер_столбца равен 4, потому что температура находится в четвертом столбце нашей таблицы. Параметр номер_строки опущен.
Аналогичным образом можно найти минимальную и максимальную температуры:
=МИН(ИНДЕКС(A4:E12;;4))
=МАКС(ИНДЕКС(A4:E12;;4))
И посчитаем общую массу планет (масса – второй столбец в таблице):
=СУММ(ИНДЕКС(A4:E12;;2))
С практической точки зрения функция ИНДЕКС в приведенной выше формуле лишняя. Вы можете просто написать =СРЗНАЧ(D4:D12) и получить те же результаты.
При работе с реальными данными эта функция может оказаться полезной как часть более сложных формул, которые вы используете для анализа данных.
К примеру, формула =СУММ(ИНДЕКС(A4:E12;;С1)) позволит вам подсчитывать сумму в том столбце, номер которого вы укажете в ячейке C1.
3. Использование ИНДЕКС с другими функциями (СУММ, СРЗНАЧ, МАКС, МИН)
Из предыдущих примеров у вас может сложиться впечатление, что формула ИНДЕКС возвращает значения, но на самом деле она возвращает ссылку на ячейку, содержащую значение. И этот пример демонстрирует, как эту возвращаемую ссылку можно использовать.
Поскольку результат формулы ИНДЕКС является ссылкой, мы можем использовать его в других функциях для создания динамического диапазона. Звучит запутанно? Сейчас поясним.
Предположим, у вас есть формула =СРЗНАЧ(D4:D12), которая возвращает среднее значений в ячейках D4:D12. Вместо того, чтобы писать диапазон непосредственно в формуле, вы можете заменить либо D4, либо D12, либо их оба, функцией ИНДЕКС, например:
=СРЗНАЧ(D4 : ИНДЕКС(D1:D20,12))
Обе приведенные выше формулы дадут один и тот же результат, поскольку функция ИНДЕКС также возвращает ссылку на ячейку В12 (номер строки равен 12, номер столбца опущен). Разница в том, что диапазон формулы СРЗНАЧ / ИНДЕКС является динамическим, и как только вы измените аргумент номер_строки в ИНДЕКС, то диапазон, обрабатываемый функцией СРЗНАЧ, также изменится, и формула вернет другой результат.
По-видимому, такое использование формулы ИНДЕКС кажется слишком сложным и не всегда рациональным и логичным. Но этот подход имеет важное практическое применение, как показано в следующих примерах.
Пример 1. Вычислить среднее значение первых N элементов в списке
Допустим, вы хотите узнать средний диаметр N самых больших планет в солнечной системе.
Количество позиций, которые мы будем использовать для расчета, указываем в ячейке В1.
Затем вы сортируете таблицу по столбцу диаметра от наибольшего к наименьшему и используете следующую формулу расчёта среднего значения:
=СРЗНАЧ(C5:ИНДЕКС(C5:C13;B1))
Как видите, количество значений для расчета среднего мы можем выбирать, не корректируя саму формулу
Пример 2. Сумма элементов между указанными двумя элементами
Если вы хотите определить элементы верхней и нижней границы в своей формуле, вам просто нужно использовать две функции ИНДЕКС, чтобы вернуть первый и последний элемент, которые вам нужны.
Например, следующая формула при помощи функции СУММ возвращает сумму значений в столбце Масса между двумя элементами, указанными в ячейках B1 и Е1:
=СУММ(ИНДЕКС(B5:B13;B1):ИНДЕКС(B5:B13;E1))
4. Формула ИНДЕКС для создания динамических диапазонов и раскрывающихся списков
Как это часто бывает, когда вы начинаете упорядочивать данные на листе, вы можете не знать, сколько записей у вас в конечном итоге будет. Это не относится к нашей таблице планет, которая кажется полной, но кто знает...
В любом случае, если у вас есть изменяющееся количество элементов в данном столбце, скажем, от A1 до An , вы можете создать динамический именованный диапазон , включающий все ячейки с данными. При этом вы хотите, чтобы диапазон корректировался автоматически по мере добавления новых элементов или удаления некоторых из существующих. Например, если у вас сейчас 10 элементов, ваш именованный диапазон будет A1:A10. Если вы добавите новую запись, именованный диапазон автоматически расширится до A1:A11, а если вы передумаете и удалите эти вновь добавленные данные, диапазон автоматически вернется к A1:A10.
Основное преимущество этого подхода заключается в том, что вам не нужно постоянно обновлять все формулы в книге, чтобы убедиться, что они относятся к правильным диапазонам.
Одним из способов определения динамического диапазона является использование функции Excel СМЕЩ (OFFSET) :
=СМЕЩ($A$2; 0; 0; СЧЁТЗ($A1:$A100)-1; 1)
Другим возможным решением является использование ИНДЕКС Excel вместе с функцией СЧËТЗ:
=$A$2:ИНДЕКС($A2:$A100; СЧЁТЗ($A2:$A100))
В обеих формулах A2 — это ячейка, содержащая первый элемент списка. Динамический диапазон, создаваемый обеими формулами, будет идентичным.
Разница заключается в подходах. В то время как функция СМЕЩ перемещается от начальной точки на определенное количество строк и/или столбцов, то ИНДЕКС находит ячейку на пересечении определенной строки и столбца. Функция СЧËТЗ, используемая в обеих формулах, получает количество непустых ячеек в интересующем нас столбце.
В этом примере в столбце A 9 непустых ячеек, поэтому СЧËТЗ возвращает 9. Следовательно, ИНДЕКС возвращает $A$9, то есть последнюю использованную ячейку в столбце A (обычно ИНДЕКС возвращает значение, но в этой формуле оператор ссылки (:) заставляет его вернуть ссылку). И поскольку $A$1 является нашей отправной точкой, конечным результатом формулы будет диапазон $A$1:$A$9.
На следующем рисунке показано, как можно использовать такую формулу ИНДЕКС для создания динамического выпадающего списка.
Примечание. Самый простой способ создать динамически обновляемый выпадающий список — сделать именованный список на основе таблицы Excel. В этом случае вам не понадобятся сложные формулы, поскольку таблицы Excel сами по себе являются динамическими диапазонами.
Вы также можете использовать функцию ИНДЕКС для создания зависимых раскрывающихся списков, и в следующем руководстве объясняются все необходимые шаги: Создание каскадного раскрывающегося списка в Excel .
5. Мощный поиск с ИНДЕКС/ПОИСКПОЗ
Выполнение вертикального поиска — вот где функция ИНДЕКС действительно великолепна. Если вы когда-либо пробовали использовать функцию ВПР Excel , вы хорошо знаете ее многочисленные ограничения, такие как невозможность извлечения значений из столбцов слева от столбца поиска или ограничение в 255 символов для значения поиска.
Связка ИНДЕКС/ПОИСКПОЗ во многих отношениях превосходит ВПР:
- Никаких проблем с «левым» ВПР.
- Нет ограничений на размер искомого значения.
- Сортировка не требуется (ВПР с приблизительным совпадением требует сортировки столбца подстановки в порядке возрастания).
- Вы можете вставлять и удалять столбцы в таблице, не обновляя каждую связанную формулу.
- И последнее, но не менее важное: ИНДЕКС/ПОИСКПОЗ не замедляет работу Excel, как это происходит, если использовать несколько формул ВПР в большой таблице.
Вы используете ИНДЕКС / ПОИСКПОЗ следующим образом:
=ИНДЕКС ( столбец для возврата значения , (ПОИСКПОЗ ( искомое значение , столбец для поиска , 0))
Например, по коду заказа мы можем найти название товара. При этом функция ПОИСКПОЗ ищет совпадение с кодами заказа, а затем передает номер найденной позиции в функцию ИНДЕКС. Та же в свою очередь извлекает соответствующее значение из переданного ей диапазона.
Подробное описание функции ПОИСКПОЗ здесь: Функция ПОИСКПОЗ в Excel: полное руководство.
Таким образом организуется так называемый «левый» поиск, когда нужно извлечь значение из столбца, находящегося слева от столбца поиска. Функция ВПР сделать это не может. Более подробно о «левом» поиске читайте в этой статье.
Дополнительные советы и примеры формул см. в руководстве по Excel ИНДЕКС/ПОИСКПОЗ .
6. Формула ИНДЕКС для получения одного диапазона из списка диапазонов
Еще одно умное и эффективное использование функции ИНДЕКС в Excel — это возможность получить один диапазон из списка диапазонов.
Предположим, у вас есть несколько списков (например, сведения о еженедельных продажах). Вы можете рассчитать среднее значение или суммировать значения в любом выбранном диапазоне с помощью одной формулы.
Вы можете использовать следующую формулу индекса для расчета суммы продаж в выбранном диапазоне:
=СУММ(ИНДЕКС((C2:C11;D2:D11;E2:E11;F2:F11);;;I1))
Обратите внимание, что сейчас мы используем функцию ИНДЕКС в виде ссылки. Число в последнем аргументе (номер_области) сообщает формуле, какой именно диапазон выбрать.
На скриншоте ниже для номера диапазона (ячейка I1) установлено значение 2. Поэтому формула вычисляет сумму продаж за вторую неделю. Выбран второй по счёту диапазон в списке – D2:D11.
Чтобы сделать формулу еще более удобной для пользователя, вы можете использовать проверку данных и создать в ячейке I2 выпадающий список с номерами недель, чтобы предотвратить ошибки и опечатки:
Вот несколько основных способов применения формулы ИНДЕКС в Excel.
Я надеюсь, что эти примеры показали вам, как использовать потенциал функции ИНДЕКС в ваших рабочих листах. Спасибо за чтение!
