Innhold
I praksis er det veldig ofte tilfeller der du og jeg trenger å finne den nærmeste verdien i et sett (tabell) i forhold til et gitt tall. Det kan for eksempel være:
- Beregning av rabatt avhengig av volum.
- Beregning av mengden bonuser avhengig av gjennomføringen av planen.
- Beregning av fraktpriser avhengig av avstand.
- Valg av egnede beholdere for varer mv.
I tillegg kan avrunding være nødvendig både opp og ned, avhengig av situasjonen.
Det er flere måter – åpenbare og ikke så opplagte – å løse et slikt problem. La oss se på dem sekvensielt.
Til å begynne med, la oss forestille oss en leverandør som gir rabatter på engros, og prosentandelen av rabatten avhenger av mengden varer som kjøpes. For eksempel, når du kjøper mer enn 5 stykker, gis 2% rabatt, og ved kjøp fra 20 stykker - allerede 6%, etc.
Hvordan beregne rabattprosenten raskt og vakkert når du legger inn mengden av de kjøpte varene?
Metode 1: Nestede IF-er
En metode fra serien «hva er det å tenke – du må hoppe!». Bruke nestede funksjoner IF (HVIS) for å sjekke sekvensielt om celleverdien faller inn i hvert av intervallene og vise en rabatt for det tilsvarende området. Men formelen i dette tilfellet kan vise seg å være veldig tungvint:
Jeg synes det er åpenbart at det å feilsøke en slik "monsterdukke" eller prøve å legge til et par nye forhold til den etter en stund er gøy.
I tillegg har Microsoft Excel en hekkegrense for IF-funksjonen – 7 ganger i eldre versjoner og 64 ganger i nyere versjoner. Hva om du trenger mer?
Metode 2. VLOOKUP med intervallvisning
Denne metoden er mye mer kompakt. For å beregne rabattprosenten, bruk den legendariske funksjonen VPR (VISNING) i omtrentlig søkemodus:
hvor
- B4 – verdien av mengden varer i den første transaksjonen som vi ser etter rabatt for
- $G$4:$H$8 – en lenke til rabatttabellen – uten "header" og med adressene festet med $-tegnet.
- 2 — ordensnummeret til kolonnen i rabatttabellen som vi ønsker å få rabattverdien fra
- TRUE – det er her «hunden» ligger begravet. If som siste funksjonsargument VPR spesifisere LØVER (FALSK) eller 0, vil funksjonen se etter streng match i mengdekolonnen (og i vårt tilfelle vil det gi en #N/A feil, siden det ikke er noen verdi 49 i rabatttabellen). Men hvis i stedet LØVER skrive TRUE (EKTE) eller 1, så vil funksjonen ikke se etter den eksakte, men nærmeste minste verdi og vil gi oss prosentandelen av rabatten vi trenger.
Ulempen med denne metoden er behovet for å sortere rabatttabellen i stigende rekkefølge etter den første kolonnen. Hvis det ikke er slik sortering (eller det gjøres i omvendt rekkefølge), vil ikke formelen vår fungere:
Følgelig kan denne tilnærmingen bare brukes til å finne den nærmeste minste verdien. Hvis du trenger å finne den nærmeste største, må du bruke en annen tilnærming.
Metode 3. Finne den nærmeste største ved å bruke funksjonene INDEX og MATCH
La oss nå se på problemet vårt fra den andre siden. Anta at vi selger flere modeller av industrielle pumper med ulik kapasitet. Salgstabellen til venstre viser kraften som kreves av kunden. Vi må velge en pumpe med nærmeste maksimale eller lik effekt, men ikke mindre enn det som kreves av prosjektet.
VLOOKUP-funksjonen vil ikke hjelpe her, så du må bruke dens analoge - en haug med INDEX-funksjoner (INDEKS) og MER UTSATT (KAMP):
Her fungerer MATCH-funksjonen med det siste argumentet -1 i modusen for å finne den nærmeste største verdien, og INDEX-funksjonen trekker deretter ut modellnavnet vi trenger fra den tilstøtende kolonnen.
Metode 4. Ny funksjon VIEW (XLOOKUP)
Hvis du har en versjon av Office 365 med alle oppdateringer installert, så i stedet for VLOOKUP (VISNING) du kan bruke dens analoge – VIEW-funksjonen (XLOOKUP), som jeg allerede har analysert i detalj:
Her:
- B4 – startverdien av mengden av produktet som vi ser etter rabatt for
- $G$4:$G$8 – utvalget der vi ser etter kamper
- $H$4:$H$8 – utvalget av resultater du ønsker å returnere rabatten fra
- fjerde argument (-1) inkluderer søket etter det nærmeste minste tallet vi ønsker i stedet for et eksakt samsvar.
Fordelene med denne metoden er at det ikke er behov for å sortere rabatttabellen og muligheten til å søke, om nødvendig, ikke bare den nærmeste minste, men også den nærmeste største verdien. Det siste argumentet i denne saken vil være 1.
Men dessverre har ikke alle denne funksjonen ennå - bare glade eiere av Office 365.
Metode 5. Power Query
Hvis du ennå ikke er kjent med det kraftige og helt gratis Power Query-tillegget for Excel, så er du her. Hvis du allerede er kjent, så la oss prøve å bruke det til å løse problemet vårt.
La oss gjøre litt forberedende arbeid først:
- La oss konvertere kildetabellene våre til dynamiske (smarte) ved hjelp av en hurtigtast Ctrl+T eller team Hjem – Formater som en tabell (Hjem — Formater som tabell).
- For klarhetens skyld, la oss gi dem navn. Salg и Rabatter tab Constructor (Design).
- Last inn hver av tabellene etter tur til Power Query ved å bruke knappen Fra Tabell/Rekkevidde tab Data (Data – Fra tabell/område). I nyere versjoner av Excel har denne knappen fått nytt navn til Med blader (Fra ark).
- Hvis tabellene har forskjellige kolonnenavn med mengder, som i vårt eksempel ("Antall varer" og "Antall fra ..."), må de gis nytt navn i Power Query og gi samme navn.
- Etter det kan du gå tilbake til Excel ved å velge kommandoen i Power Query-redigeringsvinduet Hjem — Lukk og last — Lukk og last inn... (Hjem — Lukk&last — Lukk&last til...) og deretter alternativ Bare opprette en forbindelse (Bare opprette forbindelse).
- Så begynner det mest interessante. Hvis du har erfaring med Power Query, så antar jeg at den videre tankegangen bør gå i retning av å slå sammen disse to tabellene med en join-query (merge) a la VLOOKUP, slik tilfellet var i forrige metode. Faktisk må vi slå sammen i tilleggsmodus, noe som slett ikke er åpenbart ved første øyekast. Velg i Excel-fanen Data – Hent data – Kombiner forespørsler – Legg til (Data – Hent data – Kombiner spørringer – Legg til) og så våre bord Salg и Rabatter i vinduet som vises:
- Etter å ha klikket på OK bordene våre limes til én helhet – under hverandre. Vær oppmerksom på at kolonnene med varemengden i disse tabellene falt under hverandre, fordi. de har samme navn:
- Hvis den opprinnelige rekkefølgen av rader i salgstabellen er viktig for deg, så, slik at du etter alle påfølgende transformasjoner kan gjenopprette den, legg til en nummerert kolonne i tabellen vår ved å bruke kommandoen Legge til en kolonne – Indekskolonne (Legg til kolonne — Indekskolonne). Hvis rekkefølgen av linjer ikke betyr noe for deg, kan du hoppe over dette trinnet.
- Bruk nå rullegardinlisten i overskriften til tabellen og sorter den etter kolonne Antall Stigende:
- Og hovedtrikset: høyreklikk på kolonneoverskriften Rabatt velge et lag Fyll – ned (Fyll - ned). Tomme celler med null automatisk fylt ut med tidligere rabattverdier:
- Det gjenstår å gjenopprette den opprinnelige rekkefølgen av rader ved å sortere etter kolonne Index (du kan trygt slette det senere) og bli kvitt unødvendige linjer med et filter null etter kolonne Transaksjons kode:
- Bruke VLOOKUP-funksjonen til å søke og slå opp data
- Bruk av VLOOKUP (VLOOKUP) skiller mellom store og små bokstaver
- XNUMXD VLOOKUP (VLOOKUP)