Как найти ближайшее значение их диапазонами

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

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

• Расчет скидки в зависимости от объема.
• Вычисление размера бонусов в зависимости от выполнения плана.
• Калькуляция тарифов на доставку в зависимости от расстояния.
• Подбор подходящей тары для товара и т.д.

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

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

Для начала, представим себе поставщика, который дает скидки на опт, причем процент скидки зависит от количества купленного товара. Например, при покупке свыше 5 штук дается скидка 2%, а при покупке от 20 штук – уже 6% и и т.д.

Как же быстро и красиво вычислить процент скидки при вводе количества купленного товара?

Способ 1. Вложенные ЕСЛИ

Способ из серии “а что тут думать – прыгать надо!”. Используем вложенные функции ЕСЛИ (IF) для последовательной проверки попадания значения ячейки в каждый из интервалов и вывода скидки для соответствующего диапазона. Но формула при этом может получиться весьма громоздкой: 

 

Думаю, очевидно, что отлаживать такую “матрёшку-монстра” или пытаться спустя какое-то время добавить в неё парочку новых условий – это весело.

Кроме того, в Microsoft Excel есть ограничение на вложенность для функции ЕСЛИ – 7 раз в старых и – 64 раза в новых версиях. А если нужно больше?

Способ 2. ВПР с интервальным просмотром

Этот способ гораздо компактнее. Для расчета процента скидки используем легендарную функцию ВПР (VLOOKUP) в режиме приблизительного поиска:

где

• B4 – значение количества товара в первой сделке, для которого мы ищем скидку
• $G$4:$H$8 – ссылка на таблицу скидок – без “шапки” и с закрепленными значком $ адресами.
• 2 – порядковый номер столбца в таблице скидок, из которого мы хотим получить значение скидки
• ИСТИНА – здесь и зарыта “собака”. Если в качестве последнего аргумента функции ВПР указать ЛОЖЬ (FALSE) или 0, то функция будет искать строгое совпадение в столбце количества (и в нашем случае выдаст ошибку #Н/Д, поскольку значения 49 в таблице скидок нет). А вот если вместо ЛОЖЬ написать ИСТИНА (TRUE) или 1, то функция будет искать не точное, а ближайшее наименьшее значение и выдаст нужный нам процент скидки.

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

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

Способ 3. Поиск ближайшего наибольшего функциями ИНДЕКС и ПОИСКПОЗ

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

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

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

Способ 4. Новая функция ПРОСМОТРХ (XLOOKUP)

Если у вас версия Office 365 со всеми установленными обновлениями, то вместо ВПР (VLOOKUP) можно использовать её аналог – функцию ПРОСМОТРХ (XLOOKUP), которую я уже подробно разбирал:

Здесь:

• B4 – исходное значение количества товара, для которого мы ищем скидку
• $G$4:$G$8 – диапазон, где мы ищем совпадения
• $H$4:$H$8 – диапазон результатов, откуда нужно вернуть скидку
• Четвёртый аргумент (-1) включает нужный нам поиск ближайшего наименьшего числа вместо точного совпадения.

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

Но, к сожалению, эта функция пока далеко не у всех – только у счастливых обладателей Office 365.

Способ 5. Power Query

Если вы ещё не знакомы с мощной и при этом совершенно бесплатной надстройкой Power Query для Excel, то вам сюда. Если уже знакомы, то давайте попробуем использовать её для решения нашей задачи.

Сначала выполним подготовительные операции:

1. Преобразуем наши исходные таблицы в динамические (умные) с помощью сочетания клавиш Ctrl+T или командой Главная – Форматировать как таблицу (Home – Format as Table).
2. Для наглядности дадим им имена Продажи и Скидки на вкладке Конструктор (Design).
3. По очереди загрузим каждую из таблиц в Power Query используя кнопку Из таблицы/диапазона на вкладке Данные (Data – From table/range). В последних версиях Excel эту кнопку переименовали в С листа (From sheet).
4. Если у таблиц различаются названия столбцов с количеством как в нашем примере (“Количество товара” и “Количество от…”), то их в Power Query необходимо переименовать и назвать одинаково.
5. После этого можно вернуться обратно в Excel, выбрав в окне редактора Power Query команду Главная – Закрыть и загрузить – Закрыть и загрузить в… (Home – Close&Load – Close&Load to…) и затем вариант Только создать подключение (Only create connection).

   

6. Дальше начинается самое интересное. Если у вас есть опыт работы в Power Query, то, предполагаю, дальнейший ход мыслей должен быть в сторону слияния этих двух таблиц запросом объединения (merge) а-ля ВПР, как это было в предыдущем способе. На самом деле, нам потребуется слияние в режиме добавления, что на первый взгляд совсем не очевидно. Выбираем в Excel на вкладке Данные – Получить данные – Объединить запросы – Добавить (Data – Get Data – Combine queries – Append) и затем наши таблицы Продажи и Скидки в появившемся окне:

   

7. После нажатия на ОК наши таблицы будут склеены в единое целое – друг под друга. Обратите внимание, что столбцы с количеством товара в этих таблицах встали друг под друга, т.к. у них одинаковые названия:

   

8. Если вам важна исходная последовательность строк в таблице продаж, то, чтобы после всех последующих преобразований потом можно было её восстановить, добавим к нашей таблице столбец с нумерацией, используя команду Добавление столбца – Столбец индекса (Add column – Index column). Если последовательность строк для вас роли не играет, то этот шаг можно пропустить.
9. Теперь с помощью выпадающего списка в шапке таблицы отсортируем её по столбцу Количество по возрастанию:

   

10. И главный трюк: щёлкаем правой кнопкой мыши по заголовку столбца Скидка выбираем команду Заполнить – Вниз (Fill – Down). Пустые ячейки с null автоматически заполнятся предыдущими значениями скидок:

    
11. Осталось восстановить исходную последовательность строк сортировкой по столбцу Индекс (его потом можно смело удалить) и избавиться от ненужных строк фильтром null по столбцу Код сделки:

    

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

• Использование функции ВПР (VLOOKUP) для поиска и подстановки данных
• Использование функции ВПР (VLOOKUP) с учетом регистра
• Двумерный ВПР (VLOOKUP)

Каждый пользователь Excel без проблем может найти наименьшее или наибольшее значение в диапазоне чисел, используя для этого функции: =МИН(), =МАКС() или =НАИМЕНЬШИЙ() и =НАИБОЛЬШИЙ(). Так же легко найти номер позиции исходного значения в диапазоне ячеек с помощью функции =ПОИСКПОЗ(). Но в данном примере будет более интересное решение, которое позволяет выполнить поиск ближайшего значения в Excel. Создадим формулу, которая способна находить наиболее приближенное значение к соответствию запроса пользователя. Например, несли диапазон данных не содержит значений для точного совпадения с запросом пользователя то функция ПОИСКПОЗ возвращает ошибку #Н/Д. Но пользователя вполне устроил бы и приближенный результат, не зависимо от того будет ли он немного меньше или немного больше соответствовать запросу. Важным преимуществом такой формулы заключается в том, что нет необходимости использовать условную сортировку для решения такого рода задач.

Как найти ближайшее значение в Excel?

Возьмем для примера, конкретную ситуацию. Фирма переводит склад на новое место, и чтобы полностью заполнить фуру товарами с одинаковым объемом упаковок (например, офисная бумага для принтера формат A4 по 500 листов) нужно вложить еще 220 пачке. Но желательно не перемешивать ассортимент продукции. То есть нужно постараться избежать пересорта товаров при переезде на новый склад. Заполним оставшееся пустое место в фуре пачками офисной бумаги одного и того же типа стандарта качества. У нас есть остатки по товарам всех ассортиментов:

Нам нужно выполнить поиск ближайшего меньшего значения Excel. Чтобы найти ассортимент с наиболее подходящим количеством по остаткам (не более 220 шт.) создаем формулу:

1. В ячейке E2 введите значение 220 – это количество пачек офисной бумаги, которое соответствует для заполнения свободного объема в фуре.
2. В ячейке E3 вводим формулу:
   Подобную формулу можно использовать для поиска ближайшего значения в диапазоне Excel, а не только в одном столбце.
3. Для подтверждения ввода формулы нажимаем комбинацию клавиш CTRL+SHIFT+Enter, так как формула должна выполняться в массиве. Если вы сделали все правильно, то в строке формул вы должны заметить фигурные скобки.

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

В результате поедет комплект одного ассортимента бумаги тип-9 (195шт.). Так как его количество на остатках наиболее приближенно соответствует к объему в 220 пачек. Фура будет максимально возможно заполнена, а на складе будет меньше пересорта по ассортиментам товаров.



Принцип поиска ближайшего значения по формуле:

Из каждого числа остатков в диапазоне ячеек B3:B12 вычитается исходное значение в ячейке E2. Таким образом создается условная таблица значений равных этой разницы, с размером соответствующему числу ячеек в диапазоне B3:B12. Функция ABS возвращает абсолютную величину числа по модулю и в этой же условной таблице заменяет все значения отрицательных чисел на положительные (без знака минус). Из полученных данных находим наименьшее значение с помощью функции =МИН(). А функция =ПОИСКПОЗ() возвращает нам позицию в условной таблице для наименьшего значения найденного функций МИН. Полученный результат вычисления функцией ПОИСКПОЗ является аргументом для функции =ИНДЕКС(), которая возвращает нам значение ячейки находящиеся под номером позиции в диапазоне B3:B12.

Другие возможности Excel для поиска значений

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

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

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

Скачать пример поиска ближайшего значения в Excel

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

Для поиска ЧИСЛА ближайшего к заданному, в EXCEL существует специальные функции, например,

ВПР()

,

ПРОСМОТР()

,

ПОИСКПОЗ()

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

формулы массива

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

Решение задачи поиска ближайшего числового значения в случае

сортированного

списка приведена в статье

Поиск ЧИСЛА ближайшего к заданному. Сортированный список

.

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

A4:A15

). (см.

Файл примера

).

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

С4

. Найдем значение из диапазона, ближайшее к критерию с помощью

формул массива

:

Решение	Как происходит поиск	Примечание
= ИНДЕКС(A4:A15;ПОИСКПОЗ( МАКС(ЕСЛИ(A4:A15<=C4;A4:A15;””)); $A$4:$A$15;0);1)	ищется наибольшее значение, которое меньше, чем искомое значение (ближайшее сверху)	Если заданное значение меньше минимального, то выдается ошибка #Н/Д
= ИНДЕКС(A4:A15;ПОИСКПОЗ( МИН(ЕСЛИ(A4:A15>=C4;A4:A15;””)); $A$4:$A$15;0);1)	ищется наименьшее значение, которое больше, чем искомое значение (ближайшее снизу)	Если заданное значение больше максимального, то выдается ошибка #Н/Д
= ИНДЕКС(A4:A15;ПОИСКПОЗ( МИН(ABS(A4:A15-C4)); ABS(A4:A15-C4);0))	ищется ближайшее к критерию число	если ближайшее снизу и ближайшее сверху отстоят на одинаковое расстояние от критерия, то берется ближайшее число, расположенное первым в списке (например, ближайшее к 5 в списке 2; 4 ; 6 ; 8 будет 4, а в списке 2; 6 ; 4 ; 8 – будет 6), т.е. предсказать будет ли число ближайшим сверху или снизу невозможно
= МАКС(ЕСЛИ(МИН(ABS(A4:A15-C4))=ABS(A4:A15-C4);A4:A15;МИН(A4:A15)))	ищется ближайшее к критерию число	если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое больше
= МИН(ЕСЛИ(МИН(ABS(A4:A15-C4))=ABS(A4:A15-C4);A4:A15;МАКС(A4:A15)))	ищется ближайшее к критерию число	если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое меньше

СОВЕТ:

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

F9

.

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

Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список

.

• 

= FILTER( A2:B15 ,ABS( D2 - B2:B15 ) = min(ABS( D2 - B2:B15 )))

=QUERY( A2:B15 , " select A, B where B >= " & D2 & " order by B limit 1 " , 0 )

= FILTER( A2:B15 ,ABS( D2 - B2:B15 ) = min(ABS( D2 - B2:B15 )))

=QUERY( A2:B15 , " select A, B where B >= " & D2 & " order by B limit 1 " , 0 )

Поиск ближайшего значения в массиве и всех дубликатов в Excel

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

Как найти ближайшее большее значение по формуле в Excel

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

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

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

По одному запросу нужно получить ближайшее значение числа баллов и соответствующее ему имя.

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

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

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

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

Для имени соответствующему ближайшему значению:

Где столбец «D» — наш вспомогательный столбец, а столбец «B» — столбец с именами. Сразу же добавлю (для ясности), что столбец «C» является столбцом со значениями баллов.

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

Решение «хардкор» с использованием формул массива (для любителей и тех, кто просто хочет потренироваться в создании формул массива).

Поиск ближайшего значения в массиве (CTRL+SHIFT+ENTER):

Поиск соответствующего ему имени в массиве (CTRL+SHIFT+ENTER):

Повторяющиеся ближайшие значения в Excel

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

Итак, каким же образом можно заставить Excel вернуть список всех имен с интересующими нас значениями баллов при наличии дубликатов ближайших значений?

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

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

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

Формула должна возвращать номер строки, в которой находится значение, наиболее близкое к искомому. В противном случае возвращает пробел.

Без использования формул массива

Вспомогательный столбец уже готов, мы можем вернуться к нашему поиску.

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

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

=»&» «))))’ >

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

Поиск дублирующийся ближайших значений в массиве Excel

В завершении то же самое, но с использованием формул массива (мы используем вспомогательный столбец, описанный ранее).

Выбираем диапазон ячеек, в которых мы хотим иметь список имен (например, G15:G19) и используем формулу массива:

0″)))))’ >

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

Источник

Поиск ЧИСЛА ближайшего к заданному. Сортированный список в EXCEL

history 3 апреля 2013 г.

Для поиска ЧИСЛА ближайшего к заданному, в EXCEL существуют специальные функции, например, ВПР() , но они работают только если исходный список сортирован по возрастанию или убыванию.

Пусть имеется сортированный по возрастанию список чисел (в диапазоне A4:A15 ) (см. Файл примера ).

В качестве критерия для поиска используем любое число (не обязательно из списка), введем его в ячейку С4 . Найдем значение из диапазона, ближайшее к критерию.

Для решения этой задачи в EXCEL существует функция ВПР() : = ВПР(C4;A4:A15;1;ИСТИНА)

Тот же результат можно получить с помощью функции ПРОСМОТР() и ПОИСКПОЗ() с типом сопоставления =1: = ПРОСМОТР(C4;A4:A15;A4:A12) = ИНДЕКС(A4:A15;ПОИСКПОЗ(C4;A4:A15;1))

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

При использовании этих формул следует помнить следующее:

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

Чтобы найти наименьшее значение, которое больше или равно, чем искомое значение (ближайшее сверху) можно использовать функцию ПОИСКПОЗ() с типом сопоставления =-1, но для этого требуется чтобы список был отсортирован по убыванию.

Также для поиска ближайшего числа к заданному можно использовать формулы массива , которые не требуют предварительной сортировки списка. Пример приведен в статье Поиск ЧИСЛА ближайшего к заданному. Несортированный список .

Список с пустыми ячейками

Если список сортирован и содержит пустые ячейки, то ВПР() будет работать без проблем и найдет ближайшее значение, которое МЕНЬШЕ или равно заданному (4-й критерий равен ИСТИНА или опущен).

В файле примера также приведена формула, которая ищет ближайшее значение, которое БОЛЬШЕ или равно заданному.

Источник

Поиск ЧИСЛА ближайшего к заданному. Несортированный список в EXCEL

history 3 апреля 2013 г.

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

Решение задачи поиска ближайшего числового значения в случае сортированного списка приведена в статье Поиск ЧИСЛА ближайшего к заданному. Сортированный список .

Рассмотрим задачу в более общем виде. Пусть имеется несортированный список чисел (в диапазоне A4:A15 ). (см. Файл примера ).

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

Решение

Как происходит поиск

Примечание

ищется наибольшее значение, которое меньше, чем искомое значение (ближайшее сверху)

Если заданное значение меньше минимального, то выдается ошибка #Н/Д

= ИНДЕКС(A4:A15;ПОИСКПОЗ( МИН(ЕСЛИ(A4:A15>=C4;A4:A15;»»)); $A$4:$A$15;0);1)

ищется наименьшее значение, которое больше, чем искомое значение (ближайшее снизу)

Если заданное значение больше максимального, то выдается ошибка #Н/Д

= ИНДЕКС(A4:A15;ПОИСКПОЗ( МИН(ABS(A4:A15-C4)); ABS(A4:A15-C4);0))

ищется ближайшее к критерию число

если ближайшее снизу и ближайшее сверху отстоят на одинаковое расстояние от критерия, то берется ближайшее число, расположенное первым в списке (например, ближайшее к 5 в списке 2; 4 ; 6 ; 8 будет 4, а в списке 2; 6 ; 4 ; 8 — будет 6), т.е. предсказать будет ли число ближайшим сверху или снизу невозможно

ищется ближайшее к критерию число

если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое больше

ищется ближайшее к критерию число

если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое меньше

СОВЕТ: Для пошагового просмотра хода вычислений формул используйте клавишу F9 .

Источник

Поиск ближайшего значения Excel с помощью формулы

Каждый пользователь Excel без проблем может найти наименьшее или наибольшее значение в диапазоне чисел, используя для этого функции: =МИН(), =МАКС() или =НАИМЕНЬШИЙ() и =НАИБОЛЬШИЙ(). Так же легко найти номер позиции исходного значения в диапазоне ячеек с помощью функции =ПОИСКПОЗ(). Но в данном примере будет более интересное решение, которое позволяет выполнить поиск ближайшего значения в Excel. Создадим формулу, которая способна находить наиболее приближенное значение к соответствию запроса пользователя. Например, несли диапазон данных не содержит значений для точного совпадения с запросом пользователя то функция ПОИСКПОЗ возвращает ошибку #Н/Д. Но пользователя вполне устроил бы и приближенный результат, не зависимо от того будет ли он немного меньше или немного больше соответствовать запросу. Важным преимуществом такой формулы заключается в том, что нет необходимости использовать условную сортировку для решения такого рода задач.

Как найти ближайшее значение в Excel?

Возьмем для примера, конкретную ситуацию. Фирма переводит склад на новое место, и чтобы полностью заполнить фуру товарами с одинаковым объемом упаковок (например, офисная бумага для принтера формат A4 по 500 листов) нужно вложить еще 220 пачке. Но желательно не перемешивать ассортимент продукции. То есть нужно постараться избежать пересорта товаров при переезде на новый склад. Заполним оставшееся пустое место в фуре пачками офисной бумаги одного и того же типа стандарта качества. У нас есть остатки по товарам всех ассортиментов:

Нам нужно выполнить поиск ближайшего меньшего значения Excel. Чтобы найти ассортимент с наиболее подходящим количеством по остаткам (не более 220 шт.) создаем формулу:

1. В ячейке E2 введите значение 220 – это количество пачек офисной бумаги, которое соответствует для заполнения свободного объема в фуре.
2. В ячейке E3 вводим формулу: Подобную формулу можно использовать для поиска ближайшего значения в диапазоне Excel, а не только в одном столбце.
3. Для подтверждения ввода формулы нажимаем комбинацию клавиш CTRL+SHIFT+Enter, так как формула должна выполняться в массиве. Если вы сделали все правильно, то в строке формул вы должны заметить фигурные скобки.

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

В результате поедет комплект одного ассортимента бумаги тип-9 (195шт.). Так как его количество на остатках наиболее приближенно соответствует к объему в 220 пачек. Фура будет максимально возможно заполнена, а на складе будет меньше пересорта по ассортиментам товаров.

Принцип поиска ближайшего значения по формуле:

Из каждого числа остатков в диапазоне ячеек B3:B12 вычитается исходное значение в ячейке E2. Таким образом создается условная таблица значений равных этой разницы, с размером соответствующему числу ячеек в диапазоне B3:B12. Функция ABS возвращает абсолютную величину числа по модулю и в этой же условной таблице заменяет все значения отрицательных чисел на положительные (без знака минус). Из полученных данных находим наименьшее значение с помощью функции =МИН(). А функция =ПОИСКПОЗ() возвращает нам позицию в условной таблице для наименьшего значения найденного функций МИН. Полученный результат вычисления функцией ПОИСКПОЗ является аргументом для функции =ИНДЕКС(), которая возвращает нам значение ячейки находящиеся под номером позиции в диапазоне B3:B12.

Другие возможности Excel для поиска значений

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

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

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

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

Источник