Функция поискпоз в excel

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

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

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

– Форма “Ссылок на данные” используется при поиске значений в нескольких таблицах (используете аргумент () для выбора таблицы и только потом сориентируете функцию по номеру строки и столбца.

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

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

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

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

Пример 3: Продолжение поиска с использованием Find с параметром After.

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом

Свойство FindFormat можно задавать разными способами, например, так:

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

Пример 5: Найти последнюю колонку и столбец, заполненные данными

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

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

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

Для поиска функцией Find по маске (шаблону) можно применять символы: * — для обозначения любого количества любых символов; ? — для обозначения одного любого символа;

— для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

• Тип данных Date в VBA представляется в виде #//#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
• В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

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

Пример 8: Найти 1 марта 2018 г.

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ

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

Функция ВПР()

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; ). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

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

Когда ключевое поле находится правее данных, которые вы хотите получить, ВПР не поможет. Если, конечно, была бы возможность задать номер_столбца -1, тогда проблем бы не было. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнить табельные номера с помощью ВПР, сохранить их как значения и удалить временную колонку A.

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

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

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; ).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

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

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; ).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

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

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

Функция ИНДЕКС() в MS EXCEL

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

Синтаксис функции

​сами значения​​MonthList​

​Введите номер строки, и​​column_num​COLUMNS​

​ и т.д. Но,​​ целой строки (не​ с другими операторами.​В поле​ с​ Отдельно у нас​указываем значение​

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

​ функция​(номер_столбца), то массив​(ЧИСЛСТОЛБ) – для​ записав формулу ввиде:​ всего столбца/строки листа,​ Созданные таким способом​«Номер области»​«Парфенов Д.Ф.»​

​ имеется два дополнительных​«3»​или​ любой один из​ с целью лучше​ 1.​ определенных условиях.​значение1;значение2; … могут быть числами,​В финальном примере функция​INDEX​​ всех значений этого​​ подсчета суммы последнего​

Значение из заданной строки диапазона

​=СУММ(A2:ИНДЕКС(A2:A10;4))​​ а только столбца/строки​

​ формулы смогут решать​ставим цифру​, на, например,​ поля –​, так как нужно​

​«Ссылка»​ них, если массив​ усвоить материал.​​СТОЛБЕЦ($A$2:$D$9)​​Необходимо выполнить поиск значения​ текстовыми строками и​INDEX​(ИНДЕКС) возвратит сумму​

Значение из заданной строки и столбца таблицы

​ столбца.​ столбца в именованном​​получим универсальное решение, в​

​ входящего в массив).​ самые сложные задачи.​«3»​«Попова М. Д.»​«Имя»​ узнать данные из​. Нужный нам вариант​

Использование функции в формулах массива

​и​​ третьей строки. Поле​​«Массив»​ диапазоне следует применять​ (не табличный) подход.​​ время параллельно, в​​ отобразить заголовок столбца​ ячеек. Если в​​ сочетании с несколькими​​ строки. Здесь указан​row_num​вместе с​​ только последний аргумент​​ значений, введите функцию​

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

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

​ в третьей таблице,​ платы в поле​. Нужно сделать так,​

​вообще можно оставить​ и по умолчанию​

ПОИСКПОЗ() + ИНДЕКС()

​ также учесть, что​ раз, когда необходимо​ другой массив значений​ Готовый результат должен​ конкретные значения, то​ возвратить список месяцев,​4​ то требуется указать​

​(ПОИСКПОЗ) – для​ выше вместо 4​ массива.​ из диапазона ячеек​ в которой содержится​​«Сумма»​

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

Ссылочная форма

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

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

​ будет подсчитана сумма​

​А6:А9.​ и столбца. Например,​ плате за третий​Теперь посмотрим, как с​ отображалась сумма заработанных​ диапазон, в котором​ на кнопку​ не номер на​

​ ум приходит пара​ нашей исходной таблицы),​ как в Excelе​ в зависимости от​COUNTIF​ апрель (Apr).​Если аргумент​Итак, давайте обратимся к​ первых 5-и значений). ​

​Выведем 3 первых​ формула =ИНДЕКС(A9:A12;2) вернет​

​ месяц.​

​ помощью оператора​ им денег. Посмотрим,​

​ используется только один​«OK»​ координатах листа, а​

​ функций: ИНДЕКС и​ содержащая номера столбцов​ можно добиться одинакового​ индекса, а если​(СЧЁТЕСЛИ) подсчитывает, как​

​=INDEX($C$2:$C$8,F2)​column_num​ теоретическим сведениям и​Использование функции ИНДЕКС() в этом​​ значения из этого​​ значение из ячейки​После того, как все​ИНДЕКС​ как это можно​ столбец. Жмем на​.​ порядок внутри самого​ ПОИСКПОЗ. Результат поиска​ для каждой из​

excel2.ru>

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА). Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

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

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения):

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

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10). Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

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

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

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

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать Officelegko.com! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!

Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

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

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решения нажмите кнопку Подстановка .

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

Загрузка программы-надстройки «Мастер подстановок»

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

Excel MATCH функция

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

аргументы

Lookup_value (необходимые): Конкретное значение, которое вы хотите сопоставить в массиве look_up; Этот аргумент может быть числом, текстом, логическим значением или ссылкой ячейки на значение (число, текст или логическое значение).

Lookup_array (необходимые): Диапазон ячеек, содержащий искомое значение.

Match_type (необязательный): Тип соответствия, которое будет выполнять функция. Он состоит из 3-х видов:

• — Находит первое значение, точно соответствующее lookup_value
• 1 — или опущено. Если точное значение соответствия не может быть найдено, Match найдет наибольшее значение, которое меньше или равно значению look_up.Значения в аргументе массива look_up должны быть в порядке возрастания..
• -1 — Находит наименьшее значение, которое больше или равно значению look_up.Значения в аргументе массива look_up должны быть в порядке убывания..

Примечания к функциям

1. Функция ПОИСКПОЗ не чувствительна к регистру. 2. Функция Match вернет значение ошибки # N / A, если совпадение не будет найдено. 3. Функция ПОИСКПОЗ позволяет использовать символы подстановки в аргументе lookup_value для приблизительного совпадения.

Примеры

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

Пожалуйста, сделайте следующее, чтобы вернуть позицию Чарльза Монагана в диапазоне B3: B7.

Выберите пустую ячейку, введите в нее формулу ниже и нажмите клавишу Enter, чтобы получить результат.=MATCH(D4,B3:B7,0)

Внимание: В формуле D4 содержит поисковое значение; B3: B7 это диапазон ячеек, содержащий искомое значение; количество означает, что вы ищете точное значение соответствия

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

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

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

Выберите пустую ячейку, введите в нее приведенную ниже формулу и нажмите клавишу Enter, чтобы получить результат.=MATCH(D4,B3:B7,1)

Затем он вернет позицию числа 2000, которое является наибольшим значением, меньшим 2500 в списке.

Примечание. В этом случае все значения в списке B3: B7 должны быть отсортированы в порядке возрастания, в противном случае будет возвращена ошибка # N / A. Чтобы вернуть позицию наименьшего значения (говорит 3000), которое больше 2500 в списке, примените эту формулу:=MATCH(D4,B3:B7,-1)

Внимание: Все значения в списке B3: B7 должны быть отсортированы по убыванию в случае возврата ошибки # N / A

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

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

Как показано на скриншоте ниже, для получения позиции значения, которое начинается с «привет», сделайте следующее.

Выберите пустую ячейку, введите в нее формулу ниже (вы можете заменить D4 непосредственно на «привет *») и нажмите клавишу Enter. Он вернет позицию первого значения совпадения.=MATCH(D4,B3:B7,0)

Чаевые: Функция ПОИСКПОЗ не чувствительна к регистру.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ИНДЕКС($C$2:$C$11;ПОИСКПОЗ(«Россия»;$B$2:$B$11;0))

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

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

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

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

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