Contents [show]
Ons het onlangs 'n artikel opgedra aan een van die nuttigste Excel-funksies genaamd VPR en gewys hoe dit gebruik kan word om die vereiste inligting uit 'n databasis in 'n werkbladsel te onttrek. Ons het ook genoem dat daar twee gebruiksgevalle vir die funksie is VPR en slegs een van hulle handel oor databasisnavrae. In hierdie artikel sal jy 'n ander minder bekende manier leer om die funksie te gebruik VPR in Excel.
As jy dit nog nie gedoen het nie, lees dan die laaste artikel oor die funksie VPR, want al die inligting hieronder veronderstel dat jy reeds vertroud is met die beginsels wat in die eerste artikel beskryf word.
Wanneer daar met databasisse gewerk word, funksies VPR 'n unieke identifiseerder word deurgegee, wat gebruik word om die inligting te identifiseer wat ons wil vind (byvoorbeeld 'n produkkode of 'n kliënt-identifikasienommer). Hierdie unieke kode moet in die databasis teenwoordig wees, anders VPR sal 'n fout rapporteer. In hierdie artikel sal ons kyk na hierdie manier om die funksie te gebruik VPRwanneer die id glad nie in die databasis bestaan nie. Asof die funksie VPR oorgeskakel na die benaderde modus, en kies watter data om ons te verskaf wanneer ons iets wil vind. In sekere omstandighede is dit presies wat nodig is.
'n Voorbeeld uit die lewe. Ons het die taak gestel
Kom ons illustreer hierdie artikel met 'n werklike voorbeeld - berekening van kommissies gebaseer op 'n wye reeks verkoopsstatistieke. Ons begin met 'n baie eenvoudige opsie, en dan sal ons dit geleidelik kompliseer totdat die enigste rasionele oplossing vir die probleem is om die funksie te gebruik VPR. Die aanvanklike scenario vir ons fiktiewe taak is soos volg: as 'n verkoopspersoon meer as $30000 30 in verkope in 'n jaar maak, dan is sy kommissie 20%. Andersins is die kommissie slegs XNUMX%. Kom ons stel dit in die vorm van 'n tabel:
Die verkoper voer hul verkoopsdata in sel B1 in, en die formule in sel B2 bepaal die korrekte kommissiekoers wat die verkoper kan verwag. Op sy beurt word die gevolglike koers in sel B3 gebruik om die totale kommissie te bereken wat die verkoper moet ontvang (vermenigvuldig net selle B1 en B2).
Die interessantste deel van die tabel is vervat in sel B2 – dit is die formule vir die bepaling van die kommissiekoers. Hierdie formule bevat 'n Excel-funksie genaamd IF (IF). Vir die lesers wat nie met hierdie funksie vertroud is nie, sal ek verduidelik hoe dit werk:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Condition is 'n funksie-argument wat die waarde van óf neem WARE KODE (WAAR), of ONWAAR (ONWAAR). In die voorbeeld hierbo, die uitdrukking B1
Is dit waar dat B1 minder as B5 is?
Of jy kan dit anders sê:
Is dit waar dat die totale bedrag van verkope vir die jaar minder as die drempelwaarde is?
As ons hierdie vraag beantwoord JA (WAAR), dan keer die funksie terug waarde indien waar (waarde indien WAAR). In ons geval sal dit die waarde van sel B6 wees, dit wil sê kommissiekoers wanneer totale verkope onder die drempel is. As ons die vraag beantwoord GEEN (ONWAAR) keer dan terug waarde indien onwaar (waarde indien ONWAAR). In ons geval is dit die waarde van sel B7, dit wil sê kommissiekoers wanneer totale verkope bo die drempel is.
Soos u kan sien, as ons die totale verkope van $20000 2 neem, kry ons 'n 20% kommissiekoers in sel B40000. As ons 'n waarde van $30 XNUMX invoer, sal die kommissiekoers met XNUMX% verander:
Dit is hoe ons tafel werk.
Ons bemoeilik die taak
Kom ons maak dinge 'n bietjie moeiliker. Kom ons stel nog 'n drempel: as die verkoper meer as $40000 40 verdien, verhoog die kommissiekoers tot XNUMX%:
Alles blyk eenvoudig en duidelik te wees, maar ons formule in sel B2 word merkbaar meer ingewikkeld. As jy noukeurig na die formule kyk, sal jy sien dat die derde argument van die funksie IF (IF) het in nog 'n volwaardige funksie verander IF (IF). Hierdie konstruksie word nesting van funksies in mekaar genoem. Excel laat hierdie konstrukte gelukkig toe, en hulle werk selfs, maar dit is baie moeiliker om te lees en te verstaan.
Ons sal nie in die tegniese besonderhede delf nie - hoekom en hoe dit werk, en ons gaan nie in op die nuanses van die skryf van geneste funksies nie. Dit is immers 'n artikel wat aan die funksie gewy is VPR, nie 'n volledige gids tot Excel nie.
Wat ook al die geval is, die formule word meer ingewikkeld! Wat as ons 'n ander opsie instel vir 'n kommissiekoers van 50% vir daardie verkopers wat meer as $50000 60000 aan verkope maak. En as iemand meer as $60 XNUMX verkoop het, sal hulle XNUMX% kommissie betaal?
Nou het die formule in sel B2, al is dit sonder foute geskryf, heeltemal onleesbaar geword. Ek dink daar is min wat formules met 4 vlakke van nes in hul projekte wil gebruik. Daar moet 'n makliker manier wees?!
En daar is so 'n manier! Die funksie sal ons help VPR.
Ons pas die VLOOKUP-funksie toe om die probleem op te los
Kom ons verander die ontwerp van ons tafel 'n bietjie. Ons sal dieselfde velde en data hou, maar dit op 'n nuwe, meer kompakte manier rangskik:
Neem 'n oomblik en maak seker die nuwe tafel Tarieftabel sluit dieselfde data as die vorige drempeltabel in.
Die hoofgedagte is om die funksie te gebruik VPR om die verlangde tariefkoers volgens die tabel te bepaal Tarieftabel afhangende van verkoopsvolume. Neem asseblief kennis dat die verkoper goedere kan verkoop vir 'n bedrag wat nie gelyk is aan een van die vyf drempels in die tabel nie. Hy kan byvoorbeeld vir $34988 verkoop, maar daar is nie so 'n bedrag nie. Kom ons kyk hoe die funksie VPR so 'n situasie kan hanteer.
Voeg 'n VLOOKUP-funksie in
Kies sel B2 (waar ons ons formule wil invoeg) en vind VLOOKUP (VLOOKUP) in die Excel-funksiesbiblioteek: Formules (formules) > Funksie Biblioteek (Funksiebiblioteek) > Soek en verwysing (Verwysings en skikkings).
'n Dialoogkassie verskyn Funksie argumente (Funksie-argumente). Ons vul die waardes van die argumente een vir een in, begin met Opsoek_waarde (Opsoek_waarde). In hierdie voorbeeld is dit die totale hoeveelheid verkope vanaf sel B1. Plaas die wyser in die veld Opsoek_waarde (Opsoekwaarde) en kies sel B1.
Vervolgens moet u die funksies spesifiseer VPRwaar om data te soek. In ons voorbeeld is dit 'n tabel Tarieftabel. Plaas die wyser in die veld Tabel_skikking (Tabel) en kies die hele tabel Tarieftabelbehalwe opskrifte.
Vervolgens moet ons spesifiseer watter kolom data uit die gebruik van ons formule moet onttrek. Ons stel belang in die kommissiekoers, wat in die tweede kolom van die tabel is. Daarom, vir die argument Kol_indeks_getal (Kolomnommer) voer die waarde 2 in.
En laastens stel ons die laaste argument voor – Reeks_opsoek (Interval_soek).
Belangrik: dit is die gebruik van hierdie argument wat die verskil maak tussen die twee maniere om die funksie toe te pas VPR. Wanneer daar met databasisse gewerk word, word die argument Reeks_opsoek (reeks_soek) moet altyd 'n waarde hê ONWAAR (ONWAAR) om vir 'n presiese pasmaat te soek. In ons gebruik van die funksie VPR, moet ons hierdie veld leeg laat, of 'n waarde invoer WARE KODE (WAAR). Dit is uiters belangrik om hierdie opsie korrek te kies.
Om dit duideliker te maak, sal ons bekendstel WARE KODE (WAAR) in die veld Reeks_opsoek (Interval_soek). Alhoewel, as jy die veld leeg laat, sal dit nie 'n fout wees nie, aangesien WARE KODE is sy verstekwaarde:
Ons het al die parameters ingevul. Nou druk ons OK, en Excel skep vir ons 'n formule met 'n funksie VPR.
As ons eksperimenteer met verskeie verskillende waardes vir die totale verkoopsbedrag, dan sal ons seker maak dat die formule korrek werk.
Gevolgtrekking
Wanneer die funksie VPR werk met databasisse, argument Reeks_opsoek (reeks_soek) moet aanvaar ONWAAR (ONWAAR). En die waarde ingevoer as Opsoek_waarde (Opsoekwaarde) moet in die databasis bestaan. Met ander woorde, dit soek 'n presiese pasmaat.
In die voorbeeld waarna ons in hierdie artikel gekyk het, is dit nie nodig om 'n presiese passing te kry nie. Dit is die geval wanneer die funksie VPR moet oorskakel na benaderde modus om die gewenste resultaat terug te gee.
Byvoorbeeld: Ons wil bepaal watter koers om te gebruik in die kommissieberekening vir 'n verkoopspersoon met 'n verkoopsvolume van $34988. Funksie VPR gee vir ons 'n waarde van 30%, wat absoluut korrek is. Maar hoekom het die formule die ry gekies wat presies 30% bevat en nie 20% of 40% nie? Wat word bedoel met benaderde soektog? Kom ons wees duidelik.
Wanneer die argument Reeks_opsoek (interval_lookup) het 'n waarde WARE KODE (WAAR) of weggelaat, funksie VPR herhaal deur die eerste kolom en kies die grootste waarde wat nie die opsoekwaarde oorskry nie.
Belangrike punt: Vir hierdie skema om te werk, moet die eerste kolom van die tabel in stygende volgorde gesorteer word.