Pronalaženje najbližeg broja

sadržaj

U praksi se vrlo često dešavaju slučajevi kada vi i ja treba da pronađemo najbližu vrijednost u skupu (tablici) u odnosu na dati broj. To može biti, na primjer:

Obračun popusta u zavisnosti od količine.
Obračun iznosa bonusa u zavisnosti od realizacije plana.
Obračun cijena dostave ovisno o udaljenosti.
Izbor odgovarajućih kontejnera za robu itd.

Štaviše, zaokruživanje može biti potrebno i prema gore i prema dolje, ovisno o situaciji.

Postoji nekoliko načina – očiglednih i ne tako očiglednih – da se riješi takav problem. Pogledajmo ih uzastopno.

Za početak, zamislimo dobavljača koji daje popuste na veleprodaju, a postotak popusta zavisi od količine kupljene robe. Na primjer, pri kupovini više od 5 komada daje se popust od 2%, a pri kupovini od 20 komada – već 6% itd.

Kako brzo i lijepo izračunati postotak popusta pri unosu količine kupljene robe?

Metoda 1: Ugniježđeni IF-ovi

Metoda iz serije „šta ima da se misli – treba skočiti!“. Korištenje ugniježđenih funkcija IF (AKO) kako biste uzastopno provjerili da li vrijednost ćelije spada u svaki od intervala i prikazali popust za odgovarajući raspon. Ali formula u ovom slučaju može se pokazati vrlo glomaznom:

Pronalaženje najbližeg broja

Mislim da je očigledno da je otklanjanje grešaka u takvoj “čudovišnoj lutki” ili pokušaj da joj se doda nekoliko novih uslova nakon nekog vremena zabavno.

Osim toga, Microsoft Excel ima ograničenje ugniježđenja za funkciju IF – 7 puta u starijim verzijama i 64 puta u novijim verzijama. Šta ako vam treba više?

Metoda 2. VLOOKUP sa intervalnim prikazom

Ova metoda je mnogo kompaktnija. Za izračunavanje postotka popusta koristite legendarnu funkciju VPR (Vlookup) u približnom načinu pretraživanja:

Pronalaženje najbližeg broja

gdje

B4 – vrijednost količine robe u prvoj transakciji za koju tražimo popust
$G$4:$H$8 – link do tabele popusta – bez “zaglavlja” i sa adresama fiksiranim znakom $.
2 — redni broj kolone u tabeli popusta iz koje želimo da dobijemo vrednost popusta
ISTINITO – tu je „pas“ zakopan. Ako kao posljednji argument funkcije VPR odrediti LAŽEŠ (NETOČNO) ili 0, tada će funkcija tražiti stroga utakmica u koloni količina (a u našem slučaju će dati grešku #N/A, jer u tabeli popusta nema vrijednosti 49). Ali ako umjesto toga LAŽEŠ pisati ISTINITO (TAČNO) ili 1, tada funkcija neće tražiti točno, već najbliži najmanji vrijednost i dat će nam postotak popusta koji nam je potreban.

Loša strana ove metode je potreba za sortiranjem tabele popusta uzlaznim redoslijedom prema prvoj koloni. Ako nema takvog sortiranja (ili se radi obrnutim redoslijedom), onda naša formula neće raditi:

Pronalaženje najbližeg broja

Shodno tome, ovaj pristup se može koristiti samo za pronalaženje najbliže najmanje vrijednosti. Ako trebate pronaći najbliži najveći, onda morate koristiti drugačiji pristup.

Metoda 3. Pronalaženje najbližeg najvećeg pomoću funkcija INDEX i MATCH

Pogledajmo sada naš problem s druge strane. Pretpostavimo da prodajemo nekoliko modela industrijskih pumpi različitih kapaciteta. Prodajna tabela na lijevoj strani pokazuje snagu potrebnu za kupca. Moramo odabrati pumpu najbliže maksimalne ili jednake snage, ali ne manje od one koja je predviđena projektom.

Funkcija VLOOKUP ovdje neće pomoći, tako da ćete morati koristiti njen analog – hrpu INDEX funkcija (INDEX) i IZLOŽENIJI (UTAKMICA):

Pronalaženje najbližeg broja

Ovdje funkcija MATCH sa posljednjim argumentom -1 radi u načinu pronalaženja najbliže najveće vrijednosti, a funkcija INDEX tada izvlači naziv modela koji nam je potreban iz susjedne kolone.

Metoda 4. Nova funkcija VIEW (XLOOKUP)

Ako imate verziju Office 365 sa instaliranim svim ažuriranjima, umjesto VLOOKUP-a (Vlookup) možete koristiti njegov analog – funkciju VIEW (XLOOKUP), koji sam već detaljno analizirao:

Pronalaženje najbližeg broja

Ovdje:

B4 – početna vrijednost količine proizvoda za koju tražimo popust
$G$4:$G$8 – raspon u kojem tražimo šibice
$H$4:$H$8 – raspon rezultata iz kojih želite da vratite popust
četvrti argument (-1) uključuje pretragu za najbližim najmanjim brojem koji želimo umjesto tačnog podudaranja.

Prednosti ove metode su u tome što nema potrebe za sortiranjem tabele popusta i mogućnost pretraživanja, ako je potrebno, ne samo najbliže najmanje, već i najbliže najveće vrijednosti. Posljednji argument u ovom slučaju će biti 1.

Ali, nažalost, još uvijek nemaju svi ovu funkciju – samo sretni vlasnici Office 365.

Metod 5. Power Query

Ako još niste upoznati sa moćnim i potpuno besplatnim dodatkom Power Query za Excel, onda ste ovdje. Ako ste već upoznati, pokušajmo to iskoristiti za rješavanje našeg problema.

Prvo uradimo neke pripremne radove:

Konvertujmo naše izvorne tabele u dinamičke (pametne) koristeći prečicu na tastaturi Ctrl+T ili tim Početna – Formatirajte kao tabelu (Početna — Format kao tabela).
Radi jasnoće, dajmo im imena. prodajni и Popusti tabulator konstruktor (Dizajn).
Učitajte svaku od tabela redom u Power Query pomoću dugmeta Iz tabele/opseg tabulator podaci (Podaci — iz tabele/opseg). U novijim verzijama Excela, ovo dugme je preimenovano u Sa lišćem (sa lista).
Ako tabele imaju različite nazive kolona s količinama, kao u našem primjeru („Količina robe” i „Količina od…”), onda se moraju preimenovati u Power Queryju i nazvati istim imenom.
Nakon toga, možete se vratiti u Excel odabirom naredbe u prozoru Power Query editora Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj na…) a zatim opciju Samo stvorite vezu (Samo kreirajte vezu).
Tada počinje ono najzanimljivije. Ako imate iskustva u Power Queryju, onda pretpostavljam da bi dalje razmišljanje trebalo da bude u pravcu spajanja ove dve tabele sa upitom za spajanje (spajanje) a la VLOOKUP, kao što je bio slučaj u prethodnoj metodi. Zapravo, morat ćemo se spojiti u načinu dodavanja, što na prvi pogled uopće nije očito. Odaberite na kartici Excel Podaci – Dobijte podatke – Kombinirajte zahtjeve – Dodajte (Podaci — Dobijte podatke — Kombinirajte upite — Dodati) a onda i naši stolovi prodajni и Popusti u prozoru koji se pojavi:
Posle klika na OK naši stolovi će biti zalijepljeni u jednu cjelinu – jedan ispod drugog. Napominjemo da su kolone sa količinom robe u ovim tabelama potpadale jedna ispod druge, jer. imaju isti naziv:
Ako vam je važan originalni redoslijed redova u tablici prodaje, onda da biste ga nakon svih naknadnih transformacija mogli vratiti, dodajte numerirani stupac našoj tablici pomoću naredbe Dodavanje kolone – kolona indeksa (Dodaj kolonu — Indeksni stupac). Ako vam redoslijed redova nije bitan, onda možete preskočiti ovaj korak.
Sada, koristeći padajuću listu u zaglavlju tabele, sortirajte je po koloni Količina Uzlazno:
I glavni trik: desnim klikom na zaglavlje kolone Popust izabrati tim Ispuna – Dolje (Popuni — dolje). Prazne ćelije sa null automatski se popunjava prethodnim vrijednostima popusta:
Ostaje vratiti originalni niz redova sortiranjem po koloni indeks (možete ga sigurno izbrisati kasnije) i riješite se nepotrebnih linija pomoću filtera null po koloni Šifra transakcije: