Намиране на най-близкия номер

Съдържание

В практиката много често има случаи, когато вие и аз трябва да намерим най-близката стойност в набор (таблица) спрямо дадено число. Може да бъде например:

Изчисляване на отстъпка в зависимост от обема.
Изчисляване на размера на бонусите в зависимост от изпълнението на плана.
Изчисляване на цените за доставка в зависимост от разстоянието.
Избор на подходящи контейнери за стоки и др.

Освен това може да се наложи закръгляване както нагоре, така и надолу, в зависимост от ситуацията.

Има няколко начина – очевидни и не толкова очевидни – за решаване на такъв проблем. Нека ги разгледаме последователно.

Като начало, нека си представим доставчик, който дава отстъпки на едро, като процентът на отстъпката зависи от количеството закупени стоки. Например при закупуване на повече от 5 броя се прави 2% отстъпка, а при покупка от 20 броя – вече 6% и т.н.

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

Метод 1: Вложени IF

Метод от поредицата "какво има да мислиш - трябва да скочиш!". Използване на вложени функции IF (АКО) за последователна проверка дали стойността на клетката попада във всеки от интервалите и показване на отстъпка за съответния диапазон. Но формулата в този случай може да се окаже много тромава:

Намиране на най-близкия номер

Мисля, че е очевидно, че отстраняването на грешки на такава „кукла-чудовище“ или опитът за добавяне на няколко нови условия към нея след известно време е забавно.

Освен това Microsoft Excel има ограничение за влагане на функцията IF – 7 пъти в по-стари версии и 64 пъти в по-нови версии. Ами ако имате нужда от повече?

Метод 2. VLOOKUP с интервален изглед

Този метод е много по-компактен. За да изчислите процента на отстъпката, използвайте легендарната функция ВПР (VLOOKUP) в режим на приблизително търсене:

Намиране на най-близкия номер

където

B4 – стойността на количеството стока при първата сделка, за която търсим отстъпка
$G$4:$H$8 – връзка към таблицата с отстъпки – без „хедър“ и с фиксирани адреси със знака $.
2 — поредният номер на колоната в таблицата с отстъпките, от която искаме да вземем стойността на отстъпката
TRUE – тук е заровено „кучето“. If като последен аргумент на функцията ВПР посочете ЛЪЖА (НЕВЯРНО) или 0, тогава функцията ще търси строго съответствие в колоната за количество (и в нашия случай ще даде грешка #N/A, тъй като няма стойност 49 в таблицата с отстъпки). Но ако вместо това ЛЪЖА пиша TRUE (ВЯРНО) или 1, тогава функцията ще търси не точното, а най-близкият най-малък стойност и ще ни даде необходимия процент отстъпка.

Недостатъкът на този метод е необходимостта от сортиране на таблицата с отстъпки във възходящ ред по първата колона. Ако няма такова сортиране (или се извършва в обратен ред), тогава нашата формула няма да работи:

Намиране на най-близкия номер

Съответно този подход може да се използва само за намиране на най-близката най-малка стойност. Ако трябва да намерите най-близкия най-голям, тогава трябва да използвате различен подход.

Метод 3. Намиране на най-близкия най-голям с помощта на функциите INDEX и MATCH

Сега нека погледнем нашия проблем от другата страна. Да предположим, че продаваме няколко модела индустриални помпи с различен капацитет. Таблицата с продажби вляво показва необходимата мощност на клиента. Трябва да изберем помпа с най-близката максимална или равна мощност, но не по-малка от изискваната от проекта.

Функцията VLOOKUP няма да помогне тук, така че ще трябва да използвате нейния аналог - куп функции INDEX (ИНДЕКС) и ПО-ЕКСПОЗИРАН (СЪВПАДА):

Намиране на най-близкия номер

Тук функцията MATCH с последния аргумент -1 работи в режим на намиране на най-близката най-голяма стойност, а функцията INDEX след това извлича името на модела, от което се нуждаем, от съседната колона.

Метод 4. Нова функция VIEW (XLOOKUP)

Ако имате версия на Office 365 с всички инсталирани актуализации, тогава вместо VLOOKUP (VLOOKUP) можете да използвате неговия аналог – функцията VIEW (XLOOKUP), които вече анализирах подробно:

Намиране на най-близкия номер

Тук:

B4 – първоначалната стойност на количеството от продукта, за което търсим отстъпка
$G$4:$G$8 – диапазонът, в който търсим съвпадения
$H$4:$H$8 – диапазонът от резултати, от които искате да върнете отстъпката
четвърти аргумент (-1) включва търсене на най-близкото най-малко число, което искаме, вместо точно съвпадение.

Предимствата на този метод са, че няма нужда да сортирате таблицата с отстъпки и възможността да търсите, ако е необходимо, не само най-близката най-малка, но и най-близката най-голяма стойност. Последният аргумент в този случай ще бъде 1.

Но, за съжаление, все още не всеки има тази функция - само щастливите собственици на Office 365.

Метод 5. Power Query

Ако все още не сте запознати с мощната и напълно безплатна добавка Power Query за Excel, значи сте тук. Ако вече сте запознати, тогава нека се опитаме да го използваме, за да разрешим нашия проблем.

Нека първо направим подготвителна работа:

Нека преобразуваме нашите изходни таблици в динамични (интелигентни) с помощта на клавишна комбинация Ctrl+T или екип Начало – Форматиране като таблица (Начало — Форматиране като таблица).
За по-голяма яснота нека им дадем имена. Продажби и Намаления етикет конструктор (Дизайн).
Заредете всяка от таблиците последователно в Power Query с помощта на бутона От таблица/обхват етикет Дата (Данни — От таблица/обхват). В последните версии на Excel този бутон е преименуван на С листа (От лист).
Ако таблиците имат различни имена на колони с количества, както в нашия пример („Количество стоки“ и „Количество от...“), тогава те трябва да бъдат преименувани в Power Query и именувани по същия начин.
След това можете да се върнете обратно към Excel, като изберете командата в прозореца на редактора на Power Query Начало — Затвори и зареди — Затвори и зареди в… (Начало — Затвори&Зареди — Затвори&Зареди в...) и след това опция Просто създайте връзка (Само създаване на връзка).
Тогава започва най-интересното. Ако имате опит в Power Query, тогава предполагам, че по-нататъшната линия на мисъл трябва да бъде в посока на сливане на тези две таблици със заявка за присъединяване (сливане) a la VLOOKUP, както беше в предишния метод. Всъщност ще трябва да се слеем в режим на добавяне, което изобщо не е очевидно на пръв поглед. Изберете в раздела Excel Данни – Получаване на данни – Комбиниране на заявки – Добавяне (Данни — Получаване на данни — Комбиниране на заявки — Добавяне) и след това нашите маси Продажби и Намаления в прозореца, който се появява:
След като кликнете върху OK нашите маси ще бъдат слепени в едно цяло – една под друга. Моля, имайте предвид, че колоните с количеството на стоките в тези таблици попадат една под друга, т.к. имат едно и също име:
Ако оригиналната последователност от редове в таблицата за продажби е важна за вас, тогава, за да можете след всички последващи трансформации да я възстановите, добавете номерирана колона към нашата таблица с помощта на командата Добавяне на колона – Индексна колона (Добавяне на колона — Индекс колона). Ако последователността на редовете няма значение за вас, можете да пропуснете тази стъпка.
Сега, като използвате падащия списък в заглавката на таблицата, сортирайте я по колона Количество Възходящ:
И основният трик: щракнете с десния бутон върху заглавката на колоната Отстъпка изберете екип Запълване – надолу (Попълване — Надолу). Празни клетки с нула автоматично се попълва с предишните стойности на отстъпката:
Остава да възстановите оригиналната последователност от редове чрез сортиране по колони индекс (можете безопасно да го изтриете по-късно) и да се отървете от ненужните редове с филтър нула по колона Код на транзакцията: