Excel индекс если нет данных

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

Пример работы функции ИНДЕКС в Excel от простого к сложному

Сначала рассмотрим самый простой пример применения. У нас есть несколько городов. Пусть нашей первой задачей будет извлечь пятый по счету город из нашей одномерной базы данных. Синтаксис нашей формулы будет следующий: ИНДЕКС(массив; номер_строки). Начинаем писать формулу (яч. B9). Первый аргумент – это все города B3:C7, а второй – необходимый нам номер ряда, в котором хранится информация, которую мы хотим получить – пятый город:

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

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



Функция ИНДЕКС двумерный и многомерный массив

Теперь наш массив двухмерный. Пусть теперешним заданием будет узнать количество туристов в Ливерпуле. В ячейке B19 пишем формулу. Синтаксис тоже видоизменится: ИНДЕКС(массив; номер_строки; номер_столбца), у нас добавится третий аргумент – номер столбца, в котором хранится нужные нам данные:

Аналогично функция работает с трёхмерными массивами, четырёхмерными и так далее. Теперь давайте рассмотрим, как решать задачи, когда таблиц несколько. Пусть у нас будут города трёх стран с количеством посетителей:

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

И получаем готовую формулу с решением поставленной задачи:

Пример формулы комбинации функций ИНДЕКС и СУММ

Функцию ИНДЕКC так же можно комбинировать и с другими функциями. Например, наша база данных содержит дополнительно количество туристов Великобритании за 2018-2019 года, мы хотим узнать сумму туристов:

1. за 2018 год.
2. по городу Манчестер за весь период.

В этом нам поможет известная любому пользователю несложная функция СУММ. Она охватит результат поиска, сделанным ИНДЕКC и выполнит операцию сложения найденных значений. Чтобы выполнить первую задачу, для начала нам нужно выбрать диапазон с одними числами(без столбца с городами, поскольку будем суммировать численные значения C43:E47). Затем на месте, где мы прописывали номер строки, пишем 0. Благодаря этому ИНДЕКC не будет «искать» данные по строкам, а просто перейдет к следующей операции. А следующая операция – прописываем столбец 2 и получаем ответ на первый пункт:

Сумма туристов за 2018 год по пяти городам Великобритании составляет 40 987 людей.

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

И получаем ответ на второй пункт – всего посетителей за три года в Манчестере было 2 474 человека.

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

Формулировку функции можно видоизменять в зависимости от постановки задачи. Пусть нам нужно определить и показать сколько туристов вместе было в 2017-м году в первых трёх городах по списку – Кембридже, Манчестере и Лондоне. Тогда мы снова используем функцию СУММ, но в месте, где указывается конец диапазона значений, по которым мы проводим поиск, вставляем нашу ИНДЕКС. Проще говоря, диапазон для суммирования выглядит так: (начало:конец). Началом будет первая ячейка массива, как обычно, а конец мы заменим. Укажем количество городов, по которым будем считать людей (действие 1). Затем, когда будем составлять функцию ИНДЕКC, укажем, что первый аргумент – числовой диапазон всех пяти городов за 2017-й год, а второй – наше кол-во городов (ячейка D64):

Точно так же можно выполнить следующее задание: найти сумму посетителей Оксфорде за 2017-2018 года. В этом случае делаем всё точно так же, только выбираем горизонтальный диапазон C61:E61 :

В конце необязательно ссылаться на ячейку с написанным количеством искомых значений, можно сразу писать цифру 3 в первом примере и цифру 2 во втором примере, всё будет работать точно так же:

и

идентичны.

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

Формула ИНДЕКС и ПОИСКПОЗ лучшая замена функции ВПР

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

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

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

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

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

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

Скачать примеры функции ИНДЕКС и ПОИСКПОЗ в Excel

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

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

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

Оператор ИНДЕКС относится к группе функций из категории «Ссылки и массивы». Он имеет две разновидности: для массивов и для ссылок.

Вариант для массивов имеет следующий синтаксис:

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

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

Синтаксис для ссылочного варианта выглядит так:

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

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

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

Способ 1: использование оператора ИНДЕКС для массивов

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

Имеем таблицу зарплат. В первом её столбце отображены фамилии работников, во втором – дата выплаты, а в третьем – величина суммы заработка. Нам нужно вывести имя работника в третьей строке.

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



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



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





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

   В поле «Массив» нужно указать адрес обрабатываемого диапазона данных. Его можно вбить вручную. Но для облегчения задачи мы поступим иначе. Ставим курсор в соответствующее поле, а затем обводим весь диапазон табличных данных на листе. После этого адрес диапазона тут же отобразится в поле.

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

   После того, как все указанные настройки совершены, щелкаем по кнопке «OK».



5. Результат обработки выводится в ячейку, которая была указана в первом пункте данной инструкции. Именно выведенная фамилия является третьей в списке в выделенном диапазоне данных.

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

Результат будет точно такой же, что и выше.

Это был простейший пример, чтобы вы увидели, как работает данная функция, но на практике подобный вариант её использования применяется все-таки редко.

Урок: Мастер функций в Экселе

Способ 2: применение в комплексе с оператором ПОИСКПОЗ

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

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

Синтаксис оператора ПОИСКПОЗ такой:

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

• Искомое значение – это значение, позицию которого в диапазоне мы ищем;
• Просматриваемый массив – это диапазон, в котором находится это значение;
• Тип сопоставления – это необязательный параметр, который определяет, точно или приблизительно искать значения. Мы будем искать точные значения, поэтому данный аргумент не используется.

С помощью этого инструмента можно автоматизировать введение аргументов «Номер строки» и «Номер столбца» в функцию ИНДЕКС.

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

1. Прежде всего, узнаем, какую заработную плату получает работник Парфенов Д. Ф. Вписываем его имя в соответствующее поле.



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

   В поле «Массив» вносим координаты столбца, в котором находятся суммы заработных плат работников.

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

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

   После того, как все значения внесены, жмем на кнопку «OK».



3. Результат количества заработка Парфенова Д. Ф. после обработки выводится в поле «Сумма».



4. Теперь, если в поле «Имя» мы изменим содержимое с «Парфенов Д.Ф.», на, например, «Попова М. Д.», то автоматически изменится и значение заработной платы в поле «Сумма».

Способ 3: обработка нескольких таблиц

Теперь посмотрим, как с помощью оператора ИНДЕКС можно обработать несколько таблиц. Для этих целей будет применяться дополнительный аргумент «Номер области».

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

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



2. Открывается окно аргументов. В поле «Ссылка» нам нужно указать адреса всех трех диапазонов. Для этого устанавливаем курсор в поле и выделяем первый диапазон с зажатой левой кнопкой мыши. Затем ставим точку с запятой. Это очень важно, так как если вы сразу перейдете к выделению следующего массива, то его адрес просто заменит координаты предыдущего. Итак, после введения точки с запятой выделяем следующий диапазон. Затем опять ставим точку с запятой и выделяем последний массив. Все выражение, которое находится в поле «Ссылка» берем в скобки.

   В поле «Номер строки» указываем цифру «2», так как ищем вторую фамилию в списке.

   В поле «Номер столбца» указываем цифру «3», так как колонка с зарплатой является третьей по счету в каждой таблице.

   В поле «Номер области» ставим цифру «3», так как нам нужно найти данные в третьей таблице, в которой содержится информация о заработной плате за третий месяц.

   После того, как все данные введены, щелкаем по кнопке «OK».



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

Способ 4: вычисление суммы

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

При сложении суммы СУММ имеет следующий синтаксис:

=СУММ(адрес_массива)

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

=СУММ(C4:C9)

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

=СУММ(C4:ИНДЕКС(C4:C9;6))

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

Урок: Полезные функции Excel

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

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

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

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

• Базовая информация об ИНДЕКС и ПОИСКПОЗ
• Используем функции ИНДЕКС и ПОИСКПОЗ в Excel
• Преимущества ИНДЕКС и ПОИСКПОЗ перед ВПР
• ИНДЕКС и ПОИСКПОЗ – примеры формул
  • Как находить значения, которые находятся слева
  • Вычисления при помощи ИНДЕКС и ПОИСКПОЗ
  • Поиск по известным строке и столбцу
  • Поиск по нескольким критериям
  • ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

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

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

Каждый аргумент имеет очень простое объяснение:

• array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
• row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
• column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

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

Вот простейший пример функции INDEX (ИНДЕКС):

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

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

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

ПОИСКПОЗ – синтаксис и применение функции

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

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку «London» – это третий элемент в списке.

=MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

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

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

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

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

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

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

=INDEX(столбец из которого извлекаем,(MATCH (искомое значение,столбец в котором ищем,0))
=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое значение;столбец в котором ищем;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

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

• Функция MATCH (ПОИСКПОЗ) ищет значение «Japan» в столбце B, а конкретно – в ячейках B2:B10, и возвращает число 3, поскольку «Japan» в списке на третьем месте.
• Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:

  =INDEX($D$2:$D$10,3)
=ИНДЕКС($D$2:$D$10;3)

  Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4, так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?

=VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

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

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

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

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

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

=VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

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

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

=VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

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

=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

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

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

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

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

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

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

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

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

=INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

• Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение «Russia» в списке:

  =MATCH("Russia",$B$2:$B$10,0))
=ПОИСКПОЗ("Russia";$B$2:$B$10;0))

• Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10.
• Затем соединяем обе части и получаем формулу:

  =INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

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

• Если указываете 1, значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
• Если указываете -1, значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает «Moscow», поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

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

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

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

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

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

И я поздравляю тех из Вас, кто догадался!

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

=INDEX(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))
=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))

Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

Итак, начнём с двух функций ПОИСКПОЗ, которые будут возвращать номера строки и столбца для функции ИНДЕКС:

• ПОИСКПОЗ для столбца – мы ищем в столбце B, а точнее в диапазоне B2:B11, значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

  =MATCH($H$2,$B$1:$B$11,0)
=ПОИСКПОЗ($H$2;$B$1:$B$11;0)

  Результатом этой формулы будет 4, поскольку «USA» – это 4-ый элемент списка в столбце B (включая заголовок).

• ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1, то есть в ячейках A1:E1:

  =MATCH($H$3,$A$1:$E$1,0)
=ПОИСКПОЗ($H$3;$A$1:$E$1;0)

  Результатом этой формулы будет 5, поскольку «2015» находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

=INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:

{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13),0),3)}
{=ИНДЕКС('Lookup table'!$A$2:$C$13;ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13);0);3)}

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

MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)
ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

• Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
• Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
• Далее, мы делаем то же самое для значений столбца B (Product).
• Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1. Если оба критерия ложны, или выполняется только один из них – Вы получите 0.

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

Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС. Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3.

И, наконец, т.к. нам нужно проверить каждую ячейку в массиве, эта формула должна быть формулой массива. Вы можете видеть это по фигурным скобкам, в которые она заключена. Поэтому, когда закончите вводить формулу, не забудьте нажать Ctrl+Shift+Enter.

Если всё сделано верно, Вы получите результат как на рисунке ниже:

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:

=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")

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

Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки («»), как значение второго аргумента функции ЕСЛИОШИБКА. Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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

Функция ИНДЕКС в программе Microsoft Excel

​Смотрите также​ Если да, то​ + Shift +​ использования функции​ значение -1, это​ подходящее решение среди​ нет –​:​, затем находит ближайшее​ВПР+СУММ​ возвращают ошибочные значения,​Функция​Функция​– это не​ Все выражение, которое​«Сумма»​ обводим весь диапазон​

​Одной из самых полезных​ мы получаем значение​

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

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

​ если удалить или​INDEX​

​MATCH​

​ единственная функция поиска​ находится в поле​. Нужно сделать так,​ табличных данных на​ функций программы Эксель​ ИСТИНА. Быстро меняем​ случае с классическими​номер строки или​ значений в B2:​ уроке, смело опишите​(ЛОЖЬ).​– мы ищем​ возвращает значение из​ что проверка каждого​ добавить столбец в​(ИНДЕКС) использует​

​(ПОИСКПОЗ) имеет вот​ в Excel, и​

​«Ссылка»​

​ что при введении​ листе. После этого​ является оператор ИНДЕКС.​ её на ЛОЖЬ​​ формулами массива).​​ столбца задать равным​​ B10 должен быть​​ свою проблему в​​Далее, мы делаем то​​ в столбце​ столбца​ значения в массиве​ таблицу поиска. Для​3​

​ такой синтаксис:​ её многочисленные ограничения​берем в скобки.​ имени работника автоматически​ адрес диапазона тут​ Он производит поиск​ и умножаем на​​Первое решение выглядит следующем​​ нулю (или просто​ в порядке убывания​ комментариях, и мы​ же самое для​B​C​

Способ 1: использование оператора ИНДЕКС для массивов

​ требует отдельного вызова​ функции​для аргумента​​MATCH(lookup_value,lookup_array,[match_type])​​ могут помешать Вам​

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

1. ​, а точнее в​той же строки:​ функции​ВПР​​row_num​​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​ получить желаемый результат​«Номер строки»​

   

2. ​ им денег. Посмотрим,​​ поле.​​ на пересечении указанных​​ столбца (значение ИСТИНА​​Рассмотрим ближе каким образом​​ функция будет выдавать​​ Но значения в​​ решить её.​​B​ диапазоне​=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))​ВПР​​любой вставленный или​​(номер_строки), который указывает​lookup_value​

   

3. ​ во многих ситуациях.​указываем цифру​ как это можно​В поле​​ строки и столбца,​​ заменяем на ЛОЖЬ,​​ эта формула работает:​​ уже не значение,​​ порядке возрастания, и,​​Урок подготовлен для Вас​(Product).​B2:B11​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))​. Поэтому, чем больше​​ удалённый столбец изменит​​ из какой строки​

   

4. ​(искомое_значение) – это​​ С другой стороны,​​«2»​ воплотить на практике,​«Номер строки»​ возвращая результат в​ потому что нас​

   ​($A$2:$D$9=F2)​​ а ссылку на​​ которая приводит к​ командой сайта office-guru.ru​Затем перемножаем полученные результаты​, значение, которое указано​Результат: Moscow​ значений содержит массив​ результат формулы, поскольку​ нужно возвратить значение.​ число или текст,​ функции​, так как ищем​ применив функции​ставим цифру​ заранее обозначенную ячейку.​

   ​ не интересует ошибка,​​Данный фрагмент формулы необходимо​​ диапазон-столбец или диапазон-строку​​ ошибке # н/д.​​Источник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/​ (1 и 0).​ в ячейке​Используя функцию​ и чем больше​​ синтаксис​​ Т.е. получается простая​​ который Вы ищите.​​ИНДЕКС​ вторую фамилию в​ИНДЕКС​«3»​

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

   

5. ​Перевел: Антон Андронов​ Только если совпадения​H2​СРЗНАЧ​ формул массива содержит​ВПР​ формула:​ Аргумент может быть​

​и​​ списке.​​и​, так как по​ этой функции раскрываются​ Также ищем варианты​ ячейки таблицы с​Обратите внимание, что поскольку​Измените аргумента​Автор: Антон Андронов​​ найдены в обоих​​(USA). Функция будет​в комбинации с​ Ваша таблица, тем​требует указывать весь​=INDEX($D$2:$D$10,3)​ значением, в том​ПОИСКПОЗ​В поле​​ПОИСКПОЗ​​ условию нам нужно​​ при использовании её​​ (столбцы), в котором​​ искомым словом. Для​​ИНДЕКС​тип_сопоставления​Примечание:​​ столбцах (т.е. оба​​ выглядеть так:​ИНДЕКС​ медленнее работает Excel.​ диапазон и конкретный​=ИНДЕКС($D$2:$D$10;3)​ числе логическим, или​– более гибкие​​«Номер столбца»​​.​

​ определить третье имя​ в сложных формулах​ формула не выдала​

​ этого формула создаёт​выдает в этом​1 или сортировка​Мы стараемся как​ критерия истинны), Вы​=MATCH($H$2,$B$1:$B$11,0)​и​

​С другой стороны, формула​​ номер столбца, из​

Способ 2: применение в комплексе с оператором ПОИСКПОЗ

​Формула говорит примерно следующее:​​ ссылкой на ячейку.​​ и имеют ряд​указываем цифру​​Прежде всего, узнаем, какую​​ в списке. В​​ в комбинации с​​ ошибку. Потому что​​ в памяти компьютера​​ варианте не конкретное​ по убыванию формат​ можно оперативнее обеспечивать​ получите​=ПОИСКПОЗ($H$2;$B$1:$B$11;0)​ПОИСКПОЗ​ с функциями​​ которого нужно извлечь​​ ищи в ячейках​

​lookup_array​​ особенностей, которые делают​​«3»​ заработную плату получает​ поле​ другими операторами. Давайте​

​ это будет означать,​​ массив значений, равный​​ значение ячейки, а​

​ таблицы. Затем повторите​

• ​ вас актуальными справочными​​1​Результатом этой формулы будет​, в качестве третьего​
• ​ПОИСКПОЗ​​ данные.​ от​(просматриваемый_массив) – диапазон​
• ​ их более привлекательными,​​, так как колонка​ работник Парфенов Д.​«Номер столбца»​ рассмотрим различные варианты​ что искомое значение​ размерам нашей таблицы,​ссылку на диапазон​

​ попытку.​ материалами на вашем​. Если оба критерия​​4​​ аргумента функции​​и​​Например, если у Вас​​D2​​ ячеек, в котором​

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

1. ​ потребуется заключить ее​ сообщества, посвященном Excel​ переведена автоматически, поэтому​ только один из​ это 4-ый элемент​чаще всего нужно​

   

2. ​просто совершает поиск​​A1:C10​​D10​match_type​.​ в каждой таблице.​​ поле.​​, так как колонка​

   ​ Excel​​ уверен, что анализ​​ через какое-то время​ в дополнительную функцию,​У вас есть предложения​ ее текст может​

   ​ них – Вы​​ списка в столбце​​ будет указывать​ и возвращает результат,​, и требуется извлечь​и извлеки значение​

   ​(тип_сопоставления) – этот​​Базовая информация об ИНДЕКС​​В поле​Выделяем ячейку в поле​ с именами является​​Оператор​​ формул позволит вам​ мы будем умножать​ например​ по улучшению следующей​ содержать неточности и​ получите​​B​​1​ выполняя аналогичную работу​ данные из столбца​ из третьей строки,​ аргумент сообщает функции​ и ПОИСКПОЗ​«Номер области»​«Сумма»​ первой в выделенном​ИНДЕКС​ быстро понять ход​ эти значения, и​СУММ (SUM)​ версии Excel? Если​ грамматические ошибки. Для​0​

   ​(включая заголовок).​или​ заметно быстрее.​​B​​ то есть из​

   

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

   

4. ​относится к группе​​ мыслей.​​ Excel автоматически преобразует​,​​ да, ознакомьтесь с​​ нас важно, чтобы​​.​​ПОИСКПОЗ для строки​-1​Теперь, когда Вы понимаете​​, то нужно задать​​ ячейки​

Способ 3: обработка нескольких таблиц

​, хотите ли Вы​ ПОИСКПОЗ в Excel​​«3»​​ выводиться итоговый результат.​После того, как все​ функций из категории​Функция INDEX имеет две​​ их на ноль).​​СРЗНАЧ (AVERAGE)​

​ темами на портале​ эта статья была​Теперь понимаете, почему мы​– мы ищем​в случае, если​ причины, из-за которых​ значение​D4​ найти точное или​Преимущества ИНДЕКС и ПОИСКПОЗ​

1. ​, так как нам​ Запускаем окно аргументов​ указанные настройки совершены,​«Ссылки и массивы»​​ формы. Первая, содержит​​ Нули там, где​и т.п.​ пользовательских предложений для​ вам полезна. Просим​ задали​ значение ячейки​ Вы не уверены,​​ стоит изучать функции​​2​

   

2. ​, так как счёт​ приблизительное совпадение:​​ перед ВПР​​ нужно найти данные​ функции​ щелкаем по кнопке​. Он имеет две​ 3 аргумента для​ значение в таблице​Общеизвестно, что стандартная ссылка​ Excel.​ вас уделить пару​1​H3​ что просматриваемый диапазон​ПОИСКПОЗ​для аргумента​ начинается со второй​1​ИНДЕКС и ПОИСКПОЗ –​ в третьей таблице,​ИНДЕКС​«OK»​ разновидности: для массивов​ ввода. Однако существует​ не равно искомому​ на любой диапазон​​Как исправить ошибку #​​ секунд и сообщить,​

   ​, как искомое значение?​​(2015) в строке​​ содержит значение, равное​​и​​col_index_num​ строки.​или​

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

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

   ​В поле​Результат обработки выводится в​Вариант для массивов имеет​​ 4 аргументами, где​​ трудно догадаться, в​

   

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

Способ 4: вычисление суммы

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

​ такое значение есть,​​ самому интересному и​​, вот так:​

​Важно! Количество строк и​

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

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

​ можно указать несколько​ слова, в памяти​​, например​​ несколькими условиями в​ Для удобства также​

​ когда оба критерия​

​A1:E1​ – ставьте​ увидим, как можно​=VLOOKUP(«lookup value»,A1:C10,2)​ столбцов в массиве,​ равное искомому. Просматриваемый​ и ПОИСКПОЗ​​После того, как все​​ в котором находятся​ пункте данной инструкции.​​При этом два последних​​ таблиц. Такой подход​ компьютера в соответствующем​A2:B5​ Excel​

​ приводим ссылку на​​ выполняются.​

​:​​0​​ применить теоретические знания​=ВПР(«lookup value»;A1:C10;2)​ который использует функция​ массив должен быть​Поиск по известным строке​ данные введены, щелкаем​ суммы заработных плат​ Именно выведенная фамилия​ аргумента в формуле​ позволяет одновременную работу​ месте будет цифра​. Хитрость в том,​Функция ИНДЕКС​ оригинал (на английском​Обратите внимание:​=MATCH($H$3,$A$1:$E$1,0)​для поиска точного​

​ на практике.​

lumpics.ru

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

​Если позднее Вы вставите​INDEX​​ упорядочен по возрастанию,​​ и столбцу​​ по кнопке​​ работников.​ является третьей в​ можно использовать, как​ сразу по нескольким​​ 1.​​ что если взять​Функция ПОИСКПОЗ​ языке) .​В этом случае​=ПОИСКПОЗ($H$3;$A$1:$E$1;0)​ совпадения.​​Любой учебник по​​ новый столбец между​

​(ИНДЕКС), должно соответствовать​ то есть от​Поиск по нескольким критериям​«OK»​Поле​​ списке в выделенном​​ вместе, так и​ таблицам.​СТОЛБЕЦ($A$2:$D$9)​ функцию​Общие сведения о формулах​В разделе описаны наиболее​​ необходимо использовать третий​​Результатом этой формулы будет​Если указываете​ВПР​ столбцами​

​ значениям аргументов​ меньшего к большему.​ИНДЕКС и ПОИСКПОЗ в​​.​​«Номер столбца»​ диапазоне данных.​ любой один из​В качестве последнего аргумента​В то же самое​ИНДЕКС​ в Excel​ распространенные причины «Ошибка​ не обязательный аргумент​​5​​1​​твердит, что эта​​A​row_num​0​ сочетании с ЕСЛИОШИБКА​После этого в предварительно​​оставляем пустым, так​​Мы разобрали применение функции​

• ​ них, если массив​ необходимо выбрать номер​
• ​ время параллельно, в​в первом или​
• ​Рекомендации, позволяющие избежать появления​ # н/д» должна​
• ​ функции​, поскольку «2015» находится​
  • ​, значения в столбце​ функция не может​
  • ​и​(номер_строки) и​
  • ​– находит первое​Так как задача этого​
  • ​ выделенную ячейку выводятся​
  • ​ как мы используем​ИНДЕКС​

Базовая информация об ИНДЕКС и ПОИСКПОЗ

​ одномерный. При многомерном​ области, к которой​ памяти компьютера создаётся​​ втором варианте и​​ неработающих формул​​ отображаться, являющееся результатом​​ИНДЕКС​ в 5-ом столбце.​ поиска должны быть​ смотреть влево. Т.е.​B​

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

ИНДЕКС – синтаксис и применение функции

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

​ отображается сумма заработной​
​ диапазон.​

​ (несколько столбцов и​ оба значения. Нужно​

• ​ пресмыкающиеся друг к​​ (тех же размеров,​после двоеточия​ с помощью функции​ .​
• ​ в первом аргументе​​ в функцию​ а формула вернёт​ не является крайним​ придется изменить с​MATCH​ИНДЕКС​ИНДЕКС​​ платы второго по​​А вот в поле​
• ​ строк). Если бы​​ также учесть, что​ другу области должны​ что и размеры​, то наша функция​ проверки ошибок​Примечание:​ мы задаем всю​​ИНДЕКС​​ максимальное значение, меньшее​

​ левым в диапазоне​2​​(ПОИСКПОЗ). Иначе результат​​/​и​ счету работника (Сафронова​«Номер строки»​

​ диапазон был одномерным,​​ под номером строки​​ быть заключены в​

​ нашей исходной таблицы),​
​ будет выдавать уже​

​Все функции Excel (по​ Если необходимо​​ таблицу и должны​​и вуаля:​ или равное среднему.​​ поиска, то нет​​на​​ формулы будет ошибочным.​​ПОИСКПОЗ​ПОИСКПОЗ​​ В. М.) за​​нам как раз​

​ то заполнение данных​ и столбца понимается​ круглые скобки, а​ содержащая номера столбцов​ не значение, а​ алфавиту)​ИНДЕКСУ​​ указать функции, из​​=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))​

ПОИСКПОЗ – синтаксис и применение функции

​Если указываете​​ шансов получить от​​3​Стоп, стоп… почему мы​всегда нужно точное​для реализации вертикального​ третий месяц.​ нужно будет записать​

​ в окне аргументов​​ не номер на​​ таблицы должны быть​ для каждой из​ адрес, и на​Все функции Excel (по​​или​​ какого столбца нужно​=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))​-1​

​ВПР​
​, иначе формула возвратит​

​ не можем просто​​ совпадение, поэтому третий​​ поиска в Excel,​Ссылочная форма не так​

​ функцию​
​ было бы ещё​

• ​ координатах листа, а​​ отделены друг от​ ячеек нашей таблицы.​ выходе мы получим​ категориям)​ПОИСКПОЗ​ извлечь значение. В​Если заменить функции​
• ​, значения в столбце​​желаемый результат.​ результат из только​ использовать функцию​
• ​ аргумент функции​​ мы не будем​ часто применяется, как​​ПОИСКПОЗ​​ проще. В поле​ порядок внутри самого​ друга, так как​
  • ​ Затем одна таблица​​ полноценную ссылку на​​Бывает у вас такое:​​для возврата осмысленные​ нашем случае это​ПОИСКПОЗ​ поиска должны быть​Функции​ что вставленного столбца.​VLOOKUP​
  • ​ПОИСКПОЗ​​ задерживаться на их​ форма массива, но​. Для её записи​​«Массив»​​ указанного массива.​​ и аргументы в​​ умножается на другую.​ диапазон от начальной​ смотришь на человека​​ значения вместо #​​ столбец​​на значения, которые​​ упорядочены по убыванию,​
  • ​ПОИСКПОЗ​​Используя​(ВПР)? Есть ли​должен быть равен​ синтаксисе и применении.​ её можно использовать​ придерживаемся того синтаксиса,​тем же методом,​Синтаксис для ссылочного варианта​

​ Excel, то есть​ Выглядит это более​​ ячейки до той,​​ и думаешь «что​ н/д, используйте функцию​C​ они возвращают, формула​ а возвращено будет​

​и​ПОИСКПОЗ​ смысл тратить время,​0​Приведём здесь необходимый минимум​ не только при​ о котором шла​ что и выше,​​ выглядит так:​​ с использованием точки​​ или менее так,​​ которую нашла​​ за @#$%)(*?» А​​ IFERROR и затем​(Sum), и поэтому​​ станет легкой и​​ минимальное значение, большее​ИНДЕКС​/​ пытаясь разобраться в​.​ для понимания сути,​ работе с несколькими​ речь выше. Сразу​

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

​ мы указываем его​=ИНДЕКС(ссылка;номер_строки;номер_столбца;[номер_области])​ с запятой.​ как показано на​ИНДЕКС​ потом при близком​​ вложить функций​​ мы ввели​​ понятной:​​ или равное среднему.​​в Excel гораздо​​ИНДЕКС​ лабиринтах​-1​ а затем разберём​​ диапазонами, но и​​ в поле вписываем​ адрес. В данном​Тут точно так же​В этой версии функции​

​ рисунке ниже (пример​:​​ знакомстве оказывается, что​​индекс​​3​​=INDEX($A$1:$E$11,4,5))​

​В нашем примере значения​ более гибкие, и​, Вы можете удалять​
​ПОИСКПОЗ​– находит наименьшее​ подробно примеры формул,​

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

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

​.​
​=ИНДЕКС($A$1:$E$11;4;5))​

​ в столбце​ им все-равно, где​ или добавлять столбцы​

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

  ​ примера я сделал​
​В результате мы получаем​

  ​СМЕЩ (OFFSET)​ парашютом, имеет семеро​в эту функцию.​​ нужно проверить каждую​​ на пересечении​​упорядочены по возрастанию,​​ значением, которое нужно​ не искажая результат,​?​ Просматриваемый массив должен​​ИНДЕКС​​ суммы в комбинации​ сразу же открываем​ колонке​

​«Номер строки»​ три таблицы с​

​ массив с нулями​, но она, в​ детей и черный​​ Только заменив собственное​​ ячейку в массиве,​4-ой​​ поэтому мы используем​​ извлечь. Для примера,​​ так как определен​​=VLOOKUP(«Japan»,$B$2:$D$2,3)​​ быть упорядочен по​​и​ с оператором​

​ скобку и указываем​«Имя»​или​​ ценами из разных​​ везде, где значения​ отличие от​ пояс в шахматах,​ значение # н/д​​ эта формула должна​​строки и​​ тип сопоставления​​ снова вернёмся к​

​ непосредственно столбец, содержащий​
​=ВПР("Japan";$B$2:$D$2;3)​

​ убыванию, то есть​ПОИСКПОЗ​СУММ​ координаты искомого значения.​. В поле​«Номер столбца»​​ магазинов. Диапазонам данных​​ в нашей таблице​​ИНДЕКС​​ да и, вообще,​ идентифицирует ошибку, но​ быть формулой массива.​5-го​​1​​ таблице со столицами​​ нужное значение. Действительно,​​В данном случае –​ от большего к​вместо​​.​​ Это координаты той​

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

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

​ этого примера –​На первый взгляд, польза​​.​​СУММ​​ мы отдельно записали​​«3»​вообще является необязательным​ используя поле «Имя».​​ столбца, в котором​​, т.е. пересчитывается каждый​​Так и в Microsoft​​ чем использовать​​ скобкам, в которые​​, то есть значение​

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

​/​​ запишем формулу​ приходится с большими​​ исключительно демонстрационная, чтобы​​ от функции​Функция​имеет следующий синтаксис:​ фамилию работника Парфенова.​, так как нужно​ и он применяется​ Теперь диапазоны называются​ соответствующее значение было​​ раз при изменении​​ Excel: есть несколько​​ЕСЛИОШИБКА​​ она заключена. Поэтому,​ ячейки​ПОИСКПО​ПОИСКПОЗ​ объёмами данных. Вы​ Вы могли понять,​ПОИСКПОЗ​INDEX​=СУММ(адрес_массива)​

​ Ставим точку с​ узнать данные из​​ только тогда, когда​​ Магазин1(B2:E5), Магазин2(B8:E11) и​​ найдено.​ любой ячейки листа.​ похожих функций, про​, убедитесь, что формула​ когда закончите вводить​E4​​З​​/​ можете добавлять и​ как функции​вызывает сомнение. Кому​​(ИНДЕКС) в Excel​​В нашем конкретном случае​ запятой и указываем​ третьей строки. Поле​ в операции участвуют​ Магазин3(B14:E17).​

​СУММ(($A$2:$D$9=F2)*СТОЛБЕЦ($A$2:$D$9))​ИНДЕКС​​ которых фраза «внешность​​ работает неправильно данное.​ формулу, не забудьте​​. Просто? Да!​​возвращает «Moscow», поскольку​ИНДЕКС​​ удалять столбцы, не​​ПОИСКПОЗ​​ нужно знать положение​​ возвращает значение из​​ сумму заработка всех​​ координаты просматриваемого диапазона.​

​«Номер столбца»​
​ несколько диапазонов.​

​Первый аргумент определен теперь​Наконец, все значения из​же работает более​​ обманчива» работает на​​Если функция​​ нажать​​В учебнике по​ величина населения города​​, которая покажет, какое​​ беспокоясь о том,​​и​​ элемента в диапазоне?​ массива по заданным​ работников за месяц​

​ В нашем случае​​вообще можно оставить​​Таким образом, оператор ищет​​ осталось написать формулы​​ нашей таблицы суммируются​ тонко и запускает​ 100%. Одна из​ПОИСКПОЗ​Ctrl+Shift+Enter​ВПР​ Москва – ближайшее​ место по населению​ что нужно будет​ИНДЕКС​ Мы хотим знать​ номерам строки и​ можно вычислить при​ это адрес столбца​ пустым, так как​ данные в установленном​ для остальных аргументов.​​ (в нашем примере​​ пересчет только при​

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

​Если всё сделано верно,​ формулы с функцией​​ значению (12 269​​ (Москва).​ функцию​​ Последующие примеры покажут​​Позвольте напомнить, что относительное​​ вот такой синтаксис:​​=СУММ(C4:C9)​ После этого закрываем​​ диапазон, в котором​​ строки или столбца.​

​С помощью функции ВЫБОР,​
​ получаем «3» то​

​ что ощутимо ускоряет​ИНДЕКС (INDEX)​ подстановки, возвращается ошибка​​ Вы получите результат​​ВПР​ 006).​Как видно на рисунке​ВПР​​ Вам истинную мощь​​ положение искомого значения​​INDEX(array,row_num,[column_num])​​Но можно её немного​

​ скобку.​
​ используется только один​

​ Данная функция своими​ я создал виртуальную​​ есть номер столбца,​ расчет в тяжелых​. Далеко не все​ # н/д.​ как на рисунке​для поиска по​Эта формула эквивалентна двумерному​ ниже, формула отлично​.​ связки​ (т.е. номер строки​ИНДЕКС(массив;номер_строки;[номер_столбца])​ модифицировать, использовав функцию​После того, как все​​ столбец. Жмем на​​ возможностями очень похожа​​ таблицу данных, состоящую​​ в котором находится​​ книгах по сравнению​​ пользователи Excel про​Если вы считаете, что​ ниже:​​ нескольким критериям. Однако,​​ поиску​

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

​ это как раз​ простое объяснение:​​. Тогда она будет​​ на кнопку​​«OK»​​оператора ВПР​ с именами магазинов,​ просто выбирает и​СМЕЩ​

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

​ еще меньше используют​ данных в электронной​ заметили (и не​​ решения была необходимость​​и позволяет найти​​=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0))​​Используя​ПОИСКПОЗ​ то, что мы​array​ иметь следующий вид:​

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

​«OK»​​.​​, но в отличие​ т.е. A1, A7,​ показывает значения из​.​ все её возможности.​ таблице, но не​ раз), если вводить​ добавлять вспомогательный столбец.​​ значение на пересечении​​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​

​ВПР​​, которая легко справляется​​ должны указать для​​(массив) – это​​=СУММ(C4:ИНДЕКС(C4:C9;6))​.​Результат будет точно такой​ от него может​ A13 и я​ строки заголовка, что​Один из весьма распространенных​ Давайте разберем варианты​ удалось найти его​ некорректное значение, например,​ Хорошая новость: формула​​ определённой строки и​​Теперь у Вас не​​, помните об ограничении​​ с многими сложными​ аргументов​ диапазон ячеек, из​В этом случае в​

​Результат количества заработка Парфенова​ же, что и​ производить поиск практически​ использовал ее как​

​ осуществляется при помощи​
​ на практике сценариев​

​ ее применения, ибо​СООТВЕТСТВИЕ​ которого нет в​ИНДЕКС​

• ​ столбца.​​ должно возникать проблем​​ на длину искомого​ ситуациями, когда​row_num​

  ​ которого необходимо извлечь​
​ координатах начала массива​

• ​ Д. Ф. после​ выше.​​ везде, а не​​ массив для функции​ функции ИНДЕКС.​ применения​ их аж целых​​, возможно, так​​ просматриваемом массиве, формула​
• ​/​В этом примере формула​

  ​ с пониманием, как​
​ значения в 255​

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

​ левом столбце таблицы.​​ после ввода названия​​ вычисления и сосредоточимся​в таком варианте​​Самый простой случай использования​​Ячейка содержит непредвиденные символы​/​​может искать по​​/​

​Во-первых, задействуем функцию​
​ получить ошибку​

​Решая, какую формулу использовать​

​(номер_столбца) функции​​(номер_строки) – это​​ А вот в​«Сумма».​​ как работает данная​​Давайте, прежде всего, разберем​ выбранного магазина в​​ на поиске решения,​​ — это сочетание​

​ функции​
​ или скрытые пробелы.​

​ПОИСКПОЗ​

​ значениям в двух​​ПОИСКПОЗ​​MATCH​#VALUE!​​ для вертикального поиска,​​INDEX​ номер строки в​ координатах указания окончания​Теперь, если в поле​​ функция, но на​​ на простейшем примере​

​ ячейку G1, функция​
​ основанного на формулах​

​ с функцией​

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

​ИНДЕКС​​Ячейка не отформатированное правильный​​сообщает об ошибке​​ столбцах, без необходимости​​будет очень похожа​​(ПОИСКПОЗ), которая находит​​(#ЗНАЧ!). Итак, если​ большинство гуру Excel​​(ИНДЕКС). Как Вы​​ массиве, из которой​ массива используется оператор​​«Имя»​​ практике подобный вариант​​ алгоритм использования оператора​​ ИНДЕКС относится к​ массива.​СЧЁТЗ (COUNTA)​– это ситуация,​ тип данных. Например​#N/A​ создания вспомогательного столбца!​ на формулы, которые​​ положение «Russia» в​​ таблица содержит длинные​ считают, что​

• ​ помните, функция​​ нужно извлечь значение.​​ИНДЕКС​мы изменим содержимое​ её использования применяется​ИНДЕКС​ диапазону с ценами​Пример 2. Вместо формулы​
• ​, чтобы получить автоматически​​ когда нам нужно​​ ячейка содержит числовые​(#Н/Д) или​Предположим, у нас есть​ мы уже обсуждали​ списке:​ строки, единственное действующее​

​ИНДЕКС​ИНДЕКС​​ Если не указан,​​. В данном случае​ с​ все-таки редко.​​для массивов.​​ указанного мной магазина.​​ массива мы можем​​ растягивающиеся диапазоны для​​ извлечь данные из​​ значения, но он​​#VALUE!​ список заказов, и​ в этом уроке,​=MATCH(«Russia»,$B$2:$B$10,0))​ решение – это​/​

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

​может возвратить значение,​ то обязательно требуется​​ первый аргумент оператора​​«Парфенов Д.Ф.»​Урок:​Имеем таблицу зарплат. В​Последней модификацией формулы является​

​ использовать формулу, основанную​​ выпадающих списков, сводных​​ одномерного диапазона-столбца, если​​ может быть отформатированные​​(#ЗНАЧ!). Если Вы​ мы хотим найти​ с одним лишь​=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​ использовать​ПОИСКПОЗ​

​ находящееся на пересечении​ аргумент​​ИНДЕКС​​, на, например,​Мастер функций в Экселе​

​ первом её столбце​
​ добавление функции ПОИСКПОЗ​

​ на функции СУММПРОИЗВ.​ таблиц и т.д.​ мы знаем порядковый​

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

​Принцип работы в основном​Функция ИНДЕКС предназначена для​ номер ячейки. Синтаксис​текст​ сообщение на что-то​
​ критериям –​Как Вы помните, синтаксис​ функции​/​ВПР​

​ столбца, но она​(номер_столбца).​ а второй –​, то автоматически изменится​​ИНДЕКС​​ во втором –​​ и столбца, для​​ такой же, как​

​ создания массивов значений​ в этом случае​.​ более понятное, то​имя покупателя​ функции​INDEX​ПОИСКПОЗ​

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

​ того чтобы эти​ и в первом​​ в Excel и​​ будет:​Решение​ можете вставить формулу​​(Customer) и​​INDEX​

• ​(ИНДЕКС), из которого​​.​ Excel по-прежнему прибегают​​ какие именно строка​​(номер_столбца) – это​ ячейку – шестую.​​ платы в поле​​ вместе с аргументом​ в третьем –​​ значения были переменными,​​ случае. Мы используем​ чаще всего используется​

  ​=ИНДЕКС(Диапазон_столбец; Порядковый_номер_ячейки)​
​: чтобы удалить непредвиденные​

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

• ​ номер столбца в​​Урок:​«Сумма»​​ПОИСКПОЗ​​ величина суммы заработка.​​ а не постоянными.​​ здесь просто другую​ в паре с​​Этот вариант известен большинству​​ символы или скрытых​

  ​ИНДЕКС​
​(Product). Дело усложняется​

  ​ три аргумента:​​ В нашем случае​​ такую формулу с​ВПР​

​ интересуют.​ массиве, из которого​​Полезные функции Excel​​.​

​. Связка​
​ Нам нужно вывести​

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

​ тем, что один​
​INDEX(array,row_num,[column_num])​

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

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

​ на конкретных примерах​​ В таком виде​​ ПЕЧСИМВ или СЖПРОБЕЛЫ​ПОИСКПОЗ​​ покупатель может купить​​ИНДЕКС(массив;номер_строки;[номер_столбца])​A2:A10​, которая ищет в​ гораздо проще. Так​ базовая информация об​ Если не указан,​​ИНДЕКС​​ помощью оператора​​–​​ третьей строке.​ ввожу пример названия​ должна подтверждаться как​ формул с комбинациями​

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

​ проверьте, если ячейки,​​ЕСЛИОШИБКА​​ продуктов, и имена​​ из Вас, кто​​Затем соединяем обе части​

​B5​
​ очень немногие люди​
​ полагаю, что уже​
​ аргумент​

​ Экселе для решения​можно обработать несколько​является мощнейшим инструментом​ будет выводиться результат​​ Теперь, изменяя значения​​ на этот раз​​ других функций для​​часто используется в​ отформатированные как типы​.​​ покупателей в таблице​​ догадался!​ и получаем формулу:​

​до​
​ до конца понимают​

​ становится понятно, как​row_num​ довольно разноплановых задач.​​ таблиц. Для этих​​ при работе в​ обработки. Кликаем по​ в ячейках от​ мы создаём в​ поиска значений при​ связке с функцией​ данных.​

• ​Синтаксис функции​ на листе​​Начнём с того, что​​=INDEX($A$2:$A$10;MATCH(«Russia»;$B$2:$B$10;0))​​D10​​ все преимущества перехода​ функции​(номер_строки)​ Хотя мы рассмотрели​​ целей будет применяться​​ Эксель, который по​
• ​ значку​ G1 до G3,​​ памяти компьютера столько​​ определенных условиях.​ПОИСКПОЗ (MATCH)​​При использовании массива в​​ЕСЛИОШИБКА​
• ​Lookup table​ запишем шаблон формулы.​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​​значение, указанное в​​ с​
• ​ПОИСКПОЗ​Если указаны оба аргумента,​ далеко не все​ дополнительный аргумент​ своему функционалу более​«Вставить функцию»​ в ячейке H1​​ же массивов значений,​​Необходимо выполнить поиск значения​, которая выдает номер​индекс​очень прост:​расположены в произвольном​​ Для этого возьмём​​Подсказка:​

​ ячейке​ВПР​​и​​ то функция​ возможные варианты её​​«Номер области»​​ гибок, чем его​, который размещен сразу​ отображается цена, выбранная​

​ сколько столбов в​​ в таблице и​ искомого значения в​,​IFERROR(value,value_if_error)​​ порядке.​​ уже знакомую нам​Правильным решением будет​A2​на связку​ИНДЕКС​ИНДЕКС​ применения, а только​.​ ближайший аналог –​​ слева от строки​​ на основании трех​ нашей таблице. Каждый​​ отобразить заголовок столбца​​ диапазоне. Таким образом,​

​ПОИСКПОЗ​ЕСЛИОШИБКА(значение;значение_если_ошибка)​Вот такая формула​ формулу​ всегда использовать абсолютные​:​ИНДЕКС​могут работать вместе.​возвращает значение из​ самые востребованные. Существует​Имеем три таблицы. В​ оператор​​ формул.​​ аргументов.​

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

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

​Где аргумент​ИНДЕКС​ИНДЕКС​ ссылки для​=VLOOKUP(A2,B5:D10,3,FALSE)​и​​ПОИСКПОЗ​​ ячейки, находящейся на​​ два типа этой​​ каждой таблице отображена​​ВПР​​Происходит процедура активации​​Этот пример должен был​​ единичном массиве нули​ Готовый результат должен​ легендарную​ функций необходим на​value​/​​/​​ИНДЕКС​​=ВПР(A2;B5:D10;3;ЛОЖЬ)​​ПОИСКПОЗ​​определяет относительную позицию​​ пересечении указанных строки​

​ функции: ссылочный и​​ заработная плата работников​​.​

​Мастера функций​
​ показать, как работает​

​ находятся везде, где​​ выглядеть следующим образом:​​ВПР (VLOOKUP)​ клавиатуре нажмите клавиши​(значение) – это​ПОИСКПОЗ​ПОИСКПОЗ​​и​​Формула не будет работать,​​, а тратить время​​ искомого значения в​​ и столбца.​​ для массивов. Наиболее​ за отдельный месяц.​Основной задачей функции​. В категории​

​ функция ИНДЕКС, а​ искомое значение не​Небольшое упражнение, которое показывает,​​:​​ Ctrl-клавиши Shift +​

​ значение, проверяемое на​
​решает задачу:​и добавим в​ ​ПОИСКПОЗ​
​ если значение в​ на изучение более​

​ заданном диапазоне ячеек,​Вот простейший пример функции​ эффективно её можно​ Нашей задачей является​

​ПОИСКПОЗ​«Ссылки и массивы»​ также какие возможности​ было найдено. Однако​ как в Excelе​… но, в отличие​​ Ввод. Excel автоматически​​ предмет наличия ошибки​

​{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)*​
​ неё ещё одну​

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

​(B2=’Lookup table’!$B$2:$B$13),0),3)}​ функцию​
​ не сбились при​
​A2​

​ не хочет.​

office-guru.ru

Исправление ошибки #Н/Д в функциях ИНДЕКС и ПОИСКПОЗ

​ИНДЕКС​​(ИНДЕКС):​ с другими операторами.​ (третий столбец) второго​ по порядку определенного​«Полный алфавитный перечень»​ функций и массивов.​ функции СТОЛБЕЦ, мы​ результата при использовании​ извлекать значения левее​ в фигурные скобки​ – результат формулы​{=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)*​ПОИСКПОЗ​ копировании формулы в​длиннее 255 символов.​Далее я попробую изложить​использует это число​=INDEX(A1:C10,2,3)​ Созданные таким способом​ работника (вторая строка)​ значения в выделенном​

​ищем наименование​Olivia​ перемножаем каждую такую​ совершенно разных функций.​ поискового столбца и​ {}. Если вы​

​ИНДЕКС​​(B2=’Lookup table’!$B$2:$B$13);0);3)}​​, которая будет возвращать​​ другие ячейки.​​ Вместо неё Вам​​ главные преимущества использования​ (или числа) и​=ИНДЕКС(A1:C10;2;3)​ формулы смогут решать​ за третий месяц​​ диапазоне.​​«ИНДЕКС»​​: Добрый день. Надеюсь​​ временную таблицу на​Итак, у нас есть​ номер столбца-результата высчитывать​ пытаетесь ввести их,​/​Эта формула сложнее других,​ номер столбца.​​Вы можете вкладывать другие​​ нужно использовать аналогичную​ПОИСКПОЗ​

Проблема: Нет соответствий

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

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

• ​ функции Excel в​ формулу​

• ​и​ соответствующей ячейки.​ диапазоне​Автор: Максим Тютюшев​Выделяем ячейку, в которой​ПОИСКПОЗ​​ нашли этого оператора,​​ по такой теме:​

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

Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД

​ИНДЕКС​​Ещё не совсем понятно?​​A1:C10​​Этот учебник рассказывает о​​ будет производиться вывод​такой:​ выделяем его и​ сделан расчет всех​Пример 3. Третий пример​ формула Excel отвечала​ состоит из нескольких​При использовании​value_if_error​ знанием функций​ искать,0)),(MATCH(значение для горизонтального​

Проблема: Несоответствие типа сопоставления и порядка сортировки данных

​и​​/​​в Excel, а​ Представьте функции​​и возвращает значение​​ главных преимуществах функций​ результата и обычным​=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])​ щелкаем по кнопке​ вариантов цены ввоза​ – это также​ на вопрос, в​

• ​ строк и столбцов,​​СООТВЕТСТВИЕ​​(значение_если_ошибка) – это​ИНДЕКС​ поиска,строка в которой​​ПОИСКПОЗ​​ПОИСКПОЗ​ Вы решите –​ИНДЕКС​ ячейки во​ИНДЕКС​ способом открываем​Искомое значение​

• ​«OK»​​ товара, результаты сведены​​ формула массива.​​ каком столбце (или​​ то наша функция​между значениями в качестве​

​ значение, которое нужно​и​​ искать,0))​

​, например, чтобы найти​

​:​​ остаться с​​и​2-й​и​Мастер функций​– это значение,​, которая размещается в​ в таблицу сопоставления​Все точно так же,​ строке, однако в​ будет использоваться немного​ аргумента​

​ возвратить, если формула​​ПОИСКПОЗ​​=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального​​ минимальное, максимальное или​=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))​ВПР​ПОИСКПОЗ​

У вас есть вопрос об определенной функции?

​строке и​ПОИСКПОЗ​

Помогите нам улучшить Excel

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

См. также

​тип_сопоставления​ выдаст ошибку.​

​Вы одолеете ее.​ поиска,столбец, в котором​ ближайшее к среднему​=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))​

​или переключиться на​

​в таком виде:​

​3-м​в Excel, которые​

​ типа оператора выбираем​ диапазоне мы ищем;​

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

​ использую столбец), находится​=ИНДЕКС(Диапазон; Номер_строки; Номер_столбца)​

​и порядок сортировки​Например, Вы можете вставить​

support.office.com

5 вариантов использования функции ИНДЕКС (INDEX)

​ Самая сложная часть​ искать,0)),(MATCH(значение для горизонтального​ значение. Вот несколько​4. Более высокая скорость​ИНДЕКС​=INDEX(столбец из которого извлекаем,(MATCH​столбце, то есть​ делают их более​ ссылочный вид. Это​Просматриваемый массив​ котором нужно выбрать​ Наилучшая цена выбрана​ запись немного отличается.​ искомое слово. Думаю,​

​Т.е. функция извлекает значение​ значений в массиве​ формулу из предыдущего​ – это функция​ поиска,строка в которой​ вариантов формул, применительно​ работы.​/​​ (искомое значение,столбец в​​ из ячейки​ привлекательными по сравнению​ нам нужно потому,​– это диапазон,​ один из типов​ формулой НАИМЕНЬШИЙ в​ Я рекомендую самостоятельно​ что анимация, расположенная​ из ячейки диапазона​

Вариант 1. Извлечение данных из столбца по номеру ячейки

​ подстановки должно быть​ примера в функцию​​ПОИСКПОЗ​​ искать,0))​ к таблице из​Если Вы работаете​ПОИСКПОЗ​ котором ищем,0))​C2​ с​ что именно этот​

​ в котором находится​

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

​ тип поддерживает работу​ это значение;​«Массив»​ Выведена в отдельное​ с целью лучше​ задачу.​

Вариант 2. Извлечение данных из двумерного диапазона

​ и столбца с​ отличается от перечисленных​вот таким образом:​ объяснить первой.​ двумерного поиска нужно​1.​

​ то разница в​

​1. Поиск справа налево.​ значение;столбец в котором​Очень просто, правда? Однако,​. Вы увидите несколько​ с аргументом​

​Тип сопоставления​или​​ поле — для​​ усвоить материал.​​​​ заданными номерами.​ ниже правил, появляется​

Вариант 3. Несколько таблиц

​=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),​MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)​ указать всю таблицу​​MAX​​ быстродействии Excel будет,​Как известно любому​ ищем;0))​ на практике Вы​ примеров формул, которые​«Номер области»​

​– это необязательный​

​«Ссылка»​ внешних пользователей.​И наконец-то, несколько иной​Пример 1. Первая идя​Легко сообразить, что с​ ошибка # н/д.​»Совпадений не найдено.​ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)​

Вариант 4. Ссылка на столбец / строку

​ в аргументе​(МАКС). Формула находит​​ скорее всего, не​​ грамотному пользователю Excel,​Думаю, ещё проще будет​ далеко не всегда​ помогут Вам легко​.​ параметр, который определяет,​. Нужный нам вариант​Хотелось бы рядом​ (не табличный) подход.​

​ для решения задач​​ помощью такой вариации​​Если​ Попробуйте еще раз!»)​В формуле, показанной выше,​​array​​ максимум в столбце​ заметная, особенно в​ВПР​ понять на примере.​​ знаете, какие строка​​ справиться со многими​​Открывается окно аргументов. В​​ точно или приблизительно​

Вариант 5. Ссылка на ячейку

​«Массив»​ с найденной ценой​ Как правило, каждый​ типа – это​​ИНДЕКС​​тип_сопоставления​​=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));​​ искомое значение –​(массив) функции​D​​ последних версиях. Если​​не может смотреть​ Предположим, у Вас​ и столбец Вам​​ сложными задачами, перед​​ поле​ искать значения. Мы​. Он расположен первым​ вывести соответствующего поставщика​ раз, когда необходимо​ при помощи какого-то​и двух функций​равен 1 или​»Совпадений не найдено.​​ это​​INDEX​

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

​ВПР​нам нужно указать​ значения, поэтому данный​​ выделен. Поэтому нам​​ Когда вычисляла каждую​ Excel, моментально на​ прочитать каждую ячейку​​можно легко реализовать​​ в​И теперь, если кто-нибудь​, а массив поиска​А теперь давайте испытаем​

planetaexcel.ru

Примеры формул с функциями ИНДЕКС и ПОИСКПОЗ СУММПРОИЗВ в Excel

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

Поиск значений по столбцам таблицы Excel

​ двумерный поиск:​просматриваемый_массив​ введет ошибочное значение,​ – это результат​ этот шаблон на​той же строки:​

​ строк и сотни​ находиться в крайнем​ из столиц, например,​.​В нескольких недавних статьях​

​ диапазонов. Для этого​С помощью этого инструмента​ на кнопку​ хотя понимаю, что​ функций: ИНДЕКС и​ и сравнить её​Если таблица не одна,​должен быть в​ формула выдаст вот​ умножения. Хорошо, что​ практике. Ниже Вы​=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))​ формул поиска, Excel​

​ левом столбце исследуемого​

Формула массива или функции ИНДЕКС и СУММПРОИЗВ

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

​ же мы должны​ видите список самых​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))​ будет работать значительно​ диапазона. В случае​ формулу:​MATCH​ усилия, чтобы разъяснить​

1. ​ поле и выделяем​
2. ​ аргументов​.​ более грамотным решением.​ выглядит так:​ Если их значения​ то функция​ -2, -1, 0,​Если Вы предпочитаете в​ перемножить и почему?​ населённых стран мира.​

​Результат: Beijing​ быстрее, при использовании​

​ с​=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0))​

​(ПОИСКПОЗ) в Excel​

​ начинающим пользователям основы​ первый диапазон с​«Номер строки»​Открывается окно аргументов функции​ А вот по​Формула построена из блоков​ совпадают, то отображается​ИНДЕКС​ 1, 2…, A,​ случае ошибки оставить​ Давайте разберем все​ Предположим, наша задача​2.​ПОИСКПОЗ​ПОИСКПОЗ​=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Japan»;$B$2:$B$10;0))​ ищет указанное значение​ функции​ зажатой левой кнопкой​и​ИНДЕКС​ сути обратную к​ и имеет более​ имя соответствующего столбца.​может извлечь данные​

​ B и C…,​

​ ячейку пустой, то​ по порядку:​ узнать население США​MIN​и​/​Теперь давайте разберем, что​ в диапазоне ячеек​ВПР​ мыши. Затем ставим​«Номер столбца»​. Как выше говорилось,​ ИНДЕКСу операцию изобразить​ или менее следующий​Когда речь идет о​ из нужной строки​ ЛОЖЬ, ИСТИНА лишь​ можете использовать кавычки​

​Берем первое значение в​ в 2015 году.​(МИН). Формула находит​ИНДЕКС​ИНДЕКС​ делает каждый элемент​ и возвращает относительную​и показать примеры​ точку с запятой.​

​в функцию​

​ у неё имеется​ не могу -​ вид:​ циклах Excel (за​ и столбца именно​ некоторые из них.​ («»), как значение​ столбце​Хорошо, давайте запишем формулу.​ минимум в столбце​вместо​, столбец поиска может​ этой формулы:​

​ позицию этого значения​ более сложных формул​ Это очень важно,​ИНДЕКС​ три аргумента, а​

Пример формулы поиска значений с функциями ИНДЕКС и ПОИСКПОЗ

​ никак не даются​1*НЕ(ЕОШИБКА(ПОИСКПОЗ(F5;A2:A9;0)))​ исключением VBA, конечно,​ заданной таблицы. В​

​Если​ второго аргумента функции​A​ Когда мне нужно​D​ВПР​ быть, как в​Функция​ в диапазоне.​ для продвинутых пользователей.​ так как если​.​ соответственно и три​ мне индексы.​Изменяются только выделенные фрагменты.​ потому что там​ этом случае используется​тип_сопоставления​ЕСЛИОШИБКА​(Customer) на листе​ создать сложную формулу​и возвращает значение​. В целом, такая​ левой, так и​MATCH​Например, если в диапазоне​

Пример формулы функций ИНДЕКС и НЕ

​ Теперь мы попытаемся,​ вы сразу перейдете​Посмотрим, как это можно​

​ поля для заполнения.​В прикрепленном файле​ Для каждого столбца​ дело намного проще),​ следующий синтаксис:​-1, значения в​. Вот так:​Main table​

​ в Excel с​ из столбца​ замена увеличивает скорость​ в правой части​(ПОИСКПОЗ) ищет значение​B1:B3​ если не отговорить​ к выделению следующего​ сделать на конкретном​В поле​

​ — итоговая таблица​ с помощью функции​ на ум приходят​=ИНДЕКС((Диапазон1;Диапазон2;Диапазон3); Номер_строки; Номер_столбца; Номер_диапазона)​

​просматриваемый_массив​

​IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»)​и сравниваем его​ вложенными функциями, то​C​ работы Excel на​ диапазона поиска. Пример:​ «Japan» в столбце​содержатся значения New-York,​ Вас от использования​ массива, то его​ примере. Работаем все​«Массив»​ с проблемой.​ ПОИСКПОЗ, мы проверяем​ в первую очередь​Обратите особое внимание, что​должен быть в​ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»»)​ со всеми именами​ я сначала каждую​той же строки:​13%​ Как находить значения,​B​ Paris, London, тогда​ВПР​ адрес просто заменит​ с той же​нужно указать адрес​Serge_007​ удалось ли найти​ следующие решения:​ в этом случае​ порядке убывания.​Надеюсь, что хотя бы​

exceltable.com

Примеры функции ИНДЕКС и ПОИСКПОЗ с несколькими условиями Excel

​ покупателей в таблице​ вложенную записываю отдельно.​=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))​.​ которые находятся слева​, а конкретно –​ следующая формула возвратит​, то хотя бы​ координаты предыдущего. Итак,​ таблицей, о которой​ обрабатываемого диапазона данных.​:​

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

​ в столбце искомое​Итерационные (итеративные) вычисления.​ первый аргумент –​В приведенном ниже примере​ одна формула, описанная​ на листе​Итак, начнём с двух​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))​Влияние​ покажет эту возможность​ в ячейках​ цифру​ показать альтернативные способы​ после введения точки​ шла речь выше.​

​ Его можно вбить​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС($B5:$C8;СУММПРОИЗВ(($D5:$N8=$D11)*СТРОКА(1:4));СТОЛБЕЦ(A1))​ выражение. Если нет,​Формулы массива (или такие​ список диапазонов -​ — функция​ в этом учебнике,​Lookup table​ функций​Результат: Lima​ВПР​ в действии.​B2:B10​

​3​ реализации вертикального поиска​ с запятой выделяем​

​ Отдельно у нас​

Формула из комбинации функций ВЫБОР, ИНДЕКС и ПОИСКПОЗ

​ вручную. Но для​Olivia​ то функция возвращает​ функции, как СУММПРОИЗВ,​ заключается в скобки,​ПОИСКПОЗ​ показалась Вам полезной.​(A2:A13).​ПОИСКПОЗ​3.​на производительность Excel​2. Безопасное добавление или​, и возвращает число​, поскольку «London» –​ в Excel.​ следующий диапазон. Затем​

​ имеется два дополнительных​ облегчения задачи мы​: То, что нужно​ ошибку. С помощью​ которые работают с​ а сами диапазоны​=MATCH(40,B2:B10,-1)​ Если Вы сталкивались​Если совпадение найдено, уравнение​, которые будут возвращать​AVERAGE​ особенно заметно, если​ удаление столбцов.​3​ это третий элемент​Зачем нам это? –​ опять ставим точку​ поля –​

​ поступим иначе. Ставим​. Еще и​ функции ЕОШИБКА мы​ массивами, хотя мы​ перечисляются через точку​Аргумент​

exceltable.com

формула, обратная ИНДЕКСу

​ с другими задачами​​ возвращает​ номера строки и​(СРЗНАЧ). Формула вычисляет​ рабочая книга содержит​Формулы с функцией​, поскольку «Japan» в​ в списке.​ спросите Вы. Да,​ с запятой и​«Имя»​ курсор в соответствующее​ с индексом.​ проверяем выдала ли​ их не подтверждаем​ с запятой.​
​тип_сопоставления​ поиска, для которых​1​ столбца для функции​ среднее в диапазоне​ сотни сложных формул​ВПР​ списке на третьем​=MATCH(«London»,B1:B3,0)​ потому что​ выделяем последний массив.​и​ поле, а затем​Большое спасибо, Serge_007​ функция ПОИСКПОЗ ошибку.​
​ с помощью Ctr​Если во втором варианте​в синтаксисе присваивается​

​ не смогли найти​​(ИСТИНА), а если​​ИНДЕКС​

​D2:D10​​ массива, таких как​​перестают работать или​ месте.​
​=ПОИСКПОЗ(«London»;B1:B3;0)​

excelworld.ru

​ВПР​

ПОИСКПОЗ в Excel

Автор: Неструев Саня · Опубликовано 15.02.2019 · Обновлено 15.02.2019

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

Мы рассмотрим следующие темы:

• назначение и описание ПОИСКПОЗ;
• синтаксис формулы;
• пошаговая инструкция по набору;
• примеры использования;
• комбинации с другими командами в «Экселе».

Назначение ПОИСКПОЗ

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

На английском написание команды в «Эксель» выглядит как MATCH. Чаще всего используется в качестве вспомогательной функции вкупе с ИНДЕКС, ВПР, ВЫБОР и другими. Однако мы рассмотрим и одиночное использование.

Правильное написание

Формула включает в себя следующие элементы:

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

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

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

Типы сопоставления

Рассмотрим подробнее разницу между несколькими типами сопоставления:

• 1 или без указания данного параметра – определяет наибольшее значение, которое максимально приближено к указанному в формуле;
• 0 – первое значение в диапазоне, которое равно искомому;
• -1 – наименьшее значение относительно искомого.

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

Ввод команды в Excel

Теперь разберемся, как вводится формула на практике. Первый вариант добавления – это отдельное меню со списком функций:

1. Сверху над основной таблицей находим иконку функции и жмем ее.

1. В открывшемся окне нужно выбрать категорию «Ссылки и массивы», затем найти нужный вариант в списке и нажать «ОК».

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

Для редактирования формулы можно использовать верхнюю строку. С помощью кнопки креста удаляется введенная строка.

Примеры использования

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

Поиск по одному критерию

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

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

1. Ставим точку с запятой и выделяем весь массив. Он может состоять как из одного столбца, как в примере, так и из полноценной таблицы.

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

1. Жмем клавишу Enter и видим готовый результат.

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

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

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

Поиск по двум критериям

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

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

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

Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.

Для вычисления позиции необходимо воспользоваться комбинацией Ctrl + Shift + Enter . Так вы запустите выполнение функции в массиве.

Взаимодействие с другими операторами

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

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

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

Вторым аргументом будет функция ПОИСКПОЗ с указанием ссылки на ячейку с названием месяца и диапазона столбца массива:

Последний аргумент – еще одна ПОИСКПОЗ, но теперь с ссылками на название марки и строкой с автомобилями:

Жмем на Enter и видим результат в соответствующей ячейке. Проверяем по нашей таблице.

Теперь можно менять значения в нашей таблице справа и получать новые значения:

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

По представленному примеру описываемый оператор может использоваться во многих формулах. Например:

• ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ));
• ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА;(СОВПАД))));
• ИНДЕКС(ПОИСКПОЗ(МИН(ЕСЛИ))) и другие.

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

Видеоинструкция

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

Заключение

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

Функции MS Excel ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) – более гибкая альтернатива ВПР

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

ВПР требует, чтобы в диапазоне с искомыми данными столбец критериев всегда был первым слева. Это обстоятельство, конечно, является ограничением ВПР. Как же быть, если искомые данные находятся левее столбца с критерием? Можно, конечно, расположить столбцы в нужном порядке, что в целом, является неплохим выходом из ситуации. Но бывает так, что сделать этого нельзя, или трудно. К примеру, вы работаете в чужом документе или регулярно получаете новый отчет. В общем, нужно решение, не зависящее от расположения столбцов. Такое решение существует.

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

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

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

Довольно стандартная ситуация, с которой успешно справляется функция ЕСЛИОШИБКА. Она перехватывает ошибки и вместо них выдает что-либо другое, например, нули.

Конструкция формулы будет следующая:

Вот, собственно, и все.

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

Ниже видеоурок по работе функций ИНДЕКС и ПОИСКПОЗ.

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

В этой статье рассматривается синтаксис функции ПОИСКПОЗ Excel, а также как использовать функцию ПОИСКПОЗ в Excel с примерами формул.

Также в этой статье рассматривается комбинация формул ИНДЕКС ПОИСКПОЗ в Excel.

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

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

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

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

Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) – определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

• 1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
• 0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
• -1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

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

=ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel

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

Особенности функции ПОИСКПОЗ Excel

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

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

Использование ПОИСКПОЗ в Excel – примеры формул

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

Формула ПОИСКПОЗ Excel с подстановочными знаками

Как и многие другие функции Excel, ПОИСКПОЗ распознает следующие подстановочные знаки:

• Вопросительный знак (?) — заменяет любой символ
• Звездочка (*) — заменяет любую последовательность символов

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

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

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

Предполагая, что названия компаний находятся в диапазоне A2:A8, и вы ищете название, начинающееся с «восток», формула выглядит следующим образом:

=ПОИСКПОЗ(«восток*»; A2:A8; 0)

Чтобы сделать нашу формулу ПОИСКПОЗ более универсальной, вы можете ввести значение поиска в любой ячейке (E1 в этом примере) и объединить эту ячейку с подстановочным символом, например:

=ПОИСКПОЗ(E1&»*»; A2:A8; 0)

Как показано на изображении ниже, формула возвращает 2 позицию, которая соответствует компании « Восток-авто »:

Функция ПОИСКПОЗ в Excel – Пример функции ПОИСКПОЗ Excel с подстановочными знаками

Чтобы заменить только один символ в значении поиска, используйте подстановочный оператор «?», например:

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

Формула ПОИСКПОЗ с учетом регистра

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

Чувствительность к регистру формулы для сопоставления данных в Excel заключается в следующем:

Логика этой формулы такова:

• Функция СОВПАД сравнивает значение поиска с каждым элементом просматриваемого массива. Если сравниваемые ячейки в точности равны, функция возвращает ИСТИНУ, или ЛОЖЬ в противном случае.
• Далее функция ПОИСКПОЗ сравнивает ИСТИНУ (которая является ее искомым_значением) с каждым значением в массиве, возвращаемом СОВПАД, и возвращает позицию первого совпадения

Обратите внимание , что это формула массива, которая требует нажатия Ctrl+Shift+Enter для правильной обработки.

Пусть значение поиска находится в ячейке E1, а массив поиска — A2:A8, тогда формула выглядит следующим образом:

=ПОИСКПОЗ(ИСТИНА; СОВПАД(A2:A8; E1);0)

На следующем изображении демонстрируется формула ПОИСКПОЗ с учетом регистра в Excel:

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

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

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

=ЕСЛИ(ЕНД(ПОИСКПОЗ (1-е значение в Списке 1; Список 2; 0)); «Нет в Списке 1»; «»)

Для любого значения из Списка 2, отсутствующего в Списке 1, формула возвращает «Нет в Списке 1». И вот как это происходит:

• Функция ПОИСКПОЗ выполняет поиск значения из Списка 1 в Списке 2. Если значение найдено, она возвращает относительное положение, в противном случае ошибку #Н/Д.
• Функция ЕНД в Excel выполняет только одно: проверяет наличие ошибок #Н/Д (что означает «недоступно»). Если заданное значение является ошибкой #Н/Д, функция возвращает ИСТИНА, в противном случае – ЛОЖЬ. В нашем случае ИСТИНА означает, что значение из Списка 1 не найдено в Списке 2 (ПОИСКПОЗ возвращает ошибку #Н/Д).
• Поскольку для других пользователей будет сложно интерпретировать результаты в формате ИСТИНА/ЛОЖЬ, то мы будем использовать функцию ЕСЛИ для отображения текста «Нет в Списке 1» для значений, которых нет в Списке 1.

Предположим, что нам необходимо сравнить значения в столбце B со значениями в столбце A, формула примет следующую форму (где B2 — самая верхняя ячейка):

=ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0));»Нет в Списке 1″; «»)

Как вы помните, функция ПОИСКПОЗ Excel сама по себе не учитывает регистр. Чтобы заставить ее различать регистр, вставьте функцию СОВПАД в аргумент просматриваемый_массив и не забудьте нажать Ctrl+Shift+Enter , чтобы завершить эту формулу массива :

=ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A;B2); 0));»Нет в Списке 1″; «»)

На следующем изображении демонстрируются обе формулы в действии:

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

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

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

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

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

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

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

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

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

Вот простейший пример формулы ИНДЕКС:

Формула ищет в ячейках от A1 до C10 и возвращает значение ячейки во второй строке и третьем столбце, то есть в ячейке C2.

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

ИНДЕКС ПОИСКПОЗ в Excel пример

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

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

Комбинацию ИНДЕКС ПОИСКПОЗ в Excel можно представить таким образом:

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

Для лучшего понимания рассмотрим наглядный пример. Предположим, у нас есть список стран с населением, подобных этому:

Функция ПОИСКПОЗ в Excel – Исходные данные для формулы ИНДЕКС ПОИСКПОЗ в Excel

Найдем численность населения, например, России, используя следующую формулу ИНДЕКС ПОИСКПОЗ:

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

• Функция ПОИСКПОЗ выполняет поиск значения «Россия» в столбце B, точнее в ячейках B2:B11 и возвращает номер 7, потому что «Россия» находится на седьмом месте в списке.
• Функция ИНДЕКС принимает «7» во втором параметре (номер_строки), который указывает, из какой строки вы хотите вернуть значение, и превращается в простую формулу =ИНДЕКС($C$2:$C$11, 7). То есть формула производит поиск в ячейках C2-C11 и возвращает значение ячейки в седьмой строке, то есть ячейке C8, потому что мы начинаем отсчет со второй строки.

И вот результат, который мы получаем в Excel:

Функция ПОИСКПОЗ в Excel – ИНДЕКС ПОИСКПОЗ пример формулы

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

5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

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

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

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

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

Вариант 2. Извлечение данных из двумерного диапазона

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

=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )

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

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

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

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

Вариант 4. Ссылка на столбец / строку

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

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

Вариант 5. Ссылка на ячейку

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

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

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

ПОИСКПОЗ (функция ПОИСКПОЗ)

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.

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

Аргументы функции ПОИСКПОЗ описаны ниже.

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

Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.

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

Тип_сопоставления. Необязательный аргумент. Число -1, 0 или 1. Аргумент тип_сопоставления указывает, каким образом в Microsoft Excel искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. По умолчанию в качестве этого аргумента используется значение 1.

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

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

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

Что возвращает функция

Возвращает данные из конкретной строки и столбца табличных данных.

Синтаксис

=INDEX (array, row_num, [col_num]) — английская версия

=INDEX (array, row_num, [col_num], [area_num]) — английская версия

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

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

Аргументы функции

• array (массив) — диапазон ячеек или массив данных для поиска;
• row_num (номер_строки) — номер строки, в которой находятся искомые данные;
• [col_num] ([номер_столбца]) (необязательный аргумент) — номер колонки, в которой находятся искомые данные. Этот аргумент необязательный. Но если в аргументах функции не указаны критерии для row_num (номер_строки), необходимо указать аргумент col_num (номер_столбца);
• [area_num] ([номер_области]) — (необязательный аргумент) — если аргумент массива состоит из нескольких диапазонов, то это число будет использоваться для выбора всех диапазонов.

Дополнительная информация

• Если номер строки или колонки равен “0”, то функция возвращает данные всей строки или колонки;
• Если функция используется перед ссылкой на ячейку (например, A1), она возвращает ссылку на ячейку вместо значения (см. примеры ниже);
• Чаще всего INDEX (ИНДЕКС) используется совместно с функцией MATCH (ПОИСКПОЗ);
• В отличие от функции VLOOKUP (ВПР), функция INDEX (ИНДЕКС) может возвращать данные как справа от искомого значения, так и слева;
• Функция используется в двух формах — Массива данных и Формы ссылки на данные:

— Форма «Массива» используется когда вы хотите найти значения, основанные на конкретных номерах строк и столбцов таблицы;

— Форма «Ссылок на данные» используется при поиске значений в нескольких таблицах (используете аргумент [area_num] ([номер_области]) для выбора таблицы и только потом сориентируете функцию по номеру строки и столбца.

Примеры использования функции ИНДЕКС в Excel

Пример 1. Ищем результаты экзамена по физике для Алексея

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

Для того, чтобы найти результаты экзамена по физике для Андрея нам нужна формула:

=INDEX($B$3:$E$9,3,2) — английская версия

=ИНДЕКС($B$3:$E$9;3;2) — русская версия

В формуле мы определили аргумент диапазона данных, где мы будем искать данные $B$3:$E$9. Затем, указали номер строки “3”, в которой находятся результаты экзамена для Андрея, и номер колонки “2”, где находятся результаты экзамена именно по физике.

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

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

Пример динамического отображения данных ниже:

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

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

=INDEX($B$3:$E$9,MATCH($G$4,$A$3:$A$9,0),MATCH($H$3,$B$2:$E$2,0)) — английская версия

=ИНДЕКС($B$3:$E$9;ПОИСКПОЗ($G$4;$A$3:$A$9;0);ПОИСКПОЗ($H$3;$B$2:$E$2;0)) — русская версия

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

Динамический отображение строки задается следующей частью формулы —

MATCH($G$4,$A$3:$A$9,0) — английская версия

ПОИСКПОЗ($G$4;$A$3:$A$9;0) — русская версия

Она сканирует имена студентов и определяет значение поиска ($G$4 в нашем случае). Затем она возвращает номер строки для поиска в наборе данных. Например, если значение поиска равно Алексей, функция вернет “1”, если это Максим, оно вернет “4” и так далее.

Динамическое отображение данных столбца задается следующей частью формулы —

MATCH($H$3,$B$2:$E$2,0) — английская версия

ПОИСКПОЗ($H$3;$B$2:$E$2;0) — русская версия

Она сканирует имена объектов и определяет значение поиска ($H$3 в нашем случае). Затем она возвращает номер столбца для поиска в наборе данных. Например, если значение поиска Математика, функция вернет “1”, если это Физика, функция вернет “2” и так далее.

Пример 3. Создаем динамический поиск значений с использованием функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) и выпадающего списка

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

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

Пример ниже:

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

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

=INDEX($B$3:$E$9,MATCH($G$4,$A$3:$A$9,0),MATCH($H$3,$B$2:$E$2,0)) — английская версия

=ИНДЕКС($B$3:$E$9;ПОИСКПОЗ($G$4;$A$3:$A$9;0);ПОИСКПОЗ($H$3;$B$2:$E$2;0)) — русская версия

Единственное отличие, от Примера 2, мы на месте ввода имени и предмета создадим выпадающие списки:

• Выбираем ячейку, в которой мы хотим отобразить выпадающий список с именами студентов;
• Кликаем на вкладку “Data” => Data Tools => Data Validation;
• В окне Data Validation на вкладке “Settings” в подразделе Allow выбираем “List”;
• В качестве Source нам нужно выбрать диапазон ячеек, в котором указаны имена студентов;
• Кликаем ОК

Теперь у вас есть выпадающий список с именами студентов в ячейке G5. Таким же образом вы можете создать выпадающий список с предметами.

Пример 4. Использование трехстороннего поиска с помощью INDEX (ИНДЕКС) / MATCH (ПОИСКПОЗ)

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

Что такое трехсторонний поиск?

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

Теперь предположим, что к концу года студент прошел три уровня экзаменов: «Вступительный», «Полугодовой» и «Итоговый экзамен».

Трехсторонний поиск — это возможность получить отметки студента по заданному предмету с указанным уровнем экзамена.

Вот пример трехстороннего поиска:

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

Для таких расчетов нам поможет формула:

=INDEX(($B$3:$E$7,$B$11:$E$15,$B$19:$E$23),MATCH($G$4,$A$3:$A$7,0),MATCH($H$3,$B$2:$E$2,0),IF($H$2=»Вступительный»,1,IF($H$2=»Полугодовой»,2,3))) — английская версия

=ИНДЕКС(($B$3:$E$7;$B$11:$E$15;$B$19:$E$23);ПОИСКПОЗ($G$4;$A$3:$A$7;0);ПОИСКПОЗ($H$3;$B$2:$E$2;0); ЕСЛИ($H$2=»Вступительный»;1;ЕСЛИ($H$2=»Полугодовой»;2;3))) — русская версия

Давайте разберем эту формулу, чтобы понять, как она работает.

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

=INDEX (array, row_num, [col_num]) — английская версия

=INDEX (array, row_num, [col_num], [area_num]) — английская версия

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

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

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

Рассмотрим каждую часть формулы на основе второго синтаксиса.

• array (массив) – ($B$3:$E$7,$B$11:$E$15,$B$19:$E$23):Вместо использования одного массива, в данном случае мы использовали три массива в круглых скобках.
• row_num (номер_строки) – MATCH($G$4,$A$3:$A$7,0): функция MATCH (ПОИСКПОЗ) используется для поиска имени студента для ячейки $G$4 из списка всех студентов.
• col_num (номер_столбца) – MATCH($H$3,$B$2:$E$2,0): функция MATCH (ПОИСКПОЗ) используется для поиска названия предмета для ячейки $H$3 из списка всех предметов.
• [area_num] ([номер_области]) – IF($H$2=”Вступительный”,1,IF($H$2=”Полугодовой”,2,3)): Значение номера области сообщает функции INDEX (ИНДЕКС), какой массив с данными выбрать. В этом примере у нас есть три массива в первом аргументе. Если вы выберете «Вступительный» из раскрывающегося меню, функция IF (ЕСЛИ) вернет значение “1”, а функция INDEX (ИНДЕКС) выберут 1-й массив из трех массивов ($B$3:$E$7).

Уверен, что теперь вы подробно изучили работу функции INDEX (ИНДЕКС) в Excel!