Excel Kuwoneka Kwa Njira Zili Pogwiritsa Ntchito VLOOKUP Part 1

Mwa kuphatikiza ntchito ya VLOOKUP ya Excel ndi MATCH ntchito , tikhoza kupanga njira yodziwika ndi njira ziwiri kapena ziwiri zomwe zimakulolani kuti muwerenge magawo awiri a chidziwitso mu deta kapena tebulo la deta.

Njira yowunikira njira ziwiri zimathandiza pamene mukufuna kupeza kapena kuyerekeza zotsatira za zosiyana zosiyanasiyana.

Mu chitsanzo chomwe chikuwonetsedwa pa chithunzi pamwambapa, njira yowonjezera imapangitsa kuti zikhale zosavuta kupeza malonda a malonda a ma cookies osiyana mu miyezi yosiyana mwa kusintha dzina lakhuki ndi mwezi mu maselo olondola.

01 ya 06

Pezani Dera pa Njira Yotsutsana ndi Mzere ndi Mzere

Phunziroli laphatikizidwa mu magawo awiri. Kutsatira ndondomeko zotchulidwa mu gawo lirilonse kumapanga njira yopezera njira ziwiri zomwe zikuwonetsedwa mu chithunzi pamwambapa.

Phunziroli limaphatikizapo kupeza chinyumba cha MATCH mkati mwa VLOOKUP.

Kukhazikitsa ntchito kumaphatikizapo kulowa ntchito yachiwiri ngati imodzi mwa mfundo zogwirira ntchito yoyamba.

Mu phunziro ili, ntchito ya MATCH idzalowetsedwa ngati VLOOKUP ndondomeko ya chiwerengero cha mndandanda.

Masewera Zamkatimu

• Kulowa Datorial Data
• Kupanga Zina Zogwiritsidwa Ntchito pa Data Deta
• Kuyambira ntchito ya VLOOKUP
• Kulowetsa Kufunika Koyang'ana
• Kulowa Mndandanda wa Mndandanda Wotsutsana
• Kuyambira Mgwirizano wa NTHAWI Yogwidwa
• Kuwonjezera Mapangidwe a Dongosolo kwa Ntchito MATCH
• Kuwonjezera Mtundu wa Match ndi Kumaliza Ntchito YAMATCH
• Kumaliza ntchito ya VLOOKUP
• Kuwonjezera Zowonjezera Zowonjezera kuyesa Fomu Yomaliza
• Kujambula Zopangidwe Zachiwiri Zowonjezera ndi Kudzaza Mankhwala

02 a 06

Kulowa Datorial Data

Gawo loyamba mu phunziroli ndilowetsa deta mu Excel sheet sheet .

Kuti mutenge tsatanetsatane mu phunzirolo lowetsani deta yosonyezedwa mu chithunzi pamwamba pa maselo otsatirawa.

• Lowetsani deta pamwamba pa maselo D1 mpaka F1
• Lowani chigawo chachiwiri mu maselo D4 mpaka G8

Mzere 2 ndi 3 zatsalira zokhazokha kuti zithetsere njira zofufuzira ndi ndondomeko yowonjezera yomwe idapangidwa pa phunziroli.

Maphunzirowa sakuphatikizapo maonekedwe omwe awonedwa mu fano, koma izi sizidzakhudza momwe fomu yamakono imagwirira ntchito.

Zambiri pazomwe mungapangire zofanana ndi zomwe tawonera pamwambazi zikupezeka mu phunziroli la Basic Excel Formatting .

Maphunziro Otsogolera

1. Lowetsani deta monga momwe mukuwonera pa chithunzi pamwambapa mu maselo D1 mpaka G8

03 a 06

Kupanga Zina Zogwiritsidwa Ntchito pa Data Deta

Njira yotchulidwa ndi njira yophweka yolankhulira deta yamtunduwu mu fomu. M'malo molemba mafotokozedwe a chipinda cha deta, mungathe kulemba dzina lazomwezo.

Phindu lachiwiri pogwiritsa ntchito dzina lake ndiloti mafotokozedwe a selo a mtundu uwu samasintha ngakhale pamene fomuyo imakopedwa kwa maselo ena pa tsamba la ntchito.

Maphunziro Otsogolera

1. Onetsetsani maselo D5 ku G8 pa tsamba kuti muwasankhe
2. Dinani pa Bokosi la Dzina lomwe lili pamwamba pa ndime A
3. Lembani "tebulo" (palibe ndemanga) mu Box Name
4. Onetsetsani ENTER yowonjezera pa kambokosi
5. Maselo D5 ku G8 tsopano ali ndi dzina la "tebulo". Tidzagwiritsa ntchito dzina la ndondomeko ya tablete ya VLOOKUP pamapeto pake

04 ya 06

Kutsegula Bokosi la Dialogu la VLOOKUP

Ngakhale kuti n'zotheka kungosungira kapangidwe kathu kolowera mu selolo, anthu ambiri amakumana ndi zovuta kusunga molunjika bwino - makamaka pazomwe zimagwiritsidwa ntchito mu phunziroli.

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 F2 la worksheet - malo kumene zotsatira za mawonekedwe awiri omwe mukuyang'ana pazithunzi zidzawonetsedwa
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

05 ya 06

Kulowetsa Kufunika Koyang'ana

Kawirikawiri, mtengo wamtengo wapatali umagwirizanitsa gawo la deta mu gawo loyamba la tebulo la deta.

Mu chitsanzo chathu, mtengo wamakono umatanthawuza mtundu wa coko tikufuna kupeza zambiri zokhudza.

Maulendo ovomerezeka a chiwerengero cha lookup ndi awa:

• deta yamtundu
• mtengo wogwirizana (WOONA kapena WOYERA)
• chiwerengero
• selo loyang'ana ku mtengo mu tsamba la ntchito

Muzitsanzo izi tidzalowa mu selo lotanthauzira komwe dzina lakhuki lidzakhazikitsidwe - selo D2.

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 . Ili ndilo selo yomwe tidzasindikiza dzina lakhuki zomwe tikufuna kudziwa

06 ya 06

Kulowa Mndandanda wa Mndandanda Wotsutsana

Mndandanda wa tebulo ndi gome la deta yomwe fomu yoyesera ikuyesa kupeza zomwe tikufuna.

Mzere wa tebulo uyenera kukhala ndi zigawo ziwiri za deta .

• chigawo choyamba chili ndi ndondomeko yamtengo wapatali (chiyambi cha phunziro)
• chingwe chachiwiri, ndi zina zowonjezera, zidzafufuzidwa ndi ndondomeko yowonjezera kuti mudziwe zambiri zomwe tikufotokoza.

Mtsutso waukulu wa tebulo uyenera kulowa monga mtundu uliwonse umene uli ndi mafotokozedwe a maselo a tebulo la deta kapena ngati dzina losiyana .

Pa chitsanzo ichi, titha kugwiritsa ntchito dzina lalitali lomwe lapangidwa mu gawo lachitatu la phunziroli.

Maphunziro Otsogolera

1. Dinani pa tebulo_mzere wozungulira mu bokosi la bokosi
2. Lembani "tebulo" (palibe ndemanga) kuti mulowetse mayina osiyanasiyana pazokangana
3. Siyani ntchito ya VLOOKUP yolemba bokosi lotsegulira gawo lotsatira la phunziroli

Pitirizani ku Gawo 2 >>