Функции ИНДЕКС и ПОИСКПОЗ в Excel на обычных примерах

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

Наиболее тщательно о функциях ВПР и ПРОСМОТР.

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

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

К примеру, на рисунке ниже формула возвратит число 5, так как имя “Дарья” находится в пятой строке спектра A1:A9.

В последующем примере формула возвратит 3, так как число 300 находится в 3-ем столбце спектра B1:I1.

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

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

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

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

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

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

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

К примеру, последующая формула возвращает 5-ое значение из спектра A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из спектра A1:L1(горизонтальный вектор):

Совместное внедрение ПОИСКПОЗ и ИНДЕКС в Excel

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

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

Пускай ячейка C15 содержит обозначенный нами месяц, к примеру, Май. А ячейка C16 – тип продукта, к примеру, Овощи. Введем в ячейку C17 последующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

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

В данной формуле функция ИНДЕКС воспринимает все 3 аргумента:

1-ый аргумент – это спектр B2:E13, в котором мы осуществляем поиск.
Вторым аргументом функции ИНДЕКС является номер строчки. Номер мы получаем при помощи функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что все-таки возвращает нам данная формула:
Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем при помощи функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

Интересно почитать: Не функция в excel

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

=ИНДЕКС(B2:E13;D15;D16)

Видите ли, все довольно просто!

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

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

Может быть, вы понимаете, что сможете сочетать что собой представляет ИНДЕКС и МАТЧ функции либо используйте ВПР функция для поиска значений в Excel. Но при поиске регистр не учитывается. Итак, чтоб выполнить сравнение с учетом регистра, вы должны пользоваться EXACT a nd ВЫБЕРИТЕ функции.

Сделайте поиск с учетом регистра при помощи ИНДЕКС и ПОИСКПОЗ

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

Общий синтаксис

=INDEX( return_range ,MATCH(TRUE,EXACT( lookup_value , lookup_range ),0))

√ Примечание. Это формула массива, требующая ввода при помощи Ctrl + Shift + Enter.

return_range: Спектр, из которого вы желаете, чтоб комбинационная формула возвращала значение. Тут имеется в виду модельный ряд.
lookup_value: Значение EXACT, применяемое для выполнения чувствительного к регистру сопоставления с текстовыми строчками в lookup_range. Тут имеется в виду данное имя, ЮКИ.
lookup_range: Спектр ячеек для сопоставления с искомое_значение. Тут имеется в виду спектр имен.
match_type 0: ПОИСКПОЗ отыщет 1-ое значение, которое буквально равно искомое_значение.

Чтоб знать класс of YUKI, скопируйте либо введите формулу ниже в ячейку G6, и нажмите Ctrl + Shift + Enter чтоб получить итог:

= ИНДЕКС ( D5: D14 , ПОИСКПОЗ (ИСТИНА, ТОЧНО ( «ЮКИ» , B5: B14 ), 0))

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

= ИНДЕКС ( D5: D14 , ПОИСКПОЗ (ИСТИНА, ТОЧНО ( G5 , B5: B14 ), 0))

Пояснение формулы

=INDEX(D5:D14,MATCH(TRUE,EXACT(«YUKI»,B5:B14),0))

ТОЧНЫЙ (G5, B5: B14): Функция EXACT ассоциирует текстовые строчки в спектре имен учащихся. B5: B14 против «YUKI«, значение в ячейке G5, возвращает ИСТИНА, если значение в ячейках от B5 до B14 буквально такое же, как YUKI, в неприятном случае — ЛОЖЬ. Итак, мы получим массив ИСТИНЫ и ЛОЖИ вроде этого:
ПОИСКПОЗ (ИСТИНА; ТОЧНО (G5; B5: B14); 0) = ПОИСКПОЗ (ИСТИНА; <ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА>; 0): Match_type 0 просит функцию ПОИСКПОЗ отыскать четкое совпадение. Потом функция возвратит позицию собственного четкого lookup_value “TURE”В массиве, который 10, потому что единственное ИСТИНА находится на 10-я позиция в массиве.
INDEX(D5:D14,MATCH(TRUE,EXACT(G5,B5:B14),0)) = INDEX(D5:D14,10): Функция ИНДЕКС возвращает 10th значение в спектре оценок D5: D14, Которая является A.

Сделайте поиск с учетом регистра при помощи ВПР

Чтоб знать страна что ДЖИММИ исходит из функции VLOOKUP, для вас следует пользоваться функциями CHOOSE и EXACT для выполнения поиска с учетом регистра. Функция EXACT будет проводить сопоставление ДЖИММИ и каждой текстовой строчки в спектре имен учащихся с учетом регистра. Потом мы можем применять ВЫБОР, чтоб соединить ТОЧНЫЙ итог и значения в спектре государств. Настало время VLOOKUP показать свои способности.

Общий синтаксис

=VLOOKUP(TRUE,CHOOSE(<1,2>,EXACT( lookup_value , lookup_range ), return_range ),2,FALSE)

√ Примечание. Это формула массива, требующая ввода при помощи Ctrl + Shift + Enter.

lookup_value: Значение EXACT, применяемое для выполнения чувствительного к регистру сопоставления с текстовыми строчками в lookup_range. Тут имеется в виду данное имя, ДЖИММИ.
lookup_range: Спектр ячеек для сопоставления с искомое_значение. Тут имеется в виду спектр имен.
return_range: Спектр, из которого вы желаете, чтоб комбинационная формула возвращала значение. Тут имеется в виду спектр страны.
range_lookup ЛОЖЬ: Функция ВПР будет находить четкое совпадение.

Чтоб знать страна ДЖИММИ происходит из, скопируйте либо введите формулу ниже в ячейку G9, и нажмите Ctrl + Shift + Enter чтоб получить итог:

= ВПР (ИСТИНА; ВЫБРАТЬ (<1,2>; ТОЧНО ( «ДЖИММИ» , B5: B14 ), C5: C14 ), 2, ЛОЖЬ)

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

= ВПР (ИСТИНА; ВЫБРАТЬ (<1,2>; ТОЧНО ( G8 , B5: B14 ), C5: C14 ), 2, ЛОЖЬ)

Пояснение формулы

=VLOOKUP(TRUE,CHOOSE(<1,2>,EXACT( G8 , B5:B14 ), C5:C14 ),2,FALSE)

ТОЧНЫЙ( G8 , B5: B14 ): Функция EXACT ассоциирует текстовые строчки в спектре имен учащихся. B5: B14 против значения в ячейке G8, который «ДЖИММИ«, возвращает ИСТИНА, если значение в спектре имен учащихся буквально такое же, как ДЖИММИ, в неприятном случае — ЛОЖЬ. Таковым образом, мы получим массив ИСТИНА и ЛОЖЬ, схожий этому:
ВЫБЕРИТЕ (<1,2>, ТОЧНО ( G8 , B5: B14 ) = ВЫБРАТЬ (<1,2>, <ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ>, C5: C14): Аргумент index_num команды CHOOSE 1,2 <> соединяет воединыжды ТОЧНЫЙ массив и значения из C5: C14 в двумерный массив вроде этого:
ВПР (ИСТИНА, ВЫБРАТЬ (<1,2>, ТОЧНО ( G8 , B5: B14 ), C5: C14 ), 2, ЛОЖЬ) = ВПР (ИСТИНА, <ЛОЖЬ, «Китай»; ЛОЖЬ, «Англия»; ЛОЖЬ, «Америка»; ЛОЖЬ, «Индия»; ИСТИНА, «Америка»; ЛОЖЬ, «Индия»; ЛОЖЬ, «Америка»; ЛОЖЬ, ») Китай "; ЛОЖЬ," Англия "; ЛОЖЬ," Китай ">, 2, ЛОЖЬ): Range_lookup НЕПРАВДА запрашивает функцию ВПР для поиска четкого значения «ИСТИНА”В первом столбце двумерного массива и возвращает его четкое совпадение в 2ый столбец, который Америка.

Связанные функции

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

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

Функция ВПР в Excel делает поиск значения по первому столбцу таблицы и возвращает соответственное значение из определенного столбца в той же строке.

Связанные формулы

В этом руководстве вы узнаете, как применять формулу с функциями СУММПРОИЗВ, ЕЧИСЛО и НАЙТИ для подсчета ячеек, содержащих определенный текст, с учетом верхнего и нижнего регистра.

Наилучшие инструменты для работы в кабинете

Kutools for Excel — поможет для вас выделиться из толпы

Желаете стремительно и идеально делать свою ежедневную работу? Kutools for Excel дает массивные расширенные функции 300 (объединение книжек, сумма по цвету, разделение содержимого ячеек, дата преобразования и так дальше . ) и экономия 80% времени вам.

Поиск в Эксель с внедрение функции «ПОИСКПОЗ» и «ИНДЕКС»

Юзерам пакета офисных программ «MS Office» приходится сталкиваться с трудностями в осознании функций программ. В особенности это касается способностей, представленных в «Microsoft Excel». Одной из их является функция «ПОИСКПОЗ».

Что же это все-таки за функция, для чего употребляется

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

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

Индивидуальности функции

Функция поиска имеет ряд особенностей:

при использовании происходит возврат лишь относительного положения искомого значения;
производит поиск вне зависимости от регистра;
при наличии нескольких совпадений в массиве, ворачивается только 1-ое попавшееся;
при отсутствии совпадений ворачивается ошибка «#Н/З».

Синтаксис формулы

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

Сама формула смотрится так: ПОИСКПОЗ (Значение_для_поиска; ПромежутокЗначений; Сравнение).

Расшифруем ее характеристики:

«Значение_для_поиска» — разыскиваемый аргумент (число, слово, знак и т.п.).
«ПромежутокЗначений» — массив из ячеек, в которых происходит поиск.
«Сравнение» — задает соответствие, по которому будет происходить поиск:
- «1»– поиск происходит до большего. Массив при всем этом, должен быть упорядочен по возрастанию;
- «0»– для четкого поиска;
- «-1»– обратно «1», проводит поиск меньшего. Массив должен быть упорядочен по убыванию.

ПОИСКПОЗ в Excel в примерах

По одному аспекту

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

Для поиска значений в столбце возьмем, например ПОИСКПОЗ («Дарья»; A1:A9; 1). При подстановке фразы расположенной в B1, и поиске в промежутке A1:A9, в пространство, где употребляется формула, будет возвращено его положение. Разыскиваемое слово находится в пятой ячейке, потому возвращено «5».

В этом случае формула =ПОИСКПОЗ(А3;В1:I1). Так как число 300 находится в 3-ем столбце, будет возвращена «3».

По двум столбцам

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

Наименования авто и отделов дублируются в массиве, но не попарно. Отыщем 1-ый попавшийся Форд:

Для того, чтоб конкретизировать запрос и отыскать авто из другого отдела необходимо в ячейке B16 ввести главный аспект, а в C16 ввести аспект, которые сузивает область, а в формуле, в «разыскиваемое значение» вписать номера ячеек c входными данными вот так: «B16&C16». Такие же манипуляции провести с массивом, в котором происходит поиск. Другими словами, дописать к основному «B2:B13» промежутку символ «&» и ввести 2-ой просвет «C2:C13».

Удерживая «CTRL+SHIFT+ENTER» произвести операцию.

Текстовые значения

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

Числа

С числами все просто. Ввести число в “Разыскиваемое значение”, потом указать просвет и запустить выполнение. Зависимо от типа сравнения программка выдаст итог.

Подстановочные знаки

Посреди остального, есть способности определения символов. Происходит это вот так:

«?» – является подменой для хоть какого знака.
«*» – подмена хоть какой последовательности знаков.

Работает подстановка лишь в случае, если тип сравнения равен «0».

К примеру, если необходимо отыскать «Восток», в строке можно ввести «вост?к» и программка автоматом отыщет совпадения и выдаст итог.

Для подмены нескольких знаков следует вводить в поле сочетание «В*к». Но в этом случае будут найдены все слова, которые начинаются на «В» и завершаются на «К».

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

Потому что формула не различает конфигурации в регистре, следует внести некие конфигурации:

ПОИСКПОЗ (ИСТИНА; СОВПАД (ИскомоеЗначение;Просматриваемый_Массив); 0)

Сущность формулы в том, что «СОВПАД» дозволяет отыскать четкие совпадения в таблице. Если отысканный элемент в таблице соответствует аспектам – его размещение ворачивается в качестве результата. Для обработки требуется надавить комбинацию «Ctrl+Shift+Enter».

Нужные данные размещены в E1, просвет, в котором его необходимо отыскать — A2:A8.

Совмещение с ИНДЕКС

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

Формула смотрится вот так:

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

К примеру, имеется таблица населения нескольких государств:

Найдем население Нигерии с внедрением формулы:

=ИНДЕКС (промежуток1; ПОИСКПОЗ («ИскомоеЗначение»; ПромежутокЗначений2; 0)), где:

«промежуток1» — столбец с количеством населения (C2:C11);
«промежуток2» — столбец с заглавием страны (B2:B11).

Дальше, ПОИСКПОЗ проводит поиск «Разыскиваемое значение» (Нигерия) посреди тех, что размещены в ячейках B2:B11. Будет возвращено «8», ведь в перечне Нигерия находится на восьмом месте.