Yepamberi VLOOKUP Mienzaniso: Multi-Criteria Kutsvaga

Muchikamu chechipiri chedzidziso yedu pane basa VPR (VLOOKUP) muExcel, tichaongorora mienzaniso mishoma ichakubatsira kutungamira masimba ese VPR kugadzirisa anonyanya kushuvira Excel mabasa. Mienzaniso inofunga kuti unotova neruzivo rwekutanga rwekuti chinhu ichi chinoshanda sei. Kana zvisina kudaro, unogona kufarira kutanga nechikamu chekutanga chedzidziso iyi, inotsanangura masintakisi uye mashandisirwo ekutanga. VPR. Zvakanaka, ngatitangei.

• Tsvaga muExcel nemaitiro akawanda
• Isu tinobvisa iyo yechipiri, 2rd, nezvimwewo tsika tichishandisa VLOOKUP
• Dzokorora zvese zvakadzokororwa kukosha kwaunoda
• XNUMXD kutsvaga nemutsara unozivikanwa uye koramu
• Kushandisa akawanda maVLOOKUP mune imwe fomula
• Dynamic kutsiva data kubva kumatafura akasiyana

Tsvaga muExcel nemaitiro akawanda

basa VPR muExcel chishandiso chine simba chaizvo chekuita kutsvaga kwehumwe kukosha mudhatabhesi. Nekudaro, pane yakakosha kudzikisira - syntax yayo inobvumidza iwe kutsvaga kukosha kumwe chete. Ko kana uchida kutsvaga nemaitiro akawanda? Iwe uchawana mhinduro pazasi.

Muenzaniso 1: Tsvaga nemaitiro maviri akasiyana

Ngatitii tine runyoro rwemaodha uye tinoda kuwana Huwandu hwezvinhu (Qty.), zvichibva pamaitiro maviri - Zita remutengi (Mutengi) uye Chigadzirwa zita (Chigadzirwa). Nyaya yacho yakaomeswa nenyaya yekuti mumwe nemumwe wevatengi akaodha marudzi akati wandei ezvigadzirwa, sezvinoonekwa patafura pazasi:

basa renguva dzose VPR haishande mune iyi mamiriro nekuti ichadzosa kukosha kwekutanga kwainowana kunoenderana neyakapihwa yekutarisa kukosha. Semuenzaniso, kana iwe uchida kuziva huwandu hwechinhu Maswiti'zvakarairwa nemutengi Jeremy Hill, nyora fomula inotevera:

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

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

- iyi fomula ichadzosera mhedzisiro 15zvinoenderana nechigadzirwa Apples, nekuti ndiyo yekutanga kukosha inofanana.

Pane iri nyore workaround - gadzira imwe koramu yekusanganisa ese anodiwa maitiro. Mumuenzaniso wedu, aya ndiwo makoramu Zita remutengi (Mutengi) uye Chigadzirwa zita (Chigadzirwa). Usakanganwe kuti koramu yakabatanidzwa inofanira kugara iriyokuruboshwe pachikamu chekutsvaga, sezvo iriyo mbiru yekuruboshwe iyo basa racho. VPR anotarisa kumusoro kana achitsvaga kukosha.

Saka, iwe unowedzera yebetsero koramu patafura uye kukopa inotevera fomula pamusoro pemasero ayo ese: =B2&C2. Kana iwe uchida kuti tambo iverengeke zvakanyanya, unogona kupatsanura iyo yakasanganiswa tsika nenzvimbo: =B2&» «&C2. Mushure meizvozvo, unogona kushandisa fomula inotevera:

=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;ЛОЖЬ)

Sero ririkupi B1 ine hukoshi hwakabatana hwenharo lookup_value ( lookup_value) uye 4 – Nharo col_index_num (column_number), kureva nhamba yekoramu ine data rinozotorwa.

Muenzaniso 2: VLOOKUP nemaitiro maviri ane tafura iri kutariswa pane rimwe pepa

Kana iwe uchida kugadzirisa tafura huru (Main table) nekuwedzera data kubva patafura yechipiri (Tarisa tafura), iri pane rimwe pepa kana mune rimwe bhuku rebasa reExcel, ipapo unogona kuunganidza kukosha kwaunoda zvakananga mufomula yaunoisa. mu main table.

Semumuenzaniso wapfuura, iwe unozoda koramu yekubatsira muTarisa tafura ine yakasanganiswa kukosha. Koramu iyi inofanirwa kunge iri yekuruboshwe padanho rekutsvaga.

Saka formula ne VPR zvinogona kuva seizvi:

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

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

Pano, makoramu B neC ane mazita evatengi uye mazita echigadzirwa, zvichiteerana, uye chinongedzo Maodha!$A&$2:$D$2 inotsanangura tafura yekutarisa mune rimwe pepa.

Kuita kuti fomula iwedzere kuverengeka, unogona kupa iro renji rezita zita, uye ipapo fomura yacho ichaita seyakapusa:

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

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

Kuti fomula ishande, kukosha kuri kuruboshwe kwetafura yauri kutarisa kunofanirwa kusanganiswa nenzira imwecheteyo senge mune yekutsvaga maitiro. Mumufananidzo uri pamusoro, takabatanidza kukosha u2bu2band kuisa nzvimbo pakati pavo, nenzira imwechete yaunofanirwa kuita munharo yekutanga yebasa (BXNUMX& "" & CXNUMX).

Rangarira! basa VPR inogumira kumabhii 255, haikwanise kutsvaga kukosha kunodarika 255 mavara kureba. Chengetedza izvi mupfungwa uye iva nechokwadi chokuti urefu hwehuwandu hunodiwa haupfuuri muganhu uyu.

Ini ndinobvuma kuti kuwedzera koramu yekubatsira haisi iyo yakanyanya kunaka uye haisi nguva dzose inogamuchirwa mhinduro. Iwe unogona kuita zvimwe chete pasina mubatsiri mbiru, asi izvo zvingada yakanyanya kuomarara formula ine musanganiswa wemabasa INDEX (INDEX) uye MATCH (ZVIMWE ZVINOFUNDURWA).

Isu tinobvisa iyo yechipiri, 2rd, nezvimwewo tsika tichishandisa VLOOKUP

Munozviziva izvozvo VPR inogona kudzosa kukosha kumwe chete kunoenderana, zvakanyanya, yekutanga yawanikwa. Asi ko kana kukosha uku kukadzokororwa kakawanda muchirongwa chakatariswa, uye iwe uchida kubvisa yechipiri kana yechitatu yavo? Zvakadini kana zvose zvinokosha? Dambudziko rinoita serakaoma, asi mhinduro iripo!

Ngatiti imwe koramu yetafura ine mazita evatengi (Zita reMutengi), uye imwe koramu ine zvigadzirwa (Chigadzirwa) chavakatenga. Ngatiedzei kutsvaga 2nd, 3rd uye 4th zvinhu zvakatengwa nemutengi wakapihwa.

Nzira iri nyore ndeyekuwedzera koramu yekubatsira pamberi pekoramu zita remutengi uye uzadze nemazita evatengi nenhamba yekudzokorora yezita rega rega, semuenzaniso, John Doe1, John Doe2 etc. Tichaita hunyengeri nekuverenga tichishandisa basa COUNTIF (COUNTIF), zvakapihwa kuti mazita evatengi ari mukoramu B:

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

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

Mushure maizvozvo unogona kushandisa zvakajairika basa VPRkuti uwane kurongeka kunodiwa. Semuyenzaniso:

• wana 2-th chinhu chakarairwa nemutengi Dan Brown:

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

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

• wana 3-th chinhu chakarairwa nemutengi Dan Brown:

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

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

Muchokwadi, iwe unogona kuisa sero referensi sekukosha kwekutarisa panzvimbo yemavara, sezvakaratidzwa mumufananidzo unotevera:

Kana iwe uchitsvaga chete 2-e kudzokorora, iwe unogona kuzviita pasina iyo yekubatsira koramu nekugadzira yakanyanya kuomarara formula:

=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;ИСТИНА);"")

Mune iyi formula:

• $F$2 - sero rine zita remutengi (haina kuchinjika, ndapota cherechedza - chinongedzo chakazara);
• $B $ – mbiru zita remutengi;
• Tafura4 - Tafura yako (iyi nzvimbo inogona zvakare kuve yakajairwa renji);
• $C16 - iyo yekupedzisira sero yetafura yako kana renji.

Iyi fomula inowana chete yechipiri kukosha kwekufananidza. Kana iwe uchida kubvisa kudzokorora kwasara, shandisa mhinduro yapfuura.

Kana iwe uchida rondedzero yemachisi ese - basa VPR iyi haisi mubatsiri, sezvo inongodzosera imwe kukosha panguva - nguva. Asi Excel ine basa INDEX (INDEX), iyo inogona kubata nebasa iri nyore. Kuti fomula yakadaro ichaita sei, iwe uchadzidza mumuenzaniso unotevera.

Dzokorora zvese zvakadzokororwa kukosha kwaunoda

Sezvambotaurwa pamusoro apa VPR haikwanise kuburitsa ese akateedzera kukosha kubva kune yakaongororwa renji. Kuti uite izvi, iwe unoda yakawedzera kuomarara formula, inogadzirwa akati wandei Excel mabasa, akadai INDEX (INDEX), KUSVIRA (DUKU) uye mutsara (MUTSARA)

Semuenzaniso, fomula iri pazasi inowana kudzokororwa kwese kweukoshi kubva musero F2 muchikamu B2:B16 uye inodzosa mhedzisiro kubva mumitsetse yakafanana mukoramu 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));"")}

Pinda iyi fomula mumaseru akawanda ari pedyo, semaseru F4: f8sezvinoratidzwa mumufananidzo uri pasi apa. Huwandu hwemasero hunofanirwa kuenzana kana kuti hunopfuura huwandu hunogoneka hwedzokororo yeukoshi hwatsvaga. Usakanganwa kudzvanya Ctrl + Shift + Pindakuti uise array formula nenzira kwayo.

Kana iwe uchifarira kunzwisisa kuti inoshanda sei, ngatinyure mukati meiyo fomula zvishoma:

Chikamu 1:

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

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

$F$2=B2:B16 - enzanisa kukosha muchitokisi F2 neimwe yemhando dzemhando B2: B16. Kana machisi awanikwa, ipapo chirevo STRING(C2:C16)-1 inodzorera nhamba yemutsara unoenderana (value -1 inokubvumira kuti usasanganise mutsara wemusoro). Kana pasina machisi, basa IF (IF) inodzosera tambo isina chinhu.

Mhedzisiro yebasa IF (IF) pachave neyakachinjika array: {1,"",3,"",5,"","","","","","",12,"","",""}

Chikamu 2:

ROW()-3

СТРОКА()-3

Heino basa mutsara (LINE) inoshanda seyekuwedzera counter. Sezvo fomula yacho ichikopwa mumasero F4: F9, tinobvisa nhamba 3 kubva pamhedzisiro yebasa kuti uwane kukosha 1 muchitokisi F4 (mutsara 4, kubvisa 3) kuwana 2 muchitokisi F5 (mutsara 5, kubvisa 3) zvichingodaro.

Chikamu 3:

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

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

basa KUSVIRA (DUKU) inodzoka n-o iyo diki kukosha mune data array. Muchiitiko chedu, nzvimbo ipi (kubva padiki) kudzoka inotariswa nebasa mutsara (MUTSARA) (ona Chikamu 2). Saka, kune sero F4 basa DIKI({kuranga},1) inodzoka 1-th (smallest) array element, kureva 1. Yesero F5 inodzoka 2-th chinhu chidiki pane zvese muhurongwa, kureva 3, Etc.

Chikamu 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))

basa INDEX (INDEX) inongodzosa kukosha kwesero rakati muhurongwa C2:C16. Yesero F4 basa INDEX($C$2:$C$16) vachadzoka ApplesFor F5 basa INDEX($C$2:$C$16) vachadzoka Maswiti' zvichingoenda zvakadaro.

Chikamu 5:

IFERROR()

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

Pakupedzisira, tinoisa chimiro mukati mebasa IFERROR (IFERROR), nekuti haungafarire meseji yekukanganisa #AT (#N/A) kana nhamba yemasero anokopwa fomula iri pasi pehuwandu hwezvakapetwa hunhu muhuwandu huri kutariswa.

XNUMXD kutsvaga nemutsara unozivikanwa uye koramu

Kuita XNUMXD yekutsvaga muExcel kunosanganisira kutsvaga kukosha nemutsara unozivikanwa uye nhamba yekoramu. Mune mamwe mazwi, uri kutora kukosha kwesero pamharadzano yeimwe mutsara uye koramu.

Saka, ngatitendeukire kutafura yedu uye tinyore fomula ine basa VPR, iyo ichawana ruzivo pamusoro pemutengo wemarimu anotengeswa muna March.

Pane nzira dzinoverengeka dzekutsvaga XNUMXD yekutsvaga. Tarisa sarudzo uye sarudza iyo inokodzera iwe zvakanyanya.

VLOOKUP uye MATCH mabasa

Iwe unogona kushandisa boka remabasa VPR (VLOOKUP) uye ZVAKAWANDA AKAFUMURWA (MATCH) kuwana kukosha pamharadzano yeminda Chigadzirwa zita (tambo) uye Mwedzi (column) yehurongwa huri mubvunzo:

=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 iri pamusoro ibasa renguva dzose VPR, iyo inotarisa chaiyo yekufananidza kukosha kwe "Lemons" mumasero A2 kuburikidza neA9. Asi sezvo iwe usingazive kuti ndeipi koramu yekutengesa kwaKurume, haugone kuseta nhamba yekoramu yechitatu basa nharo. VPR. Pane kudaro, basa rinoshandiswa ZVAKAWANDA AKAFUMURWAkutsanangura chikamu ichi.

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

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

Yakaturikirwa mumutauro wevanhu, fomula iyi inoreva:

• Tiri kutsvaga mavara "Mar" - nharo lookup_value ( lookup_value);
• Kutarisa mumasero kubva kuA1 kusvika kuI1 - nharo lookup_array (lookup_array);
• Kudzosa machisi chaiwo - nharo match_type (match_type).

kushandisa 0 munharo yechitatu, munoti mabasa ZVAKAWANDA AKAFUMURWA tsvaga kukosha kwekutanga kunoenderana nekukosha kwauri kutsvaga. Izvi zvakaenzana nekukosha VENHEMA (NHEMA) yenharo yechina VPR.

Aya ndiwo maitiro aungaita nzira mbiri yekutsvaga fomula muExcel, inozivikanwawo semaviri-dimensional kutsvaga kana bidirectional kutsvaga.

SUMPRODUCT basa

basa SUMPRODUCT (SUMPRODUCT) inodzosa huwandu hwezvigadzirwa zvezvakasarudzwa arrays:

=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)

INDEX uye MATCH mabasa

Muchinyorwa chinotevera ini ndichatsanangura aya mabasa zvakadzama, saka parizvino unogona kungokopa iyi fomula:

=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))

Mazita ane mazita uye mushandisi wemharadzano

Kana iwe usiri mune ese akaomesesa eExcel mafomula, unogona kuda iyi inooneka uye isingakanganwike nzira:

1. Sarudza tafura, vhura tab Formulas (Formulas) uye tinya Gadzira kubva kuSarudzo (Gadzira kubva pakusarudza).
2. Tarisa mabhokisi Mutsara wepamusoro (pamutsara uri pamusoro) uye Kuruboshwe chikamu (muchikamu chiri kuruboshwe). Microsoft Excel ichapa mazita kumatanho kubva kune zvakakosha mumutsara wepamusoro uye kuruboshwe koramu yespredishiti yako. Iye zvino unogona kutsvaga uchishandisa mazita aya zvakananga pasina kugadzira mafomula.
3. Musero chero risina chinhu, nyora =row_name column_name, semuenzaniso seizvi:

   =Maremoni Kurume

   ... kana zvakasiyana:

   =Mar Lemons

   Rangarira kuti mutsara nemazita emakoramu anofanirwa kuparadzaniswa nenzvimbo, iyo mune ino kesi inoshanda seyemharadzano opareta.

Paunoisa zita, Microsoft Excel icharatidza chishandiso chine runyoro rwemazita anoenderana, sezvakangoita paunonyora fomula.

4. dzvanya pinda uye tarisa mugumisiro

Kazhinji, chero ipi yenzira dziri pamusoro dzaunosarudza, mhedzisiro yekutsvaga-mativi maviri ichave yakafanana:

Kushandisa akawanda maVLOOKUP mune imwe fomula

Zvinoitika kuti tafura huru uye tafura yekutarisa haina koramu imwechete yakafanana, uye izvi zvinokutadzisa kushandisa yakajairika basa. VPR. Nekudaro, pane imwe tafura isina ruzivo rwatinofarira, asi ine koramu yakafanana ine tafura huru uye tafura yekutarisa.

Ngatitarisei muenzaniso unotevera. Tine tafura huru ine mbiru SKU (nyowani), kwaunoda kuwedzera koramu nemitengo inoenderana kubva kune imwe tafura. Mukuwedzera, tine 2 matafura ekutarisa. Yekutanga (Tarisa tafura 1) ine nhamba dzakagadziridzwa SKU (nyowani) uye mazita echigadzirwa, uye yechipiri (Kutarisa tafura 2) - mazita echigadzirwa uye nhamba dzekare SKU (yekare).

Kuti uwedzere mitengo kubva patafura yechipiri yekutarisa kune iyo huru tafura, unofanirwa kuita chiitiko chinozivikanwa sembiri VPR kana nested VPR.

1. Nyora basa VPR, iyo inowana zita rechigadzirwa mutafura Tarisa tafura 1kushandisa SKU, sekukosha kwaunoda:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   pano Nyowani_SKU - zita renji $A:$B mutafura Tarisa tafura 1, 2 - iyi ndiyo column B, ine mazita ezvigadzirwa (ona mufananidzo uri pamusoro)

2. Nyora fomula yekuisa mitengo kubva patafura Tarisa tafura 2 zvichibva pamazita echigadzirwa anozivikanwa. Kuti uite izvi, namira iyo fomula yawakagadzira kare seyako yekutarisa yebasa idzva VPR:

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

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

   pano mutengo - zita renji $A:$C mutafura Tarisa tafura 2, 3 iri column C ine mitengo.

Mufananidzo uri pazasi unoratidza mhedzisiro yakadzoserwa neformula yatakagadzira:

Kutsiviwa kwakasimba kwedata kubva kumatafura akasiyana uchishandisa VLOOKUP uye INDIRECT

Chekutanga, ngatijekese zvatiri kureva neshoko rekuti “Dynamic substitution of data from different tables” kuti tive nechokwadi chekuti tinonzwisisana nemazvo.

Pane mamiriro ezvinhu kana paine akati wandei mapepa ane data reiyo fomati, uye zvinodikanwa kubvisa ruzivo rwakakosha kubva kune rimwe pepa, zvichienderana nekukosha kunopinzwa mune imwe sero. Ndinofunga kuti zviri nyore kutsanangura izvi nemuenzaniso.

Fungidzira kuti une mishumo yekutengesa kumatunhu akati wandei ane zvigadzirwa zvakafanana uye mune imwechete fomati. Iwe unoda kutsvaga nhamba dzekutengesa kune chaiyo dunhu:

Kana iwe uine mishumo miviri chete yakadai, saka unogona kushandisa zvinonyadzisa fomula ine mabasa VPR и IF (IF) kusarudza report yaunoda kutsvaga:

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

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

Where:

• $D $2 isero rine zita rechigadzirwa. Ziva kuti tinoshandisa mareferences pano kudzivirira kushandura kukosha kwekutarisa kana uchikopa formula kune mamwe maseru.
• $D3 isero rine zita redunhu. Tiri kushandisa absolute column referensi uye referensi yemutsara nekuti isu tinoronga kukopa fomula kune mamwe maseru ari muchikamu chimwe chete.
• FL_Sales и CA_Sales -Mazita ematafura (kana mazita enziyo) ane mishumo yekutengesa inoenderana. Iwe unogona, hongu, kushandisa akajairwa mapepa mazita uye sero referensi mareferensi, semuenzaniso 'FL Sheet'!$A$3:$B$10, asi mazita mazita ari nyore nyore.

Nekudaro, kana paine akawanda matafura akadaro, basa racho IF haisiriyo mhinduro yakanaka. Pane kudaro, unogona kushandisa basa INDIRECT (INDIRECT) kudzosa tsvakiridzo yaunoda.

Sezvaungave uchiziva, basa racho INDIRECT rinoshandiswa kudzorera chinongedzo chakapihwa nerunyoro tambo, ndizvo chaizvo zvatiri kuda ikozvino. Saka, neushingi tsiva muformula iri pamusoro apa chirevo nechishandiso IF kubatanidza nebasa INDIRECT. Heino musanganiswa VPR и INDIRECT inoshanda zvikuru ne:

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

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

Where:

• $D $2 - iri sero rine zita rechigadzirwa, harina kuchinjwa nekuda kwekubatana kwakakwana.
• $D3 ndiro sero rine chikamu chekutanga chezita redunhu. Mumuenzaniso wedu, izvi FL.
• _Kutengesa - chikamu chinozivikanwa chezita remhando dzese dzine mazita kana matafura. Kana yasanganiswa nehuwandu musero D3, inoumba zita rakazara rakakodzera remhando inodiwa. Pazasi pane mamwe madeti kune avo vatsva kubasa INDIRECT.

Mashandiro anoita INDIRECT neVLOOKUP

Kutanga, rega ndikuyeuchidze iyo syntax yebasa racho INDIRECT (INDIRECT):

INDIRECT(ref_text,[a1])

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

Nharo yekutanga inogona kunge iri referensi yesero (A1 kana R1C1 maitiro), zita remhando, kana tambo yemavara. Nharo yechipiri inosarudza kuti ndeipi nzira yekubatanidza iri munharo yekutanga:

• A1kana nharo dziripo TRUE CODE (CHOKWADI) kana chisina kutaurwa;
• R1C1, kana FAS E (NHEMA).

Muchiitiko chedu, chinongedzo chine maitiro A1, saka unogona kusiya nharo yechipiri woisa pfungwa pane yekutanga.

Saka ngatidzokere kune yedu sales report. Kana iwe uchirangarira, saka imwe neimwe rondedzero itafura yakaparadzana iri pane imwe pepa. Kuti fomula ishande nemazvo, unofanirwa kutumidza matafura ako (kana mitsara), uye mazita ese anofanira kunge aine chikamu chakafanana. Semuenzaniso, seizvi: CA_Sales, FL_Sales, TX_Sales zvichingoenda zvakadaro. Sezvauri kuona, "_Kutengesa" iripo mumazita ese.

basa INDIRECT inobatanidza kukosha mukoramu D uye tambo yemavara "_Sales", nokudaro ichitaurira VPR mutafura ipi yekutsvaga. Kana sero D3 ine kukosha kwe "FL", iyo fomula ichatsvaga patafura FL_Sales, kana "CA" - mutafura CA_Sales zvichingoenda zvakadaro.

Mhedzisiro yemabasa VPR и INDIRECT zvichave zvinotevera:

Kana iyo data iri mumabhuku akasiyana eExcel, saka iwe unofanirwa kuwedzera zita rebhuku pamberi pezita rakadanwa, semuenzaniso:

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

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

Kana basa INDIRECT zvinoreva rimwe bhuku rebasa, iro bhuku rebasa rinofanira kuvhurwa. Kana yakavharwa, basa racho rinoshuma kukanganisa. #REF! (#SYL!).