Pronalaženje najbližeg broja

Sadržaj

U praksi vrlo često postoje slučajevi kada ti i ja trebamo pronaći najbližu vrijednost u skupu (tablici) u odnosu na zadani broj. To može biti, na primjer:

Obračun popusta ovisno o količini.
Izračun iznosa bonusa ovisno o provedbi plana.
Izračun cijena dostave ovisno o udaljenosti.
Odabir prikladnih spremnika za robu i sl.

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

Postoji nekoliko načina – očitih i manje očitih – za rješavanje takvog problema. Pogledajmo ih redom.

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

Kako brzo i lijepo izračunati postotak popusta prilikom unosa količine kupljene robe?

Metoda 1: Ugniježđeni IF-ovi

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

Pronalaženje najbližeg broja

Mislim da je očito da je otklanjanje pogrešaka takvoj "čudovišnoj lutki" ili pokušaj dodavanja nekoliko novih uvjeta nakon nekog vremena zabavno.

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

Metoda 2. VLOOKUP s intervalskim prikazom

Ova metoda je mnogo kompaktnija. Za izračun postotka popusta koristite legendarnu funkciju VPR (VLOOKUP) u načinu približnog 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 – poveznica na tablicu s popustima – bez „zaglavlja“ i s adresama fiksiranim znakom $.
2 — redni broj stupca u tablici popusta iz kojeg želimo dobiti vrijednost popusta
TRUE – tu je “pas” zakopan. If kao zadnji argument funkcije VPR odrediti LAGANJE (NETOČNO) ili 0, tada će funkcija tražiti strogo podudaranje u stupcu količine (i u našem slučaju to će dati grešku #N/A, budući da u tablici popusta nema vrijednosti 49). Ali ako umjesto toga LAGANJE pisati TRUE (PRAVI) 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 tablice popusta uzlaznim redoslijedom prema prvom stupcu. Ako ne postoji takvo sortiranje (ili se radi obrnutim redoslijedom), naša formula neće raditi:

Pronalaženje najbližeg broja

Prema tome, ovaj se pristup može koristiti samo za pronalaženje najbliže najmanje vrijednosti. Ako trebate pronaći najbliži najveći, tada 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. Tablica prodaje na lijevoj strani prikazuje snagu potrebnu kupcu. Moramo odabrati pumpu najbliže maksimalne ili jednake snage, ali ne manje od one koju projekt zahtijeva.

Funkcija VLOOKUP ovdje neće pomoći, pa ćete morati koristiti njezin analog - hrpu funkcija INDEX (INDEKS) i EKSPONIRANIJI (UTAKMICA):

Pronalaženje najbližeg broja

Ovdje funkcija MATCH sa zadnjim argumentom -1 radi u načinu pronalaženja najbliže najveće vrijednosti, a funkcija INDEX zatim iz susjednog stupca izvlači naziv modela koji nam treba.

Metoda 4. Nova funkcija VIEW (XLOOKUP)

Ako imate verziju Office 365 sa svim instaliranim ažuriranjima, tada umjesto VLOOKUP (VLOOKUP) možete koristiti njegov analog - funkciju VIEW (XLOOKUP), koje 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 podudaranja
$H$4:$H$8 – raspon rezultata od kojih želite vratiti popust
četvrti argument (-1) uključuje traženje najbližeg najmanjeg broja koji želimo umjesto točnog podudaranja.

Prednosti ove metode su u tome što nema potrebe za sortiranjem tablice 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 bit će 1.

Ali, nažalost, još nemaju svi ovu značajku - samo sretni vlasnici Office 365.

Metoda 5. Power Query

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

Prvo obavimo neke pripremne radove:

Pretvorimo naše izvorne tablice u dinamičke (pametne) pomoću prečaca na tipkovnici Ctrl+T ili tim Početna – Format kao tablica (Početna — Formatiraj kao tablicu).
Radi jasnoće, dajmo im imena. Prodaje и Popusti kartica Konstruktor (Oblikovati).
Učitajte svaku od tablica redom u Power Query pomoću gumba Iz tablice/raspona kartica Datum (Podaci — iz tablice/raspona). U novijim verzijama programa Excel ovaj je gumb preimenovan u S lišćem (S lista).
Ako tablice imaju različite nazive stupaca s količinama, kao u našem primjeru (“Količina robe” i “Količina od …”), tada ih je potrebno preimenovati u Power Queryju i nazvati istim.
Nakon toga se možete vratiti u Excel odabirom naredbe u prozoru uređivača Power Query Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj u…) a zatim opciju Samo stvorite vezu (Samo stvoriti vezu).
Tada počinje ono najzanimljivije. Ako imate iskustva u Power Queryju, onda pretpostavljam da bi daljnji pravac razmišljanja trebao biti u smjeru spajanja ove dvije tablice upitom za spajanje (spoj) a la VLOOKUP, kao što je bio slučaj u prethodnoj metodi. Zapravo, morat ćemo spojiti u načinu dodavanja, što uopće nije vidljivo na prvi pogled. Odaberite u Excel kartici Podaci – Dohvati podatke – Kombiniraj zahtjeve – Dodaj (Podaci — Dohvati podatke — Kombiniraj upite — Dodaj) a zatim i naše stolove Prodaje и Popusti u prozoru koji se pojavi:
Nakon što kliknete OK naši će stolovi biti zalijepljeni u jednu cjelinu – jedan ispod drugog. Imajte na umu da su stupci s količinom robe u ovim tablicama potpadali jedan pod drugi, jer. imaju isti naziv:
Ako vam je originalni slijed redaka u prodajnoj tablici važan, kako biste ga nakon svih naknadnih transformacija mogli vratiti, dodajte numerirani stupac u našu tablicu pomoću naredbe Dodavanje stupca – stupac indeksa (Dodaj stupac — stupac Indeks). Ako vam redoslijed redaka nije bitan, možete preskočiti ovaj korak.
Sada pomoću padajućeg popisa u zaglavlju tablice razvrstajte je po stupcu Količina Uzlazni:
I glavni trik: desni klik na zaglavlje stupca Popust izabrati tim Popuniti (Popuniti). Prazne ćelije s nula automatski se popunjava prethodnim vrijednostima popusta:
Ostaje vratiti izvorni slijed redaka sortiranjem po stupcu indeks (možete ga kasnije sigurno izbrisati) i filtrom se riješite nepotrebnih linija nula po stupcu Kod transakcije: