Excel индекс поискпоз все значения

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

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

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

• Базовая информация об ИНДЕКС и ПОИСКПОЗ
• Используем функции ИНДЕКС и ПОИСКПОЗ в Excel
• Преимущества ИНДЕКС и ПОИСКПОЗ перед ВПР
• ИНДЕКС и ПОИСКПОЗ – примеры формул
  • Как находить значения, которые находятся слева
  • Вычисления при помощи ИНДЕКС и ПОИСКПОЗ
  • Поиск по известным строке и столбцу
  • Поиск по нескольким критериям
  • ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

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

INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])

Каждый аргумент имеет очень простое объяснение:

• array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
• row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
• column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

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

Вот простейший пример функции INDEX (ИНДЕКС):

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

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

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

ПОИСКПОЗ – синтаксис и применение функции

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

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку «London» – это третий элемент в списке.

=MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

• lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
• lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
• match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ, хотите ли Вы найти точное или приблизительное совпадение:
  • 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
  • 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС/ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0.
  • -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

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

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

=INDEX(столбец из которого извлекаем,(MATCH (искомое значение,столбец в котором ищем,0))
=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое значение;столбец в котором ищем;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

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

• Функция MATCH (ПОИСКПОЗ) ищет значение «Japan» в столбце B, а конкретно – в ячейках B2:B10, и возвращает число 3, поскольку «Japan» в списке на третьем месте.
• Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:

  =INDEX($D$2:$D$10,3)
=ИНДЕКС($D$2:$D$10;3)

  Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4, так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?

=VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

Решая, какую формулу использовать для вертикального поиска, большинство гуру 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:

=VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.

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

ИНДЕКС и ПОИСКПОЗ – примеры формул

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

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

Любой учебник по ВПР твердит, что эта функция не может смотреть влево. Т.е. если просматриваемый столбец не является крайним левым в диапазоне поиска, то нет шансов получить от ВПР желаемый результат.

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ/ИНДЕКС, которая покажет, какое место по населению занимает столица России (Москва).

Как видно на рисунке ниже, формула отлично справляется с этой задачей:

=INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:

• Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение «Russia» в списке:

  =MATCH("Russia",$B$2:$B$10,0))
=ПОИСКПОЗ("Russia";$B$2:$B$10;0))

• Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10.
• Затем соединяем обе части и получаем формулу:

  =INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

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

• Если указываете 1, значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
• Если указываете -1, значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает «Moscow», поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

В этом примере формула ИНДЕКС/ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])

И я поздравляю тех из Вас, кто догадался!

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

=INDEX(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))
=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))

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

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

Итак, начнём с двух функций ПОИСКПОЗ, которые будут возвращать номера строки и столбца для функции ИНДЕКС:

• ПОИСКПОЗ для столбца – мы ищем в столбце B, а точнее в диапазоне B2:B11, значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

  =MATCH($H$2,$B$1:$B$11,0)
=ПОИСКПОЗ($H$2;$B$1:$B$11;0)

  Результатом этой формулы будет 4, поскольку «USA» – это 4-ый элемент списка в столбце B (включая заголовок).

• ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1, то есть в ячейках A1:E1:

  =MATCH($H$3,$A$1:$E$1,0)
=ПОИСКПОЗ($H$3;$A$1:$E$1;0)

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

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

=INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:

{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13),0),3)}
{=ИНДЕКС('Lookup table'!$A$2:$C$13;ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13);0);3)}

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

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, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

• Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
• Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
• Далее, мы делаем то же самое для значений столбца B (Product).
• Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1. Если оба критерия ложны, или выполняется только один из них – Вы получите 0.

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

Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС. Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3.

И, наконец, т.к. нам нужно проверить каждую ячейку в массиве, эта формула должна быть формулой массива. Вы можете видеть это по фигурным скобкам, в которые она заключена. Поэтому, когда закончите вводить формулу, не забудьте нажать Ctrl+Shift+Enter.

Если всё сделано верно, Вы получите результат как на рисунке ниже:

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент 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));
"Совпадений не найдено. Попробуйте еще раз!")

И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:

Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки («»), как значение второго аргумента функции ЕСЛИОШИБКА. Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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

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

​Смотрите также​​ целое с помощью​​ первом решении, только​​ из нашей таблицы​​ таком случае напишем​ в которой ведется​​ месяцу, который может​​ значение, а ссылочная​​Челябинск​​=ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА)​​2,17​​ функции поиска.​может быть не​ сопоставления = -1.​ Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0)​ искомого значения, а​ A1:L1(горизонтальный вектор):​Совместное использование функций​ оператора сцепки (&),​ запись немного отличается.​ и сравнить её​​ два нуля.​​ учет купленной продукции.​​ быть выбран из​​ – ссылку.​02.05.12​Поиск числа 3 в​

​100​Функции ВПР и ГПР​

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

​ упорядочен.​​ В этом случае​​ вернет ошибку, т.к.​ не само значение.​Если Вы уже работали​ИНДЕКС​ чтобы получить уникальный​ Я рекомендую самостоятельно​ с искомым словом.​Скачать примеры использования функций​Наша цель: создать карточку​

​ выпадающего списка.​Форма массива имеет вот​​3467​​ трех строках константы​1,09​ вместе с функций​

​-1​ функция ПОИСКПОЗ() находит​​ значения «грейпфрут» в​​ Например: ПОИСКПОЗ(«б»;{«а»;»б»;»в»;»б»};0) возвращает​ с функциями​и​

​ столбец-ключ для поиска:​ сравнить эти формулы​ Если их значения​​ ИНДЕКС и ПОИСКПОЗ​​ заказа, где по​=INDEX($C$2:$C$8,MATCH($F$2,$D$2:$D$8,0))​ такой синтаксис:​Челябинск​ массива и возврат​1,95​ индекс и ПОИСКПОЗ,описаны​Функция​ наименьшее значение, которое​ диапазоне ячеек​

​ вывести максимальное значение​​ будет видеть, что​​В этом примере именованный​ИНДЕКС(массив;номер_строки;номер_столбца)​3474​ 2 того же​1,29​ полезных функций в​​находит наименьшее значение,​​ чем искомое значение.​

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

​нет.​​ «б» в массиве​​ГПР​ хорошая альтернатива​ВПР (VLOOKUP)​И наконец-то, несколько иной​Когда речь идет о​ из массива. Протянем​ это за товар,​ диапазон​ar​Челябинск​

​ (в данном случае —​1,71​ Microsoft Excel.​ которое больше или​Функции ПОИСКПОЗ() и ИНДЕКС()​В файле примера можно​ {«а»;»б»;»в»;»б»}. Позиция второй​и​ВПР​

​для поиска склеенной​ (не табличный) подход.​ циклах Excel (за​ ее вправо, получив​ какой клиент его​​MonthAmts​​ray​04.05.12​ третьего) столбца. Константа​0​

​Примечание:​ равно значению аргумента​ часто используются вместе,​ найти применение функции​

​ буквы «б» будет​ПРОСМОТР​,​

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

​ пары​ Как правило, каждый​​ исключением VBA, конечно,​​ аналогичную информацию по​​ приобрел, сколько было​​состоит из 3-х​​(массив) – массив​​3490​ массива содержит три​Формула​ Функция мастер подстановок больше​искомое_значение​ т.к. позволяют по​ при поиске в​ проигнорирована, функция вернет​в Excel, то​ГПР​НектаринЯнварь​​ раз, когда необходимо​​ потому что там​​ цене и сумме.​​ куплено и по​ несмежных диапазонов. Диапазон​

​ констант или диапазон​Челябинск​ строки значений, разделенных​Описание​ не доступен в​.​ найденной позиции в​ горизонтальном массиве.​ позицию только первой​

​ должны знать, что​и​из ячеек H3​​ что-то искать в​​ дело намного проще),​Функция INDEX имеет две​ какой общей стоимости.​​MonthAmts​​ ячеек.​05.05.12​ точкой с запятой​​Результат​​ Microsoft Excel.​

​Просматриваемый_массив​

​ одном диапазоне вывести​Поиск позиции можно производить​ буквы. О том​ они осуществляют поиск​ПРОСМОТР​ и J3 в​

​ Excel, моментально на​​ на ум приходят​​ формы. Первая, содержит​ Сделать это поможет​

1. ​имеет 3 области​Если аргумент​3503​ (;). Так как​
2. ​=ВПР(1,A2:C10,2)​​Вот пример того, как​​должен быть упорядочен​ соответствующее значение из​ не только в​​ как вернуть ВСЕ​​ только в одномерном​. Эта связка универсальна​ созданном ключевом столбце:​
3. ​ ум приходит пара​​ в первую очередь​​ 3 аргумента для​ функция ИНДЕКС совместно​ – по одной​array​​Челябинск​​ «c» было найдено​Используя приблизительное соответствие, функция​

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

​ ввода. Однако существует​

​ с ПОИСКПОЗ.​ для каждого месяца​

​(массив) имеет только​08.05.12​ в строке 2​ ищет в столбце​=ВПР(B2;C2:E7,3,ИСТИНА)​ ЛОЖЬ, Z-A, …,​​ пример.​​ и в массивах​​ читайте ниже в​​ приходится сталкиваться с​ возможностями этих функций.​: Простой способ, знакомая​ ПОИСКПОЗ. Результат поиска​Итерационные (итеративные) вычисления.​ функция ИНДЕКС с​Для начала создадим выпадающий​ – и в​ 1 строку или​

​3151​

office-guru.ru

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

​ того же столбца,​ A значение 1,​В этом примере B2​ 2, 1, 0,​Найдем количество заданного товара​​ констант. Например, формула​​ разделе Поиск позиций​ двумерным поиском, когда​ А в некоторых​ функция, работает с​ выглядит так:​Формулы массива (или такие​​ 4 аргументами, где​​ список для поля​ каждой области по​ 1 столбец, то​Нижний Новгород​​ что и 3,​​ находит наибольшее значение,​​ — это первый​​ -1, -2, …​ на определенном складе.​ =ПОИСКПОЗ(«груши»;{«яблоки»;»ГРУШИ»;»мандарины»};0) вернет значение​ ВСЕХ текстовых значений,​

​ соответствия требуется искать​ случаях, например, при​ любыми данными.​Формула построена из блоков​ функции, как СУММПРОИЗВ,​ вместо одной таблицы​ АРТИКУЛ ТОВАРА, чтобы​ 4 строки и​ соответствующий аргумент с​09.04.12​ возвращается «c».​ которое меньше или​аргумент​ и т. д.​ Для этого используем​ 2.​ удовлетворяющих критерию.​ сразу по двум​

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

​ двумерном поиске данных​​Минусы​

​ и имеет более​​ которые работают с​ можно указать несколько​ не вводить цифры​​ 2 столбца. Вот​​ номером строки/столбца не​3438​c​ равняется 1 и​— элемент данных, функция​Функция​ формулу​

​Если искомое значение точно​​ПОИСКПОЗискомое_значение просматриваемый_массив​ параметрам. Именно в​ на листе, окажется​​: Надо делать дополнительный​​ или менее следующий​ массивами, хотя мы​ таблиц. Такой подход​​ с клавиатуры, а​​ формула для имени​ обязателен.​Нижний Новгород​​В этом примере последней​​ составляет 0,946, а​ должна работать. Функции​ПОИСКПОЗ​=ИНДЕКС(B67:E71;ПОИСКПОЗ(A74;A67:A71;0);ПОИСКПОЗ(C74;B66:E66;0))​​ не известно, то​​; тип_сопоставления)​ таких случаях связка​ просто незаменимой. В​ столбец и потом,​

​ вид:​​ их не подтверждаем​ позволяет одновременную работу​​ выбирать их. Для​​MonthAmts​Если аргумент​​02.05.12​​ использует функций индекс​ затем возвращает значение​​ ВПР это первый​

• ​возвращает не само​​В файле примера, соответствующий​​ с помощью подстановочных​Искомое_значение​ПОИСКПОЗ​ данном уроке мы​​ возможно, еще и​​1*НЕ(ЕОШИБКА(ПОИСКПОЗ(F5;A2:A9;0)))​​ с помощью Ctr​​ сразу по нескольким​ этого кликаем в​
• ​:​array​3471​ и ПОИСКПОЗ вместе​ из столбца B​​ аргумент — значение,​​ значение, а его​ столбец и строка​ знаков можно задать​- значение, используемое​и​ последовательно разберем функции​​ прятать его от​​Изменяются только выделенные фрагменты.​ + Shift +​ таблицам.​
• ​ соответствующую ячейку (у​​=’Ex02′!$B$3:$C$6,’Ex02′!$E$3:$F$6,’Ex02′!$H$3:$I$6​​(массив) содержит более​Нижний Новгород​ для возвращения раннюю​ в той же​ которое требуется найти.​​ позицию в аргументе​​ выделены с помощью​ поиск по шаблону,​ при поиске значения​ИНДЕКС​ПОИСКПОЗ​ пользователя. При изменении​

​ Для каждого столбца​ Enter, как в​В качестве последнего аргумента​

​ нас это F13),​=’Ex02′!$B$3:$C$6;’Ex02′!$E$3:$F$6;’Ex02′!$H$3:$I$6​ 1-й строки и​04.05.12​

Поиск позиции в массивах с текстовыми значениями

​ номер счета-фактуры и​ строке.​ Этот аргумент может​​просматриваемый_массив​​ Условного форматирования.​

​ т.е. искомое_значение может​ в​в Excel оказывается​

​и​ числа строк в​

​ с помощью функции​ случае с классическими​ необходимо выбрать номер​ затем выбираем вкладку​При помощи функции​ 1-го столбца:​

​3160​ его соответствующих даты​2,17​ быть ссылка на​. Например, функция​

​СОВЕТ: Подробнее о поиске​ содержать знаки шаблона:​просматриваемом_массивеИскомое_значение​ просто незаменимой.​ИНДЕКС​ таблице — допротягивать​ ПОИСКПОЗ, мы проверяем​ формулами массива).​​ области, к которой​​ ДАННЫЕ – ПРОВЕРКА​

​INDEX​и задано значение только​Москва​ для каждого из​

Поиск позиции в массиве констант

​=ВПР(1,A2:C10,3,ИСТИНА)​ ячейку или фиксированным​ПОИСКПОЗ(«б»;{«а»;»б»;»в»};0)​ позиций можно прочитать​ звездочку (*) и​может быть значением​На рисунке ниже представлена​

Поиск позиции с использованием подстановочных знаков

​, а затем рассмотрим​ формулу сцепки на​ удалось ли найти​Первое решение выглядит следующем​ ссылается формула. Не​ ДАННЫХ. В открывшемся​(ИНДЕКС) Вы можете​ аргумента​18.04.12​ пяти городов. Так​Используя приблизительное соответствие, функция​ значением, например «строфа»​возвращает 2 — относительную​

​ в соответствующем разделе​ знак вопроса (?).​ (числом, текстом или​ таблица, которая содержит​ пример их совместного​ новые строки (хотя​ в столбце искомое​

​ образом:​ пресмыкающиеся друг к​ окне в пункте​ возвратить стоимость (Cost)​row_num​3328​ как дата возвращаются​

​ ищет в столбце​ или 21,000. Второй​ позицию буквы «б»​ сайта: Поиск позиции.​​ Звездочка соответствует любой​​ логическим значением (ЛОЖЬ​ месячные объемы продаж​

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

​ использования в Excel.​ это можно упростить​ выражение. Если нет,​Рассмотрим ближе каким образом​ другу области должны​ ТИП ДАННЫХ выбираем​

​ или сумму выручки​(номер_строки), то будет​​Москва​​ в виде числа,​ A значение 1,​

​ аргумент — это​ в массиве {«а»;»б»;»в»}.​С помощью функций ПОИСКПОЗ()​ последовательности знаков, знак​

​ или ИСТИНА)) или​

​ каждого из четырех​Более подробно о функциях​ применением умной таблицы).​ то функция возвращает​ эта формула работает:​ быть заключены в​ СПИСОК. А в​​ (Rev) для определённого​​ возвращен массив всех​26.04.12​ мы используем функцию​ находит наибольшее значение,​ диапазон ячеек, C2-:E7,​Функция​

​ и ИНДЕКС() можно​ вопроса соответствует любому​

​ ссылкой на ячейку,​

​ видов товара. Наша​ ВПР и ПРОСМОТР.​Если нужно найти именно​ ошибку. С помощью​($A$2:$D$9=F2)​

Поиск позиции в массивах с Числами

​ круглые скобки, а​ качестве источника выделяем​ региона и месяца.​ значений этой строки.​​3368​​ текст отформатировать его​

​ которое меньше или​ в котором выполняется​ПОИСКПОЗ​

​ заменить функцию ВПР(),​ одиночному знаку.​ содержащую число, текст​

​ задача, указав требуемый​​Функция​​ число (в нашем​ функции ЕОШИБКА мы​Данный фрагмент формулы необходимо​ таблицы должны быть​

​ столбец с артикулами,​=INDEX(MonthAmts,B10,C10,D10)​если же задано значение​Москва​​ как дату. Результат​​ равняется 1 и​

​ поиск значения, которые​не различает регистры​ об этом читайте​Предположим, что имеется перечень​ или логическое значение.​ месяц и тип​ПОИСКПОЗ​ случае цена как​ проверяем выдала ли​ сравнивать значение каждой​ отделены друг от​ включая шапку. Так​=ИНДЕКС(MonthAmts;B10;C10;D10)​ только аргумента​

​29.04.12​ функции ПОИСКПОЗ фактически​

​ составляет 0,946, а​ нужно найти. Третий​ при сопоставлении текста.​

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

Функции ПОИСКПОЗ() и ИНДЕКС()

​С результатом функции​column_num​3420​ используется функция индекс​ затем возвращает значение​ аргумент — это​Если функция​ функции ВПР().​

​ не знаем точно​— непрерывный диапазон​ продаж.​ ячейки в заданном​

​ вместо ВПР можно​

​ Если да, то​ искомым словом. Для​ и аргументы в​ выпадающий список артикулов,​

​INDEX​(номер_столбца), то массив​Москва​ аргументом. Сочетание функций​

​ из столбца C​ столбец в диапазон​ПОИСКПОЗ​Функция​ как записана товарная​ ячеек, возможно, содержащих​

excel2.ru

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

​Пускай ячейка C15 содержит​​ диапазоне Excel, содержимое​​ использовать функцию​ мы получаем значение​ этого формула создаёт​ Excel, то есть​ которые мы можем​(ИНДЕКС) можно произвести​ всех значений этого​01.05.12​ индекс и ПОИСКПОЗ​​ в той же​​ ячеек, содержащий значение,​не находит соответствующего​ПОИСКПОЗ​

​ позиция относящаяся к​​ искомые значения.​​ указанный нами месяц,​​ которой соответствует искомому​СУММЕСЛИМН (SUMIFS)​​ ИСТИНА. Быстро меняем​​ в памяти компьютера​ с использованием точки​ выбирать.​ операцию умножения, как​ столбца.​​3501​​ используются два раза​ строке.​​ которое вы поиска.​​ значения, возвращается значение​​выполняет поиск указанного​​ яблокам: яблоки или​

Синтаксис

​Просматриваемый_массив​

​ например,​ значению. Т.е. данная​

• ​, появившуюся начиная с​​ её на ЛОЖЬ​ массив значений, равный​ с запятой.​​Теперь нужно сделать так,​​ при вычислении налога​Если аргумент​Москва​ в каждой формуле​100​Четвертый аргумент не является​ ошибки #Н/Д.​
  ​ элемента в диапазоне​​ яблоко.​​может быть только​Май​ функция возвращает не​ Excel 2007. По​ и умножаем на​

• ​ размерам нашей таблицы,​​В этой версии функции​ чтобы при выборе​ (Tax) в ячейке​

• ​row_num​​06.05.12​ — сначала получить​=ВПР(0,7,A2:C10,3,ЛОЖЬ)​​ обязательным. Введите TRUE​​Если​ ячеек и возвращает​​В качестве критерия можно​​ одностолбцовым диапазоном ячеек,​. А ячейка C16​​ само содержимое, а​​ идее, эта функция​ введённый вручную номер​ заполненную нулями (фактически​
  ​ ИНДЕКС можно установить​ артикула автоматически выдавались​ F10:​(номер_строки) не указан,​Краткий справочник: обзор функции​​ номер счета-фактуры, а​​Используя точное соответствие, функция​

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

• ​является текстом, то​​ A1:A3 содержит значения​​ со слова яблок​ в одной строке,​

• ​. Введем в ячейку​​ формула вернет число​​ условиям. Но если​ не интересует ошибка,​ эти значения, и​

• ​ ценами из разных​​ ее и параллельно​​ ссылку для функции​​Если аргумент​​Использование аргумента массива таблицы​​ вставьте ее в​​ нет, возвращается сообщение​ приблизительное значение, указать​​искомое_значение​​ 5, 25 и​ (если она есть​​ например,​​ C17 следующую формулу​5​ в нашем списке​ возвращаемая функцией ПОИСКПОЗ.​ Excel автоматически преобразует​ магазинов. Диапазонам данных​ изучаем синтаксис.​CELL​column_num​​ в функции ВПР​​ ячейку A1 пустого​

Пример

​ об ошибке.​ в качестве первого​может содержать подстановочные​ 38, то формула​ в списке).​А2:Е2​ и нажмем​, поскольку имя «Дарья»​ нет повторяющихся товаров​ Также ищем варианты​ их на ноль).​ я дал именам,​Массив. В данном случае​

support.office.com

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

​ формула не выдала​​ значение в таблице​ используя поле «Имя».​ заказов. Выделяем ее​ в ячейке G10.​row_num​ 30 дней​    Прежде чем вставлять данные​Используя приблизительное соответствие, функция​ будут соответствовать значение​) и вопросительный знак​ элемент 25 является вторым​ позиции текстовых значений​ будет (выдаст ошибку​=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))​В следующем примере формула​ выведет значение цены​ ошибку. Потому что​ не равно искомому​ Теперь диапазоны называются​ вместе с шапкой​=CELL(«address»,INDEX(MonthAmts,B10,C10,D10))​

​(номер_строки).​мы искали текстовые​ в Excel, установите​ ищет в столбце​ в первом аргументе​ (​ в диапазоне.​ и​ #Н/Д), так как​Как видите, мы получили​ вернет​ для заданного товара​ это будет означать,​

​ слову. Как не​ Магазин1(B2:E5), Магазин2(B8:E11) и​ и фиксируем клавишей​=ЯЧЕЙКА(«адрес»;ИНДЕКС(MonthAmts;B10;C10;D10))​Если указаны оба аргумента,​ строки, используя функцию​

​ для столбцов A​​ A значение 0,1.​ предоставить. Другими словами,​?​

​Совет:​Типом сопоставления​

​Просматриваемый_массив​

​ верный результат. Если​3​​ и месяца:​​ что искомое значение​ трудно догадаться, в​ Магазин3(B14:E17).​ F4.​Вы можете использовать функцию​ то функция возвращает​FIND​ – D ширину​ Поскольку 0,1 меньше​ оставив четвертый аргумент​). Звездочка соответствует любой​ Функцией​= 0 (третий​представляет собой диапазон​ поменять месяц и​, поскольку число 300​Плюсы​ было найдено. Немного​ случае нахождения искомого​

​Первый аргумент определен теперь​Номер строки. Если бы​INDEX​ значение ячейки на​(НАЙТИ), и выяснили,​ в 250 пикселей​ наименьшего значения в​ пустым, или ввести​ последовательности знаков, вопросительный​ПОИСКПОЗ​ аргумент функции).​ ячеек размещенный одновременно​ тип товара, формула​ находится в третьем​: Не нужен дополнительный​ запутано, но я​ слова, в памяти​

​ осталось написать формулы​ у нас требовалось​(ИНДЕКС), чтобы создать​ пересечении указанных строки​ что она чувствительна​ и нажмите кнопку​ столбце A, возвращается​ значение ИСТИНА —​ знак — любому одиночному​следует пользоваться вместо​Функция ПОИСКПОЗ() возвращает только​ в нескольких столбцах​ снова вернет правильный​

​ столбце диапазона B1:I1.​ столбец, решение легко​ уверен, что анализ​ компьютера в соответствующем​ для остальных аргументов.​ вывести одно значение,​ динамический диапазон. В​ и столбца.​ к регистру, в​

​Перенос текста​ сообщение об ошибке.​ обеспечивает гибкость.​ знаку. Если нужно​ одной из функций​ одно значение. Если​ и нескольких ячейках.​

Попробуйте попрактиковаться

​ результат:​Из приведенных примеров видно,​ масштабируется на большее​ формул позволит вам​ месте будет цифра​​ мы бы написали​ этом примере создан​Если в качестве аргумента​ отличие от функции​(вкладка «​#Н/Д​В этом примере показано,​ найти сам вопросительный​ПРОСМОТР​ в списке присутствует​Тип_сопоставления​

Пример функции ВПР в действии

​В данной формуле функция​ что первым аргументом​

​ количество условий (до​​ быстро понять ход​ 1.​С помощью функции ВЫБОР,​ какую-то конкретную цифру.​ именованный диапазон​row_num​SEARCH​​Главная​​=ВПР(2,A2:C10,2,ИСТИНА)​​ как работает функция.​​ знак или звездочку,​​, когда требуется найти​​ несколько значений, удовлетворяющих​

Пример функции ГПР

​ листах. В прикреплении​ (тех же размеров,​ A13 и я​ каждый раз, когда​

​Если в список в​​ столбца или всей​(ИНДЕКС). Используя номер​Дата выставления счета​ равняется 2 и​ (2-й аргумент) и​Скопируйте образец данных из​можно использовать для​​. Найдем все позиции​​со значениями в​​ поиск.​​ функция имеет еще​​ не работает в​​ файл. Уважаемые знатоки​

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

​Искомое значение. В нашем​ списке в ячейке​ИНДЕКС(ссылка;номер_строки;номер_столбца;номер_области)​ ходе марафона мы​07.04.12​ строке.​ аргумент).​ отобразить результаты формул,​.​ помощью формулы массива​равен 0, то​ с помощью функции​ трех вариантов:​ИНДЕКС (INDEX)​GP75​ Затем одна таблица​ ячейку G1, функция​ случае это ячейка,​ F2, который использует​r​ уже не раз​=»Казань = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),1)& «,​1,71​

​Четвертый аргумент пуст, поэтому​ выделите их и​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​=(«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65))​

​ функция ПОИСКПОЗ() находит​​ПОИСКПОЗ(C15;A2:A13;0)​0​и​, вы столбцы со​ умножается на другую.​ ИНДЕКС относится к​​ в которой указывается​​MonthList​​eference (ссылка)​​ использовали функцию​​ Дата выставления счета:​​Скопируйте всю таблицу и​

Дополнительные сведения о функциях поиска

• ​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),3),»m/d/yy»)​ установите для столбцов​

• ​ результат вообще.​Количество​

• ​. Например, при поиске​CTRL+SHIFT+ENTER​

support.office.com

30 функций Excel за 30 дней: ИНДЕКС (INDEX)

​Если тип_сопоставления равен 1,​​ получаем с помощью​— функция​​ их для поиска​ Microsoft Excel, то​​ массив с нулями​​ значения были переменными,​ шапкой. Фиксируем F4.​ отсортированный в алфавитном​reference​​ точному совпадению в​​3154​

​ A – С​Когда вы будете довольны​Бананы​​ номера в телефонной​​. В позициях, в​ то функция ПОИСКПОЗ()​ функции​ПОИСКПОЗ​ по нескольким столбцам​ должны быть знакомы​ везде, где значения​ а не постоянными.​​Тип сопоставления. Excel предлагает​​ порядке. Функция​(ссылка) имеет только​

• ​ списке;​​Казань​​ ширину в 250​ ВПР, ГПР одинаково​25​ книге имя абонента​
• ​ которых есть значение​​ находит наибольшее значение,​​ПОИСКПОЗ(C16;B1:E1;0)​ищет самое большое​ в виде формулы​
• ​ с функцией поиска​​ в нашей таблице​​ В ячейках G2​ три типа сопоставления:​COUNTIF​ 1 строку или​
• ​вместе с​​11.04.12​​ пикселей и нажмите​ удобно использовать. Введите​Апельсины​

​ указывается в качестве​ Груши будет выведено​ которое меньше либо​. Для наглядности вычислим​​ значение, которое меньше​​ массива. Для этого:​ и подстановки​ не соответствуют искомому​ и G3 я​ больше, меньше и​

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

​(СЧЁТЕСЛИ) подсчитывает, как​​ 1 столбец, то​​AREAS​=»Челябинск = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),1)& «,​ кнопку​ те же аргументы,​38​ искомого значения, а​​ соответствующее значение позиции,​​ равно, чем​ и это значение:​

Как можно использовать функцию INDEX (ИНДЕКС)?

​ или равно заданному.​​Выделите пустую зеленую ячейку,​​ВПР​ выражению, и номер​ ввожу пример названия​ точное совпадение. У​ много месяцев стоит​

• ​ соответствующий аргумент с​(ОБЛАСТИ) – для​
• ​ Дата выставления счета:​Перенос текста​ но он осуществляет​
• ​Яблоки​ нужным значением будет​
• ​ в остальных ячейках​искомое_значениеПросматриваемый_массив​Если подставить в исходную​

Синтаксис INDEX (ИНДЕКС)

​ Требуется сортировка в​​ где должен быть​​или​ столбца, в котором​ продукта и веса.​ нас конкретный артикул,​ перед каждым конкретным​ номером строки/столбца не​

​ поиска последней области​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),3),»m/d/yy»)​

​(вкладка "​
​ поиск в строках​

• ​40​​ номер телефона.​​ быдет выведен 0.​должен быть упорядочен​ громоздкую формулу вместо​
• ​ порядке возрастания.​​ результат.​​VLOOKUP​ соответствующее значение было​ Теперь, изменяя значения​ поэтому выбираем точное​ месяцем в списке​ обязателен.​
• ​ в именованном диапазоне;​​3191​​Главная​ вместо столбцов. «​Груши​
  • ​Аргумент​C помощью другой формулы​​ по возрастанию: …,​​ функций​-1​Введите в строке формул​
  • ​(если еще нет,​ найдено.​​ в ячейках от​​ совпадение. В программе​ (создается массив).​area_num​
• ​вместе с​​Казань​​», группа «​Если вы хотите поэкспериментировать​​41​​искомое_значение​
• ​ массива​​ -2, -1, 0,​​ПОИСКПОЗ​— функция​​ в нее следующую​​ то сначала почитайте​
• ​СУММ(($A$2:$D$9=F2)*СТОЛБЕЦ($A$2:$D$9))​ G1 до G3,​ оно значится как​SMALL​(номер_области) указывает номер​
• ​COLUMNS​​21.04.12​​Выравнивание​​ с функциями подстановки,​​Формула​может быть значением​=НАИБОЛЬШИЙ((«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65));СТРОКА()-СТРОКА($D$65))​ 1, 2, …,​уже вычисленные данные​

​ПОИСКПОЗ​ формулу:​

​ эту статью, чтобы​
​Наконец, все значения из​

• ​ в ячейке H1​​ 0 (ноль). На​​(НАИМЕНЬШИЙ) возвращает n-ое​ области в аргументе​(ЧИСЛСТОЛБ) – для​=»Нижний Новгород = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Нижний​»).​ прежде чем применять​
• ​Описание​​ (числом, текстом или​​можно отсортировать найденные позиции,​ A-Z, ЛОЖЬ, ИСТИНА.​ из ячеек D15​ищет самое маленькое​Нажмите в конце не​ им стать). Для​
• ​ нашей таблицы суммируются​​ отображается цена, выбранная​ этом аргументы ПОИСКПОЗ​​ наименьшее значение в​​reference​ подсчета суммы последнего​
• ​ Новгород»,$B$2:$B$33,0),1)& «, Дата​​Оси​​ их к собственным​Результат​ логическим значением) или​
• ​ чтобы номера найденных​​ Если​​ и D16, то​​ значение, которое больше​​ Enter, а сочетание​ тех, кто понимает,​ (в нашем примере​ на основании трех​ закончились.​
• ​ созданном массиве, а​(ссылка), из которой​ столбца в именованном​

Ловушки INDEX (ИНДЕКС)

​ выставления счета: «​​Подшипники​​ данным, то некоторые​​=ПОИСКПОЗ(39;B2:B5,1;0)​​ ссылкой на ячейку,​ позиций отображались в​тип_сопоставления​ формула преобразится в​​ или равно заданному.​​Ctrl+Shift+Enter​ рекламировать ее не​​ в результате мы​​ аргументов.​

Пример 1: Находим сумму продаж для выбранного месяца

​Номер столбца. Опять же​MATCH​​ нужно вернуть результат.​​ диапазоне;​ & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Нижний Новгород»,$B$2:$B$33,0),3),»m/d/yy»)​Болты​ образцы данных. Некоторые​​Так как точного соответствия​​ содержащую такое значение.​ первых ячейках (см.​опущен, то предполагается,​

​ более компактный и​
​ Требуется сортировка в​

​, чтобы ввести формулу​ нужно -​ получаем «3» то​​Этот пример должен был​​ воспользуемся ПОИСКПОЗ. Искомым​(ПОИСКПОЗ) на базе​Если аргумент​вместе с​3293​

​4​
​ пользователи Excel, такие​

Пример 2: Получаем ссылку на определенную строку, столбец или область

​ нет, возвращается позиция​Просматриваемый_массив​​ файл примера).​​ что он равен​ понятный вид:​​ порядке убывания.​​ не как обычную,​ без нее не​ есть номер столбца,​ показать, как работает​ значением будет ячейка​ этого значения возвращает​area_num​MATCH​​Казань​​4​

​ как с помощью​
​ ближайшего меньшего элемента​

​    Обязательный аргумент. Диапазон ячеек,​​1. Произведем поиск позиции​​ 1.​=ИНДЕКС(B2:E13;D15;D16)​В одиночку функция​ а как формулу​ обходится ни один​

​ в котором находится​
​ функция ИНДЕКС, а​

​ E14, где указано​​ номер строки нужного​​(номер_области) не указан,​(ПОИСКПОЗ) – для​25.04.12​9​ функции ВПР и​

​ (38) в диапазоне​
​ в которых производится​

​ в НЕ сортированном​Если​​Как видите, все достаточно​​ПОИСКПОЗ​ массива.​ сложный расчет в​

​ искомое выражение). Остальное​
​ также какие возможности​

Пример 3: Создаём динамический диапазон, основанный на подсчете

​ наименование параметра, который​​ месяца.​​ то будет выбрана​ поиска имени участника,​=»Москва = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),1)& «,​5​​ ГПР; другие пользователи​​ B2:B5.​ поиск.​

​ списке числовых значений​
​тип_сопоставления​

​ просто!​, как правило, не​Как это на самом​ Excel. Есть, однако,​ просто выбирает и​ предоставляет использование вложения​ мы ищем (ТОВАР).​​Эта формула должна быть​​ 1 область.​

Пример 4: Сортируем столбец с текстовыми данными в алфавитном порядке

​ угадавшего ближайшее значение.​​ Дата выставления счета:​​7​ предпочитают с помощью​2​Тип_сопоставления.​ (диапазон​равен -1, то​​На этой прекрасной ноте​​ представляет особой ценности,​ деле работает:​ одна проблема: эта​ показывает значения из​ функций и массивов.​​ Просматриваемый массив: шапка​​ введена в ячейку​Если аргумент​Итак, давайте обратимся к​​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),3),»m/d/yy»)​​10​ функций индекс и​=ПОИСКПОЗ(41;B2:B5;0)​    Необязательный аргумент. Число -1,​

​B8:B14​ функция ПОИСКПОЗ() находит​ мы закончим. В​ поэтому в Excel​​Функция ИНДЕКС выдает из​​ функция умеет искать​ строки заголовка, что​Функция ИНДЕКС предназначена для​

​ с наименованиями, потому​
​ E4, как формула​

​row_num​ теоретическим сведениям и​
​3331​
​6​

​ ПОИСКПОЗ вместе. Попробуйте​

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

​Позиция значения 41 в​ 0 или 1.​)​ наименьшее значение, которое​ этом уроке Вы​ ее очень часто​ диапазона цен C2:C161​ данные только по​ осуществляется при помощи​

​ создания массивов значений​ что искать система​ массива, нажатием​(номер_строки) или​ практическим примерам по​Казань​8​ каждый из методов​ диапазоне B2:B5​ Аргумент​Столбец Позиция приведен для​ больше либо равно​ познакомились еще с​ используют вместе с​ содержимое N-ой ячейки​ совпадению одного параметра.​

Пример использования функций ИНДЕКС и ПОИСКПОЗ

​ функции ИНДЕКС.​ в Excel и​ будет по слову​Ctrl+Shift+Enter​column_num​ функции​27.04.12​

​11​ и посмотрите, какие​4​тип_сопоставления​ наглядности и не​ чем​ двумя полезными функциями​ функцией​ по порядку. При​ А если у​Отложим в сторону итерационные​ чаще всего используется​

​ ТОВАР. Тип сопоставления:​. А затем скопирована​(номер_столбца) равен нулю,​INDEX​3350​Формула​ из них подходящий​=ПОИСКПОЗ(40;B2:B5;-1)​указывает, каким образом​ влияет на вычисления.​искомое_значениеПросматриваемый_массив​ Microsoft Excel –​ИНДЕКС​ этом порядковый номер​ нас их несколько?​ вычисления и сосредоточимся​ в паре с​ 0.​ с остальные ячейки​ то функция возвратит​(ИНДЕКС). Если у​Казань​

​Описание​ вариант.​Возвращает сообщение об ошибке,​ в Microsoft Excel​Найдем позицию значения 30​должен быть упорядочен​ПОИСКПОЗ​.​

​ нужной ячейки нам​Предположим, что у нас​ на поиске решения,​ другими функциями. Рассмотрим​Синтаксис функции ИНДЕКС закончен.​ диапазона E4:E9.​

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

​ основанного на формулах​ на конкретных примерах​

​ Как в итоге​=INDEX($C$4:$C$9,MATCH(SMALL(COUNTIF($C$4:$C$9,» ROW(E4)-ROW(E$3)),COUNTIF($C$4:$C$9,»​ столбец или всю​ информация или примеры,​3390​=ГПР(«Оси»;A1:C4;2;ИСТИНА)​

​ пустой лист.​ B2:B5 упорядочен не​сопоставляется со значениями​ =ПОИСКПОЗ(30;B8:B14;0)​ ЛОЖЬ, Z-A, …,​

​ИНДЕКС​ИНДЕКС​ Она ищет связку​ по ценам товаров​ массива.​ формул с комбинациями​ выглядит формула, видно​=ИНДЕКС($C$4:$C$9;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ($C$4:$C$9;» СТРОКА(E4)-СТРОКА(E$3));СЧЁТЕСЛИ($C$4:$C$9;»​ строку соответственно.​ пожалуйста, делитесь ими​Казань​

​Поиск слова «Оси» в​Совет:​ по убыванию.​ в аргументе​Формула ищет​ 2, 1, 0,​, разобрали возможности на​возвращает содержимое ячейки,​ названия товара и​ за разные месяцы:​Пример 2. Вместо формулы​ функции ИНДЕКС и​

​ на скриншоте выше.​Урок подготовлен для Вас​Результат – это ссылка,​ в комментариях.​01.05.12​ строке 1 и​    Прежде чем вставлять​#Н/Д​просматриваемый_массив​точное​ -1, -2, …,​ простых примерах, а​ которая находится на​

​ месяца (​

Поиск индекса максимального числа массива в Excel

​Нужно найти и вытащить​ массива мы можем​ других функций для​ Видим, что артикул​ командой сайта office-guru.ru​ которая может быть​Функция​3441​

​ возврат значения из​ данные в Excel,​Примечание:​. По умолчанию в​

​значение 30. Если​ и так далее.​ также посмотрели их​ пересечении заданных строки​НектаринЯнварь​ цену заданного товара​ использовать формулу, основанную​

​ поиска значений при​ 3516 действительно у​Источник: http://blog.contextures.com/archives/2011/01/25/30-excel-functions-in-30-days-24-index/​ использована другими функциями.​INDEX​Казань​ строки 2, находящейся​

​ установите для столбцов​Мы стараемся как​

​ качестве этого аргумента​ в списке его​Функция ПОИСКПОЗ() не различает​ совместное использование. Надеюсь,​ и столбца. Например,​) по очереди во​

exceltable.com

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

​ (​ на функции СУММПРОИЗВ.​ определенных условиях.​ арахиса. Протянем формулу​Перевел: Антон Андронов​Если​(ИНДЕКС) возвращает значение​02.05.12​ в том же​ A – С​ можно оперативнее обеспечивать​ используется значение 1.​

Работа функции ИНДЕКС и ПОИСКПОЗ по нескольким условиям

​ нет, то будет​ РеГИстры при сопоставлении​ что данный урок​ на рисунке ниже​ всех ячейках склеенного​Нектарин​Принцип работы в основном​Необходимо выполнить поиск значения​ на остальные строки​Автор: Антон Андронов​row_num​ или ссылку на​3517​ столбце (столбец A).​ ширину в 250​

​ вас актуальными справочными​В приведенной ниже​ возвращена ошибка #Н/Д.​ текстов.​ Вам пригодился. Оставайтесь​ формула возвращает значение​ из двух столбцов​) в определенном месяце​ такой же, как​ в таблице и​ и проверим. Теперь,​В Excel есть очень​(номер_строки) и​

​ значение. Используйте её​Казань​4​

​ пикселей и нажмите​

Формула из комбинации функций ВЫБОР, ИНДЕКС и ПОИСКПОЗ

​ материалами на вашем​ таблице описано, как​2. Произведем поиск позиции​Если функция ПОИСКПОЗ() не​ с нами и​ из диапазона A1:C4,​ диапазона A2:A161&B2:B161 и​ (​ и в первом​ отобразить заголовок столбца​ меняя артикул товара,​ удобная, но почему-то​column_num​ в сочетании с​08.05.12​=ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ)​

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

​ переведена автоматически, поэтому​ в зависимости от​ возрастанию списке числовых​ то возвращается значение​ Excel.​ пересечении 3 строки​

exceltable.com

Примеры формул с функциями ИНДЕКС и ПОИСКПОЗ СУММПРОИЗВ в Excel

​ ячейки, где нашла​), т.е. получить на​ здесь просто другую​ Готовый результат должен​ кто его купил,​ которая называется ИНДЕКС.​ ячейку, не принадлежащую​ как​Орел​ строке 1 и​(вкладка «​ ее текст может​

Поиск значений по столбцам таблицы Excel

​ аргумента​ значений (диапазон​ ошибки #Н/Д.​Автор: Антон Андронов​ и 2 столбца.​ точное совпадение. По​

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

​ заданному массиву или​MATCH​09.04.12​ возврат значения из​Главная​ содержать неточности и​тип_сопоставления​B31:B37​Произведем поиск позиции в​Функция ПОИСКПОЗ(), английский вариант​Стоит отметить, что номера​ сути, это первый​152​

​ вся формула не​

Формула массива или функции ИНДЕКС и СУММПРОИЗВ

​Небольшое упражнение, которое показывает,​​ что позволяет выдавать​ ссылке, функция​(ПОИСКПОЗ), чтобы создавать​3155​ строки 3, находящейся​», группа «​ грамматические ошибки. Для​.​)​ НЕ сортированном списке​

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

1. ​INDEX​
2. ​ могучие формулы.​Орел​ в том же​Выравнивание​ нас важно, чтобы​Тип_сопоставления​Сортированные списки позволяют искать​ текстовых значений (диапазон​ значения в диапазоне​ задаются относительно верхней​

​ столбец создается виртуально​ с помощью формулы.​

​ формула массива. Однако​ можно добиться одинакового​

​ выделить из массива​

​ по заданным номерам​(ИНДЕКС) сообщит об​Функция​11.04.12​ столбце (столбец B).​»).​ эта статья была​Поведение​ не только точные​B7:B13​ ячеек. Например, если​ левой ячейки диапазона.​ прямо внутри формулы,​ ВПР в чистом​ на этот раз​ результата при использовании​ максимальное число. Рассмотрим​ строки и столбца.​ ошибке​INDEX​3177​7​Плотность​ вам полезна. Просим​1 или опущен​

​ значения (их позицию),​

​)​ в ячейке​ Например, если ту​ а не в​ виде тут не​ мы создаём в​ совершенно разных функций.​ тот же самый​На практике ИНДЕКС редко​#REF!​(ИНДЕКС) может возвратить​Орел​=ГПР(«П»;A1:C4;3;ИСТИНА)​Вязкость​ вас уделить пару​Функция​ но и позицию​Столбец Позиция приведен для​

​А10​ же таблицу расположить​ ячейках листа.​ поможет, но есть​ памяти компьютера столько​Итак, у нас есть​ пример. Попробуем определить​ используется, скорее всего,​(#ССЫЛКА!).​

​ значение или ссылку​

​19.04.12​Поиск буквы «П» в​Температура​ секунд и сообщить,​ПОИСКПОЗ​ ближайшего значения. Например,​ наглядности и не​содержится значение «яблоки»,​ в другом диапазоне,​Плюсы​ несколько других способов​ же массивов значений,​ таблица, и мы​

​ максимальные значения купленного​ из-за того, что​Введите номер строки, и​ на значение. Вы​3357​

Пример формулы поиска значений с функциями ИНДЕКС и ПОИСКПОЗ

​ строке 1 и​0,457​ помогла ли она​находит наибольшее значение,​

​ в списке на​ влияет на вычисления.​ то формула =ПОИСКПОЗ​ то формула вернет​: Не нужен отдельный​ решить эту задачу.​ сколько столбов в​ бы хотели, чтобы​ количества товара, цены​ эти самые номер​ функция​ можете использовать ее,​Орел​ возврат значения из​3,55​ вам, с помощью​ которое меньше или​ картинке ниже нет​Формула для поиска позиции​ («яблоки»;A9:A20;0) вернет 2,​ тот же результат:​ столбец, работает и​Это самый очевидный и​ нашей таблице. Каждый​ формула Excel отвечала​ и суммы.​

Пример формулы функций ИНДЕКС и НЕ

​ строки и столбца​INDEX​ чтобы:​

​28.04.12​ строки 3, находящейся​500​ кнопок внизу страницы.​ равно значению аргумента​ значения 45, но​ значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)​ т.е. искомое значение​

​Если массив содержит только​ с числами и​ простой (хотя и​ раз, в таком​ на вопрос, в​Начнем с количества. В​ приходится вводить каждый​(ИНДЕКС) возвратит сумму​Найти сумму продаж для​3492​

​ в том же​0,525​ Для удобства также​искомое_значение​

​ можно найти позицию​

​Формула находит первое значение​ «яблоки» содержится во​ одну строку или​ с текстом.​ не самый удобный)​ единичном массиве нули​ каком столбце (или​ любой ячейке под​ раз. Ведь искомое​ продаж из этой​ выбранного месяца.​Орел​ столбце. Так как​3,25​ приводим ссылку на​.​ наибольшего значения, которое​ сверху и выводит​ второй ячейке диапазона​ один столбец, т.е.​Минусы​ способ. Поскольку штатная​ находятся везде, где​ строке, однако в​ этим столбцом пишем​ значение не всегда​ строки. Здесь указан​Получить ссылку на выбранную​06.05.12​ «П» найти не​400​ оригинал (на английском​Просматриваемый_массив​ меньше либо равно,​ его позицию в​

exceltable.com

Функции ИНДЕКС и ПОИСКПОЗ с данными на разных листах (Формулы/Formulas)

​A9:A20А9​​ является вектором, то​: Ощутимо тормозит на​ функция​ искомое значение не​ нашем примере я​ =ИНДЕКС.​ нужно выдавать по​

​ номер месяца​​ строку, столбец или​​3316​​ удалось, возвращается ближайшее​0,606​​ языке) .​

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

excelworld.ru

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

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

​ второй аргумент функции​ больших таблицах (как​ВПР (VLOOKUP)​ было найдено. Однако​ использую столбец), находится​​Первым аргументом у нас​​ порядку. Но тогда​​4​​ область.​Челябинск​ из меньших значений:​2,93​Предположим, что у вас​ по возрастанию: …,​ т.е. позицию значения​ Груши учтено не​ (предполагается, что в​ИНДЕКС​ и все формулы​умеет искать только​ здесь вместо использования​ искомое слово. Думаю,​ будет не просто​ к функции ИНДЕКС​, поэтому результатом будет​

​Создать динамический диапазон, основанный​25.04.12​ «Оси» (в столбце​300​

​ есть списка номеров​ -2, -1, 0,​ 40.​​ будет.​​ ней не содержится​указывает номер значения​​ массива, впрочем), особенно​​ по одному столбцу,​ функции СТОЛБЕЦ, мы​​ что анимация, расположенная​​ массив, а максимальное​ на помощь приходит​ сумма продаж за​ на подсчете.​3346​ A).​0,675​

Способ 1. Дополнительный столбец с ключом поиска

​ офисов расположение и​ 1, 2, …,​Это можно сделать с​Чтобы найти номер строки,​ значение «яблоки»),​​ в этом векторе.​​ если указывать диапазоны​ а не по​ перемножаем каждую такую​ выше, полностью показывает​ число из массива.​ функция ПОИСКПОЗ, которая​

​ апрель (Apr).​Отсортировать столбец с текстовыми​Челябинск​5​2,75​ вам нужно знать,​ A-Z, ЛОЖЬ, ИСТИНА.​ помощью формулы =ПОИСКПОЗ(45;B31:B37;1)​ а не позиции​

​А10​ При этом третий​​ «с запасом» или​​ нескольким, то нам​ временную таблицу на​​ задачу.​​ Поэтому дополнительно используем​ как раз таки​=INDEX($C$2:$C$8,F2)​

​ данными в алфавитном​​28.04.12​=ГПР(«Болты»;A1:C4;4)​250​

​ какие сотрудники являются​​0​Обратите внимание, что тип​ в искомом диапазоне,​- вторая,​ аргумент указывать необязательно.​ сразу целые столбцы​ нужно из нескольких​ введённый вручную номер​​ команду МАКС и​ позволяет найти нужную​

Способ 2. Функция СУММЕСЛИМН

​=ИНДЕКС($C$2:$C$8;F2)​ порядке.​3372​Поиск слова «Болты» в​0,746​ в каждой программы​​Функция​​ сопоставления =1 (третий​ можно записать следующую​А11​Например, следующая формула возвращает​ (т.е. вместо A2:A161​ сделать один!​ столбца.​Пример 1. Первая идя​ выделяем соответствующий массив.​ позицию.​Чтобы сделать эту формулу​Функция​Челябинск​ строке 1 и​

​2,57​​ office. Электронную таблицу​ПОИСКПОЗ​ аргумент функции).​ формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)​- третья и​

​ пятое значение из​​ вводить A:A и​Добавим рядом с нашей​Пример 3. Третий пример​ для решения задач​В принципе, нам больше​Рассмотрим интересный пример, который​ более гибкой, Вы​

Способ 3. Формула массива

​INDEX​01.05.12​​ возврат значения из​​200​​ огромный, поэтому вы​​находит первое значение,​3. Поиск позиции в​Если искомое значение не​ т.д. (подсчет позиции​ диапазона A1:A12 (вертикальный​ т.д.) Многим непривычны​ таблицей еще один​ – это также​ типа – это​ не нужны никакие​

1. ​ позволит понять прелесть​ можете использовать функцию​(ИНДЕКС) имеет две​
2. ​3414​ строки 4, находящейся​0,835​
   
3. ​ думаете, что он​ равное аргументу​​ списке отсортированном по​​ обнаружено в списке,​ производится от верхней​ вектор):​ формулы массива в​

​ столбец, где склеим​ формула массива.​

​ при помощи какого-то​ аргументы, но требуется​ функции ИНДЕКС и​MATCH​ синтаксические формы –​Челябинск​ в том же​2,38​ является довольно сложной​искомое_значение​​ убыванию выполняется аналогично,​​ то будет возвращено​ ячейки).​Данная формула возвращает третье​ принципе (тогда вам​ название товара и​Все точно так же,​ вида цикла поочерёдно​ ввести номер строки​ неоценимую помощь ПОИСКПОЗ.​(ПОИСКПОЗ) для определения​ массива и ссылочная.​01.05.12​ столбце (столбец C).​

​150​​ задачи. Это задача несложная​.​ но с типом​ значение ошибки #Н/Д.​

​Функция ПОИСКПОЗ() возвращает позицию​​ значение из диапазона​ сюда).​ месяц в единое​ как и в​ прочитать каждую ячейку​ и столбца. В​ Имеем сводную таблицу,​ номера строки по​ Форма массива возвращает​3451​11​0,946​ делать с помощью​

planetaexcel.ru

​Просматриваемый_массив​

Поиск нужных данных в диапазоне

Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы — загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.

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

Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.

Задача решается при помощи двух функций:

=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16. Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.

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

Ссылки по теме

• Использование функции ВПР (VLOOKUP) для поиска и подстановки значений.
• Улучшенная версия функции ВПР (VLOOKUP)
• Многоразовый ВПР

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

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

Пример 1. В турнирной таблице хранятся данные о сыгранных футбольных матчах для нескольких команд. Определить:

1. Сколько очков заработала команда (поиск по названию) на данный момент.
2. Суммарное значение очков, заработанных всеми командами.
3. Сколько игр было сыграно какой-либо командой.

Вид исходной таблицы данных:

Для удобства в ячейках A11, A13 и A15 созданы выпадающие списки, элементы которых выбраны из диапазонов ячеек B1:E1 (для A11) и A2:A9 (для A13 и A15), содержащих названия команд. Для создания первого выпадающего списка необходимой перейти курсором на ячейку A11. Выбрать вкладку «ДАННЫЕ» ленты меню, найти секцию с инструментами «Работа с данными» и выбрать инструмент «Проверка данных»:

В открывшемся диалоговом окне необходимо выбрать «Тип данных:» — «Список» и указать в поле «Источник» диапазон ячеек:

Переходим в ячейку A13 и выполняем аналогичные действия только лишь указываем другую ссылку на диапазон в поле «Источник:»

Такой же выпадающий список следует создать и для ячейки A15.

Для подсчета общего количества очков в ячейке B11 используем формулу:

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

Результат расчетов:

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

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

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

Пример расчета:

В результате мы получили значение по 2-м критериям:

1. – «Челси».
2. – «Очки».



Динамическое суммирование диапазона ячеек по критерию в Excel

Пример 2. Используя таблицу из предыдущего примера определить суммарное количество заработанных очков несколькими командами (задается опционально).

Вид таблицы данных:

Искомое значение может быть найдено с помощью следующей формулы:

Функция СУММ рассчитывает сумму значений, хранящихся в столбце «Очки», при этом количество ячеек для расчета может быть задано с помощью критерия – выбранного названия команды. Функция ИНДЕКС может возвращать не только значение, хранящееся в искомой ячейке, но и ссылку на эту ячейку. Поэтому можно использовать запись типа E2:ИНДЕКС(…). В результате выполнения функция ИНДЕКС вернет ссылку на ячейку, и приведенная выше запись примет, например, следующий вид: E2:E4 (если выбрана команда «Манчестер Ю.».

Пример расчетов:

Проверим результат выборочного динамического суммирования столбца таблицы с верху вниз.

Сумма чисел в диапазоне E2:E7 и в ячейке B13 совпадает все ОК.

Подсчет количества рабочих дней в Excel по условию начальной даты

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

Вид таблицы данных:

Как видно на рисунке в ячейке A10 снова используется выпадающий список, созданный по аналогичной схеме, описанной выше.

Для определения искомого значения даты используем следующую формулу (формула массива CTRL+SHIFT+ENTER):

Первая функция ИНДЕКС выполняет поиск ячейки с датой из диапазона A1:I1. Номер строки указан как 1 для упрощения итоговой формулы. Функция СТОЛБЕЦ возвращает номер столбца с ячейкой, в которой хранится первая запись о часах работы. Выражение «ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>»»» выполняет поиск первой непустой ячейки для выбранной фамилии работника, указанной в ячейке A10 (<>”” – не равно пустой ячейке). Второй аргумент «ПОИСКПОЗ(A10;A1:A6;0)» возвращает номер строки с выбранной фамилией, а «ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>»»» — номер позиции значения ИСТИНА в массиве (соответствует номеру столбца), полученном в результате операции сравнения с пустым значением.

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

Для автоматического подсчета количества только рабочих дней начиная от даты приема сотрудника на работу, будем использовать функцию ЧИСТРАБДНИ:

Для проверки выберем другую фамилию сотрудника из выпадающего списка в ячейке A9:

Особенности работы с формулами ИНДЕКС И ПОИСКПОЗ в Excel

Функция ИНДЕКС может возвращать ссылку или массив значений из одного диапазона или нескольких несмежных диапазонов, принимая на вход ссылку на области ячеек или константу массива. При этом последующие аргументы позволяют указать номера интересующих строки и столбца относительно выбранного диапазона, а также порядковый номер диапазона (если диапазоны ячеек не являются смежными, например, при поиске в различных таблицах). В простейшем случае функция ИНДЕКС возвращает значение, хранящееся в ячейке на пересечении строки и столбца. Например, =ИНДЕКС(A2:B5;2;2) вернет значение, которое хранится в ячейке B3, поскольку третья строка является второй по счету относительно ячейки A2, а столбец B:B является вторым относительно столбца A:A.

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

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

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

=ИНДЕКС(диапазон; ПОИСКПОЗ(аргументы); ПОИСКПОЗ(аргументы))

Такая формула используется чаще всего для поиска сразу по двум критериям.

ПОИСКПОЗ в Excel

Автор: Неструев Саня · Опубликовано 15.02.2019 · Обновлено 15.02.2019

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

Мы рассмотрим следующие темы:

• назначение и описание ПОИСКПОЗ;
• синтаксис формулы;
• пошаговая инструкция по набору;
• примеры использования;
• комбинации с другими командами в «Экселе».

Назначение ПОИСКПОЗ

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

На английском написание команды в «Эксель» выглядит как MATCH. Чаще всего используется в качестве вспомогательной функции вкупе с ИНДЕКС, ВПР, ВЫБОР и другими. Однако мы рассмотрим и одиночное использование.

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

Формула включает в себя следующие элементы:

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

Итак, финальный вариант формулы выглядит следующим образом:

Если вам необходимо одиночное использование функции, то она должна иметь строго указанный вид. В противном случае вы можете получить неверный результат или ошибку Н/Д.

Типы сопоставления

Рассмотрим подробнее разницу между несколькими типами сопоставления:

• 1 или без указания данного параметра – определяет наибольшее значение, которое максимально приближено к указанному в формуле;
• 0 – первое значение в диапазоне, которое равно искомому;
• -1 – наименьшее значение относительно искомого.

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

Ввод команды в Excel

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

1. Сверху над основной таблицей находим иконку функции и жмем ее.

1. В открывшемся окне нужно выбрать категорию «Ссылки и массивы», затем найти нужный вариант в списке и нажать «ОК».

1. Теперь появятся поля для ввода данных. Заполнять их можно вручную или указывать с помощью выделения ячеек таблицы.

Для редактирования формулы можно использовать верхнюю строку. С помощью кнопки креста удаляется введенная строка.

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

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

Поиск по одному критерию

Начнем с простого варианта – это поиск с одним искомым значением. Работает по строкам и по столбцам. Также можно использовать массив в виде таблицы. Для нахождения позиции значения делаем следующее:

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

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

1. Теперь указываем тип сопоставления. Если хотим найти точное совпадение, вписываем 0.

1. Жмем клавишу Enter и видим готовый результат.

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

Подобным образом функция работает и для текстовых значений. Видим таблицу с месяцами года. Допустим, хотим узнать расположение августа относительно данного массива. Так же указываем ссылку на ячейку с названием месяца, затем выделяем массив и добавляем тип сопоставления. Если речь идет о текстовых значениях, то необходимо ставить 0.

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

Поиск по двум критериям

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

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

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

Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.

Для вычисления позиции необходимо воспользоваться комбинацией Ctrl + Shift + Enter . Так вы запустите выполнение функции в массиве.

Взаимодействие с другими операторами

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

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

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

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

Последний аргумент – еще одна ПОИСКПОЗ, но теперь с ссылками на название марки и строкой с автомобилями:

Жмем на Enter и видим результат в соответствующей ячейке. Проверяем по нашей таблице.

Теперь можно менять значения в нашей таблице справа и получать новые значения:

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

По представленному примеру описываемый оператор может использоваться во многих формулах. Например:

• ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ));
• ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА;(СОВПАД))));
• ИНДЕКС(ПОИСКПОЗ(МИН(ЕСЛИ))) и другие.

Если вы знакомы с функционалом VBA для Microsoft Excel, то сможете создать надстройку для автоматического вычисления и поиска необходимых значений.

Видеоинструкция

Подробно и наглядно про данную возможность Excel рассказывается в представленном видеоролике.

Заключение

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

Функции MS Excel ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) – более гибкая альтернатива ВПР

Многие пользователи знают и применяют формулу ВПР. Известно также, что ВПР имеет ряд особенностей и ограничений, которые несложно обойти. Однако есть нюанс, который значительно ограничивает возможности функции ВПР.

ВПР требует, чтобы в диапазоне с искомыми данными столбец критериев всегда был первым слева. Это обстоятельство, конечно, является ограничением ВПР. Как же быть, если искомые данные находятся левее столбца с критерием? Можно, конечно, расположить столбцы в нужном порядке, что в целом, является неплохим выходом из ситуации. Но бывает так, что сделать этого нельзя, или трудно. К примеру, вы работаете в чужом документе или регулярно получаете новый отчет. В общем, нужно решение, не зависящее от расположения столбцов. Такое решение существует.

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

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

Следует обратить внимание на корректность ссылок, чтобы при копировании формулы ничего не «съехало». Протягиваем формулу вниз. Если в таблице, откуда подтягиваются данные, нет искомого критерия, то функция выдает ошибку #Н/Д.

Довольно стандартная ситуация, с которой успешно справляется функция ЕСЛИОШИБКА. Она перехватывает ошибки и вместо них выдает что-либо другое, например, нули.

Конструкция формулы будет следующая:

Вот, собственно, и все.

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

Ниже видеоурок по работе функций ИНДЕКС и ПОИСКПОЗ.

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

В этой статье рассматривается синтаксис функции ПОИСКПОЗ Excel, а также как использовать функцию ПОИСКПОЗ в Excel с примерами формул.

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

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

Функция ПОИСКПОЗ в Excel – синтаксис и использование

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

Синтаксис формулы ПОИСКПОЗ выглядит следующим образом:

Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) – определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

• 1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
• 0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
• -1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ, основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:

=ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel

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

Особенности функции ПОИСКПОЗ Excel

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

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

Использование ПОИСКПОЗ в Excel – примеры формул

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

Формула ПОИСКПОЗ Excel с подстановочными знаками

Как и многие другие функции Excel, ПОИСКПОЗ распознает следующие подстановочные знаки:

• Вопросительный знак (?) — заменяет любой символ
• Звездочка (*) — заменяет любую последовательность символов

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

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

Предположим, что у вас есть список компаний и данные их продаж за последнюю неделю. Вы хотите найти относительное положение определенной компании в списке (отсортированному по объему продаж в порядке убывания), но вы не можете точно вспомнить его имя, хотя вы помните несколько первых букв.

Предполагая, что названия компаний находятся в диапазоне A2:A8, и вы ищете название, начинающееся с «восток», формула выглядит следующим образом:

=ПОИСКПОЗ(«восток*»; A2:A8; 0)

Чтобы сделать нашу формулу ПОИСКПОЗ более универсальной, вы можете ввести значение поиска в любой ячейке (E1 в этом примере) и объединить эту ячейку с подстановочным символом, например:

=ПОИСКПОЗ(E1&»*»; A2:A8; 0)

Как показано на изображении ниже, формула возвращает 2 позицию, которая соответствует компании « Восток-авто »:

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

Чтобы заменить только один символ в значении поиска, используйте подстановочный оператор «?», например:

Вышеуказанная формула будет соответствовать названию « Бейкер » и вернет его относительное положение, которое равно 5.

Формула ПОИСКПОЗ с учетом регистра

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

Чувствительность к регистру формулы для сопоставления данных в Excel заключается в следующем:

Логика этой формулы такова:

• Функция СОВПАД сравнивает значение поиска с каждым элементом просматриваемого массива. Если сравниваемые ячейки в точности равны, функция возвращает ИСТИНУ, или ЛОЖЬ в противном случае.
• Далее функция ПОИСКПОЗ сравнивает ИСТИНУ (которая является ее искомым_значением) с каждым значением в массиве, возвращаемом СОВПАД, и возвращает позицию первого совпадения

Обратите внимание , что это формула массива, которая требует нажатия Ctrl+Shift+Enter для правильной обработки.

Пусть значение поиска находится в ячейке E1, а массив поиска — A2:A8, тогда формула выглядит следующим образом:

=ПОИСКПОЗ(ИСТИНА; СОВПАД(A2:A8; E1);0)

На следующем изображении демонстрируется формула ПОИСКПОЗ с учетом регистра в Excel:

Функция ПОИСКПОЗ в Excel – Пример функции ПОИСКПОЗ Excel с учетом регистра

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

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

=ЕСЛИ(ЕНД(ПОИСКПОЗ (1-е значение в Списке 1; Список 2; 0)); «Нет в Списке 1»; «»)

Для любого значения из Списка 2, отсутствующего в Списке 1, формула возвращает «Нет в Списке 1». И вот как это происходит:

• Функция ПОИСКПОЗ выполняет поиск значения из Списка 1 в Списке 2. Если значение найдено, она возвращает относительное положение, в противном случае ошибку #Н/Д.
• Функция ЕНД в Excel выполняет только одно: проверяет наличие ошибок #Н/Д (что означает «недоступно»). Если заданное значение является ошибкой #Н/Д, функция возвращает ИСТИНА, в противном случае – ЛОЖЬ. В нашем случае ИСТИНА означает, что значение из Списка 1 не найдено в Списке 2 (ПОИСКПОЗ возвращает ошибку #Н/Д).
• Поскольку для других пользователей будет сложно интерпретировать результаты в формате ИСТИНА/ЛОЖЬ, то мы будем использовать функцию ЕСЛИ для отображения текста «Нет в Списке 1» для значений, которых нет в Списке 1.

Предположим, что нам необходимо сравнить значения в столбце B со значениями в столбце A, формула примет следующую форму (где B2 — самая верхняя ячейка):

=ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0));»Нет в Списке 1″; «»)

Как вы помните, функция ПОИСКПОЗ Excel сама по себе не учитывает регистр. Чтобы заставить ее различать регистр, вставьте функцию СОВПАД в аргумент просматриваемый_массив и не забудьте нажать Ctrl+Shift+Enter , чтобы завершить эту формулу массива :

=ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A;B2); 0));»Нет в Списке 1″; «»)

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

Функция ПОИСКПОЗ в Excel – Сравнение 2 столбцов на совпадения с использованием ЕНД и ПОИСКПОЗ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

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

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

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

. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.

Вариант 2. Извлечение данных из двумерного диапазона

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

=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )

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

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

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

Обратите особое внимание, что в этом случае первый аргумент – список диапазонов — заключается в скобки, а сами диапазоны перечисляются через точку с запятой.

Вариант 4. Ссылка на столбец / строку

Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

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

Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте — это сочетание с функцией СЧЁТЗ (COUNTA) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.

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

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.

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

Аргументы функции ПОИСКПОЗ описаны ниже.

Искомое_значение. Обязательный аргумент. Значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а нужным значением будет номер телефона.

Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.

Просматриваемый_массив Обязательный аргумент. Диапазон ячеек, в которых производится поиск.

Тип_сопоставления. Необязательный аргумент. Число -1, 0 или 1. Аргумент тип_сопоставления указывает, каким образом в Microsoft Excel искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. По умолчанию в качестве этого аргумента используется значение 1.

В приведенной ниже таблице описано, как функция находит значения в зависимости от аргумента тип_сопоставления.