VLOOKUP. Kushandisa VLOOKUP. Excel - ZWL

Chikumbiro chirongwa Excel yakakurumbira nokuda kwayo affordability uye nyore, nokuti haatarisiri ruzivo chero chaidzo uye unyanzvi. Table maonero pfungwa mashoko chero anoishandisa, uye akasiyana -siyana zvokushandisa, kusanganisira "Tenzi basa" rinobvumira chero kuchinja uye Masvomhu pamwe yakapiwa mashoko.

Mumwe aizivikanwa Excel Masvomhu chinhu wehunyambiri maonero. Kushandisa VLOOKUP pakutanga kuaona sokuti chaizvo yakaoma, asi zviri chete kutanga.

Sei Excel VLOOKUP

Kana kushanda ZWL payakavakirwa vanofanira kufunga kuti zvichava kutsvaka vakada tsika chete muzvikamu pane mitsara. Kushandisa kushanda zvinoda risingamboshandisi nhamba yembiru - vaviri, kufadza iripo.

ZWL basa unonzvera akafanorongerwa chokuratidza iyo ingava chinhu pamanyorerwo (chinyorwa, Numeric, mari, Musi uye nguva, t. D.) In patafura. Kana kurekodha izvozvo outputs zvaonekwa (inoisa) mutengo yakanyorwa mumutsara chete, asi pamwe i.e. wenyaya mbiru, unoenderana rakapihwa chokuratidza. Kana mutengo asina kuwanikwa, ipapo kukanganisa iri rakapa # N / A (muna Chirungu shanduro # N / A).

kudiwa kushandisa

VLOOKUP rinouya kubatsira opareta, kana uchida kukurumidza kuwana uye kushandisa zvimwe Masvomhu, ongororo kana Kufembera tafura imwe ukoshi yakakura. Chinhu chikuru kana ukashandisa mutoo uyu - nechokwadi chokuti yakanatsa yezvinoreva kutsvaka yave nemazvo vakasarudzwa. Zvinofanira kusanganisira zvose zvinyorwa, kureva kubva pakutanga kusvikira pakupedzisira.

Kushandiswa kupfuura kakawanda zviri ZWL (Excel Zvaiitwa) - fananidzo uye kusanganiswa mashoko ari mabwendefa maviri, achishandisa chaizvo zvinodiwa. Uyezve, kutsvaka siyana zvinogona kuva huru uye ane zviuru minda, akaisa pamusoro machira akasiyana kana mabhuku.

Chinoratidza kushanda ZWL, kuishandisa, sei kuita Masvomhu, sezvo muenzaniso nhamba kumusoro. Pano isu funga tafura retails hukuru zvichienderana nharaunda uye maneja. Search chokuratidza ndiyo chaiyo maneja (zita rake rizere), uye aida mutengo pakuverengwa wokutengesa ayo.

Somugumisiro ari ZWL basa (VLOOKUP), tafura itsva umo chaiyo zvaaida maneja nokukurumidza vachiwirirana rokutengesa ake zvachose.

Kuzadza algorithm fomura

Zvinowanikwa fomura ZWL mu "Rinoshanda Wizard" uye "References uye Arrays." Nhaurirano nzira iri sezvinotevera:

iri nzira iri pfungwa vakapinda mune tungamidzana:

• The aida ukoshi - ndiko kuwana basa, uye dzakasiyana vari tsika sero, kero yayo, zita rakapiwa kwariri nokuda opareta. Nezvedu - ndiwo zita uye zita maneja.
• Table - zvakawanda mitsara uye mbiru, sezvamakaropafadzwa chokuratidza iri kutsvaka.
• Column nhamba - nhamba yacho serial, ndiro uwandu rokutengesa, kuti ndiwo mugumisiro pamusoro nzira.
• Chinguva maonero. Iye akabata kukosha kana dzenhema kana dzechokwadi. Uye MANYEPO inodzoka chete ane mutambo chaiyo, DZECHOKWADI - anobvumira kutsvaga anofungidzirwa tsika.

Muenzaniso mashandiro

VLOOKUP muenzaniso kushandiswa angava sezvinotevera: patinenge wezvokutengeserana zvebhizimisi munyika Excel Spreadsheets mu shongwe A nezita chigadzirwa zvakanyorwa, uye mbiru B - the runoenzanirana mutengo. Wokucheresa kumusoro vaitaura ari mbiru C unofanira kuwana mari imwe chigadzirwa kuti uchida kuratidza ari mbiru D.

The yemaitiro, akanyorwa D, vachatarira seizvi: = VLOOKUP (C1, A1: B5, 2, 0), kureva, = VLOOKUP (Lookup kukosha, tafura Data siyana, mashoko Serial nhamba mbiru, 0). Somunhu nharo chechina angashandiswa pachinzvimbo 0 ndeyenhema.

Kuzadza mutoo uyu inopa tafura, unofanira kutevedzera yose mbiru D.

Yakachengeteka date nharaunda rwekuita siyana zvinogoneka achishandisa Mhedziso arimo. Kuti aite izvi manually tabulated $ zviratidzo pamberi nearufabheti uye kusanganiswa netsika kubudikidza masero zvakanyanyisa rworuboshwe uye rworudyi tafura. Panyaya iyi nzira anova: = ZWL (C1; $ A $ 1: $ B $ 5, 2, 0).

kushandisa kukanganisa

ZWL basa zvikasashanda, uye ipapo shoko iri mbiru goho pamusana kukanganisa (# N / A kana # N / A). Izvi zvinoitika muna Muzviitiko zvakadaro:

1. The yemaitiro kutaurwa, uye aida nzira mbiru chinhu (iyi, mbiru C).
2. In Mbiru C akaita mutengo kuti vasipo mu sa A (kutsvaka Range date). Kuongorora runodiwa ukoshi anofanira dzinounzwa uye mbiru nzira iri Muterere Tab "Edit" - "Wana" kuti pinza chinyorwa ichi, tangai kutsvaka. Kana chirongwa haawani nayo, ipapo haako.
3. Formats mbiru masero A uye C (runodiwa nzira) akasiyana Somuenzaniso, mumwe - rugwaro, uye mumwe - nhamba. Kushandura pamanyorerwo sero rinogona kuva, kana isu kupinda muchitokisi fora (F2). matambudziko akadaro anowanzoratidza kuitika kana iwe kupinza mashoko nemamwe mafomu. Kudzivisa zvikanganiso zvakadaro nzira ZWL zvinobvira nokupinza zvinotevera zvinhu: ZVINOKOSHA kana TEXT. Implementation pakati algorithms izvi zvaitika exchange pamanyorerwo masero.
4. Mashandiro Bumbiro rine non-city vatambi kana dzezvimwe. Ipapo iwe unofanira kunyatsoongorora tarisa nzira pamberi chiyamuro zvikanganiso.
5. Isa chinhu anofungidzirwa kutsvaka, kuti ndicho nharo chechina VLOOKUP 1 kana KWECHOKWADI, asi patafura haisi arongwe nokuda pakazvimba kukosha. Panyaya iyi, runodiwa dzekuziva kuti mbiru uchida kugadzirisa zviri vachikwira kuti.

Uye kana munhu mutsva pfupiso tafura sangano ndiyo zvaida nzira inogona chero murayiro uye kutevedzana uye kwete hazvo kugamuchirwa Pamazita zvakakwana (kukwana kusarudzwa).

Zvakakurukurwa kushandiswa sezvo penguva kuona 1 kana KWECHOKWADI

Error pasi №5 kwakakurumbira chaizvo uye kuratidzwa pachena nomufananidzo pazasi.

In muenzaniso uyu, zita mazita ari arongwe maererano nendima haisi vachikwira kuitira, uye pamusoro kuwa kukosha. Uyezve, sezvo penguva inoshandiswa maonero chokuratidza RWECHOKWADI (1), izvo pakarepo anokudimburira kutsvaka kana anoona kukosha kupfuura waida, saka kukanganisa iri rakapa.

In kushanda 1 kana vechokwadi wechina nharo kudiwa kuitira kuti mbiru pamwe aida zvinodiwa kwave arongwe mu vachikwira kuti. Kana uchishandisa 0 kana nhema, ichi hachisisiri chikonzero, asi kushayikwa ipapo zvinoita penguva maonero.

Just yeuka kuti chinguva kukosha tafura kugadzirisa. Zvikasadaro VLOOKUP achaita kuratidza kururama Data muchitokisi.

Other nuances chokushanda VLOOKUP

Zvivanakire, nzira yemaitiro yakadaro inogona kodzero tafura Range riri kundoishora (wechipiri nharo), sezvinoratidzwa Fig.

Panyaya iyi, kuti okutengesa nzvimbo patafura kodzero. Kuti izvi zvidaro, zvine tafura, kunze mbiru misoro, uye muzita mumunda (pasi Tab pakapuranga kuruboshwe) riri basa rimwe zita.

Munogonawo - Musoro - zvinoreva mugove pakati date siyana, uye ipapo chinja kusvika Muterere "Insert" - "Zita" - "Muzvitsaurire".

Kuti kushandisa mashoko akaisa pamusoro perimwe papepa iri workbook, uchishandisa PPS basa, zvakakodzera mune nharo pamusoro nzira mazita nzvimbo Data Range wechipiri. Somuenzaniso, ZWL = (A1; Sheet 2 $ A $ 1: $ B $ 5; 2; 0), apo Sheet2! - kurevatska akakumbira bhuku list, uye $ A $ 1: $ B $ 5 - Kero date kutsvaka siyana.

Muenzaniso rokudzidzisa kwacho pamwe ZWL

Quite zvakakunakira Excel VLOOKUP basa kushandisa kwete chete kune yemakambani aidaro zvokutengeserana, asi masangano kudzidzisa optimize vadzidzi enzanirana muitiro (vadzidzi) pamwe inofungidzirwa dzavo. Mienzaniso mabasa izvi zvinoratidzwa mune nenhamba pazasi.

Pane mazita maviri vadzidzi tafura. Mumwe nokufungidzira dzavo, uye yechipiri inoratidza zera. Zvakakodzera kuenzanisa mabwendefa maviri kuitira kuti iri ndima pamwe nemakore Vadzidzi vakaratidza kuongororwa yavo, ndiko kuwedzera wokuwedzera mbiru yechipiri pasi.

VLOOKUP kuzvigona basa iri. In "mavanga" G Mbiru vanoda akanyorwa runoenzanirana payakavakirwa: = ZWL (E4, B3: C13, 2, 0). Her vanofanira kutevedzera yose mbiru patafura.

Somugumisiro ari VLOOKUP basa achaita kuongorora yaunganidzwa zvimwe vadzidzi.

Muenzaniso kutsvaka injini pamwe ZWL

Mumwe muenzaniso pokushandisa ZWL basa - sangano search injini kana mune Database maererano wainyatsotaura zvinodiwa zvinofanira kuva kuwana runoenzanirana ukoshi. Saka, chirungamutauro rinoratidza mazita nicknames yemhuka vavo kuva imwe mhando.

Achibatsirwa ZWL, tafura itsva iri nyore kuwana, zita remadunhurirwa mhuka wawo. Relevant kutsvaka injini zvakafanana kana kushanda huru mazita. Kuti manually kuongorora zvose vakanyoresa, unogona kukurumidza kushandisa kutsvaka uye kuwana zvinodiwa mugumisiro.