Содержание
- Функция ИНДЕКС в Excel (примеры + видео)
- Функция ИНДЕКС в Excel — Примеры
- Функция ИНДЕКС в Excel — видеоурок
Когда использовать функцию ИНДЕКС в Excel
Функцию ИНДЕКС Excel можно использовать, если вы хотите получить значение из табличных данных и у вас есть номер строки и номер столбца точки данных. Например, в приведенном ниже примере вы можете использовать функцию ИНДЕКС, чтобы получить отметки «Том» в физике, если вы знаете номер строки и номер столбца в наборе данных.
Что это возвращает
Он возвращает значение из таблицы для указанного номера строки и номера столбца.
Синтаксис
= ИНДЕКС (массив; номер_строки; [номер_столбца])
= ИНДЕКС (массив; номер_строки; [номер_столбца]; [номер_площади])
Функция ИНДЕКС имеет 2 синтаксиса. Первый используется в большинстве случаев, однако в случае трехстороннего поиска используется второй (описанный в примере 5).
Входные аргументы
- множество — а диапазон ячеек или константа массива.
- row_num — номер строки, из которой должно быть извлечено значение.
- [col_num] — номер столбца, из которого должно быть извлечено значение. Хотя это необязательный аргумент, но если row_num не указан, его необходимо указать.
- [area_num] — (Необязательно) Если аргумент массива состоит из нескольких диапазонов, это число будет использоваться для выбора ссылки из всех диапазонов.
Дополнительные примечания (скучный материал… но важно знать)
- Если номер строки или столбца равен 0, он возвращает значения всей строки или столбца соответственно.
- Если функция ИНДЕКС используется перед ссылкой на ячейку (например, A1 :), она возвращает ссылку на ячейку вместо значения (см. Примеры ниже).
- Наиболее широко используется вместе с функцией ПОИСКПОЗ.
- В отличие от ВПР, функция ИНДЕКС может возвращать значение слева от значения поиска.
- Функция ИНДЕКС имеет две формы — форма массива и справочная форма.
- «Форма массива» — это место, где вы выбираете значение на основе номера строки и столбца из данной таблицы.
- «Справочная форма» — это когда есть несколько таблиц, и вы используете аргумент area_num, чтобы выбрать таблицу, а затем получить значение в ней, используя номер строки и столбца (см. Живой пример ниже).
Функция ИНДЕКС в Excel — Примеры
Вот шесть примеров использования функции ИНДЕКС Excel.
Пример 1. Поиск оценок Тома по физике (двусторонний поиск)
Предположим, у вас есть набор данных, как показано ниже:
Чтобы узнать оценки Тома по физике, используйте следующую формулу:
= ИНДЕКС ($ B $ 3: $ E $ 10,3,2)
Эта формула ИНДЕКС определяет массив как $ B $ 3: $ E $ 10, в котором есть оценки для всех предметов. Затем он использует номер строки (3) и номер столбца (2) для получения оценок Тома по физике.
Пример 2 — Создание динамического значения LOOKUP с помощью функции MATCH
Не всегда возможно указать номер строки и номер столбца вручную. У вас может быть огромный набор данных или вы можете захотеть сделать его динамическим, чтобы он автоматически определял имя и / или тему, указанные в ячейках, и давал правильный результат.
Что-то вроде того, что показано ниже:
Это можно сделать с помощью комбинации ИНДЕКС и функции ПОИСКПОЗ.
Вот формула, которая сделает поисковые значения динамическими:
= ИНДЕКС ($ B $ 3: $ E $ 10, ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0), ПОИСКПОЗ ($ H $ 4, $ B $ 2: $ E $ 2,0))
В приведенной выше формуле вместо жесткого кодирования номера строки и номера столбца используется функция ПОИСКПОЗ, чтобы сделать его динамическим.
- Динамический номер строки задается следующей частью формулы — ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0). Он сканирует имена студентов и определяет значение поиска (в данном случае $ G $ 5). Затем он возвращает номер строки искомого значения в наборе данных. Например, если значение поиска — Мэтт, оно вернет 1, если это Боб, оно вернет 2 и так далее.
- Номер динамического столбца задается следующей частью формулы — ПОИСКПОЗ ($ H $ 4, $ B $ 2: $ E $ 2,0). Он сканирует имена субъектов и определяет значение поиска (в данном случае $ H $ 4). Затем он возвращает номер столбца искомого значения в наборе данных. Например, если значение поиска Math, оно вернет 1, если это Physics, оно вернет 2 и так далее.
Пример 3 — Использование раскрывающихся списков в качестве значений поиска
В приведенном выше примере нам нужно вручную ввести данные. Это может занять много времени и привести к ошибкам, особенно если у вас есть огромный список значений поиска.
В таких случаях хорошей идеей является создание раскрывающегося списка значений поиска (в данном случае это могут быть имена учащихся и предметы), а затем просто выбирать из списка. На основе выбора формула автоматически обновит результат.
Что-то вроде того, что показано ниже:
Это хороший компонент приборной панели, так как у вас может быть огромный набор данных с сотнями учеников в конце, но конечный пользователь (скажем, учитель) может быстро получить оценки ученика по предмету, просто сделав выбор из выпадающий.
Как это сделать:
Формула, использованная в этом случае, такая же, как в Примере 2.
= ИНДЕКС ($ B $ 3: $ E $ 10, ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0), ПОИСКПОЗ ($ H $ 4, $ B $ 2: $ E $ 2,0))
Значения поиска преобразованы в раскрывающиеся списки.
Вот шаги для создания раскрывающегося списка Excel:
- Выберите ячейку, в которой вы хотите открыть раскрывающийся список. В этом примере в G4 нам нужны имена учеников.
- Перейдите в Data -> Data Tools -> Data Validation.
- В диалоговом окне «Проверка данных» на вкладке настроек выберите «Список» в раскрывающемся списке «Разрешить».
- В источнике выберите $ A $ 3: $ A $ 10
- Щелкните ОК.
Теперь в ячейке G5 появится раскрывающийся список. Точно так же вы можете создать его в H4 для предметов.
Пример 4 — Возвращаемые значения из всей строки / столбца
В приведенных выше примерах мы использовали функцию ИНДЕКС Excel, чтобы выполнить двусторонний поиск и получить одно значение.
А что, если вы хотите получить все оценки студента. Это может позволить вам найти максимальный / минимальный балл этого ученика или общее количество баллов, набранных по всем предметам.
Говоря простым языком, вы хотите сначала получить всю строку баллов для ученика (скажем, Боба), а затем в рамках этих значений определить наивысший балл или общую сумму всех баллов.
Вот уловка.
В функции ИНДЕКС Excel при вводе номер столбца как 0, он вернет значения всей этой строки.
Итак, формула для этого будет:
= ИНДЕКС ($ B $ 3: $ E $ 10, ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Теперь это формула. если использовать как есть, вернет #VALUE! ошибка. В то время как он отображает ошибку, в бэкэнде он возвращает массив, в котором есть все оценки для Тома — {57,77,91,91}.
Если вы выберете формулу в режиме редактирования и нажмете F9, вы сможете увидеть массив, который она возвращает (как показано ниже): 
Точно так же, в зависимости от того, что такое значение поиска, когда номер столбца указан как 0 (или оставлен пустым), он возвращает все значения в строке для значения поиска.
Теперь, чтобы подсчитать общий балл, полученный Томом, мы можем просто использовать приведенную выше формулу в функции СУММ.
= СУММ (ИНДЕКС ($ B $ 3: $ E $ 10, ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
Аналогично, для вычисления наивысшего балла мы можем использовать MAX / LARGE, а для вычисления минимума — MIN / SMALL.
Пример 5 — Трехсторонний поиск с использованием INDEX / MATCH
Функция ИНДЕКС Excel предназначена для обработки трехстороннего поиска.
Что такое трехсторонний поиск?
В приведенных выше примерах мы использовали одну таблицу с оценками учащихся по разным предметам. Это пример двустороннего поиска, поскольку мы используем две переменные для получения оценки (имя учащегося и предмет).
Теперь предположим, что за год у студента есть экзамены трех разных уровней: модульный тест, промежуточный экзамен и итоговый экзамен (это то, что я сдавал, когда был студентом).
Трехсторонний поиск — это возможность получить оценки учащегося по указанному предмету на заданном уровне экзамена. Это сделало бы поиск трехсторонним, поскольку есть три переменных (имя студента, имя предмета и уровень экзамена).
Вот пример трехстороннего поиска:
В приведенном выше примере, помимо выбора имени студента и предмета, вы также можете выбрать уровень экзамена. В зависимости от уровня экзамена он возвращает соответствующее значение из одной из трех таблиц.
Вот формула, используемая в ячейке H4:
= ИНДЕКС (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), ПОИСКПОЗ ($ G $ 4, $ A $ 3: $ A $ 7,0), ПОИСКПОЗ ($ H $ 3, $ B $ 2: $ E $ 2,0), IF ($ H $ 2 = «Модульный тест», 1, IF ($ H $ 2 = «Среднесрочный период», 2,3)))
Давайте разберемся с этой формулой, чтобы понять, как она работает.
Эта формула принимает четыре аргумента. ИНДЕКС — одна из тех функций в Excel, которые имеют более одного синтаксиса.
= ИНДЕКС (массив; номер_строки; [номер_столбца])
= ИНДЕКС (массив; номер_строки; [номер_столбца]; [номер_площади])
До сих пор во всех приведенных выше примерах мы использовали первый синтаксис, но для трехстороннего поиска нам нужно использовать второй синтаксис.
Теперь давайте посмотрим на каждую часть формулы на основе второго синтаксиса.
- array — ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): в данном случае вместо использования одного массива мы использовали три массива в скобках.
- row_num — ПОИСКПОЗ ($ G $ 4, $ A $ 3: $ A $ 7,0): функция ПОИСКПОЗ используется для поиска позиции имени учащегося в ячейке $ G $ 4 в списке имен учащихся.
- col_num — ПОИСКПОЗ ($ H $ 3, $ B $ 2: $ E $ 2,0): функция ПОИСКПОЗ используется для поиска позиции имени субъекта в ячейке $ H $ 3 в списке имен субъектов.
- [area_num] — IF ($ H $ 2 = «Unit Test», 1, IF ($ H $ 2 = «Midterm», 2,3)): значение номера области сообщает функции INDEX, какой массив выбрать. В этом примере у нас есть три массива в первом аргументе. Если вы выбираете Unit Test из раскрывающегося списка, функция IF возвращает 1, а функции INDEX выбирают 1-й массив из трех массивов (это $ B $ 3: $ E $ 7).
Пример 6 — Создание ссылки с помощью функции ИНДЕКС (динамические именованные диапазоны)
Это одно из безумных применений функции ИНДЕКС в Excel.
Возьмем простой пример.
У меня есть список имен, как показано ниже:
Теперь я могу использовать простую функцию ИНДЕКС, чтобы получить фамилию в списке.
Вот формула:
= ИНДЕКС ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Эта функция просто подсчитывает количество непустых ячеек и возвращает последний элемент из этого списка (работает только тогда, когда в списке нет пробелов).
Итак, что здесь происходит с магией.
Если вы поместите формулу перед ссылкой на ячейку, формула вернет ссылку на ячейку с совпадающим значением (вместо самого значения).
= A2: ИНДЕКС ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Вы ожидаете, что приведенная выше формула вернет = A2: «Джош» (где Джош — последнее значение в списке). Однако он возвращает = A2: A9 и, следовательно, вы получаете массив имен, как показано ниже:
Одним из практических примеров, где может быть полезна эта техника, является создание динамических именованных диапазонов.
Это все в этом руководстве. Я попытался охватить основные примеры использования функции ИНДЕКС Excel. Если вы хотите, чтобы в этот список было добавлено больше примеров, дайте мне знать в разделе комментариев.
Примечание. Я изо всех сил старался проверить это руководство, но если вы обнаружите какие-либо ошибки или орфографические ошибки, сообщите мне об этом 🙂
Функция ИНДЕКС в Excel — видеоурок
- Функция ВПР в Excel.
- Функция Excel HLOOKUP.
- Функция НЕПРЯМАЯ в Excel.
- Функция ПОИСКПОЗ в Excel.
- Функция смещения Excel.
Вам также могут понравиться следующие руководства по Excel:
- VLOOKUP Vs. ИНДЕКС / МАТЧ
- Соответствие индекса Excel
- Поиск и возврат значений во всей строке / столбце.
содержание видео
В данном уроке рассматриваем сразу две функции в Excel — ИНДЕКС и ПОИСКПОЗ. Связка данных функций хорошая альтернатива функции ВПР в Excel. Подробно разбираем возможности, аргументы этих функций
Дата: 2022-01-11
Комментарии и отзывы: 3
Доброе утро, Павел!
Подскажите пожалуйста, а можно ли с помощью ВПР или с помощью ИНДЕКС и ПОИСКПОЗ вытягивать не только первое найденное совпадение?
Например, нам нужно вытянуть города в которых есть холодильники. Проблема в том что обе функции нам будут вытягивать всегда Вологду, а нам нужно чтоб второй раз нам уже вытягивало Москву.
Анастасия
Здравствуйте, большое спасибо за ваши видео, очень полезно!
Есть один вопрос по гугл таблицам, не подскажете, как сделать так, чтобы от пользователей таблицы были скрыты другие пользователи, то есть чтобы в поле: просматривают файл, они были не видны? Заранее спасибо!
Виктор
Здравствуйте, я делаю интернет магазин по вашем урокам и на 5 уроке у меня не получается настроить умную таблицу Раздел и Группа не могли ли вы помоч мне как нибудь с этой проблемой?
Другие видео канала
16:48
11:33
12:26
22:23
12:34
11:57
15:13
15:1
11:14
9:43
Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?
Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX). Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.
Вариант 1. Извлечение данных из столбца по номеру ячейки
Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:
=ИНДЕКС(Диапазон_столбец; Порядковый_номер_ячейки)
Этот вариант известен большинству продвинутых пользователей Excel. В таком виде функция ИНДЕКС часто используется в связке с функцией ПОИСКПОЗ (MATCH), которая выдает номер искомого значения в диапазоне. Таким образом, эта пара заменяет легендарную ВПР (VLOOKUP):
… но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.
Вариант 2. Извлечение данных из двумерного диапазона
Если диапазон двумерный, т.е. состоит из нескольких строк и столбцов, то наша функция будет использоваться немного в другом формате:
=ИНДЕКС(Диапазон; Номер_строки; Номер_столбца)
Т.е. функция извлекает значение из ячейки диапазона с пересечения строки и столбца с заданными номерами.
Легко сообразить, что с помощью такой вариации ИНДЕКС и двух функций ПОИСКПОЗ можно легко реализовать двумерный поиск:
Вариант 3. Несколько таблиц
Если таблица не одна, а их несколько, то функция ИНДЕКС может извлечь данные из нужной строки и столбца именно заданной таблицы. В этом случае используется следующий синтаксис:
=ИНДЕКС((Диапазон1;Диапазон2;Диапазон3); Номер_строки; Номер_столбца; Номер_диапазона)
Обратите особое внимание, что в этом случае первый аргумент – список диапазонов — заключается в скобки, а сами диапазоны перечисляются через точку с запятой.
Вариант 4. Ссылка на столбец / строку
Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:
Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM), СРЗНАЧ (AVERAGE) и т.п.
Вариант 5. Ссылка на ячейку
Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:
Нечто похожее можно реализовать функцией СМЕЩ (OFFSET), но она, в отличие от ИНДЕКС, является волатильной, т.е. пересчитывается каждый раз при изменении любой ячейки листа. ИНДЕКС же работает более тонко и запускает пересчет только при изменении своих аргументов, что ощутимо ускоряет расчет в тяжелых книгах по сравнению со СМЕЩ.
Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте — это сочетание с функцией СЧЁТЗ (COUNTA), чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.
Ссылки по теме
- Трехмерный поиск данных по нескольким листам (ВПР 3D)
- Поиск и подстановка по нескольким условиям (ВПР по нескольким столбцам)
- Подстановка данных из одной таблицы в другую с помощью функции ВПР (VLOOKUP)
Skip to content
В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР.
В нескольких недавних статьях мы приложили немало усилий, чтобы объяснить основы функции ВПР новичкам и предоставить более сложные примеры формул ВПР опытным пользователям. А теперь я постараюсь если не отговорить вас от использования ВПР, то хотя бы показать вам альтернативный способ поиска нужных значений в Excel.
- Краткий обзор функций ИНДЕКС и ПОИСКПОЗ
- Как использовать формулу ИНДЕКС ПОИСКПОЗ
- ИНДЕКС+ПОИСКПОЗ вместо ВПР?
- Поиск справа налево
- Двусторонний поиск в строках и столбцах
- ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям
- Как найти среднее, максимальное и минимальное значение
- Что делать с ошибками поиска?
Для чего это нужно? Потому что функция ВПР имеет множество ограничений, которые могут помешать вам получить желаемый результат во многих ситуациях. С другой стороны, комбинация ПОИСКПОЗ ИНДЕКС более гибкая и имеет много замечательных возможностей, которые во многих отношениях превосходят ВПР.
Функции Excel ИНДЕКС и ПОИСКПОЗ — основы
Поскольку целью этого руководства является демонстрация альтернативного способа выполнения поиска в Excel с использованием комбинации функций ИНДЕКС и ПОИСКПОЗ, мы не будем подробно останавливаться на их синтаксисе и использовании. Тем более, что это подробно рассмотрено в других статьях, ссылки на которые вы можете найти в конце этого руководства. Мы рассмотрим лишь минимум, необходимый для понимания общей идеи, а затем подробно рассмотрим примеры формул, раскрывающие все преимущества использования ПОИСКПОЗ и ИНДЕКС вместо ВПР.
Функция ИНДЕКС
Функция ИНДЕКС (в английском варианте – INDEX) возвращает значение в массиве на основе указанных вами номеров строк и столбцов. Синтаксис функции ИНДЕКС прост:
ИНДЕКС(массив,номер_строки,[номер_столбца])
Вот простое объяснение каждого параметра:
- массив — это диапазон ячеек, именованный диапазон или таблица.
- номер_строки — это номер строки в массиве, из которого нужно вернуть значение. Если этот аргумент опущен, требуется следующий – номер_столбца.
- номер_столбца — это номер столбца, из которого нужно вернуть значение. Если он опущен, требуется номер_строки.
Дополнительные сведения см. в статье Функция ИНДЕКС в Excel .
А вот пример формулы ИНДЕКС в самом простом виде:
=ИНДЕКС(A1:C10;2;3)
Формула выполняет поиск в ячейках с A1 по C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, т. е. в ячейке C2.
Очень легко, правда? Однако при работе с реальными данными вы вряд ли когда-нибудь будете заранее знать, какие строки и столбцы вам нужны. Здесь вам пригодится ПОИСКПОЗ.
Функция ПОИСКПОЗ
Она ищет нужное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.
Синтаксис функции ПОИСКПОЗ следующий:
ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения])
- искомое_значение — числовое или текстовое значение, которое вы ищете.
- диапазон_поиска — диапазон ячеек, в которых будем искать.
- тип_совпадения — указывает, следует ли искать точное соответствие или наиболее близкое совпадение:
- 1 или опущено — находит наибольшее значение, которое меньше или равно искомому значению. Требуется сортировка массива поиска в порядке возрастания.
- 0 — находит первое значение, точно равное искомому значению. В комбинации ИНДЕКС/ПОИСКПОЗ вам почти всегда нужно точное совпадение, поэтому вы чаще всего устанавливаете третий аргумент вашей функции в 0.
- -1 — находит наименьшее значение, которое больше или равно искомому значению. Требуется сортировка массива поиска в порядке убывания.
Например, если диапазон B1:B3 содержит значения «яблоки», «апельсины», «лимоны», приведенная ниже формула возвращает число 3, поскольку «лимоны» — это третья по счету запись в этом диапазоне:
=ПОИСКПОЗ(«лимоны»;B1:B3;0)
Дополнительные сведения см . в статье Функция ПОИСКПОЗ в Excel .
На первый взгляд полезность функции ПОИСКПОЗ может показаться сомнительной. Кого волнует положение значения в диапазоне? Что мы действительно хотим определить, так это само значение.
Однако, относительная позиция искомого значения (т. е. номера строки и столбца, в которых оно находится) — это именно то, что нам нужно указать для аргументов номер_строки и номер_столбца функции ИНДЕКС. Как вы помните, ИНДЕКС может найти значение на пересечении заданной строки и столбца, но сама не может определить, какую именно строку и столбец ей нужно выбрать.
Вот поэтому совместное использование ИНДЕКС и ПОИСКПОЗ открывает перед нами массу возможностей для поиска в Excel.
Как использовать формулу ИНДЕКС ПОИСКПОЗ в Excel
Теперь, когда вы знаете основы, я считаю, что вы уже начали понимать, как ПОИСКПОЗ и ИНДЕКС работают вместе. Короче говоря, ИНДЕКС извлекает нужное значение по номерам столбцов и строк, а ПОИСКПОЗ предоставляет ей эти номера. Вот и все!
Для вертикального поиска вы используете функцию ПОИСКПОЗ только для определения номера строки, указывая диапазон столбцов непосредственно в самой формуле:
ИНДЕКС ( столбец для возврата значения ; ПОИСКПОЗ ( искомое значение ; столбец для поиска ; 0))
Все еще не совсем понимаете эту логику? Возможно, будет проще разобрать на примере. Предположим, у вас есть список национальных столиц и их население:
Чтобы найти население определенной столицы, скажем, Индии, используйте следующую формулу ПОИСКПОЗ ИНДЕКС:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(“Индия”;A2:A10;0))
Теперь давайте проанализируем, что на самом деле делает каждый компонент этой формулы:
- Функция ПОИСКПОЗ ищет искомое значение «Индия» в диапазоне A2:A10 и возвращает число 2, поскольку это слово занимает второе место в массиве поиска.
- Этот номер поступает непосредственно в аргумент номер_строки функции ИНДЕКС, предписывая вернуть значение из этой строки.
Таким образом, приведенная выше формула превращается в ИНДЕКС(C2:C10;2), которая означает, что нужно искать в ячейках от C2 до C10 и извлекать значение из второй ячейки в этом диапазоне, то есть из C3, потому что мы начинаем отсчет со второй строки.
Но указывать название города в формуле не совсем правильно, так как для каждого нового поиска придется корректировать эту формулу. Введите его в какую-нибудь отдельную ячейку, скажем, F1, укажите ссылку на ячейку для ПОИСКПОЗ, и вы получите формулу динамического поиска:
=ИНДЕКС(C2:C10;ПОИСКПОЗ(F1;A2:A10;0))
Важное замечание! Количество строк в аргументе массив функции ИНДЕКС должно совпадать с количеством строк в аргументе просматриваемый_массив в ПОИСКПОЗ, иначе формула выдаст неверный результат.
Вы спросите: «А почему бы нам просто не использовать обычную формулу ВПР? Какой смысл тратить время на то, чтобы разобраться в хитросплетениях ИНДЕКС ПОИСКПОЗ в Excel?»
Вот как это будет выглядеть:
=ВПР(F1; A2:C10; 3; 0)
Конечно, так проще. Но этот наш элементарный пример предназначен только для демонстрационных целей, чтобы вы поняли, как именно функции ИНДЕКС и ПОИСКПОЗ работают вместе. Действительно, ВПР была бы здесь более уместна. Другие примеры, которые вы найдёте ниже, покажут вам реальную силу этой комбинации, которая легко справляется со многими сложными задачами, когда ВПР будет бессильна.
ИНДЕКС+ПОИСКПОЗ вместо ВПР?
Решая, какую функцию использовать для вертикального поиска, большинство знатоков Excel сходятся во мнении, что ПОИСКПОЗ+ИНДЕКС намного лучше, чем ВПР. Однако многие до сих пор остаются с ВПР, во-первых, потому что это проще, а, во-вторых, потому что они не до конца понимают все преимущества использования формулы ПОИСКПОЗ ИНДЕКС в Excel. Без такого понимания никто не захочет тратить свое время на изучение более сложного синтаксиса.
Ниже я укажу на ключевые преимущества ИНДЕКС ПОИСКПОЗ перед ВПР, а уж вам решать, является ли это достойным дополнением к вашему арсеналу знаний в Excel.
4 основные причины использовать ИНДЕКС ПОИСКПОЗ вместо ВПР
- Поиск справа налево. Как известно любому образованному пользователю, ВПР не может искать влево. Это означает, что искомое значение всегда должно находиться в крайнем левом столбце таблицы. А извлекать нужное значение мы будем из столбца, который находится правее. ИНДЕКС+ПОИСКПОЗ может легко выполнять поиск влево! Здесь это показано в действии: Как выполнить поиск значения слева в Excel .
- Можно безопасно вставлять или удалять столбцы. Формулы ВПР не работают или выдают неверные результаты, когда новый столбец удаляется из таблицы поиска или добавляется в нее, поскольку синтаксис ВПР требует указания порядкового номера столбца, из которого вы хотите извлечь данные. Естественно, когда вы добавляете или удаляете столбцы, этот номер в формуле автоматически не меняется, а нужный столбец уже оказывается на новом месте.
С функциями ИНДЕКС и ПОИСКПОЗ вы указываете диапазон возвращаемых столбцов, а не номер одного из них. В результате вы можете вставлять и удалять столько столбцов, сколько хотите, не беспокоясь об обновлении каждой связанной с ними формулы.
- Нет ограничений на размер искомого значения. При использовании функции ВПР общая длина ваших критериев поиска не может превышать 255 символов, иначе вы получите ошибку #ЗНАЧ!. Таким образом, если ваш набор данных содержит длинные строки, ИНДЕКС ПОИСКПОЗ — единственное работающее решение.
- Более высокая скорость обработки. Если ваши таблицы относительно небольшие, вряд ли будет какая-то существенная разница в производительности Excel. Но если ваши рабочие листы содержат сотни или тысячи строк и, следовательно, сотни или тысячи формул, ИНДЕКС ПОИСКПОЗ будет работать намного быстрее, чем ВПР. Причина в том, что Excel будет обрабатывать только столбцы поиска и возврата, а не весь массив таблицы.
Влияние ВПР на производительность Excel может быть особенно заметным, если ваша книга содержит сложные формулы массива. Чем больше значений содержит ваш массив и чем больше формул массива содержится в книге, тем медленнее работает Excel.
ИНДЕКС ПОИСКПОЗ в Excel – примеры формул
Уяснив, почему все же стоит изучать ИНДЕКС ПОИСКПОЗ, давайте перейдем к самому интересному и посмотрим, как можно применить теоретические знания на практике.
Формула для поиска справа налево
Как уже упоминалось, ВПР не может получать значения слева от столбца поиска. Таким образом, если ваши значения поиска не находятся в самом левом столбце, нет никаких шансов, что формула ВПР принесет вам желаемый результат. Функция ПОИСКПОЗ ИНДЕКС в Excel более универсальна и не имеет особого значения, где расположены столбцы поиска и возврата.
Для этого примера мы добавим столбец «Ранг» слева от нашей основной таблицы и попытаемся выяснить, какое место занимает столица России по численности населения среди других перечисленных столиц.
Записав искомое значение в G1, используйте следующую формулу для поиска в C2:C10 и возврата соответствующего значения из A2:A10:
=ИНДЕКС(A2:A10; ПОИСКПОЗ(G1;C2:C10;0))
Совет. Если вы планируете использовать формулу ПОИСКПОЗ ИНДЕКС более чем для одной ячейки, обязательно зафиксируйте оба диапазона абсолютными ссылками (например, $A$2:$A$10 и $C$2:$C$10), чтобы они не изменялись при копировании формулы.
Двусторонний поиск в строках и столбцах
В приведенных выше примерах мы использовали ИНДЕКС ПОИСКПОЗ вместо классической функции ВПР, чтобы вернуть значение из точно указанного столбца. Но что, если вам нужно искать в нескольких строках и столбцах? То есть, сначала нужно найти подходящий столбец, а уж потом извлечь из него значение? Другими словами, что, если вы хотите выполнить так называемый матричный или двусторонний поиск?
Это может показаться сложным, но формула очень похожа на базовую функцию ПОИСКПОЗ ИНДЕКС в Excel, но с одним отличием.
Просто используйте две функции ПОИСКПОЗ, вложенных друг в друга: одну – для получения номера строки, а другую – для получения номера столбца.
ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))
А теперь, пожалуйста, взгляните на приведенную ниже таблицу и давайте составим формулу двумерного поиска, чтобы найти население (в миллионах) в данной стране за данный год.
С целевой страной в G1 (значение_поиска1) и целевым годом в G2 (значение_поиска2) формула принимает следующий вид:
=ИНДЕКС(B2:D11; ПОИСКПОЗ(G1;A2:A11;0); ПОИСКПОЗ(G2;B1:D1;0))
Как работает эта формула?
Всякий раз, когда вам нужно понять сложную формулу Excel, разделите ее на более мелкие части и посмотрите, что делает каждая отдельная функция:
ПОИСКПОЗ(G1;A2:A11;0); – ищет в A2:A11 значение из ячейки G1 («США») и возвращает его позицию, которая равна 3.
ПОИСКПОЗ(G2;B1:D1;0) – просматривает диапазон B1:D1, чтобы получить позицию значения из ячейки G2 («2015»), которая равна 3.
Найденные выше номера строк и столбцов становятся соответствующими аргументами функции ИНДЕКС:
ИНДЕКС(B2:D11, 3, 3)
В результате вы получите значение на пересечении 3-й строки и 3-го столбца в диапазоне B2:D11, то есть из D4. Несложно?
ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям
Если у вас была возможность прочитать наши материалы по ВПР в Excel, вы, вероятно, уже протестировали формулу для ВПР с несколькими условиями . Однако существенным недостатком этого подхода является необходимость добавления вспомогательного столбца. Хорошей новостью является то, что функция ПОИСКПОЗ ИНДЕКС в Excel также может выполнять поиск по нескольким условиям без изменения или реструктуризации исходных данных!
Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}
Примечание. Это формула массива , которую необходимо вводить с помощью сочетания клавиш Ctrl + Shift + Enter.
Предположим, что в таблице ниже вы хотите найти значение на основе двух критериев: Покупатель и Товар.
Следующая формула ИНДЕКС ПОИСКПОЗ отлично работает:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; (F1=A2:A10) * (F2=B2:B10); 0))
Где C2:C10 — это диапазон, из которого возвращается значение, F1 — это критерий1, A2:A10 — это диапазон для сравнения с критерием 1, F2 — это критерий 2, а B2:B10 — это диапазон для сравнения с критерием 2.
Не забудьте правильно ввести формулу, нажав Ctrl + Shift + Enter, и Excel автоматически заключит ее в фигурные скобки, как показано на скриншоте ниже:
Рис5
Если вы не хотите использовать формулы массива, добавьте в формулу в F4 еще одну функцию ИНДЕКС и завершите ее ввод обычным нажатием Enter:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; ИНДЕКС((F1=A2:A10) * (F2=B2:B10); 0; 1); 0))
Разберем пошагово, как это работает.
Здесь используется тот же подход, что и в обычном сочетании ИНДЕКС ПОИСКПОЗ, где просматривается один столбец. Чтобы оценить несколько критериев, вы создаете два или более массива значений ИСТИНА и ЛОЖЬ, которые представляют совпадения и несовпадения для каждого отдельного критерия, а затем перемножаете соответствующие элементы этих массивов. Операция умножения преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно и создает массив, в котором единицы соответствуют строкам, которые удовлетворяют всем условиям. Функция ПОИСКПОЗ со значением поиска 1 находит первую «1» в массиве и передает ее позицию в ИНДЕКС, которая возвращает значение в этой позиции из указанного столбца.
Вторая формула без массива основана на способности функции ИНДЕКС работать с массивами. Второй вложенный ИНДЕКС имеет 0 в номер_строки , так что он будет передавать весь массив столбцов в ПОИСКПОЗ.
Среднее, максимальное и минимальное значение при помощи ИНДЕКС ПОИСКПОЗ
Microsoft Excel имеет специальные функции для поиска минимального, максимального и среднего значения в диапазоне. Но что, если вам нужно получить значение из другой ячейки, связанной с этими значениями? Например, получить название города с максимальным населением или узнать товар с минимальными продажами? В этом случае используйте функцию МАКС , МИН или СРЗНАЧ вместе с ИНДЕКС ПОИСКПОЗ.
Максимальное значение.
Предположим, нам нужно в списке городов найти столицу с самым большим населением. Чтобы найти наибольшее значение в столбце С и вернуть соответствующее ему значение из столбца В, находящееся в той же строке, используйте эту формулу:
=ИНДЕКС(B2:B10; ПОИСКПОЗ(МАКС(C2:C10); C2:C10; 0))
Скриншот с примером находится чуть ниже.
Минимальное значение
Теперь найдём город с самым маленьким населением в списке. Чтобы найти наименьшее число в столбце С и получить соответствующее ему значение из столбца В:
=ИНДЕКС(B2:B10; ПОИСКПОЗ(МИН(C2:C10); C2:C10; 0))
Ближайшее к среднему
Теперь мы находим город, население которого наиболее близко к среднему значению. Чтобы вычислить позицию, наиболее близкую к среднему значению показателя, рассчитанному из D2:D10, и получить соответствующее значение из столбца C, используйте следующую формулу:
=ИНДЕКС(B2:B10; ПОИСКПОЗ(СРЗНАЧ(C2:C10); C2:C10; -1 ))
В зависимости от того, как организованы ваши данные, укажите 1 или -1 для третьего аргумента (тип_совпадения) функции ПОИСКПОЗ:
- Если ваш столбец поиска (столбец D в нашем случае) отсортирован по возрастанию , поставьте 1. Формула вычислит наибольшее значение, которое меньше или равно среднему значению.
- Если ваш столбец поиска отсортирован по убыванию , введите -1. Формула вычислит наименьшее значение, которое больше или равно среднему значению.
- Если ваш массив поиска содержит значение , точно равное среднему, вы можете ввести 0 для точного совпадения. Никакой сортировки не требуется.
В нашем примере данные в столбце D отсортированы в порядке убывания, поэтому мы используем -1 для типа соответствия. В результате мы получаем «Токио», так как его население (13 189 000) является ближайшим, превышающим среднее значение (12 269 006).
Что делать с ошибками поиска?
Как вы, наверное, заметили, если формула ИНДЕКС ПОИСКПОЗ в Excel не может найти искомое значение, она выдает ошибку #Н/Д. Если вы хотите заменить это стандартное сообщение чем-то более информативным, оберните формулу ПОИСКПОЗ ИНДЕКС в функцию ЕСНД . Например:
=ЕСНД(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Не найдено»)
И теперь, если кто-то вводит значение, которое не существует в диапазоне поиска, формула явно сообщит пользователю, что совпадений не найдено:
Если вы хотите перехватывать все ошибки, а не только #Н/Д, используйте функцию ЕСЛИОШИБКА вместо ЕСНД:
=ЕСЛИОШИБКА(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Что-то пошло не так!»)
Пожалуйста, имейте в виду, что во многих ситуациях было бы не совсем правильно скрывать все такие ошибки, потому что они предупреждают вас о возможных проблемах в вашей формуле.
Итак, еще раз об основных преимуществах формулы ИНДЕКС ПОИСКПОЗ.
-
Возможен ли «левый» поиск?
-
Повлияет ли на результат вставка и удаление столбцов?
Вы можете вставлять и удалять столько столбцов, сколько хотите. На результат ИНДЕКС ПОИСКПОЗ это не повлияет.
-
Возможен ли поиск по строкам и столбцам?
Можно сначала найти подходящий столбец, а уж потом извлечь из него значение. Общий вид формулы:
ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))
Подробную инструкцию смотрите здесь. -
Как сделать поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям?
Можно выполнять поиск по двум или более условиям без добавления дополнительных столбцов. Вот формула массива, которая решит проблему:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}
Вот как можно использовать ИНДЕКС и ПОИСКПОЗ в Excel. Я надеюсь, что наши примеры формул окажутся полезными для вас.
Вот еще несколько статей по этой теме: