Kuchokera Kumanzere Kuchokera Kugwiritsa Ntchito VLOOKUP

01 a 03

Pezani Dera Kumanzere

Kuchokera Kumanzere Kwambiri Kuchokera Mwapang'onopang'ono

Ntchito ya VLOOKUP ya Excel ikugwiritsidwa ntchito kupeza ndi kubwezeretsa zambiri kuchokera pa tebulo la deta pogwiritsa ntchito chiwerengero chomwe mukusankha.

Kawirikawiri, VLOOKUP imafunika kufunika kokhala pakhomo lamanzere pa tebulo la deta, ndipo ntchitoyo imabwereranso gawo lina la deta yomwe ili mumzere womwewo kumanja.

Mwa kuphatikiza VLOOKUP ndi CHOOSE ntchito ; Komabe, njira yothandizira yotsalira ikhoza kukhazikitsidwa yomwe:

• imalola kuti phindu lakulumikiza likhale losankhidwa kuchokera ku chigawo chilichonse mu tebulo la deta
• Uthenga wobwereza womwe uli m'mbali iliyonse kumanzere kwa chiwerengero choyang'ana

Chitsanzo: Pogwiritsa ntchito VLOOKUP ndi CHOOSE Ntchito mu Gawo Loyang'ana Kwambiri

Mapangidwe omwe ali pansipa apange fomu yowonda yachitsulo yomwe imapezeka mu chithunzi pamwambapa.

Njirayi

= VLOOKUP ($ D $ 2, CHOOSE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)

zimathandiza kupeza gawo limene limaperekedwa ndi makampani osiyanasiyana omwe ali mu ndemanga 3 ya tebulo la deta.

Ntchito ya CHOOSE ntchito mu njirayi ndikunamizira VLOOKUP kuti akhulupirire kuti ndime 3yi ndilo ndime 1. Chotsatira chake, dzina la kampani lingagwiritsidwe ntchito ngati chidziwitso chothandizira kupeza dzina la gawo lomwe limaperekedwa ndi kampani iliyonse.

Maphunziro Otsogolera - Kulowa Datorial Data

1. Lowetsani ziganizo zotsatira m'maselo omwe amasonyeza: D1 - Wogulitsa E1 - Part
2. Lowani tebulo la deta lowonedwa mu chithunzi pamwamba pa maselo D4 mpaka F9
3. Mizere 2 ndi 3 yasiyidwa yopanda kanthu kuti akwaniritse zofufuzira komanso njira yowonjezera yomwe ikugwiritsidwa ntchito panthawiyi

Kuyambira Pulogalamu Yoyang'ana Kumanzere - Kutsegula Bokosi la Ma Dilo la VLOOKUP

Ngakhale kuti n'zotheka kulembera ndondomekoyi pamwamba pa selo F1 mu worksheet, anthu ambiri amavutika ndi mawu ofanana nawo.

Njira ina, mu nkhaniyi, ndiyo kugwiritsa ntchito bokosi la dialog VLOOKUP. Pafupifupi ntchito zonse za Excel zili ndi bokosi lakulankhulana lomwe limakulolani kuti mulowetse mndandanda uliwonse wa ntchito pa mzere wosiyana.

Maphunziro Otsogolera

1. Dinani pa selo E2 la tsamba - malo pomwe zotsatira za fomu yomaliza yochezera ziwonetsedwe
2. Dinani pa Fomu tab ya riboni
3. Dinani pazomwe mungapezeko ndi Kutsatsa njira mukaboni kuti mutsegule ntchito yolemba pansi
4. Dinani pa VLOOKUP mu mndandanda kuti mubweretse bokosi lazokambirana

02 a 03

Kulowa Mipikisano mu Bokosi la Olojera la VLOOKUP - Dinani kuti Muwone Zithunzi Zazikulu

Mavumbulutso a VLOOKUP

Zolinga za ntchito ndizo zikhalidwe zomwe zimagwiritsidwa ntchito ndi kuwerengera zotsatira.

Mu bokosi la bokosi la ntchito, dzina la ndondomeko iliyonse ili pamzere wosiyana ndi kutsatidwa ndi munda womwe ungalowemo mtengo.

Lowani mfundo zotsatirazi pazifukwa za VLOOKUP pazolondola pa bokosi la bokosi monga momwe taonera pa chithunzi pamwambapa.

Phindu la Lookup

Vuto loyang'ana ndilo gawo la chidziwitso chomwe amagwiritsidwa ntchito kufufuza tebulo. VLOOKUP akubwezeretsanso gawo lina la deta kuchokera mu mzere womwewo monga mtengo wofunika.

Chitsanzo ichi chikugwiritsira ntchito selo loyang'ana malo komwe dzina la kampani lidzalowetsedwera pa tsamba. Ubwino wa izi ndikuti zimapangitsa kuti zisinthe kusintha dzina la kampani popanda kusintha ndondomekoyi.

Maphunziro Otsogolera

1. Dinani pamzere wokhala pa tsamba loyang'ana mubox
2. Dinani pa selo D2 kuti muwonjezere gawo la seloyi ku mzere wa lookup_value
3. Dinani fayilo F4 pa makiyi kuti mupange selo loyenera - $ D $ 2

Zindikirani: Mafotokozedwe osagwiritsidwa ntchito amagwiritsidwa ntchito poyang'ana phindu ndi mndandanda wa ma tebulo kuti muteteze zolakwika ngati fomu yojambulira imakopedwera ku maselo ena pa tsamba.

Table Array: Lowani Ntchito YOSANKHA

Mndandanda wa mndandanda wa mndandanda ndi chidziwitso cha chidziwitso chodziwika chomwe chidziwitsochi chimachotsedwa.

Kawirikawiri, VLOOKUP imangoyang'anitsitsa ndondomeko yamtengo wapatali yowunikira kuti mupeze deta patebulo. Kuti muyang'ane kumanzere, VLOOKUP iyenera kunyengedwa mwa kukonzanso ndondomekoyi m'mizere yonse pogwiritsa ntchito CHOOSE ntchito.

Mwachidule ichi, NTCHITO YA CHOOSE imakwaniritsa ntchito ziwiri:

1. imapanga magome omwe ali ndi zigawo ziwiri zokha - zigawo D ndi F
2. imasintha ufulu kumanzere a ndondomeko m'magulu a tebulo kotero kuti ndime F imabwera yoyamba ndipo D yanu yachiwiri

Zambiri za momwe CHOOSE ntchito ikugwirira ntchito izi zingapezeke pa tsamba 3 la phunziro .

Maphunziro Otsogolera

Zindikirani: Pamene mukulowa ntchito pamanja, mfundo zonsezi zimagwirizanitsidwa ndi chiwerengero "," .

1. Mu ntchito ya VLOOKUP dialog box, dinani pa Table_array line
2. Lowetsani ntchito YOTSATIRA yotsatira
3. SANKHA ({1,2}, $ F: $ F, $ D: $ D)

Nambala ya Index Index

Kawirikawiri, nambala ya ndondomeko ya mndandanda imasonyeza kuti ndi chigawo chiti cha tebulo yomwe ili ndi deta yomwe mwasunga. Mu chiganizo ichi; Komabe, limatanthawuza dongosolo la zipilala zosankhidwa ndi CHOOSE ntchito.

CHOYAMBA ntchito imapanga mzere wa tebulo womwe uli mizere ikuluikulu yokhala ndi chigawo F chotsatira pambuyo pa ndondomeko D. Popeza chidziwitso chofunidwa - gawo la gawo - liri mu gawo D, mtengo wa ndondomeko ya ndondomeko ya mndandanda uyenera kukhazikitsidwa ku 2.

Maphunziro Otsogolera

1. Dinani pa Col_index_num mzere mu bokosi la dialog
2. Sakani 2 pa mzerewu

Range Lookup

Malemba a Range_lookup a VLOOKUP ndiwotheka (ZOONA kapena ZOKHUDZA zokha) zomwe zikuwonetsa ngati mukufuna VLOOKUP kupeza mndandanda weniweni kapena wofanana ndi mtengo wowerengera.

• Ngati ZOKHUDZA kapena ngati zotsutsanazi , VLOOKUP akubwezerani molingana ndi Lookup_value, kapena, ngati palibe mndandanda womwewo, VLOOKUP amabwezera mtengo waukulu wotsatira. Pogwiritsa ntchito njirayi, deta yoyamba ya Table_array iyenera kukonzedwa mokweza.
• Ngati FALSE, VLOOKUP idzagwiritsanso ntchito zofanana ndi Lookup_value. Ngati pali zikhulupiliro ziwiri kapena zingapo m'ndandanda yoyamba ya Table_array yomwe ikufanana ndi mtengo wokopa, mtengo woyamba unkagwiritsidwa ntchito. Ngati mndandanda weniweni sungapezeke, vuto la # N / A libwezedwa.

Mu phunziro ili, popeza tikuyang'ana dzina lapadera, Range_lookup idzayikidwa ku Bodza kotero kuti zofanana zenizeni zimabweretsedwanso ndi ndondomekoyi.

Maphunziro Otsogolera

1. Dinani pa Range_lookup mzere mu bokosi la dialog
2. Lembani mawu Onyenga pamzerewu kuti asonyeze kuti tikufuna VLOOKUP kubwezera mndandanda weniweni wa deta yomwe tikuifuna
3. Dinani OK kuti mukwaniritse bokosi lalingaliro lamanzere ndi loyandikira
4. Popeza sitinalowemo dzina la kampani mu selo D2, vuto la # N / A liyenera kupezeka mu selo E2

03 a 03

Kuyesera Njira Yowonda Kumanzere

Dongosolo Lobwezera ndi Fomu Yoyang'ana Kumanzere

Kuti mupeze omwe makampani amapereka magawo omwe, lembani dzina la kampani kukhala selo D2 ndikusindikizira ENTER kwa makiyi.

Dzina la gawo liwonetsedwa mu selo E2.

Maphunziro Otsogolera

1. Dinani pa selo D2 mu tsamba lanu la ntchito
2. Lembani Zambiri Zamagetsi mu selo D2 ndikusindikizira ENTER makiyi pa makiyi
3. Mauthenga Zagawo - gawo lomwe limaperekedwa ndi kampani Zogulitsa Zambiri - ziyenera kuwonetsedwa mu selo E2
4. Yesani njira yowonjezeramo polemba mayina ena a kampani mu selo D2 ndipo dzina lofanana nalo liyenera kuoneka mu selo E2

VLOOKUP Zolakwa Mauthenga

Ngati uthenga wolakwika monga # N / A umawoneka mu selo E2, choyamba fufuzani zolakwika zapelulo mu selo D2.

Ngati kalembedwe si vuto, mndandanda wa mauthenga olakwika a VLOOKUP angakuthandizeni kudziwa komwe vuto liri.

Kuthetsa CHOOSE Ntchito ya Ntchito

Monga tanenera, mu chigamulochi, CHOOSE ntchito ili ndi ntchito ziwiri:

• imapanga magome omwe ali ndi zigawo ziwiri zokha - zigawo D ndi F
• imasintha ufulu kumanzere a ndondomeko m'magulu a tebulo kotero kuti ndime F imabwera yoyamba ndipo D yanu yachiwiri

Kupanga Mndandanda Wowonjezera Pawiri

Chizindikiro cha CHOOSE ntchito ndi:

= CHOOSE (Mndandanda wa Index, Mtengo1, Value2, ... Value254)

CHOYENERA ntchito imabweretsanso mtengo umodzi kuchokera pa mndandanda wa zikhulupiliro (Value1 mpaka Value254) zochokera ku nambala ya ndondomeko yomwe inalowa.

Ngati nambala ya ndondomekoyi ndi 1, ntchitoyo imabwerera ku Value1 kuchokera mndandanda; ngati nambala ya ndondomekoyi ndi 2, ntchitoyo imabwerera ku Value2 kuchokera mndandanda ndi zina zotero.

Polemba manambala ambiri; Komabe, ntchitoyo idzabwereranso malingaliro osiyanasiyana muyonse yomwe ikufunidwa. KUSANKHA kubwereranso miyezo yambiri kumapangidwira pakupanga mndandanda .

Kulowa mndandanda kumapangidwa ndi kuzungulira manambala omwe amalowa ndi maboda okhwima kapena mabotolo. Nambala ziwiri zalowa mu nambala ya index: {1,2} .

Tiyenera kukumbukira kuti kusankha sikutanthauza kupanga mapaundi awiri a mndandanda. Pogwiritsa ntchito nambala yowonjezera - monga {1,2,3} - ndi zina zoonjezerapo pamaganizo a mtengo wapatali, magome atatu a mzere angapangidwe.

Zowonjezera zikhodzalo zingakulolezeni kuti mubwererenso chidziwitso chosiyana ndi njira yowonjezereka pokhapokha mutasintha ndondomeko ya VLOOKUP ya ndondomeko ya chiwerengero cha chiwerengero cha chiwerengero chazomwe zili ndi zomwe mukufuna.

Kusintha Mndandanda wa Ma Coloni ndi CHOOSE Function

Mu CHOOSE ntchito yogwiritsidwa ntchito mwachondomeko ichi: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) , mndandanda wa chigawo F uli wolembedwa patsogolo pa ndime D.

Popeza CHOOSE ntchito imapanga tebulo la VLOOKUP - gwero la deta la ntchitoyo - kusintha ndondomeko ya zipilala mu CHOOSE ntchito imadutsa ku VLOOKUP.

Tsopano, mpaka VLOOKUP ikukhudzidwa, mndandanda wa tebulo ndi mizati iwiri yokhala ndi chigawo F kumanzere ndi chigawo D kumanja. Popeza ndime F imakhala ndi dzina la kampani yomwe tikufunafuna, ndipo popeza chigawo D chili ndi mayina, VLOOKUP idzachita ntchito yake yodziwika bwino pofufuza deta yomwe ili kumanzere kwa chiwerengerocho.

Chotsatira chake, VLOOKUP ikhoza kugwiritsa ntchito dzina la kampani kuti lipeze gawo limene amapereka.