Shembuj të avancuar VLOOKUP: Kërkim me shumë kritere

Në pjesën e dytë të tutorialit tonë mbi funksionin VPR (VLOOKUP) në Excel, ne do të analizojmë disa shembuj që do t'ju ndihmojnë të drejtoni të gjithë fuqinë VPR për të zgjidhur detyrat më ambicioze të Excel. Shembujt supozojnë se ju tashmë keni një njohuri bazë se si funksionon kjo veçori. Nëse jo, mund të jeni të interesuar të filloni me pjesën e parë të këtij tutoriali, i cili shpjegon sintaksën dhe përdorimin bazë. VPR. Epo, le të fillojmë.

• Kërkoni në Excel me disa kritere
• Ne nxjerrim vlerat e 2-të, të 3-të, etj. duke përdorur VLOOKUP
• Merrni të gjitha përsëritjet e vlerës së dëshiruar
• XNUMXD kërkim sipas rreshtit dhe kolonës së njohur
• Përdorimi i shumëfishtë VLOOKUP në një formulë
• Zëvendësimi dinamik i të dhënave nga tabela të ndryshme

Kërkoni në Excel me disa kritere

funksion VPR në Excel është një mjet vërtet i fuqishëm për të kryer kërkime për një vlerë të caktuar në një bazë të dhënash. Sidoqoftë, ekziston një kufizim i rëndësishëm - sintaksa e tij ju lejon të kërkoni vetëm një vlerë. Po sikur të dëshironi të kërkoni sipas kushteve të shumta? Zgjidhjen do ta gjeni më poshtë.

Shembulli 1: Kërko sipas 2 kritereve të ndryshme

Supozoni se kemi një listë porosish dhe duam të gjejmë Sasia e mallit (Sasia), bazuar në dy kritere - Emri i klientit (Klient) и Emri i Produktit (Produkt). Çështja është e ndërlikuar nga fakti se secili nga blerësit porositi disa lloje mallrash, siç mund të shihet nga tabela e mëposhtme:

funksion të rregullt VPR nuk do të funksionojë në këtë skenar sepse do të kthejë vlerën e parë që gjen që përputhet me vlerën e dhënë të kërkimit. Për shembull, nëse doni të dini sasinë e një artikulli Ëmbëlsirat'porositur nga blerësi Jeremy Hill, shkruani formulën e mëposhtme:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

– kjo formulë do të kthejë rezultatin 15që korrespondon me produktin mollë, sepse është vlera e parë që përputhet.

Ekziston një zgjidhje e thjeshtë - krijoni një kolonë shtesë në të cilën kombinohen të gjitha kriteret e dëshiruara. Në shembullin tonë, këto janë kolonat Emri i klientit (Klient) и Emri i Produktit (Produkt). Mos harroni se kolona e bashkuar duhet të jetë gjithmonë kolona më e majtë në diapazonin e kërkimit, pasi është kolona e majtë që funksionon VPR shikon lart kur kërkon një vlerë.

Pra, shtoni një kolonë ndihmëse në tabelë dhe kopjoni formulën e mëposhtme mbi të gjitha qelizat e saj: =B2&C2. Nëse dëshironi që vargu të jetë më i lexueshëm, mund t'i ndani vlerat e kombinuara me një hapësirë: =B2&» «&C2. Pas kësaj, mund të përdorni formulën e mëposhtme:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Ku është qelia B1 përmban vlerën e bashkuar të argumentit lookup_value (vlera_kërkimi) dhe 4 – Argument col_index_num (column_number), dmth. numri i kolonës që përmban të dhënat që do të merren.

Shembulli 2: VLOOKUP sipas dy kritereve me tabelën që shihet në një fletë tjetër

Nëse keni nevojë të përditësoni tabelën kryesore (tabela kryesore) duke shtuar të dhëna nga tabela e dytë (tabela e kërkimit), e cila ndodhet në një fletë tjetër ose në një libër tjetër pune në Excel, atëherë mund të mbledhni vlerën e dëshiruar drejtpërdrejt në formulën që futni. në tabelën kryesore.

Si në shembullin e mëparshëm, do t'ju duhet një kolonë ndihmëse në tabelën e kërkimit me vlerat e kombinuara. Kjo kolonë duhet të jetë kolona më e majtë në diapazonin e kërkimit.

Pra formula me VPR mund të jetë kështu:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Këtu, kolonat B dhe C përmbajnë emrat e klientëve dhe emrat e produkteve, përkatësisht, dhe lidhjen Porosit!$A&$2:$D$2 përcakton një tabelë për të kërkuar në një fletë tjetër.

Për ta bërë formulën më të lexueshme, mund t'i jepni një emër diapazonit të pamjes dhe më pas formula do të duket shumë më e thjeshtë:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Që formula të funksionojë, vlerat në kolonën më të majtë të tabelës që po shikoni duhet të kombinohen saktësisht në të njëjtën mënyrë si në kriteret e kërkimit. Në figurën e mësipërme, ne kombinuam vlerat u2bu2band vendosëm një hapësirë ​​midis tyre, në të njëjtën mënyrë që duhet të bëni në argumentin e parë të funksionit (BXNUMX& "" & CXNUMX).

Mos harroni! funksion VPR i kufizuar në 255 karaktere, ai nuk mund të kërkojë një vlerë që është më shumë se 255 karaktere. Mbani parasysh këtë dhe sigurohuni që gjatësia e vlerës së dëshiruar të mos e kalojë këtë kufi.

Jam dakord që shtimi i një kolone ndihmëse nuk është zgjidhja më elegante dhe jo gjithmonë e pranueshme. Ju mund të bëni të njëjtën gjë pa kolonën ndihmëse, por kjo do të kërkonte një formulë shumë më komplekse me një kombinim funksionesh INDEKSI (INDEKS) dhe NDESHJE (ME E EKSPOZUAR).

Ne nxjerrim vlerat e 2-të, të 3-të, etj. duke përdorur VLOOKUP

Ju tashmë e dini atë VPR mund të kthejë vetëm një vlerë që përputhet, më saktë, të parën e gjetur. Por, çka nëse kjo vlerë përsëritet disa herë në grupin e parë, dhe ju dëshironi të nxirrni të dytin ose të tretën prej tyre? Po sikur të gjitha vlerat? Problemi duket i ndërlikuar, por zgjidhja ekziston!

Supozoni se një kolonë e tabelës përmban emrat e klientëve (Emri i klientit), dhe kolona tjetër përmban produktet (Produkti) që ata kanë blerë. Le të përpiqemi të gjejmë artikujt e dytë, të tretë dhe të katërt të blerë nga një klient i caktuar.

Mënyra më e lehtë është të shtoni një kolonë ndihmëse përpara kolonës Emri i Klientit dhe plotësoni atë me emrat e klientëve me numrin e përsëritjes së secilit emër, për shembull, John Doe1, John Doe2 etj. Ne do të bëjmë trukun me numërimin duke përdorur funksionin countif (COUNTIF), duke pasur parasysh që emrat e klientëve janë në kolonën B:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

Pas kësaj mund të përdorni funksionin normal VPRpër të gjetur rendin e kërkuar. Për shembull:

• Gjej 2-të artikull i porositur nga klienti Dan Brown:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• Gjej 3-të artikull i porositur nga klienti Dan Brown:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

Në fakt, mund të futni një referencë qelize si vlerë kërkimi në vend të tekstit, siç tregohet në figurën e mëposhtme:

Nëse jeni vetëm duke kërkuar 2-e përsëritje, mund ta bëni pa kolonën ndihmëse duke krijuar një formulë më komplekse:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

Në këtë formulë:

• 2 dollarë F$ – një qelizë që përmban emrin e blerësit (është e pandryshuar, ju lutemi vini re – lidhja është absolute);
• $ B $ – kolona Emri i Klientit;
• Table4 – Tabela juaj (ky vend mund të jetë edhe një varg i rregullt);
• $ C16 – qeliza fundore e tabelës ose diapazonit tuaj.

Kjo formulë gjen vetëm vlerën e dytë të përputhjes. Nëse keni nevojë të nxirrni përsëritjet e mbetura, përdorni zgjidhjen e mëparshme.

Nëse keni nevojë për një listë të të gjitha ndeshjeve - funksioni VPR ky nuk është ndihmës, pasi kthen vetëm një vlerë në të njëjtën kohë - periudhë. Por Excel ka një funksion INDEKSI (INDEX), i cili mund ta përballojë lehtësisht këtë detyrë. Si do të duket një formulë e tillë, do të mësoni në shembullin e mëposhtëm.

Merrni të gjitha përsëritjet e vlerës së dëshiruar

Siç u përmend më sipër VPR nuk mund të nxjerrë të gjitha vlerat e kopjuara nga diapazoni i skanuar. Për ta bërë këtë, ju nevojitet një formulë pak më komplekse, e përbërë nga disa funksione Excel, si p.sh INDEKSI (INDEKS), SMALL (E VOGLA) dhe ROW (LINJË)

Për shembull, formula më poshtë gjen të gjitha përsëritjet e vlerës nga qeliza F2 në intervalin B2:B16 dhe kthen rezultatin nga të njëjtat rreshta në kolonën C.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

Futni këtë formulë të grupit në qeliza të shumta ngjitur, siç janë qelizat F4: F8siç tregohet në figurën e mëposhtme. Numri i qelizave duhet të jetë i barabartë ose më i madh se numri maksimal i mundshëm i përsëritjeve të vlerës së kërkuar. Mos harroni të klikoni Ctrl + Shift + Enterpër të futur saktë formulën e grupit.

Nëse jeni të interesuar të kuptoni se si funksionon, le të zhytemi pak në detajet e formulës:

1 Pjesa:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 – krahasoni vlerën në qelizën F2 me secilën nga vlerat në diapazonin B2:B16. Nëse gjendet një përputhje, atëherë shprehja STRING(C2:C16)-1 kthen numrin e rreshtit përkatës (vlera -1 ju lejon të mos përfshini linjën e kokës). Nëse nuk ka ndeshje, funksioni IF (IF) kthen një varg bosh.

Rezultati i funksionit IF (IF) do të ketë një grup të tillë horizontal: {1,"",3,"",5,"","","","","","",12,"","",""}

2 Pjesa:

ROW()-3

СТРОКА()-3

Këtu funksioni ROW (LINE) vepron si një numërues shtesë. Meqenëse formula kopjohet në qelizat F4:F9, ne e zbresim numrin 3 nga rezultati i funksionit për të marrë vlerën 1 në qeli F4 (rreshti 4, zbrit 3) për të marrë 2 në qeli F5 (rreshti 5, zbrit 3) dhe kështu me radhë.

3 Pjesa:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

funksion SMALL (VOGLA) kthehet n-oh vlera më e vogël në grupin e të dhënave. Në rastin tonë, cili pozicion (nga më i vogli) për t'u kthyer përcaktohet nga funksioni ROW (LINE) (shih Pjesën 2). Pra, për një qelizë F4 funksion I VOGËL ({array},1) Kthim 1-të elementi i grupit (më i vogël), dmth 1. Për qelizën F5 Kthim 2-të elementi më i vogël në grup, domethënë 3, Etj

4 Pjesa:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

funksion INDEKSI (INDEX) thjesht kthen vlerën e një qelize specifike në një grup C2: C16. Për qelizën F4 funksion INDEX ($C$2:$C$16) do te kthehen mollëPër F5 funksion INDEX ($C$2:$C$16) do te kthehen Ëmbëlsirat' dhe kështu me radhë.

5 Pjesa:

IFERROR()

ЕСЛИОШИБКА()

Së fundi, ne vendosim formulën brenda funksionit IFERROR (IFERROR), sepse nuk ka gjasa të jeni të kënaqur me mesazhin e gabimit #AT (#N/A) nëse numri i qelizave në të cilat kopjohet formula është më i vogël se numri i vlerave të kopjuara në intervalin që shikohet.

XNUMXD kërkim sipas rreshtit dhe kolonës së njohur

Kryerja e një kërkimi XNUMXD në Excel përfshin kërkimin e një vlere sipas një numri të njohur rreshti dhe kolone. Me fjalë të tjera, ju po nxjerrni vlerën e qelizës në kryqëzimin e një rreshti dhe kolone të veçantë.

Pra, le të kthehemi në tabelën tonë dhe të shkruajmë një formulë me një funksion VPR, i cili do të gjejë informacione për koston e limonëve të shitur në muajin mars.

Ka disa mënyra për të kryer një kërkim XNUMXD. Shikoni opsionet dhe zgjidhni atë që ju përshtatet më së miri.

Funksionet VLOOKUP dhe MATCH

Ju mund të përdorni një sërë funksionesh VPR (VLOOKUP) dhe ME TE EKSPOZUAR (MATCH) për të gjetur vlerën në kryqëzimin e fushave Emri i Produktit (varg) dhe Muaj (kolona) e grupit në fjalë:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Formula e mësipërme është një funksion i rregullt VPR, e cila kërkon një përputhje të saktë të vlerës "Lemons" në qelizat A2 deri në A9. Por meqenëse nuk e dini se në cilën kolonë janë shitjet e marsit, nuk do të jeni në gjendje të vendosni numrin e kolonës për argumentin e funksionit të tretë. VPR. Në vend të kësaj, funksioni përdoret ME TE EKSPOZUARpër të përcaktuar këtë kolonë.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

E përkthyer në gjuhën njerëzore, kjo formulë do të thotë:

• Kërkojmë personazhet “Mar” – argument lookup_value (vlera_kërkimi);
• Duke kërkuar në qelizat nga A1 në I1 - argument grup_kërkuesish (grup_kërkimi);
• Kthimi i përputhjes së saktë - argumenti lloji i ndeshjes (lloji i ndeshjes).

Përdorim 0 në argumentin e tretë, ju thoni funksione ME TE EKSPOZUAR kërkoni vlerën e parë që përputhet saktësisht me vlerën që kërkoni. Kjo është e barabartë me vlerën I RREMË (FALSE) për argumentin e katërt VPR.

Kështu mund të krijoni një formulë kërkimi me dy drejtime në Excel, e njohur gjithashtu si kërkimi dydimensional ose kërkimi me dy drejtime.

Funksioni SUMPRODUCT

funksion SUMPRODUCT (SUMPRODUCT) kthen shumën e produkteve të vargjeve të zgjedhura:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

Funksionet INDEX dhe MATCH

Në artikullin tjetër do t'i shpjegoj këto funksione në detaje, kështu që tani për tani mund të kopjoni këtë formulë:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Vargjet e emërtuara dhe operatori i kryqëzimit

Nëse nuk jeni në të gjitha ato formula komplekse Excel, mund t'ju pëlqejë kjo mënyrë vizuale dhe e paharrueshme:

1. Zgjidhni tabelën, hapni skedën Formulat (Formulat) dhe klikoni Krijo nga Përzgjedhja (Krijoni nga përzgjedhja).
2. Kontrolloni kutitë Rreshti i sipërm (në vijën e mësipërme) dhe Kolona e majtë (në kolonën në të majtë). Microsoft Excel do t'i caktojë emrat diapazoneve nga vlerat në rreshtin e sipërm dhe në kolonën e majtë të tabelës suaj. Tani mund të kërkoni duke përdorur këta emra drejtpërdrejt pa krijuar formula.
3. Në çdo qelizë të zbrazët, shkruani =emri_rreshtit emri_kolona, për shembull si kjo:

   =Limon Mars

   … ose anasjelltas:

   = Mar Limon

   Mos harroni se emrat e rreshtave dhe kolonave duhet të ndahen me një hapësirë, e cila në këtë rast funksionon si operatori i kryqëzimit.

Kur futni një emër, Microsoft Excel do të shfaqë një këshillë veglash me një listë emrash që përputhen, ashtu si kur futni një formulë.

4. shtyp Shkruani dhe kontrolloni rezultatin

Në përgjithësi, cilado nga metodat e mësipërme që zgjidhni, rezultati i një kërkimi dydimensional do të jetë i njëjtë:

Përdorimi i shumëfishtë VLOOKUP në një formulë

Ndodh që tabela kryesore dhe tabela e kërkimit të mos kenë një kolonë të përbashkët, dhe kjo ju pengon të përdorni funksionin e zakonshëm VPR. Megjithatë, ekziston një tabelë tjetër që nuk përmban informacionin që na intereson, por ka një kolonë të përbashkët me tabelën kryesore dhe tabelën e kërkimit.

Le të hedhim një vështrim në shembullin e mëposhtëm. Ne kemi një tabelë kryesore me një kolonë SKU (i ri), ku dëshironi të shtoni një kolonë me çmimet përkatëse nga një tabelë tjetër. Përveç kësaj, ne kemi 2 tabela kërkimi. E para (tabela e kërkimit 1) përmban numra të përditësuar SKU (i ri) dhe emrat e produkteve, dhe e dyta (tabela e kërkimit 2) – emrat e produkteve dhe numrat e vjetër SKU (e vjetër).

Për të shtuar çmimet nga tabela e dytë e kërkimit në tabelën kryesore, duhet të kryeni një veprim të njohur si dyfishi VPR ose me fole VPR.

1. Shkruani një funksion VPR, e cila gjen emrin e produktit në tabelë Tabela e kërkimit 1përdorim SKU, si vlera e dëshiruar:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   Këtu New_SKU – varg i emërtuar $A:$B ne tavoline Tabela e kërkimit 1, 2 – kjo është kolona B, e cila përmban emrat e mallrave (shih foton më lart)

2. Shkruani një formulë për të futur çmimet nga një tabelë Tabela e kërkimit 2 bazuar në emrat e njohur të produkteve. Për ta bërë këtë, ngjitni formulën që keni krijuar më parë si vlerën e kërkimit për funksionin e ri VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   Këtu Çmimi – varg i emërtuar $A:$C ne tavoline Tabela e kërkimit 2, 3 është kolona C që përmban çmime.

Figura më poshtë tregon rezultatin e kthyer nga formula që kemi krijuar:

Zëvendësimi dinamik i të dhënave nga tabela të ndryshme duke përdorur VLOOKUP dhe INDIRECT

Së pari, le të sqarojmë se çfarë nënkuptojmë me shprehjen "Zëvendësimi dinamik i të dhënave nga tabela të ndryshme" për t'u siguruar që e kuptojmë saktë njëri-tjetrin.

Ka situata kur ka disa fletë me të dhëna të të njëjtit format dhe është e nevojshme të nxirret informacioni i nevojshëm nga një fletë e caktuar, në varësi të vlerës që futet në një qelizë të caktuar. Mendoj se është më e lehtë ta shpjegosh këtë me një shembull.

Imagjinoni që keni raporte shitjesh për disa rajone me të njëjtat produkte dhe në të njëjtin format. Ju dëshironi të gjeni shifrat e shitjeve për një rajon specifik:

Nëse keni vetëm dy raporte të tilla, atëherë mund të përdorni një formulë të thjeshtë të turpshme me funksione VPR и IF (IF) për të zgjedhur raportin e dëshiruar për të kërkuar:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

ku:

• $ 2 $ XNUMX është një qelizë që përmban emrin e produktit. Vini re se ne përdorim referenca absolute këtu për të shmangur ndryshimin e vlerës së kërkimit kur kopjoni formulën në qeliza të tjera.
• 3 dollarë është një qelizë me emrin e rajonit. Ne jemi duke përdorur një referencë absolute të kolonës dhe një referencë relative të rreshtit sepse planifikojmë ta kopjojmë formulën në qeliza të tjera në të njëjtën kolonë.
• FL_Sales и CA_Sales – emrat e tabelave (ose diapazoneve të emërtuara) që përmbajnë raportet përkatëse të shitjeve. Sigurisht, mund të përdorni emrat e zakonshëm të fletëve dhe referencat e gamës së qelizave, për shembull 'Fletë FL'!$A$3:$B$10, por vargjet e emërtuara janë shumë më të përshtatshme.

Megjithatë, kur ka shumë tabela të tilla, funksioni IF nuk është zgjidhja më e mirë. Në vend të kësaj, ju mund të përdorni funksionin INDIREKT (INDIRECT) për të kthyer gamën e dëshiruar të kërkimit.

Siç ndoshta e dini, funksioni INDIREKT përdoret për të kthyer një lidhje të dhënë nga një varg teksti, që është pikërisht ajo që na nevojitet tani. Pra, zëvendësoni me guxim në formulën e mësipërme shprehjen me funksionin IF për t'u lidhur me funksionin INDIREKT. Këtu është një kombinim VPR и INDIREKT punon shkëlqyeshëm me:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

ku:

• $ 2 $ XNUMX – kjo është një qelizë me emrin e produktit, është e pandryshuar për shkak të lidhjes absolute.
• 3 dollarë është qeliza që përmban pjesën e parë të emrit të rajonit. Në shembullin tonë, kjo FL.
• _Shitjet – pjesa e përbashkët e emrit të të gjitha vargjeve ose tabelave të emërtuara. Kur kombinohet me vlerën në qelizën D3, ajo formon emrin plotësisht të kualifikuar të diapazonit të kërkuar. Më poshtë janë disa detaje për ata që janë të rinj në funksion INDIREKT.

Si funksionojnë INDIRECT dhe VLOOKUP

Së pari, më lejoni t'ju kujtoj sintaksën e funksionit INDIREKT (INDIREKT):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

Argumenti i parë mund të jetë një referencë qelize (stili A1 ose R1C1), një emër vargu ose një varg teksti. Argumenti i dytë përcakton se çfarë stili lidhjeje përmban argumenti i parë:

• A1nëse argumenti është KODI I VËRTETË (E VËRTETË) ose jo e specifikuar;
• R1C1, Në qoftë se FAS E (I RREMË).

Në rastin tonë, lidhja ka stilin A1, kështu që mund të lini jashtë argumentin e dytë dhe të përqendroheni te i pari.

Pra, le të kthehemi te raportet tona të shitjeve. Nëse ju kujtohet, atëherë çdo raport është një tabelë e veçantë e vendosur në një fletë të veçantë. Që formula të funksionojë si duhet, duhet të emërtoni tabelat (ose vargjet) dhe të gjithë emrat duhet të kenë një pjesë të përbashkët. Për shembull, si kjo: CA_Sales, FL_Sales, TX_Sales e kështu me radhë. Siç mund ta shihni, "_Sales" është i pranishëm në të gjithë emrat.

funksion INDIREKT lidh vlerën në kolonën D dhe vargun e tekstit "_Sales", duke treguar kështu VPR në cilën tabelë të kërkoni. Nëse qeliza D3 përmban vlerën "FL", formula do të kërkojë tabelën FL_Sales, nëse "CA" - në tabelë CA_Sales dhe kështu me radhë.

Rezultati i funksioneve VPR и INDIREKT do të jetë si më poshtë:

Nëse të dhënat ndodhen në libra të ndryshëm Excel, atëherë duhet të shtoni emrin e librit përpara intervalit të emërtuar, për shembull:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Nëse funksioni INDIREKT i referohet një libri tjetër pune, ai libër pune duhet të jetë i hapur. Nëse është i mbyllur, funksioni do të raportojë një gabim. # REF! (#SSYL!).