Как найти ближайшее значение эксель

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

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

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

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

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

Для начала, представим себе поставщика, который дает скидки на опт, причем процент скидки зависит от количества купленного товара. Например, при покупке свыше 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 существует специальные функции, например,

ВПР()

,

ПРОСМОТР()

,

ПОИСКПОЗ()

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

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

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

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

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

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

.

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

A4:A15

). (см.

Файл примера

).

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

С4

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

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

:

СОВЕТ:

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

F9

.

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

Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS 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 обладает большими возможностями в этой области. Можно сортировать данные относительно многих критериев и столбцов, а также относительно формата ячеек. В следующих статьях будет представлен целый ряд эффективных способов сортировки, которые мало известные большинству пользователям Excel.

Просмотров: 426

Функция ИНДЕКС, ПОИСКПОЗ и СМЕЩ. Постановка задачи

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

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

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

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

• Искомое значение. Это значение, которое мы будем искать в списке. Значение должно быть уникальным, иначе Excel найдет только первое!
• Диапазон списка. Может состоять только из одного столбца или одной сроки. Это важно! Если попробовать выделить две колонки или две строки, программа выдаст ошибку!
• Способ поиска. Тут надо указать вариант, согласно которому приложение выполнит поиск позиции для нашего искомого значения. Тут возможны следующие типы.

1  Будет найдено ближайшее к нашему значение, которое не превышает его, то есть НЕ БОЛЬШЕ нужного. Равным может быть, но не больше. Список должен располагаться ПО ВОЗРАСТАНИЮ.

-1  Excel найдет значение, которое самое близкое к заданному нами, но НЕ МЕНЬШЕ ЕГО. Список должен быть ПО УБЫВАНИЮ.

0   Запустится поиск ТОЧНОГО СООТВЕТСТВИЯ значения из списка заданному нами. Сортировка тут НЕ ТРЕБУЕТСЯ.

Наглядное применение ПОИСКПОЗ.

Посмотрите на скриншот.

Особое внимание обратите вот на что. Во второй и третьей формуле задан тип поиска равный 1 (единице). Однако если в первой из них Excel искал число, которое действительно присутствует в списке, то есть число 50, то он его порядковый номер и указал. А вот числа 68 в списке нет, поэтому он указал порядковый номер значения из списка, которое находится ближе всего к нужному нам, то есть к 68, но его не превышает. А это – число 60.

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

Замечу, что для текстовых списков надо указывать максимально возможное ТЕКСТОВОЕ значение, например, так: «ЯЯЯ». В случае с числовыми данными указывает максимальное ЧИСЛОВОЕ значение, например, 99999999.

Функция ИНДЕКС в Excel и ее особенности.

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

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

Стоит поменять в ней значение, и результат функции СУММ поменяется.

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

1. Области, которые будут обработаны. Их пишут в отдельных скобках, разделяя точкой с запятой или запятой в зависимости от ваших настроек.
2. Строка в выбранной области, которая интересует
3. Столбец, на пересечении которого с указанной строкой надо взять значение
4. Область из перечисленных в начале.

Результат работы может выглядеть так, как на рисунке.

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

Функция СМЕЩ в Excel и тонкости ее применения.

.Функция СМЕЩ показывает значение ячейки или диапазона, которые смещен относительно указанного адреса на нужное количество строк и столбцов. Ее синтаксис следующий.

СМЕЩ(1;2;3;4;5)

1. Ссылка, от которой отсчитывается перемещение.
2. На сколько строк надо переместиться.
3. Сколько  столбцов надо отсчитать для перемещения.
4. Объем строк в диапазоне, на которые перемещаемся. Указывать не обязательно.
5. Количество столбцов, на которые перемещаемся. Тоже указывать не обязательно.

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

Приведем пример.

В примере выше происходит перемещение от ячейки G11 сначала на 2 строчки вверх, затем на 4 колонки влево. В найденной ячейки находится число 10, которое и является результатом работы функции.

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

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

Первый вариант создания “левого ВПР”. Комбинируем ИНДЕКС и ПОИСКПОЗ. 

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

Данные формулы конечно можно объединить в одну. Если в последней формуле вместо адреса К8 указать ее содержимое, то есть записать ПОИСКПОЗ(K6;C7:C86;0), то результат работы не изменится. Итоговая формула будет уже такой:

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

Второй вариант создания “левого ВПР”. Комбинируем СМЕЩ и ПОИСКПОЗ.

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

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

Особенности связки СМЕЩ и ПОИСКПОЗ

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

Делаем выводы.

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

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