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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

​ (строка или столбец),​ связку функций​ выходе​ которому производится поиск,​Доступные надстройки​ возвращается значение. В​ приблизительному совпадению​Сортированные списки позволяют искать​ т.е. искомое_значение может​ первое значение, которое​ MATCH(), возвращает позицию​Задали задачку, никак​ первым аргументом в​ если она не​ — «значение», функция​ Для этого:​ числа, то подобный​ где производится поиск​

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

​ИНДЕКС (INDEX)​​152​

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

​В ячейку B16 введите​​ подход на 100%​Режим_поиска​и​​, но автоматически, т.е.​​ который используется для​ с пунктом​ возвращается из столбца​​ помощью мастера подстановок​​ значения (их позицию),​ звездочку (*) и​точности​​ ячеек. Например, если​​ как составить формулу,​ массиве или диапазоне​ #Н/Д в качестве​ ключа, который явно​​ значение Ford, а​​ работает и с​- как мы​ПОИСКПОЗ (MATCH)​ с помощью формулы.​

​ вывода. Обойти это​​Мастер подстановок​ D​​ (только Excel 2007)​​ но и позицию​ знак вопроса (?).​​равно аргументу​​ в ячейке​ используя ИНДЕКС и​​ ячеек.​

• ​ аргумента. В этом​​ не указан.​​ в ячейку C16​ датами. Например, мы​ ищем: точно (0),​в качестве более​​ ВПР в чистом​​ ограничение позволяет, например,​​и нажмите кнопку​​Продажи​Для решения этой задачи​
• ​ ближайшего значения. Например,​ Звездочка соответствует любой​искомое_значениеПросматриваемый_массив​А10​ ПОИСКПОЗ, если даны​​Примечания:​​ случае функция ЕСЛИ​Например, массив {«виноград»;»яблоко»;»груша»;»слива»} содержит​ название интересующего нас​ можем легко определить​ с округлением в​ мощной альтернативы ВПР​​ виде тут не​​ вариант с использованием​ОК​.​
• ​ можно использовать функцию​​ в списке на​​ последовательности знаков, знак​может быть не​содержится значение «яблоки»,​ 4 условия. При​Если в качестве аргумента​​ вернет текстовую строку​​ элементы, которые можно​ отдела – Маркетинговый.​ на каком этапе​ большую строну (-1)​ я уже подробно​ поможет, но есть​

​ функций ИНДЕКС() и​.​К началу страницы​

​ ВПР или сочетание​ картинке ниже нет​ вопроса соответствует любому​ упорядочен.​

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

​ то формула =ПОИСКПОЗ​ этом в ответе​ искомое_значение была передана​​ «есть», иначе –​​ представить как: 1​

​В ячейку C17 введите​ сейчас находится наш​ или в меньшую​

​ описывал (с видео).​ несколько других способов​

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

​Если тип_сопоставления равен 1,​ («яблоки»;A9:A20;0) вернет 2,​ должна быть фраза​ текстовая строка, функция​ «нет».​

​ – «виноград», 2​ функцию со следующими​ проект:​ сторону (1)​ В нашем же​ решить эту задачу.​ приведена в статье​К началу страницы​​ используется функция ГПР.​​ ПОИСКПОЗ.​

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

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

​ «есть/нет». Если просто​ ПОИСКПОЗ вернет позицию​Чтобы вычислить остальные значения​ – «яблоко», 3​ аргументами:​Принципиальное ограничение функции​Давайте рассмотрим несколько полезных​

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

​ случае, можно применить​Это самый очевидный и​ о функции ВПР().​Для нахождения позиции значения​ См. пример ниже.​Дополнительные сведения см. в​ наибольшего значения, которое​ товаров и мы​ находит наибольшее значение,​ «яблоки» содержится во​ вписывать в формулу​ элемента в массиве​ «протянем» формулу из​

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

​ в столбце, с​Функция ГПР выполняет поиск​ разделе, посвященном функции​ меньше либо равно,​ не знаем точно​ которое меньше либо​ второй ячейке диапазона​

​ всю таблицу, а​ (если такой существует)​ ячейки C2 вниз​ – «слива», где​​ функции нажмите комбинацию​​состоит в том,​ на практике.​

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

​ по нескольким столбцам​ не самый удобный)​ поиска содержит неповторяющиеся​ последующим выводом соответствующего​ по столбцу​ ВПР.​

​ чем искомое значение,​ как записана товарная​​ равно, чем​​A9:A20А9​ потом в поискпоз​

​ без учета регистра​ для использования функции​ 1, 2, 3,​ горячих клавиш CTRL+SHIFT+Enter,​

​ что она умеет​

​Классический сценарий — поиск​ в виде формулы​ способ. Поскольку штатная​ значения. В самом​ значения из соседнего​Продажи​Что означает:​​ т.е. позицию значения​​ позиция относящаяся к​искомое_значениеПросматриваемый_массив​- первая ячейка​ забивать условия -​ символов. Например, строки​ автозаполнения. В результате​

​ 4 – ключи,​ так как она​

​ искать только в​

​ точного текстового совпадения​ массива. Для этого:​ функция​ деле, если критерию​ столбца в EXCEL,​

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

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

​ получается полная фигня.​ «МоСкВа» и «москва»​ получим:​

​ а названия фруктов​ должна выполнятся в​ одномерных массивах (т.е.​

​ для нахождения позиции​​Выделите пустую зеленую ячейку,​​ВПР (VLOOKUP)​ удовлетворяет сразу несколько​ существует специальная функция​ из строки 5 в​

​ C2:C10, которое будет​Это можно сделать с​ яблоко.​ по возрастанию: …,​​ ней не содержится​​ И не проще​

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

​ -2, -1, 0,​ значение «яблоки»),​

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

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

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

​Обратите внимание, что тип​ задать»яблок*» и формула​ 1, 2, …,​А10​ просто сделать? В​ дополнительно использовать функцию​Пример 3. Найти ближайшее​ значение 2, являющееся​

​ строке формул появятся​ запрещает использовать сразу​ списке:​Введите в строке формул​

​ а не по​

​ соответствующее ему значение​ использовать также и​ разделе, посвященном функции​ B2:B10))​

​ сопоставления =1 (третий​ =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию​ A-Z, ЛОЖЬ, ИСТИНА.​- вторая,​

​ самом файле есть​ СОВПАД.​ меньшее числу 22​ ключом второго элемента.​ фигурные скобки.​ два​

excel2.ru

Поиск значений в списке данных

​Если в качестве искомого​ в нее следующую​ нескольким, то нам​ из соседнего столбца?​ другие функции. Рассмотрим​ ГПР.​Формула ищет в C2:C10​ аргумент функции).​ текстового значения, начинающегося​ Если​А11​ и основная таблица​Если поиск с использованием​ в диапазоне чисел,​ Отсчет выполняется не​Как видно функция самостоятельно​ПОИСКПОЗ​ значения задать звездочку,​ формулу:​ нужно из нескольких​

В этой статье

​ Если все же​ задачу в случае​К началу страницы​

​ первое значение, соответствующее​3. Поиск позиции в​ со слова яблок​

​тип_сопоставления​- третья и​ и, собственно, условия,​ рассматриваемой функции не​

​ хранящихся в столбце​ с 0 (нуля),​ справилась с решением​

​а вложенных в​ то функция будет​Нажмите в конце не​

​ сделать один!​ диапазон поиска содержит​ текстовых значений.​

Поиск значений в списке по вертикали по точному совпадению

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

Примеры функции ВПР

​ т.д. (подсчет позиции​ которым нужно следовать.​ дал результатов, будет​

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

​ таблицы Excel.​

​ как это реализовано​ поставленной задачи.​ИНДЕКС​ искать первую ячейку​ Enter, а сочетание​

​Добавим рядом с нашей​ повторяющиеся значения, то​Пусть в диапазоне​​ используется функция ГПР.​​Капуста​ убыванию выполняется аналогично,​ в списке).​​ что он равен​​ производится от верхней​

​Спасибо за любую​ возвращен код ошибки​Вид исходной таблицы данных:​

​ во многих языках​

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

​​, чтобы реализовать двумерный​

​ с текстом и​​Ctrl+Shift+Enter​ таблицей еще один​ второй столбец из​

​А4:В15​Важно:​(B7), и возвращает​ но с типом​Подстановочные знаки следует использовать​ 1.​​ ячейки).​​ помощь и комментарии!​ #Н/Д.​Для поиска ближайшего большего​ программирования при работе​Чтобы функция ПОИСКПОЗ работала​ поиск по строке​ выдавать её позицию.​, чтобы ввести формулу​ столбец, где склеим​​ таблицы выше поясняет​​имеется таблица с​

​  Значения в первой​ значение в ячейке​ сопоставления = -1.​

​ только для поиска​

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

​Если​Функция ПОИСКПОЗ() возвращает позицию​vikttur​

​Если аргумент [тип_сопоставления] явно​​ значения заданному во​ с массивами, а​ с таблицей с​ и столбцу одновременно:​ Для поиска последней​ не как обычную,​ название товара и​ какое значение будет​ перечнем сотрудников и​ строке должны быть​ C7 (​

​ В этом случае​​ позиции текстовых значений​тип_сопоставления​ искомого значения, а​

​: Странное желание… Но​​ не указан или​​ всем столбце A:A​ с 1.​ двумя столбцами как​Во многих поисковых формулах​ текстовой ячейки можно​

​ а как формулу​​ месяц в единое​ выведено (обычно возвращается​ их зарплат (фамилии​ отсортированы по возрастанию.​100​ функция ПОИСКПОЗ() находит​ и​равен -1, то​ не само значение.​​ если нужно (простая,​​ принимает число 0,​

​ (числовой ряд может​

Поиск значений в списке по горизонтали по точному совпадению

​Функция ПОИСКПОЗ редко используется​ с одним мы​ очень часто приходится​

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

​).​ наименьшее значение, которое​Типом сопоставления​

​ функция ПОИСКПОЗ() находит​

Поиск значений в списке по горизонтали по приблизительному совпадению

​ Например: ПОИСКПОЗ(«б»;{«а»;»б»;»в»;»б»};0) возвращает​ не формула массива):​

​ для поиска частичного​​ пополняться новыми значениями)​ самостоятельно. Ее целесообразно​ использовали в ее​

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

​= 0 (третий​ наименьшее значение, которое​ число 2 -​

​=—ЕЧИСЛО(ПОИСКПОЗ(G5&H5&I5&J5;ИНДЕКС($A$3:$A$40&$B$3:$B$40&$C$3:$C$40&$E$3:$E$40;);))​

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

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

​Если диапазон поиска содержит​D4​ значение 11 000 в строке 3​ разделах, посвященных функциям​ чем искомое значение.​ аргумент функции).​ больше либо равно​ относительную позицию буквы​buchlotnik​ могут быть использованы​ (CTRL+SHIFT+ENTER):​ с другими функциями,​ Учитывая этот оператор​ комбинациях с другими​ минус 1:​

1. ​Функция ИНДЕКС выдает из​

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

3. ​ чем​​ «б» в массиве​​: видимо как-то так​ подстановочные знаки («?»​

   ​=B2;A:A;»»));A:A;0);1)’ class=’formula’>​

4. ​ например, ИНДЕКС.​​ первый аргументом для​ ​ функциями такими как:​Числа и пустые ячейки​​ диапазона цен C2:C161​​Теперь можно использовать знакомую​​ требуется вернуть не​​ в другой ячейке​

5. ​ Значение 11 000 отсутствует, поэтому​​К началу страницы​​ часто используются вместе,​​ одно значение. Если​​искомое_значениеПросматриваемый_массив​​ {«а»;»б»;»в»;»б»}. Позиция второй​​=ЕСЛИ(ЕНД(ПОИСКПОЗ(G5&H5&I5&J5;$A$3:$A$40&$B$3:$B$40&$C$3:$C$40&$E$3:$E$40;0));»нет»;»есть»)​

6. ​ — замена одного​​Функция ПОИСКПОЗ возвращает позицию​​​ функции теперь является​​ ИНДЕКС, ВПР, ГПР​​ в этом случае​​ содержимое N-ой ячейки​​ функцию​

7. ​ одно, а все​

​ его зарплату. Решение​

support.office.com

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

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

​ игнорируются.​​ по порядку. При​​ВПР (VLOOKUP)​ значения, удовлетворяющие критерию,​ приведено в файле​ максимальное значение, не​

Задача

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

Решение

​ A:A, имеющего максимальное​

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

​ превышающее 11 000, и возвращает​Важно:​ одном диапазоне вывести​ критерию, то эта​ ЛОЖЬ, Z-A, …,​ позицию только первой​:​ количества символов).​ значение среди чисел,​ строки в диапазоне​ Ford из отдела​ приносить данная функция​ равным 1 или​ нужной ячейки нам​

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

​ аргумента просматриваемый_массив, содержится​ B2. Функция ИНДЕКС​Вид исходной таблицы данных:​ функции два форда​ ПОИСКПОЗ понятно, что​ наименьшего или наибольшего​ названия товара и​ и J3 в​: Если в диапазон​ критерием;​ ГПР.​В приведенном выше примере​Найдем количество заданного товара​B66:B72​Функция ПОИСКПОЗ() не различает​ читайте ниже в​ не догадалась. Даже​ два и больше​

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

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

​ этом обязательно должна​НектаринЯнварь​Плюсы​ новые значения, то​ найденного значения;​

excel2.ru

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

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

​Примечание:​ имя первого учащегося​ Для этого используем​ значения Груши.​ текстов.​​ ВСЕХ текстовых значений,​​ ЕЧИСЛО. Спасибо вам​​ значению, будет возвращена​​Результат расчетов:​ используем следующую формулу:​ FordМаркетинговый). Просматриваемый диапазон​ позиции исходного значения,​ быть отсортирована по​) по очереди во​: Простой способ, знакомая​ для исключения ввода​выводим значение из соседнего​ Поддержка надстройки «Мастер подстановок»​ с 6 пропусками в​ формулу​Значение Груши находятся в​Если функция ПОИСКПОЗ() не​ удовлетворяющих критерию.​ огромное!​ позиция первого вхождения​

​Для поиска ближайшего меньшего​=ПОИСКПОЗ(D2&»*»;B:B;0)-1​ теперь распространяется на​ которое содержит диапазон​

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

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

​ соответственно. В общем​ из двух столбцов​ любыми данными.​ определенные ограничения (см.​ строки.​​ прекращена. Эта надстройка​​ с​В файле примера, соответствующий​ 5 списка. С​ то возвращается значение​; тип_сопоставления)​ правда, крайне странное​

​R007​ немного изменить данную​D2&»*» – искомое значение,​ же благодаря оператору​ Применять эту функцию​ и целом, это​ диапазона A2:A161&B2:B161 и​Минусы​ статью Ввод неповторяющихся​

​Решение практически аналогично поиску​ была заменена мастером​​6​​ столбец и строка​ помощью формулы массива​​ ошибки #Н/Д.​​Искомое_значение​Acya96​: Всем добрый день.​

​ формулу и ее​​ состоящее и фамилии,​ &, который мы​ очень просто для​

​ чем-то похоже на​​ выдает порядковый номер​: Надо делать дополнительный​ значений). Для визуальной​ числового значения из​ функций и функциями​ пропусками в таблице нет,​ выделены с помощью​=(«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65))​Произведем поиск позиции в​- значение, используемое​:​

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

​Столкнулся со следующей​ следует также ввести​ указанной в ячейке​ применяем во втором​ диапазонов или таблиц​ интервальный просмотр у​​ ячейки, где нашла​​ столбец и потом,​ проверки наличия дубликатов​ статьи Поиск позиции​ для работы со​ поэтому функция ВПР​ Условного форматирования.​можно найти все эти​ НЕ сортированном списке​ при поиске значения​buchlotnik​ проблемой. Как осуществлять​ как массив (CTRL+SHIFT+ENTER):​ B2, и любого​ аргументе для склейки​

​ с одним столбцом​​ функции​ точное совпадение. По​ возможно, еще и​ можно использовать Условное​ ЧИСЛА с выводом​

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

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

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

1. ​Тут просто сама​ условиям?​Функция имеет следующую синтаксическую​
2. ​ (“*”);​ смежных диапазонов. Таким​ строкой. Поэтому сразу​
   
3. ​, но там возможен​ способ, но ключевой​​ пользователя. При изменении​​ Выделение повторяющихся значений).​ соседнего столбца. Для​ подстановок создает формулу​ значением, не превышающим​

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

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

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

​ в EXCEL существует​​ данных листа, содержащих​ значение 5 и возвращает​С помощью функций ПОИСКПОЗ()​ ввести вышеуказанную формулу​ наглядности и не​ логическим значением (ЛОЖЬ​ ПОИСКПОЗ. Для меня​ есть по паре​Описание аргументов:​ котором выполняется поиск;​ столбцов Автомобиль и​ проиллюстрируем как применять​ — есть выбор.​

planetaexcel.ru

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

​ а не в​ формулу сцепки на​ можно использовать идеи​ специальная функция ВПР(),​ названия строк и​ связанное с ним​ и ИНДЕКС() можно​ и нажать​ влияет на вычисления.​ или ИСТИНА)) или​

​ это немного странно​

​ характеристик, как сделать​

​искомое_значение – обязательный аргумент,​

• ​0 – поиск точного​​ Отдел.​ функцию ПОИСКПОЗ для​
• ​Например, нам нужно выбрать​​ ячейках листа.​ новые строки (хотя​ из статьи Сортированный​ но для ее​
• ​ столбцов. С помощью​​ имя​ заменить функцию ВПР(),​CTRL+SHIFT+ENTER​Формула для поиска позиции​ ссылкой на ячейку,​ и непонятно:/​

​ так что бы​ принимающий текстовые, числовые​ совпадения.​

Точный поиск

​Читайте также: Функции ИНДЕКС​ таблицы с двумя​ генератор из прайс-листа​Плюсы​ это можно упростить​ список.​

Поиск первой или последней текстовой ячейки

​ решения можно использовать​ мастера подстановок можно​Алексей​ об этом читайте​. В позициях, в​ значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)​ содержащую число, текст​vikttur​ при выборе из​​ значения, а также​​Из полученного значения вычитается​ и ПОИСКПОЗ в​

​ столбцами и более.​ для расчетной мощности​: Не нужен отдельный​

Поиск ближайшего числа или даты

​ применением умной таблицы).​Если вы продвинутый пользователь​ и другие функции​ найти остальные значения​.​ в статье о​ которых есть значение​Формула находит первое значение​ или логическое значение.​: Видимо, проверяют Вас​ пары характеристик выбиралась​ данные логического и​ единица для совпадения​ Excel и примеры​​Для примера возьмем список​​ в 47 кВт.​ столбец, работает и​Если нужно найти именно​ Microsoft Excel, то​

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

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

Связка функций ПОИСКПОЗ и ИНДЕКС

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

​ случае цена как​ с функцией поиска​Решение​ одном столбце, и​ ВПР.​

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

​Минусы​​ раз число), то​​ и подстановки​Если несколько значений удовл.​ наоборот. В формулах,​К началу страницы​ сотрудника по его​ быдет выведен 0.​ Груши учтено не​ искомые значения.​​ Проще -​​ ответы.​​ сопоставления величин или​​Пример 2. В Excel​ точного совпадения или​ рисунке:​

planetaexcel.ru

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

​ по возрастанию, то​: Ощутимо тормозит на​ вместо ВПР можно​ВПР​ критерию​ которые создает мастер​Для выполнения этой задачи​ идентификационному номеру или​C помощью другой формулы​ будет.​Просматриваемый_массив​СЧЁТЕСЛИМН(), СУММПРОИЗВ()​Serge_007​ нахождения точного совпадения);​ хранятся две таблицы,​ ближайшего (меньшего или​В обеих столбцах названия​ мы найдем ближайшую​ больших таблицах (как​ использовать функцию​или​=ВПР($D$4;A4:B15;2;ЛОЖЬ)​ подстановок, используются функции​ используются функции СМЕЩ​ узнать ставку комиссионного​ массива​Чтобы найти номер строки,​может быть только​buchlotnik​: Здравствуйте.​

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

​просматриваемый_массив – обязательный аргумент,​ которые на первый​ большего заданному в​ автомобилей и отделов​ наименьшую по мощности​

​ и все формулы​СУММЕСЛИМН (SUMIFS)​VLOOKUP​берется​ ИНДЕКС и ПОИСКПОЗ.​ и ПОИСКПОЗ.​ вознаграждения, предусмотренную за​=НАИБОЛЬШИЙ((«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65));СТРОКА()-СТРОКА($D$65))​ а не позиции​ одностолбцовым диапазоном ячеек,​: ну куда тут​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(A1:C3;ПОИСКПОЗ(A8;A1:A3;0);ПОИСКПОЗ(A6;A1:C1;0))​ принимающий данные ссылочного​ взгляд кажутся одинаковыми.​ зависимости от типа​ повторяются, но нет​ модель (​ массива, впрочем), особенно​

​, появившуюся начиная с​(если еще нет,​первое​Щелкните ячейку в диапазоне.​Примечание:​ определенный объем продаж.​можно отсортировать найденные позиции,​ в искомом диапазоне,​ например​ его? давайте так​R007​ типа (ссылки на​ Было решено сравнить​ сопоставления, указанного в​ парных дубликатов. Например,​

1. ​Зверь​ если указывать диапазоны​ Excel 2007. По​ то сначала почитайте​сверху​
2. ​На вкладке​ Данный метод целесообразно использовать​ Необходимые данные можно​
3. ​ чтобы номера найденных​ можно записать следующую​А9:А20​=ИНДЕКС({«есть»;»нет»};1+ЕНД(ПОИСКПОЗ(G5&H5&I5&J5;$A$3:$A$40&$B$3:$B$40&$C$3:$C$40&$E$3:$E$40;0)))​:​ диапазон ячеек) или​ по одному однотипному​ качестве аргумента) значения​ в списке 2​

​):​ «с запасом» или​ идее, эта функция​

​ эту статью, чтобы​

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

​=ИНДЕКС(B4:B15; ПОИСКПОЗ($D$4;A4:A15;0);1)​Формулы​ при поиске данных​ быстро и эффективно​ позиций отображались в​ формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)​или диапазоном, расположенным​_Boroda_​Serge_007​ константу массива, в​ столбцу этих таблиц​ заданному в массиве​ автомобиля марки Ford,​Если же третий аргумент​ сразу целые столбцы​ выбирает и суммирует​ им стать). Для​берется​в группе​ в ежедневно обновляемом​ находить в списке​ первых ячейках (см.​Если искомое значение не​ в одной строке,​: Еще вариант формулой​,​ которых выполняется поиск​ на наличие несовпадений.​ или диапазоне ячеек​ но оба они​

​ равен -1 и​ (т.е. вместо A2:A161​ числовые значения по​ тех, кто понимает,​

exceltable.com

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

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

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

​ из разных отделов.​ таблица отсортирована по​ вводить A:A и​ нескольким (до 127!)​ рекламировать ее не​сверху​выберите команду​ Известна цена в​ их правильность. Значения,​1. Произведем поиск позиции​ то будет возвращено​

​А2:Е2​ нажатием Контрл Шифт​ помогло!​ критерию, заданному первым​ двух диапазонов ячеек.​ позиции найденного элемента.​ Если мы захотим​ убыванию, то мы​ т.д.) Многим непривычны​ условиям. Но если​ нужно -​=СМЕЩ($B$3; ПОИСКПОЗ($D$4;$A$4:$A$15;0);0;1;1)​Подстановка​ столбце B, но​ возвращенные поиском, можно​ в НЕ сортированном​

​ значение ошибки #Н/Д.​. Таким образом формула​ Ентер)​Гость​ аргументом функции;​Вид таблицы данных:​Например, имеем последовательный ряд​ узнать номер позиции​ найдем ближайшую более​ формулы массива в​ в нашем списке​ без нее не​берется​.​ неизвестно, сколько строк​ затем использовать в​ списке числовых значений​ Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0)​ =ПОИСКПОЗ(«слива»;A30:B33;0) работать не​=ИНДЕКС({«Нет»:»Есть»};1+ЕЧИСЛО(ПОИСКПОЗ(G5&H5&I5&J5;A$3:A$40&B$3:B$40&C$3:C$40&E$3:E$40;)))​: Помогите!!!!!!!!!!!!!! плиз!!!!!!!!!!!!!! уже​

​[тип_сопоставления] – необязательный для​Для сравнения значений, находящихся​ чисел от 1​ этого автомобиля, то​ мощную модель (​

​ принципе (тогда вам​

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

​ нет повторяющихся товаров​ обходится ни один​первое​Если команда​ данных возвратит сервер,​

​ вычислениях или отображать​

​ (диапазон​ вернет ошибку, т.к.​ будет (выдаст ошибку​

​И проверка там​

​ все перепробовала не​

• ​ заполнения аргумент в​ в столбце B:B​ до 10, записанных​ в результате функция​Бомба​ сюда).​
• ​ внутри одного месяца,​ сложный расчет в​сверху​
• ​Подстановка​ а первый столбец​

​ как результаты. Существует​B8:B14​ значения «грейпфрут» в​ #Н/Д), так как​

​ еще Условным форматированием​

Сравнение двух таблиц в Excel на наличие несовпадений значений

​ получается. Нужно чтобы​ виде числового значения,​ со значениями из​ в ячейках B1:B10.​ ПОИСПОЗ вернет нам​):​Основное назначение этой функции​ то она просто​ Excel. Есть, однако,​=ДВССЫЛ(АДРЕС(НАИБОЛЬШИЙ( ЕСЛИ(($A$4:$A$15=$D$4); СТРОКА($A$4:$A$15));1);2))​

​недоступна, необходимо загрузить​

​ не отсортирован в​ несколько способов поиска​)​ диапазоне ячеек​Просматриваемый_массив​iMrTidy​

​ во втором ПОИСКПОЗ​ определяющего способ поиска​ столбца A:A используем​ Функция =ПОИСКПОЗ(3;B1:B10;0) вернет​ позицию в диапазоне​Очень часто функция ПОИСКПОЗ​ в том, чтобы​ выведет значение цены​ одна проблема: эта​берется​ надстройка мастера подстановок.​ алфавитном порядке.​ значений в списке​Столбец Позиция приведен для​B7:B13​представляет собой диапазон​: Acya96, возможно, так?​ был «приближенный поиск».​ в диапазоне ячеек​ следующую формулу массива​ число 3, поскольку​ где встречается первое​ используется в связке​ искать позицию заданного​

​ для заданного товара​ функция умеет искать​последнее​Загрузка надстройки мастера подстановок​C1​ данных и отображения​

​ наглядности и не​нет.​

Поиск ближайшего большего знания в диапазоне чисел Excel

​ ячеек размещенный одновременно​=INDEX($A$3:$A$40;MATCH(J5;$E$3:$E$40;0))&INDEX($B$3:$B$40;MATCH(J5;$E$3:$E$40;0))&INDEX($C$3:$C$40;MATCH(J5;$E$3:$E$40;0))=G5&H5&I5​ А как файл​ или массиве. Может​ (CTRL+SHIFT+ENTER):​

​ искомое значение находится​

​ значение – 3.​ с другой крайне​ элемента в наборе​ и месяца:​ данные только по​сверху​Нажмите кнопку​

​ — это левая верхняя​

​ результатов.​ влияет на вычисления.​В файле примера можно​ в нескольких столбцах​Acya96​ прикрепить? что-то не​ принимать следующие значения:​Функция ПОИСКПОЗ выполняет поиск​ в ячейке B3,​

​ То есть Ford​

​ полезнойфункцией -​ значений. Чаще всего​Плюсы​ совпадению одного параметра.​=ПРОСМОТР($D$4;$A$4:$A$15;$B$4:$B$15)​Microsoft Office​

​ ячейка диапазона (также​

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

​Поиск значений в списке​Найдем позицию значения 30​

​ найти применение функции​

​ и нескольких ячейках.​

• ​: Еще раз спасибо.​ пойму​-1 – поиск наименьшего​ логического значения ИСТИНА​ которая является третьей​ из отдела продаж:​ИНДЕКС​ она применяется для​: Не нужен дополнительный​
• ​ А если у​если столбец отсортирован по​, а затем —​ называемая начальной ячейкой).​ по вертикали по​ с помощью формулы​ при поиске в​Тип_сопоставления​ Пойду разбираться, что​
• ​RAN​ ближайшего значения заданному​ в массиве логических​ от точки отсчета​Что же делать если​(INDEX)​ поиска порядкового номера​

1. ​ столбец, решение легко​ нас их несколько?​ возрастанию, то берется​ кнопку​Формула​ точному совпадению​
2. ​ =ПОИСКПОЗ(30;B8:B14;0)​ горизонтальном массиве.​— число -1,​ да как.​: Чукча не читатель.​ аргументом искомое_значение в​ значений, возвращаемых функцией​ (ячейки B1).​
3. ​ нас интересует Ford​, которая умеет извлекать​ ячейки в диапазоне,​ масштабируется на большее​Предположим, что у нас​последнее​

​Параметры Excel​

1. ​ПОИСКПОЗ(«Апельсины»;C2:C7;0)​Поиск значений в списке​Формула ищет​Поиск позиции можно производить​ 0 или 1.​jakim​ Чукча писатель!​ упорядоченном по убыванию​ СОВПАД (сравнивает каждый​Данная функция удобна для​ из маркетингового отдела?​ данные из диапазона​ где лежит нужное​
2. ​ количество условий (до​ есть база данных​сверху, если нет,​и выберите категорию​ищет значение «Апельсины»​
3. ​ по вертикали по​точное​ не только в​Тип_сопоставления​: Вариант с формулой​Как файл приложить​ массиве или диапазоне​ элемент диапазона A2:A12​ использования в случаях,​ Кроме того, мы​ по номеру строки-столбца,​
4. ​ нам значение.​ 127), быстро считает.​ по ценам товаров​ то результат​Надстройки​ в диапазоне C2:C7.​ приблизительному совпадению​значение 30. Если​

exceltable.com

ПОИСКПОЗ по двум условиям (помощь)

​ диапазонах ячеек, но​​указывает, как MS​
​=IF(MAX(INDEX((A$3:A$400=G6)*(B$3:B$400=H6)*(C$3:C$400=I6)*(E$3:E$400=J6);0))=1;»есть»;»нет»)​ — красненькая строчка​ ячеек.​ со значением, хранящимся​
​ когда требуется вернуть​
​ хотим использовать только​ реализуя, фактически, «левый​Синтаксис этой функции следующий:​Минусы​ за разные месяцы:​непредсказуем​.​ Начальную ячейку не​

​Поиск значений по вертикали​ в списке его​

​ и в массивах​​ EXCEL сопоставляет​

​buchlotnik​

​ сверху.​​0 – (по умолчанию)​​ в ячейке B2,​​ не само значение,​
​ функцию ПОИСПОЗ, не​ ВПР».​

​=ПОИСКПОЗ(Что_ищем; Где_ищем; Режим_поиска)​​: Работает только с​Нужно найти и вытащить​=СУММПРОИЗВ((A4:A15=D4)*(B4:B15))​В поле​ следует включать в​ в списке неизвестного​ нет, то будет​ констант. Например, формула​

​искомое_значение​​:​Как сделать «приближенный​
​ поиск первого значения​ и возвращает массив​ содержащееся в искомой​
​ прибегая к формулам​Так, в предыдущем примере​

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

excelworld.ru

Индекс и Поискпоз с несколькими условиями (Формулы/Formulas)

​ этот диапазон.​​ размера по точному​
​ возвращена ошибка #Н/Д.​ =ПОИСКПОЗ(«груши»;{«яблоки»;»ГРУШИ»;»мандарины»};0) вернет значение​со значениями в​jakim​ поиск» — F1​ в массиве или​ результатов сравнения). Если​ ячейке, а ее​ с комбинациями других​ получить не номер,​Что_ищем​ выходе, не применима​ (​=СУММЕСЛИ(A4:A15;D4;B4:B15)​выберите значение​1​ совпадению​2. Произведем поиск позиции​ 2.​ аргументе​, а ПОИСКПОЗ()-то где?​
​Il_sun​ диапазоне ячеек (не​

​ функция ПОИСКПОЗ нашла​​ координату относительно рассматриваемого​ функций ИНДЕКС и​ а название модели​
​- это значение,​

​ для поиска текста,​​Нектарин​​соответствующие значения суммируются​​Надстройки Excel​

​ — это количество столбцов,​​Поиск значений в списке​​ в отсортированном по​​Если искомое значение точно​просматриваемый_массив.​jakim​: в отношении прикрепленного​ обязательно упорядоченном), которое​ значение ИСТИНА, будет​
​ диапазона. В случае​ т.п. Выход из​

​ генератора можно очень​​ которое надо найти​​ не работает в​​) в определенном месяце​
​=БИЗВЛЕЧЬ(A3:B15;2;F15:F16)​и нажмите кнопку​ которое нужно отсчитать​ по горизонтали по​ возрастанию списке числовых​ не известно, то​

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

​ легко:​​Где_ищем​ старых версиях Excel​​ (​

​возвращается ошибка #ЧИСЛО!​​Перейти​ справа от начальной​ точному совпадению​ значений (диапазон​
​ с помощью подстановочных​
​тип_сопоставления​ наоборот.​

​ иметь вид​​ значением, переданным в​
​ первого вхождения в​

​ массивов, которые могут​​ в определении настроек​Ну, и поскольку Excel​- это одномерный​

​ (2003 и ранее).​​Январь​
​Для функции ВПР() требуется,​

​.​​ ячейки, чтобы получить​​Поиск значений в списке​​B31:B37​

​ знаков можно задать​​равен 0, то​=IFERROR(MATCH(1;INDEX((B$3:B$400=H5)*(C$3:C$400=I5)*(D$3:D$400=J5)*(F$3:F$400=K5);0);0);»нет»)​
​200?'200px':''+(this.scrollHeight+5)+'px');">=ИНДЕКС(B2:C3;ПОИСКПОЗ(A8;A2:A3;0);ПОИСКПОЗ(A6;B1:C1;0))​

excelworld.ru

​ качестве первого аргумента.​

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

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

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

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

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

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

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

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

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

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

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS), появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы: Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

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

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

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

   

3. Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы: Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы: Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

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

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

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Четыре способа использования ВПР с несколькими условиями

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

Итак, мы с вами рассмотрим четыре варианта создания подстановочной функции с двумя условиями:

1. Использование дополнительной колонки
2. Использование функции ВЫБОР для создания новой таблицы просмотра
3. Использование функций ИНДЕКС и ПОИСКПОЗ
4. Использование функции СУММПРОИЗВ

Ну а начнем мы с вами с самого простого.

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

В большинстве случаев сложные проблемы становятся проще и более управляемыми, если их разбить на маленькие кусочки. Тоже самое касается при построении формул в Excel.

Рассмотрим классический пример. У нас имеется таблица с продажами по месяцам и городам. И нам необходимо определить значение продаж, соответствующее двум условиям: месяц – Февраль и город – Самара.

Использование функции ВПР в классическом виде нам не поможет, так как она сможет вернуть значение, соответствующее только одному условию. Из положения нам поможет выйти дополнительный столбец, в котором мы объединим значения столбцов Месяц и Город. Для этого в ячейке А2 прописываем формулу =B2&C2 и протягиваем данную формулу до ячейки А13. Теперь мы сможем использовать значения столбца А, чтобы вернуть необходимое значение. Прописываем в ячейке G3 формулу:

Данная формула объединяет два условия ячеек G1 и G2 в одну строку и просматривает его в столбце А. После того, как нужное условие было обнаружено, формула возвращает значение с четвертого столбца таблицы A1:D13, т.е. столбца Продажи.

Использование функции ВЫБОР для создания новой таблицы просмотра

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

Использование функции ВЫБОР подразумевает создание новой таблицы для просмотра, в котором значения столбцов Месяц и Город уже объединены. Наша формула будет выглядеть следующим образом:

Основной момент данной формулы заключается в части ВЫБОР(<1;2>;B2:B13&C2:C13;D2:D13), который делает две вещи:

1. Объединяет значения столбцов Месяц и Город в один массив: ЯнвМосква, ФевМосква …
2. Объединяет два массива в таблицу, состоящую из двух столбцов.

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

Теперь формула стала более понятной.

ВАЖНО: Так как мы использовали формулу массива, по окончании ввода формулы нажмите Ctrl+Shift+Enter, чтобы дать знать программе о наших намерениях. После нажатия данной комбинации клавиш, программа автоматически установит фигурные скобки в начале и в конце формулы.

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

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

Формула будет выглядеть следующим образом.

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

Сначала рассмотрим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В данном случае последовательно сравнивается значение ячейки G1 с каждым значением ячеек диапазона B2:B13 и возвращается ИСТИНА, если значения совпадают и ЛОЖЬ, если нет. Такое же сравнение производится со значением ячейки G2 и диапазоном C2:C13. Далее мы сравниваем оба эти массива, состоящих из ИСТИНА и ЛОЖЬ. Комбинация ИСТИНА * ИСТИНА дает нам результат 1 (ИСТИНА). Давайте посмотрим на картинку ниже, которая поможет объяснить принцип работы более наглядно.

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

Использование СУММПРОИЗВ

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

Принцип работы данной формулы схож с принципом работы предыдущего подхода. Создается виртуальная таблица, в которой сравниваются значения ячеек G1 и G2 с диапазонами B2:B13 и С2:С13 соответственно. Далее оба этих массива сопоставляются и получается массив из единиц и нулей, где единица присваивается той строке, в которой оба условия совпали. Далее данный виртуальный массив перемножается на диапазон D2:D13. Так как в нашем виртуальном массиве будет только одна единица в шестой строке, формула вернёт результат 189.

Данная функция не будет работать, если в диапазоне D2:D13 имеются текстовые значения.

Чтобы понять, как работает данная формула, рекомендую прочитать статью о функции СУММПРОИЗ.

Итак, какой же способ использовать? Хотя все они работают стабильно, я предпочитаю первый способ. В своей ежедневной работе, я предпочитаю работать с файлами, которые просты для понимания и поддаются изменениям. Оба эти требования отвечают условиям первого подхода.

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

Вам также могут быть интересны следующие статьи

10 комментариев

А я пользовался только одной — самой простой.
Автору спасибо за обзор! Очень часто приходится пользоваться в работе этой функцией.

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

Добрый день уважаемый читатель!

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

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

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

Для этой операции подойдёт формула:

В обязательном порядке формулу необходимо вносить не просто так, а как формулу массива, о чём свидетельствуют фигурные скобки. Произвести это возможно с помощью комбинации горячих клавиш Shift+Ctrl+Enter. Как же собственно работает формула? Рассмотрим поподробнее… Используемый «амперсанд» в функции ПОИСКПОЗ соединяет нужные значения «Нагаев А.В.» и «Апельсин» в одно значение «Нагаев А.В.Апельсин» и производит просмотр массива данных таблицы «B2:B6» и «C2:C6», которые предварительно были условно сцеплены формулой, типа «Нагаев А.В.Банан», «Сидоров А.С.Лимон» и т.д.

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

Ну, вот результат и готов!

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

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

Не забудьте подкинуть автору на кофе…

Поиск в excel по двум условиям

Мне надо, чтобы был поиск значения по двум условиям. Пример:

У меня будет таблица, состоящая из Color и Type of wire . Формула, сравнивая эти значения, должна определять Number . Номеров около тысячи, поэтому думаю, как реализовать поиск по этому условию.

Можно сцепить эти колонки и получить уникальное значение и по нему осуществлять поиск. сцепить(COLOR;Type of wire) получаем BKFLY 10.00 QMM уникальное зачение после переместив колонку Number в право от уникального значения осуществляем ВПР(искомое значение то есть BKFLY 10.00 QMM;диапазон поиска;2;0)

Так выглядит формула:
=ВПР(С1;A1:B5;2;0)

Формула массива вводится тремя клавишами: Ctrl+Shift+Enter (формула должна обрамиться фигурными скобками). С помощью функций ЕСЛИ исключаются лишние вычисления.

Если предварительно объединить значения, формула упрощается и не требует «массивного» ввода:

А если сцепку Color&Type переместить левее таблицы (в столбец A, сама таблица сместится на один столбец вправо), можно применить формулу с единственной функцией ВПР:

Функция пользователя (UDF)

Функция размещается в общем модуле. Макросы должны быть разрешены. В ячейку листа:

где F2 и G2 — ячейки выбора прараметров.

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

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

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

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

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

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

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

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

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

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

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

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

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

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

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Функция ВПР с несколькими условиями критериев поиска в Excel

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

Работа функции ВПР по нескольким критериям

Для наглядности разберем формулу ВПР с примером нескольких условий. Для примера будем использовать схематический отчет по выручке торговых представителей за квартал:

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

1. – Дата сдачи выручки в кассу.
2. – Фамилия торгового представителя.

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

1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

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

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

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

Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

1. элемент – Дата.
2. элемент – Фамилия.
3. элемент – Выручка.

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

А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат: Beijing

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

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

Результат: Lima

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

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

Результат: Moscow

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)
ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

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

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

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

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

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

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

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

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

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

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:

=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")

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

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

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

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

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

Пример формулы в G7:

{ = ИНДЕКС (D5:D10; ПОИСКПОЗ (G6; ЕСЛИ( B5:B10 = G5; С5:С10);1))}

Целью данной формулы является найти размер кошки, если известен ее вес.

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

Это делается с помощью простой функции ЕСЛИ:

ЕСЛИ( B5:B10 = G5; С5:С10 )

Она входит в функцию ПОИСКПОЗ как массив. Значение поиска для совпадений происходит от G6, который содержит вес (7 кг в примере).

Обратите внимание, что совпадение настроено для приблизительного совпадения, установив тип_сопоставления = 1, это нужно сортировки С5:С10.

ПОИСКПОЗ возвращает позицию веса в массиве, и передается ИНДЕКСУ как номер строки. Поисковый_массив для ИНДЕКСА размеры в D5:D10, так ИНДЕКС получает размер, соответствующий положению генерируемого совпадением (номер 6 в показанном примере).

Базовый ИНДЕКС ПОИСКПОЗ с приближенным сопоставлением

= ИНДЕКС( класс; ПОИСКПОЗ( балл; баллы; 1))

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

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

Базовый ИНДЕКС ПОИСКПОЗ, точное совпадение

= ИНДЕКС( данные; ПОИСКПОЗ( значение; поиск_столбца ; ЛОЖЬ); столбец )

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

Обратите внимание, что последний аргумент имеет значение 0, что заставляет ПОИСКПОЗ найти точное совпадение.

ИНДЕКС с одним столбцом

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

= ИНДЕКС (С5:C9; ПОИСКПОЗ (H4; B5:B9;0)) // год

= ИНДЕКС (D5:D9; ПОИСКПОЗ (H4; B5:B9;0)) // ранг

= ИНДЕКС (E5:E9; ПОИСКПОЗ (H4; B5:B9;0)) // продажи

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

Чувствительное к регистру совпадение

{ = ПОИСКПОЗ (ИСТИНА; СОВПАД (диапазон; значение); 0)}

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

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

= ПОИСКПОЗ («ИВАН»; B5:B11; 0 )

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

СОВПАД ( B5:B11; E4 )

Которая получает массив истина/ложь значения:

{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}

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

= ПОИСКПОЗ (ИСТИНА; { ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}; 0 )

ПОИСКПОЗ затем получает позицию первого найденного истинного значения: 4.

Точный поиск соответствия с ИНДЕКС и ПОИСКПОЗ

{= ИНДЕКС (данные;ПОИСКПОЗ (ИСТИНА; СОВПАД(текст1; текст2 );0 ); номер_столбца )}

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

В примере, мы используем следующую формулу

={ИНДЕКС (B5:D12; ПОИСКПОЗ (ИСТИНА; СОВПАД (F5;B5:B12);0);3)}

Эта формула будет извлекать текст и числовые значения. Если вы хотите получить только числа, вы можете использовать формулу, основанную на СУММПРОИЗВ.

Время на прочтение
16 мин

Количество просмотров 236K

Добрый день, уважаемые хаброжители!

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

Поиск выдал мне всего одну статью на Хабре по схожей тематике — «Талмуд по формулам в Google SpreadSheet». В ней дано хорошее описание базовых вещей для работы в excel (хотя он и не 100% про сам excel).

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

Речь пойдет о решении наиболее распространенных задач, с которыми сталкиваются пользователи.

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

Кейс_1: Логические функции и функции поиска совпадений
«У меня есть набор значений в табличке и необходимо что бы при выполнении определенного условия/набора условий выводилось определенное значение» (с) Пользователь

Данные, как правило, представлены в табличной форме:

Условие:

• если значение в столбце «Количество» больше 5,
• то нужно вывести в колонке «Результат» значение «Заказ не требуется»,

В этом нам поможет формула «ЕСЛИ» (IF), которая относится к логическим формулам и может выдавать в решении любые значения, которые мы заранее записываем в формуле. Обращаю внимание, что любые текстовые значения записываются, используя кавычки.

Синтаксис формулы следующий:
ЕСЛИ(лог_выражение, [значение_если_истина], [значение_если_ложь])

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

Синтаксис формулы для решения:
Вывод результата в ячейку D2:

=ЕСЛИ(C5>5;«Заказ не требуется»;«Необходим заказ»)

На выходе получаем результат:

Бывает, что условие носит более сложный характер, например выполнение 2-х и более условий:

• если значение в столбце «Количество» больше 5, а значение в колонке «Тип» равно «А»
• то нужно вывести в колонке «Результат» значение «1», в обратном случае «0».

В данном случае мы уже не можем ограничиться использованием одной только формулы «ЕСЛИ», необходимо добавить в ее синтаксис другую формулу. И это будет еще одна логическая формула «И» (AND).
Синтаксис формулы следующий:
И(логическое_значение1, [логическое_значение2], …)

• Логическое_значение1-2 и т.д. — проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ

Синтаксис решения будет следующим:
Вывод результата в ячейку D2:

=ЕСЛИ(И(C2>5;B2=«А»);1;0)

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

Попробуем усложнить задачу – новое условие:

• если значение в столбце «Количество» равно 10, а значение в колонке «Тип» равно «А»
• или же значение в столбце «Количество» больше или равно 5, а значение «Тип» равен «Б»
• то нужно вывести в колонке «Результат» значение «1», в обратном случае «0».

Синтаксис решения будет следующим:
Вывод результата в ячейку D2:

=ЕСЛИ(ИЛИ(И(C2=10;B2=«А»); И(C2>=5;B2=«Б»));1;0)

Как видно из записи, в формулу «ЕСЛИ» включено одно условие «ИЛИ» (OR) и два условия с использованием формулы «И», включенных в него. Если хотя бы одно из условий 2-го уровня имеет значение «ИСТИНА», то в колонку «Результат» будет выведен результат «1», в противном случае будет «0».
Результат:

Теперь перейдем к следующей ситуации:
Представим, что в зависимости от значения в столбце «Условие» должно выводиться определенное условие в столбце «Результат», ниже приведено соответствие значений и результата.
Условие:

• 1 = А
• 2 = Б
• 3 = В
• 4 = Г

При решении задачи с помощью функции «ЕСЛИ», синтаксис будет следующим:
Вывод результата в ячейку B2:

=ЕСЛИ(A2=1;«А»; ЕСЛИ(A2=2;«Б»; ЕСЛИ(A2=3;«В»; ЕСЛИ(A2=4;«Г»;0))))

Результат:

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

Альтернативное решение_1:
Использование формулы «ВЫБОР» (CHOOSE),
Синтаксис функции:
ВЫБОР(номер_индекса, значение1, [значение2], …)

• Номер_индекса — номер выбираемого аргумента-значения. Номер индекса должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254.
• Значение1, значение2,… — значение от 1 до 254 аргументов-значений, из которых функция «ВЫБОР», используя номер индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, определенными именами, формулами, функциями или текстом.

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

• 1 = А
• 2 = Б
• 3 = В
• 4 = Г

Синтаксис формулы:

=ВЫБОР(A2;«А»;«Б»;«В»;«Г»)

Результат аналогичен решению с цепочкой функций «ЕСЛИ» выше.
При применении этой формулы существуют следующие ограничения:
В ячейку «А2» (номер индекса) могут быть указаны только цифры, а значения результата будут выводиться в порядке возрастания от 1 до 254 значений.
Иными словами, функция будет работать только если в ячейке «А2» указаны цифры от 1 до 254 в порядке возрастания и это накладывает определенные ограничения при использовании этой формулы.
Т.е. если мы захотим, что бы значение «Г» выводилось при указании числа 5,

• 1 = А
• 2 = Б
• 3 = В
• 5 = Г

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

=ВЫБОР(A31;«А»;«Б»;«В»;;«Г»)

Как видно, значение «4» в формуле нам приходится оставить пустым и перенести результат «Г» на порядковый номер «5».

Альтернативное решение_2:
Вот мы и подошли к одной из самых популярных функций Excel, овладение которой автоматически превращает любого офисного работника в «опытного пользователя excel» /sarcasm/.
Синтаксис формулы:
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

• Искомое_значение – значение, поиск которого осуществляется функцией.
• Таблица – диапазон ячеек, содержащий данные. Именно в этих ячейках будет происходить поиск. Значения могут быть текстовыми, числовыми или логическими.
• Номер_столбца — номер столбца в аргументе «Таблица», из которого будет выводиться значение в случае совпадения. Важно понимать, что отсчет столбцов происходит не по общей сетке листа (A.B,C,D и т.д.), а внутри массива, указанного в аргументе «Таблица».
• Интервальный_просмотр — определяет, какое совпадение должна найти функция — точное или приблизительное.

Важно: функция «ВПР» ищет совпадение только по первой уникальной записи, если искомое_значение присутствует в аргументе «Таблица» несколько раз и имеет разные значения, то функция «ВПР» найдет только самое ПЕРВОЕ совпадение, результаты по всем остальным совпадениям показаны не будутИспользование формулы «ВПР» (VLOOKUP) связано с еще одним подходом в работе с данными, а именно с формированием «справочников».
Суть подхода в создании «справочника» соответствия аргумента «Искомое_значение» определенному результату, отдельно от основного массива, в котором прописываются условия и соответствующие им значения:

Затем в рабочей части таблицы уже прописывается формула со ссылкой на справочник, заполненный ранее. Т.е. в справочнике в столбце «D» происходит поиск значения из столбца «А» и при нахождении соответствия выводится значение из столбца «Е» в столбец «В».
Синтаксис формулы:
Вывод результата в ячейку B2:

=ВПР(A2;$D$2:$E$5;2;0)

Результат:

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

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

=ВПР($A3;$H$3:$M$6;2;0)

Но при решении сталкиваемся с новой проблемой – при «протягивании» написанной нами формулы вправо от столбца «В» до столбца «Е», нам придется вручную заменять аргумент «номер_столбца». Дело это трудоемкое и неблагодарное, потому, на помощь нам приходит другая функция — «СТОЛБЕЦ» (COLUMN).
Синтаксис функции:
СТОЛБЕЦ([ссылка])

• Ссылка — ячейка или диапазон ячеек, для которых требуется возвратить номер столбца.

Если использовать запись типа:

=СТОЛБЕЦ()

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

=ВПР($A3;$H$3:$M$6; СТОЛБЕЦ();0)

Функция «СТОЛБЕЦ» определит номер текущего столбца, который будет использоваться аргументом «Номер_столбца» для определения номера столбца поиска в справочнике.
Кроме того, можно использовать конструкцию:

=СТОЛБЕЦ()-1

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

Продолжаем развивать тему и усложняем условие: представим, что у нас есть два справочника с разными данными по продуктам и необходимо вывести в таблицу с результатом значения в зависимости от того, какой тип справочника указан в колонке «Справочник»
Условие:

• Если в столбце «Справочник» указано число 1, данные должны тянуться из таблицы «Справочник_1», если число 2, то из таблицы «Справочник_2» в соответствии с указанным месяцем

Вариант решения, который сразу приходит на ум, следующий:
Вывод результата в ячейку C3:

=ЕСЛИ($B3=1; ВПР($A3;$G$3:$I$6; СТОЛБЕЦ()-1;0); ВПР($A3;$K$3:$M$6; СТОЛБЕЦ()-1;0))

Плюсы: наименование справочника может быть любым (текст, цифры и их сочетание), минусы – плохо подходит, если вариантов более 3-х.
Если же номера справочников всегда представляют собой числа, имеет смысл использовать следующее решение:
Вывод результата в ячейку C3:

=ВПР($A3; ВЫБОР($B3;$G$3:$I$6;$K$3:$M$6); СТОЛБЕЦ()-1;0)

Плюсы: формула может включать до 254 наименований справочников, минусы – их наименование должно быть строго числовым.
Результат для формулы с использованием функции «ВЫБОР»:

Бонус: ВПР по двум и более признакам в аргументе «искомое_значение».
Условие:

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

Обе таблицы приведены ниже:

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

=H3&»_»&I3&»_»&J3

Используя символ «&», объединяем три признака в один (разделитель между словами может быть любым, как и не быть вовсе, главное использовать аналогичное правило и для поиска)
Аналогом формулы может быть функция «СЦЕПИТЬ» (CONCATENATE), в этом случае она будет выглядеть следующим образом:

=СЦЕПИТЬ(H3;»_»;I3;»_»;J3)

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

=ЕСЛИОШИБКА(ВПР(A2&»_»&B2&»_»&C2;$G$2:$K$6;5;0);0)

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

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

Кейс_3 Поиск значения в массиве, или когда ВПР не в силах нам помочь

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

• в столбце «Условие поиска» указано значение и необходимо определить, присутствует ли оно в столбце «Массив для поиска»

Визуально все выглядит в следующем виде:

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

По порядку обо всех, «ЕСЛИ» мы уже разобрали ранее, потому перейдем к функции «ЕСЛИОШИБКА» (IFERROR)

ЕСЛИОШИБКА(значение, значение_при_ошибке)

• Значение — аргумент, проверяемый на возникновение ошибок.
• Значение_при_ошибке — значение, возвращаемое при ошибке при вычислении по формуле. Возможны следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!.

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

«СТРОЧН» (LOWER)

СТРОЧН(текст)

• Текст — текст, преобразуемый в нижний регистр.

Важно: функция «СТРОЧН» не заменяет знаки, не являющиеся буквами.
Роль в формуле: поскольку функция «НАЙТИ» (FIND) осуществляет поиск и учетом регистра текста, то необходимо привести весь текст к одному регистру, в противном случае «чАй» будет не равно «чай» и т.д. Это актуально, если значение регистра не является условием поиска и отбора значений, в противном случае формулу «СТРОЧН» можно не использовать, так поиск будет более точным.

Теперь подробнее о синтаксисе функции «НАЙТИ» (FIND).

НАЙТИ(искомый_текст, просматриваемый_текст, [нач_позиция])

• Искомый_текст — текст, который необходимо найти.
• Просматриваемый_текст — текст, в котором нужно найти искомый текст.
• Нач_позиция — знак, с которого нужно начать поиск. Первый знак в тексте «просматриваемый_текст» имеет номер 1. Если номер не указан, он по умолчанию считается равным 1.

Синтаксис формулы-решения будет иметь вид:
Вывод результата в ячейку B2:

=ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ(СТРОЧН(A2); СТРОЧН(E2);1);0)=0;«fail»;«bingo!»)

Разберем логику формулы по действиям:

1. СТРОЧН(A2) – преобразует аргумент «Искомый_текст» в ячейке в А2 в текст с нижним регистром
2. Функция «НАЙТИ» начинает поиск преобразованного аргумента «Искомый_текст» в массиве «Просматриваемый_текст», который преобразовывается функцией «СТРОЧН(E2)», также в текст с нижним регистром.
3. В случае если, функция находит совпадение, т.е. возвращает порядковый номер первого символа совпадающего слова/значения, срабатывает условие ИСТИНА в формуле «ЕСЛИ», т.к. полученное значение не равно нулю. Как результат, в столбце «Результат» будет выведено значение «Bingo!»
4. Если же, функция не находит совпадение т.е. порядковый номер первого символа совпадающего слова/значения не указывается и вместо значения возвращается ошибка, срабатывает условие, заложенное в формулу «ЕСЛИОШИБКА» и возвращается значение равное «0», что соответствует условию ЛОЖЬ в формуле «ЕСЛИ», т.к. полученное значение равно «0». Как результат, в столбце «Результат» будет выведено значение «fail».

Как видно из рисунка выше, благодаря функциям «СТРОЧН» и «НАЙТИ» мы находим искомые значения вне зависимости от регистра символов, и места нахождения в ячейке, но необходимо обратить внимание на строку 5.
Условие поиска задано как «111», но в массиве поиска указано значение «1111111 печенюшки», однако формула выдает результат «Bingo!». Это происходит потому, что значение «111» входит в ряд значений «1111111», как следствие находится совпадение. В обратном случае данное условие не сработает.

Кейс_4 Поиск значения в массиве по нескольким условиям, или когда ВПР тем более не в силах нам помочь

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

Условие:

• В таблицу с результатом необходимо подтянуть данные в соответствии с совпадением условий «Наименование» и «Месяц».

Для решения подобной задачи подойдет комбинация функций «ИНДЕКС» и «ПОИСКПОЗ»

Синтаксис функции «ИНДЕКС» (INDEX)

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

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

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

Синтаксис функции «ПОИСКПОЗ» (MATCH)

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

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

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

часть формулы:

ПОИСКПОЗ($A4;$I$4:$I$7;0)

возвращает число по оси Y, в данном случае оно будет равно 1, т.к. значение «А» присутствует в искомом диапазоне и имеет относительную позицию «1» в этом диапазоне.
часть формулы:

ПОИСКПОЗ(B$3;$J$3:$L$3;0)

возвращает значение #Н/Д, т.к. значение «1» отсутствует в просматриваемом диапазоне.

Таким образом, мы получили координаты точки (1; #Н/Д) которые функция «ИНДЕКС» использует для поиска в аргументе «Массив».
Полностью написанная функция для ячейки B4 будет иметь следующий вид:

=ИНДЕКС($J$4:$L$7; ПОИСКПОЗ($A4;$I$4:$I$7;0); ПОИСКПОЗ(B$3;$J$3:$L$3;0))

По сути, если бы мы знали координаты нужного нам значения, функция выглядела бы следующим образом:

=ИНДЕКС($J$4:$L$7;1;#Н/Д))

Поскольку, аргумент «Номер_столбца» имеет значение «#Н/Д», то результат для ячейки «B4» будет соответствующий.
Как видно из получившегося результата не все значения в таблице с результатом находят совпадение со справочником и в итоге мы видим, что часть значений в таблице выводится в виде «#Н/Д», что затрудняет использование данных для дальнейших расчетов.
Результат:

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

=ЕСЛИОШИБКА(ИНДЕКС($J$4:$L$7; ПОИСКПОЗ($A4;$I$4:$I$7;0); ПОИСКПОЗ(B$3;$J$3:$L$3;0));0)

Демонстрация результата:

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

Кейс_5 Поиск значения в диапазоне чисел

Представим, что нам необходимо дать определенный признак числам, входящим в определенный диапазон.
Условие:
В зависимости от стоимости продукта ему должна присваиваться определенная категория
Если значение находится в диапазоне

• От 0 до 1000 = А
• От 1001 до 1500 = Б
• От 1501 до 2000 = В
• От 2001 до 2500 = Г
• Более 2501 = Д

Функция ПРОСМОТР (LOOKUP) возвращает значение из строки, столбца или массива. Функция имеет две синтаксических формы: векторную и форму массива.

ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])

• Искомое_значение — значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
• Просматриваемый_вектор — диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.
• Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: …, -2, -1, 0, 1, 2, …, A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.
• Вектор_результатов — диапазон, состоящий из одной строки или столбца. Вектор_результатов должен иметь тот же размер, что и просматриваемый_вектор.

Вывод результата в ячейку B3:

=ПРОСМОТР(E3;$A$3:$A$7;$B$3:$B$7)

Аргументы «Просматриваемый_вектор» и «Вектор_результата» можно записать в форме массива – в этом случае не придется выводить их в отдельную таблицу на листе Excel.
В этом случае функция будет выглядеть следующим образом:
Вывод результата в ячейку B3:
=ПРОСМОТР(E3;{0;1001;1501;2001;2501};{«А»;«Б»;«В»;«Г»;«Д»})

Кейс_6 Суммирование чисел по признакам

Для суммирования чисел по определенным признакам можно использовать три разных функции:
СУММЕСЛИ (SUMIF) – суммирует только по одному признаку
СУММЕСЛИМН (SUMIFS) – суммирует по множеству признаков
СУММПРОИЗВ (SUMPRODUCT) – суммирует по множеству признаков
Существует также вариант с использованием «СУММ» (SUM) и функции формулы массивов, когда формула «СУММ» возводится в массив:
({=СУММ(()*())}
но такой подход довольно неудобен и полностью перекрывается по функционалу формулой «СУММПРОИЗВ»
Теперь подробнее по синтаксису «СУММПРОИЗВ»:

СУММПРОИЗВ(массив1, [массив2], [массив3],…)

• Массив1 — первый массив, компоненты которого нужно перемножить, а затем сложить результаты.
• Массив2, массив3… — от 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.

Условие:

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

Как видно из таблицы с данными, что бы посчитать стоимость необходимо цену умножить на количество, а полученное значение, применив условия отбора переносить в таблица с результатом.
Однако, формула «СУММПРОИЗ» позволяет проводить такие расчеты внутри формулы.
Вывод результата в ячейку B4:

=СУММПРОИЗВ(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11<C$3);($M$3:$M$11)*($L$3:$L$11))

Разберем формулу по частям:

($A4=$H$3:$H$11)

– задаем условие по отбору в столбце «Наименование» таблицы с данными по столбцу «Наименование» в таблице с результатом

($K$3:$K$11>=B$3)*($K$3:$K$11<C$3)

– задаем условие по временным рамкам, дата больше или равна первого числа текущего месяца, но меньше первого числа месяца следующего. Аналогично – условие в таблице с результатом, массив – в таблице с данными.

($M$3:$M$11)*($L$3:$L$11)

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

Теперь усложним условие и добавим требование, что бы отбор по наименованию «печеньки» происходил только по классам «малые» и «большие», а по наименованию «булки» все, кроме по классу «с джемом»:

Вывод результата в ячейку B4:

=СУММПРОИЗВ(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11<C$3)*(($I$3:$I$11=«малые»)+($I$3:$I$11=«большие»));($L$3:$L$11*$K$3:$K$11))

В формуле для отбора по печенькам добавилось новое условие:

(($I$3:$I$11=«малые»)+($I$3:$I$11=«большие»))

– как видно, два или более условия по одному столбцу выделяются в отдельную группу при помощи символа «+» и заключения условий в дополнительные скобки.
В формуле для отбора по булкам также добавилось новое условие:

=СУММПРОИЗВ(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11<C$3)*($I$3:$I$11<>«с джемом»);($L$3:$L$11)*($K$3:$K$11))

это:

($I$3:$I$11<>«с джемом»)

– на самом деле, в данной формуле можно было написать условие отбора также как и при отборе по печенькам, но тогда, пришлось бы перечислять три условия в формуле, в данном случае, проще написать исключение – не равно «с джемом» для этого используем значение «<>».
Вообще, если группы признаков/классов заранее известны, то лучше объединять их в эти группы, создавая справочники, чем записывать все условия в функцию, раздувая ее.
Результат:

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

Спасибо за уделенное время!