Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Совет: Попробуйте использовать новые функции ПРОСМОТР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
Ниже на рисунке представлены города и области, в которых находятся магазины сети. Допустим, что после выбора пользователем названия области с выпадающего списка в ячейке G1 должны автоматически заполнится поля «Город» G2 и «№ магазина» G3:
Функция ИНДЕКС возвращает значение ячейки, находящееся в определенной строке и столбце указанного диапазона ячеек. В данном случае в аргументе функции как диапазон ячеек указана исходная таблица магазинов. Во втором аргументе указывается сначала номер строки, а в третьем – номер столбца. В формуле для поиска города значения считываются с первого столбца, поэтому последний аргумент содержит число 1.
Формула для поиска номера магазина по названию города возвращает значения из четвертого столбца:
Поэтому последний аргумент в функции ВПР равен числу 4.
Если диапазон ячеек не начинается с ячейки A1 аргументы, определяющие строку и столбец, не соответствуют номерам строк и столбцов рабочего листа Excel. Отсчет номеров производится, начиная от ячейки левого верхнего угла указанного диапазона (в данном примере это A2) в первом аргументе функции ИНДЕКС. Например, формула =ИНДЕКС(G2:P10;2;2) возвращает значение ячейки H3, которая находится во второй строке второго столбца диапазона G2:P10.
Во втором аргументе функции ИНДЕКС содержится функция ПОИСКПОЗ. В ее аргументах всегда указывается диапазон ячеек, которые содержат только одну строку или один столбец. Если указать диапазон из более чем одной строки или столбца, тогда функция ПОИСКПОЗ возвращает ошибку #Н/Д!
Чтобы получить номер соответствующей строки для функции ИНДЕКС необходимо использовать функцию ПОИСКПОЗ, которая возвращает позицию найденного значения в таблице. Функция ПОИСКПОЗ использует 3 аргумента в своем синтаксисе:
В данном примере искомым значением выступает название области указана в ячейке G1. Данное значение формула ищет в списке областей диапазона C2:C11. Функция ПОИСКПОЗ поочередно проверяет все ячейки, пока не найдет строку «Свердловская», находящееся на 5-ой позиции. После чего данная функция возвращает значение 5, которое будет использовано как второй аргумент с номером строки для функции ИНДЕКС.
После возвращения результата через функцию ПОИСКПОЗ функция ИНДЕКС будет иметь все необходимые данные для отображения соответственного значения. Перейдет на 5-тую строку диапазона ячеек и выберет значение из первого столбца «Город» или из четвертого «№ магазина».
Внимание! Если в аргументах функции ИНДЕКС указать номер строки больше чем количество строк в диапазоне ячеек или номер столбца больше чем количество столбцов, тогда функция возвращает ошибку #ССЫЛКА!
Как сделать выборку из списка формулой ПРОСМОТР в Excel
Формула из комбинации функций ИНДЕКС и ПОИСКПОЗ – это самая популярный тип формул для поиска значений в таблице Excel. Значительно реже используется функция ПРОСМОТР. В ее синтаксисе используется 3 аргумента:
Внимание! Сразу же отметим недостатки функции ПРОСМОТР, а они весьма существенные:
Поэтому сначала перед применением функции отсортируем просматриваемый диапазон вектора по возрастанию. Следующие две формулы предназначены для альтернативного поиска города:
И соответственного номера магазина:
Таблица пересортирована по диапазону C2:C11 по возрастанию и теперь все работает как надо.
Первые два аргумента функции ПРОСМОТР такие же, как и у функции ПОИСКПОЗ. Эти две функции работают по одному и тому же принципу. То есть ищет заданное значение в одном направлении вектора. Отличие в том, что она возвращает не позицию где находится найденная ячейка в диапазоне с нужным значением, а содержимое соответственной ячейки находящиеся на позиции (указанной в векторе результатов) от нее. Ну и не стоит забывать, что для функции ПРОСМОТР нужно сортировать таблицу по возрастанию просматриваемого диапазона и у нее нет аргумента точности совпадения при поиске значений.
Чтобы найти необходимый город, функция ПРОСМОТР сначала определяет, что строка «Свердловская» находится на 10-ой позиции (после сортировки таблицы) просматриваемого вектора по диапазону C2:C11. После формула чего возвращает содержимое 10-ой ячейки, но уже по вектору в диапазоне A2:A11. Аналогичным принципом функция ищет номер магазина, но там результирующий вектор – это уже диапазон D2:D11.
5 вариантов использования функции ИНДЕКС (INDEX)
Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?
Вариант 1. Извлечение данных из столбца по номеру ячейки
Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:
=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )
. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.
Вариант 2. Извлечение данных из двумерного диапазона
Если диапазон двумерный, т.е. состоит из нескольких строк и столбцов, то наша функция будет использоваться немного в другом формате:
=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )
Т.е. функция извлекает значение из ячейки диапазона с пересечения строки и столбца с заданными номерами.
Легко сообразить, что с помощью такой вариации ИНДЕКС и двух функций ПОИСКПОЗ можно легко реализовать двумерный поиск:
Вариант 3. Несколько таблиц
Если таблица не одна, а их несколько, то функция ИНДЕКС может извлечь данные из нужной строки и столбца именно заданной таблицы. В этом случае используется следующий синтаксис:
=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )
Вариант 4. Ссылка на столбец / строку
Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:
Вариант 5. Ссылка на ячейку
Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:
Функции ИНДЕКС и ПОИСКПОЗ в 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 есть очень удобная, но почему-то редко используемая функция, которая называется ИНДЕКС. Удобная она потому, что позволяет выдавать значение из диапазона по заданным номерам строки и столбца.
На практике ИНДЕКС редко используется, скорее всего, из-за того, что эти самые номер строки и столбца приходится вводить каждый раз. Ведь искомое значение не всегда нужно выдавать по порядку. Но тогда к функции ИНДЕКС на помощь приходит функция ПОИСКПОЗ, которая как раз таки позволяет найти нужную позицию.
Пример использования функций ИНДЕКС и ПОИСКПОЗ
Рассмотрим интересный пример, который позволит понять прелесть функции ИНДЕКС и неоценимую помощь ПОИСКПОЗ. Имеем сводную таблицу, в которой ведется учет купленной продукции.
Наша цель: создать карточку заказа, где по номеру артикула можно будет видеть, что это за товар, какой клиент его приобрел, сколько было куплено и по какой общей стоимости. Сделать это поможет функция ИНДЕКС совместно с ПОИСКПОЗ.
Для начала создадим выпадающий список для поля АРТИКУЛ ТОВАРА, чтобы не вводить цифры с клавиатуры, а выбирать их. Для этого кликаем в соответствующую ячейку (у нас это F13), затем выбираем вкладку ДАННЫЕ – ПРОВЕРКА ДАННЫХ. В открывшемся окне в пункте ТИП ДАННЫХ выбираем СПИСОК. А в качестве источника выделяем столбец с артикулами, включая шапку. Так у нас получился выпадающий список артикулов, которые мы можем выбирать.
Теперь нужно сделать так, чтобы при выборе артикула автоматически выдавались значения в остальных четырех строках. Воспользуемся функцией ИНДЕКС. Записываем ее и параллельно изучаем синтаксис.
Массив. В данном случае это вся таблица заказов. Выделяем ее вместе с шапкой и фиксируем клавишей F4.
Номер строки. Если бы у нас требовалось вывести одно значение, мы бы написали какую-то конкретную цифру. Но раз нам нужно, чтобы результат менялся, воспользуемся функцией ПОИСКПОЗ. Она будет искать необходимую позицию каждый раз, когда мы будем менять артикул.
Записываем команду ПОИСКПОЗ и проставляем ее аргументы.
Искомое значение. В нашем случае это ячейка, в которой указывается артикул, т.е. F13. Фиксируем ее клавишей F4.
Просматриваемый массив. Т.к. мы ищем по артикулу, значит, выделяем столбец артикулов вместе с шапкой. Фиксируем F4.
Тип сопоставления. Excel предлагает три типа сопоставления: больше, меньше и точное совпадение. У нас конкретный артикул, поэтому выбираем точное совпадение. В программе оно значится как 0 (ноль). На этом аргументы ПОИСКПОЗ закончились.
Номер столбца. Опять же воспользуемся ПОИСКПОЗ. Искомым значением будет ячейка E14, где указано наименование параметра, который мы ищем (ТОВАР). Просматриваемый массив: шапка с наименованиями, потому что искать система будет по слову ТОВАР. Тип сопоставления: 0.
Синтаксис функции ИНДЕКС закончен. Как в итоге выглядит формула, видно на скриншоте выше. Видим, что артикул 3516 действительно у арахиса. Протянем формулу на остальные строки и проверим. Теперь, меняя артикул товара, мы будем видеть, кто его купил, сколько и почем.
Поиск индекса максимального числа массива в Excel
Функция ИНДЕКС также помогает выделить из массива максимальное число. Рассмотрим тот же самый пример. Попробуем определить максимальные значения купленного количества товара, цены и суммы.
Начнем с количества. В любой ячейке под этим столбцом пишем =ИНДЕКС.
Первым аргументом у нас будет не просто массив, а максимальное число из массива. Поэтому дополнительно используем команду МАКС и выделяем соответствующий массив.
В принципе, нам больше не нужны никакие аргументы, но требуется ввести номер строки и столбца. В таком случае напишем два нуля.
Получили простейшую формулу, помогающую вывести максимальное значение из массива. Протянем ее вправо, получив аналогичную информацию по цене и сумме.
