Contents
Di beşa duyemîn a dersa me ya li ser fonksiyonê de VPR (VLOOKUP) li Excel, em ê çend mînakan analîz bikin ku dê ji we re bibin alîkar ku hûn hemî hêzê bi rê ve bibin VPR ji bo çareserkirina karên Excel-ê yên herî ambicioz. Nimûne texmîn dikin ku hûn berê xwedan zanînek bingehîn a ka ev taybetmendî çawa dixebite. Heke ne, dibe ku hûn bala xwe bidin ku hûn bi beşa yekem a vê dersê dest pê bikin, ku hevoksazî û karanîna bingehîn rave dike. VPR. Belê, em dest pê bikin.
Li Excel li gorî gelek pîvanan bigerin
Karî VPR di Excel de amûrek bi rastî hêzdar e ku ji bo lêgerînên ji bo nirxek taybetî di databasê de pêk tîne. Lêbelê, sînorek girîng heye - hevoksaziya wê dihêle hûn tenê nirxek bigerin. Ger hûn bixwazin li gorî şertên pirjimar bigerin çi? Hûn ê çareseriyê li jêr bibînin.
Mînak 1: Li gorî 2 pîvanên cihê bigerin
Bifikirin ku me navnîşek fermanan heye û em dixwazin bibînin Hejmara kelûpelan (Qty.), li ser bingeha du pîvanan - Navê xerîdar (Xerîdar) и Navê hilberê (Mal). Mesele ji ber vê yekê tevlihev e ku her yek ji kiriyaran çend celeb tiştan ferman dane, wekî ku ji tabloya jêrîn tê dîtin:
fonksiyona rêkûpêk VPR dê di vê senaryoyê de nexebite ji ber ku ew ê yekem nirxa ku ew dibîne ku bi nirxa lêgerînê ya hatî dayîn vegerîne. Mînakî, heke hûn dixwazin mîqdara tiştek zanibin şîrîn'ji hêla kirrûbir ve hatî ferman kirin Jeremy Hill, formula jêrîn binivîse:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- ev formula dê encamê vegerîne 15li gorî hilberê Apples, ji ber ku ew nirxa yekem e ku li hev dike.
Rêbazek hêsan heye - stûnek pêvek biafirînin ku tê de hemî pîvanên xwestinê berhev bikin. Di mînaka me de, ev stûn in Navê xerîdar (Xerîdar) и Navê hilberê (Mal). Ji bîr nekin ku stûna hevgirtî divê her gav stûna herî çepê be di rêza lêgerînê de, ji ber ku ew stûna çepê ye ku fonksiyonê dike. VPR dema ku li nirxek digere xuya dike.
Ji ber vê yekê, hûn stûnek alîkar li tabloyê zêde dikin û formula jêrîn li ser hemî şaneyên wê kopî bikin: =B2&C2. Heke hûn dixwazin rêzik bêtir were xwendin, hûn dikarin nirxên hevgirtî bi valahiyek veqetînin: =B2&» «&C2. Piştî vê yekê, hûn dikarin formula jêrîn bikar bînin:
=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;ЛОЖЬ)
Hucre li ku ye B1 nirxa hevgirtî ya argumanê dihewîne nirxa_dîtinê (lookup_value) û 4 - Argument col_index_num (hejmara_stûnê), ango hejmara stûna ku daneya ku divê were standin tê de ye.
Mînak 2: VLOOKUP bi du pîvanan bi tabloya ku li ser pelek din tê dîtin
Heke hûn hewce ne ku bi lê zêdekirina daneyan ji tabloya duyemîn (tabloya lêgerînê), ku li ser pelek din an di pirtûkek xebata Excel-ê ya din de ye, tabloya sereke (tabloya sereke) nûve bikin, wê hingê hûn dikarin nirxa xwestinê rasterast di formula ku hûn têxin de berhev bikin. nav maseya sereke.
Mîna ku di mînaka berê de, hûn ê di tabloya Lêgerînê de bi nirxên hevgirtî re stûnek alîkar hewce bikin. Divê ev stûn di rêza lêgerînê de stûna herî çepê be.
Ji ber vê yekê formula bi VPR dikare bi vî rengî be:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Li vir, stûnên B û C, bi rêzê, navên xerîdar û navên hilber û girêdanê vedigirin Ferman!$A&$2:$D$2 tabloyek diyar dike ku di pelek din de lê bigere.
Ji bo ku formula bêtir were xwendin, hûn dikarin navek rêza dîtinê bidin, û dûv re formula dê pir hêsan xuya bike:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Ji bo ku formula bixebite, divê nirxên di stûna herî çepê ya tabloya ku hûn lê dinêrin de bi heman awayê ku di pîvanên lêgerînê de têne hev kirin. Di jimareya jor de, me nirxên u2bu2band di navbera wan de cîhek danî, bi heman rengî ku hûn hewce ne ku di argumana yekem a fonksiyonê de bikin (BXNUMX& "" & CXNUMX).
Bîrveanîn! Karî VPR bi 255 tîpan ve sînorkirî ye, ew nikare li nirxek ku ji 255 tîpan zêdetir dirêj e bigere. Vê yekê di hişê xwe de bigirin û pê ewle bin ku dirêjahiya nirxa xwestî ji vê sînor derbas nebe.
Ez razî me ku lê zêdekirina stûnek alîkar ne çareseriya herî elegant û ne her gav pejirandî ye. Hûn dikarin bêyî stûna arîkar heman tiştî bikin, lê ew ê bi tevhevkirina fonksiyonan re formula pir tevlihevtir hewce bike NAVEROK (INDEX) û WEKWÎ (BÊTIR DERXISTIN).
Em bi karanîna VLOOKUP-ê nirxên 2, 3, hwd derdixin
Jixwe hûn vê yekê dizanin VPR dikare tenê yek nirxek lihevhatî vegerîne, bi rastî, ya yekem hatî dîtin. Lê heke ev nirx di rêzika hatî dîtin de çend caran were dubare kirin, û hûn dixwazin 2 an 3-ya wan derxînin? Heke hemî nirx çi bikin? Pirsgirêk tevlihev xuya dike, lê çareserî heye!
Bifikirin ku stûnek tabloyê navên xerîdar (Navê Xerîdar) heye, û stûna din jî hilberên (Berhem) ku wan kirî hene. Ka em hewl bidin ku tiştên 2mîn, 3. û 4emîn ên ku ji hêla xerîdarek diyarkirî ve hatine kirîn bibînin.
Rêya herî hêsan ev e ku meriv stûnek alîkar berî stûnê zêde bike navê mişterî û wê bi navên xerîdar bi hejmara dubarekirina her navekî tije bikin, mînakî, John Doe1, John Doe2 hwd.. Em ê bi jimartinê re bi karanîna fonksiyonê fêlbazî bikin COUNTIF (COUNTIF), ji ber ku navên xerîdar di stûna B de ne:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Piştî vê yekê hûn dikarin fonksiyona normal bikar bînin VPRku emrê pêwîst bibînin. Bo nimûne:
- Dîtin 2-ê babete ji aliyê mişterî emir Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Dîtin 3-ê babete ji aliyê mişterî emir Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Bi rastî, hûn dikarin li şûna nivîsê, wekî ku di jimareya jêrîn de tê xuyang kirin, referansek hucreyê wekî nirxa lêgerînê têkevin:
Ger hûn tenê lê digerin 2-e dubarekirin, hûn dikarin wê bêyî stûna alîkar bi afirandina formularek tevlihevtir bikin:
=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;ИСТИНА);"")
Di vê formula de:
- $F$2 - hucreyek ku navê kiryarê tê de heye (ew neguherî ye, ji kerema xwe bala xwe bidin - girêdan mutleq e);
- $ B $ - stûn navê mişterî;
- Table4 - Tabloya we (ev cîh di heman demê de dibe rêzek birêkûpêk jî);
- $ C16 - hucreya dawî ya sifrê an qada we.
Ev formula tenê nirxa hevberdana duyemîn dibîne. Heke hûn hewce ne ku dubareyên mayî derxînin, çareseriya berê bikar bînin.
Heke hûn hewceyê navnîşek hemî maçan hewce ne - fonksiyon VPR ev ne arîkar e, ji ber ku ew di her carê de tenê nirxek vedigerîne - serdem. Lê fonksiyonek Excel heye NAVEROK (INDEX), ku bi hêsanî dikare bi vî karî re mijûl bibe. Formulek wusa dê çawa xuya bike, hûn ê di mînaka jêrîn de fêr bibin.
Hemî dubareyên nirxa xwestinê bistînin
Wek ku jor gotiye VPR nekare hemî nirxên dubare ji rêza şopandin derxe. Ji bo vê yekê, hûn hewce ne ku formula hinekî tevlihevtir, ku ji çend fonksiyonên Excel pêk tê, wek mînak NAVEROK (NAVEROK), BIÇÛK (BIÇÛK) û DOR (XET)
Mînakî, formula jêrîn hemî dubareyên nirxê ji hucreya F2 di rêza B2:B16 de dibîne û encamê ji heman rêzên stûna C vedigerîne.
{=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));"")}
Vê formula arrayiyê têxe nav gelek şaneyên cîran, wek şaneyan F4: F8wek ku di wêneya jêrîn de tê nîşandan. Hejmara şaneyan divê ji ya herî zêde ya dubarekirina nirxa lêgerînê wekhev an mezintir be. Ji bîr nekin ku bikirtînin Ctrl + Shift + Enterji bo ku formula array rast têkevin.
Ger hûn bala we dikişînin ku hûn fêm bikin ka ew çawa dixebite, bila em hinekî li hûrguliyên formulê bigerin:
Part 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - Nirxa di hucreya F2 de bi her yek ji nirxên di rêza B2:B16 de bidin hev. Ger lihevhatinek were dîtin, wê hingê vegotin STRING(C2:C16)-1 hejmara rêza têkildar vedigerîne (nirx -1 destûrê dide te ku hûn xêza sernivîsê negirin). Ger maçên tune, fonksiyonê IF (IF) rêzek vala vedigerîne.
Encama fonksiyonê IF (HER) dê rêzek horîzontal hebe: {1,"",3,"",5,"","","","","","",12,"","",""}
Part 2:
ROW()-3
СТРОКА()-3
Li vir fonksiyonê DOR (LINE) wekî jimarvanek zêde tevdigere. Ji ber ku formula di hucreyên F4:F9 de tê kopî kirin, em hejmarê jê dikin 3 ji encama fonksiyonê nirxê bistînin 1 di hucreyê de F4 (xêza 4, jê 3) ji bo bidestxistina 2 di hucreyê de F5 (xêza 5, jê 3) û hwd.
Part 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Karî BIÇÛK (BIÇÛK) vedigere n-oh nirxa herî piçûk di rêza daneyê de. Di rewşa me de, kîjan pozîsyon (ji ya herî piçûk) vegere ji hêla fonksiyonê ve tê destnîşankirin DOR (XÊT) (binihêre Beş 2). Ji ber vê yekê, ji bo hucreyek F4 karî PIÇÛK({array},1) vegerin 1-ê (herî biçûk) hêmana array, ango 1. Ji bo hucreyê F5 vegerin 2-ê hêmana herî piçûk di rêzê de, ew e 3, Hwd.
Part 4:
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))
Karî NAVEROK (INDEX) bi tenê nirxa şaneyek taybetî ya di rêzikekê de vedigerîne C2:C16. Ji bo hucreyê F4 karî INDEX($C$2:$C$16) vegerin ApplesBo F5 karî INDEX($C$2:$C$16) vegerin şîrîn' wate ya vê çîye.
Part 5:
IFERROR()
ЕСЛИОШИБКА()
Di dawiyê de, em formula têxin hundurê fonksiyonê IFERROR (IFERROR), ji ber ku hûn ne mimkûn e ku hûn ji peyama xeletiyê kêfxweş bibin #BA (#N/A) heke hejmara şaneyên ku formula tê de tê kopî kirin ji hejmara nirxên dubare yên di rêza ku tê dîtin de kêmtir be.
XNUMXD lêgerîna rêz û stûna naskirî
Pêkanîna XNUMXD lêgerînek li Excel lêgerîna nirxek ji hêla hejmarek rêz û stûnek naskirî ve vedihewîne. Bi gotinek din, hûn nirxa hucreyê li hevberdana rêzek û stûnek taybetî derdixin.
Ji ber vê yekê, em werin ser tabloya xwe û bi fonksiyonek formulek binivîsin VPR, ku dê agahdariya li ser lêçûna leymûnên ku di meha Adarê de têne firotin bibînin.
Gelek awayên ku lêgerîna XNUMXD-ê pêk bînin hene. Vebijarkan binihêrin û ya ku ji we re çêtirîn e hilbijêrin.
fonksiyonên VLOOKUP û MATCH
Hûn dikarin komek fonksiyonan bikar bînin VPR (VLOOKUP) û PÊKIRÎ DERKETIN (MATCH) ji bo dîtina nirxê li hevberdana zeviyan Navê hilberê (têl) û Meh (stûna) rêza pirsê:
=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 jorîn fonksiyonek birêkûpêk e VPR, ku di şaneyên A2 heya A9 de li hevberek rastîn a nirxa "Lemon" digere. Lê ji ber ku hûn nizanin firotanên Adarê li kîjan stûnê ne, hûn ê nikaribin ji bo argumana fonksiyona sêyemîn jimareya stûnê saz bikin. VPR. Di şûna wê de, fonksiyon tê bikar anîn PÊKIRÎ DERKETINji bo diyarkirina vê stûnê.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Bi zimanê mirovî hatiye wergerandin, ev formula tê wateya:
- Em li karakterên "Mar" - arguman digerin nirxa_dîtinê (lookup_value);
- Di hucreyan de ji A1 heya I1 digere - arguman lêgerîn_array (lookup_array);
- Lihevhatina rast vedigere - arguman match_type (maç_cure).
bikaranîna 0 di argumana sêyemîn de, hûn fonksiyonan dibêjin PÊKIRÎ DERKETIN li nirxa yekem ku tam bi nirxa ku hûn lê digerin bigerin. Ev bi nirxê wekhev e ŞAŞ (FALSE) ji bo argumana çaremîn VPR.
Bi vî rengî hûn dikarin formula lêgerîna du-alî li Excel biafirînin, ku wekî lêgerîna du-alî an lêgerîna dualî jî tê zanîn.
fonksiyona SUMPRODUCT
Karî SUMPRODUCT (SUMPRODUCT) berhevoka hilberên rêzikên hilbijartî vedigerîne:
=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)
fonksiyonên INDEX û MATCH
Di gotara din de ez ê van fonksiyonan bi hûrgulî rave bikim, ji ber vê yekê heya niha hûn dikarin tenê vê formula kopî bikin:
=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))
Rêjeyên binavkirî û operatorê hevberdanê
Heke hûn ne di nav wan hemî formûlên tevlihev ên Excel de ne, dibe ku hûn vî awayê dîtbar û bîranîn hez bikin:
- Tabloyê hilbijêrin, tabloyê vekin Formulas (Formulên) û bikirtînin Ji Hilbijartinê çêbikin (Ji hilbijartinê çêbikin).
- Sindoqan kontrol bikin Rêza jorîn (li ser xeta jorîn) û Stûna çepê (di stûna çepê de). Microsoft Excel dê navên rêzikan ji nirxên di rêza jorîn û stûna çepê ya pelgeya we de destnîşan bike. Naha hûn dikarin rasterast bi karanîna van navan bêyî afirandina formulan bigerin.
- Di her hucreyek vala de, binivîse =row_name column_name, bo nimûne bi vî rengî:
=Leymûn Mar
… an berevajî:
=Mar Lemons
Bînin bîra xwe ku navên rêz û stûnan divê ji hêla valahiyek ve bêne veqetandin, ku di vê rewşê de mîna operatora hevberdanê dixebite.
Gava ku hûn navek binivîsin, Microsoft Excel dê amûrek bi navnîşek navên lihevhatî nîşan bide, mîna gava ku hûn formulek têkevin.
- Rojnamevanî Derbasbûn û encamê kontrol bikin
Bi gelemperî, kîjan ji rêbazên jorîn hûn hilbijêrin, encama lêgerînek du-alî dê yek be:
Di yek formula de gelek VLOOKUP bikar bînin
Wusa diqewime ku tabloya sereke û tabloya lêgerînê yek stûnek hevpar tune, û ev yek ji we re nahêle ku hûn fonksiyona asayî bikar bînin. VPR. Lêbelê, tabloyek din heye ku agahdariya ku em jê re eleqedar in nahewîne, lê stûnek hevpar bi tabloya sereke û tabloya lêgerînê re heye.
Ka em li mînaka jêrîn binêrin. Me tabloyek sereke ya bi stûnek heye SKU (nû), ku hûn dixwazin ji tabloyek din stûnek bi bihayên têkildar lê zêde bikin. Wekî din, me 2 tabloyên lêgerînê hene. Yekem (tabloya lêgerînê 1) hejmarên nûvekirî hene SKU (nû) û navên hilberê, û ya duyemîn (tabloya lêgerînê 2) - navên hilber û hejmarên kevn SKU (kevn).
Ji bo ku bihayên ji tabloya lêgerîna duyemîn li tabloya sereke zêde bikin, divê hûn çalakiyek ku wekî ducar tê zanîn pêk bînin VPR an hêlîn VPR.
- Fonksiyonek binivîse VPR, ku navê hilberê di tabloyê de dibîne Tabloya lêgerînê 1bi bikaranîna SKU, wekî nirxa xwestinê:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)vir New_SKU - range bi navê $A:$B di sifrê de Tabloya lêgerînê 1, 2 - ev stûna B ye, ku navên tiştan tê de hene (li wêneya li jor binêre)
- Formulek binivîsin ku bihayên ji tabloyek têxin Tabloya lêgerînê 2 li ser bingeha navên hilberên naskirî. Ji bo kirina vê, formula ku we berê afirandiye wekî nirxa lêgerînê ya fonksiyona nû bişopînin VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)vir Biha - range bi navê $A:$C di sifrê de Tabloya lêgerînê 2, 3 stûna C ye ku bihayan vedihewîne.
Nîgara jêrîn encama ku ji hêla formula me ve hatî vegerandin nîşan dide:
Veguheztina dînamîkî ya daneyên ji tabloyên cihêreng bi karanîna VLOOKUP û INDIRECT
Pêşî, bila em ronî bikin ka mebesta me bi gotina "Veguheztina dînamîkî ya daneyan ji tabloyên cihêreng" çi ye da ku em rast ji hev fêm bikin.
Rewş hene ku çend pelên bi daneyên heman formatê hene, û pêdivî ye ku li gorî nirxa ku di hucreyek diyarkirî de tête navnîş kirin agahdariya pêwîst ji pelek diyarkirî derxînin. Ez difikirim ku bi mînakek ravekirina vê yekê hêsantir e.
Bifikirin ku we raporên firotanê ji bo çend deveran bi heman hilberan û di heman formatê de hene. Hûn dixwazin ji bo herêmek taybetî hejmarên firotanê bibînin:
Ger we tenê du raporên weha hene, wê hingê hûn dikarin bi fonksiyonan re formulakek hêsan a şermî bikar bînin VPR и IF (IF) ji bo hilbijartina rapora xwestî ya lêgerînê:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
li ku derê:
- $D$2 şaneyek e ku navê berhemê tê de ye. Bala xwe bidinê ku em li vir referansên bêkêmasî bikar tînin da ku dema ku formula li şaneyên din kopî dikin, nirxa lêgerînê neguherînin.
- $D3 şaneyek bi navê herêmê ye. Em referansek stûnek bêkêmasî û referansek rêzek têkildar bikar tînin ji ber ku em plan dikin ku formula di heman stûnê de li şaneyên din kopî bikin.
- FL_Sales и CA_Sales - Navên tabloyan (an rêzikên bi nav) yên ku raporên firotanê yên têkildar hene. Bê guman, hûn dikarin, mînakî, navên pelan û referansên rêza hucreyê bikar bînin 'FL Sheet'!$A$3:$B$10, lê rêzikên binavkirî pir rehettir in.
Lêbelê, gava ku gelek tabloyên weha hene, fonksiyon IF ne çareseriya herî baş e. Di şûna wê de, hûn dikarin fonksiyonê bikar bînin NADIREKT (NEDIREKT) ji bo vegerandina qada lêgerînê ya xwestî.
Wekî ku hûn dizanin, fonksiyonê NADIREKT ji bo vegerandina lînka ku ji hêla rêzika nivîsê ve hatî dayîn vegere, ku tam ya ku em niha hewce ne tê bikar anîn. Ji ber vê yekê, bi wêrekî di formula jorîn de îfadeyê bi fonksiyonê veguherînin IF ji bo girêdana bi fonksiyonê NADIREKT. Li vir tevliheviyek heye VPR и NADIREKT baş dixebite bi:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
li ku derê:
- $D$2 - ev şaneyek bi navê hilberê ye, ji ber girêdana mutleq nayê guhertin.
- $D3 şaneya ku beşa yekem a navê herêmê tê de ye. Di mînaka me de, ev FL.
- _Firotinê - beşa hevpar a navê hemî rêze an tabloyên binavkirî. Dema ku bi nirxa di hucreya D3 de were hev kirin, ew navek bi tevahî jêhatî ya rêza pêwîst pêk tîne. Li jêr hin hûrgulî ji bo kesên ku nû di fonksiyonê de ne hene NADIREKT.
Çawa INDIRECT û VLOOKUP dixebitin
Pêşîn, bila ez hevoksaziya fonksiyonê bînim bîra we NADIREKT (NEDIREKT):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Argumana yekem dikare referansek hucreyê (şêweya A1 an R1C1), navek rêzek, an rêzek nivîsê be. Argumana duyemîn diyar dike ka kîjan şêwaza girêdanê di argumana yekem de heye:
- A1eger arguman e KODA RAST (RAST) an ne diyar;
- R1C1, heke FAS E (ŞAŞ).
Di rewşa me de, girêdan xwediyê şêwazê ye A1, ji ber vê yekê hûn dikarin argumana duyemîn bihêlin û li ser yekem bisekinin.
Ji ber vê yekê em vegerin ser raporên firotanên xwe. Ger tê bîra we, wê hingê her rapor tabloyek cihê ye ku li ser kaxezek cihê ye. Ji bo ku formula bi rêkûpêk bixebite, divê hûn navên tabloyên xwe (an rêzeçavên) bi nav bikin, û hemî nav divê beşek hevbeş bin. Mînakî, bi vî rengî: CA_Sales, FL_Sales, TX_Sales wate ya vê çîye. Wekî ku hûn dibînin, "_Sales" di hemî navan de heye.
Karî NADIREKT nirxa stûna D û rêzika nivîsê "_Sales" girêdide, bi vî rengî vedibêje VPR di kîjan tabloyê de bigere. Ger hucreya D3 nirxa "FL" heye, formula dê li tabloyê bigere FL_Sales, heke "CA" - di tabloyê de CA_Sales wate ya vê çîye.
Encama fonksiyonan VPR и NADIREKT dê wiha be:
Ger dane di pirtûkên Excel-ê yên cihêreng de cih digirin, wê hingê hûn hewce ne ku navê pirtûkê berî navça binavkirî lê zêde bikin, mînakî:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Ger fonksiyona NADIREKT pirtûkek din a xebatê vedibêje, divê ew pirtûka xebatê vekirî be. Ger girtî be, fonksiyon dê xeletiyek rapor bike. #REF! (#SSYL!).