Svinkovod.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция ИНДЕКС (англ. INDEX) в Excel с примерами

Функция ИНДЕКС (англ. INDEX) в Excel с примерами

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

Давайте вспомним как работает эта функция, а после этого рассмотрим работу данной функции совместно с функцией ПОИСКПОЗ (англ. MATCH)

Посмотрите на вот этот пример

Функция индекс в Excel с примером

Есть таблица с продажами различных фруктов в разных магазинах — это область A2:F10

Напомню синтаксис функции ИНДЕКС:

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

где массив — это наша таблица A2:F10

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

номер_столбца — это номер столбца указанного массива. В нашем случае первый столбец совпадает с первым столбцом нашего массива.

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

=ИНДЕКС( A2:F10 ; 5 ; 3 ) — смотрите рисунок выше

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

Функция ИНДЕКС в Excel с функцией ПОИСКПОЗ

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

Функция ИНДЕКС и ПОИСКПОЗ с примером

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

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

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

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

=ИНДЕКС( A2:F10 ; 3 ; 3 ) — груша находится в третьей строке таблицы A2:F10, а Перекресток в третьем столбце . Отлично, а теперь пропишем формулу, чтобы номер строки и номер столбца считался автоматически.

Чтобы найти номер строки используем функцию ПОИСКПОЗ — поиск позиции. Синтаксис функции:

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

искомое_значение — нашем случае, в ячейке L4 мы ищем груши, поэтому искомое значение у нас будет K4

просматриваемый массив — нашем примере нам необходимо найти груши с столбце с фруктами — это диапазон A2:A10

тип_сопоставления — указываем 0, так как мы ищем полное совпадение.

Формула будет иметь следующий вид:

=ПОИСКПОЗ(K4;A2:A10;0) — итогом данной формулы будет позиция 3 в диапазоне A2:A10

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

искомое_значение — магазин перекресток или ячейка L3

просматриваемый массив — магазин мы находим в строке с магазинами — это диапазон A2:F2

тип_сопоставления — указываем 0, так как мы ищем точное совпадение.

в итоге получаем формулу:

=ПОИСКПОЗ(L3;A2:F2;0) — итогом данной формулы будет позиция 3 в диапазоне A2:F2

Теперь в нашу формулу =ИНДЕКС( A2:F10 ; 3 , 3 ) вместо номера строки и номера столбца пропишем раноценные значения, но в виде формул:

=ИНДЕКС( A2:F10 ; ПОИСКПОЗ(K4;A2:A10;0) ; ПОИСКПОЗ(L3;A2:F2;0) )

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

наш массив A2:F10 имеет относительный адрес, поэтому при протягивании формулы вниз и право диапазон так же будет сдвигаться, а он у нас постоянный, поэтому пропишем преобразуем его в абсолютный адрес, для этого пропишем знаки долларов перед столбцами и строки (можно выделить данный диапазон в формуле и нажать клавишу F4).

Далее идет номер строки с формулой ПОИСКПОЗ(K4;A2:A10 ;0) , при протягивании вниз у нас автоматически K4 (Груши) поменяется на K5 (Сливы), что нам и требуется, но диапазон, А2:A10 у нас постоянный, поэтому пропишем его в абсолютном виде А2:A10 → $А$2:$A$10

Читайте так же:
Как в ворде написать формулу с корнем

Все отлично, но когда мы будем протягивать формулу вправо, то K4 (Груши) автоматически поменяется на L4, нам же необходимо, чтобы при протягивании право K4 не менялось. Но мы помним, что в то же время нам необходимо, чтобы K4 менялось при протягивании вниз. Поэтому нам необходимо закрепить только столбец (K), а строка должна меняться. Пропишем знак доллар только перед столбцом К4 → $ K4

В итоге формула поиска номера строки будет выглядеть ПОИСКПОЗ($K4;$A$2:$A$10 ;0)

Аналогично с номером столбца, диапазон должен быть полностью закреплен, при протягивании вправо столбец должен меняться, а при протягивании вниз номер строки (строка с магазинами) не должен меняться. Для этого пропишем знак доллара только перед номером строки L3 → L$3

В итоге формула поиска номера столбца будет выглядеть ПОИСКПОЗ(L$3;$A$2:$F$2;0)

Итоговая формула, которую вы можете протянуть вниз и вправо

=ИНДЕКС( $ A$2:$F$10 ; ПОИСКПОЗ($K4;$A$2:$A$10;0) ; ПОИСКПОЗ(L$3;$A$2:$F$2;0) )

=INDEX($A$2:$F$10;MATCH($K4;$A$2:$A$10;0);MATCH(L$3;$A$2:$F$2;0)) — англ. версии

Функция индекс и поискпоз в Excel с примером

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

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

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

Функция INDEX (ИНДЕКС) в Excel. Как использовать?

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

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

Функция INDEX в Excel - 1

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

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

Синтаксис

=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 в 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!

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Функция ПОИСКПОЗ в 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:

1-funktciia-poiskpoz-v-excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2-funktciia-poiskpoz-v-excel

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

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

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

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

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

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

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

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

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

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

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

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

3-funktciia-poiskpoz-v-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″; «»)

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

4-funktciia-poiskpoz-v-excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5-funktciia-poiskpoz-v-excel

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

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

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

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

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

6-funktciia-poiskpoz-v-excel

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

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

ПОИСКПОЗ в Excel

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

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

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

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

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

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

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

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

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

ПОИСКПОЗ

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

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

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

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

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

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

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

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

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

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

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

Ссылки и массивы

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

Поля для ввода данных

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

Редактирование формулы

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

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

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

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

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

Простой поиск

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

Выделение массива

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

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

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

Ввод результата

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

Смена значения

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

Поиск по датам

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

Результат

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

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

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

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

Простая таблица

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

Поиск нужной ячейки

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

Поиск точного значения

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

Выполнение функции

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

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

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

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

ИНДЕКС

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

ПОИСКПОЗ с указанием ссылки на ячейку

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

ПОИСКПОЗ по маркам

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

Правильный результат

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

Подмена значений

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

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

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

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

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

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

Заключение

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector