Inhoud
In de praktijk zijn er heel vaak gevallen waarin jij en ik de dichtstbijzijnde waarde in een set (tabel) moeten vinden in relatie tot een bepaald getal. Het kan bijvoorbeeld zijn:
- Berekening van korting afhankelijk van volume.
- Berekening van het bedrag van de bonussen afhankelijk van de uitvoering van het plan.
- Berekening van de verzendkosten afhankelijk van de afstand.
- Selectie van geschikte containers voor goederen, enz.
Bovendien kan, afhankelijk van de situatie, afronding zowel naar boven als naar beneden nodig zijn.
Er zijn verschillende manieren – voor de hand liggend en niet zo voor de hand liggend – om een dergelijk probleem op te lossen. Laten we ze achtereenvolgens bekijken.
Laten we ons om te beginnen een leverancier voorstellen die kortingen geeft op groothandel, en het percentage van de korting hangt af van de hoeveelheid gekochte goederen. Bij afname van bijvoorbeeld meer dan 5 stuks wordt 2% korting gegeven, bij afname vanaf 20 stuks al 6% etc.
Hoe berekent u snel en mooi het kortingspercentage bij het invoeren van de hoeveelheid gekochte goederen?
Methode 1: Geneste IF's
Een methode uit de serie “wat valt er te denken – je moet springen!”. Geneste functies gebruiken IF (ALS) om achtereenvolgens te controleren of de celwaarde in elk van de intervallen valt en een korting weer te geven voor het overeenkomstige bereik. Maar de formule kan in dit geval erg omslachtig blijken te zijn:
Ik denk dat het duidelijk is dat het leuk is om zo'n "monsterpop" te debuggen of er na een tijdje een paar nieuwe voorwaarden aan toe te voegen.
Bovendien heeft Microsoft Excel een nestlimiet voor de ALS-functie - 7 keer in oudere versies en 64 keer in nieuwere versies. Wat als je meer nodig hebt?
Methode 2. VERT.ZOEKEN met intervalweergave
Deze methode is veel compacter. Gebruik de legendarische functie om het kortingspercentage te berekenen VPR (VERT.ZOEKEN) in zoekmodus bij benadering:
WAAR
- B4 – de waarde van de hoeveelheid goederen in de eerste transactie waarvoor we een korting zoeken
- $G$4:$H$8 – een link naar de kortingstabel – zonder “header” en met de adressen vast met het $ teken.
- 2 — het volgnummer van de kolom in de kortingstabel waarvan we de kortingswaarde willen krijgen
- TRUE – hier ligt de “hond” begraven. Als als laatste functieargument VPR specificeren LIEGEN (ONWAAR) of 0, dan zal de functie zoeken naar strikte overeenkomst in de hoeveelheidkolom (en in ons geval geeft het een #N/A-fout, aangezien er geen waarde 49 in de kortingstabel staat). Maar als in plaats daarvan LIEGEN schrijven TRUE (WAAR) of 1, dan zoekt de functie niet naar de exacte, maar dichtstbijzijnde kleinste waarde en geeft ons het kortingspercentage dat we nodig hebben.
Het nadeel van deze methode is de noodzaak om de kortingstabel in oplopende volgorde te sorteren op de eerste kolom. Als zo'n sortering niet bestaat (of in omgekeerde volgorde wordt gedaan), werkt onze formule niet:
Dienovereenkomstig kan deze benadering alleen worden gebruikt om de dichtstbijzijnde kleinste waarde te vinden. Als u de dichtstbijzijnde grootste wilt vinden, moet u een andere benadering gebruiken.
Methode 3. De dichtstbijzijnde grootste vinden met behulp van de INDEX- en MATCH-functies
Laten we ons probleem nu eens van de andere kant bekijken. Stel dat we verschillende modellen industriële pompen met verschillende capaciteiten verkopen. De verkooptabel aan de linkerkant toont het vermogen dat de klant nodig heeft. We moeten een pomp selecteren met het dichtstbijzijnde maximale of gelijke vermogen, maar niet minder dan wat het project vereist.
De VERT.ZOEKEN-functie zal hier niet helpen, dus je zult de analoge moeten gebruiken - een heleboel INDEX-functies (INHOUDSOPGAVE) en MEER BLOOTGESTELD (WEDSTRIJD):
Hier werkt de MATCH-functie met het laatste argument -1 in de modus om de dichtstbijzijnde grootste waarde te vinden, en de INDEX-functie extraheert vervolgens de modelnaam die we nodig hebben uit de aangrenzende kolom.
Methode 4. Nieuwe functie VIEW (XLOOKUP)
Als je een versie van Office 365 hebt waarop alle updates zijn geïnstalleerd, dan in plaats van VERT.ZOEKEN (VERT.ZOEKEN) je kunt de analoge gebruiken - de VIEW-functie (XZOEKEN), die ik al in detail heb geanalyseerd:
Hier:
- B4 – de initiële waarde van de hoeveelheid van het product waarvoor we een korting zoeken
- $G$4:$G$8 – het assortiment waar we naar op zoek zijn naar matches
- $H$4:$H$8 – het bereik van resultaten waarvan u de korting wilt retourneren
- vierde argument (-1) bevat de zoekopdracht naar het dichtstbijzijnde kleinste getal dat we willen in plaats van een exacte overeenkomst.
De voordelen van deze methode zijn dat het niet nodig is om de kortingstabel te sorteren en de mogelijkheid om, indien nodig, niet alleen de dichtstbijzijnde kleinste, maar ook de dichtstbijzijnde grootste waarde te zoeken. Het laatste argument in dit geval is 1.
Maar helaas heeft nog niet iedereen deze functie - alleen gelukkige eigenaren van Office 365.
Methode 5. Power-query
Als u nog niet bekend bent met de krachtige en volledig gratis Power Query-invoegtoepassing voor Excel, dan bent u hier. Als u al bekend bent, laten we proberen het te gebruiken om ons probleem op te lossen.
Laten we eerst wat voorbereidend werk doen:
- Laten we onze brontabellen converteren naar dynamisch (slim) met behulp van een sneltoets Ctrl+T of team Home – Opmaak als tabel (Home - Opmaken als tabel).
- Laten we ze voor de duidelijkheid een naam geven. Verkoop и Kortingen tab Constructeur (Ontwerp).
- Laad elk van de tabellen om de beurt in Power Query met behulp van de knop Van tafel/bereik tab Data (Gegevens — Uit tabel/bereik). In recente versies van Excel is deze knop hernoemd naar Met bladeren (Van blad).
- Als de tabellen verschillende kolomnamen hebben met hoeveelheden, zoals in ons voorbeeld (“Aantal goederen” en “Aantal van …”), dan moeten ze in Power Query hernoemd worden en dezelfde naam krijgen.
- Daarna kunt u terugkeren naar Excel door de opdracht te selecteren in het Power Query-editorvenster Home — Sluiten en laden — Sluiten en laden in… (Home — Sluiten&Laden — Sluiten&Laden naar…) en dan optie Maak gewoon een verbinding (Alleen verbinding maken).
- Dan begint het meest interessante. Als je ervaring hebt met Power Query, dan neem ik aan dat de verdere gedachtegang in de richting moet zijn van het samenvoegen van deze twee tabellen met een join-query (merge) a la VERT.ZOEKEN, zoals het geval was in de vorige methode. In feite zullen we moeten samenvoegen in de toevoegmodus, wat op het eerste gezicht helemaal niet duidelijk is. Selecteer in Excel-tabblad Gegevens – Gegevens ophalen – Aanvragen combineren – Toevoegen (Gegevens — Gegevens ophalen — Query's combineren — Toevoegen) en dan onze tafels Verkoop и Kortingen in het venster dat verschijnt:
- Na het klikken op OK onze tafels worden tot één geheel gelijmd - onder elkaar. Houd er rekening mee dat de kolommen met de hoeveelheid goederen in deze tabellen onder elkaar vallen, want. ze hebben dezelfde naam:
- Als de oorspronkelijke rijenvolgorde in de verkooptabel belangrijk voor u is, voeg dan, zodat u deze na alle volgende transformaties kunt herstellen, een genummerde kolom toe aan onze tabel met behulp van de opdracht Een kolom toevoegen – Indexkolom (Kolom toevoegen — Indexkolom). Als de volgorde van de regels u niet uitmaakt, kunt u deze stap overslaan.
- Gebruik nu de vervolgkeuzelijst in de kop van de tabel en sorteer deze op kolom Hoeveelheid Oplopend:
- En de belangrijkste truc: klik met de rechtermuisknop op de kolomkop Korting kies een team Vullen – Omlaag (Vul — Omlaag). Lege cellen met nul automatisch ingevuld met de vorige kortingswaarden:
- Het blijft om de oorspronkelijke rijenvolgorde te herstellen door op kolom te sorteren Index (je kunt het later veilig verwijderen) en verwijder onnodige regels met een filter nul per kolom Transactie code:
- De functie VERT.ZOEKEN gebruiken om gegevens te zoeken en op te zoeken
- Het gebruik van VERT.ZOEKEN (VERT.ZOEKEN) is hoofdlettergevoelig
- XNUMXD VERT.ZOEKEN (VERT.ZOEKEN)