значение по номеру строки excel
Функция ИНДЕКС() в EXCEL
history 29 декабря 2013 г.
Синтаксис функции
ИНДЕКС (массив; номер_строки; номер_столбца)
Массив — ссылка на диапазон ячеек.
Номер_строки — номер строки в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.
Номер_столбца — номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.
Если используются оба аргумента — и «номер_строки», и «номер_столбца», — то функция ИНДЕКС() возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.
Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС() возвращает значение ошибки #ССЫЛКА! Например, формула =ИНДЕКС(A2:A13;22) вернет ошибку, т.к. в диапазоне А2:А13 только 12 строк.
Значение из заданной строки диапазона
Пусть имеется одностолбцовый диапазон А6:А9.
Если диапазон горизонтальный (расположен в одной строке, например, А6:D6 ), то формула для вывода значения из 2-го столбца будет выглядеть так =ИНДЕКС(A6:D6;;2)
Значение из заданной строки и столбца таблицы
Пусть имеется таблица в диапазоне А6:B9.
Использование функции в формулах массива
Зачем это нужно? Теперь удалить по отдельности значения из ячеек А21 , А22 , А23 не удастся, мы получим предупреждение «нельзя изменять часть массива».
Хотя можно просто ввести в этих 3-х ячейках ссылки на диапазон А6:А8. Выделите 3 ячейки и введите формулу =A6:A8. Затем нажмите CTRL+SHIFT+ENTER и получим тот же результат.
Использование массива констант
Вместо ссылки на диапазон можно использовать массив констант :
ПОИСКПОЗ() + ИНДЕКС()
Формула =ВПР(«яблоки»;A35:B38;2;0) аналогична формуле =ИНДЕКС(B35:B38;ПОИСКПОЗ(«яблоки»;A35:A38;0)) которая извлекает цену товара Яблоки из таблицы, размещенную в диапазоне A35:B38
Ссылочная форма
Функция ИНДЕКС() позволяет использовать так называемую ссылочную форму. Поясним на примере.
получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений).
Использование функции ИНДЕКС() в этом примере принципиально отличается от примеров рассмотренных выше, т.к. функция возвращает не само значение, а ссылку (адрес ячейки) на значение. Вышеуказанная формула =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)
Аналогичный результат можно получить используя функцию СМЕЩ()
Теперь более сложный пример, с областями.
Пусть имеется таблица продаж нескольких товаров по полугодиям.
Функция ПОИСКПОЗ() в EXCEL
history 9 января 2014 г.
Синтаксис функции
ПОИСКПОЗ ( искомое_значение ; просматриваемый_массив ; тип_сопоставления)
Функция ПОИСКПОЗ() не различает РеГИстры при сопоставлении текстов.
Если функция ПОИСКПОЗ() не находит соответствующего значения, то возвращается значение ошибки #Н/Д.
Поиск позиции в массивах с текстовыми значениями
Произведем поиск позиции в НЕ сортированном списке текстовых значений (диапазон B7:B13 )
Столбец Позиция приведен для наглядности и не влияет на вычисления.
Формула для поиска позиции значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)
Формула находит первое значение сверху и выводит его позицию в диапазоне, второе значение Груши учтено не будет.
Чтобы найти номер строки, а не позиции в искомом диапазоне, можно записать следующую формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)
Если искомое значение не обнаружено в списке, то будет возвращено значение ошибки #Н/Д. Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0) вернет ошибку, т.к. значения «грейпфрут» в диапазоне ячеек B7:B13 нет.
В файле примера можно найти применение функции при поиске в горизонтальном массиве.
Поиск позиции в массиве констант
Поиск позиции с использованием подстановочных знаков
Если искомое значение точно не известно, то с помощью подстановочных знаков можно задать поиск по шаблону, т.е. искомое_значение может содержать знаки шаблона: звездочку (*) и знак вопроса (?). Звездочка соответствует любой последовательности знаков, знак вопроса соответствует любому одиночному знаку.
В качестве критерия можно задать «яблок*» и формула =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию текстового значения, начинающегося со слова яблок (если она есть в списке).
Подстановочные знаки следует использовать только для поиска позиции текстовых значений и Типом сопоставления = 0 (третий аргумент функции).
Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию
Функция ПОИСКПОЗ() возвращает только одно значение. Если в списке присутствует несколько значений, удовлетворяющих критерию, то эта функция не поможет.
Значение Груши находятся в позициях 2 и 5 списка. С помощью формулы массива
можно отсортировать найденные позиции, чтобы номера найденных позиций отображались в первых ячейках (см. файл примера ).
Поиск позиции в массивах с Числами
1. Произведем поиск позиции в НЕ сортированном списке числовых значений (диапазон B8:B14 )
Столбец Позиция приведен для наглядности и не влияет на вычисления.
Найдем позицию значения 30 с помощью формулы =ПОИСКПОЗ(30;B8:B14;0)
Формула ищет точное значение 30. Если в списке его нет, то будет возвращена ошибка #Н/Д.
2. Произведем поиск позиции в отсортированном по возрастанию списке числовых значений (диапазон B31:B37 )
Сортированные списки позволяют искать не только точные значения (их позицию), но и позицию ближайшего значения. Например, в списке на картинке ниже нет значения 45, но можно найти позицию наибольшего значения, которое меньше либо равно, чем искомое значение, т.е. позицию значения 40.
Это можно сделать с помощью формулы =ПОИСКПОЗ(45;B31:B37;1)
Обратите внимание, что тип сопоставления =1 (третий аргумент функции).
Функции ПОИСКПОЗ() и ИНДЕКС()
Функции ПОИСКПОЗ() и ИНДЕКС() часто используются вместе, т.к. позволяют по найденной позиции в одном диапазоне вывести соответствующее значение из другого диапазона. Рассмотрим пример.
Найдем количество заданного товара на определенном складе. Для этого используем формулу
ИНДЕКС (функция ИНДЕКС)
Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.
Функцию ИНДЕКС можно использовать двумя способами:
Если вы хотите возвращать значение указанной ячейки или массива ячеек, см. раздел Форма массива.
Если требуется возвращать ссылку на указанные ячейки, см. раздел Ссылочная форма.
Форма массива
Описание
Возвращает значение элемента таблицы или массива, выбранного индексами номеров строк и столбцов.
Если первый аргумент функции ИНДЕКС является константной массива, используйте форму массива.
Синтаксис
ИНДЕКС(массив; номер_строки; [номер_столбца])
Аргументы формы массива функции ИНДЕКС следующую:
Массива — обязательный аргумент. Диапазон ячеек или константа массива.
Если массив содержит только одну строку или один столбец, 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, мы не будем задерживаться на их синтаксисе и применении.
Приведём здесь необходимый минимум для понимания сути, а затем разберём подробно примеры формул, которые показывают преимущества использования ИНДЕКС и ПОИСКПОЗ вместо ВПР.
ИНДЕКС – синтаксис и применение функции
Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:
Каждый аргумент имеет очень простое объяснение:
Если указаны оба аргумента, то функция ИНДЕКС возвращает значение из ячейки, находящейся на пересечении указанных строки и столбца.
Вот простейший пример функции INDEX (ИНДЕКС):
Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2.
Очень просто, правда? Однако, на практике Вы далеко не всегда знаете, какие строка и столбец Вам нужны, и поэтому требуется помощь функции ПОИСКПОЗ.
ПОИСКПОЗ – синтаксис и применение функции
Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.
Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку “London” – это третий элемент в списке.
Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:
На первый взгляд, польза от функции ПОИСКПОЗ вызывает сомнение. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!
Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.
Как использовать ИНДЕКС и ПОИСКПОЗ в Excel
Теперь, когда Вам известна базовая информация об этих двух функциях, полагаю, что уже становится понятно, как функции ПОИСКПОЗ и ИНДЕКС могут работать вместе. ПОИСКПОЗ определяет относительную позицию искомого значения в заданном диапазоне ячеек, а ИНДЕКС использует это число (или числа) и возвращает результат из соответствующей ячейки.
Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:
Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:
Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:
Теперь давайте разберем, что делает каждый элемент этой формулы:
Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4, так как счёт начинается со второй строки.
Вот такой результат получится в Excel:
Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.
Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?
В данном случае – смысла нет! Цель этого примера – исключительно демонстрационная, чтобы Вы могли понять, как функции ПОИСКПОЗ и ИНДЕКС работают в паре. Последующие примеры покажут Вам истинную мощь связки ИНДЕКС и ПОИСКПОЗ, которая легко справляется с многими сложными ситуациями, когда ВПР оказывается в тупике.
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС/ПОИСКПОЗ намного лучше, чем ВПР. Однако, многие пользователи Excel по-прежнему прибегают к использованию ВПР, т.к. эта функция гораздо проще. Так происходит, потому что очень немногие люди до конца понимают все преимущества перехода с ВПР на связку ИНДЕКС и ПОИСКПОЗ, а тратить время на изучение более сложной формулы никто не хочет.
Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: Как находить значения, которые находятся слева покажет эту возможность в действии.
2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.
Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:
=VLOOKUP(«lookup value»,A1:C10,2)
=ВПР(«lookup value»;A1:C10;2)
Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.
Используя ПОИСКПОЗ/ИНДЕКС, Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР.
3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.
Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:
Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:
4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.
Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.
С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто совершает поиск и возвращает результат, выполняя аналогичную работу заметно быстрее.
ИНДЕКС и ПОИСКПОЗ – примеры формул
Теперь, когда Вы понимаете причины, из-за которых стоит изучать функции ПОИСКПОЗ и ИНДЕКС, давайте перейдём к самому интересному и увидим, как можно применить теоретические знания на практике.
Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС
Любой учебник по ВПР твердит, что эта функция не может смотреть влево. Т.е. если просматриваемый столбец не является крайним левым в диапазоне поиска, то нет шансов получить от ВПР желаемый результат.
Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ/ИНДЕКС, которая покажет, какое место по населению занимает столица России (Москва).
Как видно на рисунке ниже, формула отлично справляется с этой задачей:
Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:
Подсказка: Правильным решением будет всегда использовать абсолютные ссылки для ИНДЕКС и ПОИСКПОЗ, чтобы диапазоны поиска не сбились при копировании формулы в другие ячейки.
Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)
Вы можете вкладывать другие функции Excel в ИНДЕКС и ПОИСКПОЗ, например, чтобы найти минимальное, максимальное или ближайшее к среднему значение. Вот несколько вариантов формул, применительно к таблице из предыдущего примера:
1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:
2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:
3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:
О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ
Используя функцию СРЗНАЧ в комбинации с ИНДЕКС и ПОИСКПОЗ, в качестве третьего аргумента функции ПОИСКПОЗ чаще всего нужно будет указывать 1 или -1 в случае, если Вы не уверены, что просматриваемый диапазон содержит значение, равное среднему. Если же Вы уверены, что такое значение есть, – ставьте 0 для поиска точного совпадения.
В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает “Moscow”, поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).
Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу
Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.
В этом примере формула ИНДЕКС/ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?
Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:
И я поздравляю тех из Вас, кто догадался!
Начнём с того, что запишем шаблон формулы. Для этого возьмём уже знакомую нам формулу ИНДЕКС/ПОИСКПОЗ и добавим в неё ещё одну функцию ПОИСКПОЗ, которая будет возвращать номер столбца.
Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).
А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.
Хорошо, давайте запишем формулу. Когда мне нужно создать сложную формулу в Excel с вложенными функциями, то я сначала каждую вложенную записываю отдельно.
Итак, начнём с двух функций ПОИСКПОЗ, которые будут возвращать номера строки и столбца для функции ИНДЕКС:
Результатом этой формулы будет 5, поскольку “2015” находится в 5-ом столбце.
Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:
Если заменить функции ПОИСКПОЗ на значения, которые они возвращают, формула станет легкой и понятной:
Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!
Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ
В учебнике по ВПР мы показывали пример формулы с функцией ВПР для поиска по нескольким критериям. Однако, существенным ограничением такого решения была необходимость добавлять вспомогательный столбец. Хорошая новость: формула ИНДЕКС/ПОИСКПОЗ может искать по значениям в двух столбцах, без необходимости создания вспомогательного столбца!
Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.
Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:
Эта формула сложнее других, которые мы обсуждали ранее, но вооруженные знанием функций ИНДЕКС и ПОИСКПОЗ Вы одолеете ее. Самая сложная часть – это функция ПОИСКПОЗ, думаю, её нужно объяснить первой.
MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)
ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)
В формуле, показанной выше, искомое значение – это 1, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:
Теперь понимаете, почему мы задали 1, как искомое значение? Правильно, чтобы функция ПОИСКПОЗ возвращала позицию только, когда оба критерия выполняются.
Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС. Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3.
И, наконец, т.к. нам нужно проверить каждую ячейку в массиве, эта формула должна быть формулой массива. Вы можете видеть это по фигурным скобкам, в которые она заключена. Поэтому, когда закончите вводить формулу, не забудьте нажать Ctrl+Shift+Enter.
Если всё сделано верно, Вы получите результат как на рисунке ниже:
ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel
Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.
Синтаксис функции ЕСЛИОШИБКА очень прост:
Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.
Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:
=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
«Совпадений не найдено. Попробуйте еще раз!») =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
«Совпадений не найдено. Попробуйте еще раз!»)
И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:
Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки (“”), как значение второго аргумента функции ЕСЛИОШИБКА. Вот так:
Надеюсь, что хотя бы одна формула, описанная в этом учебнике, показалась Вам полезной. Если Вы сталкивались с другими задачами поиска, для которых не смогли найти подходящее решение среди информации в этом уроке, смело опишите свою проблему в комментариях, и мы все вместе постараемся решить её.