Индекс позиции поиска в Excel

Обновлено: 02.07.2024

Формула 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 будут полезны для продолжения обучения и совершенствования своих навыков:

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

Есть два способа использования функции ИНДЕКС:

Если вы хотите вернуть значение указанной ячейки или массива ячеек, см. раздел Форма массива.

Если вы хотите вернуть ссылку на указанные ячейки, см. форму ссылки.

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

Описание

Возвращает значение элемента в таблице или массиве, выбранное с помощью индексов номеров строк и столбцов.

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

Синтаксис

ИНДЕКС(массив, номер_строки, [номер_столбца])

Форма массива функции ИНДЕКС имеет следующие аргументы:

Обязательный массив. Диапазон ячеек или константа массива.

Если массив содержит только одну строку или столбец, соответствующий аргумент row_num или column_num является необязательным.

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

номер_строки Обязательно, если не указан номер_столбца. Выбирает строку в массиве, из которой нужно вернуть значение. Если номер_строки опущен, требуется номер_столбца.

номер_столбца Необязательно. Выбирает столбец в массиве, из которого нужно вернуть значение. Если номер_столбца опущен, требуется номер_строки.

Примечания

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

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

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

Примеры

Пример 1

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

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД.

Описание

Значение на пересечении второй строки и второго столбца в диапазоне A2:B3.

Значение на пересечении второй строки и первого столбца в диапазоне A2:B3.

Пример 2

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

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

Описание

Значение найдено в первой строке, втором столбце массива. Массив содержит 1 и 2 в первой строке и 3 и 4 во второй строке.

Значение найдено во второй строке, во втором столбце массива (тот же массив, что и выше).

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

Описание

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

Синтаксис

ИНДЕКС(ссылка, номер_строки, [номер_столбца], [номер_области])

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

ссылка обязательна. Ссылка на один или несколько диапазонов ячеек.

Если вы вводите несмежный диапазон для ссылки, заключите ссылку в круглые скобки.

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

номер_строки Обязательно. Номер строки в ссылке, из которой возвращается ссылка.

номер_столбца Необязательно. Номер столбца в ссылке, из которого возвращается ссылка.

Например, если ссылка описывает ячейки (A1:B4,D1:E4,G1:H4), area_num 1 – это диапазон A1:B4, area_num 2 – это диапазон D1:E4, а area_num 3 – это диапазон G1. :H4.

Примечания

После того, как reference и area_num выбрали определенный диапазон, row_num и column_num выбирают конкретную ячейку: row_num 1 — это первая строка в диапазоне, column_num 1 — это первый столбец и так далее. Ссылка, возвращаемая ИНДЕКСом, представляет собой пересечение row_num и column_num.

Если задать для row_num или column_num значение 0 (ноль), ИНДЕКС возвращает ссылку для всего столбца или строки соответственно.

Результат функции ИНДЕКС является эталоном и интерпретируется как таковой другими формулами. В зависимости от формулы возвращаемое значение ИНДЕКС может использоваться как ссылка или как значение.Например, формула ЯЧЕЙКА("ширина",ИНДЕКС(A1:B2,1,2)) эквивалентна ЯЧЕЙКЕ("ширина",B1). Функция CELL использует возвращаемое значение INDEX в качестве ссылки на ячейку. С другой стороны, такая формула, как 2*ИНДЕКС(A1:B2,1,2), преобразует возвращаемое значение ИНДЕКС в число в ячейке B1.

Примеры

Функция ИНДЕКС 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.

Совет. Попробуйте использовать новые функции XLOOKUP и XMATCH, улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их более простыми и удобными в использовании, чем их предшественники.

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

Функции ВПР и ГПР вместе с ИНДЕКС и ПОИСКПОЗ — одни из самых полезных функций в Excel.

Примечание. Мастер поиска больше не доступен в Excel.

Вот пример использования функции ВПР.

=ВПР(B2,C2:E7,3,ИСТИНА)

В этом примере B2 — это первый аргумент — элемент данных, необходимый для работы функции. Для ВПР этот первый аргумент — это значение, которое вы хотите найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например «кузнец» или 21 000. Второй аргумент — это диапазон ячеек C2-:E7, в котором нужно искать значение, которое вы хотите найти. Третий аргумент — это столбец в этом диапазоне ячеек, который содержит искомое значение.

Четвертый аргумент является необязательным. Введите ИСТИНА или ЛОЖЬ. Если вы введете TRUE или оставите аргумент пустым, функция вернет приблизительное соответствие значению, указанному вами в первом аргументе. Если вы введете FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, оставляя четвертый аргумент пустым или вводя значение TRUE, вы получаете больше гибкости.

В этом примере показано, как работает функция. Когда вы вводите значение в ячейку B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2:E7 (2-й аргумент) и возвращает наиболее близкое приближенное совпадение из третьего столбца диапазона, столбца E (3-й аргумент).< /p>

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

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

Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР

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

В этом примере показан небольшой список, в котором значение, которое мы хотим найти, Чикаго, отсутствует в крайнем левом столбце. Итак, мы не можем использовать функцию ВПР. Вместо этого мы будем использовать функцию ПОИСКПОЗ, чтобы найти Чикаго в диапазоне B1:B11. Оно находится в строке 4. Затем ИНДЕКС использует это значение в качестве аргумента поиска и находит население Чикаго в 4-м столбце (столбец D). Используемая формула показана в ячейке A14.

Попробуйте

Если вы хотите поэкспериментировать с функциями поиска, прежде чем применять их к своим собственным данным, вот несколько примеров данных.

Пример VLOOKUP в работе

Скопируйте следующие данные в пустую электронную таблицу.

Совет. Прежде чем вставлять данные в Excel, задайте для столбцов с A по C ширину 250 пикселей и нажмите "Обтекание текстом" (вкладка "Главная", группа "Выравнивание").

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


Программное обеспечение для mac os

Приложение Mask Photo для Android

Как сделать вывеску в фотошопе

Что такое браузерные трекеры

Как поменять местами столбцы в Excel