Inhoud
We hebben onlangs een artikel gewijd aan een van de handigste Excel-functies genaamd VPR en liet zien hoe het kan worden gebruikt om de vereiste informatie uit een database in een werkbladcel te extraheren. We hebben ook vermeld dat er twee use-cases zijn voor de functie VPR en slechts één daarvan houdt zich bezig met databasequery's. In dit artikel leert u een andere, minder bekende manier om de functie te gebruiken VPR in Excel.
Als je dit nog niet hebt gedaan, lees dan zeker het laatste artikel over de functie VPR, want bij alle onderstaande informatie wordt ervan uitgegaan dat je al bekend bent met de principes die in het eerste artikel zijn beschreven.
Bij het werken met databases, functies VPR er wordt een unieke identificatiecode doorgegeven, die wordt gebruikt om de informatie te identificeren die we willen vinden (bijvoorbeeld een productcode of een klantidentificatienummer). Deze unieke code moet aanwezig zijn in de database, anders VPR zal een fout melden. In dit artikel zullen we kijken naar deze manier om de functie te gebruiken: VPRwanneer de id helemaal niet in de database bestaat. Alsof de functie VPR overgeschakeld naar de geschatte modus en kiest welke gegevens ons moeten verstrekken als we iets willen vinden. In bepaalde omstandigheden is dit precies wat nodig is.
Een voorbeeld uit het leven. Wij stellen de taak
Laten we dit artikel illustreren met een voorbeeld uit de praktijk: commissies berekenen op basis van een breed scala aan verkoopstatistieken. We zullen beginnen met een heel eenvoudige optie, en dan zullen we het geleidelijk aan ingewikkelder maken totdat de enige rationele oplossing voor het probleem is om de functie te gebruiken VPR. Het eerste scenario voor onze fictieve taak is als volgt: als een verkoper in een jaar meer dan $ 30000 aan verkopen verdient, is zijn commissie 30%. Anders is de commissie slechts 20%. Laten we het in de vorm van een tabel zetten:
De verkoper voert zijn verkoopgegevens in cel B1 in en de formule in cel B2 bepaalt het juiste commissietarief dat de verkoper kan verwachten. Het resulterende tarief wordt op zijn beurt gebruikt in cel B3 om de totale commissie te berekenen die de verkoper zou moeten ontvangen (door simpelweg de cellen B1 en B2) te vermenigvuldigen.
Het meest interessante deel van de tabel bevindt zich in cel B2 - dit is de formule voor het bepalen van het commissietarief. Deze formule bevat een Excel-functie genaamd IF (ALS). Voor de lezers die niet bekend zijn met deze functie, zal ik uitleggen hoe het werkt:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Conditie is een functieargument dat de waarde aanneemt van ofwel WARE CODE (Waar of Juist (VALS). In het bovenstaande voorbeeld is de uitdrukking B1
Klopt het dat B1 kleiner is dan B5?
Of je kunt het anders zeggen:
Klopt het dat het totale aantal verkopen voor het jaar lager is dan de drempelwaarde?
Als we deze vraag beantwoorden JA (TRUE), dan keert de functie terug waarde indien waar (waarde indien WAAR). In ons geval is dit de waarde van cel B6, dwz het commissiepercentage wanneer de totale verkoop onder de drempel ligt. Als we de vraag beantwoorden: NEE (FALSE) keert dan terug waarde indien onwaar (waarde indien ONWAAR). In ons geval is dit de waarde van cel B7, dwz het commissiepercentage wanneer de totale verkoop boven de drempel ligt.
Zoals u kunt zien, krijgen we een commissie van 20000% in cel B2 als we de totale verkoop van $ 20 nemen. Als we een waarde van $ 40000 invoeren, verandert het commissiepercentage met 30%:
Zo werkt onze tafel.
We compliceren de taak
Laten we het een beetje moeilijker maken. Laten we een andere drempel instellen: als de verkoper meer dan $ 40000 verdient, stijgt het commissiepercentage tot 40%:
Alles lijkt eenvoudig en duidelijk, maar onze formule in cel B2 wordt merkbaar ingewikkelder. Als je goed naar de formule kijkt, zie je dat het derde argument van de functie IF (IF) omgezet in een andere volwaardige functie IF (ALS). Deze constructie wordt het nesten van functies in elkaar genoemd. Excel staat deze constructies met plezier toe, en ze werken zelfs, maar ze zijn veel moeilijker te lezen en te begrijpen.
We zullen niet ingaan op de technische details - waarom en hoe het werkt, en we zullen niet ingaan op de nuances van het schrijven van geneste functies. Dit is tenslotte een artikel gewijd aan de functie VPR, geen volledige gids voor Excel.
Hoe dan ook, de formule wordt ingewikkelder! Wat als we een andere optie introduceren voor een commissietarief van 50% voor die verkopers die meer dan $ 50000 aan verkopen verdienen. En als iemand meer dan $60000 heeft verkocht, betalen ze dan 60% commissie?
Nu is de formule in cel B2, zelfs als deze foutloos is geschreven, volledig onleesbaar geworden. Ik denk dat er maar weinig zijn die formules met 4 niveaus van nesting in hun projecten willen gebruiken. Er moet toch een makkelijkere manier zijn?!
En er is zo'n manier! De functie zal ons helpen VPR.
We passen de VERT.ZOEKEN-functie toe om het probleem op te lossen
Laten we het ontwerp van onze tafel een beetje veranderen. We zullen dezelfde velden en gegevens behouden, maar ze op een nieuwe, compactere manier ordenen:
Neem even de tijd en zorg ervoor dat de nieuwe tafel Tarieftabel bevat dezelfde gegevens als de vorige drempeltabel.
Het belangrijkste idee is om de functie te gebruiken: VPR om het gewenste tarief te bepalen volgens de tabel Tarieftabel afhankelijk van het verkoopvolume. Houd er rekening mee dat de verkoper goederen kan verkopen voor een bedrag dat niet gelijk is aan een van de vijf drempels in de tabel. Hij zou bijvoorbeeld kunnen verkopen voor $ 34988, maar een dergelijk bedrag is er niet. Laten we eens kijken hoe de functie VPR kan omgaan met een dergelijke situatie.
Een VERT.ZOEKEN-functie invoegen
Selecteer cel B2 (waar we onze formule willen invoegen) en zoek VLOOKUP (VERT.ZOEKEN) in de Excel-functiebibliotheek: Formules (formules) > Functiebibliotheek (Functiebibliotheek) > Opzoeken en verwijzen (Referenties en arrays).
Er verschijnt een dialoogvenster Functieargumenten (Functie argumenten). We vullen de waarden van de argumenten één voor één in, te beginnen met Opzoekwaarde (Opzoekwaarde). In dit voorbeeld is dit het totale bedrag aan verkopen uit cel B1. Zet de cursor in het veld Opzoekwaarde (Lookup_value) en selecteer cel B1.
Vervolgens moet u de functies specificeren VPRwaar u gegevens kunt zoeken. In ons voorbeeld is dit een tabel Tarieftabel. Zet de cursor in het veld Tabel_array (Tabel) en selecteer de hele tafel Tarieftabelbehalve kopteksten.
Vervolgens moeten we specificeren uit welke kolom gegevens moeten worden geëxtraheerd met behulp van onze formule. We zijn geïnteresseerd in het commissietarief, dat in de tweede kolom van de tabel staat. Daarom, voor het argument Col_index_num (Kolom_nummer) voer de waarde 2 in.
En tot slot introduceren we het laatste argument - Bereik_opzoeken (Interval_opzoeken).
Belangrijk: het is het gebruik van dit argument dat het verschil maakt tussen de twee manieren om de functie toe te passen VPR. Bij het werken met databases is het argument Bereik_opzoeken (range_lookup) moet altijd een waarde hebben Juist (FALSE) om naar een exacte overeenkomst te zoeken. Bij ons gebruik van de functie VPR, moeten we dit veld leeg laten, of een waarde invoeren WARE CODE (WAAR). Het is uiterst belangrijk om deze optie correct te kiezen.
Om het duidelijker te maken, zullen we introduceren: WARE CODE (TRUE) in het veld Bereik_opzoeken (Interval_opzoeken). Hoewel, als u het veld leeg laat, dit geen fout is, aangezien WARE CODE is de standaardwaarde:
We hebben alle parameters ingevuld. Nu drukken we op OK, en Excel maakt een formule voor ons met een functie VPR.
Als we experimenteren met meerdere verschillende waarden voor het totale verkoopbedrag, dan zorgen we ervoor dat de formule correct werkt.
Conclusie
Wanneer de functie VPR werkt met databases, argument Bereik_opzoeken (range_lookup) moet accepteren Juist (VALS). En de waarde ingevoerd als Opzoekwaarde (Lookup_value) moet in de database voorkomen. Met andere woorden, het is zoeken naar een exacte match.
In het voorbeeld dat we in dit artikel hebben bekeken, is het niet nodig om een exacte overeenkomst te krijgen. Dit is het geval wanneer de functie VPR moet overschakelen naar de benaderingsmodus om het gewenste resultaat te krijgen.
Bijvoorbeeld: We willen bepalen welk tarief we moeten gebruiken bij de berekening van de commissie voor een verkoper met een verkoopvolume van $ 34988. Functie VPR geeft ons een waarde van 30%, wat absoluut correct is. Maar waarom selecteerde de formule de rij die precies 30% bevat en niet 20% of 40%? Wat wordt bedoeld met zoeken bij benadering? Laten we duidelijk zijn.
wanneer het argument Bereik_opzoeken (interval_lookup) heeft een waarde WARE CODE (TRUE) of weggelaten, functie VPR doorloopt de eerste kolom en selecteert de grootste waarde die de opzoekwaarde niet overschrijdt.
Belangrijk punt: Om dit schema te laten werken, moet de eerste kolom van de tabel in oplopende volgorde worden gesorteerd.