Индексировать в Excel, как это работает

Обновлено: 05.07.2024

В этом учебном пособии по Excel объясняется, как использовать функцию ИНДЕКС в Excel с синтаксисом и примерами.

Описание

Функция ИНДЕКС Microsoft Excel возвращает значение в таблице на основе пересечения позиции строки и столбца в этой таблице. Первая строка в таблице — это строка 1, а первый столбец в таблице — это столбец 1.

Функция ИНДЕКС – это встроенная функция Excel, относящаяся к категории Функция поиска/справки. Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию ИНДЕКС можно ввести как часть формулы в ячейку рабочего листа.

Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.

Синтаксис

Синтаксис функции ИНДЕКС в Microsoft Excel:

Параметры или аргументы

таблица Диапазон ячеек, содержащий таблицу данных. row_number Позиция строки в таблице, где находится значение, которое вы хотите найти. Это относительная позиция строки в таблице, а не фактический номер строки на листе. column_number Позиция столбца в таблице, где находится значение, которое вы хотите найти. Это относительная позиция столбца в таблице, а не фактический номер столбца на листе.

Возврат

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

Применимо к

Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

Пример (как функция рабочего листа)

Давайте рассмотрим, как использовать ИНДЕКС в качестве функции рабочего листа в Microsoft Excel.

Исходя из приведенной выше электронной таблицы Excel, будут возвращены следующие примеры INDEX:

Теперь давайте рассмотрим пример =ИНДЕКС(A2:D6,1,1), который возвращает значение 10247, и разберемся почему.

Первый параметр

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

В этом примере первым параметром является A2:D6, который определяет диапазон ячеек, содержащих данные.

Второй параметр

Второй параметр — это номер строки, используемый для определения местоположения пересечения в таблице. Значение 1 указывает на первую строку в таблице, значение 2 — на вторую строку и т. д.

В этом примере второй параметр равен 1, поэтому мы знаем, что наше пересечение произойдет в первой строке таблицы.

Третий параметр

Третий параметр — это номер столбца, используемый для определения местоположения пересечения в таблице. Значение 1 указывает на первый столбец в таблице, значение 2 — на второй столбец и т. д.

В этом примере третий параметр равен 1, поэтому мы знаем, что наше пересечение произойдет в первом столбце таблицы.

Поскольку теперь у нас есть значения строк и столбцов, мы знаем, что ищем пересечение строк row1 и col1 в таблице данных. Это приводит к тому, что точка пересечения находится в ячейке A2 таблицы, поэтому функция ИНДЕКС возвращает значение 10247.

Часто задаваемые вопросы

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

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

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

Функция ИНДЕКС

Функция ИНДЕКС в Excel фантастически гибкая и мощная, и вы найдете ее в огромном количестве формул Excel, особенно в сложных формулах. Но что на самом деле делает INDEX? В двух словах, ИНДЕКС извлекает значение в заданном месте в диапазоне.Например, допустим, у вас есть таблица планет в нашей Солнечной системе (см. ниже), и вы хотите получить название 4-й планеты, Марса, с помощью формулы. Вы можете использовать ИНДЕКС следующим образом:

ИНДЕКС возвращает значение в 4-й строке диапазона.

Что делать, если вы хотите получить диаметр Марса с помощью ИНДЕКС? В этом случае мы можем указать как номер строки, так и номер столбца, а также предоставить больший диапазон. В приведенной ниже формуле ИНДЕКС используется весь диапазон данных в B3:D11 с номером строки 4 и номером столбца 2:

ИНДЕКС извлекает значение в строке 4 столбца 2.

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

В этот момент вы можете подумать: "Ну и что? Как часто вы действительно знаете положение чего-либо в электронной таблице?"

Совершенно верно. Нам нужен способ определить положение вещей, которые мы ищем.

Войдите в функцию ПОИСКПОЗ.

Функция ПОИСКПОЗ

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

ПОИСКПОЗ возвращает 3, так как "Персик" является третьим элементом. ПОИСКПОЗ не чувствителен к регистру.

ПОИСКПОЗ не имеет значения, является ли диапазон горизонтальным или вертикальным, как вы можете видеть ниже:

Тот же результат с горизонтальным диапазоном, ПОИСКПОЗ возвращает 3.

Важно! Последний аргумент функции ПОИСКПОЗ — это тип соответствия. Тип соответствия важен и определяет, будет ли совпадение точным или приблизительным. Во многих случаях вы захотите использовать ноль (0), чтобы обеспечить точное соответствие. Тип соответствия по умолчанию равен 1, что означает приблизительное совпадение, поэтому важно указать значение. Подробнее см. на странице ПОИСКПОЗ.

ИНДЕКС и ПОИСКПОЗ вместе

Теперь, когда мы рассмотрели основы ИНДЕКС и ПОИСКПОЗ, как нам объединить две функции в одной формуле? Рассмотрим приведенные ниже данные — таблицу со списком продавцов и ежемесячными объемами продаж за три месяца: январь, февраль и март.

Допустим, мы хотим написать формулу, которая возвращает количество продаж за февраль для данного продавца. Из приведенного выше обсуждения мы знаем, что можем указать INDEX номер строки и столбца для получения значения. Например, чтобы получить число продаж Frantz за февраль, мы предоставляем диапазон C3:E11 со строкой 5 и столбцом 2:

Но мы, очевидно, не хотим жестко кодировать числа. Вместо этого нам нужен динамический поиск.

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

Сделав еще один шаг вперед, мы будем использовать значение из H2 в ПОИСКПОЗ:

ПОИСКПОЗ находит "Франц" и возвращает 5 в ИНДЕКС для строки.

ИНДЕКС требует числовых позиций.
ПОИСКПОЗ находит эти позиции.
ПОИСКПОЗ вложен в ИНДЕКС.

Теперь займемся номером столбца.

Двусторонний поиск с ИНДЕКСОМ и ПОИСКПОЗОМ

Выше мы использовали функцию ПОИСКПОЗ для динамического поиска номера строки, но жестко запрограммировали номер столбца. Как мы можем сделать формулу полностью динамической, чтобы мы могли возвращать продажи для любого продавца в любом конкретном месяце? Хитрость заключается в том, чтобы использовать ПОИСКПОЗ дважды: один раз, чтобы получить позицию в строке, и один раз, чтобы получить позицию в столбце.

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

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

Полностью динамический двусторонний поиск с ИНДЕКСОМ и ПОИСКПОЗОМ.

Первая формула ПОИСКПОЗ возвращает 5 для ИНДЕКС в качестве номера строки, вторая формула ПОИСКПОЗ возвращает 3 для ИНДЕКС в качестве номера столбца. После запуска ПОИСКПОЗ формула упрощается до:

и ИНДЕКС правильно возвращает 10 525 долларов США, объем продаж Frantz в марте.

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

Видео: отладка формулы с помощью F9 (чтобы увидеть возвращаемые значения ПОИСКПОЗ)

Поиск слева

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

Поиск с учетом регистра

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

Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter, за исключением Excel 365.

Ближайшее соответствие

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

Поиск по нескольким критериям

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

Другие примеры ИНДЕКС + ПОИСКПОЗ

Вот еще несколько основных примеров ИНДЕКС и ПОИСКПОЗ в действии, каждый из которых имеет подробное объяснение:

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

массив — диапазон ячеек или константа массива.
номер_строки – позиция строки в ссылке или массиве.
col_num – [необязательно] позиция столбца в ссылке или массиве.
area_num – [необязательный] диапазон ссылок, который следует использовать.

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

В большинстве случаев ИНДЕКС принимает три аргумента: массив, номер_строки и номер_столбца. Массив — это диапазон или массив, из которого извлекаются значения. Номер_строки – это номер строки, из которой нужно получить значение, а номер_столбца – это номер столбца, из которого нужно получить значение. Col_num является необязательным и не требуется, если массив является одномерным.

В примере, показанном выше, цель состоит в том, чтобы получить диаметр планеты Юпитер. Поскольку Юпитер — пятая планета в списке, а Диаметр — третья колонка, формула в G7 выглядит так:

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

Основное использование

ИНДЕКС получает значение в заданном месте в диапазоне ячеек на основе числового положения. Когда диапазон является одномерным, вам нужно указать только номер строки. Если диапазон двумерный, вам нужно будет указать номер строки и столбца. Например, чтобы получить третий элемент из одномерного диапазона A1:A5:

Приведенные ниже формулы показывают, как можно использовать ИНДЕКС для получения значения из двумерного диапазона:

ИНДЕКС и ПОИСКПОЗ

В приведенных выше примерах позиция "жестко закодирована". Обычно функция ПОИСКПОЗ используется для поиска позиций для ИНДЕКС. Например, на приведенном ниже экране функция ПОИСКПОЗ используется для поиска «Марса» (G6) в строке 3 и подачи этой позиции в ИНДЕКС. Формула в G7:

ПОИСКПОЗ передает номер строки (4) в ИНДЕКС. Номер столбца по-прежнему жестко закодирован как 3.

ИНДЕКС и ПОИСКПОЗ с горизонтальной таблицей

На приведенном ниже экране приведенная выше таблица перемещена по горизонтали. Функция ПОИСКПОЗ возвращает номер столбца (4), а номер строки жестко закодирован как 2. Формула в C10:

Подробное объяснение со множеством примеров см. в разделе Как использовать ИНДЕКС и ПОИСКПОЗ

Вся строка/столбец

Индекс можно использовать для возврата целых столбцов или строк следующим образом:

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

Ссылка как результат

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

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

Две формы

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

Форма массива

В форме массива INDEX первым параметром является массив, который предоставляется в виде диапазона ячеек или константы массива. Синтаксис формы массива ИНДЕКС:

Если указаны и номер_строки, и номер_столбца, функция ИНДЕКС возвращает значение в ячейке на пересечении номер_строки и номер_столбца< /эм>.

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

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

Справочная форма

В ссылочной форме ИНДЕКС первый параметр является ссылкой на один или несколько диапазонов, а четвертый необязательный аргумент, номер_области, предоставляется для выбора подходящего диапазона. спектр. Синтаксис справочной формы ИНДЕКС:

Как и форма массива ИНДЕКС, ссылочная форма ИНДЕКС возвращает ссылку на ячейку на пересечении номер_строки и номер_столбца. Разница в том, что аргумент ссылка содержит более одного диапазона, а номер_области выбирает, какой диапазон следует использовать. Аргумент номер_области предоставляется в виде числа, которое действует как числовой индекс. Первый массив внутри ссылки равен 1, второй массив равен 2 и т. д.

Например, в приведенной ниже формуле номер_области указывается как 2, что означает диапазон A7:C10:

В приведенной выше формуле ИНДЕКС вернет значение в строке 1 и столбце 3 A7:C10.

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

=INDEX() возвращает значение ячейки в таблице на основе номера столбца и строки.

=MATCH() возвращает позицию ячейки в строке или столбце.

В сочетании две формулы могут искать и возвращать значение ячейки в таблице на основе вертикального и горизонтального критериев. Для краткости это называется просто функцией Index Match. Чтобы просмотреть видеоруководство, посетите наш бесплатный ускоренный курс Excel.

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

Выполните следующие действия:

Введите «=INDEX(» и выберите область таблицы, затем добавьте запятую

Введите номер строки для Кевина (4) и добавьте запятую.

Введите номер столбца для высоты (2) и закройте квадратную скобку.

Результат: «5,8».

Используя тот же пример, что и выше, давайте воспользуемся ПОИСКПОЗ, чтобы выяснить, в какой строке находится Кевин.

Выполните следующие действия:

Введите «=ПОИСКПОЗ(» и укажите ссылку на ячейку, содержащую «Кевин»… имя, которое мы хотим найти.

Выделите все ячейки в столбце "Имя" (включая заголовок "Имя").

Введите ноль «0» для точного совпадения.

В результате Кевин находится в строке "4".

Используйте ПОИСКПОЗ еще раз, чтобы выяснить, в каком столбце находится высота.

Выполните следующие действия:

Введите «=ПОИСКПОЗ(» и укажите ссылку на ячейку, содержащую «Рост»… критерии, которые мы хотим найти.

Выберите все ячейки в верхней строке таблицы.

Введите ноль «0» для точного совпадения.

В результате высота находится в столбце "2".

Теперь мы можем взять две формулы ПОИСКПОЗ и использовать их для замены «4» и «2» в исходной формуле ИНДЕКС. Результатом является формула ПОИСКПОЗ ИНДЕКС.

Выполните следующие действия:

Вырежьте формулу ПОИСКПОЗ для Кевина и замените ею число "4".

Вырежьте формулу ПОИСКПОЗ для высоты и замените на нее цифру "2".

Результат: рост Кевина равен "5,8".

Поздравляем, теперь у вас есть динамическая формула ПОИСКПОЗ ИНДЕКС!

Видеообъяснение того, как использовать сопоставление индексов в Excel

Ниже представлен короткий видеоурок о том, как объединить две функции и эффективно использовать сопоставление индексов в Excel! Ознакомьтесь с другими бесплатными учебными пособиями по Excel на канале CFI на YouTube.

Надеюсь, из этого короткого видео стало еще понятнее, как использовать две функции, чтобы значительно улучшить возможности поиска в Excel.

Дополнительные уроки Excel

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

Читайте также:


Лучшая шахматная программа для Android

Как удалить безопасное соединение касперского

Cyberlink power media player 14 что это за программа

Как убрать полосу зацикливания в fl studio

Программное обеспечение Italc для управления компьютерным классом