Как использовать функции ИНДЕКС и ПОИСКПОЗ в Excel

2024 Автор: Abigail Brown | [email protected]. Последнее изменение: 2023-12-17 06:59

Что нужно знать

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

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

Что такое функции ИНДЕКС и ПОИСКПОЗ?

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

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

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

ИНДЕКС и ПОИСКПОЗ Синтаксис и аргументы

Вот как нужно написать обе функции, чтобы Excel их понял:

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

array - это диапазон ячеек, которые будет использовать формула. Это может быть одна или несколько строк и столбцов, например A1:D5. Это обязательно.
row_num – это строка в массиве, из которой нужно вернуть значение, например 2 или 18. Это необходимо, если не указан column_num.
column_num - это столбец в массиве, из которого нужно вернуть значение, например 1 или 9. Это необязательно.

=ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления])

lookup_value - это значение, которое вы хотите найти в lookup_array. Это может быть число, текст или логическое значение, введенное вручную или указанное через ссылку на ячейку. Это необходимо.
lookup_array - диапазон ячеек для просмотра. Это может быть одна строка или один столбец, например A2:D2 или G1:G45. Это необходимо.
match_type может принимать значения -1, 0 или 1. Он указывает, как искомое_значение сопоставляется со значениями в искомом_массиве (см. ниже). 1 - значение по умолчанию, если этот аргумент опущен.

Какой тип соответствия использовать
Тип соответствия	Что он делает	Правило	Пример
1	Находит наибольшее значение, которое меньше или равно искомому_значению.	Значения lookup_array должны быть расположены в порядке возрастания (например, -2, -1, 0, 1, 2; или A-Z;, или FALSE, TRUE.	lookup_value равно 25, но оно отсутствует в lookup_array, поэтому вместо него возвращается позиция следующего наименьшего числа, например 22.
0	Находит первое значение, которое точно равно искомому_значению.	Значения lookup_array могут быть в любом порядке.	lookup_value равно 25, поэтому возвращает позицию 25.
-1	Находит наименьшее значение, которое больше или равно искомому_значению.	Значения lookup_array должны быть расположены в порядке убывания (например, 2, 1, 0, -1, -2).	lookup_value равно 25, но оно отсутствует в lookup_array, поэтому вместо него возвращается позиция следующего по величине числа, например 34.

Используйте 1 или -1, когда вам нужно выполнить приблизительный поиск по шкале, например, при работе с числами и когда приближения допустимы. Но помните, что если вы не укажете match_type, по умолчанию будет 1, что может исказить результаты, если вы действительно хотите получить точное совпадение.

Пример формул ИНДЕКС и ПОИСКПОЗ

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

ИНДЕКС Примеры

=ИНДЕКС(A1:B2, 2, 2)

=ИНДЕКС(A1:B1, 1)

=ИНДЕКС(2:2, 1)=ИНДЕКС(B1:B2, 1)

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

=INDEX(A1:B2, 2, 2) просматривает A1:B2, чтобы найти значение во втором столбце и второй строке, то есть Stacy.
=INDEX(A1:B1, 1) просматривает A1:B1, чтобы найти значение в первом столбце, то есть Jon.
=INDEX(2:2, 1) просматривает все во второй строке, чтобы найти значение в первом столбце, то есть Tim.
=INDEX(B1:B2, 1) просматривает B1:B2, чтобы найти значение в первой строке, то есть Amy.

MATCH Примеры

=ПОИСКПОЗ("Стейси", A2:D2, 0)

=ПОИСКПОЗ(14, D1:D2)

=ПОИСКПОЗ(14, D1:D2, -1)=ПОИСКПОЗ(13, A1:D1, 0)

Вот четыре простых примера функции ПОИСКПОЗ:

=MATCH("Stacy", A2:D2, 0) ищет Stacy в диапазоне A2:D2 и возвращает 3 в качестве результата.
=ПОИСКПОЗ(14, D1:D2) ищет 14 в диапазоне D1:D2, но поскольку его нет в таблице, ПОИСКПОЗ находит следующее наибольшее значение это меньше или равно 14, что в данном случае равно 13, что находится в позиции 1 lookup_array.
=MATCH(14, D1:D2, -1) идентична приведенной выше формуле, но поскольку массив не находится в порядке убывания, как требует -1, получаем ошибку.
=ПОИСКПОЗ(13, A1:D1, 0) ищет 13 в первой строке листа, который возвращает 4, так как это четвертый элемент в этом массиве.

INDEX-MATCH Примеры

Вот два примера, где мы можем объединить ИНДЕКС и ПОИСКПОЗ в одну формулу:

Найти ссылку на ячейку в таблице

=ИНДЕКС(B2:B5, ПОИСКПОЗ(F1, A2:A5))

В этом примере формула ПОИСКПОЗ вложена в формулу ИНДЕКС. Цель состоит в том, чтобы определить цвет предмета по номеру предмета.

Если вы посмотрите на изображение, вы увидите в строках «Отдельно», как формулы будут записаны сами по себе, но поскольку мы их вкладываем, вот что происходит:

MATCH(F1, A2:A5) ищет значение F1 (8795) в наборе данных A2:A5. Если мы посчитаем столбец в обратном порядке, мы увидим, что это 2, так что это то, что только что вычислила функция ПОИСКПОЗ.
Массив INDEX имеет вид B2:B5, так как в конечном итоге мы ищем значение в этом столбце.
Функция ИНДЕКС теперь может быть переписана следующим образом, поскольку ПОИСКПОЗ находит 2: ИНДЕКС(B2:B5, 2, [номер_столбца]).
Поскольку column_num является необязательным, мы можем удалить его, оставив следующее: INDEX(B2:B5, 2).
Теперь это похоже на обычную формулу ИНДЕКС, в которой мы находим значение второго элемента в B2:B5, красного цвета.

Поиск по заголовкам строк и столбцов

=ИНДЕКС(B2:E13, ПОИСКПОЗ(G1, A2:A13, 0), ПОИСКПОЗ(G2, B1:E1, 0))

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

ПОИСКПОЗ(G1, A2:A13, 0) - первое решение этой формулы. Он ищет G1 (слово «май») в A2:A13, чтобы получить определенное значение. Мы не видим его здесь, но это 5.
ПОИСКПОЗ(G2, B1:E1, 0) - вторая формула ПОИСКПОЗ, очень похожая на первую, но вместо этого ищущая G2 (слово «Зеленый») в заголовках столбцов в B1:E1. Этот разрешается в 3.
Теперь мы можем переписать формулу ИНДЕКС следующим образом, чтобы визуализировать происходящее: =ИНДЕКС(B2:E13, 5, 3). Это ищет во всей таблице B2:E13 пятую строку и третий столбец, который возвращает $180.

Правила ПОИСКПОЗ и ИНДЕКС

При написании формул с этими функциями следует помнить о нескольких вещах:

ПОИСКПОЗ не чувствителен к регистру, поэтому прописные и строчные буквы обрабатываются одинаково при сопоставлении текстовых значений.
ПОИСКПОЗ возвращает Н/Д по нескольким причинам: если тип_сопоставления равен 0, а искомое_значение не найдено, если тип_сопоставления равно -1 и искомый_массив не в порядке убывания, если тип_сопоставления равен 1, а искомый_массив не находится в порядке возрастания порядке, и если lookup_array не является отдельной строкой или столбцом.
Вы можете использовать подстановочный знак в аргументе искомое_значение, если тип_сопоставления равен 0, а искомое_значение является текстовой строкой. Знак вопроса соответствует любому одиночному символу, а звездочка соответствует любой последовательности символов (например,g., =MATCH("Jo", 1:1, 0)). Чтобы использовать ПОИСКПОЗ для поиска действительного вопросительного знака или звездочки, сначала введите ~.
ИНДЕКС возвращает ССЫЛКА! если row_num и column_num не указывают на ячейку в массиве.

Связанные функции Excel

Функция ПОИСКПОЗ похожа на ПРОСМОТР, но ПОИСКПОЗ возвращает позицию элемента вместо самого элемента.

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