Проналажење најближег броја

Садржај

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

Обрачун попуста у зависности од обима.
Обрачун износа бонуса у зависности од реализације плана.
Израчунавање цене доставе у зависности од удаљености.
Избор одговарајућих контејнера за робу итд.

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

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

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

Како брзо и лепо израчунати проценат попуста при уносу количине купљене робе?

Метод 1: Угнежђени ИФ

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

Проналажење најближег броја

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

Поред тога, Мицрософт Екцел има ограничење угнежђења за функцију ИФ – 7 пута у старијим верзијама и 64 пута у новијим верзијама. Шта ако вам треба више?

Метод 2. ВЛООКУП са интервалним приказом

Овај метод је много компактнији. Да бисте израчунали проценат попуста, користите легендарну функцију ВПР (ВЛООКУП) у приближном режиму претраге:

Проналажење најближег броја

где

B4 – вредност количине робе у првој трансакцији за коју тражимо попуст
$Г$4:$Х$8 – линк ка табели попуста – без „заглавља“ и са адресама фиксираним знаком $.
2 — редни број колоне у табели попуста из које желимо да добијемо вредност попуста
ТАЧНО – овде је „пас” закопан. Ако као последњи аргумент функције ВПР спецификовати ЛЕЖАЊЕ (НЕТАЧНО) или КСНУМКС, онда ће функција тражити строга утакмица у колони количина (а у нашем случају ће дати грешку #Н/А, пошто у табели попуста нема вредности 49). Али ако уместо тога ЛЕЖАЊЕ write (писати) ТАЧНО (ИСТИНИТО) или КСНУМКС, онда функција неће тражити тачно, већ најближи најмањи вредност и даће нам проценат попуста који нам је потребан.

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

Проналажење најближег броја

Сходно томе, овај приступ се може користити само за проналажење најближе најмање вредности. Ако треба да пронађете најближи највећи, онда морате да користите другачији приступ.

Метод 3. Проналажење најближег највећег помоћу функција ИНДЕКС и МАТЦХ

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

Функција ВЛООКУП овде неће помоћи, па ћете морати да користите њен аналог – гомилу ИНДЕКС функција (ИНДЕКС) и ИЗЛОЖЕНИЈИ (УТАКМИЦА):

Проналажење најближег броја

Овде функција МАТЦХ са последњим аргументом -1 ради у режиму проналажења најближе највеће вредности, а функција ИНДЕКС затим извлачи назив модела који нам је потребан из суседне колоне.

Метод 4. Нова функција ВИЕВ (КСЛООКУП)

Ако имате верзију Оффице 365 са инсталираним свим ажурирањима, уместо ВЛООКУП-а (ВЛООКУП) можете користити његов аналог – функцију ВИЕВ (КСЛООКУП), који сам већ детаљно анализирао:

Проналажење најближег броја

Овде:

B4 – почетна вредност количине производа за коју тражимо попуст
$Г$4:$Г$8 – опсег у коме тражимо шибице
$Х$4:$Х$8 – опсег резултата из којих желите да вратите попуст
четврти аргумент (-1) укључује претрагу за најближим најмањим бројем који желимо уместо тачног подударања.

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

Али, нажалост, још увек немају сви ову функцију - само срећни власници Оффице 365.

Метод 5. Повер Куери

Ако још нисте упознати са моћним и потпуно бесплатним Повер Куери додатком за Екцел, онда сте ту. Ако сте већ упознати, хајде да покушамо да га искористимо да решимо наш проблем.

Хајде да прво урадимо неке припремне радове:

Хајде да конвертујемо наше изворне табеле у динамичке (паметне) користећи пречицу на тастатури Цтрл+T или тим Почетна – Формат као табела (Почетна — Формат као табела).
Ради јасноће, дајмо им имена. Продајни и Popusti табулатор Конструктор (Дизајн).
Учитајте сваку од табела редом у Повер Куери помоћу дугмета Из табеле/опсега табулатор Датум (Подаци — из табеле/опсега). У новијим верзијама Екцел-а, ово дугме је преименовано у Са лишћем (Са листа).
Ако табеле имају различите називе колона са количинама, као у нашем примеру („Количина робе“ и „Количина од…“), онда се морају преименовати у Повер Куерију и назвати истим именом.
Након тога, можете се вратити у Екцел тако што ћете изабрати команду у прозору Повер Куери едитора Почетна — Затвори и учитај — Затвори и учитај у… (Почетна — Затвори&Учитај — Затвори&Учитај у…) а затим опција Само створите везу (Само креирајте везу).
Тада почиње оно најзанимљивије. Ако имате искуства у Повер Куери-у, онда претпостављам да би даље размишљање требало да буде у правцу спајања ове две табеле са упитом за спајање (мерге) а ла ВЛООКУП, као што је био случај у претходној методи. У ствари, мораћемо да се спојимо у режиму додавања, што на први поглед уопште није очигледно. Изаберите на картици Екцел Подаци – Добијте податке – Комбинујте захтеве – Додајте (Подаци — Добијте податке — Комбинујте упите — Додати) а затим и наше трпезе Продајни и Popusti у прозору који се појави:
После клика на OK наши столови ће бити залепљени у једну целину – један испод другог. Напомињемо да су колоне са количином робе у овим табелама потпадале једна испод друге, јер. имају исто име:
Ако вам је важан оригинални редослед редова у табели продаје, онда, да бисте после свих наредних трансформација могли да га вратите, додајте нумерисану колону нашој табели помоћу команде Додавање колоне – колона индекса (Додај колону — Индекс колона). Ако вам редослед редова није битан, онда можете прескочити овај корак.
Сада, користећи падајућу листу у заглављу табеле, сортирајте је по колони количина Узлазни:
И главни трик: кликните десним тастером миша на заглавље колоне Попуст изабрати тим Попунити доле (Попунити доле). Празне ћелије са нула аутоматски се попуњава претходним вредностима попуста:
Остаје да се врати оригинални низ редова сортирањем по колони индекс (можете безбедно да га избришете касније) и ослободите се непотребних линија помоћу филтера нула по колони Шифра трансакције: