VLOOKUP. Ukusebenzisa VLOOKUP. Excel - CDF

Uhlelo lokusebenza le-Excel iyathandwa ngenxa ukuyikhokhela yayo kanye lula, ngoba Akudingi ulwazi olukhethekile namakhono. Ithebula umbono umqondo ulwazi kunoma umsebenzisi, futhi ebanzi ezahlukene amathuluzi, kuhlanganise "Master umsebenzi" ivumela noma iyiphi manipulation izibalo ngedatha enikeziwe.

Omunye Excel amafomula owaziwa umbono mpo. Ukusebenzisa VLOOKUP unganakile kubonakala kubenzima, kodwa kwakumane kuyisiqalo.

kanjani Excel VLOOKUP

Lapho ukusebenza CDF ifomula kufanele bacabangele ukuthi abayofuna amagugu oyifunayo kuphela amakholomu kunokuba imigqa. Ukwenza umsebenzi kudinga inombolo okungenani amakholomu - amabili, esiphezulu etholakalayo.

CDF umsebenzi efuna ukufanelekela predetermined okungase yanoma yimuphi format (umbhalo, izinombolo, imali, usuku kanye nesikhathi, t. D.) Ngo etafuleni. Endabeni ukuqoshwa ke okukhiphayo a okwatholakala (ifaka) ukubaluleka eqoshiwe kumugqa ofanayo, kodwa isihloko ikholomu, isb elihambisana ukufanelekela inikezwe. Uma ukubaluleka ayitholakali ke nephutha ikhishwa # N / A (e-English version # N / A).

isidingo ukusebenzisa

VLOOKUP iza ukusiza opharetha, uma ufuna ukuthola ngokushesha futhi zisebenzisa izibalo phambili, ukuhlaziya noma isibikezelo itafula inani elithile elikhulu. Into esemqoka lapho usebenzisa le formula - qinisekisa ukuthi ububanzi ecacisiwe yokusesha iye kahle ekhethiwe. Kufanele zihlanganisa wonke amarekhodi, okungukuthi, kusukela ngosuku lokuqala kuze kube sekugcineni.

Ukusetshenziswa njalo iningi CDF (Excel umsebenzi) - sifaniso futhi ngaphezu idatha ku Ngasengibamba izibhebhe zombili, usebenzisa letitsite. Ngaphezu kwalokho, i-range search zingaba zinkulu futhi aqukethe izinkulungwane Amasimu, ibekwe amashidi ezahlukene noma izincwadi.

Ibonisa umsebenzi we CDF, ungayisebenzisa kanjani, nokuthi bayifeze kanjani izibalo, njengesibonelo e sibalo ngenhla. Lapha sibheka ithebula nabathengisi osayizi kuncike esifundeni kanye nemeneja. Ukucinga umbandela umphathi ethize (igama lakhe egcwele), futhi inani wayefisa yinani lika yokuthengisa yayo.

Ngenxa yalokho we CDF umsebenzi (VLOOKUP), itafula omusha lapho imenenja eqondile oyifunayo ifaniswe ngokushesha yokuthengisa kwakhe ngokuphelele.

Ukugcwalisa algorithm ifomula

Kutholwe ifomula CDF ku "Umsebenzi Iseluleki" kanye "izithenjwa futhi afanayo." inketho Ingxoxo simiswe ngalendlela lelandzelako:

ku-formula-agumenti zifakiwe ulayini:

• Inani oyifunayo - okungukuthi ukuthola umsebenzi, futhi ezahlukeneyo okuyizinto amagugu kwengqamuzana, ikheli layo, igama esethiwa lona ke ngu-opharetha. Esimweni sethu - ke igama negama umphathi.
• Ithebula - ibanga lemigqa namakholomu, lapho umbandela okungiyo.
• inombolo Ikholomu - inombolo yayo ye-serial, okuyinto isamba yokuthengisa, okungukuthi umphumela ifomula.
• umbono wesikhathi. Ubeke a ukubaluleka noma NGAMANGA kumbe TRUE. Futhi ukubuyiselwa FALSE kuphela umdlalo ngqo, TRUE - ivumela ukucinga amanani eseduze.

Isibonelo umsebenzi

VLOOKUP isibonelo ukusetshenziswa kungenzeka kanje: lapho sisebenzelana ukuhweba kwamabhizinisi e Excel kwikholomu Igama ebhaliwe umkhiqizo, futhi uB - intengo okuhambisanayo. Ukuze ezilandelwayo iziphakamiso kukhalamu C udinga ukuthola izindleko umkhiqizo othize ukuthi ufuna ukubonisa kwikholomu D.

Ifomula, abhalwe D, izobukeka kanje: = VLOOKUP (C1, A1: B5, 2, 0), okusho ukuthi, = VLOOKUP (Ukubheka ukubaluleka, itafula ibanga ledatha, inombolo ye-serial kukholamu; 0). Njengoba kungenzeka kungabi ingxabano wesine ingasetshenziswa esikhundleni 0 kuyinto FALSE.

Ukuze ugcwalise leli ifomula inikeza itafula, kufanele ukopishe lonke ikholomu D.

Secure idatha endaweni senhlobo zokusebenza kungenzeka ngezindlela yezinkomba ngokuphelele. Ukuze wenze lokhu ngesandla labalwa $ izimpawu phambi amagugu ngezinhlamvu kanye zezinombolo by amaseli kakhulu kwesokunxele kanye ilungelo etafuleni. Kulokhu ifomula kuba: = CDF (C1; $ A $ 1: $ B $ 5, 2, 0).

usebenzisa iphutha

CDF umsebenzi kungasebenzi, bese umlayezo ivela yi ikholomu okukhipha yephutha (# N / A noma # N / A). Lokhu kwenzeka ezimweni ezinjalo:

1. Ifomula yethulwa futhi wayefisa nenqubo ikholomu ayinalutho (kuleli cala, noC).
2. Ngo noC wenza inani engekho kwikholomu A (search ibanga ledatha). Ukuze uhlole inani elidingekayo kufanele zabelwe kanye nenqubo ikholomu kwimenyu ithebhu "Hlela" - "Thola" ukufaka leli rekhodi, uqale ukucinga. Uma uhlelo singakutholi, khona-ke alikho.
3. Izakhiwo ikholomu lamaseli A no-C (nenqubo edingekayo) zihlukile, isibonelo, omunye - umbhalo, kanye nezinye - inombolo. Shintsha ifomethi cell kungaba, uma siye ukuhlela iseli (F2). Izinkinga ezinjalo ngokuvamile kwenzeka uma i-data esuka kwezinye izinhlelo zokusebenza. Ukuze ugweme amaphutha anjalo esikhathini ifomula CDF kungenzeka ukuba nifaka izici ezilandelayo: VALUE noma UMBHALO. Ukuqaliswa kwalawa algorithm ukuguqula ngokuzenzakalelayo ifomethi amaseli.
4. Ikhodi umsebenzi linezinhlamvu non-okuqinisekisa noma izikhala. Khona-ke kufanele uhlole ngokucophelela Ifomula khona amaphutha input.
5. Setha i-search eseduze, okungukuthi i-agumenti wesine VLOOKUP 1 noma TRUE, kodwa kwetafula aliyona ihlelwa ukubaluleka liphuma. Kulokhu, nezidingo kukholamu ofuna ukuhlunga in inyuka oda.

Futhi lapho ithebula elisha isifinyezo nhlangano nenqubo oludingekayo kungaba noma ngayiphi indlela ukulandelana hhayi amukelwe uhlu oluphelele (Ukukhetha esinqunyiwe).

Izici ukusetshenziswa njengoba isikhawu ukubukwa 1 noma TRUE

Iphutha ngaphansi №5 kuvamile impela futhi aboniswa ngokucacile kulo mdwebo ongezansi.

Kulesi sibonelo, igama uhlu afakwe ngokuvumelana izinombolo akuyona inyuka oda, futhi ukubaluleka falling. Ngaphezu kwalokho, njengoba isikhawu elisetshenziswa umbono umbandela TRUE (1), okuyinto ngokushesha iphazamisa yokusesha uma ithola kunani edingekayo, ngakho nephutha ikhishwa.

Ngo ukusetshenziswa 1 noma kweqiniso e-agumenti wesine nesidingo sokuqinisekisa ukuthi ikholomu nenqubo oyifunayo iye ihlelwa in inyuka oda. Lapho usebenzisa i-0 noma amanga, lokhu akuseyona isidingo, kodwa futhi ulahlekile ke ukuthi kungenzeka umbono isikhawu.

Vele khumbula ukuthi isikhawu kubalulekile ukuhlunga etafuleni. Kungenjalo VLOOKUP izoveza idatha engalungile kuseli.

Okunye ezicashile ukusebenza VLOOKUP

Ukuze kube lula, ifomula enjalo kungaba unelungelo ithebula ububanzi okuyinto ukuseshwa (Isizathu sesibili), njengoba kuboniswe Fig.

Kulokhu, lo yokuthengisa indawo etafuleni unelungelo. Kuze kube yimanje, kusobala itafula, ngaphandle kwe-izihloko ikholomu, futhi igama emkhakheni (ngaphansi kwibha yethebhu ngakwesokunxele) inikezwa igama.

Enye - ukuthi isihlokwana - kusho ukwabiwa uhla idatha, bese ushintshele ku imenyu "Faka" - "Igama" - "Beka".

Ukuze usebenzise idatha ibekwe kwenye ishidi kubhuku, usebenzisa umsebenzi PPS, kubalulekile e-agumenti kubukwa irejista yalabakhona ifomula idatha yendawo ububanzi yesibili. Ngokwesibonelo, CDF = (A1; Ishidi 2 $ A $ 1: $ B $ 5; 2; 0), lapho Ishidi2! - ireferensi eceliwe Uhlu lwamabhuku $ A $ 1: $ B $ 5 - nekheli uhla search idatha.

Isibonelo inqubo yemfundo ne CDF

Izimo zethu zihluke kakhulu elula Excel VLOOKUP umsebenzi akusebenzi kubantu kuphela amafemu wahlanganyela ukuhweba, kodwa futhi zezemfundo ukuze nokwandisa abafundi inqubo ofanayo (abafundi) ne izilinganiso zabo. Izibonelo yalomsebenti ziboniswe izibalo ngezansi.

Zimbili uhlu abafundi kwethebula. Omunye nge izilinganiso zabo, kanti eyesibili ibonisa ubudala. Kuyadingeka ukuba uqhathanise amatafula amabili ukuze belingana engu-abafundi ukuboniswa ukuhlolwa kwabo, okungukuthi ukuze ungeze ikholomu ezengeziwe ohlwini lwesibili.

VLOOKUP abhekana ngayo nalesi msebenzi. Ngo "izimpawu" G ikholomu osesikhundleni eqoshiwe ifomula ohambelana: = CDF (E4, B3: C13, 2, 0). Wakhe kudingeka alingise lonke kukholomu etafuleni.

Ngenxa umsebenzi VLOOKUP ngeke lehlola etholwe abafundi abathile.

Isibonelo injini yokusesha kanye CDF

Esinye isibonelo isicelo se-CDF umsebenzi - inhlangano yenjini yokusesha lapho database ngokuvumelana nezimfuno ecacisiwe kufanele kube ukuthola nenani. Ngakho, lesi sibalo sikhombisa uhlu izidlaliso kwezilwane kanye bengabakaJehova uhlobo oluthile.

Ngosizo CDF, itafula omusha Kulula ukuthola, wethiwa isilwane injalo. Ahambisanayo izinjini like lapho esebenza uhlu enkulu. Ukuze ubuyekeze ngesandla konke okufakiwe, ungakwazi ngokushesha sebenzisa search futhi uthole umphumela oyifunayo.