VLOOKUP. Ukusebenzisa VLOOKUP. Excel - CDF

Isicelo Iprogram ye-Excel iyathandwa ngenxa yokufikeleleka yayo nelula, kuba ufuna naluphi na ulwazi olukhethekileyo kunye nezakhono. Table Ngenxa yokuba ulwazi nawuphi na umsebenzisi, kunye olubanzi uluhlu lwezixhobo, kuquka 'umsebenzi weNkosi' ivumela naluphi usetyenziso kunye izibalo kunye kwedatha enikiweyo.

Enye fomyula Excel ezaziwayo-kakuhle imbono nkqo. Ukusebenzisa VLOOKUP xa uqala ubonakala unzima kakhulu, kodwa ke kuphela ekuqaleni.

kanjani Excel VLOOKUP

Xa usebenza ifomula CDF kufuneka luthathele ingqalelo ukuba aya kumfuna amaxabiso ezinqwenelekayo kuphela kwimiqolo kunokuba imikrozo. Ukuze ube nokufaka isicelo umsebenzi ifuna ubuncinane inani lwemihlathi - ezimbini, esiphezulu ekhoyo.

CDF umsebenzi uphengulula i header kwangaphambili olunokuba nasiphi ifomathi (itekisi, amanani, imali, umhla kunye nexesha, t. D.) Kule thebhile. Kwimeko yokurekhoda ovelisa yokufunyaniswa (ifaka) ixabiso ebhalwe kumqolo omnye, kodwa kwisihloko umhlathi, ngamanye ehambisana njengenqobo elinikiweyo. Ukuba ixabiso alafunyanwa, ngoko imposiso sikhutshiwe # N / A (kwi IsiNgesi uguqulelo # N / A).

nemfuneko yokusebenzisa

VLOOKUP uyeza ziyabanceda umqhubi, xa ufuna ukufumana ngokukhawuleza kwaye usebenzise e izibalo ngakumbi, uhlalutyo okanye ukuqikelela etafileni ixabiso elithile elikhulu. Eyona nto ibalulekileyo xa usebenzisa lo ifomula - ukuqinisekisa ukuba imihlaba ezichaziweyo usetsho ukhethiwe ngokuchanekileyo. Lo msebenzi mawubandakanye zonke iirekhodi, oko kukuthi, ukususela ekuqaleni ukuya ekugqibeleni.

Ukusetyenziswa kakhulu rhoqo lwe CDF (Excel function) - le uthelekiso kongezwe data kwi iitafile ezimbini, kusetyenziswa imiqathango ethile. Ngaphezu koko, uluhlu ukufuna kuba mkhulu kwaye ziqulathe amawaka amasimi, zibekwe kwi amaphepha ezahlukeneyo okanye iincwadi.

Ibonisa umsebenzi CDF, ukusebenzisa njani na, ukwenza njani izibalo, umzekelo kulo mzobo ngasentla. Apha siqwalasela itafile retails zobungakanani ngokuxhomekeke kummandla kunye nomphathi. Ukufuna bemfuno umphathi ethile (igama lakhe elipheleleyo), kwaye ixabiso Umnqweno womntu udibaniso yentengiso yayo.

Ngenxa yokuba umsebenzi CDF (VLOOKUP), itafile elitsha apho ngokukhawuleza kumphathi ezithile ezinqwenelekayo ezifane intengiso yakhe iyonke.

Ukugcwalisa ifomula algorithm

CDF yokubala akhiwe "Function Wizard" kunye "Izingqiniso kunye azinazo." ukhetho yencoko yababini ngolu hlobo lulandelayo:

kwifomula wempikiswano zifakwa emgceni:

• Ixabiso efunekayo - oko ukufumana umsebenzi, kunye ezahlukeneyo ezo amaxabiso yeseli, idilesi yayo, igama elinikiweyo yona yomqhubi. Kwimeko wethu - kuba igama kunye negama yomphathi.
• Uluhlu - uluhlu lwemiqolo nemihlathi, apho wafuna umbandela lowo.
• Inombolo Column - serial number, nto leyo isiphumo yentengiso, oko kukuthi isiphumo ifomula.
• umbono lokuphumla. Ubeke ixabiso nokuba FALSE okanye WENENE. Nokubuyisela FALSE kuphela kungqamana ngqo, TRUE - kuvumela ukukhangela amaxabiso luqikelelo.

Umzekelo umsebenzi

VLOOKUP umzekelo yokusetyenziswa kuba ngolu hlobo lulandelayo: xa usebenza lweshishini urhwebo e-Excel spreadsheets elikwikholamu A Igama ebhaliweyo imveliso, kwaye elikwikholamu B - ixabiso ahambelanayo. Kuba ukuzoba up izindululo kwikholam C kufuneka ufumane iindleko imveliso ethile ukuba ufuna ukubonisa elikuluhlu D.

Ifomula, abhalwe D, uya kukhangela ngolu hlobo: = VLOOKUP (C1, A1: B5, 2, 0), okt, = VLOOKUP (ixabiso engusebekho, uluhlu itafile data, inani lothotho inani kumhlathi; 0). Njengoko ingxoxo sesine ukuze izakusetyenziswa endaweni 0 bubuxoki.

Ukuze agcwalise le formula inikeza itafile, kufuneka ekhuphela yonke kwikholam D.

indawo data Secure kwebanga yokusebenza kunokwenzeka esebenzisa iimbekiselo epheleleyo. Ukuze wenze oku ngesandla ezivunyiweyo $ imiqondiso phambi amaxabiso alfabhethi kwaye ngamanani yi iiseli kakhulu asekhohlo nasekunene ze table. Kulo mzekelo ifomula uba: = CDF (C1; $ A $ 1: $ B $ 5, 2, 0).

usebenzisa imposiso

CDF umsebenzi alusebenzi, kwaye ke ngoko umyalezo kubonakala kwikholam kwimveliso ngenxa yempazamo (# N / A okanye # N / A). Oku kwenzeka kwiimeko ezinjalo:

1. Le fomula yaziswa, ze kwikholam imiqathango efunekayo ayinanto (kulo mzekelo, column C).
2. Xa ikholam C wenza elinexabiso angekhoyo elikuluhlu A (yokukhangela uluhlu data). Ukujonga ixabiso efunekayo kufuneka yabelwe kunye neenqobo ikholam kwi menu tab "Hlela" - "Fumana" ukuba ufaka le ngxelo, ukuqala uphendlo. Ukuba udweliso lwenkqubo ungakufumani, ngoko ke asikho.
3. Iifomati kwalomu ka (iikhrayitheriya ezifunekayo) iiseli A no C zizinto ezahlukeneyo, umzekelo, omnye - okubhaliweyo, kwaye enye - inani. Tshintsha ifomathi iseli kuba, ukuba siye wokuhlela iseli (F2). iingxaki ezinjalo kaninzi zenzeka xa ukufaka idatha kwezinye izicelo. Ukuze anqande eso iimpazamo kwifomula CDF kunokwenzeka ukuba zizibandakanye mpawu zilandelayo: IXABISO okanye TEXT. Ukuphunyezwa kwezi algorithms ukuguqula ngokuzenzekelayo kwifomathi iiseli.
4. Umsebenzi ikhowudi iqulathe weempawu ezinga-okushicilelekayo okanye izithuba. Emva koko kufuneka bayitshekishe ngocoselelo indlela phambi iimpazamo igalelo.
5. Seta search okuyo, oko kukuthi argument yesine VLOOKUP 1 okanye TRUE, kodwa ke itafile leyo esotiweyo ixabiso nokunyuka. Kulo mzekelo, iikhrayitheriya ezifunekayo ukuze umhlathi ofuna ukubeka ukuze inyuka.

Kwaye xa isishwankathelo entsha yentlangano yetafula iikhrayitheriya ezifunekayo banokuba nawuphi na umyalelo kunye ulandelelwano yaye akayi kunyanzeleka ukuba wafumana uluhlu olupheleleyo (ukhetho akakhethi).

Iimpawu yokusebenzisa njengoko lokuphumla nokubukela 1 okanye TRUE

Imposiso phantsi №5 yinto exhaphakileyo yaye kuboniswe ngokucacileyo kulo mzobo ungezantsi.

Kulo mzekelo, uluhlu igama afakwe ngokuvumelana namanani ayikho ukuze elinyukayo, nangexabiso ekungqulekeni. Ngaphezu koko, njengoko Isithuba esisetyenziswayo ngenxa njengenqobo TRUE (1), nto leyo nangoko iphazamisa uphendlo xa sibona ixabiso ngaphezu kufuneka, ngoko impazamo ikhutshwe.

Xa isicelo-1 okanye ezinyanisekileyo yingxoxo sesine imfuneko ukuqinisekisa ukuba nsika iikhrayitheriya endiyilangazelelayo ezibekwe ukuze inyuka. Xa usebenzisa 0 okanye amanga, oku akusekho mfuneko, kodwa ayikho ngoko ke amathuba umbono lokuphumla.

Nje khumbula ukuba kwisithuba kubaluleke ngokukodwa ukuba ukubeka itafile. Kungenjalo VLOOKUP izakubonisa data olungachanekanga esiseleni.

Ezinye bokuqonda ukusebenza VLOOKUP

Ukunenzela lula, ifomula onjalo unelungelo itafile Uluhlu apho kufunwa (Ingxoxo yesibini), njengoko kubonisiwe okuqashwa.

Kulo mzekelo, indawo yokuthengisa ye itafile unelungelo. Ukuza kuthi ga ngoku, ukuba kukho netafile, ngaphandle kwezi kwezihloko lomhlathi, kwaye egameni endle (ngaphantsi tab bar ukuya ekhohlo) unikwe igama.

Enye indlela - Isihloko - kuthetha ukwabiwa kuluhlu data, uze Ukuvulela kwimenyu "Faka" - "Igama" - "Beka".

Ukuze usebenzise i data ezibekwe kwenye kwimakhishithi ye-yomsebenzi, esebenzisa umsebenzi yePPM, oko kuyimfuneko yingxoxo yesibini kwirejista formula uluhlu datha yendawo. Umzekelo, CDF = (A1; Sheet 2 $ A $ 1: $ B $ 5; 2; 0), apho Sheet2! - kubhekiswa kuluhlu incwadi eceliweyo, kwaye $ A $ 1: $ B $ 5 - idilesi uluhlu lweenkcukacha search.

Umzekelo yenkqubo yemfundo kunye CDF

Kakhulu elula Excel VLOOKUP umsebenzi akusebenzi kuphela mveliso inxaxheba kurhwebo, kodwa amaziko emfundo ukwandisa abafundi inkqubo matching (abafundi) kunye iingqikelelo zabo. Imizekelo yezi misebenzi ziboniswa kwi-manani ngezantsi.

Kukho izintlu ezimbini abafundi kwetafile. Enye noqikelelo zabo, yaye owesibini ubonisa ubudala. Kuyimfuneko ukuba bathelekise iitafile ezimbini ukwenzela ukuba belingana ubudala abafundi babonisa zovavanyo zazo, oko kukuthi wongeze i umhlathi owongezelelweyo kuluhlu lwesibini.

VLOOKUP ukhona lo msebenzi. In "amanqaku" umhlathi G esihloko sithi abhalwe ifomula ehambelana: = CDF (E4, B3: C13, 2, 0). Her kufuneka ekhuphela yonke le selitye itafile.

Ngenxa yokuba umsebenzi VLOOKUP liya kuhlola afunyenwe ngabafundi ezithile.

Umzekelo injini ukhangelo CDF

Omnye umzekelo we ukusetyenziswa CDF umsebenzi - umbutho lwe-enjini yokukhangela xa kwidathabheyisi njengako iikhrayitheriya ezicaciswe kufuneka ibe ukufumana ixabiso ahambelanayo. Ngoko ke, lo mzobo ubonisa uluhlu lweziqhulo izilwanyana yaye bobakhe kwabo nohlobo oluthile.

Ngoncedo lwe CDF, itafile omtsha ukuba kulula ukufumana, uWele isilwanyana ifomu yayo. iinjini zokuphendla Relevant xa besebenza kunye nezintlu ezinkulu. Ukuze ihlole ngesandla onke amangeniso, ungenza ngokukhawuleza ungasebenzisa ukhangelo kwaye ufumane isiphumo oyifunayo.