Примеры функции индекс и поискпоз с несколькими условиями excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ВПР пригодится, чтобы объединять данные из разных таблиц.

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

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

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

Таким вот образом мы обнаружили в перечне товаров персики, записав в условии поиска «*»&»персики»&»*» или же «*»&D2&»*».

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

Функция имеет два варианта синтаксической записи:

= ИНДЕКС(массив; номер_строки; )

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

Описание аргументов:

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

  1. Одну строку либо столбец. В этом случае один из последующих аргументов функции является необязательным для заполнения (строка или столбец соответственно).
  2. Несколько строк и столбцов. Функция ИНДЕКС вернет диапазон ячеек, являющихся строкой или столбцом массива (диапазона), переданного в качестве первого аргумента, если указан только номер его строки либо номер столбца соответственно.
  • ссылка – обязательный для заполнения первый аргумент функции ссылочной формы, принимающий ссылку на один или несколько диапазонов ячеек. Ссылки на два и более несмежных диапазона должны быть заключены в скобки (например, функция с аргументами ((A1:B4;D4:E9);2;2;1), принимающая ссылку на несмежные диапазоны A1:B4 и D4:E9). Один из последующих аргументов функции необязателен для заполнения, если в качестве данного аргумента передана ссылка на области, состоящие из одной строки или одного столбца.
  • номер_строки – обязательный для заполнения аргумент (если следующий явно не указан), принимающий числовые значения, которые характеризуют номер строки в массиве или диапазоне, заданном аргументом массив, либо номер строки в диапазоне ячеек, заданном аргументом ссылка.
  • – необязательный аргумент, принимающий число, которое соответствует номеру искомого столбца в массиве или диапазоне, который задан аргументом массив, либо номер строки в диапазоне ячеек, переданном в качестве аргумента ссылка.
  • – необязательный аргумент функции ИНДЕКС ссылочной формы, принимающий числовое значение, которое характеризует порядковый номер диапазона из переданных в качестве аргумента ссылка, в котором будет произведен поиск на основе заданных номерах столбца и строки. Нумерация диапазонов начинается с единицы (1). Для корректной работы функции ИНДЕКС все несмежные диапазоны, переданные в качестве аргумента ссылка, должны находиться на одном листе.

Примечания:

  1. Аргументы номер_строки и должны указывать на ячейку (ячейки) внутри массива или диапазона, переданных в качестве аргументов массив или ссылка, иначе функция вернет код ошибки #ССЫЛКА!.
  2. Если в качестве аргументов номер_строки и (или) указаны значения 0 (нуль), результатом выполнения функции ИНДЕКС будет массив значений из строки или столбца либо всего массива (диапазона) переданного в качестве аргумента массив. Это справедливо и для функции ИНДЕКС ссылочной формы, только вместо массива значений будет возвращена ссылка не весь столбец, всю строку или весь диапазон (если оба аргумента принимают значение 0).
  3. Если требуется, чтобы функция формы массива вернула значения нескольких ячеек, а не одной ячейки, находящейся на пересечении указанных номеров строки и столбца, она должна быть выполнена в качестве формулы массива (сочетание клавиш Ctrl+Shift+Enter).

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

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

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 — тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

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

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

  1. Первый аргумент — это диапазон B2:E13, в котором мы осуществляем поиск.

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

  3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

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

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

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

Два ВПР быстрее, чем один.

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

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

То есть, мы ищем точное совпадение. Если номер заказа не будет найден, то получим ошибку #Н/Д.

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

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

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

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

Окончательное выражение выглядит так:

То есть, мы сначала ищем и извлекаем номер заказа. И если результат этого поиска точно совпадает с критерием, повторяем еще раз, но теперь уже извлекаем именно сумму заказа.

Примечание: ваши данные должны быть отсортированы, чтобы использовать этот способ.

Именованные диапазоны облегчают понимание расчетов и еще больше упрощают работу.

Абсолютные ссылки выглядят довольно некрасиво и не очень понятно и наглядно. Поэтому можно сделать ваши расчёты намного чище и проще для понимания, заменив абсолютные ссылки именованными диапазонами. И никакие возможные изменения на вашем листе Excel не смогут их «испортить».

Копировать и переносить их также можно без проблем.

В приведенном выше примере с данными о сотрудниках вы можете назвать входную ячейку B2 «фамилия», а затем выделить все ячейки с информацией и назвать диапазон B5:F100 как «ДанныеСлужащего». Затем перепишите свою формулу в C2 следующим образом:

Сравните сами — насколько понятнее стал расчет из совета №12 по сравнению с №11.

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

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

Синтаксис этой функции следующий:

=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )

  • Что_ищем — это значение, которое надо найти
  • Где_ищем — это одномерный диапазон или массив (строка или столбец), где производится поиск
  • Режим_поиска — как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

Давайте рассмотрим несколько полезных вариантов ее применения на практике.

Точный поиск

Классический сценарий — поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

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

Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:

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

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

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

Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

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

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

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

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

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

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

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

Оператор ПОИСКПОЗ возвращает положение указанного значения в выделенном диапазоне ячеек.

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

.

Давайте “пробежимся” по аргементам функции:

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

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

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

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

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

В аргумент “Тип_сопоставления” указываем цифру .

Обращаем внимание на строку формул. Здесь мы левой кнопкой мыши щелкаем по слову “ИНДЕКС”.

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

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

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

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

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

Синтаксис

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

​ПОИСКПОЗ​ или менее следующий​

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

  • ​ описывал (с видео).​​ простой (хотя и​ сделано правильно в​Мастер подстановок​

  • ​К началу страницы​​Дополнительные сведения см. в​ функцией​ в ячейке D5​​(ВЫБОР), чтобы перевести​​ поиска значения «Воронеж»​ совпадения может иметь​​Продукт​​Просматриваемый_массив​ элемента в диапазоне​​1*НЕ(ЕОШИБКА(ПОИСКПОЗ(F5;A2:A9;0)))​​ или менее так,​ таблица, и мы​ В нашем же​​ не самый удобный)​ строке формул появятся​и нажмите кнопку​Для выполнения этой задачи​ разделе, посвященном функции​​INDEX​​ ввести более поздний​

​ успеваемость учащихся в​

​ в диапазоне B1:B11​

​ серьезные последствия, предположим,​

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

​ месяц, например,​

​ буквенную систему оценок.​​ будет использоваться функция​​ что ищется цена​Бананы​​ по возрастанию: …,​​ относительную позицию этого​​ Для каждого столбца​​ рисунке ниже (пример​ формула Excel отвечала​

​ их для поиска​

​ функция​​Как видно функция самостоятельно​​.​ См. пример ниже.​Что означает:​​ рассмотрим более пристально​​Oct​​Использовать вместе с​​ ПОИСКПОЗ. Оно найдено​ детали с идентификатором​25​ -2, -1, 0,​ элемента в диапазоне.​ с помощью функции​

  • ​ для искомого выражения​​ на вопрос, в​​ по нескольким столбцам​ВПР (VLOOKUP)​ справилась с решением​​Следуйте инструкциям мастера.​​Функция ГПР выполняет поиск​​=ИНДЕКС(нужно вернуть значение из​​ чуть позже в​(октябрь), то результатом​VLOOKUP​

  • ​ в строке 4.​​ 2345768, но вы​​Апельсины​ 1, 2, …,​

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

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

Пример

​#N/A​ выбора столбца.​ использует это значение​ и ввели их​Яблоки​0​ 5, 25 и​ в столбце искомое​ массив с нулями​ нашем примере я​Выделите пустую зеленую ячейку,​ а не по​Чтобы функция ПОИСКПОЗ работала​

​ очень часто приходится​

​и возвращает значение​

​ «Капуста» в массиве​

​ функция​

​(#Н/Д).​

​Использовать вместе с​

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

​ в формулу следующим​

​40​

​Функция​

​ 38, то формула​

​ выражение. Если нет,​

​ везде, где значения​

​ использую столбец), находится​

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

​ B2:B10))​

​MATCH​

​=MATCH(D5,{«Jan»,»Feb»,»Mar»},0)​INDEX​

​ поиска и находит​

​ образом:​

​Груши​ПОИСКПОЗ​=ПОИСКПОЗ(25;A1:A3;0)​ то функция возвращает​

​ в нашей таблице​

support.office.com>

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

Пример таблицы табель премии изображен ниже на рисунке:

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

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

Для этого:

  1. В ячейку B14 введите размер выручки: 370 000.
  2. В ячейке B15 укажите номер магазина: 3.
  3. В ячейке B16 введите следующую формулу:

В результате определена нижняя граница премии для магазина №3 при выручке больше >370 000, но меньше


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

В первом аргументе функции ВПР указываем ссылку на ячейку с критерием поискового запроса (исходная сумма выручки), который содержится в ячейке B14. Область поиска в просматриваемом диапазоне A5:K11 указывается во втором аргументе функции ВПР. А в третьем аргументе должен быть указан номер столбца, но он пока неизвестен. Из второго критерия поискового запроса известно только что исходный номер столбца таблицы относится к 3-тьему магазину (ячейка B15).

Чтобы определить номер столбца, который содержит заголовок «Магазин 3» следует использовать функцию ПОИСКПОЗ. Как само название функции говорит о том, что ее задачей является поиск позиции где находится значение внутри определенного диапазона ячеек. В нашем случаи мы ищем значение: «Магазин 3», которое следует еще определить используя конструкцию сложения амперсандом текстовой строки «Магазин » и критерий из ячейки B15. Поэтому в первому аргументе функции указываем «Магазин »&B15. Во втором аргументе функции ПОИСКПОЗ указывается ссылка на просматриваемый диапазон A3:J3 где нужно искать исходное значение (указанное в первом аргументе). Третий аргумент содержит значение 0 – это значит, что функция возвратит результат, как только найдет первое совпадение значений. В нашем примере значение «Магазин 3» находится на позиции номер 6 в диапазоне A3:J3, а значит функция ПОИСКПОЗ возвращает число 6 которое будет использовано в качестве значения для третьего критерия функции ВПР. Есть еще и четвертый аргумент в функции ВПР который определяет точность совпадения найденного значения с критерием (0-точное совпадение; 1 или пусто – приближенное совпадение), но в формуле он опущен по следующей причине. Получив все аргументы функция ВПР не находит значения 370 000 и так как не указан последний аргумент выполняет поиск ближайшего значения в Excel – 350 000.

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

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

Полезные советы для формул с функциями ВПР, ИНДЕКС и ПОИСКПОЗ:

Чтобы пошагово проанализировать формулу Excel любой сложности, рационально воспользоваться встроенными инструментами в разделе: «ФОРМУЛЫ»-«Зависимости формул». Например, особенно полезный инструмент для пошагового анализа вычислительного цикла – это «Вычислить формулу».

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

Ссылка на основную публикацию