Наоѓање на најблискиот број

содржина

Во пракса, многу често има случаи кога јас и ти треба да ја најдеме најблиската вредност во множество (табела) во однос на даден број. Тоа може да биде, на пример:

Пресметка на попуст во зависност од обемот.
Пресметка на износот на бонуси во зависност од спроведувањето на планот.
Пресметка на цени за испорака во зависност од растојанието.
Избор на соодветни контејнери за стока и сл.

Покрај тоа, може да биде потребно заокружување и нагоре и надолу, во зависност од ситуацијата.

Постојат неколку начини - очигледни и не толку очигледни - да се реши таков проблем. Ајде да ги погледнеме последователно.

За почеток, да замислиме добавувач кој дава попусти на големо, а процентот на попустот зависи од количината на купената стока. На пример, при купување на повеќе од 5 парчиња, се дава попуст од 2%, а при купување од 20 парчиња – веќе 6% итн.

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

Метод 1: Вгнездени IF

Метод од серијата „што има да размислуваш – треба да скокнеш!“. Користење на вгнездени функции IF (АКО) последователно да се провери дали вредноста на ќелијата паѓа во секој од интервалите и да прикаже попуст за соодветниот опсег. Но, формулата во овој случај може да испадне многу незгодна:

Наоѓање на најблискиот број

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

Покрај тоа, Microsoft Excel има ограничување за вгнездување за функцијата IF - 7 пати во постарите верзии и 64 пати во поновите верзии. Што ако ви треба повеќе?

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

Овој метод е многу покомпактен. За да го пресметате процентот на попуст, користете ја легендарната функција VPR (VLOOKUP) во приближен режим на пребарување:

Наоѓање на најблискиот број

каде

B4 – вредноста на количината на стоката во првата трансакција за која бараме попуст
4 $ G $: $ H $ 8 – линк до табелата со попусти – без „заглавие“ и со адреси фиксирани со знакот $.
2 — редниот број на колоната во табелата со попусти од која сакаме да ја добиеме вредноста на попустот
ТОЧНО – тука е закопано „кучето“. Ако како последен функциски аргумент VPR наведете ЛАЖЕЊЕ (НЕТОЧНО) или 0, тогаш функцијата ќе бара строг натпревар во колоната количина (а во нашиот случај ќе даде грешка #N/A, бидејќи нема вредност 49 во табелата со попусти). Но, ако наместо тоа ЛАЖЕЊЕ пишувам ТОЧНО (ВИСТИНСКО) или 1, тогаш функцијата ќе бара не точно, туку најблискиот најмал вредност и ќе ни го даде процентот на попуст што ни треба.

Негативна страна на овој метод е потребата да се подреди табелата за попуст во растечки редослед по првата колона. Ако не постои такво сортирање (или тоа е направено во обратен редослед), тогаш нашата формула нема да работи:

Наоѓање на најблискиот број

Според тоа, овој пристап може да се користи само за да се најде најблиската најмала вредност. Ако треба да го пронајдете најблискиот најголем, тогаш треба да користите поинаков пристап.

Метод 3. Наоѓање на најблиската најголема со помош на функциите INDEX и MATCH

Сега да го погледнеме нашиот проблем од другата страна. Да претпоставиме дека продаваме неколку модели на индустриски пумпи со различни капацитети. Табелата за продажба лево ја покажува моќноста што ја бара клиентот. Треба да избереме пумпа со најблиска максимална или еднаква моќност, но не помала од она што го бара проектот.

Функцијата VLOOKUP нема да помогне тука, па ќе мора да го користите нејзиниот аналог - еден куп функции INDEX (ИНДЕКС) и ПОИЗЛОЖЕНИ (натпревар):

Наоѓање на најблискиот број

Овде, функцијата MATCH со последниот аргумент -1 работи во режим на наоѓање на најблиската најголема вредност, а функцијата INDEX потоа го извлекува името на моделот што ни треба од соседната колона.

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

Ако имате верзија на Office 365 со инсталирани сите ажурирања, тогаш наместо VLOOKUP (VLOOKUP) можете да го користите неговиот аналог - функцијата VIEW (XLOOKUP), што веќе детално го анализирав:

Наоѓање на најблискиот број

Тука:

B4 – почетната вредност на количината на производот за кој бараме попуст
$4 G$: $8 G$ – опсегот каде што бараме натпревари
4 $ H $: $ H $ 8 – опсегот на резултати од кои сакате да го вратите попустот
четврти аргумент (-1) го вклучува пребарувањето за најблискиот најмал број што го сакаме наместо точно совпаѓање.

Предностите на овој метод се тоа што нема потреба да се подредува табелата со попусти и можност за пребарување, доколку е потребно, не само најблиската најмала, туку и најблиската најголема вредност. Последниот аргумент во овој случај ќе биде 1.

Но, за жал, сè уште не сите ја имаат оваа функција - само среќни сопственици на Office 365.

Метод 5. Power Query

Ако сè уште не сте запознаени со моќниот и целосно бесплатен додаток Power Query за Excel, тогаш сте тука. Ако веќе сте запознаени, тогаш ајде да се обидеме да го искористиме за да го решиме нашиот проблем.

Ајде прво да направиме подготвителна работа:

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