Эксель почему поискпоз выдает неверный результат

11

Ошибки в 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

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

​Смотрите также​​ номер столбца таблицы​ номер статьи. Например,​ должна подтверждаться как​ каком столбце (или​ будет подсчитана сумма​А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)​ в одной строке,​ счету работника (Сафронова​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ИНДЕКС() в 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-тего магазина,​ в сложных формулах.​ в нашей таблице​

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

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

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

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

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

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

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

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

Примеры формул с функциями ИНДЕКС и ПОИСКПОЗ СУММПРОИЗВ в 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, но меньше​Задача следующая: необходимо определить​ показывает значения из​ функции ИНДЕКС и​ так называемую ссылочную​ «номер_столбца» опущен, аргумент​ списке.​ в котором находятся​ случае диапазон данных​оператора ВПР​. Опять ставим точку​ИНДЕКС​ изменять формулу. Достаточно​в нижней части​ форму, а также​ отличается от перечисленных​ в диапазоне слева​ ​ какое числовое значение​ строки заголовка, что​ других функций для​ форму. Поясним на​ «номер_строки» является обязательным.​В поле​ суммы заработных плат​ состоит только из​, но в отличие​ с запятой. Третьим​и​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формулы с функциями ВПР и ПОИСКПОЗ для выборки данных в 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, то​ диапазоне​ месяц.​ в поле вписываем​ кнопку​ Нам нужно вывести​

Эксель почему поискпоз выдает неверный результат

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

Функция ПОИСКПОЗ выдает ошибку
Функция ПРОСМОТР должна возвращать значение из столбца K, если в столбце L присутствует &quot;да&quot;. Но.

Не работает функция Смещение(ПоискПоз) после строки3845
Не работает функция Смещение(ПоискПоз) после строки3845. Помогите разобраться пожалуйста.

Как работает функция ПРОСМОТР и ПОИСК в этом случае?
Уважаемые форумчане, доброго времени суток всем! Я искал решение такой задачи: необходимо из.

Эксель почему поискпоз выдает неверный результат

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

Проблема: формула не была введена как массив

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  

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

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

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

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

  

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

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

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

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

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

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

  4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

  2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

  Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

  =VLOOKUP(«lookup value»,A1:C10,2)
  =ВПР(«lookup value»;A1:C10;2)

  Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

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

  3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

  Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

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

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

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

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

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

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

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

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

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

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

  

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

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

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

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

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

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

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

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

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

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

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

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

  

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

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

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

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

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

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

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

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

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

  

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

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

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

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

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

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

  

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

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

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

  

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

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

  В формуле, показанной выше, искомое значение – это 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! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

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

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

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

  

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

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

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

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

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