Как использовать ИНДЕКС и ПОИСКПОЗ для поиска Значения в Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: lookup-formulas

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

Пример

В этом примере у меня есть таблица студентов. В столбце A указаны имена учащихся, в столбце B — номер опроса, а в столбце C — баллы.

Теперь нам нужно найти название ролика № 104, как показано на изображении ниже.

Если бы мы хотели узнать счет, мы бы использовали ВПР. Почему не здесь?

ВПР извлекает данные только из правой части таблицы. Он ничего не может сделать для столбцов слева.

Таким образом, для поиска значения слева от таблицы в Excel мы будем использовать Excel`link: / lookup-formulas-excel-index-function [INDEX] `link: / lookup-formulas-excel-match-function [MATCH] `.

В ячейке G2 я написал ссылку: / lookup-formulas-excel-index-function [INDEX] link: / lookup-formulas-excel-match-function [MATCH]

формула для получения данных слева от столбца.

=INDEX(A2:A10,MATCH(F2,B2:B10,0))

Общая формула ИНДЕКС ПОИСКПОЗ для поиска будет

=INDEX (range from where you want to retrieve data,Match(lookup_value,lookup range,0))

Это работает. Как? Пойдем, разберемся.

ИНДЕКС МАТЧ Пояснение:

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

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

ИНДЕКС возвращает значение из заданного диапазона с заданным номером индекса.

link: / lookup-formulas-excel-index-function [Функция INDEX]:

=INDEX (range from where you want to retrieve data, row index, [optional col index])

Например, из нашей таблицы учеников, если я хочу получить 4-е значение из диапазона A2: A10, я напишу:

=INDEX(A2:A10, 4)

Он вернет «Kavita», которое является 4-м значением в данном диапазоне. Здесь номер строки жестко запрограммирован.

Это тебя бьет? Да уж! Теперь нам нужно получить этот порядковый номер «Кавиты» с помощью формулы, и тогда она будет работать. Сработала функция ПОИСКПОЗ.

link: / lookup-formulas-excel-index-function [Функция ПОИСКПОЗ] в Excel Функция ПОИСКПОЗ возвращает номер индекса для первого появления значения в диапазоне.

Общая формула MATCH:

=MATCH (lookup value,range, 0 for exact match)

Например, из нашей таблицы студентов, если я хочу получить индекс или сказать номер строки 104, я напишу:

=MATCH (104,B2:B10,0)

Это вернет 4. А теперь подумайте … Да, мы можем использовать функцию ПОИСКПОЗ, чтобы присвоить функции ИНДЕКС ее индексную переменную.

Использование INDEX- MATCH

Мы уже использовали формулу ИНДЕКС-ПОИСКПОЗ в начале этой статьи.

Давайте разберемся, что:

ИНДЕКС (A2: A10, MATCH (F2, B2: B10,0)):

Функция ПОИСКПОЗ ищет номер индекса значения в F2 (104) в диапазоне B2: B10 и возвращает 4.

Теперь у нас есть ИНДЕКС (A2: A10,4). Затем функция ИНДЕКС переходит к 4-му индексу в диапазоне A2: A10 и возвращает его значение, которым является «Kavita». И дело сделано. Просто, не правда ли? Часто люди пугаются функции ИНДЕКС ПОИСКПОЗ. Они говорят, что это слишком сложно, но на самом деле это намного проще, чем формула ВПР.

Остается одна тема. В чем разница между VLOOKUP и INDEX MATCH? Давайте скроем это быстро.

VLOOKUP vs INDEX MATCH

VLOOKUP can’t retrieve data from the left of the table

INDEX MATCH can

fetch data from the left of the table

VLOOKUP needs the whole Table for reference

INDEX MATCH only needs two

columns of the table

VLOOKUP requires the column number from which you want to retrieve the

data. Thus you need to count columns manually first or take help from

another function.

INDEX MATCH does not need a column number. Hence less

human effort is required.

ЗАМЕЧАНИЯ ПРО:

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

Однако он также работает в двух измерениях. Если вы выберете 2D-диапазон, вы можете определить, из какого столбца вы хотите получить значение.

Например, ИНДЕКС (A2: C10,4,3) вернет 4-е значение из третьего столбца, которое в нашем примере равно 51.

Третий аргумент (тип соответствия) поддерживает еще 2 параметра: 1 для меньше и -1 для больше. Я не объясняю их здесь. Попробуйте сами.

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

Статьи по теме:

link: / lookup-formulas-vlookup-top-5-values-with-duplicate-values-using-index-match-in-excel [Поиск первых 5 значений с повторяющимися значениями с помощью INDEX-MATCH в Excel] link: / lookup-formulas-vlookup-multiple-values [Как выполнить ВПР для нескольких значений в Excel] «ссылка: / lookup-formulas-vlookup-with-dynamic-col-index [Как выполнить ВПР с динамическим индексом столбцов в Excel]« ссылка: / lookup-formulas-use-vlookup-from-two-or-more-lookup-tables [Как использовать ВПР из двух или более таблиц поиска в Excel] `