Find det nærmeste nummer – Sund mad i nærheden af mig

Indhold

I praksis er der meget ofte tilfælde, hvor du og jeg skal finde den nærmeste værdi i et sæt (tabel) i forhold til et givet tal. Det kan for eksempel være:

Beregning af rabat afhængig af volumen.
Beregning af mængden af bonusser afhængigt af planens gennemførelse.
Beregning af fragtpriser afhængig af afstanden.
Udvælgelse af egnede beholdere til varer mv.

Desuden kan afrunding være påkrævet både op og ned, afhængigt af situationen.

Der er flere måder – indlysende og ikke så indlysende – at løse sådan et problem. Lad os se på dem i rækkefølge.

Til at begynde med, lad os forestille os en leverandør, der giver rabatter på engrossalg, og procentdelen af rabatten afhænger af mængden af købte varer. Fx ved køb af mere end 5 styk gives 2% rabat, og ved køb fra 20 styk – allerede 6% mv.

Hvordan beregner man hurtigt og smukt rabatprocenten, når man indtaster mængden af de købte varer?

Metode 1: Indlejrede IF'er

En metode fra serien “hvad er der at tænke – du skal hoppe!”. Brug af indlejrede funktioner IF (HVIS) for sekventielt at kontrollere, om celleværdien falder inden for hvert af intervallerne, og vise en rabat for det tilsvarende interval. Men formlen i dette tilfælde kan vise sig at være meget besværlig:

At finde det nærmeste nummer

Jeg synes, det er indlysende, at det er sjovt at fejlsøge sådan en "monsterdukke" eller at forsøge at tilføje et par nye betingelser til den efter nogen tid.

Derudover har Microsoft Excel en indlejringsgrænse for IF-funktionen – 7 gange i ældre versioner og 64 gange i nyere versioner. Hvad hvis du har brug for mere?

Metode 2. VLOOKUP med intervalvisning

Denne metode er meget mere kompakt. For at beregne rabatprocenten skal du bruge den legendariske funktion VPR (OPLYSNING) i omtrentlig søgetilstand:

At finde det nærmeste nummer

hvor

B4 – værdien af mængden af varer i den første transaktion, som vi leder efter en rabat på
$G$4:$H$8 – et link til rabattabellen – uden "header" og med adresserne fastgjort med $-tegnet.
2 — ordensnummeret på kolonnen i rabattabellen, hvorfra vi ønsker at få rabatværdien
TRUE – det er her "hunden" ligger begravet. If som det sidste funktionsargument VPR specificere AT LYVE (FALSK) eller 0, så leder funktionen efter strikt match i mængdekolonnen (og i vores tilfælde vil det give en #N/A fejl, da der ikke er nogen værdi 49 i rabattabellen). Men hvis i stedet AT LYVE skriver TRUE (SAND) eller 1, så vil funktionen ikke lede efter det nøjagtige, men nærmeste mindste værdi og vil give os den procentdel af rabat, vi har brug for.

Ulempen ved denne metode er behovet for at sortere rabattabellen i stigende rækkefølge efter den første kolonne. Hvis der ikke er en sådan sortering (eller det sker i omvendt rækkefølge), vil vores formel ikke fungere:

At finde det nærmeste nummer

Derfor kan denne tilgang kun bruges til at finde den nærmeste mindste værdi. Skal du finde den nærmeste største, så skal du bruge en anden tilgang.

Metode 3. Find den nærmeste største ved hjælp af funktionerne INDEX og MATCH

Lad os nu se på vores problem fra den anden side. Antag, at vi sælger flere modeller af industrielle pumper med forskellige kapaciteter. Salgstabellen til venstre viser den effekt, som kunden har brug for. Vi skal vælge en pumpe med den nærmeste maksimale eller samme effekt, men ikke mindre end det, der kræves af projektet.

VLOOKUP-funktionen hjælper ikke her, så du bliver nødt til at bruge dens analoge - en masse INDEX-funktioner (INDEKS) og MERE UDSAT (MATCH):

At finde det nærmeste nummer

Her fungerer MATCH-funktionen med det sidste argument -1 i tilstanden til at finde den nærmeste største værdi, og INDEX-funktionen udtrækker så det modelnavn, vi skal bruge, fra den tilstødende kolonne.

Metode 4. Ny funktion VIEW (XLOOKUP)

Hvis du har en version af Office 365 med alle opdateringer installeret, så i stedet for VLOOKUP (OPLYSNING) du kan bruge dens analoge – VIEW-funktionen (XLOOKUP), som jeg allerede har analyseret i detaljer:

At finde det nærmeste nummer

Her:

B4 – startværdien af mængden af produktet, som vi leder efter rabat på
$G$4:$G$8 – sortimentet, hvor vi leder efter kampe
$H$4:$H$8 – rækken af resultater, som du ønsker at returnere rabatten fra
fjerde argument (-1) inkluderer søgningen efter det nærmeste mindste tal, som vi ønsker i stedet for et nøjagtigt match.

Fordelene ved denne metode er, at der ikke er behov for at sortere rabattabellen og muligheden for at søge, om nødvendigt, ikke kun den nærmeste mindste, men også den nærmeste største værdi. Det sidste argument i denne sag vil være 1.

Men desværre er det ikke alle, der har denne funktion endnu - kun glade ejere af Office 365.

Metode 5. Power Query

Hvis du endnu ikke er bekendt med det kraftfulde og helt gratis Power Query-tilføjelsesprogram til Excel, så er du her. Hvis du allerede er bekendt, så lad os prøve at bruge det til at løse vores problem.

Lad os først lave noget forberedende arbejde:

Lad os konvertere vores kildetabeller til dynamiske (smarte) ved hjælp af en tastaturgenvej Ctrl+T eller hold Hjem – Formater som en tabel (Hjem — Formater som tabel).
For klarhedens skyld, lad os give dem navne. Salg и Rabatter fanen Constructor (Design).
Indlæs hver af tabellerne på skift i Power Query ved hjælp af knappen Fra tabel/område fanen data (Data — Fra tabel/område). I de seneste versioner af Excel er denne knap blevet omdøbt til Med blade (Fra ark).
Hvis tabellerne har forskellige kolonnenavne med mængder, som i vores eksempel ("Mængde af varer" og "Mængde fra ..."), så skal de omdøbes i Power Query og navngives det samme.
Derefter kan du vende tilbage til Excel ved at vælge kommandoen i Power Query-editorvinduet Hjem — Luk og indlæs — Luk og indlæs... (Hjem — Luk&indlæs — Luk&indlæs til...) og derefter mulighed Bare opret en forbindelse (Opret kun forbindelse).
Så begynder det mest interessante. Hvis du har erfaring med Power Query, så går jeg ud fra, at den videre tankegang bør gå i retning af at fusionere disse to tabeller med en join-forespørgsel (merge) a la VLOOKUP, som det var tilfældet i den forrige metode. Faktisk bliver vi nødt til at fusionere i tilføjelsestilstand, hvilket slet ikke er indlysende ved første øjekast. Vælg i Excel-fanen Data – Hent data – Kombiner anmodninger – Tilføj (Data — Hent data — Kombiner forespørgsler — Tilføj) og så vores borde Salg и Rabatter i vinduet der kommer frem:
Efter at have klikket på OK vores borde bliver limet til en helhed – under hinanden. Bemærk venligst, at kolonnerne med varemængden i disse tabeller faldt under hinanden, pga. de har samme navn:
Hvis den oprindelige rækkefølge af rækker i salgstabellen er vigtig for dig, så for at du efter alle efterfølgende transformationer kan gendanne den, skal du tilføje en nummereret kolonne til vores tabel ved hjælp af kommandoen Tilføjelse af en kolonne – Indekskolonne (Tilføj kolonne — Indekskolonne). Hvis rækkefølgen af linjer ikke betyder noget for dig, kan du springe dette trin over.
Brug nu rullelisten i tabellens overskrift til at sortere den efter kolonne Antal Stigende:
Og det vigtigste trick: højreklik på kolonneoverskriften Rabat vælge et hold Fyld – ned (Fyld - ned). Tomme celler med null automatisk udfyldt med de tidligere rabatværdier:
Det er tilbage at gendanne den oprindelige rækkefølge ved at sortere efter kolonne Indeks (du kan roligt slette det senere) og slippe af med unødvendige linjer med et filter null efter kolonne Transaktionskode: