Përmbajtje
Së fundmi i kemi kushtuar një artikull një prej funksioneve më të dobishme të Excel të quajtur VPR dhe tregoi se si mund të përdoret për të nxjerrë informacionin e kërkuar nga një bazë të dhënash në një qelizë të fletës së punës. Ne përmendëm gjithashtu se ka dy raste përdorimi për funksionin VPR dhe vetëm njëri prej tyre merret me pyetjet e bazës së të dhënave. Në këtë artikull, do të mësoni një mënyrë tjetër më pak të njohur për të përdorur funksionin VPR në Excel.
Nëse nuk e keni bërë ende këtë, atëherë sigurohuni që të lexoni artikullin e fundit në lidhje me funksionin VPR, sepse i gjithë informacioni i mëposhtëm supozon se tashmë jeni njohur me parimet e përshkruara në artikullin e parë.
Kur punoni me bazat e të dhënave, funksionet VPR kalohet një identifikues unik, i cili përdoret për të identifikuar informacionin që duam të gjejmë (për shembull, një kod produkti ose një numër identifikimi të klientit). Ky kod unik duhet të jetë i pranishëm në bazën e të dhënave, përndryshe VPR do të raportojë një gabim. Në këtë artikull, ne do të shikojmë këtë mënyrë të përdorimit të funksionit VPRkur ID-ja nuk ekziston fare në bazën e të dhënave. Sikur funksioni VPR kalon në modalitetin e përafërt dhe zgjedh se çfarë të dhënash do të na japë kur duam të gjejmë diçka. Në rrethana të caktuara, kjo është pikërisht ajo që nevojitet.
Një shembull nga jeta. Ne vendosëm detyrën
Le ta ilustrojmë këtë artikull me një shembull të jetës reale – llogaritja e komisioneve bazuar në një gamë të gjerë metrikash të shitjeve. Ne do të fillojmë me një opsion shumë të thjeshtë dhe më pas do ta ndërlikojmë gradualisht derisa e vetmja zgjidhje racionale e problemit të jetë përdorimi i funksionit VPR. Skenari fillestar për detyrën tonë fiktive është si vijon: nëse një shitës bën më shumë se 30000 dollarë në shitje në një vit, atëherë komisioni i tij është 30%. Përndryshe, komisioni është vetëm 20%. Le ta vendosim në formën e një tabele:
Shitësi fut të dhënat e tyre të shitjeve në qelizën B1 dhe formula në qelizën B2 përcakton normën e saktë të komisionit që mund të presë shitësi. Nga ana tjetër, norma që rezulton përdoret në qelizën B3 për të llogaritur komisionin total që duhet të marrë shitësi (thjesht duke shumëzuar qelizat B1 dhe B2).
Pjesa më interesante e tabelës gjendet në qelizën B2 - kjo është formula për përcaktimin e shkallës së komisionit. Kjo formulë përmban një funksion Excel të quajtur IF (NËSE). Për ata lexues që nuk janë të njohur me këtë funksion, unë do të shpjegoj se si funksionon:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Gjendja është një argument funksioni që merr vlerën e njërit prej tyre KODI I VËRTETË (E VËRTETË), ose I RREMË (I RREMË). Në shembullin e mësipërm, shprehja B1
A është e vërtetë që B1 është më pak se B5?
Ose mund ta thuash ndryshe:
A është e vërtetë që shuma totale e shitjeve për vitin është më e vogël se vlera e pragut?
Nëse i përgjigjemi kësaj pyetjeje PO (TRUE), më pas funksioni kthehet vlerë nëse është e vërtetë (vlera nëse E VËRTETË). Në rastin tonë, kjo do të jetë vlera e qelizës B6, pra norma e komisionit kur shitjet totale janë nën pragun. Nëse i përgjigjemi pyetjes NO (FALSE) pastaj kthehet vlera nëse është e gabuar (vlera nëse FALSE). Në rastin tonë, kjo është vlera e qelizës B7, pra norma e komisionit kur shitjet totale janë mbi pragun.
Siç mund ta shihni, nëse marrim shitjet totale prej 20000 dollarësh, marrim një normë komisioni prej 2% në qelizën B20. Nëse futim një vlerë prej $40000, atëherë norma e komisionit do të ndryshojë me 30%:
Kështu funksionon tabela jonë.
Ne e komplikojmë detyrën
Le t'i bëjmë gjërat pak më të vështira. Le të vendosim një prag tjetër: nëse shitësi fiton më shumë se 40000 dollarë, atëherë norma e komisionit rritet në 40%:
Gjithçka duket të jetë e thjeshtë dhe e qartë, por formula jonë në qelizën B2 bëhet dukshëm më e ndërlikuar. Nëse shikoni nga afër formulën, do të shihni se argumenti i tretë i funksionit IF (IF) u kthye në një funksion tjetër të plotë IF (NËSE). Ky ndërtim quhet foleja e funksioneve në njëra-tjetrën. Excel-i i lejon me kënaqësi këto konstruksione, madje ato funksionojnë, por ato janë shumë më të vështira për t'u lexuar dhe kuptuar.
Ne nuk do të thellohemi në detajet teknike - pse dhe si funksionon, dhe nuk do të hyjmë në nuancat e shkrimit të funksioneve të mbivendosur. Në fund të fundit, ky është një artikull kushtuar funksionit VPR, jo një udhëzues i plotë për Excel.
Sido që të jetë, formula bëhet më e ndërlikuar! Po sikur të prezantojmë një opsion tjetër për një normë komisioni prej 50% për ata shitës që bëjnë më shumë se 50000 dollarë në shitje. Dhe nëse dikush ka shitur më shumë se 60000 dollarë, a do të paguajë komision 60%?
Tani formula në qelizën B2, edhe nëse është shkruar pa gabime, është bërë plotësisht e palexueshme. Mendoj se janë të paktë ata që duan të përdorin formula me 4 nivele foleje në projektet e tyre. Duhet të ketë një mënyrë më të lehtë?!
Dhe ka një mënyrë të tillë! Funksioni do të na ndihmojë VPR.
Ne aplikojmë funksionin VLOOKUP për të zgjidhur problemin
Le të ndryshojmë pak dizajnin e tryezës sonë. Ne do t'i mbajmë të gjitha fushat dhe të dhënat e njëjta, por do t'i rregullojmë ato në një mënyrë të re, më kompakte:
Merrni një moment dhe sigurohuni që tavolina e re Tabela e normave përfshin të njëjtat të dhëna si tabela e mëparshme e pragut.
Ideja kryesore është përdorimi i funksionit VPR për të përcaktuar tarifën e dëshiruar sipas tabelës Tabela e normave në varësi të vëllimit të shitjeve. Ju lutemi vini re se shitësi mund të shesë mallra për një shumë që nuk është e barabartë me një nga pesë pragjet në tabelë. Për shembull, ai mund të shesë për 34988 dollarë, por nuk ka një shumë të tillë. Le të shohim se si funksionon VPR mund të përballet me një situatë të tillë.
Futja e një funksioni VLOOKUP
Zgjidhni qelizën B2 (ku duam të fusim formulën tonë) dhe gjeni VLOOKUP (VLOOKUP) në Bibliotekën e Funksioneve të Excel: Formulat (formula) > Biblioteka e Funksioneve (Biblioteka e funksionit) > Kërkimi dhe Referenca (Referencat dhe vargjet).
Shfaqet një kuti dialogu Argumentet e funksionit (Argumentet e funksionit). Plotësojmë vlerat e argumenteve një nga një, duke filluar me Vlera e kërkimit (Vlera_kërkimi). Në këtë shembull, kjo është shuma totale e shitjeve nga qeliza B1. Vendosni kursorin në fushë Vlera e kërkimit (Lookup_value) dhe zgjidhni qelizën B1.
Tjetra, duhet të specifikoni funksionet VPRku të kërkoni të dhëna. Në shembullin tonë, kjo është një tabelë Tabela e normave. Vendosni kursorin në fushë Tabela_array (Tabela) dhe zgjidhni të gjithë tabelën Tabela e normavepërveç titujve.
Më pas, ne duhet të specifikojmë se nga cila kolonë të nxjerrim të dhëna duke përdorur formulën tonë. Ne jemi të interesuar për normën e komisionit, e cila është në kolonën e dytë të tabelës. Prandaj, për argumentin Kole_indeksi_num (Column_number) shkruani vlerën 2.
Dhe së fundi, ne prezantojmë argumentin e fundit - Range_lookup (Interval_lookup).
E rëndësishme: është përdorimi i këtij argumenti që bën dallimin midis dy mënyrave të aplikimit të funksionit VPR. Kur punoni me bazat e të dhënave, argumenti Range_lookup (range_lookup) duhet të ketë gjithmonë një vlerë I RREMË (FALSE) për të kërkuar një përputhje të saktë. Në përdorimin tonë të funksionit VPR, duhet ta lëmë bosh këtë fushë ose të fusim një vlerë KODI I VËRTETË (E VËRTETË). Është jashtëzakonisht e rëndësishme të zgjidhni këtë opsion në mënyrë korrekte.
Për ta bërë më të qartë, do të prezantojmë KODI I VËRTETË (E VËRTETË) në fushë Range_lookup (Interval_lookup). Edhe pse, nëse e lini fushën bosh, ky nuk do të jetë një gabim, pasi KODI I VËRTETË është vlera e tij e paracaktuar:
Kemi plotësuar të gjithë parametrat. Tani shtypim OK, dhe Excel krijon një formulë për ne me një funksion VPR.
Nëse eksperimentojmë me disa vlera të ndryshme për shumën totale të shitjeve, atëherë do të sigurohemi që formula të funksionojë siç duhet.
Përfundim
Kur funksioni VPR punon me bazat e të dhënave, argument Range_lookup (range_lookup) duhet të pranojë I RREMË (I RREMË). Dhe vlera e futur si Vlera e kërkimit (Lookup_value) duhet të ekzistojë në bazën e të dhënave. Me fjalë të tjera, është duke kërkuar për një përputhje të saktë.
Në shembullin që kemi parë në këtë artikull, nuk ka nevojë për të marrë një përputhje të saktë. Ky është rasti kur funksioni VPR duhet të kalojë në modalitetin e përafërt për të kthyer rezultatin e dëshiruar.
Për shembull: Ne duam të përcaktojmë se çfarë norme duhet të përdorim në llogaritjen e komisionit për një shitës me një vëllim shitjesh prej 34988 dollarë. Funksioni VPR na kthen një vlerë prej 30%, që është absolutisht e saktë. Por pse formula zgjodhi rreshtin që përmban saktësisht 30% dhe jo 20% ose 40%? Çfarë nënkuptohet me kërkim të përafërt? Le të jemi të qartë.
Kur argumenti Range_lookup (interval_lookup) ka një vlerë KODI I VËRTETË (E VËRTETË) ose i anashkaluar, funksion VPR përsëritet nëpër kolonën e parë dhe zgjedh vlerën më të madhe që nuk e kalon vlerën e kërkuar.
Pikë e rëndësishme: Që kjo skemë të funksionojë, kolona e parë e tabelës duhet të renditet në rend rritës.