Að finna næsta númer

Efnisyfirlit

Í reynd eru mjög oft tilvik þar sem þú og ég þurfum að finna næsta gildi í mengi (töflu) í tengslum við tiltekna tölu. Það gæti til dæmis verið:

Útreikningur afsláttar eftir magni.
Útreikningur á upphæð bónusa eftir framkvæmd áætlunarinnar.
Útreikningur á sendingargjöldum eftir fjarlægð.
Val á hentugum gámum fyrir vörur o.fl.

Þar að auki getur verið þörf á námundun bæði upp og niður, allt eftir aðstæðum.

Það eru nokkrar leiðir - augljósar og ekki svo augljósar - til að leysa slíkt vandamál. Við skulum skoða þau í röð.

Til að byrja með skulum við ímynda okkur birgi sem gefur afslátt í heildsölu og hlutfall afsláttarins fer eftir magni vöru sem keypt er. Til dæmis, þegar keypt er meira en 5 stykki, er veittur 2% afsláttur og þegar keypt er frá 20 stykki - nú þegar 6% o.s.frv.

Hvernig á að reikna út afsláttarprósentuna fljótt og fallega þegar magn keyptra vara er slegið inn?

Aðferð 1: Hreiður IF

Aðferð úr seríunni „hvað er að hugsa – þú þarft að hoppa!“. Að nota hreiður aðgerðir IF (EF) til að athuga í röð hvort hólfagildið falli inn í hvert tímabil og sýna afslátt fyrir samsvarandi svið. En formúlan í þessu tilfelli getur reynst mjög fyrirferðarmikil:

Að finna næsta númer

Ég held að það sé augljóst að það er gaman að kemba svona „skrímsladúkku“ eða reyna að bæta nokkrum nýjum skilyrðum við hana eftir nokkurn tíma.

Að auki hefur Microsoft Excel hreiðurmörk fyrir IF-aðgerðina - 7 sinnum í eldri útgáfum og 64 sinnum í nýrri útgáfum. Hvað ef þú þarft meira?

Aðferð 2. VLOOKUP með interval view

Þessi aðferð er miklu þéttari. Til að reikna út afsláttarprósentu, notaðu goðsagnakennda aðgerðina VPR (SKRÁNING) í áætlaðri leitarham:

Að finna næsta númer

þar sem

B4 – verðmæti vörumagnsins í fyrstu viðskiptunum sem við erum að leita eftir afslátt fyrir
$G$4:$H$8 – hlekkur á afsláttartöfluna – án „haus“ og með heimilisföngin fest með $ tákninu.
2 — raðtala dálksins í afsláttartöflunni sem við viljum fá afsláttargildið úr
SATT – það er þar sem „hundurinn“ er grafinn. If sem síðasta fall rök VPR tilgreina LJÚGA (RANGT) eða 0, þá mun aðgerðin leita að strangur samsvörun í magndálknum (og í okkar tilviki mun það gefa #N/A villu, þar sem það er ekkert gildi 49 í afsláttartöflunni). En ef í staðinn LJÚGA skrifa SATT (SATT) eða 1, þá mun aðgerðin ekki leita að nákvæmlega, heldur næst minnstu verðmæti og mun gefa okkur þá prósentu af afslátt sem við þurfum.

Gallinn við þessa aðferð er nauðsyn þess að raða afsláttartöflunni í hækkandi röð eftir fyrsta dálknum. Ef það er engin slík flokkun (eða það er gert í öfugri röð), þá mun formúlan okkar ekki virka:

Að finna næsta númer

Samkvæmt því er aðeins hægt að nota þessa aðferð til að finna næsta minnsta gildi. Ef þú þarft að finna næsta stærsta, þá verður þú að nota aðra nálgun.

Aðferð 3. Að finna næsta stærsta með því að nota INDEX og MATCH föllin

Nú skulum við líta á vandamál okkar frá hinni hliðinni. Segjum að við seljum nokkrar gerðir af iðnaðardælum með mismunandi getu. Sölutaflan til vinstri sýnir það afl sem viðskiptavinurinn þarfnast. Við þurfum að velja dælu með næsta hámarks- eða jöfnu afli, en ekki minna en það sem verkefnið krefst.

VLOOKUP aðgerðin mun ekki hjálpa hér, svo þú verður að nota hliðstæðu hennar - fullt af INDEX aðgerðum (VÍSITALA) og FLEIRI (MATCH):

Að finna næsta númer

Hér virkar MATCH fallið með síðustu röksemdinni -1 á þann hátt að finna næsta stærsta gildi, og INDEX fallið dregur síðan líkanheitið sem við þurfum úr aðliggjandi dálki.

Aðferð 4. Ný aðgerð VIEW (XLOOKUP)

Ef þú ert með útgáfu af Office 365 með allar uppfærslur uppsettar, þá í staðinn fyrir VLOOKUP (SKRÁNING) þú getur notað hliðstæðu þess - VIEW aðgerðina (XLOOKUP), sem ég hef þegar greint ítarlega:

Að finna næsta númer

hér:

B4 – upphafsvirði þess magns vörunnar sem við erum að leita eftir afslátt fyrir
$G$4:$G$8 – svið þar sem við erum að leita að samsvörun
$H$4:$H$8 – úrval niðurstaðna sem þú vilt skila afsláttinum frá
fjórða rök (-1) inniheldur leitina að næstu minnstu tölu sem við viljum í stað nákvæmrar samsvörunar.

Kostir þessarar aðferðar eru þeir að það er engin þörf á að flokka afsláttartöfluna og getu til að leita, ef nauðsyn krefur, ekki aðeins næsta minnsta, heldur einnig næsta stærsta gildi. Síðustu rökin í þessu máli verða 1.

En því miður eru ekki allir með þennan eiginleika ennþá - aðeins ánægðir eigendur Office 365.

Aðferð 5. Power Query

Ef þú ert ekki enn kunnugur kraftmiklu og algjörlega ókeypis Power Query viðbótinni fyrir Excel, þá ertu hér. Ef þú ert nú þegar kunnugur, þá skulum við reyna að nota það til að leysa vandamál okkar.

Við skulum gera smá undirbúningsvinnu fyrst:

Við skulum breyta upprunatöflunum okkar í kraftmikla (snjall) með því að nota flýtilykla Ctrl+T eða lið Heim - Snið sem töflu (Heima - Snið sem töflu).
Til glöggvunar skulum við gefa þeim nöfn. Sala и Afslættir flipi Framkvæmdaaðili (Hönnun).
Hlaðið hverri töflu fyrir sig í Power Query með því að nota hnappinn Frá borði/sviði flipi Gögn (Gögn — úr töflu/sviði). Í nýlegum útgáfum af Excel hefur þessum hnappi verið breytt í Með laufblöðum (Úr blaði).
Ef töflurnar hafa mismunandi dálkaheiti með magni, eins og í dæminu okkar ("Magn vöru" og "Magn frá ..."), þá verður að endurnefna þær í Power Query og heita það sama.
Eftir það geturðu farið aftur í Excel með því að velja skipunina í Power Query ritstjóraglugganum Heim — Loka og hlaða — Loka og hlaða inn... (Heima — Loka&hlaða — Loka&hlaða til...) og svo valmöguleiki Búðu bara til tengingu (Búa bara til tengingu).
Þá byrjar það áhugaverðasta. Ef þú hefur reynslu af Power Query, þá geri ég ráð fyrir að frekari hugsun ætti að vera í þá átt að sameina þessar tvær töflur með sameiningu (sameina) a la VLOOKUP, eins og var í fyrri aðferð. Reyndar þurfum við að sameinast í viðbótarham, sem er alls ekki augljóst við fyrstu sýn. Veldu í Excel flipanum Gögn - Fá gögn - Sameina beiðnir - Bæta við (Gögn - Fá gögn - Sameina fyrirspurnir - Bæta við) og svo borðin okkar Sala и Afslættir í glugganum sem birtist:
Eftir að smella á OK Borðin okkar verða límd í eina heild – undir hvert annað. Vinsamlegast athugaðu að dálkarnir með vörumagninu í þessum töflum féllu hver undir annan, vegna þess að. þeir heita sama nafni:
Ef upprunalega röð raða í sölutöflunni er mikilvæg fyrir þig, svo að þú getir endurheimt hana eftir allar síðari umbreytingar skaltu bæta númeruðum dálki við töfluna okkar með skipuninni Að bæta við dálki - Vísitala dálki (Bæta við dálki - Vísindadálki). Ef röð lína skiptir þig ekki máli, þá geturðu sleppt þessu skrefi.
Notaðu nú fellilistann í haus töflunnar og flokkaðu hana eftir dálki magn Hækkandi:
Og aðal bragðið: hægrismelltu á dálkhausinn Afsláttur velja lið Fylla - Niður (Fylla - Niður). Tómar frumur með null sjálfkrafa fyllt út með fyrri afsláttargildum:
Það er eftir að endurheimta upprunalegu röð raða með því að raða eftir dálkum Index (þú getur örugglega eytt því seinna) og losaðu þig við óþarfa línur með síu null eftir dálki Færslukóði: