Sisu
Pühendasime hiljuti artikli ühele kõige kasulikumale Exceli funktsioonile VPR ja näitas, kuidas selle abil saab andmebaasist vajaliku teabe töölehe lahtrisse eraldada. Mainisime ka, et funktsioonil on kaks kasutusjuhtu VPR ja ainult üks neist tegeleb andmebaasipäringutega. Sellest artiklist saate teada veel ühe vähemtuntud viisi funktsiooni kasutamiseks VPR Excelis.
Kui te pole seda veel teinud, lugege kindlasti viimast funktsiooni käsitlevat artiklit VPR, sest kogu allolev teave eeldab, et olete esimeses artiklis kirjeldatud põhimõtetega juba tuttav.
Töötades andmebaasidega, funktsioonidega VPR edastatakse kordumatu tunnus, mille abil tuvastatakse teave, mida soovime leida (näiteks tootekood või kliendi identifitseerimisnumber). Vastasel juhul peab see kordumatu kood andmebaasis olemas olema VPR teatab veast. Selles artiklis vaatleme seda funktsiooni kasutamise viisi VPRkui ID-d andmebaasis üldse pole. Justkui funktsioon VPR lülitub ligikaudsele režiimile ja valib, milliseid andmeid meile esitada, kui tahame midagi leida. Teatud juhtudel on see just see, mida vaja on.
Näide elust. Seadsime ülesande
Illustreerime seda artiklit elulise näitega – vahendustasude arvutamine väga erinevate müügimõõdikute põhjal. Alustame väga lihtsast variandist ja seejärel muudame selle järk-järgult keerulisemaks, kuni probleemi ainsaks ratsionaalseks lahenduseks on funktsiooni kasutamine VPR. Meie fiktiivse ülesande esialgne stsenaarium on järgmine: kui müüja teenib aastas rohkem kui 30000 30 dollarit, on tema vahendustasu 20%. Vastasel juhul on vahendustasu vaid XNUMX%. Paneme selle tabeli kujul:
Müüja sisestab oma müügiandmed lahtrisse B1 ja lahtris B2 olev valem määrab õige vahendustasu määra, mida müüja võib oodata. Saadud määra kasutatakse omakorda lahtris B3, et arvutada kogu komisjonitasu, mida müüja peaks saama (lihtsalt korrutades lahtrid B1 ja B2).
Tabeli kõige huvitavam osa on lahtris B2 – see on vahendustasu määra määramise valem. See valem sisaldab Exceli funktsiooni nimega IF (IF). Lugejatele, kes pole selle funktsiooniga tuttavad, selgitan, kuidas see toimib:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Tingimus on funktsiooni argument, mis võtab kummagi väärtuse TÕELINE KOOD (TRUE) või FALSE (VALE). Ülaltoodud näites avaldis B1
Kas vastab tõele, et B1 on väiksem kui B5?
Või võite öelda teisiti:
Kas vastab tõele, et aasta müügi kogusumma on väiksem kui piirväärtus?
Kui vastame sellele küsimusele JAH (TRUE), naaseb funktsioon väärtus, kui see on tõsi (väärtus, kui TRUE). Meie puhul on selleks lahtri B6 väärtus, st komisjonitasu, kui kogumüük jääb alla läve. Kui vastame küsimusele EI (FALSE) naaseb seejärel väärtus, kui vale (väärtus, kui FALSE). Meie puhul on see lahtri B7 väärtus ehk komisjonitasu, kui kogumüük on üle läve.
Nagu näete, kui võtame 20000 2 dollari kogumüügi, saame lahtris B20 40000% komisjonitasu. Kui sisestame väärtuse 30 XNUMX dollarit, muutub komisjonitasu XNUMX% võrra:
Nii töötab meie tabel.
Teeme ülesande keerulisemaks
Teeme asja natuke keerulisemaks. Seadke veel üks lävi: kui müüja teenib rohkem kui 40000 40 dollarit, tõuseb vahendustasu XNUMX%-ni:
Kõik näib olevat lihtne ja selge, kuid meie valem lahtris B2 muutub märgatavalt keerulisemaks. Kui vaatate valemit tähelepanelikult, näete, et funktsiooni kolmas argument IF (IF) muutus teiseks täieõiguslikuks funktsiooniks IF (IF). Seda konstruktsiooni nimetatakse funktsioonide üksteisega pesastamiseks. Excel lubab neid konstruktsioone hea meelega ja need isegi töötavad, kuid neid on palju raskem lugeda ja mõista.
Me ei süvene tehnilistesse üksikasjadesse – miks ja kuidas see töötab, samuti ei lasku me pesastatud funktsioonide kirjutamise nüanssidesse. Lõppude lõpuks on see funktsioonile pühendatud artikkel VPR, ei ole täielik Exceli juhend.
Igal juhul läheb valem keerulisemaks! Mis siis, kui võtaksime kasutusele teise võimaluse 50% vahendustasu jaoks nendele müüjatele, kes teenivad rohkem kui 50000 60000 dollarit. Ja kui keegi on müünud rohkem kui 60 XNUMX dollarit, kas ta maksab XNUMX% vahendustasu?
Nüüd on lahtris B2 olev valem, isegi kui see oli kirjutatud ilma vigadeta, muutunud täiesti loetamatuks. Arvan, et vähe on neid, kes soovivad oma projektides kasutada 4-tasemelise pesitsusastmega valemeid. Peab olema lihtsam viis?!
Ja selline viis on olemas! Funktsioon aitab meid VPR.
Probleemi lahendamiseks rakendame funktsiooni VLOOKUP
Muudame veidi oma tabeli kujundust. Säilitame kõik samad väljad ja andmed, kuid korraldame need uuel, kompaktsemal viisil:
Võtke hetk ja veenduge, et uus laud Hindade tabel sisaldab samu andmeid, mis eelmine lävetabel.
Põhiidee on funktsiooni kasutamine VPR soovitud tariifimäära määramiseks vastavalt tabelile Hindade tabel sõltuvalt müügimahust. Pange tähele, et müüja võib müüa kaupu summas, mis ei ole võrdne ühega tabelis toodud viiest lävendist. Näiteks võiks ta müüa 34988 dollari eest, aga sellist summat pole. Vaatame, kuidas see funktsioon toimib VPR saab sellise olukorraga hakkama.
Funktsiooni VLOOKUP sisestamine
Valige lahter B2 (kuhu tahame oma valemi sisestada) ja leidke VLOOKUP (VLOOKUP) Exceli funktsioonide teegis: Valemid (valemid) > Funktsioonide Raamatukogu (Funktsiooniteek) > Otsing ja viide (Viited ja massiivid).
Ilmub dialoogiboks Funktsiooni argumendid (Funktsiooni argumendid). Täidame argumentide väärtused ükshaaval, alustades Otsingu_väärtus (Otsingu_väärtus). Selles näites on see lahtri B1 müügi kogusumma. Asetage kursor väljale Otsingu_väärtus (Otsingu_väärtus) ja valige lahter B1.
Järgmisena peate määrama funktsioonid VPRkust andmeid otsida. Meie näites on see tabel Hindade tabel. Asetage kursor väljale Tabel_massiiv (Tabel) ja valige kogu tabel Hindade tabelvälja arvatud päised.
Järgmiseks peame määrama, millisest veerust andmed meie valemi abil eraldada. Oleme huvitatud vahendustasu määrast, mis on tabeli teises veerus. Seetõttu argumendiks Veergude_indeksi_arv (Veeru_number) sisestage väärtus 2.
Ja lõpuks tutvustame viimast argumenti - Vahemiku_otsing (Interval_otsing).
NB! just selle argumendi kasutamine teeb vahe kahe funktsiooni rakendamise viisi vahel VPR. Andmebaasidega töötamisel argument Vahemiku_otsing (vahemiku_otsing) peab alati olema väärtus FALSE (FALSE), et otsida täpset vastet. Meie funktsiooni kasutamisel VPR, peame selle välja tühjaks jätma või sisestama väärtuse TÕELINE KOOD (TÕSI). Äärmiselt oluline on see valik õigesti valida.
Et oleks selgem, tutvustame TÕELINE KOOD (TRUE) põllul Vahemiku_otsing (Interval_otsing). Kuigi kui jätate välja tühjaks, ei ole see viga, kuna TÕELINE KOOD on selle vaikeväärtus:
Täitsime kõik parameetrid. Nüüd vajutame OK, ja Excel loob meie jaoks funktsiooniga valemi VPR.
Kui katsetame kogu müügisumma mitme erineva väärtusega, siis veendume, et valem töötab õigesti.
Järeldus
Kui funktsioon VPR töötab andmebaasidega, argument Vahemiku_otsing (vahemiku_otsing) peab nõustuma FALSE (VALE). Ja väärtus sisestati kui Otsingu_väärtus (Otsingu_väärtus) peab andmebaasis olemas olema. Teisisõnu, see otsib täpset vastet.
Selles artiklis vaadeldud näites pole täpset vastet vaja hankida. Seda juhul, kui funktsioon VPR peab soovitud tulemuse saamiseks lülituma ligikaudsele režiimile.
Näiteks: Soovime kindlaks teha, millist määra kasutada vahendustasu arvutamisel müüja puhul, kelle müügimaht on 34988 dollarit. Funktsioon VPR tagastab meile väärtuse 30%, mis on täiesti õige. Aga miks valis valem rea, mis sisaldab täpselt 30%, mitte aga 20% või 40%? Mida mõeldakse ligikaudse otsingu all? Olgem selged.
Kui argument Vahemiku_otsing (interval_lookup) omab väärtust TÕELINE KOOD (TRUE) või välja jäetud, funktsioon VPR kordab läbi esimese veeru ja valib suurima väärtuse, mis ei ületa otsinguväärtust.
Oluline punkt: Selle skeemi toimimiseks tuleb tabeli esimene veerg järjestada kasvavas järjekorras.