Индекс доходности в excel: Индекс доходности (рентабельности) инвестиций. Формула. Пример расчета в Excel

Содержание

Внутренняя норма рентабельности, IRR — Альт-Инвест

Открыть эту статью в PDF

Внутренняя норма рентабельности (Internal Rate of Return, IRR) — один из наиболее популярных показателей в финансовом анализе. Его применяют в оценке инвестиционных проектов и других сферах. IRR используют, например, в анализе облигаций, где показатель доходности к погашению вычисляется как внутренняя норма рентабельности денежных потоков у покупателя облигации.

 

Определение показателя IRR

IRR денежного потока — ставка дисконтирования, при которой чистая приведенная стоимость этого денежного потока равна нулю. Таким образом, IRR находят как решение относительно переменной d следующего уравнения:

Экономический смысл IRR — максимальное значение стоимости капитала, при котором инвестиционный проект окупается. При оценке инвестиций IRR сравнивают с требуемой нормой доходности инвестированного капитала, и, если IRR выше требуемой доходности, то проект считают привлекательным.

Для простого варианта, когда в начале проекта — значительные инвестиции, а затем проект приносит доход:


Зависимость NPV от ставки дисконтирования обычно выглядит так:


Поскольку ставка дисконтирования сильнее всего влияет на будущие доходы, а начальные инвестиции либо совсем не зависят от нее, либо зависят очень слабо, то, чем выше ставка, тем меньше значение NPV, и в какой-то точке NPV достигает нуля — это и будет внутренняя норма рентабельности.

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

 

Расчет IRR в Excel

Для расчета IRR в Excel есть две функции: ВСД() и ЧИСТВНДОХ(). Работа этих функций построена на подборе значения IRR методом касательных. Подробнее об этом в видео

Функция ВСД рассчитана на денежный поток, построенный с шагом в год. Если в финансовой модели у денежного потока другой шаг, особенно если платежи распределяются неравномерно, и шаг меняется от периода к периоду, используют вторую функцию — ЧИСТВНДОХ. У нее есть еще один параметр — даты каждого платежа в денежном потоке, что позволяет оценить IRR для любого шага планирования.

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

 

Проблема 1: необычные инвестиции

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

Допустим, денежный поток выглядит таким образом:


График зависимости NPV от ставки дисконтирования для этого потока будет выглядеть так:

Здесь есть два значения в районе 5% и 30%, когда выполняется условие NPV=0, следовательно, это правильные решения для IRR. Функции вычисления IRR в Excel имеют дополнительный параметр — начальное предположение, от которого ведется подбор IRR. В данном случае, в зависимости от начального положения, Excel вычислит IRR как 5% или как 30%. Очевидно, что экономическая интерпретация этого расчета будет сильно затруднена, поэтому для подобных денежных потоков в процессе принятия решений IRR лучше не использовать вообще.

 

Проблема 2: реинвестирование

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

Но если ставка дисконтирования проекта 15%, а IRR определен на уровне 25%, то надо принимать во внимание, что при расчете IRR было сделано предположение, что и любые полученные от проекта деньги могли бы дальше приносить инвестору доход 25%. Это не соответствует действительности. В результате выбор инвестиционных идей на основе IRR несколько искажен в сторону одобрения более коротких проектов с высокой оборачиваемостью, которые с точки зрения суммарного дохода могут быть не лучшим решением.

Альтернатива IRR — выбор проектов на основе анализа их NPV, который свободен от всех недостатков IRR. Кроме того, некоторое распространение получил показатель модифицированной внутренней нормы рентабельности (MIRR), в котором предусмотрено использование отдельной ставки для ожидаемых доходов от реинвестиций.

 

История создания показателя IRR

Подходы, связанные с использованием дисконтированных денежных потоков, разрабатывались разными авторами, начиная с конца 19 века. В частности, ряд важных публикаций на эту тему подготовили Ойген фон Бём-Баверк и Ирвинг Фишер. В книге «Теория процента» Фишер даже привел уравнение для расчета IRR, но не упоминал современного названия этого термина и применял его только в варианте сравнения двух инвестиционных возможностей.

Впервые термин «внутренняя норма рентабельности» и точное описание его формулы ввел Кеннет Боулдинг в статье «Теория единичной инвестиции» (Boulding, K. E. «The Theory of a Single Investment». The Quarterly Journal of Economics 49, вып. 3 (1 мая 1935 г.): 475–94).

В конце того же года показатель упомянул Джон Кейнс в «Общей теории занятости, процента и денег», он называл его «маржинальная эффективность капитала». Поскольку книга сыграла важную роль в развитии экономической науки, то часто именно ее упоминают как источник показателя IRR.

В следующие 20 лет показатель IRR постоянно использовали в оценке инвестиций, но термин не являлся стандартом, и в ряде книг вместо него применяли «доходность» (yield), которая сейчас сохранилась в оценке облигаций (известный термин «доходность к погашению», yield to maturity, YTM — это IRR, рассчитанный для облигации).

Примерно с середины 1960-х годов термин IRR окончательно вытеснил другие формулировки, и сложилось современное понимание этого показателя.

 

 

 

 

 

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

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

зачем нужен этот показатель и как он считается

Любой индексный фонд должен четко следовать за своим индексом. Чем выше точность слежения, тем лучше фонд справляется со своей главной задачей. Владелец качественных индексных инструментов может быть уверен, что на любом горизонте инвестирования в своем портфеле он получит точное отражение рыночных (индексных) бенчмарков. Оценить качество работы фонда как раз и позволяет «ошибка слежения», или Tracking Error. Рассказываем, что обозначает этот показатель и как он рассчитывается.

Зачем нужно смотреть на ошибку слежения

Ошибка слежения (tracking error)— это приведенное к годовым значение отклонений дневных доходностей ETF от индекса-бенчмарка. По сути, ошибка слежения отражает, насколько точно фонд ежедневно отслеживает индекс и как быстро и эффективно происходит перебалансировка портфеля ETF при изменении состава индекса. Другими словами, ошибка слежения — это мера того, насколько надпись на упаковке (название ETF) соответствует ее содержанию (наполнению).

Чем меньше ошибка слежения, тем более полно ETF отображает динамику индекса, а значит, и доходность фонда будет максимально приближена к индексу. Важно помнить, что ETF — это инструмент доступа к рынку акций, облигаций или товаров, и он не должен обыгрывать индекс или демонстрировать лучшие, чем у индекса, характеристики «риск — доходность». Но он должен обеспечивать доход, близкий к бенчмарку. 

Высокая ошибка слежения фактически указывает на риск недополучения инвестором дохода из-за низкого качества управления фондом. 

Как рассчитывается ошибка слежения

На странице каждого фонда FinEx ETF публикуется информация по ошибке слежения. Давайте посмотрим, как же рассчитывается этот показатель, если вы хотите повторить вычисления самостоятельно или просто желаете увидеть все изнутри. 

1. Выгружаем дневную, то есть за каждый торговый день, динамику стоимости чистых активов фонда на 1 акцию (СЧА) и цены отслеживаемого индекса за рассматриваемый период (всё в одной валюте — в базовой валюте фонда).

Для самостоятельного расчета ошибки слежения необходимо иметь данные по динамике стоимости чистых активов на одну акцию фонда и динамике индекса за максимально возможный период (с момента создания фонда). СЧА на одну акцию каждого FinEx ETF обновляется ежедневно на продуктовых страницах, ее можно выгрузить в формате Excel за любой период.

С выгрузкой «индексных» данных, к сожалению, всё сложнее. Дело в том, что многие индекс-провайдеры публикуют динамику своих индексов только в Bloomberg или Reuters, доступа к которым у большинства розничных инвесторов нет. У FinEx ETF такой доступ есть, но размещать эти данные мы, к сожалению, не можем — это нарушение авторских прав. 

Примечание: данные приведены в качестве примера и не относятся к конкретному фонду

2. Считаем дневные доходности за каждый торговый день по СЧА фонда и по отслеживаемому индексу.

Следующий шаг — расчет дневных доходностей фонда и индекса по выгруженным данным. Для этого используется стандартная формула прироста: 

доходность за день = (значение СЧА на сегодняшний день) / (значение СЧА на вчерашний день) — 1

Примечание: данные приведены в качестве примера и не относятся к конкретному фонду

3. Считаем разницу дневной доходности фонда и индекса.

Разницу дневной доходности СЧА фонда и цен индекса (или Daily Return Difference) посчитать просто: нужно из дневной доходности фонда вычесть дневную доходность индекса. У нас должен получиться ряд данных на каждый торговый день.

Примечание: данные приведены в качестве примера и не относятся к конкретному фонду

4. Проверяем получившиеся разницы на наличие «выбросов».

В процессе анализа могут возникнуть отклонения, которые не связаны с качеством отслеживания. К примеру, если СЧА фонда не обновлялся несколько дней (такое может происходить, если торги по фонду не велись из-за локальных праздников), а значения по индексу рассчитывались. Чтобы эти «выбросы» не привели к искусственному завышению ошибки слежения, необходимо удалить лишние строки. 

Примечание: данные приведены в качестве примера и не относятся к конкретному фонду

5. Считаем ошибку слежения с помощью формулы

Чтобы рассчитать финальный показатель, нам достаточно простой статистической функции Excel. Используем функцию поиска стандартного отклонения выборки СТАНДОТКЛОН.В (в нашем случае «выборка» — это ряд данных с разницей дневных доходностей) и приведем получившееся значение к годовым терминам. Для этого его нужно умножить на корень из количества торговых дней — 252. Так мы получим заветный показатель.

Примечание: данные приведены в качестве примера и не относятся к конкретному фонду

Можно ли рассчитать показатель быстрее

В примере выше мы использовали самый точный метод расчета ошибки слежения — на основе дневных данных. Именно таким способом рассчитываются ошибки слежения всех FinEx ETF. Эти данные можно найти на странице каждого ETF.

Однако на практике инвестор может использовать и более грубый способ расчета с помощью годовых данных. 

Предположим, что существует ETF, который отслеживает определенный индекс. Результаты работы фонда и индекса за последние 5 лет можно определить всего лишь по десяти точечным данным СЧА и индекса. При этом методика расчета остается абсолютно идентичной, за исключением последнего шага — полученный показатель не нужно приводить к годовым значениям, так как за основу уже взяты годовые данные.


Примечание: данные приведены в качестве примера и не относятся к конкретному фонду

Посмотреть файл Excel со всеми расчетами и формулами из статьи можно по ссылке.

Понравился текст? Вот короткая инструкция, как получать еще больше полезных материалов: 

  • Чтобы не пропустить новые статьи, подписывайтесь на нашу рассылку. 
  • Если вы только решили стать инвестором, то читайте наш гайд «С чего начать», где вы найдете ответы на все вопросы, и подписывайтесь на лайфхаки для новичков на канале в телеграме. 
  • Смотрите обучающие ролики на нашем YouTube-канале.
  • А чтобы еще лучше разбираться в инвестициях, подписывайтесь на наш аналитический канал.  

?нвестиционный проект | Excel для финансиста

В планировании деятельности компании часто возникает задача оценки эффективности от долгосрочных (более 2 лет) инвестиций. Необходимо ответить на ряд вопросов: окупятся ли инвестиции вообще, если да — то насколько быстро, какова эффективность инвестиционного проекта по сравнению с другими управленческими решениями.

Показатели инвестиционного проекта

Для ответа на вышеприведённые вопросы используют следующие показатели эффективности инвестиционного проекта:

  • срок окупаемости проекта (обычно в месяцах)
  • чистая приведённая стоимость (net present value, NPV)
  • внутренняя норма доходности (IRR).

Срок окупаемости проекта — промежуток времени, который показывает, как долго будут возмещаться вложения в проект с учетом оплаты всех сопутствующих операционных затрат. Чем меньше этот срок, тем выше привлекательность проекта для инвестора.

 

Недостаток этого показателя – игнорирование факта изменения стоимости денег во времени (дисконтирования). Дисконтирование — это приведение будущих денежных потоков к текущему периоду с учетом изменения стоимости денег с течением времени.Номер периода

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

  • стоимостью привлекаемого капитала инвестора;
  • прогнозной инфляцией;
  • премией за риск проекта.

Коэффициент дисконтирования используется для расчёта показателя Чистая приведённая стоимость (net present value -NPV), который по сути является совокупным дисконтированным денежным потоком. Проект считается экономически выгодным, если его NPV не отрицательна. Нулевое значение NPV говорит о том, что проект принесет прибыль, достаточную для выплаты процентов по привлечённому капиталу с учётом инфляции. Чем выше NPV проекта, тем он привлекательнее (при учете рисков).

Для того чтобы получить более универсальную оценку привлекательности инвестиционного проекта, можно рассчитать третий показатель: внутреннюю норму доходности (IRR) – значение ставки дисконтирования, при которой NPV равен нулю (то есть проект отобъёт вложенные в него средства). Считается, что проект приемлем, если расчётное значение IRR больше ставки дисконтирования. Кроме того, этот показатель удобно использовать при сравнении альтернативных инвестиционных проектов: для каждого рассчитывается показатель IRR и предпочтение отдаётся проекту с наибольшим IRR.

Пример расчёта инвестиционного проекта в Excel

Скачайте файл с примером pokazateli-investproekta, ознакомьтесь с заданием. Первый шаг инвестиционного планирования – составление прогноза денежных потоков.

Прогнозирование денежного потока в Excel

Заполните таблицу «Денежные потоки»:

  • в ячейку В9 введите значение первоначальных инвестиций,
  • в ячейку В10 — формулу «=B8-B9»
  • в ячейку С8 введите сумму поступлений в первый год,
  • в D8 – формулу «=C8*1,3»,
  • в С9 — «=C8*0,8»,
  • протяните формулу из ячейки D8 вправо до 2019 года, рассчитайте итоговое значение;
  • протяните вправо формулы из ячеек С9 и В10,
  • протяните формулу из ячейки G8 на две ячейки вниз.
  • В ячейку В11 формулу «=B10», в ячейку С11 формулу =B11+C10, протяните ячейку С11 вправо до F11, сверьте значение в ячейке F11 cо значением в G10.

Теперь рассчитаны денежные потоки, в том числе нарастающим итогом.

Срок окупаемости в Excel: пример расчёта

Для расчёта срока окупаемости в примере Excel введите в ячейку В17 формулу «=СЧЁТЕСЛ?(B11:G11;»<1″)+1». Смысл этой формулы:  подсчёт всех отрицательных значений и прибавление единицы даст номер года, в котором совокупный денежный поток станет неотрицательным.

Дисконтирование в Excel денежного потока: продолжение примера

На листе «инвестиционный проект» заполните строчку Дисконтированный денежный поток: в ячейку В13 введите «=B10/СТЕПЕНЬ(1+$B$5;B12)», протяните вправо. Обратите внимание на нумерацию периодов инвестирования: текущий год имеет номер 0, так как для него денежный поток не дисконтируется. Второй год (2016) имеет номер 1, денежный поток дисконтируется с коэффициентом 0,82 (=1/(1+22%)), с каждым следующим годом коэффициент дисконтирования уменьшается. Рассчитайте суммарный дисконтированный денежный поток в ячейке G13. Как видим, значение NPV существенно отличается от значения совокупного недисконтированного денежного потока (ячейка F11) за счёт достаточно большой ставки дисконтирования. Поскольку значение положительное, проект может быть признан привлекательным для инвестирования.

Расчёт чистой приведённой стоимости (NPV) в Excel: продолжение примера

В Excel есть встроенная функция для расчёта NPVЧПС(ставка.дисконтирования; диапазон_денежных_потоков). Этой функцией пользоваться удобнее (не нужно рассчитывать дисконтированный поток), но у неё есть особенность: первый год проекта считается как будущий (с номером 1) и его денежный поток уже дисконтируется. Если проект планируется, начиная с текущего года, необходимо до применения функции ЧПС скорректировать значения денежных потоков каждого года, умножив их на коэффициент (1+ставка дисконтирования), покажем на примере.

На листе «инвестиционный проект» заполните строчку «Скорректированный денежный поток»: в ячейку В15 введите формулу «=B10*(1+$B$5)» и протяните вправо. Теперь в ячейку В18 введите формулу «=ЧПС(B5;B15:F15)», сравните с рассчитанным «вручную» значением в ячейке G13.

Расчёт внутренней нормы доходности (IRR) в Excel: окончание примера

Сделаем расчёт IRR в Excel двумя способами: вручную и автоматически.

На листе «инвестиционный проект», вручную изменяя ставку дисконтирования, примерно подберите такое значение ставки, чтобы значение в ячейке G13 было близко к нулю.

В Excel есть функция для расчёта IRR — ВСД, которая работает с той же особенностью дисконтирования с первого года. В ячейке В19 введите функцию «=ВСД(B15:F15)», сравните с подобранным вручную.

Таким образом, рассмотрен пример расчёта инвестиционного проекта в Excel, включающий составление таблицы дисконтированного денежного потока  с расчётом показателей инвестиционного проекта.

Скачать файл с примером инвестиционного проекта Excel можно по ссылке: pokazateli-investproekta.

Стоит отметить, что финансовая модель инвестиционного проекта в Excel должна кроме расчёта финансовых потоков и показателей содержать анализ чувствительности проекта: модель должна давать ответ на вопросы, как изменятся показатели в зависимости от изменения входных данных (курсы валют, спрос на рынке, себестоимость сырья и т.п.). Это тема для отдельной статьи.

Новосибирский государственный архитектурно-строительный университет — Сибстрин

НГАСУ (Сибстрин) приглашает на День открытых дверей!

26 марта (суббота) Новосибирский государственный архитектурно-строительный университет (Сибстрин) открывает свои двери для будущих абитуриентов и их родителей! Приходите и познакомьтесь с ведущим в городе и регионе вузом в области строительства, архитектуры и ЖКХ! День открытых дверей – это прекрасная возможность узнать о преимуществах НГАСУ (Сибстрин) и самых перспективных направлениях подготовки, образовательном процессе и насыщенной студенческой жизни, вступительных испытаниях и особенностях приема в 2022 году. Мероприятие пройдет в очном формате, с 13.00 до 16.00 часов по адресу: ул. Ленинградская, 113. Гостей ждет разноплановая и интересная программа. На Дне открытых дверей вы сможете получить представление о будущей профессии и консультации по вопросам поступления, встретиться с сотрудниками приемной комиссии, профессорско-преподавательским составом и студентами, проникнуться уникальной атмосферой, царящей в университете.

Час поэтического настроения: студенты НГАСУ (Сибстрин) приняли участие в литературной встрече, посвященной Всемирному дню поэзии

21 марта 2022 года на базе библиотеки НГАСУ (Сибстрин) состоялась встреча, посвященная Всемирному дню поэзии. Ее участниками стали первокурсники института архитектуры и градостроительства. Всемирный день поэзии, отмечаемый 21 марта, был учрежден ЮНЕСКО. Его цель – поддержать языковое разнообразие посредством поэтического самовыражения и дать возможность языкам, находящимся под угрозой исчезновения, быть услышанными. 21 марта 1999 года первый день поэзии в России прошел в Москве в Государственном литературном музее. В 2000 году празднование Дня поэзии состоялось в Театре на Таганке. «Встреча прошла в формате литературной гостиной. Такой формат подразумевает близкое и душевное общение, – сообщила организатор и ведущая мероприятия, заведующая отделом библиотеки Лариса Балутина. – Я рассказала ребятам о поэзии и представленных на нашей выставке, подготовленной специально к Всемирному дню поэзии, сборниках стихов. Это сборники классиков: Пушкина, Лермонтова, Некрасова, Цветаевой, Ахматовой, а также более близких к нашим дням поэтов: Беллы Ахмадулиной, Владимира Высоцкого, Андрея Дементьева, Ильи Резника, Ларисы Рубальской. Немного остановилась на творчестве Риммы Казаковой и Юлии Друниной, чья поэзия занимает особое место в моей душе. Познакомила студентов с ее стихами на военную тему, так как она прошла фронт девятнадцатилетней девочкой».

В мае пройдет Региональная научная студенческая конференция «Интеллектуальный потенциал Сибири» (РНСК-2022)

23-27 мая 2022 года в Новосибирске пройдет Региональная научная студенческая конференция «Интеллектуальный потенциал Сибири» (РНСК-2022). Цель проведения конференции: Познакомить студентов с актуальными проблемами и задачами современной науки и техники, привлечь молодежь к их решению; Представить результаты научной, творческой и инновационной деятельности студентов; Организовать интеллектуальное общение вузовской молодежи и взаимообмен информацией в сфере профессиональных интересов и в гуманитарной области; Поощрить активность студенчества в научно-исследовательской работе, техническом и художественном творчестве; содействовать повышению авторитета интеллектуальной деятельности. В конференции могут принимать участие с докладами студенты Сибирского федерального округа, а также учащиеся средних образовательных, проявившие наибольшую активность в исследовательской и творческой работе и получившие результаты, интересные в научном и практическом отношениях. Аспиранты могут участвовать только в качестве содокладчиков.

Определены победители и призеры Девятого научного конкурса обучающихся «Развитие инженерной мысли: от прошлого в будущее»

19 марта 2022 года Новосибирским государственным архитектурно-строительным университетом (Сибстрин) при поддержке вузов, представленных в Координационном совете АСВ по работе с одаренными школьниками и студентами был организован и проведен Девятый научный конкурс обучающихся «Развитие инженерной мысли: от прошлого в будущее». Конкурс является одновременно отборочным этапом Всероссийского конкурса исследовательских проектов, выполненных школьниками и студентами при научном консультировании ученых Международной ассоциации строительных вузов. В этом году на конкурс было заявлено 72 научно-практические работы и приняло участие свыше 100 конкурсантов. Учащимися школ и студентами СПО г. Новосибирска и Новосибирской области, г. Новокузнецка и г. Уфы была предложена разнообразная тематика: от исследований в области экологии до защиты собственных экономических, инженерных и архитектурных проектов.

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

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

Функция ИНДЕКС | ПОИСКПОЗ | ИНДЕКС и ПОИСКПОЗ | двусторонний поиск | Левый поиск | С учетом регистра | Ближайшее совпадение | Несколько критериев | Еще примеры

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

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

 


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

Видео: Как искать информацию с помощью ИНДЕКС

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

.
 


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

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

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

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

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

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

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

.
 


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

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

 


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

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

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

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

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

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

.
 
 =ИНДЕКС(C3:E11,5,2) // возвращает $5194 

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

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

.
 

Сделав еще один шаг вперед, мы будем использовать значение из h3 в MATCH:

 


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

Подводя итог:

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

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

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

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

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

.
 
 =ПОИСКПОЗ("Мар",C2:E2,0) // возвращает 3 

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


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

 

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

.
 

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

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

Видео: Как выполнить двусторонний поиск с помощью ИНДЕКС и ПОИСКПОЗ

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

Левый поиск

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

.

Подробное объяснение читайте здесь.

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

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

Подробное объяснение читайте здесь.

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

Ближайшее совпадение

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

Подробное объяснение читайте здесь.

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

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

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

.

Подробное объяснение читайте здесь.

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

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

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

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

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

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

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

.
 
 =ИНДЕКС(B5:E13,5,3) // диаметр Юпитера 

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

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

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

 
 =ИНДЕКС(A1:A5,3) // возвращает значение в формате A3 

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

 
 =ИНДЕКС(A1:B5,2,2) // возвращает значение в B2
=ИНДЕКС(A1:B5,3,1) // возвращает значение в формате A3 

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

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

 

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

.

INDEX и MATCH с горизонтальным столом

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

 

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

.

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

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

 
 =ИНДЕКС(диапазон,0,n) // весь столбец
=ИНДЕКС(диапазон,n,0) // вся строка 

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

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

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

.
 
 =ИНДЕКС(A1:A5,2) // возвращает A2 

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

Две формы

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

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

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

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

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

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

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

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

.
 
 =ИНДЕКС((A1:C5,A7:C10),1,3,2) 

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

  • Несколько диапазонов в , ссылка , разделены запятыми и заключены в круглые скобки.
  • Все диапазоны должны быть на одном листе, иначе ИНДЕКС вернет ошибку #ЗНАЧ.Используйте функцию ВЫБОР в качестве обходного пути.

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

Функция ПОИСКПОЗ используется для определения позиции значения в диапазоне или массиве. Например, на снимке экрана выше формула в ячейке E6 настроена на получение позиции значения в ячейке D6. Функция ПОИСКПОЗ возвращает 5, поскольку искомое значение («персик») находится на 5-й позиции в диапазоне B6:B14:

.
 
 =ПОИСКПОЗ(D6,B6:B14,0) // возвращает 5 

Функция ПОИСКПОЗ может выполнять точные и приблизительные совпадения и поддерживает подстановочные знаки (* ?) для частичных совпадений.Существует 3 отдельных режима сопоставления (устанавливаемых аргументом match_type ), как описано ниже.

Примечание: функция ПОИСКПОЗ всегда возвращает первое совпадение. Если вам нужно вернуть последнее совпадение (обратный поиск), см. функцию XMATCH. Если вы хотите вернуть все совпадения, см. функцию ФИЛЬТР.

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

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

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

Информация о типе соответствия

Тип соответствия не является обязательным. Если не указано, тип соответствия по умолчанию равен 1 (точное или следующее наименьшее). Когда тип соответствия равен 1 или -1, его иногда называют «приблизительным соответствием». Однако имейте в виду, что ПОИСКПОЗ найдет точное соответствие для всех типов соответствия, как указано в таблице ниже:

.
Тип спички Поведение Детали
1 Приблизительно ПОИСКПОЗ находит наибольшее значение , меньшее или равное искомому значению .Массив поиска должен быть отсортирован в порядке возрастания .
0 Точный ПОИСКПОЗ находит первое значение равным искомому значению. Массив поиска не нужно сортировать.
-1 Приблизительно ПОИСКПОЗ находит наименьшее значение , большее или равное искомому значению . Массив поиска должен быть отсортирован в порядке убывания .
Приблизительно Если тип соответствия опущен, по умолчанию используется значение 1 с поведением, описанным выше.

Внимание! Не забудьте установить для типа соответствия ноль (0), если вам нужно точное совпадение. Значение по умолчанию, равное 1, может привести к тому, что ПОИСКПОЗ будет возвращать результаты, которые «выглядят нормальными», но на самом деле неверны. Явное указание значения для match_type — хорошее напоминание о ожидаемом поведении.

Точное совпадение

Если для типа соответствия задано нулевое значение, ПОИСКПОЗ выполняет точное совпадение. В приведенном ниже примере формула в E3:

 
 =ПОИСКПОЗ(E2,B3:B11,0) // возвращает 4 

В приведенной выше формуле искомое значение берется из ячейки E2.Если искомое значение жестко запрограммировано в формуле, оно должно быть заключено в двойные кавычки («») , так как это текстовое значение:

.
 

Примечание. ПОИСКПОЗ не чувствителен к регистру, поэтому и «Марс», и «марс» вернут 4.

Примерное совпадение

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

 
 =ПОИСКПОЗ(E2,B3:B11,1) // возвращает 5 

Совпадение с подстановочным знаком

Если для типа соответствия задано значение ноль (0), ПОИСКПОЗ может выполнять сопоставление с использованием подстановочных знаков.В примере, показанном ниже, формула в E3:

 
 =ПОИСКПОЗ(E2,B3:B11,0) // возвращает 6 

Это эквивалентно:

 

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

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

 

В этом примере ПОИСКПОЗ настроен на выполнение точного совпадения.Функция ПОИСКПОЗ находит код ABX-075 и возвращает его позицию (7) непосредственно в функцию ИНДЕКС в качестве номера строки. Затем функция ИНДЕКС возвращает седьмое значение из диапазона C5:C12 в качестве окончательного результата. Формула решается так:

 

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

Совпадение с учетом регистра

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

.
 

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

Примечания

  • ПОИСКПОЗ — это , а не с учетом регистра.
  • ПОИСКПОЗ возвращает ошибку #Н/Д, если совпадение не найдено.
  • ПОИСКПОЗ работает только с текстом длиной до 255 символов.
  • В случае дубликатов функция ПОИСКПОЗ возвращает первое совпадение.
  • Если match_type равен -1 или 1, lookup_array должен быть отсортирован, как указано выше.
  • Если match_type равно 0, lookup_value может содержать подстановочные знаки.
  • Функция ПОИСКПОЗ часто используется вместе с функцией ИНДЕКС.

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

Спичка | Индекс | Индекс и соответствие | Двусторонний поиск | Поиск с учетом регистра | Левый поиск | Двухколоночный поиск | Ближайшее совпадение | Xlookup

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

Матч

Функция ПОИСКПОЗ возвращает позицию значения в заданном диапазоне.Например, функция ПОИСКПОЗ ниже ищет значение 53 в диапазоне B3:B9.

Объяснение: 53 (первый аргумент) найдено в позиции 5 в диапазоне B3:B9 (второй аргумент). В этом примере мы используем функцию ПОИСКПОЗ, чтобы вернуть точное совпадение, поэтому мы устанавливаем третий аргумент равным 0,

.

Индекс

Приведенная ниже функция ИНДЕКС возвращает конкретное значение в одномерном диапазоне.

Объяснение: функция ИНДЕКС возвращает 5-е значение (второй аргумент) в диапазоне E3:E9 (первый аргумент).

Индекс

и соответствие

Замените значение 5 в функции ИНДЕКС (см. предыдущий пример) на функцию ПОИСКПОЗ (см. первый пример), чтобы найти зарплату ID 53.

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

Двусторонний поиск

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

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

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

Примечание: формула правильно определяет зарплату Мии Рид, а не Мии Кларк.

Левый поиск

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

Примечание: когда мы перетаскиваем эту формулу вниз, абсолютные ссылки ($E$4:$E$7 и $G$4:$G$7) остаются прежними, а относительная ссылка (A2) меняется на A3, A4, A5 и т. д.

Двухстолбцовый поиск

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

Примечание: приведенная выше формула массива ищет зарплату Джеймса Кларка, а не Джеймса Смита и не Джеймса Андерсона.

Ближайшее совпадение

Чтобы найти ближайшее соответствие целевому значению в столбце данных, используйте ИНДЕКС, ПОИСКПОЗ, АБС и МИН в Excel.

Xlookup

Если у вас Excel 365 или Excel 2021, используйте КСПР вместо ИНДЕКС и ПОИСКПОЗ. Функция XLOOKUP проще в использовании и имеет некоторые дополнительные преимущества.

Excel INDEX MATCH vs. VLOOKUP — примеры формул

В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР.

В нескольких недавних статьях мы постарались объяснить основы функции ВПР новичкам и предоставить более сложные примеры формул ВПР опытным пользователям.А теперь я постараюсь если не отговорить вас от использования ВПР, то хотя бы показать вам альтернативный способ вертикального поиска в Excel.

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

Функции ИНДЕКС и ПОИСКПОЗ в Excel — основы

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

Функция ИНДЕКС — синтаксис и использование

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

.

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

Вот очень простое объяснение каждого параметра:

  • массив — диапазон ячеек, из которых вы хотите вернуть значение.
  • row_num — номер строки в массиве, из которого вы хотите вернуть значение. Если он опущен, необходимо указать номер_столбца.
  • номер_столбца — номер столбца в массиве, из которого вы хотите получить значение. Если опущено, row_num требуется.

Для получения дополнительной информации см. функцию ИНДЕКС в Excel.

А вот пример формулы ИНДЕКС в самом простом виде:

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

Формула выполняет поиск в ячейках с A1 по C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, т.е.е. ячейка С2.

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

Функция ПОИСКПОЗ — синтаксис и использование

Функция ПОИСКПОЗ Excel ищет искомое значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Синтаксис функции ПОИСКПОЗ следующий:

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

  • lookup_value — числовое или текстовое значение, которое вы ищете.
  • lookup_array — диапазон искомых ячеек.
  • match_type — указывает, следует ли возвращать точное совпадение или ближайшее совпадение:
    • 1 или опущен — находит наибольшее значение, которое меньше или равно искомому значению. Требуется сортировка массива поиска в порядке возрастания.
    • 0 — находит первое значение, точно равное искомому значению. В комбинации ИНДЕКС/ПОИСКПОЗ вам почти всегда нужно точное совпадение, поэтому вы устанавливаете третий аргумент вашей функции ПОИСКПОЗ в 0.
    • -1 — находит наименьшее значение, которое больше или равно искомому_значению. Требуется сортировка массива поиска в порядке убывания.

Например, если диапазон B1:B3 содержит значения «Нью-Йорк», «Париж», «Лондон», приведенная ниже формула возвращает число 3, поскольку «Лондон» является третьим элементом в диапазоне:

=ПОИСКПОЗ("Лондон",B1:B3,0)

Для получения дополнительной информации см. функцию ПОИСКПОЗ в Excel.

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

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

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

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

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

ИНДЕКС (столбец для возврата значения из , ПОИСКПОЗ ( поисковое значение , столбец для поиска по , 0))

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

.

Чтобы найти население определенной столицы, скажем, столицы Японии, используйте следующую формулу ПОИСКПОЗ ИНДЕКС:

=ИНДЕКС(C2:C10, ПОИСКПОЗ("Япония", A2:A10, 0))

Теперь давайте проанализируем, что на самом деле делает каждый компонент этой формулы:

  • Функция ПОИСКПОЗ ищет искомое значение «Япония» в диапазоне A2:A10 и возвращает число 3, поскольку «Япония» занимает третье место в массиве поиска.
  • Номер строки передается непосредственно в аргумент row_num функции INDEX, предписывая вернуть значение из этой строки.

Таким образом, приведенная выше формула превращается в простой ИНДЕКС (C2:C,3), в котором говорится о поиске в ячейках от C2 до C10 и извлечении значения из ячейки 3 rd в этом диапазоне, т.е. C4, потому что мы начинаем считать с второй ряд.

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

=ИНДЕКС(C2:C10, ПОИСКПОЗ(F1,A2:A10,0))

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

Подождите, подождите… почему бы нам просто не использовать следующую формулу Vlookup? Какой смысл тратить время на то, чтобы разобраться в хитросплетениях ИНДЕКС ПОИСКПОЗ Excel?

=ВПР(F1, A2:C10, 3, ЛОЖЬ)

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

ИНДЕКСНОЕ СОВПАДЕНИЕ против ВПР

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

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

4 основные причины использовать INDEX MATCH вместо VLOOKUP

  1. Поиск справа налево. Как известно любому образованному пользователю, функция ВПР не может смотреть влево, а это означает, что искомое значение всегда должно находиться в крайнем левом столбце таблицы. INDEX MATCH может легко выполнять поиск влево! В следующем примере это показано в действии: Как выполнить визуальный поиск значения слева в Excel.
  2. Безопасная вставка или удаление столбцов. Формулы ВПР не работают или выдают неправильные результаты, когда новый столбец удаляется из таблицы поиска или добавляется в нее, поскольку синтаксис ВПР требует указания порядкового номера столбца, из которого вы хотите извлечь данные.Естественно, когда вы добавляете или удаляете столбцы, номер индекса меняется.

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

  3. Нет ограничений на размер искомого значения. При использовании функции ВПР общая длина критериев поиска не может превышать 255 символов, иначе вы получите ошибку #ЗНАЧ! ошибка.Таким образом, если ваш набор данных содержит длинные строки, INDEX MATCH — единственное работающее решение.
  4. Более высокая скорость обработки. Если ваши таблицы относительно малы, вряд ли будет какая-то существенная разница в производительности Excel. Но если ваши рабочие листы содержат сотни или тысячи строк и, следовательно, сотни или тысячи формул, ИНДЕКС ПОИСКПОЗ будет работать намного быстрее, чем ВПР, потому что Excel придется обрабатывать только столбцы поиска и возврата, а не весь массив таблицы.

    Влияние ВПР на производительность Excel может быть особенно заметным, если ваша книга содержит сложные формулы массива, такие как ВПР и СУММ. Дело в том, что для проверки каждого значения в массиве требуется отдельный вызов функции ВПР. Таким образом, чем больше значений содержит ваш массив и чем больше формул массива содержится в книге, тем медленнее работает Excel.

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

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

Формула INDEX MATCH для поиска справа налево

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

В этом примере мы добавим столбец «Рейтинг» слева от нашей выборочной таблицы и попытаемся выяснить, какое место занимает столица России Москва по численности населения.

Со значением поиска в G1 используйте следующую формулу для поиска в C2:C10 и возврата соответствующего значения из A2:A10:

=ИНДЕКС(A2:A10,ПОИСКПОЗ(G1,C2:C10,0))

Совет. Если вы планируете использовать формулу ПОИСКПОЗ ИНДЕКС для нескольких ячеек, обязательно заблокируйте оба диапазона абсолютными ссылками на ячейки (например, $A$2:$A$10 и $C$2:4C$10), чтобы они не искажались. при копировании формулы.

INDEX MATCH MATCH для поиска в строках и столбцах

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

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

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

ИНДЕКС (массив, ПОИСКПОЗ ( значение vlookup , столбец для поиска по , 0), MATCH ( hlookup значение , строка для поиска по , 0))

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

Если целевая страна указана в G1 (значение визуального поиска), а целевой год — в G2 (значение поиска), формула примет следующий вид:

=ИНДЕКС(B2:D11,ПОИСКПОЗ(G1,A2:A11,0),ПОИСКПОЗ(G2,B1:D1,0))

Как работает эта формула

Всякий раз, когда вам нужно понять сложную формулу Excel, разбейте ее на более мелкие части и посмотрите, что делает каждая отдельная функция:

ПОИСКПОЗ(G1,A2:A11,0) — ищет в A2:A11 значение в ячейке G1 («Китай») и возвращает его позицию, которая равна 2.

ПОИСКПОЗ(G2,B1:D1,0)) — поиск в ячейке B1:D1 для получения позиции значения в ячейке G2 («2015»), которая равна 3.

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

ИНДЕКС (B2:D11, 2, 3)

В результате вы получите значение на пересечении 2-й строки и 3-го столбца в диапазоне B2:D11, что является значением в ячейке D3. Легкий? Ага!

Excel INDEX MATCH для поиска нескольких критериев

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

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

{=ИНДЕКС( return_range , MATCH(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 автоматически заключит ее в фигурные скобки, как показано на скриншоте:

Если вы не хотите использовать формулы массива в своих рабочих листах, добавьте в формулу еще одну функцию ИНДЕКС и завершите ее обычным нажатием Enter:

Как работают эти формулы

В формулах используется тот же подход, что и в базовой функции ПОИСКПОЗ ИНДЕКС, которая просматривает один столбец.Чтобы оценить несколько критериев, вы создаете два или более массива значений ИСТИНА и ЛОЖЬ, которые представляют совпадения и несовпадения для каждого отдельного критерия, а затем перемножаете соответствующие элементы этих массивов. Операция умножения преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно и создает массив, в котором единицы соответствуют строкам, соответствующим всем критериям. Функция ПОИСКПОЗ со значением поиска 1 находит первую «1» в массиве и передает ее позицию в ИНДЕКС, который возвращает значение в этой строке из указанного столбца.

Формула без массива зависит от способности функции ИНДЕКС работать с массивами. Второй ИНДЕКС настроен на 0 row_num , так что он будет передавать весь массив столбцов в ПОИСКПОЗ.

Это высокоуровневое объяснение логики формулы. Подробную информацию см. в разделе СОВПАДЕНИЕ ИНДЕКСА Excel с несколькими критериями.

ИНДЕКС Excel СОВПАДЕНИЕ со СРЕДНИМ, МАКСИМАЛЬНЫМ, МИНИМАЛЬНЫМ

Microsoft Excel имеет специальные функции для поиска минимального, максимального и среднего значения в диапазоне.Но что, если вам нужно получить значение из другой ячейки, связанной с этими значениями? В этом случае используйте функцию MAX, MIN или AVERAGE вместе с INDEX MATCH.

ИНДЕКС
СОВПАДАЕТ с MAX

Чтобы найти наибольшее значение в столбце D и вернуть значение из столбца C в той же строке, используйте следующую формулу:

=ИНДЕКС(C2:C10, ПОИСКПОЗ(МАКС(D2:D10), D2:D10, 0))

ИНДЕКС СОВПАДАЕТ с MIN

Чтобы найти наименьшее значение в столбце D и получить соответствующее значение из столбца C, используйте этот:

=ИНДЕКС(C2:C10, СОВПАДЕНИЕ(МИН(D2:D10), D2:D10, 0))

ИНДЕКС СООТВЕТСТВУЕТ СРЕДНЕМУ

Чтобы вычислить значение, наиболее близкое к среднему в D2:D10, и получить соответствующее значение из столбца C, используйте следующую формулу:

=ИНДЕКС(C2:C10, СОВПАДЕНИЕ(СРЕДНЕЕ(D2:D10), D2:D10, -1))

В зависимости от того, как организованы ваши данные, укажите 1 или -1 для третьего аргумента (match_type) функции ПОИСКПОЗ:

  • Если ваш столбец поиска (столбец D в нашем случае) отсортирован по возрастанию , поставьте 1.Формула вычислит наибольшее значение, которое на меньше или равно среднему значению.
  • Если ваш столбец подстановки отсортирован по убыванию , введите -1. Формула вычислит наименьшее значение, которое на больше или равно среднему значению.
  • Если ваш массив поиска содержит значение , точно равное среднему значению, вы можете ввести 0 для точного совпадения. Никакой сортировки не требуется.

В нашем примере совокупности в столбце D отсортированы в порядке убывания, поэтому мы используем -1 для типа соответствия.В результате мы получаем «Токио», так как его население (13 189 000) является ближайшим совпадением, превышающим среднее значение (12 269 006).

Вам может быть любопытно узнать, что ВПР тоже может выполнять такие вычисления, но в виде формулы массива: ВПР со СРЗНАЧ, МАКС, МИН.

Использование INDEX MATCH с IFNA/IFERROR

Как вы, наверное, заметили, если формула ПОИСКПОЗ ИНДЕКС в Excel не может найти искомое значение, она выдает ошибку #Н/Д. Если вы хотите заменить стандартную запись ошибки чем-то более значимым, оберните формулу ПОИСКПОЗ ИНДЕКС в функцию ЕСЛИНА.Например:

=IFNA(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "Совпадение не найдено")

И теперь, если кто-то вводит таблицу поиска, которая не существует в диапазоне поиска, формула явно сообщит пользователю, что совпадений не найдено:

Если вы хотите перехватывать все ошибки, а не только #N/A, используйте функцию ЕСЛИОШИБКА вместо IFNA:

=ЕСЛИОШИБКА(ИНДЕКС(C2:C10, СОВПАДЕНИЕ(F1,A2:A10,0)), "Ой, что-то пошло не так!")

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

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

Практическая рабочая тетрадь для скачивания

Примеры Excel INDEX MATCH (файл .xlsx)

Вас также может заинтересовать

Что такое INDEX MATCH и зачем его использовать?

Что такое ПОИСКПОЗ ИНДЕКС?

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

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



Загрузите бесплатный файл Excel INDEX MATCH для тренировок!

Используйте этот бесплатный шаблон для практики вместе с учебным пособием INDEX MATCH.


Как работает функция ВПР

Ячейки с F2 по G5 действуют как простой инструмент запроса для извлечения информации из набора данных в ячейках с A2 по D9.ВПР использует номер продукта, введенный вручную в G2, для возврата каждого соответствующего значения, а последний аргумент (ЛОЖЬ) в синтаксисе ВПР гарантирует, что Excel ищет точное совпадение между номером продукта и первым столбцом в исходном наборе данных.

Это замечательно, но в функции ВПР есть определенные ограничения.

Ограничения ВПР

  1. При использовании функции ВПР значение поиска всегда должно находиться в первом столбце массива поиска .Функция ВПР не может вернуть значение, которое находится слева от искомого значения в исходном массиве. Звучит просто, но это правда, и это становится действительно большой проблемой с данными, которые мы не можем изменить или реорганизовать. Например, если приведенный выше запрос предназначен для ввода пользователем типа бумаги, функция ВПР не сможет вернуть номер продукта с текущим макетом набора данных.
  2. Если принимаются приблизительные совпадения (последний аргумент опущен или TRUE), значения поиска в исходных данных должны быть отсортированы в порядке возрастания. Списки, отсортированные в порядке убывания, вернут неправильные результаты. Опять же, у вас может не быть такого большого контроля над данными в вашем массиве поиска.

Если вы регулярно пользуетесь функцией ВПР, вы сталкивались с этими проблемами и, возможно, просто решили с ними жить. Изучение того, как использовать INDEX MATCH, является отличным обходным путем.

Давайте разберем обе функции по отдельности.

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

Функция ПОИСКПОЗ используется для определения номера позиции известного значения в диапазоне ячеек.Функция ПОИСКПОЗ имеет три возможных аргумента со следующим синтаксисом:

.
  =ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения])  
  • Lookup_value — это известное значение, которое вы будете использовать для поиска. Это может быть значение или ссылка на ячейку.
  • Lookup_array — это диапазон ячеек, в которых будет найдено значение поиска.
  • Match_type — это параметр, который сообщает Excel, примете ли вы близкое совпадение, если искомое_значение не найдено в массиве поиска.
    • Тип соответствия 0 соответствует точному совпадению.
    • Тип совпадения 1 принимает почти совпадение, округляя в меньшую сторону на до следующего доступного значения.
    • Тип совпадения -1 принимает близкое к совпадению число , округленное до , до следующего доступного значения.
    • Match_type является необязательным. Если этот параметр опущен, предполагается, что тип соответствия равен 1.

Например, если мы хотим узнать номер позиции слова «матовый» в диапазоне от B2 до B9 ниже.

  =ПОИСКПОЗ("матовый",B2:B9,0)  

Обратите внимание, что в приведенном выше примере значение «матовая» было введено непосредственно в формулу между двойными кавычками. Если искомое значение является ссылкой на ячейку, двойные кавычки не используются.

Обратите внимание, что функция ПОИСКПОЗ не чувствительна к регистру.

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

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

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

.
  =ИНДЕКС(массив,номер_строки,[номер_столбца])  
  • Массив относится к диапазону или массиву, содержащему данные для индексации.Этот аргумент является обязательным.
  • Row_num — это номер строки, в которой должна быть найдена ячейка, содержащая возвращаемое значение. Row_num можно не указывать, если массив состоит только из одной строки. Если row_num опущен, необходимо column_num.
  • Column_num — это номер столбца, в котором должна быть найдена ячейка, содержащая возвращаемое значение. Column_num является необязательным, но требуется, если row_num опущен.

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

  =ИНДЕКС(A2:D9,3,2)  

Третий элемент в столбце 2 идентифицируется как значение «Matte» и возвращается в качестве выходных данных в ячейке F2.

Объединение функций ИНДЕКС и ПОИСКПОЗ

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

Мы можем применить это к нашему простому запросу, который извлекает значения из набора данных, используя номер продукта в качестве входных данных или значения поиска.

Мы будем использовать ПОИСКПОЗ, чтобы определить, где в списке находится номер продукта (номер позиции), и мы будем использовать ИНДЕКС, чтобы вернуть значения из соответствующих столбцов (Тип бумаги, Цена или В наличии) по их номерам столбцов.

Показано отдельно, каждая функция будет выглядеть следующим образом:

  =ПОИСКПОЗ(G2, A2:A9,0)  

Выполняет поиск значения в ячейке G2 в диапазоне от A2 до A9 и требует точного совпадения.Он возвращает номер позиции этого значения.

  =ИНДЕКС(A2:D9, row_num, 2)  

Это возвращает значение из массива от A2 до D9, которое пересекает номер строки, найденный в формуле ПОИСКПОЗ, и номер столбца 2 (Тип бумаги). Формула ПОИСКПОЗ будет использоваться в качестве аргумента row_num .

  =ИНДЕКС(A2:D9,ПОИСКПОЗ(G2,A2:A9,0),2)  

Формулы в G4 и G5 идентичны, за исключением последнего аргумента, который равен 3 и 4 соответственно, для представления каждого номера столбца.

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

Если мы изменим известное значение таким образом, что тип бумаги станет нашим искомое_значение , ВПР вернет ошибку #NA (значение недоступно). Давайте посмотрим, как с этим справляется INDEX MATCH.

  =ИНДЕКС(A2:D9,ПОИСКПОЗ(G2,B2:B9,0),1)  

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

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

Давайте рассмотрим более продвинутое применение решения INDEX MATCH.

ИНДЕКС ПОИСКПОЗ расширенный пример

Экзаменуемые ввели свои идентификаторы учащегося в свои контрольные работы, которые затем подсчитываются, а оценки вводятся в столбец D. Мы хотим найти идентификатор учащегося в массиве от A2 до C7, но функция ВПР не может этого сделать.Имена учащихся отображаются слева от идентификационных номеров, поскольку список расположен в алфавитном порядке. Это наша первая проблема.

Мы также хотим присвоить буквенную оценку на основе результатов теста каждого учащегося. Таблица результатов теста (от F2 до G6) отсортирована в порядке убывания, а это еще одна вещь, с которой функция ВПР не справляется.

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

  =ПОИСКПОЗ(B11,$C$2:$C$7,0)  

(Будет скопирован диапазон от C2 до C7, поэтому мы можем использовать $, чтобы сделать ссылки фиксированными.Узнайте больше об абсолютных и смешанных ссылках.)

Мы использовали тип соответствия 0, чтобы гарантировать, что будет возвращено только точное совпадение.

Функция ПОИСКПОЗ обнаружила идентификатор учащегося 114125 в позиции 5 массива поиска.

Теперь нам просто нужно связать это с соответствующим именем ученика.

  =ИНДЕКС($A$2:$C$7,ПОИСКПОЗ(B11,$C$2:$C$7,0),1)  

(Будет скопирован диапазон от A2 до C7, поэтому мы можем использовать $, чтобы сделать ссылки фиксированными.Узнайте больше об абсолютных и смешанных ссылках.)

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

Теперь эту формулу можно скопировать в остальные строки столбца C.

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

Тип массива

Формула ПОИСКПОЗ match_type

Точное совпадение

0

Приблизительное совпадение с ближайшим значением меньше искомого значения

1

Приблизительное совпадение с ближайшим значением, превышающим искомое значение

-1

Наши формулы ПОИСКПОЗ и ИНДЕКС будут читаться как

.
  • МАТЧ(D11,$F$2:$F$6,-1)
  • ИНДЕКС($F$2:$G$6,вложенная формула ПОИСКПОЗ,2)

При вложении полная формула будет:

  =ИНДЕКС($F$2:$G$6,ПОИСКПОЗ(D11,$F$2:$F$6,-1),2)  

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

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

Мастер Excel сегодня

Узнайте больше о базовых навыках работы с Excel и повысьте свою производительность с помощью курса GoSkills Microsoft Excel — базовый и продвинутый.

Повысьте уровень своих навыков работы с Excel

Станьте сертифицированным мастером Excel с небольшими курсами GoSkills

Начать бесплатную пробную версию

Комбинация функций ИНДЕКС и ПОИСКПОЗ в Excel (10 простых примеров)

В Excel много функций — около 450+.

И многие из них просто потрясающие. Объем работы, который вы можете выполнить с помощью нескольких формул, до сих пор меня удивляет (даже после того, как я использовал Excel более 10 лет).

И среди всех этих замечательных функций выделяется комбинация функций INDEX MATCH.

Я большой поклонник комбо INDEX MATCH, и я много раз говорил об этом.

Я даже написал статью о сопоставлении индексов и ВПР , которая вызвала небольшие споры (вы можете проверить раздел комментариев, чтобы узнать немного о фейерверке).

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

Примечание: В Excel есть и другие формулы поиска, такие как ВПР и ГПР, и они великолепны. Многие люди считают, что ВПР проще в использовании (и это тоже правда). Я считаю, что INDEX MATCH во многих случаях является лучшим вариантом. Но так как люди находят это трудным, им пользуются меньше. Поэтому я пытаюсь упростить это с помощью этого урока.

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

Функция ИНДЕКС: Находит значение на основе координат

Самый простой способ понять, как работает функция индекса, — представить ее как спутник GPS.

Как только вы сообщите спутнику координаты широты и долготы, он будет точно знать, куда идти и найдет это место.

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

Я быстро поискал свое рабочее место и вот что получил.

Впрочем, хватит географии.

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

Вкратце это и есть функция ИНДЕКС в Excel.

Итак, позвольте мне объяснить вам это простыми словами.

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

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

Но…

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

Ниже приведен синтаксис функции ИНДЕКС:

 =ИНДЕКС (массив, номер_строки, [номер_столбца])
=ИНДЕКС (массив, номер_строки, [номер_столбца], [номер_области]) 
  • массив –   диапазон ячеек или константа массива.
  • row_num – номер строки, из которой должно быть извлечено значение.
  • [col_num] — номер столбца, из которого должно быть извлечено значение. Хотя это необязательный аргумент, но если row_num не указан, его необходимо указать.
  • [номер_области] — (необязательно) Если аргумент массива состоит из нескольких диапазонов, этот номер будет использоваться для выбора ссылки из всех диапазонов.

Функция ИНДЕКС имеет 2 синтаксиса (только для справки).

В большинстве случаев используется первый. Второй используется только в сложных случаях (например, при трехстороннем поиске), которые мы рассмотрим в одном из примеров позже в этом руководстве.

Но если вы новичок в этой функции, просто запомните первый синтаксис.

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

Говоря простым языком, функция ПОИСКПОЗ Excel может найти положение ячейки в диапазоне.

А по какому принципу он будет определять положение ячейки?

На основе значения поиска.

Например, если у вас есть список, как показано ниже, и вы хотите найти в нем положение имени «Марка», вы можете использовать функцию ПОИСКПОЗ.

Функция возвращает 3, так как это позиция ячейки с именем Метка в ней.

Функция ПОИСКПОЗ начинает искать сверху вниз искомое значение (то есть «Отметка») в указанном диапазоне (в данном примере это A1:A9). Как только он находит имя, он возвращает позицию в этом конкретном диапазоне.

Ниже приведен синтаксис функции ПОИСКПОЗ в Excel.

 =ПОИСКПОЗ(искомое_значение, искомое_массив, [тип_сопоставления]) 
  • искомое_значение – Значение, для которого вы ищете совпадение в искомом_массиве.
  • искомый_массив –  Диапазон ячеек, в которых вы ищете искомое_значение.
  • [тип_сопоставления] —  (необязательно) Указывает, как Excel должен искать совпадающее значение. Он может принимать три значения -1, 0 или 1.

Понимание аргумента типа соответствия в функции ПОИСКПОЗ

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

Третий аргумент функции ПОИСКПОЗ может принимать значения 0, 1 или -1.

Ниже объясняется, как работают эти аргументы:

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

Ниже приведено видео, в котором объясняется, как использовать функцию ПОИСКПОЗ (вместе с аргументом типа соответствия)

Подводя итог и выражаясь простыми словами:

  • ИНДЕКСу требуется позиция ячейки (строка и столбец число) и дает значение ячейки.
  • ПОИСКПОЗ находит позицию, используя значение поиска.

Давайте объединим их, чтобы создать мощную систему (ИНДЕКС + ПОИСКПОЗ)

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

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

Я начну с простого примера, а затем покажу вам несколько расширенных вариантов использования.

Щелкните здесь, чтобы загрузить файл примера

Пример 1. Простой поиск с использованием INDEX MATCH Combo

Давайте выполним простой поиск с помощью INDEX/MATCH.

Ниже приведена таблица, где у меня есть оценки для десяти учеников.

Из этой таблицы я хочу найти оценки для Джима.

Ниже приведена формула, с помощью которой это легко сделать:

 =ИНДЕКС($A$2:$B$11,MATCH("Джим",$A$2:$A$11,0),2) 

Теперь, если вы думаете, что это можно легко сделать с помощью функции ВПР, вы правы! Это не лучшее использование удивительности INDEX MATCH.Несмотря на то, что я фанат INDEX MATCH, это немного сложнее, чем VLOOKUP. Если все, что вам нужно сделать, — это получить данные из столбца справа, я рекомендую вам использовать функцию ВПР.

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

Теперь позвольте мне показать преимущество INDEX MATCH.

Предположим, у вас есть те же данные, но они представлены не в столбцах, а в строках (как показано ниже).

Знаешь что, ты все еще можешь использовать комбо INDEX MATCH, чтобы получить оценки Джима.

Ниже приведена формула, которая даст вам результат:

=ИНДЕКС($B$1:$K$2,2,MATCH(«Джим»,$B$1:$K$1,0))

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

Это невозможно сделать с помощью ВПР, но вы можете легко сделать это с помощью ГПР. Комбинация

INDEX MATCH может легко обрабатывать как горизонтальные, так и вертикальные данные.

Щелкните здесь, чтобы загрузить файл примера

Пример 2. Взгляд вверх влево

Это более распространено, чем вы думаете.

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

Примерно так, как показано ниже:

Чтобы узнать продажи Майкла, вам нужно выполнить поиск слева.

Если вы думаете о ВПР, позвольте мне остановить вас прямо сейчас.

Функция ВПР не предназначена для поиска и получения значений слева.

Можно ли сделать это с помощью функции ВПР?

Да, можно!

Но это может превратиться в длинную и уродливую формулу.

Итак, если вы хотите выполнить поиск и получить данные из столбцов слева, вам лучше использовать комбинацию INDEX MATCH.

Ниже приведена формула, по которой можно получить номер продажи Майкла:

 =ИНДЕКС($A$2:$C$11,MATCH("Майкл",C2:C11,0),2) 

Еще одна точка здесь для ИНДЕКС СООТВЕТСТВОВАТЬ.Функция ВПР может извлекать данные только из тех столбцов, которые находятся справа от столбца, имеющего искомое значение.

Пример 3: двусторонний поиск

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

Но в реальной жизни данные часто охватывают несколько столбцов.

ПОИСКПОЗ ПО ИНДЕКСУ легко справляется с двусторонним поиском.

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

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

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

 =ИНДЕКС($B$2:$D$11,ПОИСКПОЗ($F$3,$A$2:$A$11,0),ПОИСКПОЗ(G$2,$B$1:$D$1,0)) 

Позвольте мне также быстро объяснить эту формулу.

В формуле ИНДЕКС в качестве диапазона используется B2:D11.

Первое ПОИСКПОЗ использует имя (Джим в ячейке F3) и извлекает его положение в столбце имен (A2:A11). Это становится номером строки, из которой необходимо извлечь данные.

Вторая формула ПОИСКПОЗ использует имя субъекта (в ячейке G2), чтобы получить позицию этого конкретного имени субъекта в ячейке B1:D1. Например, математика равна 1, физика равна 2, а химия равна 3.

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

Эта формула является динамической, что означает, что если вы измените имя учащегося или имена субъектов, она все равно будет работать и получать правильные данные.

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

Пример 4. Поиск значения из нескольких столбцов/критериев

Предположим, у вас есть набор данных, как показано ниже, и вы хотите получить оценки для «Mark Long».

Поскольку данные находятся в двух столбцах, я не могу найти Марк и получить данные.

Если я сделаю это таким образом, я получу данные оценок для Марка Фроста, а не для Марка Лонга (поскольку функция ПОИСКПОЗ даст мне результат для МЕТКИ, с которой она встречается).

Один из способов сделать это — создать вспомогательный столбец и объединить имена. Когда у вас есть вспомогательный столбец, вы можете использовать функцию ВПР и получить данные об оценках.

Если вам интересно узнать, как это сделать, прочтите это руководство по использованию функции ВПР с несколькими критериями.

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

Приведенная ниже формула даст результат.

 =ИНДЕКС($C$2:$C$11,ПОИСКПОЗ($E$3&"|"&$F$3,$A$2:A11&"|"&$B$2:$B$11,0)) 

Позвольте мне быстро объяснить, что делает эта формула.

Часть формулы MATCH объединяет значение поиска (Mark и Long), а также весь массив поиска. Когда $A$2:A11&”|”&$B$2:$B$11 используется в качестве массива поиска, он фактически сравнивает значение поиска с объединенной строкой имени и фамилии (разделенных символом вертикальной черты).

Это гарантирует получение правильного результата без использования вспомогательных столбцов.

Такой поиск (при наличии нескольких столбцов/критериев) также можно выполнять с помощью функции ВПР, но вам необходимо использовать вспомогательный столбец. Комбинация INDEX MATCH позволяет легко сделать это без каких-либо вспомогательных столбцов.

Пример 5. Получение значений из всей строки/столбца

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

Но вы можете больше.

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

А чем это может быть полезно спросите вы!

Предположим, вы хотите узнать общий балл Джима по всем трем предметам.

Вы можете использовать функцию ИНДЕКС, чтобы сначала получить все оценки Джима, а затем использовать функцию СУММ, чтобы получить общее количество.

Посмотрим, как это сделать.

Ниже приведены баллы всех учеников по трем предметам.

Приведенная ниже формула даст мне общий балл Джима по всем трем предметам.

 =СУММ(ИНДЕКС($B$2:$D$11,MATCH($F$4,$A$2:$A$11,0),0)) 

Позвольте мне объяснить, как работает эта формула.

Хитрость здесь заключается в использовании 0 в качестве номера столбца.

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

Таким образом, приведенная ниже часть формулы возвращает массив значений – {97, 70, 73}

 ИНДЕКС($B$2:$D$11,MATCH($F$4,$A$2:$A$11,0) ,0) 

Если вы просто введете приведенную выше формулу в ячейку Excel и нажмете Enter, вы увидите #ЗНАЧ! ошибка. Это потому, что он возвращает не одно значение, а массив значений.

Но не волнуйтесь, массив значений все еще там. Вы можете проверить это, выбрав формулу и нажав клавишу F9. Он покажет вам результат формулы, которая в данном случае представляет собой массив из трех значений — {97, 70, 73}

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

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

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

Щелкните здесь, чтобы загрузить файл примера

Пример 6. Определение оценки учащегося (метод приблизительного совпадения)

До сих пор мы использовали формулу ПОИСКПОЗ для получения точного совпадения искомого значения.

Но вы также можете использовать его для приблизительного совпадения.

Что, черт возьми, такое приблизительное совпадение?

Поясню.

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

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

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

Таким образом, если учащийся набирает менее 33 баллов, ему присваивается оценка F, а если он/она набирает менее 50, но более 33 баллов, он получает оценку E и так далее.

Ниже приведена формула, которая это сделает.

 =ИНДЕКС($F$3:$F$8,MATCH(B2,$E$3:$E$8,1),1) 

Позвольте мне объяснить, как работает эта формула.

В функции ПОИСКПОЗ мы использовали 1 в качестве аргумента [тип_сопоставления].Этот аргумент вернет наибольшее значение, которое меньше или равно искомому значению.

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

Таким образом, если значение метки поиска равно 20, функция ПОИСКПОЗ вернет 1, а если 85, она вернет 5.

И функция ИНДЕКС использует это значение позиции для получения оценки.

ВАЖНО: Чтобы это работало, ваши данные должны быть отсортированы в порядке возрастания. Если это не так, вы можете получить неправильные результаты.

Обратите внимание, что вышеприведенное также можно выполнить с помощью следующей формулы ВПР:

 =ВПР(B2,$E$3:$F$8,2,ИСТИНА) 

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

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

Для этого все, что мне нужно сделать, это изменить аргумент [match_type] на -1.

Ниже приведена формула, которую я использовал:

 =ИНДЕКС($F$3:$F$8,MATCH(B2,$E$3:$E$8,-1),1) 

ВПР также может выполнять приблизительное совпадают, но только тогда, когда данные отсортированы в порядке возрастания (но это не работает, если данные отсортированы в порядке убывания).

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

До сих пор все поиски, которые мы выполняли, были нечувствительны к регистру.

Это означает, что не имело значения, было ли искомое значение Джимом, ДЖИМом или Джимом. Вы получите тот же результат.

Но что, если вы хотите, чтобы поиск был чувствительным к регистру.

Обычно это имеет место, когда у вас есть большие наборы данных и возможность повторения или разных имен/идентификаторов (с той лишь разницей, что это случай)

Например, предположим, что у меня есть следующий набор данных студентов, где есть студенты с именем Джим (с той лишь разницей, что один вводится как Джим, а другой как Джим).

Обратите внимание, что есть два студента с одинаковым именем – Джим (ячейки A2 и A5).

Поскольку обычный поиск не работает, вам необходимо выполнить поиск с учетом регистра.

Ниже приведена формула, которая даст правильный результат. Поскольку это формула массива, вам нужно использовать Control + Shift + Enter.

=ИНДЕКС($B$2:$B$11,ПОИСКПОЗ(ИСТИНА,ТОЧНО(D3,A2:A11),0),1)

Поясню, как работает эта формула.

Функция EXACT проверяет точное совпадение искомого значения (в данном случае это «jim»).Он перебирает все имена и возвращает FALSE, если совпадения нет, и TRUE, если совпадение есть.

Таким образом, выход функции EXACT в этом примере – {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Обратите внимание, что существует только одно значение TRUE, когда ТОЧНОЕ функция нашла идеальное совпадение.

Функция ПОИСКПОЗ затем находит позицию ИСТИНА в массиве, возвращаемом функцией ТОЧНО, который в этом примере равен 4.

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

Пример 8. Поиск ближайшего совпадения

Теперь немного углубимся.

Предположим, у вас есть набор данных, в котором вы хотите найти человека с опытом работы, наиболее близким к требуемому опыту (упомянутому в ячейке D2).

Хотя формулы поиска не предназначены для этого, вы можете комбинировать их с другими функциями (такими как MIN и ABS), чтобы сделать это.

Ниже приведена формула, которая найдет человека с опытом, наиболее близким к требуемому, и вернет имя человека.Обратите внимание, что опыт должен быть самым близким (который может быть меньше или больше).

 =ИНДЕКС($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0)) 

Так как это массив формулы, вам нужно использовать Control + Shift + Enter.

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

Прежде чем я объясню формулу, давайте разберемся, как бы вы сделали это вручную.

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

Это именно то, что мы делаем с этой формулой.

Поясню.

Искомое значение в формуле ПОИСКПОЗ: МИН(АБС(D2-B2:B15)).

Эта часть дает вам минимальную разницу между данным опытом (который равен 2.5 лет) и все остальные опыты. В этом примере он возвращает 0,3

Обратите внимание, что я использовал ABS, чтобы убедиться, что я ищу ближайший (который может быть больше или меньше, чем заданный опыт).

Теперь это минимальное значение становится нашим поисковым значением.

Массив поиска в функции ПОИСКПОЗ: ABS(D2-$B$2:$B$15).

Это дает нам массив чисел, из которого вычтено 2,5 (необходимый опыт).

Итак, теперь у нас есть искомое значение (0.3) и поисковый массив ({6,8;0,8;19,5;21,8;14,5;11,2;0,3;9,2;2;9,8;14,8;0,4;23,8;2,9})

Функция ПОИСКПОЗ находит позицию 0,3 в этом массиве, что также является позицией имени человека, который имеет самый близкий опыт.

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

  Прочтите по теме  : Поиск ближайшего совпадения в Excel (примеры с использованием формул поиска) 

Щелкните здесь, чтобы загрузить файл примера

значение, когда есть частичное совпадение, вам нужно использовать подстановочные знаки.

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

Поскольку это не точные совпадения, в этом случае вы не можете выполнять обычный поиск.

Но вы все равно можете получить правильные данные, используя звездочку (*), которая является подстановочным знаком.

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

=ИНДЕКС($B$2:$B$10,MATCH(D2&”*”,$A$2:$A$10,0),1)

Позвольте мне объяснить, как работает эта формула.

Поскольку нет точного совпадения искомых значений, я использовал D2&”*” в качестве искомого значения в функции ПОИСКПОЗ.

Звездочка — это подстановочный знак, представляющий любое количество символов. Это означает, что Apple* в формуле будет означать любую текстовую строку, которая начинается со слова Apple и может иметь любое количество символов после него.

Таким образом, когда Apple* используется в качестве значения поиска и формула ПОИСКПОЗ ищет его в столбце A, она возвращает позицию «Apple Inc.», поскольку она начинается со слова Apple.

Вы также можете использовать подстановочные знаки для поиска текстовых строк, между которыми находится искомое значение. Например, если вы используете *Apple* в качестве значения поиска, будет найдена любая строка, в которой есть слово apple.

Примечание. Этот метод хорошо работает, когда у вас есть только один экземпляр сопоставления.Но если у вас есть несколько совпадений (например, Apple Inc и Apple Corporation), то функция ПОИСКПОЗ вернет позицию только первого совпадения.

Пример 10: Трехсторонний поиск

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

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

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

До сих пор во всех наших примерах мы использовали только первый.

Но для трехстороннего поиска необходимо использовать второй синтаксис.

Позвольте мне сначала объяснить, что означает трехсторонний взгляд.

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

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

Ниже приведена формула, которая даст результат.

 =ИНДЕКС(($B$3:$D$7,$B$11:$D$15,$B$19:$D$23),ПОИСКПОЗ($F$5,$A$3:$A$7,0),ПОИСКПОЗ( G$4,$B$2:$D$2,0),(IF(G$3="Unit Test",1,IF(G$3="Mid Term",2,3)))) 

Вышеупомянутое Формула проверяется по трем вещам — имени студента, предмету и экзамену. После того, как он находит правильное значение, он возвращает его в ячейку.

Позвольте мне объяснить, как работает эта формула, разбив формулу на части.

  • массив – ($B$3:$D$7,$B$11:$D$15,$B$19:$D$23) : вместо использования одного массива в этом случае я использовал три массива внутри скобка.
  • row_num – ПОИСКПОЗ($F$5,$A$3:$A$7,0) : Функция ПОИСКПОЗ используется для нахождения позиции имени учащегося в ячейке $F$5 в списке имен учащегося.
  • col_num – ПОИСКПОЗ(G$4,$B$2:$D$2,0) : Функция ПОИСКПОЗ используется для нахождения позиции имени субъекта в ячейке $B$2 в списке имен субъектов.
  • [номер_области] — IF(G$3=»Unit Test»,1,IF(G$3=»Mid Term»,2,3)) : Значение номера области сообщает функции ИНДЕКС, какой из трех массивов следует использовать для получения значения. Если экзамен представляет собой модульный термин, функция ЕСЛИ вернет 1, а функция ИНДЕКС будет использовать первый массив для получения значения. Если экзамен является промежуточным, формула ЕСЛИ вернет 2, иначе она вернет 3.

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

Щелкните здесь, чтобы загрузить файл примера

Почему ИНДЕКС/ПОИСКПОЗ лучше ВПР?

ИЛИ Это?

Да, в большинстве случаев.

Я представлю свое дело через некоторое время.

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

Итак, если вы хотите выполнить простой поиск, вам лучше использовать функцию ВПР.

ИНДЕКС/ПОИСКПОЗ — это ВПР на стероидах. И как только вы изучите ИНДЕКС/ПОИСКПОЗ, вы всегда можете предпочесть его использовать (особенно из-за его гибкости).

Не преувеличивая, позвольте мне быстро объяснить, почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР.

ИНДЕКС/ПОИСКПОЗ может смотреть слева (а также справа) от искомого значения

Я рассмотрел это в одном из приведенных выше примеров.

Если у вас есть значение, которое находится слева от значения поиска, вы не можете сделать это с помощью ВПР

По крайней мере, только с помощью ВПР.

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

ИНДЕКС/ПОИСКПОЗ, с другой стороны, предназначен для поиска везде (будь то слева, справа, вверх или вниз)

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

Опять же, при полном уважении к ВПР, это не сделано для этого.

В конце концов, V в ВПР означает вертикаль.

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

Конечно, есть функция ГПР для поиска по горизонтали, но тогда это не ВПР… верно?

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

ВПР не может работать с нисходящими данными

Когда дело доходит до приблизительного совпадения, ВПР и ИНДЕКС/ПОИСКПОЗ находятся на одном уровне.

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

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

ИНДЕКС/ПОИСКПОЗ может быть немного быстрее

Буду честен. Сам я этот тест не проводил.

Я полагаюсь на мудрость мастера Excel – Чарли Кида.

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

В своей статье Чарли Кид утверждает:

«В худшем случае метод ИНДЕКС-ПОИСКПОЗ почти так же быстр, как ВПР; в лучшем случае это намного быстрее».

ИНДЕКС/ПОИСКПОЗ не зависит от фактического положения столбца

Если у вас есть набор данных, как показано ниже, когда вы получаете оценку Джима по физике, вы можете сделать это с помощью ВПР.

И для этого вы можете указать номер столбца как 3 в ВПР.

Все в порядке.

А что, если я удалю столбец Math.

В этом случае формула ВПР не работает.

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

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

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

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

Несмотря на все эти факторы, есть причина, по которой функция ВПР так популярна.

И это большая причина.

ВПР проще в использовании

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

А поскольку большинство основных случаев поиска также обрабатываются функцией ВПР, она быстро стала самой популярной функцией Excel.

Я называю это королем функций Excel.

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

И это не игра с нулевой суммой.

Итак, если вы новичок в мире поиска и не знаете, как использовать функцию ВПР, лучше изучите ее.

У меня есть подробное руководство по использованию функции ВПР в Excel (со множеством примеров)

В этой статье я не собираюсь противопоставлять друг другу две замечательные функции. Я хотел показать вам силу комбинации INDEX MATCH и все ее возможности.

Надеюсь, эта статья оказалась для вас полезной.

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

Отставить комментарий

Обязательные для заполнения поля отмечены*