Uyenza kanjani i-VLOOKUP ku-Excel ngezibonelo ezithuthukile namasu

Wake wazithola uzulazula emigqeni nasemakholomu ku-Excel, uzama ukuhlanganisa idatha evela kumathebula ahlukene, kodwa ungazi ukuthi ungayisheshisa kanjani inqubo? Uma usebenza ngamaspredishithi ayinkimbinkimbi, uyazi ukuthi kuyisicefe kangakanani ukusesha nokuhlobanisa ulwazi ngesandla. Ngenhlanhla, kunomsebenzi oyisisekelo oxazulula le nkinga futhi ukonge isikhathi esiningi: umsebenzi we-VLOOKUP.

Kulesi sihloko, ngizokufundisa ngokuningiliziwe indlela yokusebenzisa i-VLOOKUP ku-Excel, izinzuzo zayo, indlela yokubhala ifomula ngendlela efanele, izibonelo ezahlukahlukene ezisebenzayo, kanye nendlela yokuxazulula izinkinga ezivame kakhulu, konke ngezibonelo ezicacile nezisebenzayo. Uzothola namaqhinga okugwema ukulahleka kumaseli nokuthola okuningi kudatha yakho, kokubili ku-Excel kanye ne-Google Sheets.

Uyini umsebenzi we-VLOOKUP ku-Excel?

I-VLOOKUP, eyaziwa nangokuthi i-VLOOKUP ngesiSpanishi, iwumsebenzi ku -Microsoft Excel kanye ne-Google Sheets okuvumela ukuthi useshe inani kukholomu yokuqala yethebula bese ubuyisela inani elihlobene kusuka kwenye ikholomu kumugqa ofanayo. Igama elithi "vertical lookup" libhekisela eqinisweni lokuthi usesho lwenziwa phansi kukholomu.

Lo msebenzi ubalulekile kulabo abadinga ukuhlobanisa idatha evela ohlwini oluhlukene ngaphandle kokuyiqhathanisa ngayinye ngayinye. Isibonelo, ungabheka intengo yomkhiqizo usebenzisa ikhodi yawo, igama lomfundi usebenzisa i-ID yakhe yomfundi, noma umnyango wesisebenzi usebenzisa i-ID yakhe . Isihluthulelo ukuthi ikholomu yokubheka iqukethe amanani ahlukile akhomba into ngayinye.

Cabanga nje uneshidi elilodwa elinama-oda ezithako endaweni yokudlela yakho kanye nelinye ishidi elinabahlinzeki abahlinzeka ngalezo zithako ezifanayo. Nge-VLOOKUP, ungathola igama lomhlinzeki, inombolo yocingo, noma usuku lokulethwa kwesithako ngasinye ngemizuzwana, ngaphandle kokukopisha noma yini ngesandla.

Isebenza kanjani ifomula ye-VLOOKUP?

Ifomula ye-VLOOKUP ihlelwe kanje:

=BUSCARV(valor_búsqueda; intervalo; índice_columna; )

Ingxenye ngayinye yomsebenzi inomsebenzi othile:

• sesha_inani: Lena idatha ofuna ukuyisesha, ngokuvamile iseli equkethe ukhiye oyingqayizivele, isibonelo ikhodi yomkhiqizo noma igama lomuntu.
• isikhawu: Ibanga lamaseli lapho idatha itholakala khona. Ikholomu lapho idatha ikuyo sesha_inani KUFANELE KUHLALE kube ikholomu yokuqala kulobo bubanzi.
• inkomba_yekholomu: Lena inombolo yekholomu, phakathi kwebanga, ofuna ukubuyisela kuyo umphumela. Khumbula ukuthi ikholomu yokusesha ingunombolo 1.
• : Lokhu kuyakhethwa. Khombisa uma ufuna okufanayo ngqo (FAKE noma 0) noma cishe eyodwa (NGEMPELA noma 1). Ngokuzenzakalelayo, kucishe kufane, kodwa ekusebenzeni, cishe uzofuna ukusebenzisa okufanayo ncamashi.

Isibonelo esiyisisekelo sokuthola intengo yesithelo etafuleni kungaba:

=BUSCARV("Manzana"; A2:C10; 3; FALSO)

Ngale fomula, i-Excel izocinga i-"Apple" kukholamu yokuqala yobubanzi A2:C10. Uma iyithola, izobuyisela inani kukholomu yesithathu yalowo mugqa (isibonelo, inani).

Khumbula: I-VLOOKUP ihlala isesha kusukela kwesobunxele kuye kwesokudla. Ayikwazi ukusesha kumakholomu ngakwesobunxele sekholomu yokusesha. Uma udinga ukusesha ngokuphambene, kuzodingeka uhlele kabusha idatha yakho noma usebenzise amafomula athuthukile kakhulu.

Isetshenziselwa ini i-VLOOKUP? Izibonelo ezingokoqobo

I-VLOOKUP iwusizo kakhulu lapho kuphathwa inani elikhulu ledatha futhi kudingeke ukuthi kuhlanganiswe ulwazi oluphakathi kwamathebula ahlukene. Nazi ezinye izibonelo ezivamile zokusetshenziswa:

• Idatha yesisebenzi esihlobene: Unohlu lwamashifu nolunye uhlu lwamagama nezikhundla. I-VLOOKUP ikusiza ukuthi ugcwalise ngokuzenzakalelayo indawo kuthebula le-shift usebenzisa inombolo yesisebenzi njengokhiye.
• Qondanisa uhlu lwezimpahla nezintengo: Kusukela kuhlu lwemikhiqizo esitokweni, ungakwazi ukwengeza intengo yawo ngamunye ngokuyicinga kuthebula lentengo.
• Buyekeza idatha ngokuzenzakalelayo: Noma nini lapho ulwazi olusethebula lereferensi lushintsha (ngokwesibonelo, abahlinzeki), idatha oyilethayo nge-VLOOKUP izobuyekezwa ngokuzenzakalelayo.
• Sesha ulwazi mayelana nabafundi, izincwadi, amaklayenti, imikhiqizo, njll. ngokushesha nangokuzenzakalelayo.

I-VLOOKUP inguzakwethu ongcono kakhulu wokuyeka ukukopisha nokunamathisela futhi wenze ngokuzenzakalelayo izinqubo eziphindaphindayo ku-Excel, wandise umkhiqizo wakho ngokumangazayo.

Isinyathelo ngesinyathelo ukwakha ifomula ye-VLOOKUP

Ake sibone ukuthi singayakha kanjani ifomula ye-VLOOKUP kusukela ekuqaleni kusetshenziswa isimo sangempela sempilo. Ake sithi uphatha ama-oda esithako endaweni yokudlela futhi ube namathebhu amabili:

• Ama-oda wesithako: Uhlu lwalokho okufanele uthenge.
• Uhlu lwabahlinzeki: Faka igama lomphakeli, inombolo yocingo, idethi yokulethwa, nolunye ulwazi oluhlobene nesithako ngasinye.

Sifuna ukwengeza amakholomu amathathu kuhlu lwama-oda: igama lomhlinzeki, inombolo yocingo, nosuku lokulethwa. Ukwenza lokhu:

1. Eshidini lama-Ingredient Orders, zulazula uye kuseli lapho ufuna kuvele khona igama lomhlinzeki.
2. Cindezela okuthi “=" ukuze uqale ukuthayipha ifomula.
3. Bhala I-VLOOKUP( o I-VLOOKUP( uma i-Excel yakho ingesiNgisi.
4. Khetha iseli enegama lesithako ofuna ukusicinga (isibonelo, B5).
5. Thayipha ukhefana bese ukhetha ububanzi lapho idatha yomhlinzeki ikhona (isibonelo, ithebula eshidini Uhlu Lwabahlinzeki, kusukela ku-A3 kuya ku-G13).
6. Cindezela okuthi F4 ukuze wenze ububanzi bube ireferensi ephelele (izimpawu ze-$ zizovela).
7. Bhala ukhefana, khombisa inombolo yekholomu equkethe idatha ofuna ukuyiletha (isibonelo, igama lomphakeli likukholomu 2, inombolo yocingo kukholamu 7, usuku lokulethwa kukholamu 5...)
8. Ekugcineni, bhala FAKE ukucinga okufanayo okuqondile kuphela bese uvala abakaki.

Ifomula yakho yokugcina yegama lomphakeli ingase ibukeke kanje: =BUSCARV(B5,'Lista de Proveedores'!$A$3:$G$13,2,FALSO)

Ocingweni, vele ushintshe inombolo yekholomu (isb., 7), futhi ngosuku lokulethwa, faka inkomba ehambisanayo.

Iqhinga elincane: uma ukopisha futhi unamathisele ifomula engezansi, qiniseka ukuthi ireferensi yethebula lokubheka ikhiyiwe ukuze ugweme amaphutha (yingakho sisebenzisa i-F4 kanye ne-$).

Imininingwane ebalulekile mayelana ne-VLOOKUP syntax nama-agumenti

Ake sihlukanise ingxenye ngayinye yengxabano ukuze singawenzi amaphutha futhi siqonde ngokugcwele esikwethulayo:

• Inani ongalibheka: Kungaba umbhalo, inombolo, ireferensi yelinye iseli… Okubalulekile ukuthi linjalo ehlukile kukholamu yokuqala yobubanzi. Isibonelo: “102” noma B5.
• Ibanga losesho: Faka ikholomu nedatha ofuna ukuyicinga kanye nawo wonke amakholomu ofuna ukukhipha kuwo ulwazi. Isibonelo: A2:D10.
• Inkomba yekholomu: Iyinani eliphelele futhi ihlala iqala ukubala ukusuka kukholomu kwesokunxele impela yobubanzi (1 = ikholomu yokubheka, 2 = okulandelayo, njll.). Awukwazi ukuba ngaphansi kuka-1 noma ngaphezulu kunenombolo yamakholomu kububanzi.
• Okufana ncamashi noma okucishe kufane: KUNJALO kunconywa ukuthi usethe AMANGA ukuze ugweme lezimanga. Sebenzisa TRUE kuphela uma ikholomu yakho yosesho ihlungiwe futhi ufuna ububanzi.

Futhi ku-Google Sheets? Konke okungenhla kusebenza ngo-100%, yize ku-Sheets izimpikiswano ngezinye izikhathi zinokuhlukahluka okuncane, njenge- is_sorted.

Izibonelo eziwusizo kakhulu ze-VLOOKUP

Nazi izibonelo ezimbalwa zezimo ezahlukene:

• Usesho lombhalo: =BUSCARV("Manzana";B4:D8;3;FALSO) → Ibuyisela intengo ye-apula
• Sesha ngereferensi yeseli:=BUSCARV(G9;B4:D8;3;FALSO) → Thola inani le-G9 ohlwini
• Sesha ngokwesilinganiso esifanayo: =BUSCARV(102;A4:D8;2;VERDADERO) → Uma u-102 engekho, ikunikeza inani eliseduze elingaphansi kuka-102
• Ngenkomba yekholomu eguquguqukayo: =BUSCARV(G3;B4:D8;2;FALSO) → Thola ubuningi ngokwenani le-G3
• Imibandela yokuhlanganisa (ku-Google AmaSpredishithi): Uma udinga ukusesha ngegama nesibongo, ungakha ikholomu yomsizi exhumanisa kokubili bese ulisebenzise njengokhiye ohlukile.

Uma unemigqa eminingi engase ifane nosesho lwakho, i-VLOOKUP izohlala ibuyisela okufanayo FIRST okutholiwe.

Amaphutha ajwayelekile kanye nendlela yokuwaxazulula

Iphutha elivame kakhulu le-VLOOKUP yi-#N/A, okusho ukuthi inani lokusesha alikho kukholomu yokuqala yobubanzi. Ake sibheke izizathu ezivame kakhulu nokuthi singazilungisa kanjani:

• Idatha eyimpinda: Uma unamarekhodi amaningi anokhiye ofanayo, elokuqala kuphela elizoboniswa. Susa izimpinda kukholomu yakho yosesho ukuze ugweme ukudideka.
• Izindawo ezihamba phambili/ezilandelayo: Uma kunezikhala ezingabonakali ngaphambi noma ngemva kwedatha yakho, i-Excel ngeke ibuyise okufanayo. Sebenzisa umsebenzi we-SPACES ukuze uhlanze idatha yakho.
• Ireferensi yethebula engalungile: Uma ukukopisha ifomula kushintsha ububanzi, ukusesha kuzohluleka. Isixazululo: Sebenzisa izinkomba eziphelele (nge-$).
• Inkomba yekholomu ayikho kububanzi: Uma ufaka inombolo enkulu kunamakholomu owakhethile, iphutha elithi #REF! liyavela.
• I-oda elingalungile: Uma usebenzisa cishe ukufanisa ngaphandle kokuhlunga ikholomu yosesho ukusuka kokuphansi kakhulu kuye phezulu kakhulu, ungase uthole imiphumela eyiphutha. Hlala njalo ucacise FALSE ngaphandle uma uqinisekile ngalokho okwenzayo.

Ungenza iphutha le-#N/A ngendlela oyifisayo ngokuhlanganisa i-VLOOKUP ne-IFNA:

• =SI.ERROR(BUSCARV(...), "No encontrado") ku-Excel
• =SI.ND(BUSCARV(...), "No encontrado") ku-Google AmaSpredishithi

Lokhu kuzobonisa umlayezo onobungani kunephutha elivamile, eliwusizo uma wabelana ngamaspredishithi akho nabanye.

Amathiphu namasu athuthukile okuba yingcweti ye-VLOOKUP

1. Sebenzisa izinkomba eziphelele kububanzi
Noma nini lapho ukopisha amafomula phansi, qiniseka ukuthi ububanzi bokusesha abushintshi. Ngakho-ke, ngemva kokukhetha ububanzi, cindezela u-F4 ukuze ufake uphawu lwe-$. Lokhu kuqinisekisa ukuthi i-Excel/AmaSpredishithi ayishintshi lapho ukopisha ifomula.

2. Njalo hlunga ikholomu yosesho uma usebenzisa cishe ukufanisa
Uma ngempela udinga ukucinga ububanzi (isb., bala ikhomishini ngobubanzi), hlunga ikholomu lapho i-VLOOKUP isesha kusukela kokuphansi kakhulu kuye phezulu kakhulu.

3. Sebenza ngedatha ehlanzekile
Ngaphambi kokusebenzisa uhlelo, susa noma yiziphi izikhala futhi uqiniseke ukuthi izinombolo noma izinsuku azilondolozwanga njengombhalo. Lokhu kuvimbela imiphumela engalindelekile.

4. I-VLOOKUP ibheka kwesokudla kuphela
Uma udinga ukubheka amanani kwesokunxele, uzodinga ukuhlela kabusha amakholomu akho noma usebenzise imisebenzi efana ne-INDEX kanye ne-MATCH, noma ushintshele ku-XLOOKUP, etholakala ezinguqulweni ezintsha ze-Excel.

5. Sebenzisa amakhadi asendle ukuze uthole ukufana okuncane
Uma ufuna ukusesha amagama aqala ngegama elifanayo kodwa ungazi ukuthi aphetha kanjani, ungasebenzisa izinkanyezi (*) noma izimpawu zombuzo (?) enanini lakho losesho nge-VLOOKUP, usebenzisa njalo okufanayo (FALSE). Isibonelo: BUSCARV("La*";...; FALSO) izobuyisela idatha yokuqala eqala ngokuthi "La".

6. I-VLOOKUP emakhasini noma ezincwadini ezahlukene
Ungasesha kumathebula abekwe kwenye ithebhu (ishidi) noma kwelinye ifayela le-Excel. Vele ucacise igama leshidi nobubanzi kanje: 'Hoja2'!A1:F20Kwezinye izincwadi zokusebenza, zivule kuqala bese ukhetha ububanzi; I-Excel izongeza indlela ngokuzenzakalelayo.

I-VLOOKUP ku-Google AmaSpredishithi: ukufana nokwehluka

Uma ungumsebenzisi we-Google Sheets, umsebenzi we-VLOOKUP usebenza cishe ngendlela efanayo naku-Excel, yize kunezinguquko ezincane kumagama empikiswano :

• search_value: ofuna ukukusesha.
• isikhawu: ububanzi bokusesha futhi bubuyise umphumela.
• inkomba: ikholomu lapho kuzolethwa khona idatha, phakathi nesikhawu (1 ikholomu yokuqala yobubanzi obukhethiwe).
• is_sorted: noma ngabe ufuna okufana ncamashí (AMAMANGA) noma okucishe kufane (TRUE).

Ukwengeza, i-Google AmaSpredishithi ihlanganisa umsebenzi SI.ND() ukwenza imilayezo ngendlela oyifisayo lapho inani eliseshiwe lingatholakali futhi SI.ERROR() kwamanye amaphutha ajwayelekile.

Omunye umniningwane othakazelisayo ukuthi ungakwazi ukusho ububanzi esikhundleni sokusebenzisa amaseli, okwenza amafomula akho abe lula futhi awenze afundeke kakhudlwana.

Imikhawulo nezinye izindlela ze-VLOOKUP

Nakuba i-VLOOKUP inamandla kakhulu, inemikhawulo ethile :

• Awukwazi ukusesha kwesokunxele sekholomu yosesho.
• Ibuyisela kuphela inani lokuqala elifanayo.
• Ingahamba kancane ngenani elikhulu ledatha.
• Ayikuvumeli ukuthi useshe amanani ngokulandelana okwehlayo uma ububanzi bakho buhlungwe ngendlela ehlukile.

Ezinguqulweni zamanje ze-Excel, ungasebenzisa i-XLOOKUP , exazulula eziningi zalezi zinkinga: ikuvumela ukuthi useshe kokubili kwesobunxele nakwesokudla, uthole imiphumela kunoma iyiphi ikholomu, futhi iguquguquka kakhudlwana.

Imikhuba engcono kakhulu yokusebenza ne-VLOOKUP

• Sebenzisa okhiye abahlukile: Uma ikholomu yakho yokubheka iqukethe izimpinda, hlanza idatha ngaphambi kokufaka i-VLOOKUP.
• Gcina ububanzi buhlungiwe kuphela uma usebenzisa cishe ukufanisa. Akudingekile ukuze kufane ncamashi, kodwa akukaze kube buhlungu ukuba nedatha ehlanzekile.
• Khiya izinkomba zobubanzi nge-$ ngaphambi kokukopisha ifomula kwamanye amaseli, lokhu kuzovimbela amaphutha namashifu angafuneki.
• Qiniseka ukuthi amadethi nezinombolo kufomethwe ngendlela efanele (hhayi njengombhalo).
• Sebenzisa i-IF.ERROR, IF.ND noma i-IFNA ukwenza ngokwezifiso imilayezo yephutha, ikakhulukazi uma wabelana ngeshidi nabasebenzisi abaningi.

I-athikili ehlobene:

Isetshenziswa kanjani i-VLOOKUP ne-XLOOKUP ku-Excel: Umhlahlandlela ophelele kanye nezibonelo ezisebenzayo

Isaka

Umbhali oshisekayo ngomhlaba wamabhayithi nobuchwepheshe ngokujwayelekile. Ngiyathanda ukwabelana ngolwazi lwami ngokubhala, futhi yilokho engizokwenza kule bhulogi, ngikubonise zonke izinto ezithakazelisayo kakhulu ngamagajethi, isofthiwe, ihadiwe, izitayela zobuchwepheshe, nokuningi. Inhloso yami ukukusiza ukuthi uzulazule emhlabeni wedijithali ngendlela elula nejabulisayo.