Поиск позиции функция в excel

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки

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

Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

Функция ВПР в Excel – это действительно мощный инструмент для выполнения поиска определённого значения в базе данных. Однако, есть существенное ограничение – её синтаксис позволяет искать только одно значение. Как же быть, если требуется выполнить поиск по нескольким условиям? Решение Вы найдёте далее.

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

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

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

– эта формула вернет результат 15, соответствующий товару Apples, так как это первое совпадающее значение.

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

Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2. Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&” “&C2. После этого можно использовать следующую формулу:

или

Где ячейка B1 содержит объединенное значение аргумента lookup_value (искомое_значение), а 4 – аргумент col_index_num (номер_столбца), т.е. номер столбца, содержащего данные, которые необходимо извлечь.

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

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

Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.

Итак, формула с ВПР может быть такой:

Здесь в столбцах B и C содержатся имена клиентов и названия продуктов соответственно, а ссылка Orders!$A&$2:$D$2 определяет таблицу для поиска на другом листе.

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

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&” “&C2).

Запомните! Функция ВПР ограничена 255 символами, она не может искать значение, состоящее из более чем 255 символов. Имейте это ввиду и следите, чтобы длина искомого значения не превышала этот лимит.

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

Как в Excel применить функцию «Подбор параметра»

Чтобы применить средство Подбор параметра, выполните команду Данные → Работа с данными → Подбор параметра. Откроется одноименное диалоговое окно, в котором надо заполнить все поля ввода, а затем щелкнуть на кнопке ОК. В результате появится диалоговое окно Результат подбора параметра.

Диалоговое окно Подбор параметра очень просто в использовании — в нем надо заполнить всего три поля ввода: Установить в ячейке, Значение и Изменяя значение ячейки, которые показаны на рис. 1.4.

Рис. 1.4. Диалоговое окно Подбор параметра

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

1. В поле ввода Установить в ячейке введите адрес или просто, когда курсор будет находиться в этом поле, щелкните на ячейке, содержащей формулу, для результата вычисления которой вы хотите задать значение.
2. В поле ввода Значение введите число, которое вы хотите увидеть в ячейке, указанной в поле Установить в ячейке.
3. В поле ввода Изменяя значение ячейки введите адрес или просто щелкните на ячейке, содержащей числовое значение, которое вы хотите определить. Формула в ячейке, указанная в поле Установить в ячейке, обязательно должна прямо или опосредованно (через другие формулы) ссылаться на ячейку, которую вы указали в поле Изменяя значение ячейки.

Заполнив все три поля ввода диалогового окна Подбор параметра, для начала работы данного средства щелкните в этом окне на кнопке ОК. После этого появится диалоговое окно Результат подбора параметра, которое сообщит, что решение найдено

Обратите внимание на два числа, отображаемые в этом окне как Подбираемое значение и Текущее значение

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

Для примера рассмотрим рабочий лист, показанный на рис. 1.5, где в ячейке А1 содержится значение градусов по Фаренгейту, а в ячейке А2 записана формула =ПРЕОБР(А1;»Р»;»С»), преобразующая значение градусов по Фаренгейту в значение градусов по Цельсию. Введите значение 100 в ячейку А1 и вы получите значение 37,8 в ячейке А2. Допустим, теперь вы хотите узнать, сколько градусов по Фаренгейту составляют 20 градусов по Цельсию.

Рис. 1.5. Преобразование значения температуры по Фаренгейту в значение температуры по Цельсию

Чтобы удовлетворить свое любопытство, вы должны выполнить такие действия.

1. Выберите команду Данные → Работа с данными → Подбор параметра. Откроется диалоговое окно Подбор параметра.
2. В поле ввода Установить в ячейке введите А2 или щелкните на ячейке А2.
3. В поле ввода Значение введите число 20.
4. В поле ввода Изменяя значение ячейки введите А1 или щелкните на ячейке А1.
5. Щелкните на кнопке ОК.

После этих действий откроется диалоговое окно Результат подбора параметра, где оба значения, Подбираемое значение и Текущее значение, будут равняться числу 20. Таким образом, Excel найдет искомое решение, которое будет отображаться в ячейке А1 как число 68.

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

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

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

Более подробно о функциях ВПР и ПРОСМОТР.

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

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

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

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

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

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

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

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

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в 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.

Индекс поискпоз в excel поиск по двум столбцам

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

Есть длина, ширина, толщина. Нужно найти значение соответствующее этим трем критериям. Есть столбец “Длина” значения 500, 1000 и др. В столбце “Ширина” при длине 500, есть значения 100, 150, 200. Так же при следующей длине – 1000. Есть так же столбцы с шириной и значениями 30, 50, 70, 100. Всем этим критериям соответствует определенное значение со временем. Которое и нужно найти.

Возможно ли это при заданной таблице в таком виде или лучше разбить таблицу на несколько различных?

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

Есть длина, ширина, толщина. Нужно найти значение соответствующее этим трем критериям. Есть столбец “Длина” значения 500, 1000 и др. В столбце “Ширина” при длине 500, есть значения 100, 150, 200. Так же при следующей длине – 1000. Есть так же столбцы с шириной и значениями 30, 50, 70, 100. Всем этим критериям соответствует определенное значение со временем. Которое и нужно найти.

Возможно ли это при заданной таблице в таком виде или лучше разбить таблицу на несколько различных? Anatoly88

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

Есть длина, ширина, толщина. Нужно найти значение соответствующее этим трем критериям. Есть столбец “Длина” значения 500, 1000 и др. В столбце “Ширина” при длине 500, есть значения 100, 150, 200. Так же при следующей длине – 1000. Есть так же столбцы с шириной и значениями 30, 50, 70, 100. Всем этим критериям соответствует определенное значение со временем. Которое и нужно найти.

Возможно ли это при заданной таблице в таком виде или лучше разбить таблицу на несколько различных? Автор – Anatoly88Дата добавления – 11.06.2016 в 15:57

ИНДЕКС ПОИСКПОЗ и ЕСНД/ЕСЛИОШИБКА

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

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

С этой формулой, если ввести данные, которых нет в таблице, форма вам выдаст указанное сообщение.

Если же хотите отловить все ошибки, тогда кроме ЕСНД можно использовать ЕСЛИОШИБКА

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

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

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

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

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

Синтаксис

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

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

• ​ умножается на другую.​​ совершенно разных функций.​ я уже подробно​Это самый очевидный и​​ массиве. Если все​​ с пунктом​.​ ПОИСКПОЗ.​ в сочетании с​ массива констант. Если​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>

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

Функция имеет следующую синтаксическую запись:

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

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

1. -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
2. 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
3. 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.

1. Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
2. Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
3. Если аргумент явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» – замена одного любого символа, «*» – замена любого количества символов).
4. Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.

Выборка данных в Excel (как сделать из таблицы массива по условию)

Первый способ: Применение расширенного автофильтра

На листе Excel необходимо выделить область, среди данных, которых и нужно осуществить выборку. Во вкладке «Главная» нажимаете «Сортировка и фильтр» (находится в блоке настроек «Редактирование»). Далее нажимаете на фильтр.

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

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

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

После фильтрации остаются только те строчки, в которых сумма выручки превышает значение 10000 (как пример).

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

В таблице останутся только те строки, в которых сумма выручки не меньше 10000, но и не больше 15000.

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

Должно запуститься окно пользовательского автофильтра. Выполните, к примеру, отбор результатов в таблице с 4 по 6 мая 2016 года включительно. Нажимаете «После или равно», а в поле справа выставляете значение «04.05.2016». В нижнем блоке переключатель ставите в позицию «До или равно», а в правом поле вносите «06.05.2016». Переключатель совместимости условий оставляете в положении по умолчанию, то есть «И». Для применения фильтрации кликаете на ОК.

Список теперь должен сократиться еще больше, потому что останутся только строки, в которых сумма выручки варьируется от 10000 до 15000 и это за период с 04.05 по 06.05.2016 включительно.

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

Выборка по сумме выручки отключится и останется только отбор по датам (с 04.05.2016 по 06.05.2016).

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

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

В новой выборке выставлены ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (Мясо и Картофель). Ограничений нет только по сумме выручки.

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

Во втором варианте можно перейти во вкладку «Главная» и нажать там на «Сортировка и фильтр» в «Редактирование». Далее кликаете на «Фильтр».

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

Второй способ: Применение формулы массива

На том же листе Excel создаете пустую таблицу с теми же наименованиями столбцов в шапке, которые имеются у исходника.

Все пустые ячейки необходимо выделить в первой колонке новой таблицы. В строку формул устанавливаете курсор, чтобы занести формулу – =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000

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

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

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

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