Excel индекс с условием если

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

В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР.

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

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

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

Функции Excel ИНДЕКС и ПОИСКПОЗ — основы

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

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

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

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

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

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

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

А вот пример формулы ИНДЕКС в самом простом виде:

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

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

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

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

Она ищет нужное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.

Синтаксис функции ПОИСКПОЗ следующий:

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

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

Например, если диапазон B1:B3 содержит значения «яблоки», «апельсины», «лимоны», приведенная ниже формула возвращает число 3, поскольку «лимоны» — это третья по счету запись в этом диапазоне:

=ПОИСКПОЗ(«лимоны»;B1:B3;0)

Дополнительные сведения см . в статье Функция ПОИСКПОЗ в Excel .

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

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

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

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

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

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

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

Все еще не совсем понимаете эту логику? Возможно, будет проще разобрать на примере. Предположим, у вас есть список национальных столиц и их население:

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

=ИНДЕКС(C2:C10; ПОИСКПОЗ(“Индия”;A2:A10;0))

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

• Функция ПОИСКПОЗ ищет искомое значение «Индия» в диапазоне A2:A10 и возвращает число 2, поскольку это слово занимает второе место в массиве поиска.
• Этот номер поступает непосредственно в аргумент номер_строки функции ИНДЕКС, предписывая вернуть значение из этой строки.

Таким образом, приведенная выше формула превращается в ИНДЕКС(C2:C10;2), которая означает, что нужно искать в ячейках от C2 до C10 и извлекать значение из второй ячейки в этом диапазоне, то есть из C3, потому что мы начинаем отсчет со второй строки.

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

=ИНДЕКС(C2:C10;ПОИСКПОЗ(F1;A2:A10;0))

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

Вы спросите: «А почему бы нам просто не использовать обычную формулу ВПР? Какой смысл тратить время на то, чтобы разобраться в хитросплетениях ИНДЕКС ПОИСКПОЗ в Excel?»

Вот как это будет выглядеть:

=ВПР(F1; A2:C10; 3; 0)

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

ИНДЕКС+ПОИСКПОЗ вместо ВПР?

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

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

4 основные причины использовать ИНДЕКС ПОИСКПОЗ вместо ВПР

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

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

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

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

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

Уяснив, почему все же стоит изучать ИНДЕКС ПОИСКПОЗ, давайте перейдем к самому интересному и посмотрим, как можно применить теоретические знания на практике.

Формула для поиска справа налево

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

Для этого примера мы добавим столбец «Ранг» слева от нашей основной таблицы и попытаемся выяснить, какое место занимает столица России по численности населения среди других перечисленных  столиц.

Записав искомое значение в G1, используйте следующую формулу для поиска в C2:C10 и возврата соответствующего значения из A2:A10:

=ИНДЕКС(A2:A10; ПОИСКПОЗ(G1;C2:C10;0))

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

Двусторонний поиск в строках и столбцах

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

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

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

ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))

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

С целевой страной в G1 (значение_поиска1) и целевым годом в G2 (значение_поиска2) формула принимает следующий вид:

=ИНДЕКС(B2:D11; ПОИСКПОЗ(G1;A2:A11;0); ПОИСКПОЗ(G2;B1:D1;0))

Как работает эта формула?

Всякий раз, когда вам нужно понять сложную формулу Excel, разделите ее на более мелкие части и посмотрите, что делает каждая отдельная функция:

ПОИСКПОЗ(G1;A2:A11;0); – ищет в A2:A11 значение из ячейки G1 («США») и возвращает его позицию, которая равна 3.

ПОИСКПОЗ(G2;B1:D1;0) – просматривает диапазон B1:D1, чтобы получить позицию значения из ячейки G2 («2015»), которая равна 3.

Найденные выше номера строк и столбцов становятся соответствующими аргументами функции ИНДЕКС:

ИНДЕКС(B2:D11, 3, 3)

В результате вы получите значение на пересечении 3-й строки и 3-го столбца в диапазоне B2:D11, то есть из D4. Несложно? 

ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям

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

Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями:

{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}

Примечание. Это формула массива , которую необходимо вводить с помощью сочетания клавиш Ctrl + Shift + Enter.

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

Следующая формула ИНДЕКС ПОИСКПОЗ отлично работает:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; (F1=A2:A10) * (F2=B2:B10); 0))

Где C2:C10 — это диапазон, из которого возвращается значение, F1 — это критерий1, A2:A10 — это диапазон для сравнения с критерием 1, F2 — это критерий 2, а B2:B10 — это диапазон для сравнения с критерием 2.

Не забудьте правильно ввести формулу, нажав Ctrl + Shift + Enter, и Excel автоматически заключит ее в фигурные скобки, как показано на скриншоте ниже:

Рис5

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

=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; ИНДЕКС((F1=A2:A10) * (F2=B2:B10); 0; 1); 0))

Разберем пошагово, как это работает.

Здесь используется тот же подход, что и в обычном сочетании ИНДЕКС ПОИСКПОЗ, где просматривается один столбец. Чтобы оценить несколько критериев, вы создаете два или более массива значений ИСТИНА и ЛОЖЬ, которые представляют совпадения и несовпадения для каждого отдельного критерия, а затем перемножаете соответствующие элементы этих массивов. Операция умножения преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно и создает массив, в котором единицы соответствуют строкам, которые удовлетворяют всем условиям. Функция ПОИСКПОЗ со значением поиска 1 находит первую «1» в массиве и передает ее позицию в ИНДЕКС, которая возвращает значение в этой позиции из указанного столбца.

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

Среднее, максимальное и минимальное значение при помощи ИНДЕКС ПОИСКПОЗ

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

Максимальное значение.

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

=ИНДЕКС(B2:B10; ПОИСКПОЗ(МАКС(C2:C10); C2:C10; 0))

Скриншот с примером находится чуть ниже.

Минимальное значение

Теперь найдём город с самым маленьким населением в списке. Чтобы найти наименьшее число в столбце С и получить соответствующее ему значение из столбца В:

=ИНДЕКС(B2:B10; ПОИСКПОЗ(МИН(C2:C10); C2:C10; 0))

Ближайшее к среднему

Теперь мы находим город, население которого наиболее близко к среднему значению. Чтобы вычислить позицию, наиболее близкую к среднему значению показателя, рассчитанному из D2:D10, и получить соответствующее значение из столбца C, используйте следующую формулу:

=ИНДЕКС(B2:B10; ПОИСКПОЗ(СРЗНАЧ(C2:C10); C2:C10; -1 ))

В зависимости от того, как организованы ваши данные, укажите 1 или -1 для третьего аргумента (тип_совпадения) функции ПОИСКПОЗ:

• Если ваш столбец поиска (столбец D в нашем случае) отсортирован по возрастанию , поставьте 1. Формула вычислит наибольшее значение, которое меньше или равно среднему значению.
• Если ваш столбец поиска отсортирован по убыванию , введите -1. Формула вычислит наименьшее значение, которое больше или равно среднему значению.
• Если ваш массив поиска содержит значение , точно равное среднему, вы можете ввести 0 для точного совпадения. Никакой сортировки не требуется.

В нашем примере данные в столбце D отсортированы в порядке убывания, поэтому мы используем -1 для типа соответствия. В результате мы получаем «Токио», так как его население (13 189 000) является ближайшим, превышающим среднее значение (12 269 006).

Что делать с ошибками поиска?

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

=ЕСНД(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Не найдено»)

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

Если вы хотите перехватывать все ошибки, а не только #Н/Д, используйте функцию ЕСЛИОШИБКА вместо ЕСНД:

=ЕСЛИОШИБКА(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Что-то пошло не так!»)

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

Итак, еще раз об основных преимуществах формулы ИНДЕКС ПОИСКПОЗ.

• Возможен ли «левый» поиск?

• Повлияет ли на результат вставка и удаление столбцов?

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

• Возможен ли поиск по строкам и столбцам?

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

• Как сделать поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям?

  Можно выполнять поиск по двум или более условиям без добавления дополнительных столбцов. Вот формула массива, которая решит проблему:
  {=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}

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

Вот еще несколько статей по этой теме:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Трехмерный поиск данных по нескольким листам (ВПР 3D)
• Поиск и подстановка по нескольким условиям (ВПР по нескольким столбцам)
• Подстановка данных из одной таблицы в другую с помощью функции ВПР (VLOOKUP)

ИНДЕКС и ПОИСКПОЗ с условием ЕСЛИ

Akiane Дата: Вторник, 24.11.2015, 10:58 | Сообщение № 1 Группа: Пользователи Ранг: Новичок Сообщений: 18 Репутация: 0 ± Замечаний: 0% ± Excel 2010 Добрый день, всем. Подскажите пожалуйста, как можно реализовать такую формулу: Нужно, чтобы при статусе товара «Актуальная» — подтягивалась формула ABC (как в столбце R), а если любой другой статус — то по ПОИСКПОЗ из таблицы данных. Пример, прилагаю. Спасибо!   Ответить

китин Дата: Вторник, 24.11.2015, 11:23 | Сообщение № 2 Группа: Модераторы Ранг: Экселист Сообщений: 6973 Репутация: 1063 ± Замечаний: 0% ± Excel 2007;2010;2016 Добрый и вам .так надо? Код =ЕСЛИ(J2=»Актуальная»;ЕСЛИ(O2<=A_;$B$2;ЕСЛИ(O2<=A_+B_;$B$3;$B$4));ИНДЕКС($B$2:$F$11; ПОИСКПОЗ(J2;$A$2:$A$11;0); ПОИСКПОЗ(K2;$B$1:$F$1;0))) К сообщению приложен файл: Akiane.xlsx (12.9 Kb) Не судите очень строго:я пытаюсь научиться ЯД 41001877306852   Ответить

Akiane Дата: Вторник, 24.11.2015, 12:29 | Сообщение № 3 Группа: Пользователи Ранг: Новичок Сообщений: 18 Репутация: 0 ± Замечаний: 0% ± Excel 2010 Так, только если у Актуальной «Сорт 2,3,4» — АВС не считается, а идет поискпоз из таблицы. :shy: Сообщение отредактировал Akiane — Вторник, 24.11.2015, 12:30   Ответить

китин Дата: Вторник, 24.11.2015, 13:06 | Сообщение № 4 Группа: Модераторы Ранг: Экселист Сообщений: 6973 Репутация: 1063 ± Замечаний: 0% ± Excel 2007;2010;2016 если у Актуальной «Сорт 2,3,4» — АВС не считается в условии этого не было тогда так Код =ЕСЛИ(И(J2=»Актуальная»;K2=»Сорт 1″);ЕСЛИ(O2<=A_;$B$2;ЕСЛИ(O2<=A_+B_;$B$3;$B$4));ИНДЕКС($B$2:$F$11; ПОИСКПОЗ(J2;$A$2:$A$11;0); ПОИСКПОЗ(K2;$B$1:$F$1;0))) Не судите очень строго:я пытаюсь научиться ЯД 41001877306852 Сообщение отредактировал китин — Вторник, 24.11.2015, 13:10   Ответить

Akiane Дата: Вторник, 24.11.2015, 13:17 | Сообщение № 5 Группа: Пользователи Ранг: Новичок Сообщений: 18 Репутация: 0 ± Замечаний: 0% ± Excel 2010 Спасибо большое! Теперь поняла почему у самой условие «и» сбивало весь счет :))   Ответить

Akiane Дата: Среда, 09.12.2015, 11:21 | Сообщение № 6 Группа: Пользователи Ранг: Новичок Сообщений: 18 Репутация: 0 ± Замечаний: 0% ± Excel 2010 Добрый день! В очередной раз вынуждена просить помощи по этой же теме. С форматом «даты» в эксель не работала, как вписать «дополнение» в формулу не знаю. Требуется чтобы при определении ABC-анализа товары, которые выведены менее 6 месяцев назад были X, а более 6 месяцев назад Z. К сообщению приложен файл: 2299836.xlsx (16.8 Kb)   Ответить

_Boroda_ Дата: Среда, 09.12.2015, 11:57 | Сообщение № 7 Группа: Модераторы Ранг: Местный житель Сообщений: 16618 Репутация: 6465 ± Замечаний: 0% ± 2003; 2007; 2010; 2013 RUS Так нужно? Код =ЕСЛИ(L2<ДАТАМЕС(СЕГОДНЯ();-6);»X»;ЕСЛИ(И(J2=»Актуальная»;K2=»Сорт 1″);ЕСЛИ(P2<=A_;$B$2;ЕСЛИ(P2<=A_+B_;$B$3;$B$4));ИНДЕКС($B$2:$F$11; ПОИСКПОЗ(J2;$A$2:$A$11;0); ПОИСКПОЗ(K2;$B$1:$F$1;0)))) Скажи мне, кудесник, любимец ба’гов… Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995   Ответить

Akiane Дата: Среда, 16.12.2015, 09:53 | Сообщение № 8 Группа: Пользователи Ранг: Новичок Сообщений: 18 Репутация: 0 ± Замечаний: 0% ± Excel 2010 Спасибо большое, помогло! Вы меня очень выручили   Ответить

vlevitskiy85 Дата: Суббота, 29.01.2022, 12:38 | Сообщение № 9 Группа: Пользователи Ранг: Прохожий Сообщений: 1 Репутация: 0 ± Замечаний: 0% ± Добрый день! Очень нужна ваша помощь. Не знаю как вписать дополнение в формулу ИНДЕКС ПОИСКПОЗ. Нужно прописать условие что если число в колонке искомого значения N больше 5010, то использовать коэффициенты другой колонки 5999. Файл прикладываю. Заранее благодарен за помощь!!! К сообщению приложен файл: 8147585.xlsx (21.2 Kb)   Ответить

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

​Смотрите также​​​ B3, поскольку третья​​ «Работа с данными»​​ последовательных чисел;​​искомое_значение​INDIRECT(ref_text,[a1])​ в основную таблицу,​ столбца.​​ диапазона.​​=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)​.​ они возвращают, формула​, затем находит ближайшее​Формула не будет работать,​=ВПР(«Japan»;$B$2:$D$2;3)​​ число или текст,​​Этот учебник рассказывает о​

​Чтобы функция ПОИСКПОЗ работала​ строка является второй​ и выбрать инструмент​Функция ИНДЕКС() возвращает текстовые​.​​ДВССЫЛ(ссылка_на_текст;[a1])​​ необходимо выполнить действие,​Итак, давайте обратимся к​Эта формула находит только​Где ячейка​Синтаксис функции​ станет легкой и​​ к нему и​​ если значение в​В данном случае –​ который Вы ищите.​ главных преимуществах функций​

​ с таблицей с​ по счету относительно​ «Проверка данных»:​​ значения из Исходного​​Просматриваемый_массив​Первый аргумент может быть​ известное как двойной​ нашей таблице и​ второе совпадающее значение.​B1​ЕСЛИОШИБКА​ понятной:​ возвращает значение из​​ ячейке​​ смысла нет! Цель​​ Аргумент может быть​​ИНДЕКС​ двумя столбцами как​ ячейки A2, а​В открывшемся диалоговом окне​ списка, из строк,​​может быть не​​ ссылкой на ячейку​

• ​ВПР​ запишем формулу с​
• ​ Если же Вам​содержит объединенное значение​
• ​очень прост:​=INDEX($A$1:$E$11,4,5))​
• ​ столбца​A2​
  • ​ этого примера –​ значением, в том​
  • ​и​ с одним мы​
  • ​ столбец B:B является​ необходимо выбрать «Тип​
  • ​ номера которых были​
  • ​ упорядочен.​ (стиль A1 или​

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

​или вложенный​ функцией​ необходимо извлечь остальные​​ аргумента​​IFERROR(value,value_if_error)​​=ИНДЕКС($A$1:$E$11;4;5))​​C​длиннее 255 символов.​ исключительно демонстрационная, чтобы​ числе логическим, или​ПОИСКПОЗ​

​ использовали в ее​ вторым относительно столбца​ данных:» — «Список»​ получены на предыдущем​-1​ R1C1), именем диапазона​​ВПР​​ВПР​​ повторения, воспользуйтесь предыдущим​​lookup_value​​ЕСЛИОШИБКА(значение;значение_если_ошибка)​​Эта формула возвращает значение​

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

​той же строки:​​ Вместо неё Вам​​ Вы могли понять,​ ссылкой на ячейку.​в Excel, которые​ аргументах оператор &.​ A:A.​ и указать в​

​ шаге.​
​Функция​

​ или текстовой строкой.​.​

• ​, которая найдет информацию​​ решением.​(искомое_значение), а​Где аргумент​ на пересечении​
• ​=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))​​ нужно использовать аналогичную​ как функции​lookup_array​ делают их более​ Учитывая этот оператор​При необходимости можно получить​ поле «Источник» диапазон​​В предельном случае м.б.​​ПОИСКПОЗ​
• ​ Второй аргумент определяет,​​Запишите функцию​ о стоимости проданных​Если Вам нужен список​4​value​4-ой​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))​​ формулу​​ПОИСКПОЗ​

​(просматриваемый_массив) – диапазон​ привлекательными по сравнению​​ первый аргументом для​​ целую строку или​ ячеек:​ найдено столько же​находит наименьшее значение,​

​ какого стиля ссылка​​ВПР​​ в марте лимонов.​

​ всех совпадений –​
​– аргумент​

​(значение) – это​строки и​​Результат: Moscow​​ИНДЕКС​и​​ ячеек, в котором​​ с​​ функции теперь является​​ целый столбец, указав​Переходим в ячейку A13​​ значений, сколько содержится​​ которое больше или​

​ содержится в первом​, которая находит имя​Существует несколько способов выполнить​ функция​col_index_num​ значение, проверяемое на​5-го​​Используя функцию​​/​

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

​ИНДЕКС​​ происходит поиск.​​ВПР​ значение FordМаркетинговый. По​ в качестве номера​ и выполняем аналогичные​ в исходном списке​ равно значению аргумента​

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

​match_type​
​. Вы увидите несколько​

​ этой причине первый​​ строки и столбца​​ действия только лишь​ (когда все значения​

​искомое_значение​
​A1​

• ​Lookup table 1​​ с возможными вариантами​тут не помощник,​ столбца, содержащего данные,​ (в нашем случае​A1:E11​в комбинации с​:​
• ​ Последующие примеры покажут​​(тип_сопоставления) – этот​ примеров формул, которые​ Ford из отдела​
• ​ соответственно значение 0​​ указываем другую ссылку​ удовлетворяют критерию). Поэтому​​.​​, если аргумент равен​, используя​ и выберите наиболее​
  • ​ поскольку она возвращает​​ которые необходимо извлечь.​​ – результат формулы​​, то есть значение​ИНДЕКС​=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))​ Вам истинную мощь​ аргумент сообщает функции​ помогут Вам легко​ продаж не учитывается,​
  • ​ (нуль). Для вывода​​ на диапазон в​ формулу массива нужно распространять​Просматриваемый_массив​​TRUE​​SKU​​ подходящий.​​ только одно значение​Если Вам необходимо обновить​ИНДЕКС​​ ячейки​​и​​=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))​​ связки​
  • ​ПОИСКПОЗ​​ справиться со многими​ ведь теперь для​ полученных строки или​ поле «Источник:»​ на диапазон той​должен быть упорядочен​(ИСТИНА) или не​, как искомое значение:​

​Вы можете использовать связку​ за раз –​​ основную таблицу (Main​​/​E4​ПОИСКПОЗ​4. Более высокая скорость​ИНДЕКС​

​, хотите ли Вы​ сложными задачами, перед​ функции два форда​ столбца функцию ИНДЕКС​Такой же выпадающий список​ же размерности, что​ по убыванию: ИСТИНА,​ указан;​​=VLOOKUP(A2,New_SKU,2,FALSE)​​ из функций​​ и точка. Но​​ table), добавив данные​​ПОИСКПОЗ​​. Просто? Да!​, в качестве третьего​​ работы.​​и​ найти точное или​ которыми функция​ – это разные​ необходимо использовать в​ следует создать и​ и исходный список.​ ЛОЖЬ, Z-A, …,​

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

​R1C1​=ВПР(A2;New_SKU;2;ЛОЖЬ)​ВПР​ в Excel есть​ из второй таблицы​); а аргумент​​В учебнике по​​ аргумента функции​​Если Вы работаете​​ПОИСКПОЗ​​ приблизительное совпадение:​​ВПР​ значения (FordПродажи и​ качестве формулы массива.​ для ячейки A15.​​ Вышеуказанная формула массива​​ 2, 1, 0,​, если​Здесь​(VLOOKUP) и​

​ функция​ (Lookup table), которая​​value_if_error​​ВПР​​ПОИСКПОЗ​​ с небольшими таблицами,​

​, которая легко справляется​1​бессильна.​
​ FordМаркетинговый). Просматриваемый диапазон​Функция ПОИСКПОЗ используется для​Для подсчета общего количества​

​ будет возвращать несколько​ -1, -2, …​F​New_SKU​ПОИСКПОЗ​

​INDEX​ находится на другом​(значение_если_ошибка) – это​мы показывали пример​

​чаще всего нужно​
​ то разница в​

​ с многими сложными​или​В нескольких недавних статьях​

• ​ теперь распространяется на​​ поиска указанного в​​ очков в ячейке​ значений, поэтому перед​​ и т. д.​​ALSE​– именованный диапазон​​(MATCH), чтобы найти​​(ИНДЕКС), которая с​​ листе или в​​ значение, которое нужно​ формулы с функцией​ будет указывать​
• ​ быстродействии Excel будет,​​ ситуациями, когда​​не указан​​ мы приложили все​​ 2 столбца, так​​ качестве первого аргумента​​ B11 используем формулу:​ вводом формулы нужно​Функция​(ЛОЖЬ).​$A:$B​

  ​ значение на пересечении​
​ легкостью справится с​

  ​ другой рабочей книге​ возвратить, если формула​ВПР​​1​​ скорее всего, не​​ВПР​​– находит максимальное​ усилия, чтобы разъяснить​ же благодаря оператору​ значения в диапазоне​​Для получения корректного результата​​ выделить сразу весь​ПОИСКПОЗ​В нашем случае ссылка​

​в таблице​ полей​

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

​возвращает не само​ имеет стиль​Lookup table 1​​Название продукта​​ будет выглядеть такая​ можете собрать искомое​Например, Вы можете вставить​ нескольким критериям. Однако,​​-1​​ последних версиях. Если​​Решая, какую формулу использовать​​ равное искомому. Просматриваемый​

​ функции​
​ применяем во втором​

​ массива. Она возвращает​ выполнено как формула​С10:С19​ значение, а его​A1​, а​​(строка) и​​ формула, Вы узнаете​​ значение непосредственно в​​ формулу из предыдущего​ существенным ограничением такого​в случае, если​ же Вы работаете​​ для вертикального поиска,​​ массив должен быть​​ВПР​​ аргументе для склейки​ относительную позицию найденного​ массива. Функция СУММ​​, ввести формулу в​​ позицию в аргументе​

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

​, поэтому можно не​2​Месяц​ в следующем примере.​​ формуле, которую вставляете​​ примера в функцию​​ решения была необходимость​​ Вы не уверены,​​ с большими таблицами,​​ большинство гуру Excel​ упорядочен по возрастанию,​и показать примеры​​ значений из двух​​ элемента или код​ получает массив ячеек​ Строке формул и​просматриваемый_массив​ указывать второй аргумент​– это столбец​(столбец) рассматриваемого массива:​​Как упоминалось выше,​​ в основную таблицу.​​ЕСЛИОШИБКА​​ добавлять вспомогательный столбец.​​ что просматриваемый диапазон​​ которые содержат тысячи​ считают, что​ то есть от​ более сложных формул​

​ смежных диапазонов. Таким​ ошибки #Н/Д, если​​ в виде столбца​​ нажать​​. Например, функция​​ и сосредоточиться на​ B, который содержит​=VLOOKUP(«Lemons»,$A$2:$I$9,MATCH(«Mar»,$A$1:$I$1,0),FALSE)​​ВПР​​Как и в предыдущем​​вот таким образом:​​ Хорошая новость: формула​​ содержит значение, равное​​ строк и сотни​

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

​ИНДЕКС​​ меньшего к большему.​ для продвинутых пользователей.​​ образом, значения берутся​​ искомые данные отсутствуют.​ таблицы, номер которого​CRTL+SHIFT+ENTER​ПОИСКПОЗ(«б»;{«а»;»б»;»в»};0)​ первом.​ названия товаров (смотрите​=ВПР(«Lemons»;$A$2:$I$9;ПОИСКПОЗ(«Mar»;$A$1:$I$1;0);ЛОЖЬ)​не может извлечь​​ примере, Вам понадобится​​=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),​​ИНДЕКС​​ среднему. Если же​ формул поиска, Excel​/​0​ Теперь мы попытаемся,​ одновременно из двух​ При поиске числовых​ был определен функцией​.​

​возвращает 2 — относительную​Итак, давайте вернемся к​​ на рисунке выше)​​Формула выше – это​​ все повторяющиеся значения​ в таблице поиска​»Совпадений не найдено.​/​ Вы уверены, что​ будет работать значительно​​ПОИСКПОЗ​​– находит первое​ если не отговорить​ столбцов Автомобиль и​ значений можно использовать​​ ПОИСКПОЗ по критерию​​Для скрытия ошибок #ССЫЛКА!,​ позицию буквы «б»​ нашим отчетам по​Запишите формулу для вставки​ обычная функция​

​ из просматриваемого диапазона.​ (Lookup table) вспомогательный​​ Попробуйте еще раз!»)​​ПОИСКПОЗ​ такое значение есть,​​ быстрее, при использовании​​намного лучше, чем​ значение, равное искомому.​​ Вас от использования​​ Отдел.​​ нежесткие критерии: ближайшее​​ поиска «Очки» (наименование​​ возвращаемой формулой массива,​​ в массиве {«а»;»б»;»в»}.​

​ продажам. Если Вы​
​ цен из таблицы​

​ВПР​ Чтобы сделать это,​ столбец с объединенными​​=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));​​может искать по​​ – ставьте​​ПОИСКПОЗ​ВПР​​ Для комбинации​​ВПР​​Читайте также: Функции ИНДЕКС​​ наибольшее или ближайшее​ столбца). Поскольку в​ к диапазону​

​Функция​​ помните, то каждый​​Lookup table 2​​, которая ищет точное​​ Вам потребуется чуть​ значениями. Этот столбец​»Совпадений не найдено.​ значениям в двух​0​и​. Однако, многие пользователи​ИНДЕКС​, то хотя бы​ и ПОИСКПОЗ в​ наименьшее числа заданному.​ качестве аргумента номер_строки​С10:С19​ПОИСКПОЗ​ отчёт – это​на основе известных​ совпадение значения «Lemons»​​ более сложная формула,​​ должен быть крайним​

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

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

​ A2 до A9.​
​ функций Excel, таких​

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

​Если функция​
​ Чтобы формула работала​

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

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

​ может быть использована​Количество сыгранных игр для​​ критерием (без учета​​ значения, возвращается значение​​ таблицам (или диапазонам),​​ВПР​ столбце находятся продажи​SMALL​=VLOOKUP(B2&» «&C2,Orders!$A&$2:$D$2,4,FALSE)​

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

​ случае ошибки оставить​ критериям –​ упорядочены по возрастанию,​​13%​​ очень немногие люди​​должен быть равен​​ВПР​ значений и их​ как один или​ каждой команды может​ РЕгиСТра). Критерий вводится​

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

​ ошибки #Н/Д.​​ причем все названия​​:​ за март, то​(НАИМЕНЬШИЙ) и​=ВПР(B2&» «&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)​ ячейку пустой, то​имя покупателя​ а формула вернёт​.​​ до конца понимают​​0​

​– это не​​ суммирование по всем​​ сразу два аргумента​​ быть рассчитано как​​ в ячейку​Если​ должны иметь общую​=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)​ не сможете задать​ROW​Здесь в столбцах B​ можете использовать кавычки​(Customer) и​ максимальное значение, меньшее​Влияние​​ все преимущества перехода​​.​​ единственная функция поиска​​ листам книги?​ функции:​ сумма выигранных, сыгранных​E6​

​тип_сопоставления​ часть. Например, так:​=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)​ номер столбца для​

​(СТРОКА)​
​ и C содержатся​

​ («»), как значение​продукт​ или равное среднему.​ВПР​

• ​ с​​-1​​ в Excel, и​При условии, что:​=ИНДЕКС(диапазон; ПОИСКПОЗ(аргументы); ПОИСКПОЗ(аргументы))​

  ​ вничью и проигранных​
​.​

• ​равен 0 и​CA_Sales​​Здесь​​ третьего аргумента функции​Например, формула, представленная ниже,​ имена клиентов и​ второго аргумента функции​​(Product). Дело усложняется​​Если указываете​
• ​на производительность Excel​ВПР​

  ​– находит наименьшее​
​ её многочисленные ограничения​

​- искомые значения​​Такая формула используется чаще​ игр. Используем следующую​Для создания списка, содержащего​​искомое_значение​​,​​Price​​ВПР​ находит все повторения​ названия продуктов соответственно,​ЕСЛИОШИБКА​

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

​ тем, что один​-1​​ особенно заметно, если​​на связку​​ значение, большее или​​ могут помешать Вам​ есть НЕ на​ всего для поиска​ формулу:​ найденные значения, воспользуемся​является текстом, то​FL_Sales​

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

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

​ каждом листе​

​ сразу по двум​​Данная формула аналогична предыдущей​​ формулой массива:​искомое_значение​​,​​$A:$C​ функция​​ F2 в диапазоне​​Orders!$A&$2:$D$2​

​IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")​
​ сразу несколько разных​

​ поиска должны быть​

​ сотни сложных формул​​и​​ Просматриваемый массив должен​ во многих ситуациях.​​- диапазон поиска​​ критериям.​ и также должна​=ИНДЕКС(Список; НАИМЕНЬШИЙ(​может содержать подстановочные​​TX_Sales​​в таблице​

​ПОИСКПОЗ​
​ B2:B16 и возвращает​

​определяет таблицу для​

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

​ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»»)​​ продуктов, и имена​​ упорядочены по убыванию,​​ массива, таких как​​ПОИСКПОЗ​​ быть упорядочен по​​ С другой стороны,​ и столбец со​​AlexKoul​​ использоваться в качестве​ЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30);​​ знаки: звездочку (​​и так далее.​​Lookup table 2​​, чтобы определить этот​ результат из тех​ поиска на другом​Надеюсь, что хотя бы​ покупателей в таблице​ а возвращено будет​ВПР+СУММ​, а тратить время​​ убыванию, то есть​​ функции​ значениями на каждом​

• ​: Уважаемые форумчане!​​ формулы массива. Благодаря​​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))​*​ Как видите, во​, а​ столбец.​ же строк в​
• ​ листе.​​ одна формула, описанная​​ на листе​ минимальное значение, большее​. Дело в том,​ на изучение более​ от большего к​ИНДЕКС​

​ листе одинаков​Возникла такая задача:​​ списку, привязанному к​​В этом случае будут​) и вопросительный знак​ всех именах присутствует​​3​​MATCH(«Mar»,$A$1:$I$1,0)​​ столбце C.​​Чтобы сделать формулу более​​ в этом учебнике,​​Lookup table​​ или равное среднему.​ что проверка каждого​ сложной формулы никто​ меньшему.​и​- условие поиска​

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

​ имеется список марок​ ячейке A13, можно​​ выведены все значения,​​ (​ «_Sales».​– это столбец​ПОИСКПОЗ(«Mar»;$A$1:$I$1;0)​

​{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3)),»»)}​​ читаемой, Вы можете​​ показалась Вам полезной.​​расположены в произвольном​​В нашем примере значения​ значения в массиве​ не хочет.​На первый взгляд, польза​ПОИСКПОЗ​ в списке на​

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

​ C, содержащий цены.​
​В переводе на человеческий​

​{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3));»»)}​ задать имя для​ Если Вы сталкивались​

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

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

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

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

​Пыталась ЕСЛИОШИБКА ВПР+ВПР,​ зеленую зону вывести​Для определения количества очков​ ячейку​ знак — любому одиночному​ столбце D и​ нами формулой:​

​Ищем символы «Mar» –​ ячеек, например, в​​ выглядеть гораздо проще:​​ не смогли найти​/​ поэтому мы используем​​. Поэтому, чем больше​​и​

• ​ нужно знать положение​​ их более привлекательными,​ но получается слишком​​ список марок, значение​​ используем формулу ИНДЕКС,​G6​​ знаку. Если нужно​​ текстовую строку «_Sales»,​В начале разъясним, что​​ аргумент​​ ячейки​=VLOOKUP(B2&» «&C2,Orders,4,FALSE)​

  ​ подходящее решение среди​
​ПОИСКПОЗ​

  ​ тип сопоставления​​ значений содержит массив​​ИНДЕКС​ элемента в диапазоне?​ по сравнению с​​ длинная формула –​​ которых больше или​

• ​ в которой оба​​.​ найти сам вопросительный​​ тем самым сообщая​​ мы подразумеваем под​​lookup_value​​F4:F8​=ВПР(B2&» «&C2;Orders;4;ЛОЖЬ)​​ информации в этом​​решает задачу:​

  ​1​
​ и чем больше​

  ​в Excel, а​​ Мы хотим знать​​ВПР​ не влезает (листов​

​ равно значению, вводимому​ аргумента, указывающие номер​​Для создания списка, содержащего​​ знак или звездочку,​

​ВПР​
​ выражением «Динамическая подстановка​

​(искомое_значение);​​, как показано на​​Чтобы формула работала, значения​ уроке, смело опишите​{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)*​. Формула​

​ формул массива содержит​
​ Вы решите –​

​ значение этого элемента!​.​​ много)​​ в ячейку С2.​​ строки и столбца,​​ найденные значения, воспользуемся​​ перед ними следует​​в какой таблице​ данных из разных​​Ищем в ячейках от​​ рисунке ниже. Количество​

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

​ в крайнем левом​​ свою проблему в​​(B2=’Lookup table’!$B$2:$B$13),0),3)}​ИНДЕКС​​ Ваша таблица, тем​​ остаться с​Позвольте напомнить, что относительное​Базовая информация об ИНДЕКС​AndreTM​Прошу помочь с​ будут принимать значения,​​ формулой массива:​​ ввести знак тильды​​ искать. Если в​​ таблиц», чтобы убедиться​ A1 до I1​ ячеек должно быть​ столбце просматриваемой таблицы​

​ комментариях, и мы​{=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)*​/​ медленнее работает Excel.​ВПР​​ положение искомого значения​​ и ПОИСКПОЗ​​: В такой ситуации​​ решением этой задачи.​ возвращаемые функцией ПОИСКПОЗ:​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​ (​ ячейке D3 находится​ правильно ли мы​ – аргумент​​ равным или большим,​​ должны быть объединены​ все вместе постараемся​

​(B2=’Lookup table’!$B$2:$B$13);0);3)}​​ПОИСКПО​​С другой стороны, формула​​или переключиться на​​ (т.е. номер строки​

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

​ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($G$6;Список);0)=1;СТРОКА(Список)-СТРОКА($A$9);30);​~​ значение «FL», формула​ понимает друг друга.​​lookup_array​​ чем максимально возможное​​ точно так же,​​ решить её.​Эта формула сложнее других,​З​​ с функциями​​ИНДЕКС​ и/или столбца) –​

​ ПОИСКПОЗ в Excel​
​ макросом, поскольку все​

​ откликнувшихся!​В результате мы получили​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))​​).​​ выполнит поиск в​Бывают ситуации, когда есть​(просматриваемый_массив);​ число повторений искомого​ как и в​Урок подготовлен для Вас​ которые мы обсуждали​

• ​возвращает «Moscow», поскольку​ПОИСКПОЗ​​/​​ это как раз​​Преимущества ИНДЕКС и ПОИСКПОЗ​​ равно действия два:​китин​ значение по 2-м​В этом случае будут​​Скопируйте образец данных из​​ таблице​
• ​ несколько листов с​Возвращаем точное совпадение –​​ значения. Не забудьте​​ критерии поиска. На​ командой сайта office-guru.ru​​ ранее, но вооруженные​​ величина населения города​
• ​и​ПОИСКПОЗ​ то, что мы​​ перед ВПР​​ сначала надо объединить​
• ​: можно так​ критериям:​ выведены все значения,​ следующей таблицы и​FL_Sales​ данными одного формата,​ аргумент​​ нажать​​ рисунке выше мы​Источник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/​ знанием функций​ Москва – ближайшее​ИНДЕКС​​.​​ должны указать для​

​ИНДЕКС и ПОИСКПОЗ –​ данные из всех​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС($G$2:$G$7;НАИМЕНЬШИЙ(ЕСЛИ($C$2​​– «Челси».​ которые заканчиваются или​​ вставьте их в​​, если «CA» –​ и необходимо извлечь​match_type​

​Ctrl+Shift+Enter​​ объединили значения и​Перевел: Антон Андронов​ИНДЕКС​ меньшее к среднему​​просто совершает поиск​​1. Поиск справа налево.​ аргументов​ примеры формул​ таблиц в один​формула массива​– «Очки».​ совпадают с критерием.​ ячейку A1 нового​ в таблице​​ нужную информацию с​​(тип_сопоставления).​, чтобы правильно ввести​​ поставили между ними​​Автор: Антон Андронов​

​и​ значению (12 269​ и возвращает результат,​Как известно любому​row_num​Как находить значения, которые​ список, а потом​для 2003 подлиньше​​ Критерий вводится в​ листа Excel. Чтобы​CA_Sales​​ определенного листа в​​Использовав​

​ формулу массива.​ пробел, точно так​Во второй части нашего​ПОИСКПОЗ​

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

​ 006).​ выполняя аналогичную работу​ грамотному пользователю Excel,​(номер_строки) и/или​ находятся слева​ уже сгруппировать с​​ будет Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕОШИБКА(ИНДЕКС($G$2:$G$7;НАИМЕНЬШИЙ(ЕСЛИ($C$2​​Пример 2. Используя таблицу​​ ячейку​​ отобразить результаты формул,​​и так далее.​​ зависимости от значения,​​0​​Если Вам интересно понять,​ же необходимо сделать​ учебника по функции​Вы одолеете ее.​Эта формула эквивалентна двумерному​ заметно быстрее.​​ВПР​​column_num​​Вычисления при помощи ИНДЕКС​​ агрегацией. Постов на​​_Boroda_​​ из предыдущего примера​

​I6​​ выделите их и​​Результат работы функций​

​ которое введено в​
​в третьем аргументе,​

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

​ Вы говорите функции​ давайте немного погрузимся​ функции (B2&» «&C2).​​(VLOOKUP) в Excel​​ – это функция​

​ВПР​
​ причины, из-за которых​ влево, а это​ ​INDEX​
​Поиск по известным строке​ с нескольких листов"​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(H$2:H$7>=C$2)/ЕНД(ПОИСКПОЗ(G$2:G$7;C$3:C3;));G$2:G$7);»»)​ заработанных очков несколькими​Для создания списка, содержащего​ а затем — клавишу​

​и​ проще это объяснить​ПОИСКПОЗ​ в детали формулы:​Запомните!​ мы разберём несколько​​ПОИСКПОЗ​​и позволяет найти​

​ стоит изучать функции​
​ значит, что искомое​

​(ИНДЕКС). Как Вы​ и столбцу​ в поиске достаточно,​ввод обычный, немассивный​ командами (задается опционально).​ найденные значения, воспользуемся​ ВВОД. При необходимости​ДВССЫЛ​ на примере.​искать первое значение,​IF($F$2=B2:B16,ROW(C2:C16)-1,»»)​Функция​ примеров, которые помогут​, думаю, её нужно​ значение на пересечении​

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

​ а «группировка/агрегация» -​

office-guru.ru

Продвинутые примеры с функцией ВПР: поиск по нескольким критериям

​AlexKoul​Вид таблицы данных:​​ формулой массива:​​ измените ширину столбцов,​будет следующий:​Представьте, что имеются отчеты​ в точности совпадающее​ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»)​​ВПР​​ Вам направить всю​ объяснить первой.​ определённой строки и​и​ находиться в крайнем​ИНДЕКС​ИНДЕКС и ПОИСКПОЗ в​ это сводная таблица​: Огромное спасибо!​Искомое значение может быть​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​ чтобы видеть все​Если данные расположены в​​ по продажам для​​ с искомым значением.​$F$2=B2:B16​

• ​ограничена 255 символами,​ мощь​
• ​MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)​ столбца.​ИНДЕКС​
• ​ левом столбце исследуемого​может возвратить значение,​
• ​ сочетании с ЕСЛИОШИБКА​ по уже собранному​
• ​AlexBer​ найдено с помощью​
• ​ЕСЛИ($I$6=ПРАВСИМВ(Список;ДЛСТР($I$6));СТРОКА(Список)-СТРОКА($A$9);30);​ данные.​

Поиск в Excel по нескольким критериям

​ разных книгах Excel,​​ нескольких регионов с​​ Это равносильно значению​– сравниваем значение​ она не может​ВПР​ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)​В этом примере формула​, давайте перейдём к​ диапазона. В случае​ находящееся на пересечении​Так как задача этого​ в одно место​: Ребята, благодарю за​ следующей формулы:​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))​

Пример 1: Поиск по 2-м разным критериям

​Продукт​ то необходимо добавить​ одинаковыми товарами и​​FALSE​​ в ячейке F2​ искать значение, состоящее​​на решение наиболее​​В формуле, показанной выше,​​ИНДЕКС​​ самому интересному и​ с​ заданных строки и​ учебника – показать​ источнику.​ Вашу поддержку,​

​Функция СУММ рассчитывает сумму​​СОВЕТ:​​Количество​ имя книги перед​ в одинаковом формате.​(ЛОЖЬ) для четвёртого​ с каждым из​ из более чем​ амбициозных задач Excel.​ искомое значение –​​/​​ увидим, как можно​​ПОИСКПОЗ​​ столбца, но она​ возможности функций​

​Ну или если​
​По образу и​

​ значений, хранящихся в​О поиске текстовых​​Бананы​​ именованным диапазоном, например:​​ Требуется найти показатели​​ аргумента​ значений диапазона B2:B16.​

​ 255 символов. Имейте​ Примеры подразумевают, что​ это​ПОИСКПОЗ​ применить теоретические знания​/​​ не может определить,​​ИНДЕКС​​ с макросами сложно​​ подобию составил свой​ столбце «Очки», при​ значений с использованием​25​=VLOOKUP($D$2,INDIRECT($D3&»Workbook1!_Sales»),2,FALSE)​ продаж для определенного​ВПР​​ Если найдено совпадение,​​ это ввиду и​ Вы уже имеете​

​1​будет очень похожа​ на практике.​ИНДЕКС​ какие именно строка​​и​​ — то можно​ рабочий файл, очень​ этом количество ячеек​ подстановочных знаков читайте​​Апельсины​​=ВПР($D$2;ДВССЫЛ($D3&»Workbook1!_Sales»);2;ЛОЖЬ)​ региона:​

​.​
​ то выражение​

​ следите, чтобы длина​

​ базовые знания о​
​, а массив поиска​

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

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

​Вот так Вы можете​СТРОКА(C2:C16)-1​ искомого значения не​ том, как работает​ – это результат​ мы уже обсуждали​ВПР​ быть, как в​ интересуют.​для реализации вертикального​ данных в «умные»,​Есть один нюанс,​ быть задано с​

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

​ в этом уроке,​​твердит, что эта​​ левой, так и​

​Теперь, когда Вам известна​
​ поиска в Excel,​

​ а затем задействовать​ который хотелось бы​ помощью критерия –​ списках. Часть2. Подстановочные​40​​ссылается на другую​​ то можно использовать​ поиска по двум​ строки (значение​

​Соглашусь, добавление вспомогательного столбца​ нет, возможно, Вам​ же мы должны​ с одним лишь​ функция не может​ в правой части​

​ базовая информация об​
​ мы не будем​

​ инструменты Power Query.​ доработать.​ выбранного названия команды.​ знаки. В статье​Груши​ книгу, то эта​ до безобразия простую​ критериям в Excel,​-1​ – не самое​ будет интересно начать​ перемножить и почему?​ отличием. Угадайте каким?​ смотреть влево. Т.е.​

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

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

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

​ как двумерный поиск​​ строку заголовков). Если​​ всегда приемлемое решение.​ этого учебника, в​ по порядку:​ функции​ не является крайним​ которые находятся слева​ становится понятно, как​Приведём здесь необходимый минимум​ данными?​ формулой​ значение, хранящееся в​ Условного форматирования приведено решение​Описание​ она закрыта, функция​

​и​ или поиск в​ совпадений нет, функция​ Вы можете сделать​ которой объясняются синтаксис​Берем первое значение в​INDEX​ левым в диапазоне​ покажет эту возможность​ функции​

​ для понимания сути,​Nesa​Вывод значений происходит​​ искомой ячейке, но​​ аналогичной задачи с​Результат​ сообщит об ошибке​ЕСЛИ​​ двух направлениях.​​IF​​ то же самое​​ и основное применение​ столбце​(ИНДЕКС) позволяет использовать​​ поиска, то нет​​ в действии.​ПОИСКПОЗ​ а затем разберём​

​: 24 листа, в​
​ снизу вверх ,​

​ и ссылку на​ использованием Условного форматирования.​​=ПОИСКПОЗ(39;B2:B5,1;0)​​#REF!​(IF), чтобы выбрать​

• ​Функция​​(ЕСЛИ) возвращает пустую​​ без вспомогательного столбца,​​ВПР​​A​

  ​ три аргумента:​
​ шансов получить от​

• ​2. Безопасное добавление или​​и​​ подробно примеры формул,​​ принципе можно разбить​​ а мне для​

  ​ эту ячейку. Поэтому​
​Как использовать функцию​

​Так как точного соответствия​(#ССЫЛ!).​ нужный отчет для​СУММПРОИЗВ​ строку.​ но в таком​. Что ж, давайте​

​(Customer) на листе​​INDEX(array,row_num,[column_num])​​ВПР​ удаление столбцов.​ИНДЕКС​ которые показывают преимущества​

​ н 2 файла​
​ удобства, да и​

​ можно использовать запись​

• ​ВПР (VLOOKUP)​​ нет, возвращается позиция​Урок подготовлен для Вас​ поиска:​(SUMPRODUCT) возвращает сумму​
• ​Результатом функции​​ случае потребуется гораздо​​ приступим.​​Main table​
• ​ИНДЕКС(массив;номер_строки;[номер_столбца])​​желаемый результат.​Формулы с функцией​могут работать вместе.​ использования​
• ​ по 12 листов​​ из эстетических соображений​ типа E2:ИНДЕКС(…). В​для поиска и​

​ ближайшего меньшего элемента​ командой сайта office-guru.ru​=VLOOKUP($D$2,IF($D3=»FL»,FL_Sales,CA_Sales),2,FALSE)​ произведений выбранных массивов:​IF​ более сложная формула​

​Поиск в Excel по​и сравниваем его​И я поздравляю тех​​Функции​​ВПР​ПОИСКПОЗ​ИНДЕКС​макросом не то​ хотелось бы видеть​ результате выполнения функция​ выборки нужных значений​​ (38) в диапазоне​​Источник: https://www.ablebits.com/office-addins-blog/2014/07/29/vlookup-formula-examples/​=ВПР($D$2;ЕСЛИ($D3=»FL»;FL_Sales;CA_Sales);2;ЛОЖЬ)​=SUMPRODUCT(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»),$A$2:$I$9)​(ЕСЛИ) окажется вот​ с комбинацией функций​ нескольким критериям​

Извлекаем все повторения искомого значения

​ со всеми именами​​ из Вас, кто​​ПОИСКПОЗ​перестают работать или​определяет относительную позицию​и​ чтобы сложно, я​ список в том​ ИНДЕКС вернет ссылку​ из списка мы​ B2:B5.​​Перевел: Антон Андронов​​Где:​​=СУММПРОИЗВ(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»);$A$2:$I$9)​​ такой горизонтальный массив:​​INDEX​​Извлекаем 2-е, 3-е и​

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

​ на ячейку, и​
​ недавно разбирали. Если​

​2​Автор: Антон Андронов​$D$2​В следующей статье я​​{1,»»,3,»»,5,»»,»»,»»,»»,»»,»»,12,»»,»»,»»}​​(ИНДЕКС) и​ т.д. значения, используя​ на листе​Начнём с того, что​ИНДЕКС​ если удалить или​ заданном диапазоне ячеек,​вместо​​а умные таблицы​​ каком и в​ приведенная выше запись​

​ вы еще с​=ПОИСКПОЗ(41;B2:B5;0)​Функция​– это ячейка,​

Часть 1:

​ буду объяснять эти​
​ROW()-3​

​MATCH​​ ВПР​Lookup table​ запишем шаблон формулы.​в Excel гораздо​ добавить столбец в​ а​​ВПР​​ — это какие?​ исходной таблице.​​ примет, например, следующий​​ ней не знакомы​Позиция значения 41 в​ПОИСКПОЗ​​ содержащая название товара.​​ функции во всех​СТРОКА()-3​

​(ПОИСКПОЗ).​​Извлекаем все повторения искомого​​(A2:A13).​ Для этого возьмём​​ более гибкие, и​

Часть 2:

​ таблицу поиска. Для​
​ИНДЕКС​

​.​​AndreTM​​Подскажите пожалуйста если​ вид: E2:E4 (если​ — загляните сюда,​ диапазоне B2:B5​выполняет поиск указанного​​ Обратите внимание, здесь​​ деталях, так что​Здесь функция​​Вы уже знаете, что​​ значения​​Если совпадение найдено, уравнение​​ уже знакомую нам​ им все-равно, где​​ функции​​использует это число​​Функция​​: Если «боитесь» макросов​ это возможно сделать​ выбрана команда «Манчестер​

Часть 3:

​ не пожалейте пяти​
​4​

​ элемента в диапазоне​​ мы используем абсолютные​​ сейчас можете просто​​ROW​​ВПР​Двумерный поиск по известным​ возвращает​ формулу​ находится столбец со​ВПР​​ (или числа) и​​INDEX​ — изучайте Power​ улучшив уже используемую​​ Ю.».​​ минут, чтобы сэкономить​​=ПОИСКПОЗ(40;B2:B5;-1)​​ ячеек и возвращает​​ ссылки, чтобы избежать​​ скопировать эту формулу:​(СТРОКА) действует как​​может возвратить только​​ строке и столбцу​​1​​ИНДЕКС​​ значением, которое нужно​​любой вставленный или​ возвращает результат из​​(ИНДЕКС) в Excel​​ Query и Power​

Часть 4:

​ формулу.​
​Пример расчетов:​

​ себе потом несколько​​Возвращает сообщение об ошибке,​​ относительную позицию этого​ изменения искомого значения​=INDEX($A$2:$I$9,MATCH(«Lemons»,$A$2:$A$9,0),MATCH(«Mar»,$A$1:$I$1,0))​​ дополнительный счётчик. Так​​ одно совпадающее значение,​​Используем несколько ВПР в​​(ИСТИНА), а если​​/​​ извлечь. Для примера,​​ удалённый столбец изменит​​ соответствующей ячейки.​​ возвращает значение из​​ Pivot.​​С уважением,​​Проверим результат выборочного динамического​​ часов.​​ так как диапазон​

Часть 5:

​ элемента в диапазоне.​
​ при копировании формулы​

​=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ(«Lemons»;$A$2:$A$9;0);ПОИСКПОЗ(«Mar»;$A$1:$I$1;0))​ как формула скопирована​​ точнее – первое​​ одной формуле​ нет –​ПОИСКПОЗ​​ снова вернёмся к​​ результат формулы, поскольку​Ещё не совсем понятно?​ массива по заданным​Ну и не​Александр.​ суммирования столбца таблицы​Если же вы знакомы​

Двумерный поиск по известным строке и столбцу

​ B2:B5 упорядочен не​ Например, если диапазон​ в другие ячейки.​Если Вы не в​ в ячейки F4:F9,​ найденное. Но как​Динамическая подстановка данных из​0​и добавим в​

​ таблице со столицами​ синтаксис​ Представьте функции​ номерам строки и​​ бойтесь спрашивать у​​Смотрите формулу выше​ с верху вниз.​ с ВПР, то​

​ по убыванию.​ A1:A3 содержит значения​$D3​ восторге от всех​ мы вычитаем число​

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

​ быть, если в​ разных таблиц​​(ЛОЖЬ).​​ неё ещё одну​​ государств и населением.​​ВПР​ИНДЕКС​ столбца. Функция имеет​​ Гуглояндекса​​ (пост №2).​​Сумма чисел в диапазоне​​ — вдогон -​

​#Н/Д​
​ 5, 25 и​

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

​ВПР​
​ же самое для​

​ПОИСКПОЗ​ запишем формулу​ диапазон и конкретный​

• ​ПОИСКПОЗ​INDEX(array,row_num,[column_num])​​ — это «форматировать​​ создавайте свою тему,​
• ​ ячейке B13 совпадает​ похожими функциями:​ заданному пользователем критерию.​​=ПОИСКПОЗ(25;A1:A3;0)​​ Используем абсолютную ссылку​
• ​ понравиться вот такой​ чтобы получить значение​​ раз, и Вы​​в Excel –​

​ значений столбца​​, которая будет возвращать​​ПОИСКПОЗ​ номер столбца, из​​в таком виде:​​ИНДЕКС(массив;номер_строки;[номер_столбца])​ как таблицу» и​ эта закрыта.​ все ОК.​​ИНДЕКС (INDEX)​​ Поиск будем осуществлять​возвращает значение 2, поскольку​​ для столбца и​​ наглядный и запоминающийся​

​1​ хотите извлечь 2-е​ это действительно мощный​B​ номер столбца.​/​ которого нужно извлечь​=INDEX(столбец из которого извлекаем,(MATCH​

Функция СУММПРОИЗВ

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

​и​
​ в диапазоне с​

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

​ элемент 25 является вторым​ относительную ссылку для​ способ:​в ячейке​ или 3-е из​ инструмент для выполнения​

​(Product).​
​=INDEX(Ваша таблица,(MATCH(значение для вертикального​

Именованные диапазоны и оператор пересечения

​ИНДЕКС​ данные.​ (искомое значение,столбец в​ простое объяснение:​ имя, если надо.​ очень часто приходится​ табеля рабочего времени​

1. ​ПОИСКПОЗ (MATCH)​​ повторяющимися значениями. При​​ в диапазоне.​​ строки, поскольку планируем​​Выделите таблицу, откройте вкладку​
2. ​F4​​ них? А что​​ поиска определённого значения​Затем перемножаем полученные результаты​​ поиска,столбец, в котором​​, которая покажет, какое​Например, если у Вас​ котором ищем,0))​array​А обработка с​ использовать функцию ПОИСКПОЗ​ хранятся данные о​, владение которыми весьма​ наличии повторов, можно​Совет:​
3. ​ копировать формулу в​Formulas​​(строка 4, вычитаем​​ если все значения?​

   ​ в базе данных.​
   ​ (1 и 0).​
   ​ искать,0)),(MATCH(значение для горизонтального​

   ​ место по населению​ есть таблица​=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое​(массив) – это​ помощью Power Query​ как вспомогательную в​

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

1. ​ 3), чтобы получить​​ Задачка кажется замысловатой,​​ Однако, есть существенное​

​ Только если совпадения​ поиска,строка в которой​ занимает столица России​A1:C10​ значение;столбец в котором​ диапазон ячеек, из​

Используем несколько ВПР в одной формуле

​ — то примерно​ комбинациях с другими​ фирмы. Определить, сколько​ опытному пользователю Excel.​ будет соответствовать несколько​ПОИСКПОЗ​ же столбца.​​Create from Selection​​2​ но решение существует!​ ограничение – её​ найдены в обоих​ искать,0))​ (Москва).​, и требуется извлечь​

​ ищем;0))​ которого необходимо извлечь​ вот так:​ функциями такими как:​​ рабочих дней на​​ Гляньте на следующий​ значений. Для их​следует пользоваться вместо​FL_Sal​(Создать из выделенного).​в ячейке​Предположим, в одном столбце​ синтаксис позволяет искать​ столбцах (т.е. оба​​=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального​​Как видно на рисунке​ данные из столбца​Думаю, ещё проще будет​ значение.​gling​​ ИНДЕКС, ВПР, ГПР​​ текущий момент отработал​

​ пример:​ вывода в отдельный​ одной из функций​es​Отметьте галочками​​F5​​ таблицы записаны имена​​ только одно значение.​​ критерия истинны), Вы​

1. ​ поиска,столбец, в котором​​ ниже, формула отлично​​B​ понять на примере.​​row_num​​: Вариант формулами.​​ и др. Но​​ любой из новых​

   ​Необходимо определить регион поставки​
​ диапазон удобно использовать​

   ​ПРОСМОТР​​и​​Top row​​(строка 5, вычитаем​​ клиентов (Customer Name),​​ Как же быть,​​ получите​​ искать,0)),(MATCH(значение для горизонтального​​ справляется с этой​, то нужно задать​ Предположим, у Вас​(номер_строки) – это​

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

   ​1​
​ поиска,строка в которой​

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

​– названия таблиц​ и​ далее.​

Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ

​ – товары (Product),​ поиск по нескольким​. Если оба критерия​ искать,0))​=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0))​2​ список столиц государств:​

​ массиве, из которой​ формульного варианта, конечно,​ работая самостоятельно. Из​Вид таблицы данных:​ C16.​ (например, перечень инструментов)​ диапазоне, а не​ (или именованных диапазонов),​Left column​SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​ которые они купили.​

​ условиям? Решение Вы​ ложны, или выполняется​Обратите внимание, что для​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​для аргумента​Давайте найдём население одной​ нужно извлечь значение.​ хватит.​

​ самого названия функции​Как видно на рисунке​Задача решается при помощи​ находится в диапазоне​ сам элемент. Например,​​ в которых содержаться​​(в столбце слева).​​НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))​​ Попробуем найти 2-й,​ найдёте далее.​ только один из​

​ двумерного поиска нужно​
​Теперь у Вас не​

​col_index_num​

• ​ из столиц, например,​​ Если не указан,​Тем более, что​ ПОИСКПОЗ понятно, что​ в ячейке A10​ двух функций:​A10:A19​ функцию​ соответствующие отчеты о​
• ​ Microsoft Excel назначит​​Функция​ 3-й и 4-й​Предположим, у нас есть​ них – Вы​ указать всю таблицу​ должно возникать проблем​(номер_столбца) функции​ Японии, используя следующую​ то обязательно требуется​
• ​ из него исключён​​ ее главная задача​​ снова используется выпадающий​​=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)​​(см. Файл примера).​ПОИСКПОЗ​ продажах. Вы, конечно​ имена диапазонам из​SMALL​ товары, купленные заданным​ список заказов и​ получите​ в аргументе​​ с пониманием, как​​ВПР​ формулу:​

​ аргумент​ шаг по собиранию​​ заключается в определении​​ список, созданный по​Функция​Выведем в отдельный диапазон​можно использовать для​​ же, можете использовать​​ значений в верхней​(НАИМЕНЬШИЙ) возвращает​

​ клиентом.​ мы хотим найти​​0​​array​ работает эта формула:​, вот так:​=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0))​column_num​ списка «искомых значений»​ позиции исходного значения,​ аналогичной схеме, описанной​ПОИСКПОЗ​ значения, которые удовлетворяют​​ передачи значения аргумента​​ обычные названия листов​ строке и левом​​n-ое​​Простейший способ – добавить​​Количество товара​​.​​(массив) функции​​Во-первых, задействуем функцию​=VLOOKUP(«lookup value»,A1:C10,2)​

​=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))​
​(номер_столбца).​

​ и «наименований листов».​

• ​ которое содержит диапазон​​ выше.​ищет в столбце​ критерию. Рассмотрим различные​номер_строки​
• ​ и ссылки на​​ столбце Вашей таблицы.​наименьшее значение в​ вспомогательный столбец перед​(Qty.), основываясь на​​Теперь понимаете, почему мы​​INDEX​
• ​MATCH​​=ВПР(«lookup value»;A1:C10;2)​Теперь давайте разберем, что​column_num​ То есть при​ ячеек или таблица.​Для определения искомого значения​D1:D13​ варианты поиска.​функции​ диапазоны ячеек, например​ Теперь Вы можете​ массиве данных. В​​ столбцом​​ двух критериях –​

Как работают ДВССЫЛ и ВПР

​ задали​(ИНДЕКС).​​(ПОИСКПОЗ), которая находит​​Если позднее Вы вставите​

​ делает каждый элемент​
​(номер_столбца) – это​

​ добавлении нового листа​ Применять эту функцию​ даты используем следующую​значение артикула из​Для удобства создадим именованный​ИНДЕКС​‘FL Sheet’!$A$3:$B$10​ осуществлять поиск, используя​ нашем случае, какую​

• ​Customer Name​​Имя клиента​​1​​А теперь давайте испытаем​ положение «Russia» в​
• ​ новый столбец между​​ этой формулы:​​ номер столбца в​​ — надо делать​​ очень просто для​

​ формулу (формула массива​ ячейки​​ диапазон Список.​​.​, но именованные диапазоны​ эти имена, напрямую,​ по счёту позицию​

​и заполнить его​(Customer) и​, как искомое значение?​ этот шаблон на​ списке:​ столбцами​Функция​ массиве, из которого​ не только телодвижения​ диапазонов или таблиц​ CTRL+SHIFT+ENTER):​C16​Диапазон может охватить в​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​​ гораздо удобнее.​​ без создания формул.​​ (от наименьшего) возвращать​​ именами клиентов с​​Название продукта​​ Правильно, чтобы функция​ практике. Ниже Вы​=MATCH(«Russia»,$B$2:$B$10,0))​A​

​MATCH​​ нужно извлечь значение.​​ по внесению его​ с одним столбцом​»»;0))))’ class=’formula’>​. Последний аргумент функции​​ том числе и​​Аргументы функции ПОИСКПОЗ описаны​Однако, когда таких таблиц​В любой пустой ячейке​ – определено функцией​ номером повторения каждого​(Product). Дело усложняется​​ПОИСКПОЗ​​ видите список самых​=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​​и​​(ПОИСКПОЗ) ищет значение​

​ Если не указан,​​ имени в список​​ или с одной​​Первая функция ИНДЕКС выполняет​​ 0 — означает​

​ незаполненные ячейки перечня.​ ниже.​ много, функция​ запишите​ROW​

​ имени, например,​
​ тем, что каждый​

​возвращала позицию только,​​ населённых стран мира.​​Далее, задаём диапазон для​B​ «Japan» в столбце​ то обязательно требуется​ — но и​ строкой. Поэтому сразу​​ поиск ячейки с​​ поиск точного (а​

​ В дальнейшем пользователь​Искомое_значение.​
​ЕСЛИ​
​=имя_строки имя_столбца​

​(СТРОКА) (смотри Часть​

office-guru.ru

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

​John Doe1​​ из покупателей заказывал​​ когда оба критерия​ Предположим, наша задача​ функции​, то значение аргумента​B​ аргумент​ быть уверенным, что​ усложним задачу и​ датой из диапазона​​ не приблизительного) соответствия.​​ может расширить перечень​    Обязательный аргумент. Значение, которое​– это не​

​, например, так:​​ 2). Так, для​​,​​ несколько видов товаров,​ выполняются.​​ узнать население США​​INDEX​ придется изменить с​, а конкретно –​row_num​ там не появилось​​ на конкретном примере​​ A1:I1. Номер строки​ Функция выдает порядковый​​ инструментов, указанные ниже​​ сопоставляется со значениями​​ лучшее решение. Вместо​​=Lemons Mar​

Синтаксис

​ ячейки​

​John Doe2​ как это видно​

• ​Обратите внимание:​​ в 2015 году.​(ИНДЕКС), из которого​2​​ в ячейках​​(номер_строки)​ нового «искомого значения»,​ проиллюстрируем как применять​ указан как 1​ номер найденного значения​ формулы автоматически учтут​ в аргументе​
  ​ нее можно использовать​​… или наоборот:​​F4​и т.д. Фокус​ из таблицы ниже:​В этом случае​Хорошо, давайте запишем формулу.​

• ​ нужно извлечь значение.​​на​B2:B10​Если указаны оба аргумента,​

• ​ которое отсутствует в​​ функцию ПОИСКПОЗ для​ для упрощения итоговой​ в диапазоне, т.е.​​ новые значения.​​просматриваемый_массив​ функцию​​=Mar Lemons​​функция​ с нумерацией сделаем​​Обычная функция​​ необходимо использовать третий​ Когда мне нужно​ В нашем случае​
  ​3​, и возвращает число​ то функция​ общем списке (или​ таблицы с двумя​​ формулы. Функция СТОЛБЕЦ​​ фактически номер строки,​

​Выведем в отдельный диапазон​	​. Например, при поиске​
​ДВССЫЛ​	​Помните, что имена строки​​НАИМЕНЬШИЙ({массив};1)​​ при помощи функции​ВПР​ не обязательный аргумент​​ создать сложную формулу​​ это​​, иначе формула возвратит​​3​ИНДЕКС​ выявить эти новые​ столбцами и более.​ возвращает номер столбца​
​ где найден требуемыый​	​ все значения Исходного​​ номера в телефонной​​(INDIRECT), чтобы возвратить​ и столбца нужно​​возвращает​​COUNTIF​​не будет работать​​ функции​ в Excel с​
​A2:A10​	​ результат из только​​, поскольку «Japan» в​​возвращает значение из​ значения и добавить​Для примера возьмем список​​ с ячейкой, в​​ артикул.​​ списка, в которых​​ книге имя абонента​ нужный диапазон поиска.​ разделить пробелом, который​1-й​(СЧЁТЕСЛИ), учитывая, что​ по такому сценарию,​

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

• ​ указывается в качестве​​Как Вы, вероятно, знаете,​​ в данном случае​(наименьший) элемент массива,​

• ​ имена клиентов находятся​​ поскольку она возвратит​​. Он необходим, т.к.​ я сначала каждую​Затем соединяем обе части​

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

Пример

​выбирает из диапазона​ дрель). Критерий вводится​ нужным значением будет​ДВССЫЛ​ пересечения.​1​=B2&COUNTIF($B$2:B2,B2)​ соответствующее заданному искомому​ мы задаем всю​Итак, начнём с двух​=INDEX($A$2:$A$10;MATCH(«Russia»;$B$2:$B$10;0))​/​INDEX​

​Вот простейший пример функции​	​- «​
​ рисунке:​	​ выполняет поиск первой​
​A1:G13​	​ в ячейку​
​ номер телефона.​	​используется для того,​
​При вводе имени, Microsoft​	​. Для ячейки​
​=B2&СЧЁТЕСЛИ($B$2:B2;B2)​	​ значению. Например, если​	​ таблицу и должны​
​ функций​	​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​ИНДЕКС​(ИНДЕКС) использует​INDEX​диапазон​	​В обеих столбцах названия​
​ непустой ячейки для​	​значение, находящееся на​С6​	​Аргумент​
​ чтобы вернуть ссылку,​	​ Excel будет показывать​F5​После этого Вы можете​ Вы хотите узнать​	​ указать функции, из​

support.office.com

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск

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

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

Задача

​, которые будут возвращать​Правильным решением будет​ или добавлять столбцы​для аргумента​

А. Найти значения, которые содержат критерий

​=INDEX(A1:C10,2,3)​ листе НЕ одинаков»,​

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

​ВПР​Sweets​ извлечь значение. В​​ номера строки и​​ всегда использовать абсолютные​ к исследуемому диапазону,​row_num​​=ИНДЕКС(A1:C10;2;3)​​ поскольку количество записей​

​ парных дубликатов. Например,​ A10 (<>”” –​ артикулом выдает функция​

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

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

• ​ ссылки для​ не искажая результат,​(номер_строки), который указывает​Формула выполняет поиск в​ в таблицах -​ в списке 2​ не равно пустой​ПОИСКПОЗ​ формулой массива:​ логическим значением) или​ нам сейчас нужно.​ вводе формулы.​ то есть​ заказ. Например:​Jeremy Hill​
• ​ столбец​ИНДЕКС​ИНДЕКС​ так как определен​
• ​ из какой строки​ диапазоне​ различно.​ автомобиля марки Ford,​ ячейке). Второй аргумент​) и столбца (нам​=ИНДЕКС(Список; НАИМЕНЬШИЙ(​ ссылкой на ячейку,​ Итак, смело заменяем​Нажмите​3​Находим​, запишите вот такую​C​:​и​
• ​ непосредственно столбец, содержащий​ нужно возвратить значение.​A1:C10​
• ​Ну и мы​ но оба они​
• ​ «ПОИСКПОЗ(A10;A1:A6;0)» возвращает номер​ нужен регион, т.е.​ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Список);0)>0;СТРОКА(Список)-СТРОКА($A$9);30);​ содержащую такое значение.​ в представленной выше​Enter​

​, и так далее.​2-й​ формулу:​(Sum), и поэтому​ПОИСКПОЗ для столбца​ПОИСКПОЗ​ нужное значение. Действительно,​ Т.е. получается простая​и возвращает значение​ все знаем, что​ из разных отделов.​ строки с выбранной​ второй столбец).​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))​Просматриваемый_массив​ формуле выражение с​​и проверьте результат​​INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​товар, заказанный покупателем​=VLOOKUP(B1,$A$5:$C$14,3,FALSE)​​ мы ввели​​– мы ищем​

​, чтобы диапазоны поиска​ это большое преимущество,​ формула:​​ ячейки во​​ произойдёт с приложением​ Если мы захотим​

Б. Найти значения, которые совпадают с критерием (точное совпадение)

​ фамилией, а «ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>»»»​Одним из основных способов​Алгоритм работы формулы следующий​    Обязательный аргумент. Диапазон ячеек,​ функцией​В целом, какой бы​​ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))​​Dan Brown​

​=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)​3​ в столбце​
​ не сбились при​
​ особенно когда работать​
​=INDEX($D$2:$D$10,3)​

B. Найти значения, которые начинаются с критерия

​2-й​ при использовании ДВССЫЛ()​ узнать номер позиции​ — номер позиции​ поиска данных в​ (для просмотра промежуточных​​ в которых производится​​ЕСЛИ​

​ из представленных выше​Функция​:​
​– эта формула вернет​
​.​
​B​

Г. Найти значения, которые заканчиваются на критерий

​ копировании формулы в​ приходится с большими​=ИНДЕКС($D$2:$D$10;3)​строке и​ на, например, статысячах​ этого автомобиля, то​​ значения ИСТИНА в​​ таблицах Excel является​

​ шагов работы формулы​ поиск.​на ссылку с​
​ методов Вы ни​
​INDEX​
​=VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE)​

​ результат​
​И, наконец, т.к. нам​, а точнее в​ другие ячейки.​ объёмами данных. Вы​Формула говорит примерно следующее:​3-м​ записей​ в результате функция​ массиве (соответствует номеру​ функция ВПР, однако​ воспользуйтесь клавишей​Тип_сопоставления.​

excel2.ru

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

​ функцией​​ выбрали, результат двумерного​​(ИНДЕКС) просто возвращает​=ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)​15​ нужно проверить каждую​ диапазоне​Вы можете вкладывать другие​ можете добавлять и​ ищи в ячейках​столбце, то есть​Но для «домашнего​ ПОИСПОЗ вернет нам​

​ столбца), полученном в​ она имеет массу​F9​    Необязательный аргумент. Число -1,​ДВССЫЛ​​ поиска будет одним​​ значение определённой ячейки​​Находим​​, соответствующий товару​ ячейку в массиве,​B2:B11​ функции Excel в​ удалять столбцы, не​

​ от​ из ячейки​ использования» — вполне.​ позицию в диапазоне​

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

​):​

​ 0 или 1.​​. Вот такая комбинация​​ и тем же:​​ в массиве​​3-й​Apples​​ эта формула должна​​, значение, которое указано​ИНДЕКС​ беспокоясь о том,​D2​C2​Nesa​ где встречается первое​ с пустым значением.​ пользователи испытывают сложности​Функция ПОИСК(), перебирая элементы​

​ Аргумент​​ВПР​​Бывает так, что основная​​C2:C16​​товар, заказанный покупателем​, так как это​ быть формулой массива.​ в ячейке​​и​​ что нужно будет​до​.​

planetaexcel.ru

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

​:​ значение – 3.​Примеры определения дат для​ при ее использовании.​ исходного списка, определяет,​тип_сопоставления​и​ таблица и таблица​. Для ячейки​Dan Brown​ первое совпадающее значение.​ Вы можете видеть​H2​ПОИСКПОЗ​ исправлять каждую используемую​D10​

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

​Очень просто, правда? Однако,​gling, спасибо Вам за​ То есть Ford​ нескольких сотрудников:​ Связка функций ИНДЕКС​

1. ​ содержится ли в​указывает, каким образом​ДВССЫЛ​
2. ​ поиска не имеют​F4​
3. ​:​Есть простой обходной путь​

​ это по фигурным​

​(USA). Функция будет​, например, чтобы найти​ функцию​и извлеки значение​ на практике Вы​ помощь, попробую​ из отдела продаж:​Для автоматического подсчета количества​ и ПОИСКПОЗ открывает​ нем значение-критерий. Если​ в Microsoft Excel​отлично работает в​ ни одного общего​функция​=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE)​ – создать дополнительный​ скобкам, в которые​ выглядеть так:​ минимальное, максимальное или​

​ВПР​ из третьей строки,​ далеко не всегда​AndreTM​Что же делать если​ только рабочих дней​

​ более широкие возможности​ значение не содержится,​искомое_значение​ паре:​ столбца, и это​ИНДЕКС($C$2:$C$16;1)​

​=ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)​ столбец, в котором​ она заключена. Поэтому,​

​=MATCH($H$2,$B$1:$B$11,0)​ ближайшее к среднему​.​

​ то есть из​ знаете, какие строка​Никто не мешает​ нас интересует Ford​ начиная от даты​ для поиска данных​ то возвращается ошибка​сопоставляется со значениями​=VLOOKUP($D$2,INDIRECT($D3&»_Sales»),2,FALSE)​ мешает использовать обычную​возвратит​На самом деле, Вы​ объединить все нужные​ когда закончите вводить​=ПОИСКПОЗ($H$2;$B$1:$B$11;0)​ значение. Вот несколько​

​3. Нет ограничения на​

​ ячейки​ и столбец Вам​ мне поставить диапазон​ из маркетингового отдела?​ приема сотрудника на​ в одной и​ #ЗНАЧ! В противном​

​ в аргументе​=ВПР($D$2;ДВССЫЛ($D3&»_Sales»);2;ЛОЖЬ)​ функцию​Apples​ можете ввести ссылку​ критерии. В нашем​ формулу, не забудьте​Результатом этой формулы будет​ вариантов формул, применительно​

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

​ случае возвращается числовое​

​просматриваемый_массив​Где:​ВПР​

1. ​, для​
2. ​ на ячейку в​

​ примере это столбцы​

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

​ нажать​4​ к таблице из​Используя​, так как счёт​

​ требуется помощь функции​

​ каждом листе (В2​ хотим использовать только​ функцию ЧИСТРАБДНИ:​

​ сразу, на что​ значение, соответствующее номеру​. По умолчанию в​$D$2​. Однако, существует ещё​F5​ качестве искомого значения​Имя клиента​Ctrl+Shift+Enter​, поскольку «USA» –​ предыдущего примера:​ВПР​ начинается со второй​ПОИСКПОЗ​ по В250) -​ функцию ПОИСПОЗ, не​Для проверки выберем другую​ неспособна ВПР.​ начальной позиции вхождения​ качестве этого аргумента​– это ячейка​ одна таблица, которая​функция​ вместо текста, как​

​(Customer) и​

​.​ это 4-ый элемент​1.​

​, помните об ограничении​ строки.​.​ не думаю что​

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

​ прибегая к формулам​ фамилию сотрудника из​Пример 1. В турнирной​ критерия в значение​ используется значение 1.​ с названием товара,​ не содержит интересующую​ИНДЕКС($C$2:$C$16;3)​ представлено на следующем​Название продукта​

​Если всё сделано верно,​

​ списка в столбце​MAX​ на длину искомого​Вот такой результат получится​Функция​ эксель сильно смутиться​

​ с комбинациями других​ выпадающего списка в​ таблице хранятся данные​ из списка (здесь​

​В приведенной ниже​

​ она неизменна благодаря​ нас информацию, но​возвратит​ рисунке:​(Product). Не забывайте,​ Вы получите результат​B​(МАКС). Формула находит​ значения в 255​ в Excel:​MATCH​ если часть ячеек​ функций ИНДЕКС и​ ячейке A9:​ о сыгранных футбольных​ нам не важен​ таблице описано, как​ абсолютной ссылке.​ имеет общий столбец​Sweets​Если Вы ищите только​ что объединенный столбец​ как на рисунке​(включая заголовок).​ максимум в столбце​ символов, иначе рискуете​Важно! Количество строк и​(ПОИСКПОЗ) в Excel​

​ будут пустыми.​ т.п. Выход из​

​Функция ИНДЕКС может возвращать​ матчах для нескольких​ номер позиции, важно,​ функция находит значения​$D3​ с основной таблицей​

​и так далее.​2-е​ должен быть всегда​ ниже:​

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

​ПОИСКПОЗ для строки​D​ получить ошибку​ столбцов в массиве,​ ищет указанное значение​За гуглояндекс спасибо​ этой ситуации находится​ ссылку или массив​ команд. Определить:​ что это число);​ в зависимости от​– это ячейка,​ и таблицей поиска.​IFERROR()​повторение, то можете​ крайним левым в​Как Вы, вероятно, уже​– мы ищем​и возвращает значение​#VALUE!​ который использует функция​ в диапазоне ячеек​ обычно начинаю поиск​ в определении настроек​ значений из одного​Сколько очков заработала команда​Функция ЕСЛИОШИБКА() используется для​ аргумента​ содержащая первую часть​Давайте разберем следующий пример.​ЕСЛИОШИБКА()​ сделать это без​ диапазоне поиска, поскольку​

​ заметили (и не​ значение ячейки​ из столбца​(#ЗНАЧ!). Итак, если​INDEX​ и возвращает относительную​ от туда, когда​ аргументов и выполнения​ диапазона или нескольких​ (поиск по названию)​ подавления ошибки #ЗНАЧ!​

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

​ на данный момент.​ заменяя ее на​.​ нашем примере это​ основная таблица (Main​ формулу внутрь функции​ более сложную формулу:​ функция​ некорректное значение, например,​

​(2015) в строке​

​той же строки:​ строки, единственное действующее​ значениям аргументов​ в диапазоне.​

exceltable.com

Вывод всех значений удовлетворяющих условию (Формулы/Formulas)

​ друг друга начинаем​​ Для этого:​
​ на вход ссылку​Суммарное значение очков, заработанных​ число 0;​Тип_сопоставления​FL​ table) со столбцом​IFERROR​=IFERROR(VLOOKUP($F$2,INDIRECT(«$B$»&(MATCH($F$2,Table4[Customer Name],0)+2)&»:$C16″),2,FALSE),»»)​ВПР​ которого нет в​
​1​=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))​
​ решение – это​row_num​

​Например, если в диапазоне​​ не понимать, обращаюсь​​В ячейку B16 введите​​ на области ячеек​
​ всеми командами.​Функция ЕСЛИ() заменяет числовые​

​Поведение​​.​
​SKU (new)​
​(ЕСЛИОШИБКА), поскольку вряд​

​=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(«$B$»&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&»:$C16″);2;ИСТИНА);»»)​​просматривает при поиске​

​ просматриваемом массиве, формула​​, то есть в​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))​
​ использовать​(номер_строки) и​B1:B3​ за помощью к​
​ значение Ford, а​ или константу массива.​Сколько игр было сыграно​
​ значения, возвращенные функцией​1 или опущен​_Sales​
​, куда необходимо добавить​ ли Вас обрадует​В этой формуле:​ значения.​ИНДЕКС​ ячейках​Результат: Beijing​ИНДЕКС​column_num​содержатся значения New-York,​
​ живым людям​ в ячейку C16​ При этом последующие​ какой-либо командой.​
​ ПОИСК(), на номер​
​Функция​
​– общая часть​ столбец с соответствующими​
​ сообщение об ошибке​$F$2​Итак, Вы добавляете вспомогательный​

excelworld.ru

Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС

​/​A1:E1​2.​/​(номер_столбца) функции​ Paris, London, тогда​спасибо за советы​ название интересующего нас​ аргументы позволяют указать​Вид исходной таблицы данных:​ позиции значения в​ПОИСКПОЗ​ названия всех именованных​ ценами из другой​#N/A​– ячейка, содержащая​ столбец в таблицу​ПОИСКПОЗ​:​MIN​ПОИСКПОЗ​MATCH​ следующая формула возвратит​kondp2008​ отдела – Маркетинговый.​ номера интересующих строки​Для удобства в ячейках​ списке. Если значение​находит наибольшее значение,​ диапазонов или таблиц.​

Пример работы ПОИСКПОЗ по двум столбцам Excel

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

​=MATCH($H$3,$A$1:$E$1,0)​(МИН). Формула находит​.​(ПОИСКПОЗ). Иначе результат​ цифру​: Добрый день​В ячейку C17 введите​ и столбца относительно​ A11, A13 и​ =0 (соответствует ошибке​ которое меньше или​ Соединенная со значением​ у нас есть​ если количество ячеек,​ неизменна, обратите внимание​ всем его ячейкам​#N/A​=ПОИСКПОЗ($H$3;$A$1:$E$1;0)​

​ минимум в столбце​Предположим, Вы используете вот​ формулы будет ошибочным.​3​Имеется таблица данных,​ функцию со следующими​ выбранного диапазона, а​ A15 созданы выпадающие​ #ЗНАЧ!), то возвращается​ равно значению аргумента​ в ячейке D3,​ 2 таблицы поиска.​ в которые скопирована​ – ссылка абсолютная);​ формулу вида:​

1. ​(#Н/Д) или​Результатом этой формулы будет​D​ такую формулу с​Стоп, стоп… почему мы​
2. ​, поскольку «London» –​ в порядке возрастания​ аргументами:​
3. ​ также порядковый номер​ списки, элементы которых​ число 30. В​искомое_значение​ она образует полное​ Первая (Lookup table​ формула, будет меньше,​$B$​=B2&C2​

​#VALUE!​5​и возвращает значение​

​ВПР​

Описание примера как работает функция ПОИСКПОЗ:

​ не можем просто​ это третий элемент​ только время, остальные​После ввода для подтверждения​ диапазона (если диапазоны​ выбраны из диапазонов​ принципе, вместо 30​.​ имя требуемого диапазона.​ 1) содержит обновленные​ чем количество повторяющихся​– столбец​. Если хочется, чтобы​(#ЗНАЧ!). Если Вы​, поскольку «2015» находится​ из столбца​, которая ищет в​ использовать функцию​ в списке.​ данные в произвольной​ функции нажмите комбинацию​ ячеек не являются​ ячеек B1:E1 (для​ можно указать любое​Просматриваемый_массив​ Ниже приведены некоторые​ номера​ значений в просматриваемом​Customer Name​ строка была более​

​ хотите заменить такое​ в 5-ом столбце.​C​ ячейках от​

exceltable.com

Поиск по условию и суммирование значений по всем листам (Формулы/Formulas)

​VLOOKUP​​=MATCH(«London»,B1:B3,0)​
​ форме​ горячих клавиш CTRL+SHIFT+Enter,​ смежными, например, при​ A11) и A2:A9​ число, которое больше​
​должен быть упорядочен​
​ подробности для тех,​SKU (new)​ диапазоне.​
​;​ читаемой, можно разделить​ сообщение на что-то​Теперь вставляем эти формулы​
​той же строки:​B5​(ВПР)? Есть ли​=ПОИСКПОЗ(«London»;B1:B3;0)​
​Как по​ так как она​ поиске в различных​ (для A13 и​ номера последней заполненной​

​ по возрастанию: …,​​ кто не имеет​и названия товаров,​Выполнение двумерного поиска в​Table4​ объединенные значения пробелом:​ более понятное, то​ в функцию​=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))​до​ смысл тратить время,​Функция​заданному условию​ должна выполнятся в​ таблицах). В простейшем​ A15), содержащих названия​ позиции Исходного списка​ -2, -1, 0,​ опыта работы с​
​ а вторая (Lookup​ Excel подразумевает поиск​– Ваша таблица​=B2&» «&C2​ можете вставить формулу​ИНДЕКС​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))​
​D10​ пытаясь разобраться в​MATCH​

​формулой вытянуть​​ массиве. Если все​ случае функция ИНДЕКС​ команд. Для создания​ (это нужно для​
​ 1, 2, …,​ функцией​ table 2) –​
​ значения по известному​ (на этом месте​

​. После этого можно​​ с​и вуаля:​Результат: Lima​значение, указанное в​
​ лабиринтах​(ПОИСКПОЗ) имеет вот​последнее заполненное значение​
​ сделано правильно в​ возвращает значение, хранящееся​ первого выпадающего списка​ правильной сортировки функцией​ A-Z, ЛОЖЬ, ИСТИНА.​
​ДВССЫЛ​ названия товаров и​ номеру строки и​ также может быть​

​ использовать следующую формулу:​​ИНДЕКС​

​=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))​​3.​ ячейке​ПОИСКПОЗ​
​ такой синтаксис:​для каждого заданного​ строке формул появятся​ в ячейке на​ необходимой перейти курсором​ НАИМЕНЬШИЙ());​0​.​ старые номера​ столбца. Другими словами,​ обычный диапазон);​=VLOOKUP(«Jeremy Hill Sweets»,$A$7:$D$18,4,FALSE)​и​=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))​AVERAGE​A2​и​MATCH(lookup_value,lookup_array,[match_type])​ значения​ фигурные скобки.​
​ пересечении строки и​ на ячейку A11.​​Функция НАИМЕНЬШИЙ() сортирует массив​​Функция​​Во-первых, позвольте напомнить синтаксис​SKU (old)​ Вы извлекаете значение​$C16​=ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)​
​ПОИСКПОЗ​Если заменить функции​(СРЗНАЧ). Формула вычисляет​:​ИНДЕКС​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​
​_Boroda_​Как видно функция самостоятельно​

​ столбца. Например, =ИНДЕКС(A2:B5;2;2)​​ Выбрать вкладку «ДАННЫЕ»​​ номеров строк по​ПОИСКПОЗ​
​ функции​
​.​ ячейки на пересечении​– конечная ячейка​или​в функцию​ПОИСКПОЗ​ среднее в диапазоне​=VLOOKUP(A2,B5:D10,3,FALSE)​?​
​lookup_value​: Так нужно?​ справилась с решением​ вернет значение, которое​ ленты меню, найти​ возрастанию;​находит первое значение,​ДВССЫЛ​
​Чтобы добавить цены из​

excelworld.ru

Поиск последнего значения по условию (Формулы/Formulas)

​ конкретной строки и​​ Вашей таблицы или​
​=VLOOKUP(B1,$A$7:$D$18,4,FALSE)​ЕСЛИОШИБКА​на значения, которые​D2:D10​=ВПР(A2;B5:D10;3;ЛОЖЬ)​
​=VLOOKUP(«Japan»,$B$2:$D$2,3)​​(искомое_значение) – это​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПРОСМОТР(2;1/(J:J=»Ц-4″);K:K)​​ поставленной задачи.​​ хранится в ячейке​ секцию с инструментами​

​Функция ДВССЫЛ() возвращает массив​​ равное аргументу​
​(INDIRECT):​

excelworld.ru

​ второй таблицы поиска​

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

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

Ниже на рисунке представленная таблица с данными о продажах по регионам за 4 года. Каждая строка представляет собой отдельную область региона, а каждый столбец – отдельный год. Допустим пользователь должен по двум условиям сделать выборку значений из таблицы:

1. Выбрать регион.
2. Указать год.

В результате должен получить значение ячейки из соответственной строки и столбца таблицы. Как в таблице Пифагора:

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

Поисковая функция ПОИСКПОЗ возвращает позицию найденного значения в списке. На рисунке сейчас выбран регион «Северный», значит функция возвращает значение 3, так как этот регион находится на третьем месте в списке. Это же число на данный момент является значением второго аргумента функции ИНДЕКС. Год 2011 найден в строке заголовка таблицы. Так как это вторая позиция в списке, то функция ПОИСКПОЗ возвращает число 2 – для третьего аргумента. Функция ИНДЕКС на основе чисел 3 и 2 возвращаемых через функцию ПОИСКПОЗ возвращает соответственное значение указанным критериям выборки пользователем.



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

Можно существенно расширить возможности выше приведенной формулы. Данные по продажам можно выводить из таблицы сразу по нескольким условиям выборки значений. Так, чтобы пользователь мог указать 1 вариант из четырех условий:

1. Указаны регион и год (как в предыдущем примере).
2. Указывать только регион.
3. Указывать только год.
4. Вообще ничего не указывать ни одного критерия выборки.

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

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

Общая структура модифицированной формулы такая же, как и в предыдущем примере. Изменено только несколько деталей. Диапазон, определенный функцией ИНДЕКС, теперь охватывает и строку листа №9 и столбец F. Так же модифицированы обе функции ПОИСКПОЗ и дополнительно расположены в аргументах функций ЕСЛИОШИБКА. Эта же функция в формуле позволяет возвращать общую сумму чисел по строкам или по столбцам благодаря охвату итоговых значений в строке B9:F9 и в столбце F3:F9.

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

На рисунке изображена та же таблица, но пользователь не указал критерий выборки «Год» в ячейке B12. Так как заголовки строк и столбцов не содержат пустых ячеек старая формула возвращает ошибку с кодом #Н/Д! В тоже время в новой измененной формуле контроль над ситуацией принимает функция ЕСЛИОШИБКА и возвращает значение из своего второго аргумента «Значение если ошибка». Таким способом в функцию ИНДЕКС просто передается номер последнего столбца. Если же не будет указан регион, а год будет указан функция ИНДЕКС будет принимать от функции ЕСЛИОШИБКА номер последней строки в исходной таблице и отображать содержимое ячейки F7 с итоговой суммой.

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

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

• • Способ 1. Функция ВПР в Excel с несколькими условиями при помощи вспомогательного столбца
  • Способ 2. ВПР с несколькими условиями в Excel при помощи сочетания функций ИНДЕКС / ПОИСКПОЗ
  • Способ 3. ВПР по двум условиям при помощи формулы массива
  • Способ 4. ВПР с несколькими условиями при помощи функции СУММЕСЛИМН

Это самый распространенный и самый простой способ в excel сделать ВПР двух или нескольких значений. 

Рассмотрим на примере. Есть две таблицы — таблица-источник (зеленая “шапка”) и рабочая таблица (синяя “шапка”), в которую нужно подтянуть количество автомобилей из источника по трем условиям: марка, модель и цвет автомобиля.

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

Вспомним синтаксис функции ВПР:

=ВПР(искомое_значение; таблица; номер столбца; [интервальный просмотр])

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

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

Как видите, функция ВПР в excel с несколькими условиями (а данном случае три условия) подтянула значение из выделенной строки.

---

   Сообщество Excel Analytics | обучение Excel

    Канал на Яндекс.Дзен 

---

Способ 2. ВПР с несколькими условиями в Excel при помощи сочетания функций ИНДЕКС / ПОИСКПОЗ

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

Этот способ удобен тем, что не нужно создавать дополнительных столбцов в таблице-источнике (на практике не всегда возможно добавить столбец — источник может быть защищен от изменений).

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

Скопируем в нашем примере строку с условиями поиска и напишем следующую формулу:

После написания формулы необходимо нажать сочетание клавиш Ctrl + Shift + Enter. Это необходимо сделать, т.к. это формула массива — в противном случае выйдет ошибка #ЗНАЧ.

Давайте разберем формулу:

=ИНДЕКС(L4:L13;ПОИСКПОЗ(B5&C5&D5;I4:I13&J4:J13&K4:K13;0))

• • • L4:L13 — массив, из которого будет подтягиваться информация. Тот столбец, данные из которого нам нужны.
    • B5&C5&D5 — критерии для поиска, которые мы объединили между собой оператором конкатенации &.
    • I4:I13&J4:J13&K4:K13 — столбцы, в которых будут происходить поиск по заданным критериям. 

I4:I13 — столбец с марками автомобилей (соответствует критерию в ячейке В5)

J4:J13 — столбец с моделями автомобилей (соответствует критерию в ячейке С5)

K4:K13 — столбец с цветом автомобилей (соответствует критерию в ячейке D5)

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

• • • 0 — аргумент, обозначающий, что нужен поиск точного совпадения.

Способ 3. ВПР по двум условиям при помощи формулы массива

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

Запишем следующую формулу для поиска:

=ВПР(C6;ЕСЛИ(K4:K13=D6;J4:L13;0);3;0)

В конце обязательно нужно нажать сочетание клавиш Ctrl + Shift + Enter, т.к. это формула массива, иначе будет ошибка #Н/Д.

Разберем, как работает эта формула. 

С6 — это первый критерий для поиска. Но поскольку у нас есть еще один критерий (D6), то искать C6 формула будет но во всем столбце J, а только в той строке, где будет совпадение с столбце К со значением второго критерия (D6).

Таким образом, при помощи конструкции внутри формулу ВПР 

ЕСЛИ(K4:K13=D6;J4:L13;0)

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

А дальше формула ВПР по двум условиям работает как обычная ВПР — указывается номер столбца 3 и интервальный просмотр 0 (точный поиск).

Способ 4. ВПР с несколькими условиями при помощи функции СУММЕСЛИМН

Функция СУММЕСЛИМН является полноценной альтернативой функции ВПР, если нужно подтянуть числовой результат по нескольким условиям.

Отличие функции СУММЕСЛИМН от ВПР в следующем: ВПР ищет самое первое совпадение и возвращает данные по строке с этом первом совпадении. А СУММЕСЛИМН просуммирует все значения, соответствующие критериям поиска. Нужно учитывать эту особенность.

Синтаксис функции СУММЕСЛИМН:

=СУММЕСЛИМН(Диапазон_суммирования; Диапазон_условия1; Условие1;…; Диапазон_условияN; УсловиеN)

Подтянем данные в нашу рабочую таблицу из таблицы-источника по тем же критериям, но уже при помощи функции СУММЕСЛИМН.

Функция СУММЕСЛИМН просуммировала все значения в столбце L, у которых значение в столбце I равно значению В7, значения в столбце J — C7, а значения в столбце K — D7. Как видите, результат такой же, как и при других способах.

Но, если мы добавим еще одну такую же строчку, то результат вычисления функции изменится. Именно эту особенность и нужно учитывать, если вы используете СУММЕСЛИМН вместо ВПР по нескольким условиям.

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

Вам может быть интересно: