Soek die naaste nommer – Gesonde kos naby my

Contents [show]

In die praktyk is daar baie dikwels gevalle waar ek en jy die naaste waarde in 'n stel (tabel) in verhouding tot 'n gegewe getal moet vind. Dit kan byvoorbeeld wees:

Berekening van afslag afhangende van volume.
Berekening van die bedrag van bonusse afhangende van die implementering van die plan.
Berekening van versendingstariewe afhangend van die afstand.
Seleksie van geskikte houers vir goedere, ens.

Daarbenewens kan afronding beide op en af vereis word, afhangende van die situasie.

Daar is verskeie maniere – voor die hand liggend en nie so voor die hand liggend nie – om so 'n probleem op te los. Kom ons kyk opeenvolgend na hulle.

Om mee te begin, stel ons 'n verskaffer voor wat afslag op groothandel gee, en die persentasie van die afslag hang af van die hoeveelheid goedere wat gekoop word. Byvoorbeeld, wanneer meer as 5 stukke gekoop word, word 'n 2% afslag gegee, en by aankoop van 20 stukke - reeds 6%, ens.

Hoe om die afslagpersentasie vinnig en pragtig te bereken wanneer die hoeveelheid gekoopte goedere ingevoer word?

Metode 1: Geneste IF's

’n Metode uit die reeks “wat is daar om te dink – jy moet spring!”. Gebruik geneste funksies IF (AS) om opeenvolgend te kyk of die selwaarde in elk van die intervalle val en 'n afslag vir die ooreenstemmende reeks te vertoon. Maar die formule in hierdie geval kan baie omslagtig blyk te wees:

Vind die naaste nommer

Ek dink dit is duidelik dat dit pret is om so 'n "monsterpop" te ontfout of 'n paar nuwe voorwaardes daarby te probeer voeg.

Daarbenewens het Microsoft Excel 'n neslimiet vir die IF-funksie – 7 keer in ouer weergawes en 64 keer in nuwer weergawes. Wat as jy meer nodig het?

Metode 2. VLOOKUP met intervalaansig

Hierdie metode is baie meer kompak. Om die afslagpersentasie te bereken, gebruik die legendariese funksie VPR (VERSOEKING) in benaderde soekmodus:

Vind die naaste nommer

waar

B4 – die waarde van die hoeveelheid goedere in die eerste transaksie waarvoor ons afslag soek
$G$4:$H$8 – ’n skakel na die afslagtabel – sonder ’n “header” en met die adresse vas met die $-teken.
2 — die rangnommer van die kolom in die afslagtabel waaruit ons die afslagwaarde wil kry
WAAR – dit is waar die “hond” begrawe is. As as die laaste funksie argument VPR spesifiseer LIEG (ONWAAR) of 0, dan sal die funksie soek streng wedstryd in die hoeveelheid kolom (en in ons geval sal dit 'n #N/A fout gee, aangesien daar geen waarde 49 in die afslagtabel is nie). Maar as in plaas daarvan LIEG skryf WAAR (WAAR) of 1, dan sal die funksie nie vir die presiese kyk nie, maar naaste kleinste waarde en sal ons die persentasie afslag gee wat ons benodig.

Die nadeel van hierdie metode is die behoefte om die afslagtabel in stygende volgorde volgens die eerste kolom te sorteer. As daar nie so 'n sortering is nie (of dit word in omgekeerde volgorde gedoen), sal ons formule nie werk nie:

Vind die naaste nommer

Gevolglik kan hierdie benadering slegs gebruik word om die naaste kleinste waarde te vind. As jy die naaste grootste moet vind, moet jy 'n ander benadering gebruik.

Metode 3. Vind die naaste grootste deur die INDEX- en MATCH-funksies te gebruik

Kom ons kyk nou na ons probleem van die ander kant af. Gestel ons verkoop verskeie modelle van industriële pompe van verskillende kapasiteite. Die verkoopstabel aan die linkerkant toon die krag wat die kliënt benodig. Ons moet 'n pomp kies met die naaste maksimum of gelyke krag, maar nie minder as wat deur die projek vereis word nie.

Die VLOOKUP-funksie sal nie hier help nie, so jy sal sy analoog moet gebruik - 'n klomp INDEX-funksies (INDEKS) en MEER BLOOTSTELLING (MATCH):

Vind die naaste nommer

Hier werk die MATCH-funksie met die laaste argument -1 in die modus om die naaste grootste waarde te vind, en die INDEX-funksie onttrek dan die modelnaam wat ons benodig uit die aangrensende kolom.

Metode 4. Nuwe funksie VIEW (XLOOKUP)

As jy 'n weergawe van Office 365 het met alle opdaterings geïnstalleer, dan in plaas van VLOOKUP (VERSOEKING) jy kan sy analoog gebruik – die VIEW-funksie (XLOOKUP), wat ek reeds in detail ontleed het:

Vind die naaste nommer

hier:

B4 – die aanvanklike waarde van die hoeveelheid van die produk waarvoor ons afslag soek
$G$4:$G$8 – die reeks waar ons pasmaats soek
$H$4:$H$8 – die reeks resultate waaruit u die afslag wil teruggee
vierde argument (-1) sluit die soektog na die naaste kleinste getal wat ons wil hê in plaas van 'n presiese passing in.

Die voordele van hierdie metode is dat dit nie nodig is om die afslagtabel te sorteer nie en die vermoë om, indien nodig, nie net die naaste kleinste nie, maar ook die naaste grootste waarde te soek. Die laaste argument in hierdie geval sal 1 wees.

Maar ongelukkig het almal nog nie hierdie kenmerk nie – net gelukkige eienaars van Office 365.

Metode 5. Kragnavraag

As jy nog nie vertroud is met die kragtige en heeltemal gratis Power Query-byvoeging vir Excel nie, dan is jy hier. As jy reeds vertroud is, kom ons probeer dit gebruik om ons probleem op te los.

Kom ons doen eers 'n paar voorbereidingswerk:

Kom ons skakel ons brontabelle om na dinamiese (slim) met behulp van 'n sleutelbordkortpad Ctrl+T of span Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel).
Vir duidelikheid, kom ons gee hulle name. Verkope и Afslag tab konstruktor (Ontwerp).
Laai elkeen van die tabelle om die beurt in Power Query deur die knoppie te gebruik Van tabel/reeks tab data (Data — Van tabel/reeks). In onlangse weergawes van Excel is hierdie knoppie hernoem na Met blare (Van blad).
As die tabelle verskillende kolomname met hoeveelhede het, soos in ons voorbeeld ("Hoeveelheid goedere" en "Hoeveelheid vanaf ..."), moet hulle in Power Query hernoem word en dieselfde genoem word.
Daarna kan u terugkeer na Excel deur die opdrag in die Power Query-redigeerdervenster te kies Tuis — Maak toe en laai — Maak toe en laai in... (Tuis — Maak toe en laai — Maak toe en laai na...) en dan opsie Skep net 'n verbinding (Skep net verbinding).
Dan begin die interessantste. As jy ondervinding het in Power Query, dan neem ek aan dat die verdere gedagtegang in die rigting moet wees om hierdie twee tabelle saam te voeg met 'n aansluitingsnavraag (samevoeging) a la VLOOKUP, soos die geval was in die vorige metode. Trouens, ons sal moet saamsmelt in die byvoegmodus, wat glad nie met die eerste oogopslag duidelik is nie. Kies in Excel-oortjie Data – Kry Data – Kombineer versoeke – Voeg by (Data – Kry Data – Kombineer navrae – Voeg by) en dan ons tafels Verkope и Afslag in die venster wat verskyn:
Na kliek op OK ons tafels sal in 'n enkele geheel vasgeplak word – onder mekaar. Neem asseblief kennis dat die kolomme met die hoeveelheid goedere in hierdie tabelle onder mekaar geval het, want. hulle het dieselfde naam:
As die oorspronklike volgorde van rye in die verkoopstabel vir jou belangrik is, voeg dan 'n genommerde kolom by ons tabel met die opdrag sodat jy dit na alle daaropvolgende transformasies kan herstel. Voeg 'n kolom by - Indekskolom (Voeg kolom by - Indekskolom). As die volgorde van lyne nie vir jou saak maak nie, kan jy hierdie stap oorslaan.
Gebruik nou die aftreklys in die kop van die tabel en sorteer dit volgens kolom Hoeveelheid Stygend:
En die belangrikste truuk: regskliek op die kolomkop Korting kies 'n span Vul - Af (Vul - af). Leë selle met nul outomaties ingevul met die vorige afslagwaardes:
Dit bly om die oorspronklike volgorde van rye te herstel deur volgens kolom te sorteer indeks (jy kan dit later veilig uitvee) en ontslae raak van onnodige lyne met 'n filter nul per kolom Transaksiekode: