Iskanje najbližje številke

vsebina

V praksi zelo pogosto pride do primerov, ko moramo ti in jaz najti najbližjo vrednost v nizu (tabeli) glede na dano število. Lahko bi bilo na primer:

Obračun popusta glede na količino.
Izračun zneska bonusov glede na izvajanje načrta.
Izračun stroškov pošiljanja glede na razdaljo.
Izbira primernih kontejnerjev za blago ipd.

Poleg tega bo morda potrebno zaokroževanje navzgor in navzdol, odvisno od situacije.

Obstaja več načinov – očitnih in manj očitnih – za rešitev takšne težave. Poglejmo jih zaporedno.

Za začetek si predstavljajmo dobavitelja, ki daje popuste na debelo, odstotek popusta pa je odvisen od količine kupljenega blaga. Na primer, pri nakupu več kot 5 kosov je 2% popust, pri nakupu od 20 kosov pa že 6% itd.

Kako hitro in lepo izračunati odstotek popusta pri vnosu količine kupljenega blaga?

1. način: ugnezdeni IF-ji

Metoda iz serije "Kaj je treba misliti - morate skočiti!". Uporaba ugnezdenih funkcij IF (ČE) da zaporedno preverite, ali vrednost celice spada v vsak interval, in prikažete popust za ustrezen obseg. Toda formula se v tem primeru lahko izkaže za zelo okorno:

Iskanje najbližje številke

Mislim, da je očitno, da je razhroščevanje takšne "pošastne lutke" ali poskus dodajanja nekaj novih pogojev čez nekaj časa zabavno.

Poleg tega ima Microsoft Excel omejitev gnezdenja za funkcijo IF – 7-krat v starejših različicah in 64-krat v novejših različicah. Kaj pa, če potrebujete več?

Metoda 2. VLOOKUP z intervalnim pogledom

Ta metoda je veliko bolj kompaktna. Za izračun odstotka popusta uporabite legendarno funkcijo VPR (VLOOKUP) v načinu približnega iskanja:

Iskanje najbližje številke

Kje

B4 – vrednost količine blaga v prvi transakciji, za katero iščemo popust
$G$4:$H$8 – povezava do tabele s popusti – brez “headerja” in z naslovi, fiksiranimi z znakom $.
2 — zaporedna številka stolpca v tabeli popustov, iz katerega želimo pridobiti vrednost popusta
TRUE – tukaj je pokopan “pes”. Če kot zadnji argument funkcije VPR navedite LAŽI (FALSE) ali 0, potem bo funkcija iskala strogo ujemanje v stolpcu količine (in v našem primeru bo dal napako #N/A, ker v tabeli popustov ni vrednosti 49). Če pa namesto tega LAŽI pisati TRUE (PRAV) ali 1, potem funkcija ne bo iskala natančnega, ampak najbližji najmanjši vrednost in nam bo dal odstotek popusta, ki ga potrebujemo.

Slaba stran te metode je potreba po razvrščanju tabele s popusti v naraščajočem vrstnem redu po prvem stolpcu. Če takšnega razvrščanja ni (ali pa se izvede v obratnem vrstnem redu), naša formula ne bo delovala:

Iskanje najbližje številke

Skladno s tem se ta pristop lahko uporabi le za iskanje najbližje najmanjše vrednosti. Če morate najti najbližjega največjega, potem morate uporabiti drugačen pristop.

Metoda 3. Iskanje najbližjega največjega z uporabo funkcij INDEX in MATCH

Zdaj pa poglejmo naš problem z druge strani. Recimo, da prodajamo več modelov industrijskih črpalk različnih zmogljivosti. Prodajna tabela na levi prikazuje moč, ki jo zahteva stranka. Izbrati moramo črpalko najbližje največje ali enake moči, vendar ne manjšo od tiste, ki jo zahteva projekt.

Funkcija VLOOKUP tukaj ne bo pomagala, zato boste morali uporabiti njen analog - kup funkcij INDEX (KAZALO) in BOLJ IZPOSTAVLJENO (TEKMA):

Iskanje najbližje številke

Tukaj funkcija MATCH z zadnjim argumentom -1 deluje v načinu iskanja najbližje največje vrednosti, funkcija INDEX pa nato iz sosednjega stolpca izvleče ime modela, ki ga potrebujemo.

Metoda 4. Nova funkcija VIEW (XLOOKUP)

Če imate nameščeno različico Office 365 z vsemi posodobitvami, potem namesto VLOOKUP (VLOOKUP) lahko uporabite njegov analog - funkcijo VIEW (XLOOKUP), ki sem jih že podrobno analiziral:

Iskanje najbližje številke

Tukaj:

B4 – začetno vrednost količine izdelka, za katerega iščemo popust
$G$4:$G$8 – območje, kjer iščemo ujemanja
$H$4:$H$8 – obseg rezultatov, od katerih želite vrniti popust
četrti argument (-1) vključuje iskanje najbližjega najmanjšega števila, ki ga želimo namesto natančnega ujemanja.

Prednosti te metode so, da ni potrebe po razvrščanju tabele popustov in možnost iskanja, če je potrebno, ne samo najbližje najmanjše, ampak tudi najbližje največje vrednosti. Zadnji argument v tem primeru bo 1.

Toda na žalost te funkcije še nimajo vsi - samo srečni lastniki Office 365.

5. način. Power Query

Če še niste seznanjeni z zmogljivim in popolnoma brezplačnim dodatkom Power Query za Excel, potem ste tukaj. Če ste že seznanjeni, ga poskusimo uporabiti za rešitev našega problema.

Najprej opravimo nekaj pripravljalnih del:

Pretvorimo naše izvorne tabele v dinamične (pametne) z uporabo bližnjice na tipkovnici Ctrl+T ali ekipa Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo).
Zaradi jasnosti jim dajmo imena. Prodaja и Popusti tab Konstruktor (Oblikovanje).
Z gumbom naložite vsako od tabel po vrsti v Power Query Iz tabele/razpona tab datum (Podatki — iz tabele/obsega). V zadnjih različicah Excela je bil ta gumb preimenovan v Z listi (Iz lista).
Če imajo tabele različna imena stolpcev s količinami, kot v našem primeru (»Količina blaga« in »Količina od …«), jih je treba v Power Queryju preimenovati in poimenovati enako.
Po tem se lahko vrnete nazaj v Excel tako, da izberete ukaz v oknu urejevalnika Power Query Domov — Zapri in naloži — Zapri in naloži v… (Domov — Zapri&Naloži — Zapri&Naloži na…) in nato možnost Samo ustvarite povezavo (Ustvari samo povezavo).
Potem se začne najbolj zanimivo. Če imate izkušnje s Power Queryjem, potem predvidevam, da bi morala biti nadaljnja smer razmišljanja v smeri združevanja teh dveh tabel s poizvedbo za združevanje (združevanje) a la VLOOKUP, kot je bilo v primeru prejšnje metode. Pravzaprav bomo morali spojiti v načinu dodajanja, kar na prvi pogled sploh ni očitno. Izberite v zavihku Excel Podatki – Pridobi podatke – Združi zahteve – Dodaj (Podatki — Pridobi podatke — Združi poizvedbe — Dodaj) in nato naše mize Prodaja и Popusti v oknu, ki se prikaže:
Po kliku OK naše mize bodo zlepljene v eno celoto – ena pod drugo. Upoštevajte, da so stolpci s količino blaga v teh tabelah padli drug pod drugega, ker. imata isto ime:
Če vam je prvotno zaporedje vrstic v prodajni tabeli pomembno, potem, da ga lahko po vseh nadaljnjih transformacijah obnovite, dodajte oštevilčen stolpec v našo tabelo z ukazom Dodajanje stolpca – indeksni stolpec (Dodaj stolpec — stolpec Indeks). Če vam zaporedje vrstic ni pomembno, lahko ta korak preskočite.
Zdaj jo s spustnim seznamom v glavi tabele razvrstite po stolpcu Količina Naraščajoče:
In glavni trik: desni klik na glavo stolpca Popust izberite ekipo Polnjenje – dol (Izpolni — dol). Prazne celice z null samodejno izpolnjene s prejšnjimi vrednostmi popusta:
Ostaja obnoviti prvotno zaporedje vrstic z razvrščanjem po stolpcu Kazalo (pozneje ga lahko varno izbrišete) in se s filtrom znebite nepotrebnih vrstic null po stolpcu Koda transakcije: