Полосы сближаются между собой ошибка

В этой статье описаны наиболее распространенные причины появления ошибки «#N/Д» в результате функций ИНДЕКСили MATCH.
 

Примечание: Если вы хотите, чтобы функция ИНДЕКС или НАЙТИВВЕРХ возвращала осмысленное значение, а не #N/Д, используйте функцию ЕСЛИERROR, а затем вложенные в нее функции ИНДЕКС и MATCH. Замена #N/A собственным значением только определяет ошибку, но не устраняет ее. Поэтому очень важно перед использованием ifERRORубедиться, что формула работает правильно.

Проблема: Нет соответствий

Если функция ПОИСКПОИСКОМ не находит искомого значения в массиве искомого массива, возвращается #N/Д.

Если вы считаете, что данные есть в электронных таблицах, но поиск поиску по поиску не удается найти, это может быть по причине:

• Ячейка содержит непредвиденные символы или скрытые пробелы.

• К ячейке применен неправильный формат данных. Например, ячейка содержит числовое значение, но отформатирована как текстовая.
   

РЕШЕНИЕ.Чтобы удалить непредвиденные символы или скрытые пробелы, используйте функции CLEAN и TRIM соответственно. Кроме того, убедитесь, что ячейки отформатированы как правильные типы данных.

Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД

При использовании массива в функции ИНДЕКС,НАЙТИВ ИЛИ сочетании этих двух функций необходимо нажать клавиши CTRL+SHIFT+ВВОД. Excel автоматически заключит формулу в фигурные скобки {}. Если вы попытаетесь ввести квадратные скобки самостоятельно, Excel отобразит формулу как текст.

Примечание: Если у вас есть текущая версия Microsoft 365 ,можно просто ввести формулу в выходную ячейку, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей: сначала выберем диапазон вывода, введите формулу в ячейку вывода, а затем нажимая CTRL+SHIFT+ВВОД, чтобы подтвердить ее. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

• Если match_type 1 или не указан, значения в lookup_array должны быть в порядке возрастания. Примеры: -2, -1, 0, 1, 2…; А, Б, В…; ЛОЖЬ, ИСТИНА и т. д.

• Если match_type -1, значения в lookup_array должны быть упорядочены по убытию.

В следующем примере функция MATCH имеет следующий

=ПОИСКПОЗ(40;B2:B10;-1)

Аргумент match_type в синтаксис имеет значение -1, то есть для формулы должен быть порядок значений в B2:B10 в порядке убытания. Но значения порядок в порядке возрастания, что приводит к #N/A.

Решение: Измените match_type на 1 или отсортирование таблицы в формате «нисходящее». Затем попробуйте еще раз.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

См. также

Исправление ошибки #Н/Д

Использование функций индекса и функции MATCH с несколькими условиями в Excel

ИНДЕКС

ПОИСКПОЗ

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах

Все функции Excel (по алфавиту)

Функции Excel (по категориям)

Ошибки в Excel

Если Excel не может правильно оценить формулу или функцию рабочего листа; он отобразит значение ошибки – например, #ИМЯ?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ССЫЛКА! – в ячейке, где находится формула. Разберем типы ошибок в Excel, их возможные причины, и как их устранить.

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ?:

1. Если в формуле используется имя, которое было удалено или не определено.

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

1. Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки: проверьте правильность написания функции.

1. В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

1. В формуле используется текст, не заключенный в двойные кавычки. Excel выдает ошибку, так как воспринимает такой текст как имя.

Ошибки в Excel – Ошибка в объединении текста с числом

Устранение ошибки: заключите текст формулы в двойные кавычки.

Ошибки в Excel – Правильное объединение текста

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки: проверьте корректность введенных аргументов в функции.

1. Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибки в Excel – Ошибка в формуле из-за слишком большого значения

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

Ошибка #ЗНАЧ!

Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.

Причины ошибки #ЗНАЧ!:

1. Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:

Ошибки в Excel – Суммирование числовых и текстовых значений

Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.

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

Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

Устранение ошибки: укажите в функции правильные аргументы.

1. При использовании формулы массива нажимается клавиша Enter и Excel выводит ошибку, так как воспринимает ее как обычную формулу.

Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter .

Ошибки в Excel – Использование формулы массива

Ошибка #ССЫЛКА

В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.

Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А

Устранение ошибки: измените формулу.

Ошибка #ДЕЛ/0!

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

Ошибки в Excel – Ошибка #ДЕЛ/0!

Устранение ошибки: исправьте формулу.

Ошибка #Н/Д

Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.

Причины ошибки #Н/Д:

1. При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:

Ошибки в Excel – Искомого значения нет в просматриваемом массиве

Устранение ошибки: задайте правильный аргумент искомое значение.

1. Ошибки в использовании функций ВПР или ГПР.

Устранение ошибки: см. раздел посвященный ошибкам функции ВПР

1. Ошибки в работе с массивами: использование не соответствующих размеров диапазонов. Например, аргументы массива имеют меньший размер, чем результирующий массив:

Ошибки в Excel – Ошибки в формуле массива

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

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

Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента

Устранение ошибки: введите все необходимые аргументы функции.

Ошибка #ПУСТО!

Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.

Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны

Устранение ошибки: проверьте правильность написания формулы.

Ошибка ####

Причины возникновения ошибки

1. Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.

Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке

Устранение ошибки: увеличение ширины столбца/столбцов.

1. Ячейка содержит формулу, которая возвращает отрицательное значение при расчете даты или времени. Дата и время в Excel должны быть положительными значениями.

Ошибки в Excel – Разница дат и часов не должна быть отрицательной

Устранение ошибки: проверьте правильность написания формулы, число дней или часов было положительным числом.

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

Функция ПОИСКПОЗ( ) , английский вариант MATCH(), возвращает позицию значения в диапазоне ячеек. Например, если в ячейке А10 содержится значение «яблоки», то формула =ПОИСКПОЗ («яблоки»;A9:A20;0) вернет 2, т.е. искомое значение «яблоки» содержится во второй ячейке диапазона A9:A20 : А9 — первая ячейка (предполагается, что в ней не содержится значение «яблоки»), А10 — вторая, А11 — третья и т.д. (подсчет позиции производится от верхней ячейки) .

Функция ПОИСКПОЗ() возвращает позицию искомого значения, а не само значение. Например: ПОИСКПОЗ(«б»;<«а»;»б»;»в»;»б»>;0) возвращает число 2 — относительную позицию буквы «б» в массиве <«а»;»б»;»в»;»б»>. Позиция второй буквы «б» будет проигнорирована, функция вернет позицию только первой буквы. О том как вернуть ВСЕ позиции искомого значения читайте ниже в разделе Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию .

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

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

Искомое_значение — значение, используемое при поиске значения в просматриваемом_массиве . Искомое_значение может быть значением (числом, текстом или логическим значением (ЛОЖЬ или ИСТИНА)) или ссылкой на ячейку, содержащую число, текст или логическое значение.

Просматриваемый_массив — непрерывный диапазон ячеек, возможно, содержащих искомые значения. Просматриваемый_массив может быть только одностолбцовым диапазоном ячеек, например А9:А20 или диапазоном, расположенным в одной строке, например, А2:Е2 . Таким образом формула =ПОИСКПОЗ(«слива»;A30:B33;0) работать не будет (выдаст ошибку #Н/Д), так как Просматриваемый_массив представляет собой диапазон ячеек размещенный одновременно в нескольких столбцах и нескольких ячейках.

Тип_сопоставления — число -1, 0 или 1. Тип_сопоставления указывает, как MS EXCEL сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив.

• Если тип_сопоставления равен 0, то функция ПОИСКПОЗ() находит первое значение, которое в точности равно аргументу искомое_значение . Просматриваемый_массив может быть не упорядочен.
• Если тип_сопоставления равен 1, то функция ПОИСКПОЗ() находит наибольшее значение, которое меньше либо равно, чем искомое_значение . Просматриваемый_массив должен быть упорядочен по возрастанию: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА. Если тип_сопоставления опущен, то предполагается, что он равен 1.
• Если тип_сопоставления равен -1, то функция ПОИСКПОЗ() находит наименьшее значение, которое больше либо равно чем искомое_значение . Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, . 2, 1, 0, -1, -2, . и так далее.

Функция ПОИСКПОЗ() не различает РеГИстры при сопоставлении текстов.

Если функция ПОИСКПОЗ() не находит соответствующего значения, то возвращается значение ошибки #Н/Д.

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

Произведем поиск позиции в НЕ сортированном списке текстовых значений (диапазон B7:B13 )

Столбец Позиция приведен для наглядности и не влияет на вычисления.

Формула для поиска позиции значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)

Формула находит первое значение сверху и выводит его позицию в диапазоне, второе значение Груши учтено не будет.

Чтобы найти номер строки, а не позиции в искомом диапазоне, можно записать следующую формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)

Если искомое значение не обнаружено в списке, то будет возвращено значение ошибки #Н/Д. Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0) вернет ошибку, т.к. значения «грейпфрут» в диапазоне ячеек B7:B13 нет.

В файле примера можно найти применение функции при поиске в горизонтальном массиве.

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

Поиск позиции можно производить не только в диапазонах ячеек, но и в массивах констант . Например, формула =ПОИСКПОЗ(«груши»;<«яблоки»;»ГРУШИ»;»мандарины»>;0) вернет значение 2.

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

Если искомое значение точно не известно, то с помощью подстановочных знаков можно задать поиск по шаблону, т.е. искомое_значение может содержать знаки шаблона: звездочку (*) и знак вопроса (?). Звездочка соответствует любой последовательности знаков, знак вопроса соответствует любому одиночному знаку.

Предположим, что имеется перечень товаров и мы не знаем точно как записана товарная позиция относящаяся к яблокам: яблоки или яблоко .

В качестве критерия можно задать «яблок*» и формула =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию текстового значения, начинающегося со слова яблок (если она есть в списке).

Подстановочные знаки следует использовать только для поиска позиции текстовых значений и Типом сопоставления = 0 (третий аргумент функции).

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

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

Рассмотрим список с повторяющимися значениями в диапазоне B66:B72 . Найдем все позиции значения Груши .

Значение Груши находятся в позициях 2 и 5 списка. С помощью формулы массива

можно найти все эти позиции. Для этого необходимо выделить несколько ячеек (расположенных вертикально), в Строке формул ввести вышеуказанную формулу и нажать CTRL+SHIFT+ENTER . В позициях, в которых есть значение Груши будет выведено соответствующее значение позиции, в остальных ячейках быдет выведен 0.

можно отсортировать найденные позиции, чтобы номера найденных позиций отображались в первых ячейках (см. файл примера ).

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

1. Произведем поиск позиции в НЕ сортированном списке числовых значений (диапазон B8:B14 )

Столбец Позиция приведен для наглядности и не влияет на вычисления.

Найдем позицию значения 30 с помощью формулы =ПОИСКПОЗ(30;B8:B14;0)

Формула ищет точное значение 30. Если в списке его нет, то будет возвращена ошибка #Н/Д.

2. Произведем поиск позиции в отсортированном по возрастанию списке числовых значений (диапазон B31:B37 )

Сортированные списки позволяют искать не только точные значения (их позицию), но и позицию ближайшего значения. Например, в списке на картинке ниже нет значения 45, но можно найти позицию наибольшего значения, которое меньше либо равно, чем искомое значение, т.е. позицию значения 40.

Это можно сделать с помощью формулы =ПОИСКПОЗ(45;B31:B37;1)

Обратите внимание, что тип сопоставления =1 (третий аргумент функции).

3. Поиск позиции в списке отсортированном по убыванию выполняется аналогично, но с типом сопоставления = -1. В этом случае функция ПОИСКПОЗ() находит наименьшее значение, которое больше либо равно чем искомое значение.

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

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

Найдем количество заданного товара на определенном складе. Для этого используем формулу

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

СОВЕТ: Подробнее о поиске позиций можно прочитать в соответствующем разделе сайта: Поиск позиции .

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

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

Мне необходимо перевести таблицу из вертикального формата (вся информация о наблюдении находится в одной строке(например, национальность — год рождения — средний вес) в горизонтальный (напр. ось Х — национальность, У — год рождения, в таблице — средний вес). «Шапка» по обеим осям у меня заполнена. Нашел на англоязычном форуме подробное решение этого вопроса, вот ссылка http://www.exceltactics.com/vlookup-multiple-criteria-using-index-match/ Index = Индекс, Match = Поискпоз в русской версии. Однако функция «поискпоз» выдаём мне #Н/Д. Что самое интересное, если открыть подробную информацию о функции, значение отображается, но в ячейке упорно остаётся #Н/Д. Скриншоты прилагаются. В них отдельно выписана функция ПОИСКПОЗ, так как ошибка именно в ней

Вы вводите формулу, использующую массивы, поэтому для ввода формулы используйте не клавишу ENTER а сочетание клавиш CTRL+SHIFT+ENTER

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

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

Функция ПОИСКПОЗ Excel — пошаговая инструкция

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

Рассмотрим пошагово, как составить формулу ПОИСКПОЗ:

ПОИСКПОЗ(искомое_значение; массив_поиска, [тип_совпадения])

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

Шаг 2. Массив_поиска (обязательно) — диапазон ячеек для поиска.

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

• 1 или опущено (по умолчанию) — найти наибольшее значение в массиве поиска, которое меньше или равно значению поиска. Требуется сортировка массива поиска в порядке возрастания, от меньшего к большему или от А до Я.
• 0 — найти первое значение в массиве, точно равное искомому значению. Никакой сортировки не требуется.
• -1 — найти наименьшее значение в массиве, которое больше или равно искомому значению. Массив поиска должен быть отсортирован в порядке убывания, от наибольшего к наименьшему или от Я до A.

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

=ПОИСКПОЗ(“Елена”; A2:A8;0)

При желании вы можете поместить искомое значение в какую-либо ячейку (E1 в этом примере) и сослаться на эту ячейку в своей формуле:

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

Как вы видите на скриншоте выше, имена расположены не по алфавиту, а потому мы устанавливаем аргументу тип_совпадения значение 0 (точное соответствие). Ведь только этот вариант не требует сортировки в массиве поиска. 

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

4 вещи, которые вы должны знать о функции ПОИСКПОЗ

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

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

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

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

Частичное совпадение с подстановочными знаками

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

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

Примечание. Подстановочные знаки можно использовать только тогда, если для параметра тип_совпадения установлено значение 0.

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

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

Предположим, что наименования товаров находятся в диапазоне A2:A11, и вы ищете имя, начинающееся с «доб». Формула выглядит следующим образом:

=ПОИСКПОЗ(«доб*»; A2:A11;0)

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

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

Как показано на скриншоте ниже, формула возвращает 4, что является позицией «Добрый экзотик»:

Обратите внимание, что напитков «Добрый» в нашем массиве целых три. Но формула останавливает поиск, как только будет найдено первое подходящее соответствие.

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

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

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

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

Вот общая формула с учетом регистра для сопоставления данных:

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

Формула работает по следующей логике:

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

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

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

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

На скриншоте показана формула поиска соответствия с учетом регистра в Excel:

Как видите, формула различает регистр букв и по этой причине «А-201» было признано неподходящим.

ПОИСКПОЗ и несколько условий

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

Предположим, у нас есть список продаж отдельных товаров в нескольких регионах. Нужно найти первую подходящую позицию в списке для нужного товара и заданного региона.

Вот формула ПОИСКПОЗ для нескольких условий:

=ПОИСКПОЗ(1;(B2:B12=G1)*(C2:C12=G2);0)

Давайте разбираться.

Наши два условия мы записываем в виде выражения (B2:B12=G1)*(C2:C12=G2). Первое условие (B2:B12=G1) означает, что мы сравниваем каждое из значений в столбце «Регион» с целью «Север», которая записана в G1. Получаем массив {ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}

Аналогично каждый из товаров сравниваем с «Яблоки» из G2. Аналогично получаем результат {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА}.

После перемножения этих массивов получаем матрицу единиц и нулей: {0:0:0:1:0:0:0:0:1:0:0}

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

И вот в этом массиве функция ПОИСКПОЗ ищет первую единицу. И находит ее в 4-й позиции.

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

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

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

Как сравнить столбцы при помощи ПОИСКПОЗ

Проверка двух списков на совпадения и различия — одна из наиболее распространенных задач в 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 сама по себе нечувствительна к регистру. Чтобы она различала регистр символов, вставьте функцию СОВПАД (EXACT по английски) в аргумент массив_поиска и не забудьте нажать Ctrl + Shift + Enter , чтобы ввести эту формулу массива :

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

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

Быть может, вам также будет интересен этот материал: 5 способов сравнения ячеек в Excel.

Совместное использование ВПР и ПОИСКПОЗ

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

Один из самых досадных недостатков ВПР заключается в том, что она перестает корректно работать после вставки или удаления столбца в таблице поиска. Это происходит потому, что функция ВПР извлекает совпадающее значение на основе указанного вами номера возвращаемого столбца (номер индекса). Поскольку номер индекса «жестко запрограммирован» в формуле, Excel не может изменить его, когда в таблицу добавляются или удаляются новые столбцы.

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

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

Предполагая, что искомое значение находится в ячейке F1, значения таблицы расположены в $A$1:$C$8 (рекомендуется зафиксировать его с помощью абсолютных ссылок на ячейки, если вы планируете копировать формулу), выражение выглядит следующим образом:

=ВПР($G$1;$A$1:$С$8; 2; ЛОЖЬ)

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

Но только до тех пор, пока вы не вставите или не удалите какие-то столбцы:

Как видите, если мы вставляем дополнительный столбец, то формула теперь извлекает неверные данные. Она по-прежнему ссылается на второй столбец, в то время как данные Тест 1 сместились в третий.

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

ПОИСКПОЗ($F$2;$A$1:$С$1;0)

Где:

• F2 — это искомое значение, которое в точности совпадает с именем возвращаемого столбца, т. е. столбца, из которого вы хотите извлечь значение ( Тест 1  в этом примере).
• A1:C1 — массив поиска, содержащий заголовки таблицы.

А теперь запишите эту формулу в аргумент номер_столбца вашей формулы ВПР, например:

=ВПР($G$1;$A$1:$D$8; ПОИСКПОЗ($F$2;$A$1:$С$1;0); ЛОЖЬ)

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

Вставляем столбец с итоговым тестом:

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

=ВПР($G$1;$A$1:$D$8; ПОИСКПОЗ($F$2;$A$1:$D$1;0); ЛОЖЬ)

Использование ГПР и ПОИСКПОЗ

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

Предположим, что искомое значение находится в ячейке B5, данные таблицы — B1:H3, имя возвращаемой строки (значение поиска для ПОИСКПОЗ) — в ячейке A6, а заголовки строк — A1:A3.

Тогда формула выглядит следующим образом:

=ГПР(B5;B1:H3;ПОИСКПОЗ(A6; A1:A3;0);ЛОЖЬ)

Как вы только что видели, комбинация ВПР/ГПР и  ПОИСКПОЗ, безусловно, является улучшением по сравнению с обычными формулами. Однако функция ПОИСКПОЗ не устраняет всех их ограничений и недостатков. В частности, эта формула ВПР по-прежнему не может делать «левый поиск», а ГПР не может выполнять поиск ни в одной строке, кроме самой верхней.

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

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

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

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

mettuon Дата: Четверг, 15.12.2016, 07:39 | Сообщение № 1 Группа: Пользователи Ранг: Участник Сообщений: 56 Репутация: 0 ± Замечаний: 0% ± Excel 2010 Ребят привет. Не могу решить задачку, неправильно выбираются данные по функции ПОИСКПОЗ. Т.е. при выборе материала должна выбираться соответствующая этому материалу строчка, а по факту выбирается фик пойми откуда, почему так? Пример в приложении. К сообщению приложен файл: 4152502.xlsx (20.0 Kb)   Ответить

китин Дата: Четверг, 15.12.2016, 07:49 | Сообщение № 2 Группа: Модераторы Ранг: Экселист Сообщений: 6997 Репутация: 1069 ± Замечаний: 0% ± Excel 2007;2010;2016 1) в ПОИСКПОЗ нет 3 аргумента-тип сопоставления. надо поставить 0 2) диапазоны проверить надо попробуйте так Код =ЕСЛИ(B5=»»;»»;ИНДЕКС(Лист2!$B$3:$B$54;ПОИСКПОЗ(B5;Лист2!$A$3:$A$54;0))) К сообщению приложен файл: 444111.xlsx (20.1 Kb) Не судите очень строго:я пытаюсь научиться ЯД 41001877306852 Сообщение отредактировал китин — Четверг, 15.12.2016, 07:51   Ответить

Nic70y Дата: Четверг, 15.12.2016, 07:50 | Сообщение № 3 Группа: Друзья Ранг: Экселист Сообщений: 8383 Репутация: 2096 ± Замечаний: 0% ± Excel 2010 Код =ЕСЛИ(B5=»»;»»;ИНДЕКС(Лист2!$B$3:$B$54;ПОИСКПОЗ(B5;Лист2!$A$3:$A$54;))) по тому, что ИНДЕКС(Лист2!$B$3:$B$54;ПОИСКПОЗ(B5;Лист2!$A$3:$A$54;)) неправильный диапазон, неправильный тип сопоставления ЮMoney 41001841029809 Сообщение отредактировал Nic70y — Четверг, 15.12.2016, 07:52   Ответить

mettuon Дата: Четверг, 15.12.2016, 07:58 | Сообщение № 4 Группа: Пользователи Ранг: Участник Сообщений: 56 Репутация: 0 ± Замечаний: 0% ± Excel 2010 1) в ПОИСКПОЗ нет 3 аргумента-тип сопоставления. надо поставить 0 2) диапазоны проверить надо попробуйте так Все работает супер, спасибо   Ответить

Исправление ошибки #Н/Д в функциях ИНДЕКС и ПОИСКПОЗ

​Смотрите также​​ номер столбца таблицы​ номер статьи. Например,​ должна подтверждаться как​ каком столбце (или​ будет подсчитана сумма​А2:А13​После того, как все​ наименование самого оператора​«OK»​ имя работника в​Как видим, функция​ сумма выручки от​Прежде всего, нам нужно​«Просматриваемый массив»​указывает точное совпадение​просматриваемый_массив​Примечание:​ относится к 3-тьему​ 7.​ формула массива. Однако​ строке, однако в​

​ первых 5-и значений). ​только 12 строк.​ данные введены, щелкаем​«ПОИСКПОЗ»​.​ третьей строке.​

​ИНДЕКС​​ которого равна 350​​ отсортировать элементы в​​нужно указать координаты​​ нужно искать или​​должен быть в​Мы стараемся как​ магазину (ячейка B15).​В ячейку B14 введите​ на этот раз​​ нашем примере я​​Использование функции ИНДЕКС() в этом​​Пусть имеется одностолбцовый диапазон​​ по кнопке​без кавычек. Затем​Результат будет точно такой​Выделяем ячейку, в которой​при помощи оператора​ рублям или самому​ столбце​​ самого диапазона. Его​​ неточное. Этот аргумент​ порядке убывания.​

Проблема: Нет соответствий

​ можно оперативнее обеспечивать​​Чтобы определить номер столбца,​​ следующую формулу:​ мы создаём в​ использую столбец), находится​ примере принципиально отличается​

​А6:А9.​«OK»​ сразу же открываем​ же, что и​ будет выводиться результат​​ПОИСКПОЗ​​ близкому к этому​«Сумма»​

• ​ можно вбить вручную,​ может иметь три​

• ​В приведенном ниже примере​ вас актуальными справочными​ который содержит заголовок​В результате получаем значение​ памяти компьютера столько​ искомое слово. Думаю,​​ от примеров рассмотренных​​Выведем значение из 2-й​

​.​​ скобку и указываем​ выше.​ обработки. Кликаем по​в заранее указанную​ значению по убыванию.​по убыванию. Выделяем​ но проще установить​ значения:​

Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД

​ — функция​​ материалами на вашем​​ «Магазин 3» следует​​ на пересечении столбца​​ же массивов значений,​ что анимация, расположенная​ выше, т.к. функция​ строки диапазона, т.е.​После этого в предварительно​ координаты искомого значения.​Это был простейший пример,​ значку​ ячейку выводит наименование​ Данный аргумент указан​ данную колонку и​

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

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

• ​ чтобы вы увидели,​​«Вставить функцию»​​«Чай»​ в поле​ переходим во вкладку​​ и выделить этот​​,​=MATCH(40,B2:B10,-1)​ переведена автоматически, поэтому​ Как само название​ 7:​ нашей таблице. Каждый​ задачу.​

• ​ значение, а ссылку​​ можно сделать с​​ результаты вычисления. Там​​ ячейки, в которую​​ как работает данная​, который размещен сразу​

​. Действительно, сумма от​«Приблизительная сумма выручки на​​«Главная»​

​ массив на листе,​

​«0»​​Аргумент​​ ее текст может​ функции говорит о​Как видно значение 40​ раз, в таком​​ (адрес ячейки) на​ помощью формулы =ИНДЕКС(A6:A9;2)​ отображается сумма заработной​ мы отдельно записали​ функция, но на​ слева от строки​

​ реализации чая (300​​ листе»​​. Щелкаем по значку​​ зажимая при этом​и​тип_сопоставления​ содержать неточности и​

У вас есть вопрос об определенной функции?

​ том, что ее​ имеет координаты Отдел​

Помогите нам улучшить Excel

​ единичном массиве нули​Пример 1. Первая идя​ значение. Вышеуказанная формула​Если диапазон горизонтальный (расположен​ платы второго по​ фамилию работника Парфенова.​ практике подобный вариант​

См. также

​ формул.​ рублей) ближе всего​

​.​«Сортировка и фильтр»​ левую кнопку мыши.​«-1»​

​в синтаксисе присваивается​

​ грамматические ошибки. Для​

​ задачей является поиск​ №3 и Статья​

​ находятся везде, где​ для решения задач​

​ =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)​ в одной строке,​ счету работника (Сафронова​

​ Ставим точку с​ её использования применяется​

​Происходит процедура активации​ по убыванию к​

support.office.com

Функция ПОИСКПОЗ в программе Microsoft Excel

​Отсортировываем элементы в столбце​, который расположен на​ После этого его​​. При значении​​ значение -1, это​ нас важно, чтобы​ позиции где находится​ №7. При этом​ искомое значение не​ типа – это​Аналогичный результат можно получить​ например,​ В. М.) за​ запятой и указываем​​ все-таки редко.​​Мастера функций​ сумме 350 рублей​«Сумма выручки»​

​ ленте в блоке​ адрес отобразится в​

Применение оператора ПОИСКПОЗ

​«0»​​ означает, что порядок​​ эта статья была​ значение внутри определенного​​ функцией ИНДЕКС не​​ было найдено. Однако​ при помощи какого-то​ используя функцию СМЕЩ() ​А6:D6​ третий месяц.​ координаты просматриваемого диапазона.​Урок:​. В категории​ из всех имеющихся​по возрастанию. Для​«Редактирование»​ окне аргументов.​оператор ищет только​ значений в B2:​ вам полезна. Просим​ диапазона ячеек. В​

​ учитываются номера строк​​ здесь вместо использования​​ вида цикла поочерёдно​

​=СУММ(СМЕЩ(A2;;;4))​

​), то формула для​Ссылочная форма не так​ В нашем случае​

​Мастер функций в Экселе​​«Ссылки и массивы»​ в обрабатываемой таблице​ этого выделяем необходимый​. В появившемся списке​В третьем поле​ точное совпадение. Если​ B10 должен быть​ вас уделить пару​ нашем случаи мы​ листа Excel, а​ функции СТОЛБЕЦ, мы​ прочитать каждую ячейку​

​Теперь более сложный пример,​​ вывода значения из​ часто применяется, как​ это адрес столбца​На практике функция​данного инструмента или​ значений.​ столбец и, находясь​​ выбираем пункт​​«Тип сопоставления»​

​ указано значение​​ в порядке убывания​ секунд и сообщить,​ ищем значение: «Магазин​ только строки и​ перемножаем каждую такую​​ из нашей таблицы​​ с областями.​​ 2-го столбца будет​​ форма массива, но​​ с именами сотрудников.​​ИНДЕКС​​«Полный алфавитный перечень»​​Если мы изменим число​ во вкладке​«Сортировка от максимального к​​ставим число​​«1»​ формулы для работы.​​ помогла ли она​​ 3», которое следует​ столбцы таблицы в​ временную таблицу на​ и сравнить её​​Пусть имеется таблица продаж​​ выглядеть так =ИНДЕКС(A6:D6;;2)​ её можно использовать​ После этого закрываем​чаще всего применяется​ищем наименование​ в поле​«Главная»​ минимальному»​«0»​, то в случае​ Но значения в​ вам, с помощью​​ еще определить используя​​ диапазоне B2:G10.​ введённый вручную номер​​ с искомым словом.​​ нескольких товаров по​

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

​ диапазоне​​ работе с несколькими​​После того, как все​ПОИСКПОЗ​. После того, как​на другое, то​«Сортировка и фильтр»​​После того, как была​​ работать с текстовыми​

​ПОИСКПОЗ​ которая приводит к​ Для удобства также​ текстовой строки «Магазин​В первом аргументе функции​​Пример 3. Третий пример​​ совпадают, то отображается​Задав Товар, год и​А6:B9.​

Способ 1: отображение места элемента в диапазоне текстовых данных

​ диапазонами, но и​ значения внесены, жмем​. Связка​​ нашли этого оператора,​​ соответственно автоматически будет​, а затем в​ сортировка произведена, выделяем​ данными, и поэтому​выдает самый близкий​ ошибке # н/д.​ приводим ссылку на​​ » и критерий​​ указывается диапазон ячеек​

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

   

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

   

3. ​ Например, её можно​​«OK»​​–​ щелкаем по кнопке​ поля​ по пункту​ выводиться результат, и​ результат.​

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

   ​ 2-м столбце таблицы,​​ применять для расчета​​.​ПОИСКПОЗ​«OK»​«Товар»​«Сортировка от минимального к​ запускаем окно аргументов​После того, как все​ указано значение​тип_сопоставления​В разделе описаны наиболее​ аргументе функции указываем​ значений на пересечении​

   ​ как и в​​ исключением VBA, конечно,​​ =ИНДЕКС((B9:C12;D9:E12;F9:G12);B15;A19;B17)​​ т.е. значение 200.​​ суммы в комбинации​Результат количества заработка Парфенова​является мощнейшим инструментом​, которая размещается в​.​

   ​ максимальному»​ тем же путем,​ данные установлены, жмем​​«-1»​​1 или сортировка​

   

4. ​ распространенные причины «Ошибка​ «Магазин »&B15. Во​ строк и столбцов.​​ первом решении, только​​ потому что там​Вся таблица как бы​ Это можно сделать​ с оператором​ Д. Ф. после​ при работе в​ нижней части окна.​​Урок:​​.​

​ о котором шла​​ на кнопку​

Способ 2: автоматизация применения оператора ПОИСКПОЗ

​, то в случае,​ по убыванию формат​ # н/д» должна​​ втором аргументе функции​​ Во втором аргументе​ запись немного отличается.​

1. ​ дело намного проще),​ разбита на 3​ с помощью формулы​​СУММ​​ обработки выводится в​​ Эксель, который по​​Открывается небольшое окошко, в​​Функция ИНДЕКС в Excel​​Выделяем ячейку в поле​ речь в первом​«OK»​ если не обнаружено​​ таблицы. Затем повторите​​ отображаться, являющееся результатом​​ ПОИСКПОЗ указывается ссылка​​ сначала указываем номер​ Я рекомендую самостоятельно​ на ум приходят​ подтаблицы (области), соответствующие​ =ИНДЕКС(A6:B9;3;2)​.​

   

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

   

3. ​и вызываем​В поле​Программа выполняет вычисление и​​ выдает самый близкий​​Задать вопрос на форуме​​ .​​ A3:J3 где нужно​ в третьем аргументе​ с целью лучше​​ следующие решения:​​B9:C12D9:E12F9:G12​

   

4. ​ «номер_строки» или «номер_столбца»​СУММ​Теперь, если в поле​ ближайший аналог –​ функции:​является очень удобной​Мастер функций​«Искомое значение»​ выводит порядковый номер​​ к нему элемент​​ сообщества, посвященном Excel​Примечание:​ искать исходное значение​ – номер столбца.​ усвоить материал.​

Способ 3: использование оператора ПОИСКПОЗ для числовых выражений

​Итерационные (итеративные) вычисления.​. Задавая номер строки,​​ значение 0, функция​​имеет следующий синтаксис:​«Имя»​

​ оператор​«Массив»​ функцией для определения​обычным способом через​вбиваем число​ позиции​

1. ​ по возрастанию. Важно,​У вас есть предложения​ Если необходимо​​ (указанное в первом​​ На основе этой​И наконец-то, несколько иной​Формулы массива (или такие​​ столбца (в подтаблице)​​ ИНДЕКС() возвратит массив​​=СУММ(адрес_массива)​​мы изменим содержимое​ВПР​​или​​ порядкового номера указанного​ кнопку​​«400»​«Сахар»​​ если ведется поиск​

   

2. ​ по улучшению следующей​ИНДЕКСУ​ аргументе). Третий аргумент​ информации функция ищет​ (не табличный) подход.​ функции, как СУММПРОИЗВ,​ и номер области,​ значений для целого​В нашем конкретном случае​

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

   

3. ​ данных. Но польза​.​«Просматриваемый массив»​​ в той ячейке,​​ а приблизительного, чтобы​​ да, ознакомьтесь с​​ПОИСКПОЗ​ – это значит,​ таблице.​ раз, когда необходимо​ массивами, хотя мы​ объем продаж. В​

​ целой строки (не​ работников за месяц​, на, например,​​ПОИСКПОЗ​​«Массив»​ от него значительно​В открывшемся окне​указываем координаты столбца​ которую мы задали​​ просматриваемый массив был​​ темами на портале​для возврата осмысленные​​ что функция возвратит​​Читайте также: Примеры функции​

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

Способ 4: использование в сочетании с другими операторами

​ всего столбца/строки листа,​ можно вычислить при​«Попова М. Д.»​является указание номера​. Он расположен первым​ увеличивается, если он​Мастера функций​​«Сумма»​​ ещё на первом​ упорядочен по возрастанию​ пользовательских предложений для​ значения вместо #​ результат, как только​ ИНДЕКС и ПОИСКПОЗ​ Excel, моментально на​ с помощью Ctr​​ строка и столбец​​ а только столбца/строки​ помощи следующей формулы:​, то автоматически изменится​ по порядку определенного​ и по умолчанию​

​ применяется в комплексных​

​в категории​. В поле​ шаге данной инструкции.​ (тип сопоставления​​ Excel.​​ н/д, используйте функцию​​ найдет первое совпадение​​ с несколькими условиями​

​ ум приходит пара​​ + Shift +​​ выделены цветом с​​ входящего в массив).​​=СУММ(C4:C9)​ и значение заработной​ значения в выделенном​ выделен. Поэтому нам​ формулах.​«Ссылки и массивы»​«Тип сопоставления»​

​ Номер позиции будет​«1»​Как исправить ошибку #​ IFERROR и затем​ значений. В нашем​ Excel.​ функций: ИНДЕКС и​​ Enter, как в​​ помощью Условного форматирования.​ Чтобы использовать массив​Но можно её немного​ платы в поле​ диапазоне.​ остается просто нажать​Автор: Максим Тютюшев​ищем наименование​​устанавливаем значение​ равен​​) или убыванию (тип​

1. ​ н/д​​ вложить функций​​ примере значение «Магазин​Внимание! Для функции ИНДЕКС​ ПОИСКПОЗ. Результат поиска​ случае с классическими​​Как использовать функцию​​ значений, введите функцию​​ модифицировать, использовав функцию​​«Сумма»​Синтаксис оператора​ на кнопку​​Одной из самых полезных​«ИНДЕКС»​​«-1»​

   

2. ​«4»​​ сопоставления​​Способы использования функций индекс​​индекс​​ 3» находится на​ номера строк и​​ выглядит так:​​ формулами массива).​

   

3. ​ВПР (VLOOKUP)​​ ИНДЕКС() как формулу​​ИНДЕКС​​.​​ПОИСКПОЗ​​«OK»​​ функций программы Эксель​, выделяем его и​​, так как мы​​.​

   

4. ​«-1»​ и ПОИСКПОЗ с​и​​ позиции номер 6​​ столбцов определяют высоту​Формула построена из блоков​Первое решение выглядит следующем​для поиска и​ массива.​. Тогда она будет​Теперь посмотрим, как с​такой:​​.​​ является оператор ИНДЕКС.​

   

5. ​ жмем на кнопку​​ производим поиск равного​​Урок:​​).​​ несколькими условиями в​ПОИСКПОЗ​​ в диапазоне A3:J3,​​ и ширину таблицы,​ и имеет более​ образом:​ выборки нужных значений​​Пусть имеется одностолбцовый диапазон​​ иметь следующий вид:​

   ​ помощью оператора​​=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])​​Открывается окно аргументов функции​ Он производит поиск​​«OK»​​ или большего значения​Мастер функций в Экселе​Аргумент​ Excel​в эту функцию.​ а значит функция​​ указанной в ее​​ или менее следующий​Рассмотрим ближе каким образом​ из списка мы​А6:А9.​​=СУММ(C4:ИНДЕКС(C4:C9;6))​​ИНДЕКС​Искомое значение​​ИНДЕКС​​ данных в диапазоне​.​​ от искомого. После​​Выше мы рассмотрели самый​«Тип сопоставления»​Функция ИНДЕКС​​ Только заменив собственное​​ ПОИСКПОЗ возвращает число​​ первом аргументе. Они​​ вид:​ эта формула работает:​ недавно разбирали. Если​Выведем 3 первых​В этом случае в​можно обработать несколько​– это значение,​. Как выше говорилось,​​ на пересечении указанных​​Далее открывается окошко, которое​ выполнения всех настроек​ примитивный случай применения​​не является обязательным.​​Функция ПОИСКПОЗ​ значение # н/д​ 6 которое будет​ никак не связаны​1*НЕ(ЕОШИБКА(ПОИСКПОЗ(F5;A2:A9;0)))​($A$2:$D$9=F2)​​ вы еще с​​ значения из этого​

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

   

6. ​ Он может быть​​Общие сведения о формулах​​ идентифицирует ошибку, но​​ использовано в качестве​​ с номерами рабочего​Изменяются только выделенные фрагменты.​​Данный фрагмент формулы необходимо​​ ней не знакомы​ диапазона, т.е. на​ указывается ячейка, с​ целей будет применяться​ диапазоне мы ищем;​ три аргумента, а​ возвращая результат в​ оператора​

   

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

​ — загляните сюда,​​А6А7А8​

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

​ критерия функции ВПР.​

lumpics.ru

Функция ИНДЕКС в программе Microsoft Excel

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

​ им.​ ПОИСКПОЗ, мы проверяем​

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

​ искомым словом. Для​​ минут, чтобы сэкономить​​ 3 ячейки (​ координатах указания окончания​​.​​ в котором находится​В поле​ этой функции раскрываются​

​ для ссылки. Нам​ предварительно указанную ячейку.​

​Для удобства на листе​

​ его значение по​Обнаружение ошибок в формулах​ЕСЛИОШИБКА​ четвертый аргумент в​Не всегда таблицы, созданные​ удалось ли найти​ этого формула создаёт​ себе потом несколько​А21 А22А23​ массива используется оператор​Имеем три таблицы. В​ это значение;​«Массив»​ при использовании её​ нужен первый вариант.​ Это позиция​

​ добавляем ещё два​ умолчанию равно​

​ с помощью функции​

​, убедитесь, что формула​ функции ВПР который​ в Excel охарактеризованы​ в столбце искомое​​ в памяти компьютера​​ часов.​​), в Строку формул​​ИНДЕКС​​ каждой таблице отображена​​Тип сопоставления​нужно указать адрес​ в сложных формулах​ Поэтому оставляем в​«3»​

​ дополнительных поля:​«1»​ проверки ошибок​ работает неправильно данное.​ определяет точность совпадения​ тем, что названия​ выражение. Если нет,​​ массив значений, равный​​Если же вы знакомы​ введите формулу =ИНДЕКС(A6:A9;0),​. В данном случае​ заработная плата работников​– это необязательный​ обрабатываемого диапазона данных.​

Способ 1: использование оператора ИНДЕКС для массивов

​ в комбинации с​ этом окне все​. Ей соответствует​​«Заданное значение»​​. Применять аргумент​

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

1. ​ параметр, который определяет,​ Его можно вбить​ другими операторами. Давайте​ настройки по умолчанию​​«Картофель»​​и​«Тип сопоставления»​ алфавиту)​

   

2. ​ПОИСКПОЗ​​ критерием (0-точное совпадение;​​ быть определены только​​ ошибку. С помощью​​ заполненную нулями (фактически​​ — вдогон -​​CTRL+SHIFT+ENTER​​ИНДЕКС​​ Нашей задачей является​ точно или приблизительно​ вручную. Но для​ рассмотрим различные варианты​​ и жмем на​​. Действительно, сумма выручки​«Номер»​

   

3. ​, прежде всего, имеет​Все функции Excel (по​не находит искомое​ 1 или пусто​​ в заголовках столбцов.​​ функции ЕОШИБКА мы​​ значениями ЛОЖЬ, но​​ стоит разобраться с​​.​​указывает на диапазон,​ узнать заработную плату​ искать значения. Мы​ облегчения задачи мы​ её применения.​​ кнопку​​ от реализации этого​

   

4. ​. В поле​​ смысл только тогда,​​ категориям)​ значение в массиве​ – приближенное совпадение),​ Иногда при анализе​ проверяем выдала ли​

   ​ через какое-то время​​ похожими функциями:​​Зачем это нужно? Теперь​ а второй –​ (третий столбец) второго​ будем искать точные​ поступим иначе. Ставим​Скачать последнюю версию​«OK»​ продукта самая близкая​«Заданное значение»​ когда обрабатываются числовые​Одним из наиболее востребованных​ подстановки, возвращается ошибка​ но в формуле​ данных таблицы мы​

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

   ​ имеем возможность пользоваться​ Если да, то​ эти значения, и​​и​​ значения из ячеек​

   

5. ​ ячейку – шестую.​ за третий месяц​ аргумент не используется.​ поле, а затем​Оператор​Открывается окно аргументов функции​ по возрастанию и​ которое нужно найти.​

​ текстовые.​​ Excel является функция​​Если вы считаете, что​ следующей причине. Получив​ как заголовками столбцов,​ мы получаем значение​ Excel автоматически преобразует​ПОИСКПОЗ (MATCH)​А21 А22А23​Урок:​​ (третья область).​​С помощью этого инструмента​ обводим весь диапазон​ИНДЕКС​ИНДЕКС​ составляет 450 рублей.​ Пусть теперь это​В случае, если​ПОИСКПОЗ​​ данные не содержится​​ все аргументы функция​​ так и названиями​​ ИСТИНА. Быстро меняем​​ их на ноль).​​, владение которыми весьма​не удастся, мы​Полезные функции Excel​​Выделяем ячейку, в которой​​ можно автоматизировать введение​ табличных данных на​относится к группе​. В поле​Аналогичным образом можно произвести​ будет​ПОИСКПОЗ​​. В её задачи​​ данных в электронной​

​ ВПР не находит​ строк, которые находятся​ её на ЛОЖЬ​

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

​ функций из категории​​«Массив»​

Способ 2: применение в комплексе с оператором ПОИСКПОЗ

​ поиск и самой​​«Мясо»​​при заданных настройках​ входит определение номера​​ таблице, но не​​ значения 370 000​​ в первом столбце.​​ и умножаем на​​ значение в таблице​​ опытному пользователю Excel.​ изменять часть массива».​ИНДЕКС​ результата и обычным​«Номер строки»​ адрес диапазона тут​«Ссылки и массивы»​​указываем адрес того​​ близкой позиции к​

​. В поле​​ не может найти​​ позиции элемента в​ удалось найти его​ и так как​Пример таблицы табель премии​

​ введённый вручную номер​​ не равно искомому​​ Гляньте на следующий​

​Хотя можно просто ввести​

• ​можно использовать в​​ способом открываем​и​ же отобразится в​
• ​. Он имеет две​​ диапазона, где оператор​«400»​«Номер»​
• ​ нужный элемент, то​​ заданном массиве данных.​СООТВЕТСТВИЕ​ не указан последний​ изображен ниже на​ столбца (значение ИСТИНА​ слову. Как не​ пример:​

​ в этих 3-х​ Экселе для решения​Мастер функций​​«Номер столбца»​​ поле.​​ разновидности: для массивов​​ИНДЕКС​​по убыванию. Только​​устанавливаем курсор и​

​ оператор показывает в​ Наибольшую пользу она​, возможно, так​ аргумент выполняет поиск​ рисунке:​ заменяем на ЛОЖЬ,​ трудно догадаться, в​Необходимо определить регион поставки​ ячейках ссылки на​​ довольно разноплановых задач.​​, но при выборе​​в функцию​​В поле​ и для ссылок.​будет искать название​ для этого нужно​ переходим к окну​ ячейке ошибку​ приносит, когда применяется​ как:​​ ближайшего значения в​​Назначением данной таблицы является​​ потому что нас​​ случае нахождения искомого​

1. ​ по артикулу товара,​ диапазон​ Хотя мы рассмотрели​ типа оператора выбираем​ИНДЕКС​«Номер строки»​

   

2. ​Вариант для массивов имеет​​ продукции. В нашем​​ произвести фильтрацию данных​ аргументов оператора тем​«#Н/Д»​ в комплексе с​​Ячейка содержит непредвиденные символы​​ Excel – 350​

   ​ поиск соответственных значений​​ не интересует ошибка,​​ слова, в памяти​ набранному в ячейку​А6:А8.​ далеко не все​

   ​ ссылочный вид. Это​​.​​ставим цифру​ следующий синтаксис:​ случае – это​ по возрастанию, а​

   ​ же способом, о​​.​​ другими операторами. Давайте​ или скрытые пробелы.​ 000.​​ премии в диапазоне​​ возвращаемая функцией ПОИСКПОЗ.​ компьютера в соответствующем​ C16.​Выделите 3 ячейки​ возможные варианты её​ нам нужно потому,​​Посмотрим, как это можно​​«3»​=ИНДЕКС(массив;номер_строки;номер_столбца)​ столбец​ в поле​ котором шел разговор​При проведении поиска оператор​ разберемся, что же​Ячейка не отформатированное правильный​Поняв принцип действия выше​ B5:K11 на основе​ Также ищем варианты​ месте будет цифра​Задача решается при помощи​ и введите формулу​ применения, а только​ что именно этот​

   ​ сделать на конкретном​, так как по​При этом два последних​​«Наименование товара»​​«Тип сопоставления»​

   

3. ​ выше.​ не различает регистры​ собой представляет функция​ тип данных. Например​​ описанной формулы, на​

   

4. ​ определенной сумы выручки​​ (столбцы), в котором​​ 1.​ двух функций:​​ =A6:A8. Затем нажмите​​ самые востребованные. Существует​​ тип поддерживает работу​​ примере. Работаем все​ условию нам нужно​ аргумента в формуле​​.​​аргументов функции установить​

Способ 3: обработка нескольких таблиц

​В окне аргументов функции​ символов. Если в​​ПОИСКПОЗ​​ ячейка содержит числовые​ ее основе можно​ и магазинов с​ формула не выдала​​СТОЛБЕЦ($A$2:$D$9)​​=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)​

​CTRL+SHIFT+ENTER​ два типа этой​ с аргументом​ с той же​ определить третье имя​ можно использовать, как​В поле​ значение​ в поле​ массиве присутствует несколько​

1. ​, и как её​ значения, но он​ легко составить формулу​ пределами минимальных или​​ ошибку. Потому что​​В то же самое​Функция​и получим тот​ функции: ссылочный и​«Номер области»​ таблицей, о которой​ в списке. В​​ вместе, так и​​«Номер строки»​

   

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

   ​Открывается окно аргументов. В​​ Отдельно у нас​​«Номер столбца»​​ них, если массив​​ функция​Урок:​ в которой вписано​

   ​выводит в ячейку​​Скачать последнюю версию​​текст​​ для продавца из​​ при автоматическом определении​ было найдено. Немного​ другой массив значений​D1:D13​

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

   ​.​ 3-тьего магазина. Измененная​ размера премии, на​​ запутано, но я​​ (тех же размеров,​

   

3. ​значение артикула из​ констант:​ с другими операторами.​«Ссылка»​ поля –​«1»​ диапазоне следует применять​. Её придется вбить​

Способ 4: вычисление суммы

​ в Excel​«Мясо»​ из них.​Оператор​Решение​ формула будет находится​ которую может рассчитывать​ уверен, что анализ​ что и размеры​ ячейки​Формула =ИНДЕКС({«первый»:»второй»:»третий»:»четвертый»};4) вернет текстовое​ Созданные таким способом​​нам нужно указать​​«Имя»​

​, так как колонка​​ оба значения. Нужно​​ вручную, используя синтаксис,​

​Эффективнее всего эту функцию​

​. В полях​Давайте рассмотрим на примере​ПОИСКПОЗ​: чтобы удалить непредвиденные​ в ячейке B17​

​ сотрудник при преодолении​

​ формул позволит вам​ нашей исходной таблицы),​​C16​​ значение четвертый.​ формулы смогут решать​

​ адреса всех трех​

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

​ самые сложные задачи.​​ диапазонов. Для этого​

​«Сумма»​​ первой в выделенном​​ под номером строки​ в самом начале​ операторами в составе​и​ когда с помощью​ функций​ пробелов, используйте функцию​ вид:​ Так как нет​ мыслей.​ для каждой из​ 0 — означает​ в связке с​Автор: Максим Тютюшев​ устанавливаем курсор в​. Нужно сделать так,​ диапазоне.​

​ и столбца понимается​

lumpics.ru

Функция ИНДЕКС() в MS EXCEL

​ статьи. Сразу записываем​ комплексной формулы. Наиболее​«Тип сопоставления»​ПОИСКПОЗ​«Ссылки и массивы»​ ПЕЧСИМВ или СЖПРОБЕЛЫ​Легко заметить, что эта​​ четко определенной одной​​Функция ИНДЕКС предназначена для​ ячеек нашей таблицы.​ поиск точного (а​

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

​ функцией ПОИСКПОЗ(), которая​​Функция ИНДЕКС(), английский вариант​

​ поле и выделяем​​ что при введении​После того, как все​

​ не номер на​​ название функции –​ часто её применяют​указываем те же​можно определить место​. Он производит поиск​ соответственно. Кроме того​

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

​ первый диапазон с​ имени работника автоматически​ указанные настройки совершены,​ координатах листа, а​«ПОИСКПОЗ»​ в связке с​ самые данные, что​ указанного элемента в​

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

Значение из заданной строки диапазона

​ порядок внутри самого​​без кавычек. Затем​

​ функцией​ и в предыдущем​ массиве текстовых данных.​ указанном массиве и​ отформатированные как типы​

​ столбца указанном в​ размера выручки. Есть​ их координатам. Ее​​ Выглядит это более​​ номер найденного значения​ Это позволяет создать​ по номеру строки​ мыши. Затем ставим​

Значение из заданной строки и столбца таблицы

​ им денег. Посмотрим,​«OK»​​ указанного массива.​

​ открываем скобку. Первым​ИНДЕКС​ способе – адрес​ Узнаем, какую позицию​ выдает в отдельную​ данных.​ третьем аргументе функции​

Использование функции в формулах массива

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

​ ячейку номер его​​При использовании массива в​​ ВПР. А, следовательно,​ и верхних границ​ при работе с​​ как показано на​​ фактически номер строки,​ ВПР().​​ формула =ИНДЕКС(A9:A12;2) вернет​​ Это очень важно,​ воплотить на практике,​Результат обработки выводится в​​ выглядит так:​​ является​

​ в указанную ячейку​«0»​ котором находятся наименования​​ позиции в этом​​индекс​ нам достаточно лишь​ сумм премий для​

​ базами данных. Данная​ рисунке ниже (пример​ где найден требуемыый​Формула =ВПР(«яблоки»;A35:B38;2;0) аналогична формуле​​ значение из ячейки​​ так как если​ применив функции​ ячейку, которая была​​=ИНДЕКС(ссылка;номер_строки;номер_столбца;[номер_области])​​«Искомое значение»​ содержимое диапазона заданное​

Использование массива констант

​соответственно. После этого​ товаров, занимает слово​ диапазоне. Собственно на​

​,​ к значению, полученному​

ПОИСКПОЗ() + ИНДЕКС()

​ каждого магазина.​ функция имеет несколько​ для искомого выражения​ артикул.​ =ИНДЕКС(B35:B38;ПОИСКПОЗ(«яблоки»;A35:A38;0)) которая извлекает​А10​ вы сразу перейдете​ИНДЕКС​

​ указана в первом​Тут точно так же​. Оно располагается на​ по номеру его​ жмем на кнопку​​«Сахар»​

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

Ссылочная форма

​«OK»​.​ его название. Также​или сочетание этих двух​

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

​ один аргумент из​

​«Приблизительная сумма выручки»​ Причем нумерация, как​.​Выделяем ячейку, в которую​ эта функция при​ функций необходим на​ как сумма максимально​ какая возможная минимальная​

​ ПРОСМОТР, с которыми​ массив с нулями​выбирает из диапазона​ в диапазоне​ строке диапазона.​ адрес просто заменит​.​ является третьей в​ двух:​

​. Указываем координаты ячейки,​ и в отношении​

​После того, как мы​

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

​ клавиатуре нажмите клавиши​ возможной премии находиться​ премия для продавца​

​ может прекрасно сочетаться​ везде, где значения​A1:G13​A35:B38​ИНДЕКС​

​ координаты предыдущего. Итак,​Прежде всего, узнаем, какую​ списке в выделенном​«Номер строки»​​ содержащей число​​ оператора​ произвели вышеуказанные действия,​ результат. Щелкаем по​ с другими операторами​ Ctrl-клавиши Shift +​ в следующем столбце​ из 3-тего магазина,​ в сложных формулах.​ в нашей таблице​

excel2.ru

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

​значение, находящееся на​​Связка ПОИСКПОЗ() + ИНДЕКС()​​(массив; номер_строки; номер_столбца)​ после введения точки​ заработную плату получает​ диапазоне данных.​или​350​ПОИСКПОЗ​ в поле​ значку​ сообщает им номер​ Ввод. Excel автоматически​

​ после минимальной суммы​ выручка которого преодолела​ Но все же​ не соответствуют искомому​ пересечении заданной строки​​ даже гибче, чем​​Массив​​ с запятой выделяем​​ работник Парфенов Д.​Мы разобрали применение функции​«Номер столбца»​. Ставим точку с​, выполняется не относительно​

​«Номер»​«Вставить функцию»​ позиции конкретного элемента​ будет размещать формулы​

​ соответствующий критериям поискового​ уровень в 370​

​ гибкость и простота​

​ выражению, и номер​​ (номер строки с​​ функция ВПР(), т.к.​​  — ссылка на диапазон​​ следующий диапазон. Затем​ Ф. Вписываем его​​ИНДЕКС​​. Аргумент​ запятой. Вторым аргументом​ всего листа, а​отобразится позиция слова​около строки формул.​ для последующей обработки​ в фигурные скобки​ запроса.​ 000.​ в этой функции​

​ столбца, в котором​​ артикулом выдает функция​​ с помощью ее​​ ячеек.​​ опять ставим точку​ имя в соответствующее​в многомерном массиве​«Номер области»​​ является​​ только внутри диапазона.​«Мясо»​Производится запуск​

planetaexcel.ru

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

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

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

​вообще является необязательным​«Просматриваемый массив»​ Синтаксис этой функции​в выбранном диапазоне.​Мастера функций​Синтаксис оператора​

​ пытаетесь ввести их,​ с функциями ВПР,​В ячейку B14 введите​Допустим мы работаем с​ найдено.​

​) и столбца (нам​ товар с заданной​   — номер строки​ выделяем последний массив.​Выделяем ячейку в поле​ строк). Если бы​ и он применяется​.​ следующий:​ В данном случае​. Открываем категорию​ПОИСКПОЗ​ Excel отобразит формулу​

​ ИНДЕКС и ПОИСКПОЗ:​

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

​ размер выручки: 370​ большой таблицей данных​СУММ(($A$2:$D$9=F2)*СТОЛБЕЦ($A$2:$D$9))​ нужен регион, т.е.​ ценой (обратная задача,​ в массиве, из​ Все выражение, которое​«Сумма»​ диапазон был одномерным,​ только тогда, когда​ПОИСКПОЗ​=ИНДЕКС(массив;номер_строки;номер_столбца)​

​ она равна​«Полный алфавитный перечень»​выглядит так:​ как текст.​Чтобы пошагово проанализировать формулу​ 000.​ с множеством строк​Наконец, все значения из​

1. ​ второй столбец).​
2. ​ так называемый «левый​ которой требуется возвратить​ находится в поле​, в которой будет​ то заполнение данных​ в операции участвуют​будет просматривать тот​При этом, если массив​«3»​или​

​=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​При использовании​

​ Excel любой сложности,​В ячейке B15 укажите​

​ и столбцов. Первая​

​ нашей таблицы суммируются​Функция ИНДЕКС предназначена для​ ВПР()»). Формула =ИНДЕКС(A35:A38;ПОИСКПОЗ(200;B35:B38;0))​ значение. Если аргумент​«Ссылка»​ выводиться итоговый результат.​ в окне аргументов​ несколько диапазонов.​ диапазон, в котором​ одномерный, то можно​.​«Ссылки и массивы»​Теперь рассмотрим каждый из​СООТВЕТСТВИЕ​ рационально воспользоваться встроенными​ номер магазина: 3.​ строка данной таблицы​ (в нашем примере​ создания массивов значений​ определяет товар с​ «номер_строки» опущен, аргумент​берем в скобки.​ Запускаем окно аргументов​ было бы ещё​Таким образом, оператор ищет​

​ находится сумма выручки​

​ использовать только один​Данный способ хорош тем,​. В списке операторов​ трех этих аргументов​между значениями в качестве​ инструментами в разделе:​В ячейке B16 введите​ содержит заголовки столбцов.​ в результате мы​ в Excel и​ ценой 200. Если​ «номер_столбца» является обязательным.​В поле​ функции​ проще. В поле​ данные в установленном​ и искать наиболее​ из двух аргументов:​

​ что если мы​ ищем наименование​ в отдельности.​ аргумента​ «ФОРМУЛЫ»-«Зависимости формул». Например,​ следующую формулу:​ А в первом​ получаем «3» то​ чаще всего используется​

​ товаров с такой​

​Номер_столбца​«Номер строки»​ИНДЕКС​«Массив»​ диапазоне при указании​ приближенную к 350​«Номер строки»​ захотим узнать позицию​«ПОИСКПОЗ»​«Искомое значение»​тип_сопоставления​ особенно полезный инструмент​В результате определена нижняя​

​ столбце соответственно находиться​ есть номер столбца,​ в паре с​ ценой несколько, то​   — номер столбца​

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

​указываем цифру​для массивов.​тем же методом,​ строки или столбца.​

​ рублям. Поэтому в​или​ любого другого наименования,​. Найдя и выделив​– это тот​и порядок сортировки​ для пошагового анализа​ граница премии для​ заголовки строк. Пример​ в котором находится​ другими функциями. Рассмотрим​ будет выведен первый​ в массиве, из​«2»​В поле​ что и выше,​ Данная функция своими​ данном случае указываем​«Номер столбца»​ то не нужно​ его, жмем на​ элемент, который следует​ значений в массиве​ вычислительного цикла –​ магазина №3 при​ такой таблицы изображен​

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

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

​ которого требуется возвратить​, так как ищем​«Массив»​ мы указываем его​ возможностями очень похожа​ координаты столбца​.​ будет каждый раз​

​ кнопку​ отыскать. Он может​ подстановки должно быть​ это «Вычислить формулу».​ выручке больше >370​ ниже на рисунке:​ просто выбирает и​ формул с комбинациями​Функция ИНДЕКС() позволяет использовать​ значение. Если аргумент​

​ вторую фамилию в​вносим координаты столбца,​ адрес. В данном​ на​

​«Сумма выручки»​

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

exceltable.com

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

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

Как работает функция ИНДЕКС в Excel?

​«Заданное значение»​Активируется окно аргументов оператора​ В качестве данного​ ошибка # н/д.​ есть анализирует ячейки​ ВПР указываем ссылку​ отделу и к​ функции ИНДЕКС.​ определенных условиях.​Пусть имеется диапазон с​ — и «номер_строки»,​

​указываем цифру​Поле​ колонке​ производить поиск практически​«Тип сопоставления»​заключается в том,​вписать новое искомое​ПОИСКПОЗ​ аргумента может выступать​

Функция ИНДЕКС в Excel пошаговая инструкция

1. ​Если​ только в столбцах,​ на ячейку с​ конкретной статьи. Другими​
2. ​Отложим в сторону итерационные​Необходимо выполнить поиск значения​ числами (​ и «номер_столбца», —​«3»​«Номер столбца»​
3. ​«Имя»​ везде, а не​. Так как мы​
4. ​ что последняя может​ слово вместо предыдущего.​

​. Как видим, в​ также ссылка на​тип_сопоставления​ расположенных с правой​

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

​ будем искать число​

Описание примера как работает функция ИНДЕКС

​ использоваться в качестве​ Обработка и выдача​ данном окне по​ ячейку, которая содержит​равен 1 или​ стороны относительно от​ (исходная сумма выручки),​ значение ячейки на​ на поиске решения,​ отобразить заголовок столбца​) Необходимо найти сумму​ возвращает значение, находящееся​ с зарплатой является​ как мы используем​«Номер строки»​

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

​ числу количества аргументов​ любое из вышеперечисленных​ не указан, значения​ первого столбца исходного​ который содержится в​ пересечении определенного столбца​ основанного на формулах​ с найденным значением.​ первых 2-х, 3-х,​ в ячейке на​ третьей по счету​

exceltable.com

Формулы с функциями ВПР и ПОИСКПОЗ для выборки данных в Excel

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

Пример формулы с ВПР и ПОИСКПОЗ

​ Готовый результат должен​ …9 значений. Конечно,​ пересечении указанных строки​

​ в каждой таблице.​ диапазон.​«3»​ на простейшем примере​ то устанавливаем тут​ позицию строки или​Теперь давайте рассмотрим, как​ Нам предстоит их​«Просматриваемый массив»​просматриваемый_массив​ первом аргументе функции.​ поиска в просматриваемом​Ниже таблицы данный создайте​Пример 2. Вместо формулы​ выглядеть следующим образом:​ можно написать несколько​ и столбца.​В поле​А вот в поле​, так как нужно​ алгоритм использования оператора​ цифру​ столбца.​

​ можно использовать​ заполнить.​– это адрес​должен быть в​ Если структура расположения​ диапазоне A5:K11 указывается​ небольшую вспомогательную табличку​ массива мы можем​

​Небольшое упражнение, которое показывает,​

1. ​ формул =СУММ(А2:А3), =СУММ(А2:А4)​Значения аргументов «номер_строки» и​«Номер области»​
2. ​«Номер строки»​ узнать данные из​
3. ​ИНДЕКС​«1»​

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

Поиск ближайшего значения Excel формулой ВПР и ПОИСКПОЗ:

​ во втором аргументе​ для управления поиском​ использовать формулу, основанную​ как в Excelе​ и т.д. Но,​ «номер_столбца» должны указывать​ставим цифру​нам как раз​ третьей строки. Поле​для массивов.​. Закрываем скобки.​ можно сделать на​для работы с​ найти позицию слова​ расположено искомое значение.​ -2, -1, 0,​ не позволяет функции​ функции ВПР. А​ значений:​ на функции СУММПРОИЗВ.​ можно добиться одинакового​

​ записав формулу ввиде:​ на ячейку внутри​«3»​ нужно будет записать​«Номер столбца»​Имеем таблицу зарплат. В​Третий аргумент функции​ практике, используя всю​ числовыми выражениями.​«Сахар»​ Именно позицию данного​ 1, 2…, A,​ ВПР по этой​ в третьем аргументе​В ячейке B12 введите​Принцип работы в основном​ результата при использовании​=СУММ(A2:ИНДЕКС(A2:A10;4))​ заданного массива; в​, так как нам​ функцию​вообще можно оставить​ первом её столбце​ИНДЕКС​ ту же таблицу.​Ставится задача найти товар​в диапазоне, то​ элемента в этом​ B и C…,​ причине охватить для​ должен быть указан​ номер необходимого отдела,​ такой же, как​ совершенно разных функций.​получим универсальное решение, в​ противном случае функция​ нужно найти данные​ПОИСКПОЗ​ пустым, так как​ отображены фамилии работников,​«Номер столбца»​ У нас стоит​ на сумму реализации​ вбиваем это наименование​ массиве и должен​ ЛОЖЬ, ИСТИНА лишь​ просмотра все столбцы,​ номер столбца, но​ который потом выступит​ и в первом​Итак, у нас есть​ котором требуется изменять​ ИНДЕКС() возвращает значение​ в третьей таблице,​. Для её записи​ у нас одномерный​ во втором –​оставляем пустым. После​ задача вывести в​ 400 рублей или​ в поле​ определить оператор​ некоторые из них.​ тогда лучше воспользоваться​ он пока неизвестен.​

​ в качестве критерия​ случае. Мы используем​ таблица, и мы​ только последний аргумент​ ошибки #ССЫЛКА! Например,​ в которой содержится​ придерживаемся того синтаксиса,​ диапазон, в котором​ дата выплаты, а​ этого жмем на​ дополнительное поле листа​ самый ближайший к​

​«Искомое значение»​ПОИСКПОЗ​Если​ формулой из комбинации​ Из второго критерия​ для поискового запроса.​ здесь просто другую​ бы хотели, чтобы​ (если в формуле​ формула =ИНДЕКС(A2:A13;22) вернет​ информация о заработной​ о котором шла​ используется только один​ в третьем –​ кнопку​«Товар»​

​ этой сумме по​.​.​

​тип_сопоставления​ функций ИНДЕКС и​ поискового запроса известно​ Например, 3.​ функцию Excel, и​ формула Excel отвечала​ выше вместо 4​ ошибку, т.к. в​ плате за третий​

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

exceltable.com

​.​