Функции ИНДЕКС и ПОИСКПОЗ в Excel и примеры их использования
В Excel есть очень удобная, но почему-то редко используемая функция, которая называется ИНДЕКС. Удобная она потому, что позволяет выдавать значение из диапазона по заданным номерам строки и столбца.
На практике ИНДЕКС редко используется, скорее всего, из-за того, что эти самые номер строки и столбца приходится вводить каждый раз. Ведь искомое значение не всегда нужно выдавать по порядку. Но тогда к функции ИНДЕКС на помощь приходит функция ПОИСКПОЗ, которая как раз таки позволяет найти нужную позицию.
Пример использования функций ИНДЕКС и ПОИСКПОЗ
Рассмотрим интересный пример, который позволит понять прелесть функции ИНДЕКС и неоценимую помощь ПОИСКПОЗ. Имеем сводную таблицу, в которой ведется учет купленной продукции.
Наша цель: создать карточку заказа, где по номеру артикула можно будет видеть, что это за товар, какой клиент его приобрел, сколько было куплено и по какой общей стоимости. Сделать это поможет функция ИНДЕКС совместно с ПОИСКПОЗ.
Для начала создадим выпадающий список для поля АРТИКУЛ ТОВАРА, чтобы не вводить цифры с клавиатуры, а выбирать их. Для этого кликаем в соответствующую ячейку (у нас это F13), затем выбираем вкладку ДАННЫЕ – ПРОВЕРКА ДАННЫХ. В открывшемся окне в пункте ТИП ДАННЫХ выбираем СПИСОК. А в качестве источника выделяем столбец с артикулами, включая шапку. Так у нас получился выпадающий список артикулов, которые мы можем выбирать.
Теперь нужно сделать так, чтобы при выборе артикула автоматически выдавались значения в остальных четырех строках. Воспользуемся функцией ИНДЕКС. Записываем ее и параллельно изучаем синтаксис.
Массив. В данном случае это вся таблица заказов. Выделяем ее вместе с шапкой и фиксируем клавишей F4.
Номер строки. Если бы у нас требовалось вывести одно значение, мы бы написали какую-то конкретную цифру. Но раз нам нужно, чтобы результат менялся, воспользуемся функцией ПОИСКПОЗ. Она будет искать необходимую позицию каждый раз, когда мы будем менять артикул.
Записываем команду ПОИСКПОЗ и проставляем ее аргументы.
Искомое значение. В нашем случае это ячейка, в которой указывается артикул, т.е. F13. Фиксируем ее клавишей F4.
Просматриваемый массив. Т.к. мы ищем по артикулу, значит, выделяем столбец артикулов вместе с шапкой. Фиксируем F4.
Тип сопоставления. Excel предлагает три типа сопоставления: больше, меньше и точное совпадение. У нас конкретный артикул, поэтому выбираем точное совпадение. В программе оно значится как 0 (ноль). На этом аргументы ПОИСКПОЗ закончились.
Номер столбца. Опять же воспользуемся ПОИСКПОЗ. Искомым значением будет ячейка E14, где указано наименование параметра, который мы ищем (ТОВАР). Просматриваемый массив: шапка с наименованиями, потому что искать система будет по слову ТОВАР. Тип сопоставления: 0.
Синтаксис функции ИНДЕКС закончен. Как в итоге выглядит формула, видно на скриншоте выше. Видим, что артикул 3516 действительно у арахиса. Протянем формулу на остальные строки и проверим. Теперь, меняя артикул товара, мы будем видеть, кто его купил, сколько и почем.
Поиск индекса максимального числа массива в Excel
Функция ИНДЕКС также помогает выделить из массива максимальное число. Рассмотрим тот же самый пример. Попробуем определить максимальные значения купленного количества товара, цены и суммы.
Начнем с количества. В любой ячейке под этим столбцом пишем =ИНДЕКС.
Первым аргументом у нас будет не просто массив, а максимальное число из массива. Поэтому дополнительно используем команду МАКС и выделяем соответствующий массив.
В принципе, нам больше не нужны никакие аргументы, но требуется ввести номер строки и столбца. В таком случае напишем два нуля.
Получили простейшую формулу, помогающую вывести максимальное значение из массива. Протянем ее вправо, получив аналогичную информацию по цене и сумме.
ИНДЕКС (функция ИНДЕКС)
Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.
Функцию ИНДЕКС можно использовать двумя способами:
Если вы хотите возвращать значение указанной ячейки или массива ячеек, см. раздел Форма массива.
Если требуется возвращать ссылку на указанные ячейки, см. раздел Ссылочная форма.
Форма массива
Описание
Возвращает значение элемента таблицы или массива, выбранного индексами номеров строк и столбцов.
Если первый аргумент функции ИНДЕКС является константной массива, используйте форму массива.
Синтаксис
ИНДЕКС(массив; номер_строки; [номер_столбца])
Аргументы формы массива функции ИНДЕКС следующую:
Массива — обязательный аргумент. Диапазон ячеек или константа массива.
Если массив содержит только одну строку или один столбец, row_num или column_num аргумент не является обязательным.
Если массив имеет несколько строк и несколько столбцов и используется только row_num или column_num, индекс возвращает массив всей строки или столбца в массиве.
Номер_строки Обязательно, если column_num нет. Выбирает строку в массиве, из которой требуется возвратить значение. Если row_num опущен, column_num требуется.
Номер_столбца — необязательный аргумент. Выбирает столбец в массиве, из которого требуется возвратить значение. Если column_num опущен, row_num требуется.
Замечания
Если используются row_num и column_num, индекс возвращает значение ячейки на пересечении row_num и column_num.
row_num и column_num должны указать на ячейку в массиве; в противном случае индекс возвращает #REF! ошибку «#ВЫЧИС!».
Примеры
Пример 1
В этих примерах функция ИНДЕКС используется для поиска значения ячейки, находящейся на пересечении заданных строки и столбца.
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.
Значение ячейки на пересечении второй строки и второго столбца в диапазоне A2:B3.
Значение ячейки на пересечении второй строки и первого столбца в диапазоне A2:B3.
Пример 2
В этом примере функция ИНДЕКС используется в формуле массива для поиска значений двух заданных ячеек в массиве с диапазоном 2 x 2.
Значение ячейки на пересечении первой строки и второго столбца в массиве. Массив содержит значения 1 и 2 в первой строке и значения 3 и 4 во второй строке.
Значение ячейки на пересечении второй строки и второго столбца в массиве, указанном выше.
Форма справки
Описание
Возвращает ссылку на ячейку, расположенную на пересечении указанной строки и указанного столбца. Если ссылка состоит из несмежных выборок, можно выбрать выбор, который нужно найти.
Синтаксис
ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области])
Аргументы функции ИНДЕКС могут быть следующими:
Ссылки — обязательный аргумент. Ссылка на один или несколько диапазонов ячеек.
Если для ссылки ввести несмежный диапазон, заключите ссылку в скобки.
Если каждая область в ссылке содержит только одну строку или столбец, row_num или column_num аргумент соответственно является необязательным. Например, для ссылки на единственную строку нужно использовать формулу ИНДЕКС(ссылка,,номер_столбца).
Номер_строки — обязательный аргумент. Номер строки в диапазоне, заданном аргументом «ссылка», из которого требуется возвратить ссылку.
Номер_столбца — необязательный аргумент. Номер столбца в диапазоне, заданном аргументом «ссылка», из которого требуется возвратить ссылку.
area_num Необязательный. Выбирает диапазон в ссылке, из которого будут возвращаться пересечения row_num и column_num. Первая выбранная или введенная область имеет номер 1, вторая — 2 и так далее. Если area_num опущен, в индексе используется область 1. Перечисленные здесь области должны быть расположены на одном листе. Если указать области, которые не находятся на одном листе друг с другом, это приведет к #VALUE! ошибку «#ВЫЧИС!». Если вам нужно использовать диапазоны, расположенные на разных листах друг от друга, рекомендуется использовать форму массива функции ИНДЕКС и использовать другую функцию для вычисления диапазона, который составляет массив. Например, с помощью функции ВЫБОР можно вычислить диапазон, который будет использоваться.
Например, если в справочнике описаны ячейки (A1:B4;D1:E4,G1:H4), area_num 1 — диапазон A1:B4, area_num 2 — диапазон D1:E4, а area_num 3 — диапазон G1:H4.
Замечания
После ссылки и area_num вы выбрали определенный диапазон, row_num и column_num выберем определенную ячейку: row_num 1 — первую строку диапазона, column_num 1 — первый столбец и так далее. Ссылка, возвращаемая индексом, является пересечением row_num и column_num.
Если для row_num или column_num 0, индекс возвращает ссылку на весь столбец или строку соответственно.
row_num, column_num и area_num должны указать на ячейку в ссылке; в противном случае индекс возвращает #REF! ошибку «#ВЫЧИС!». Если row_num и column_num опущены, индекс возвращает область в ссылке, указанной area_num.
Результатом вычисления функции ИНДЕКС является ссылка, которая интерпретируется в качестве таковой другими функциями. В зависимости от формулы значение, возвращаемое функцией ИНДЕКС, может использоваться как ссылка или как значение. Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2)) эквивалентна формуле ЯЧЕЙКА(«ширина»;B1). Функция ЯЧЕЙКА использует значение, возвращаемое функцией ИНДЕКС, как ссылку. С другой стороны, такая формула, как 2*ИНДЕКС(A1:B2;1;2), преобразует значение, возвращаемое функцией ИНДЕКС, в число в ячейке B1.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД.
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «кузьмина» или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).
Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах
Совместное использование функций ИНДЕКС и ПОИСКПОЗ в Excel – хорошая альтернатива ВПР, ГПР и ПРОСМОТР. Эта связка универсальна и обладает всеми возможностями этих функций. А в некоторых случаях, например, при двумерном поиске данных на листе, окажется просто незаменимой. В данном уроке мы последовательно разберем функции ПОИСКПОЗ и ИНДЕКС, а затем рассмотрим пример их совместного использования в Excel.
Более подробно о функциях ВПР и ПРОСМОТР.
Функция ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.
Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.
В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.
Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:
В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.
Функция ИНДЕКС в Excel
Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.
Стоит отметить, что номера строк и столбцов задаются относительно верхней левой ячейки диапазона. Например, если ту же таблицу расположить в другом диапазоне, то формула вернет тот же результат:
Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.
Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):
Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):
Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel
Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве. Но иногда приходится сталкиваться с двумерным поиском, когда соответствия требуется искать сразу по двум параметрам. Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой.
На рисунке ниже представлена таблица, которая содержит месячные объемы продаж каждого из четырех видов товара. Наша задача, указав требуемый месяц и тип товара, получить объем продаж.
Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:
=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))
Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:
В данной формуле функция ИНДЕКС принимает все 3 аргумента:
Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:
=ИНДЕКС(B2:E13;D15;D16)
Как видите, все достаточно просто!
На этой прекрасной ноте мы закончим. В этом уроке Вы познакомились еще с двумя полезными функциями Microsoft Excel – ПОИСКПОЗ и ИНДЕКС, разобрали возможности на простых примерах, а также посмотрели их совместное использование. Надеюсь, что данный урок Вам пригодился. Оставайтесь с нами и успехов в изучении Excel.
Примеры функций ИНДЕКС и ПОИСКПОЗ по нескольким критериям Excel
Одним из основных способов поиска данных в таблицах Excel является функция ВПР, однако она имеет массу недостатков, и зачастую пользователи испытывают сложности при ее использовании. Связка функций ИНДЕКС и ПОИСКПОЗ открывает более широкие возможности для поиска данных в одной и даже нескольких таблицах сразу, на что неспособна ВПР.
Примеры использования функций ИНДЕКС и ПОИСКПОЗ по двум критериям в Excel
Пример 1. В турнирной таблице хранятся данные о сыгранных футбольных матчах для нескольких команд. Определить:
Вид исходной таблицы данных:
Для удобства в ячейках A11, A13 и A15 созданы выпадающие списки, элементы которых выбраны из диапазонов ячеек B1:E1 (для A11) и A2:A9 (для A13 и A15), содержащих названия команд. Для создания первого выпадающего списка необходимой перейти курсором на ячейку A11. Выбрать вкладку «ДАННЫЕ» ленты меню, найти секцию с инструментами «Работа с данными» и выбрать инструмент «Проверка данных»:
Переходим в ячейку A13 и выполняем аналогичные действия только лишь указываем другую ссылку на диапазон в поле «Источник:»
Такой же выпадающий список следует создать и для ячейки A15.
Для подсчета общего количества очков в ячейке B11 используем формулу:
Для получения корректного результата выражение должно быть выполнено как формула массива. Функция СУММ получает массив ячеек в виде столбца таблицы, номер которого был определен функцией ПОИСКПОЗ по критерию поиска «Очки» (наименование столбца). Поскольку в качестве аргумента номер_строки функции ИНДЕКС было передано значение 0, будет возвращен весь столбец.
Количество сыгранных игр для каждой команды может быть рассчитано как сумма выигранных, сыгранных вничью и проигранных игр. Используем следующую формулу:
Данная формула аналогична предыдущей и также должна использоваться в качестве формулы массива. Благодаря списку, привязанному к ячейке A13, можно легко определить число сыгранных игр для любой команды:
Для определения количества очков используем формулу ИНДЕКС, в которой оба аргумента, указывающие номер строки и столбца, будут принимать значения, возвращаемые функцией ПОИСКПОЗ:
В результате мы получили значение по 2-м критериям:
Динамическое суммирование диапазона ячеек по критерию в Excel
Пример 2. Используя таблицу из предыдущего примера определить суммарное количество заработанных очков несколькими командами (задается опционально).
Вид таблицы данных:
Искомое значение может быть найдено с помощью следующей формулы:
Функция СУММ рассчитывает сумму значений, хранящихся в столбце «Очки», при этом количество ячеек для расчета может быть задано с помощью критерия – выбранного названия команды. Функция ИНДЕКС может возвращать не только значение, хранящееся в искомой ячейке, но и ссылку на эту ячейку. Поэтому можно использовать запись типа E2:ИНДЕКС(…). В результате выполнения функция ИНДЕКС вернет ссылку на ячейку, и приведенная выше запись примет, например, следующий вид: E2:E4 (если выбрана команда «Манчестер Ю.».
Проверим результат выборочного динамического суммирования столбца таблицы с верху вниз.
Сумма чисел в диапазоне E2:E7 и в ячейке B13 совпадает все ОК.
Подсчет количества рабочих дней в Excel по условию начальной даты
Пример 3. В таблице табеля рабочего времени хранятся данные о недавно принятых сотрудниках фирмы. Определить, сколько рабочих дней на текущий момент отработал любой из новых недавно принятых сотрудников фирмы.
Вид таблицы данных:
Как видно на рисунке в ячейке A10 снова используется выпадающий список, созданный по аналогичной схеме, описанной выше.
Для определения искомого значения даты используем следующую формулу (формула массива CTRL+SHIFT+ENTER):
Примеры определения дат для нескольких сотрудников:
Для автоматического подсчета количества только рабочих дней начиная от даты приема сотрудника на работу, будем использовать функцию ЧИСТРАБДНИ:
Для проверки выберем другую фамилию сотрудника из выпадающего списка в ячейке A9:
Особенности работы с формулами ИНДЕКС И ПОИСКПОЗ в Excel
Функция ИНДЕКС может возвращать ссылку или массив значений из одного диапазона или нескольких несмежных диапазонов, принимая на вход ссылку на области ячеек или константу массива. При этом последующие аргументы позволяют указать номера интересующих строки и столбца относительно выбранного диапазона, а также порядковый номер диапазона (если диапазоны ячеек не являются смежными, например, при поиске в различных таблицах). В простейшем случае функция ИНДЕКС возвращает значение, хранящееся в ячейке на пересечении строки и столбца. Например, =ИНДЕКС(A2:B5;2;2) вернет значение, которое хранится в ячейке B3, поскольку третья строка является второй по счету относительно ячейки A2, а столбец B:B является вторым относительно столбца A:A.
При необходимости можно получить целую строку или целый столбец, указав в качестве номера строки и столбца соответственно значение 0 (нуль). Для вывода полученных строки или столбца функцию ИНДЕКС необходимо использовать в качестве формулы массива.
Функция ПОИСКПОЗ используется для поиска указанного в качестве первого аргумента значения в диапазоне ячеек или константе массива. Она возвращает относительную позицию найденного элемента или код ошибки #Н/Д, если искомые данные отсутствуют. При поиске числовых значений можно использовать нежесткие критерии: ближайшее наибольшее или ближайшее наименьшее числа заданному.
Поскольку ПОИСКПОЗ возвращает относительную позицию элемента в диапазоне, то есть, номер строки или столбца, эта функция может быть использована как один или сразу два аргумента функции:
=ИНДЕКС(диапазон; ПОИСКПОЗ(аргументы); ПОИСКПОЗ(аргументы))
Такая формула используется чаще всего для поиска сразу по двум критериям.
