Okuqukethwe
- Sesha ku-Excel ngemibandela eminingi
- Sikhipha amanani wesibili, wesi-2, njll. sisebenzisa i-VLOOKUP
- Buyisa zonke izimpinda yenani oyifunayo
- Ukusesha kwe-XNUMXD ngomugqa owaziwayo nekholomu
- Ukusebenzisa ama-VLOOKUP amaningi kufomula eyodwa
- Ukushintshaniswa okunamandla kwedatha kusuka kumathebula ahlukene kusetshenziswa i-VLOOKUP ne-INDIRECT
Engxenyeni yesibili yokufundisa kwethu ngomsebenzi VPR (VLOOKUP) ku-Excel, sizohlaziya izibonelo ezimbalwa ezizokusiza uqondise wonke amandla VPR ukuxazulula imisebenzi ye-Excel ebaluleke kakhulu. Izibonelo zicabanga ukuthi usuvele unolwazi oluyisisekelo lokuthi lesi sici sisebenza kanjani. Uma kungenjalo, ungase ube nentshisekelo yokuqala ngengxenye yokuqala yalesi sifundo, esichaza i-syntax kanye nokusetshenziswa okuyisisekelo. VPR. Awu, ake siqale.
Sesha ku-Excel ngemibandela eminingi
Umsebenzi VPR ku-Excel iyithuluzi elinamandla ngempela lokwenza usesho lwenani elithile kusizindalwazi. Kodwa-ke, kunomkhawulo obalulekile - i-syntax yayo ikuvumela ukuthi useshe inani elilodwa kuphela. Kuthiwani uma ufuna ukusesha ngezimo eziningi? Uzothola isixazululo ngezansi.
Isibonelo sokuqala: Sesha ngemibandela emi-1 ehlukene
Ake sithi sinohlu lwama-oda futhi sifuna ukulithola Inani lezimpahla (Qty.), ngokusekelwe ezicinisweni ezimbili - Igama leklayenti (Ikhasimende) kanye Igama lomkhiqizo (Umkhiqizo). Udaba luyinkimbinkimbi yiqiniso lokuthi umthengi ngamunye wa-oda izinhlobo ezimbalwa zezimpahla, njengoba kungabonakala etafuleni elingezansi:
umsebenzi ojwayelekile VPR ngeke isebenze kulesi simo ngoba izobuyisela inani lokuqala elitholayo elifana nevelu yokubheka enikeziwe. Isibonelo, uma ufuna ukwazi ubuningi bento Amaswidi'eyalwe ngumthengi UJeremy Hill, bhala ifomula elandelayo:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– le fomula izobuyisela umphumela 15elihambisana nomkhiqizo Apples, ngoba yinani lokuqala elifana.
Kunendlela elula yokwenza - dala ikholomu eyengeziwe lapho uzohlanganisa khona zonke izindlela ezidingekayo. Esibonelweni sethu, lawa amakholomu Igama leklayenti (Ikhasimende) kanye Igama lomkhiqizo (Umkhiqizo). Ungakhohlwa ukuthi ikholomu ehlanganisiwe kufanele njalo ibe ikholomu kwesokunxele ebangeni lokusesha, njengoba kuyikholomu yesokunxele lapho umsebenzi. VPR ubheka phezulu uma ufuna inani.
Ngakho-ke, wengeza ikholomu eyisizayo etafuleni bese ukopisha ifomula elandelayo kuwo wonke amaseli ayo: =B2&C2. Uma ufuna iyunithi yezinhlamvu ifundeke kakhudlwana, ungahlukanisa amanani ahlanganisiwe nesikhala: =B2&» «&C2. Ngemva kwalokho, ungasebenzisa ifomula elandelayo:
=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;ЛОЖЬ)
Iphi iseli B1 iqukethe inani elihlanganisiwe lempikiswano ukubheka (bheka_inani) kanye 4 – Ukuphikisana inombolo_yenkomba (inombolo_yekholomu), okungukuthi inombolo yekholomu equkethe idatha ezobuyiswa.
Isibonelo sesi-2: I-VLOOKUP ngemibandela emibili ithebula libukwa kwelinye ishidi
Uma udinga ukubuyekeza ithebula elikhulu (Ithebula Eliyinhloko) ngokwengeza idatha esuka etafuleni lesibili (Ithebula Lokubheka), elikwelinye ishidi noma kwenye incwadi yokusebenzela ye-Excel, ungakwazi ukuqoqa inani olifunayo ngqo kufomula oyifakile. etafuleni elikhulu.
Njengasesibonelweni sangaphambilini, uzodinga ikholomu eyisizayo kuthebula Lokubheka elinamanani ahlanganisiwe. Le kholomu kufanele ibe ikholomu ekwesokunxele kakhulu ebangeni lokusesha.
Ngakho ifomula nge VPR kungaba kanje:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Lapha, amakholomu B kanye no-C aqukethe amagama ekhasimende namagama emikhiqizo, ngokulandelana, kanye nesixhumanisi Ama-oda!$A&$2:$D$2 ichaza ithebula okufanele libheke kwelinye ishidi.
Ukwenza ifomula ifundeke kakhudlwana, unganikeza uhla lokubuka igama, bese ifomula izobukeka ilula kakhulu:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Ukuze ifomula isebenze, amanani akukholomu yesokunxele yethebula olibhekile kufanele ahlanganiswe ngendlela efanayo ncamashi nasesimweni sokusesha. Emfanekisweni ongenhla, sihlanganise amanani u2bu2band abeka isikhala phakathi kwawo, ngendlela efanayo okudingeka ukwenze engqungqutheleni yokuqala yomsebenzi (BXNUMX& "" & CXNUMX).
Khumbula! Umsebenzi VPR ikhawulelwe kuzinhlamvu ezingu-255, ayikwazi ukusesha inani elingaphezu kwezinhlamvu ezingu-255 ubude. Gcina lokhu engqondweni futhi uqiniseke ukuthi ubude bevelu oyifunayo abudluli lo mkhawulo.
Ngiyavuma ukuthi ukungeza ikholomu eyisizayo akusona isixazululo esihle kakhulu futhi asisamukeleki ngaso sonke isikhathi. Ungenza okufanayo ngaphandle kwekholomu yomsizi, kodwa lokho kungadinga ifomula eyinkimbinkimbi kakhulu enenhlanganisela yemisebenzi INDEX (INDEX) kanye MATCH (KUDANWE KAKHULU).
Sikhipha amanani wesibili, wesi-2, njll. sisebenzisa i-VLOOKUP
Usuyakwazi lokho VPR ingabuyisela inani elilodwa kuphela elifanayo, ngokunembe kakhulu, elokuqala elitholiwe. Kodwa kuthiwani uma leli nani liphindaphindwa izikhathi ezimbalwa ohlwini olubukiwe, futhi ufuna ukukhipha okwesibili noma kwesi-2 kwazo? Kuthiwani uma wonke amanani? Inkinga ibonakala iyinkimbinkimbi, kodwa isixazululo sikhona!
Ake sithi ikholomu eyodwa yethebula iqukethe amagama amakhasimende (Igama Lekhasimende), kanti enye ikholomu iqukethe imikhiqizo (Umkhiqizo) ayithengile. Ake sizame ukuthola izinto zesi-2, 3 kanye nesi-4 ezithengwe ikhasimende elithile.
Indlela elula yokwengeza ikholomu eyisizayo ngaphambi kwekholomu igama lekhasimende futhi uyigcwalise ngamagama amakhasimende ngenombolo yokuphinda yegama ngalinye, isibonelo, John Doe1, John Doe2 njll. Sizokwenza iqhinga ngokufaka izinombolo sisebenzisa umsebenzi COUNTIF (COUNTIF), uma kubhekwa ukuthi amagama ekhasimende akukholomu B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Ngemuva kwalokho ungasebenzisa umsebenzi ojwayelekile VPRukuthola umyalelo odingekayo. Ngokwesibonelo:
- Thola 2-th into e-odwe ikhasimende Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Thola 3-th into e-odwe ikhasimende Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Eqinisweni, ungafaka ireferensi yeseli njengenani lokubheka esikhundleni sombhalo, njengoba kukhonjisiwe emfanekisweni olandelayo:
Uma ufuna kuphela I-2-e ukuphindaphinda, ungakwenza ngaphandle kwekholomu eyisizayo ngokwakha ifomula eyinkimbinkimbi kakhulu:
=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;ИСТИНА);"")
Kule fomula:
- $F$2 - iseli eliqukethe igama lomthengi (alishintshiwe, sicela uqaphele - isixhumanisi siphelele);
- I-$ B $ – ikholomu igama lekhasimende;
- Ithebula4 - Itafula lakho (le ndawo ingase futhi ibe uhla olujwayelekile);
- $C16 – iseli lokugcina letafula lakho noma ububanzi.
Le fomula ithola kuphela inani lesibili elifanayo. Uma udinga ukukhipha izimpinda ezisele, sebenzisa isisombululo sangaphambilini.
Uma udinga uhlu lwawo wonke amameshi - umsebenzi VPR lokhu akuyena umsizi, njengoba kubuyisela inani elilodwa ngesikhathi - isikhathi. Kodwa i-Excel inomsebenzi INDEX (INDEX), engabhekana kalula nalo msebenzi. Ukuthi ifomula enjalo izobukeka kanjani, uzofunda esibonelweni esilandelayo.
Buyisa zonke izimpinda yenani oyifunayo
Njengoba kushiwo ngenhla VPR ayikwazi ukukhipha wonke amanani ayimpinda ebangeni eliskeniwe. Ukuze wenze lokhu, udinga ifomula eyinkimbinkimbi kakhudlwana, eyenziwe ngemisebenzi eminingana ye-Excel, njenge INDEX (INDEX), Kancane (OKUNCANE) kanye UMUGQA (UMUGQA)
Isibonelo, ifomula engezansi ithola zonke izimpinda zenani elisuka kuseli F2 kububanzi B2:B16 futhi ibuyisela umphumela usuka emigqeni efanayo kukholomu 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));"")}
Faka le fomula yamalungu afanayo kumaseli amaningi aseduze, njengamaseli f4: uf8njengoba kuboniswe emfanekisweni ongezansi. Inani lamaseli kufanele lilingane noma libe likhulu kunenani eliphakeme elingenzeka lokuphinda inani eliseshiwe. Ungakhohlwa ukuchofoza Ctrl + Shift + Fakaukufaka ifomula yamalungu afanayo ngendlela efanele.
Uma ungathanda ukuqonda ukuthi isebenza kanjani, ake singene emininingwaneni yefomula kancane:
Ingxenye 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - qhathanisa inani kuseli F2 nenani ngalinye kububanzi B2:B16. Uma okufanayo kutholwa, kusho ukuthi inkulumo STRING(C2:C16)-1 ibuyisela inombolo yomugqa ohambelanayo (inani -1 ikuvumela ukuthi ungafaki umugqa kanhlokweni). Uma kungekho okufanayo, umsebenzi IF (IF) ibuyisela iyunithi yezinhlamvu engenalutho.
Umphumela womsebenzi IF (IF) kuzoba nomugqa ofanayo ovundlile: {1,"",3,"",5,"","","","","","",12,"","",""}
Ingxenye 2:
ROW()-3
СТРОКА()-3
Lapha umsebenzi UMUGQA (UMLINE) usebenza njengesibali esingeziwe. Njengoba ifomula ikopishelwe kumaseli F4:F9, sikhipha inombolo 3 kusuka kumphumela womsebenzi ukuze uthole inani 1 esitokisini F4 (umugqa wesi-4, susa 3) ukuze uthole 2 esitokisini F5 (umugqa wesi-5, susa u-3) njalonjalo.
Ingxenye 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Umsebenzi Kancane (OKUNCANE) kuyabuya n-oh inani elincane kuhlu lwedatha. Esimweni sethu, isiphi isikhundla (kusuka kwesincane kunazo zonke) sokubuya sinqunywa umsebenzi UMUGQA (UMUGQA) (bheka Ingxenye 2). Ngakho, ngeseli F4 umsebenzi OKUNCINANE({uhlu},1) ubuyela 1-th (encane kakhulu) i-array element, isb 1. Okweseli F5 ubuyela 2-th ingxenye encane kunazo zonke ohlwini, okungukuthi 3, Njll
Ingxenye 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))
Umsebenzi INDEX (INDEX) ivele ibuyisele inani leseli elithile kumalungu afanayo C2:C16. Okweseli F4 umsebenzi INHLOKO($C$2:$C$16) uzobuya ApplesUkuze F5 umsebenzi INHLOKO($C$2:$C$16) uzobuya Amaswidi' njalo njalo.
Ingxenye 5:
IFERROR()
ЕСЛИОШИБКА()
Ekugcineni, sifaka ifomula ngaphakathi komsebenzi IFERROR (IFERROR), ngoba mancane amathuba okuthi ujabule ngomlayezo wephutha #AT (#N/A) uma inombolo yamaseli lapho ifomula ikopishwa khona ingaphansi kwenani lamanani ayimpinda kububanzi obubukwayo.
Ukusesha kwe-XNUMXD ngomugqa owaziwayo nekholomu
Ukwenza usesho lwe-XNUMXD ku-Excel kuhlanganisa ukusesha inani ngomugqa owaziwayo nenombolo yekholomu. Ngamanye amazwi, ukhipha inani leseli ezimpambanweni zomugqa othile nekholomu.
Ngakho-ke, ake siphendukele etafuleni lethu futhi sibhale ifomula enomsebenzi VPR, ezothola ulwazi mayelana nezindleko zamalamula adayiswa ngo-March.
Kunezindlela ezimbalwa zokwenza usesho lwe-XNUMXD. Hlola izinketho bese ukhetha leyo ekufanele kakhulu.
Imisebenzi ye-VLOOKUP kanye ne-MATCH
Ungasebenzisa inqwaba yemisebenzi VPR (VLOOKUP) kanye OKUNINGI KUDANWE (MATCH) ukuze uthole inani empambanweni yezinkambu Igama lomkhiqizo (intambo) kanye izinyanga (ikholomu) yohlu okukhulunywa ngalo:
=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);ЛОЖЬ)
Ifomula engenhla iwumsebenzi ojwayelekile VPR, ebheka ukufana nse kwevelu elithi “Amalamula” kumaseli A2 kuya ku-A9. Kodwa njengoba ungazi ukuthi iyiphi ikholomu okuthengiswa kuyo ngoMashi, ngeke ukwazi ukusetha inombolo yekholomu yempikiswano yesithathu yomsebenzi. VPR. Kunalokho, umsebenzi uyasetshenziswa OKUNINGI KUDANWEukuchaza le kholomu.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Ihunyushelwa olimini lwabantu, le fomula isho:
- Sibheka abalingiswa "Mar" - ingxabano ukubheka (bheka_inani);
- Ukubuka kumaseli ukusuka ku-A1 kuye ku-I1 - ukuphikisana uhlu_lokubheka (uhlu_lokubheka);
- Ibuyisela okufanayo ngqo - ukungqubuzana fanisa_uhlobo (uhlobo_lokulinganisa).
Ukusebenzisa 0 ku-agumenti yesithathu, uthi imisebenzi OKUNINGI KUDANWE bheka inani lokuqala elifana ncamashi nenani olifunayo. Lokhu kulingana nevelu FALSE (AMAMANGA) engxabano yesine VPR.
Lena yindlela ongakha ngayo ifomula yosesho yezindlela ezimbili ku-Excel, eyaziwa nangokuthi usesho olunezinhlangothi ezimbili noma ukusesha okukabili.
Umsebenzi we-SUMPRODUCT
Umsebenzi SUMPRODUCT (I-SUMPRODUCT) ibuyisela isamba semikhiqizo yamalungu afanayo akhethiwe:
=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 kanye nemisebenzi ye-MATCH
Esihlokweni esilandelayo ngizochaza le misebenzi ngokuningiliziwe, ngakho-ke okwamanje ungakwazi ukukopisha le 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))
Ububanzi obuqanjiwe kanye no-opharetha wempambanomgwaqo
Uma ungekho kuwo wonke lawo mafomula e-Excel ayinkimbinkimbi, ungase uthande le ndlela ebonakalayo nekhumbulekayo:
- Khetha ithebula, vula ithebhu amafomula (Amafomula) bese uchofoza Dala kokuthi Okukhethiwe (Dala ngokukhetha).
- Maka amabhokisi Umugqa ophezulu (emgqeni ongenhla) kanye Ikholomu yesobunxele (kukholamu kwesokunxele). I-Microsoft Excel izonikeza amagama kububanzi ukusuka kumanani akumugqa ophezulu kanye nekholomu yesokunxele yesipredishithi sakho. Manje ungasesha usebenzisa lawa magama ngokuqondile ngaphandle kokudala amafomula.
- Kunoma iyiphi iseli engenalutho, bhala =umugqa_igama lekholomu, isibonelo kanje:
=Amalamula Mar
... noma okuphambene nalokho:
=Mar Lemons
Khumbula ukuthi amagama omugqa namakholomu kufanele ahlukaniswe yisikhala, kulokhu esisebenza njengo-opharetha we-intersection.
Uma ufaka igama, i-Microsoft Excel izobonisa ithiphu yamathuluzi enohlu lwamagama afanayo, njengalapho ufaka ifomula.
- Cindezela Faka futhi uhlole umphumela
Ngokuvamile, noma yiziphi izindlela ezingenhla ozikhethayo, umphumela wokusesha okunezinhlangothi ezimbili uzofana:
Ukusebenzisa ama-VLOOKUP amaningi kufomula eyodwa
Kwenzeka ukuthi itafula eliyinhloko kanye netafula lokubheka alinayo ikholomu eyodwa efanayo, futhi lokhu kukuvimbela ekusebenziseni umsebenzi ojwayelekile. VPR. Nokho, kunelinye ithebula elingaqukethe ulwazi esiluthakaselayo, kodwa linekholomu evamile enethebula eliyinhloko kanye nethebula lokubheka.
Ake sibheke isibonelo esilandelayo. Sinethebula Eliyinhloko elinekholomu I-SKU (entsha), lapho ufuna ukwengeza khona ikholomu enamanani ahambisanayo asuka kwelinye ithebula. Ngaphezu kwalokho, sinamathebula okubheka angu-2. Elokuqala (Ithebula lokubheka 1) liqukethe izinombolo ezibuyekeziwe I-SKU (entsha) namagama emikhiqizo, kanti eyesibili (Ithebula Lokubheka 2) – amagama emikhiqizo nezinombolo ezindala I-SKU (endala).
Ukwengeza izintengo kusuka kuthebula lokubheka lesibili kuya kuthebula elikhulu, kufanele wenze isenzo esaziwa ngokuthi kabili VPR noma esidlekeni VPR.
- Bhala umsebenzi VPR, ethola igama lomkhiqizo etafuleni Bheka ithebula 1usebenzisa SKU, njengenani elifunekayo:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Lapha I-SKU_Entsha - ububanzi obuqanjwe $A:$B etafuleni Bheka ithebula 1, 2 – lena yikholomu B, equkethe amagama ezimpahla (bheka isithombe esingenhla)
- Bhala ifomula yokufaka amanani ethebula Bheka ithebula 2 ngokusekelwe emagameni emikhiqizo aziwayo. Ukuze wenze lokhu, namathisela ifomula oyidalile ngaphambili njengevelu yokubheka yomsebenzi omusha VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Lapha Inani - ububanzi obuqanjwe I-$A:$C etafuleni Bheka ithebula 2, 3 ikholomu C equkethe amanani.
Isibalo esingezansi sibonisa umphumela obuyiswe ifomula esiyidalile:
Ukushintshaniswa okunamandla kwedatha kusuka kumathebula ahlukene kusetshenziswa i-VLOOKUP ne-INDIRECT
Okokuqala, ake sicacise ukuthi sisho ukuthini ngenkulumo ethi “Ukushintsha okunamandla kwedatha evela kumathebula ahlukene” ukuze siqiniseke ukuthi sizwana ngendlela efanele.
Kunezimo lapho kunamashidi amaningana anedatha yefomethi efanayo, futhi kuyadingeka ukukhipha ulwazi oludingekayo eshidini elithile, kuye ngenani elifakwe kuseli elinikeziwe. Ngicabanga ukuthi kulula ukuchaza lokhu ngesibonelo.
Cabanga ukuthi unemibiko yokuthengisa yezifunda ezimbalwa ezinemikhiqizo efanayo nangefomethi efanayo. Ufuna ukuthola izibalo zokuthengisa zesifunda esithile:
Uma unemibiko enjalo emibili kuphela, ungasebenzisa ifomula elula ehlazisayo enemisebenzi VPR и IF (IF) ukukhetha umbiko ofisa ukuwusesha:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
kuphi:
- $D $2 iyiseli eliqukethe igama lomkhiqizo. Qaphela ukuthi sisebenzisa izinkomba eziphelele lapha ukuze sigweme ukushintsha inani lokubheka lapho sikopisha ifomula kwamanye amaseli.
- $D3 iyiseli elinegama lesifunda. Sisebenzisa ireferensi yekholomu ephelele kanye nereferensi yomugqa ohlobene ngoba sihlela ukukopisha ifomula kwamanye amaseli akukholomu efanayo.
- FL_Sales и I-CA_Sales - amagama amathebula (noma ububanzi obuqanjiwe) aqukethe imibiko yokuthengisa ehambisanayo. Yebo, ungasebenzisa amagama eshidi ajwayelekile kanye nereferensi yebanga lamaseli, isibonelo 'FL Sheet'!$A$3:$B$10, kodwa ububanzi obuqanjiwe bulula kakhulu.
Nokho, lapho kunamathebula amaningi anjalo, umsebenzi IF akusona isixazululo esingcono kakhulu. Kunalokho, ungasebenzisa umsebenzi INDALO (INDIRECT) ukuze ubuyisele uhla lokusesha olufunayo.
Njengoba cishe uyazi, umsebenzi INDALO isetshenziselwa ukubuyisela isixhumanisi esinikezwe iyunithi yezinhlamvu yombhalo, okuyiyona kanye esiyidingayo manje. Ngakho-ke, shintsha ngesibindi isisho somsebenzi kufomula engenhla IF ukuxhuma nomsebenzi INDALO. Nansi inhlanganisela VPR и INDALO isebenza kahle nge:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
kuphi:
- $D $2 – leli yiseli elinegama lomkhiqizo, alishintshile ngenxa yesixhumanisi esiphelele.
- $D3 iyiseli eliqukethe ingxenye yokuqala yegama lesifunda. Esibonelweni sethu, lokhu FL.
- _Ukuthengisa – ingxenye evamile yegama labo bonke ububanzi obuqanjiwe noma amathebula. Uma ihlanganiswa nevelu kuseli D3, yakha igama elifaneleke ngokugcwele lobubanzi obudingekayo. Ngezansi kuneminye imininingwane yalabo abasanda kuhlanganyela INDALO.
Isebenza kanjani i-INDIRECT ne-VLOOKUP
Okokuqala, ake ngikukhumbuze i-syntax yomsebenzi INDALO (I-INDIRECT):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
I-agumenti yokuqala ingaba ireferensi yeseli (isitayela se-A1 noma i-R1C1), igama lobubanzi, noma iyunithi yezinhlamvu yombhalo. I-agumenti yesibili inquma ukuthi yisiphi isitayela sesixhumanisi esiqukethwe ku-agumenti yokuqala:
- A1uma ingxabano ikhona IKHODI LEQINISO (IQINISO) noma akucacisiwe;
- I-R1C1, uma FNJENGOBA E (AMAANGA).
Esimweni sethu, isixhumanisi sinesitayela A1, ukuze ukwazi ukushiya ngaphandle impikiswano yesibili futhi ugxile kweyokuqala.
Ngakho-ke masibuyele emibikweni yethu yokuthengisa. Uma ukhumbula, umbiko ngamunye uyitafula elihlukile elibekwe eshidini elihlukile. Ukuze ifomula isebenze kahle, kufanele uqambe amathebula akho (noma ububanzi), futhi wonke amagama kufanele abe nengxenye efanayo. Ngokwesibonelo, kanje: I-CA_Sales, FL_Ukuthengisa, TX_Ukuthengisa njalo njalo. Njengoba ubona, okuthi “_Sales” kukhona kuwo wonke amagama.
Umsebenzi INDALO ixhuma inani elikukholomu D kanye neyunithi yezinhlamvu yombhalo ethi “_Sales”, ngaleyo ndlela iyasho VPR kuliphi ithebula ongasesha. Uma iseli D3 liqukethe inani elithi “FL”, ifomula izosesha ithebula FL_Ukuthengisa, uma “CA” – kuthebula I-CA_Sales njalo njalo.
Umphumela wemisebenzi VPR и INDALO kuzoba okulandelayo:
Uma idatha itholakala ezincwadini ezahlukene ze-Excel, kuzomele wengeze igama lencwadi ngaphambi kobubanzi obuqanjiwe, isibonelo:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Uma umsebenzi INDALO ibhekisela kwenye incwadi yokusebenzela, leyo ncwadi yokusebenzela kufanele ivulwe. Uma ivaliwe, umsebenzi uzobika iphutha. #REF! (#SYL!).