Excel Kupeza Njira Zili Pogwiritsa Ntchito VLOOKUP Part 2

01 ya 06

Kuyambira Mgwirizano wa NTHAWI Yogwidwa

Bwererani ku Gawo 1

Kulowa MCHINYAMATA Ikugwira ntchito monga Nambala ya Index Yotsutsana

Kawirikawiri VLOOKUP imabwezeretsa deta kuchokera ku ndondomeko imodzi ya tebulo la deta ndipo ndimeyi imayikidwa ndi ndondomeko ya chiwerengero cha ndondomeko.

Komabe, muchitsanzo ichi tili ndi zigawo zitatu zomwe tikufuna kupeza deta kotero kuti tikusowa njira yosinthira chiwerengero cha ndondomeko ya mndandanda popanda kusintha mawonekedwe athu.

Apa ndi pamene ntchito MATCH ikugwiritsidwa ntchito. Zidzatiloleza ife kufanana ndi nambala ya mndandanda ku dzina lachidziwitso - kaya ndi Januwale, February, kapena March - zomwe timayika mu selo E2 ya pepala.

Ntchito Yogwiritsira Ntchito

Choncho ntchito ya MATCH imachita ngati VLOOKUP's column index index argument .

Izi zikukwaniritsidwa mwakutseketsa ntchito YAMAKONSO mkati mwa VLOOKUP mu Col_index_num mzere wa bokosi la bokosi.

Kulowa KUGWIRITSIDWA NTCHITO MOPANDA

Pamene chisa chikugwira ntchito, Excel sitilola kuti titsegule bokosi lachiwiri la ntchito kuti tilowetse zifukwa zake.

Ntchito YAMATCH, chotero, iyenera kulowa mwa Col_index_num mzere.

Mukamalowa ntchito pamanja, mfundo zonsezi zimagwirizanitsidwa ndi chiwerengero "," .

Maphunziro Otsogolera

Kulowetsa MATCH's Lookup_value Kukangana

Gawo loyamba lolowa mu chithunzi cha MATCH chodyetsedwa ndilowetsa mkangano wa Lookup_value .

Lookup_value idzakhala malo kapena selo loyang'ana pa nthawi yomwe mukufuna kufufuza.

1. Mu ntchito ya VLOOKUP dialog box, dinani pa Col_index_num mzere.
2. Lembani mndandanda wa dzina la ntchito motsatizana ndi bolodi lozungulira lotseguka " ( "
3. Dinani pa selo E2 kuti mulowetse selolo mu bokosi la dialog.
4. Lembani comma "," kutanthauzira selo la E3 kukwaniritsa malowedwe a MATCH's Lookup_value argument.
5. Siyani ntchito ya VLOOKUP yolemba bokosi lomwe likutsegulira sitepe yotsatira mu phunziroli.

Mu sitepe yotsiriza ya phunziro la Lookup_values ​​lidzalowetsedwa mu maselo D2 ndi E2 pa tsamba la ntchito .

02 a 06

Kuwonjezera pa Lookup_array kwa Ntchito MATCH

Kuwonjezera pa Lookup_array kwa Ntchito MATCH

Khwerero iyi ikuphatikizapo kuwonjezera mkangano wa Lookup_array wa ntchito yomanga MATCH.

Lookup_array ndi maselo osiyanasiyana omwe MATCH ntchito ikuyesa kupeza fufuzani Lookup_value yowonjezeredwa mu sitepe yapitayi ya phunzirolo.

Mu chitsanzo ichi, tikufuna kuti MATCH ntchito ifufuze maselo D5 ku G5 kuti awonane ndi dzina la mwezi womwe udzalowa mu selo E2.

Maphunziro Otsogolera

Zochitika izi ziyenera kulowetsedwa pambuyo poti komma inalowa mu sitepe yapitayi pa Col_index_num mzere mu bokosi la ntchito la VLOOKUP.

1. Ngati ndi kotheka, dinani pa Col_index_num mzere pambuyo pa comma kuti muike tsamba lolowetsa kumapeto kwa kulowa.
2. Onetsetsani maselo D5 kuti G5 muwunikirayi kuti mulowetse maumboni awa monga momwe ntchitoyi ikufunira.
3. Dinani fayilo F4 pa kibokosiko kuti musinthe makanemawa kuti mukhale maofesi angapo. Kuchita zimenezi kudzakuthandizani kufotokozera fomu yomaliza yomaliza kumalo ena ku tsamba lothandizira pamapeto
4. Lembani comma "," kutanthauzira selo ya E3 kukwaniritsa malowedwe a MATCH a Lookup_array .

03 a 06

Kuwonjezera Mtundu wa Match ndi Kumaliza Ntchito YAMATCH

Kuwonjezera Mtundu wa Match ndi Kumaliza Ntchito YAMATCH

Kukambirana kwachitatu ndi kotsiriza kwa ntchito MATCH ndi Match_type mtsutso.

Mtsutso uwu umauza Excel momwe mungagwirizanitse ndi Lookup_value ndi mfundo mu Lookup_array. Zosankha ndi: -1, 0, kapena 1.

Mtsutso uwu ndi wosankha. Ngati iyo yasiya ntchitoyi imagwiritsa ntchito mtengo wosasintha wa 1.

• ngati Match_type = 1 kapena yasiya: MATCH imapeza mtengo waukulu kwambiri womwe uli wochepa kapena wofanana ndi Lookup_value. Ngati mtengowu wasankhidwa, deta ya Lookup_array iyenera kusankhidwa kukwera.
• ngati Match_type = 0: MATCH amapeza mtengo woyamba umene uli wofanana ndi Lookup_value. Deta ya Lookup_array ikhoza kusankhidwa mu dongosolo lililonse.
• ngati Match_type = 1: MATCH imapeza mtengo wochepa kwambiri kuposa kapena wofanana ndi Lookup_value. Ngati mtengowu wasankhidwa, deta ya Lookup_array iyenera kusankhidwa potsika.

Maphunziro Otsogolera

Njira izi ziyenera kulowetsedwa pambuyo poti mavoti adalowa mu sitepe yoyamba pa Row_num line mu bokosi la ntchito la VLOOKUP.

1. Potsatira chiwerengero chachiwiri pa Col_index_num mzere, yesani zero " 0 " chifukwa tikufuna ntchito yodzala kuti tibwererenso mzere woyenera ndi mwezi womwe umalowa mu selo E2.
2. Lembani mzere wozungulira " ) " kukwaniritsa ntchito MATCH.
3. Siyani ntchito ya VLOOKUP yolemba bokosi lomwe likutsegulira sitepe yotsatira mu phunziroli.

04 ya 06

Kulowa pa VLOOKUP Mbali Yofuna Kupikisana

Kutsutsana kwa Range Lookup

Mndandanda wa Range_lookup wa VLOOKUP ndiwotheka (ZOONA kapena ZOKHUDZA zokha) zomwe zimasonyeza ngati mukufuna VLOOKUP kupeza mndandanda weniweni kapena wofanana ndi Lookup_value.

• 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 malonda ogulitsa mwezi umodzi, tidzakhazikitsa Range_lookup yofanana ndi Yonyenga .

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 mutsirize fomu yamakono yoyang'ana ndikuyang'anapo
4. Popeza kuti sitinalowetse chiwerengero cha maselo a D2 ndi E2, nambala # N / A idzakhalapo mu selo F2
5. Cholakwika ichi chidzakonzedwanso mu sitepe yotsatira mu phunziro pamene tiphatikiza zofunikira pazomwe tikuphunzira pazotsatira yotsatira.

05 ya 06

Kuyesera Njira Yoyamba Yowunikira

Kuyesera Njira Yoyamba Yowunikira

Kuti mugwiritse ntchito njira ziwiri zomwe mungagwiritsire ntchito pogwiritsa ntchito ma cookies olembedwa patebulo, lembani dzina lakhuni mu selo D2, mwezi mu selo la E2 ndikusindikizira ENTER kwa makiyi.

Deta ya deta idzawonetsedwa mu selo F2.

Maphunziro Otsogolera

1. Dinani pa selo D2 mu tsamba lanu la ntchito
2. Lembani Oatmeal mu selo D2 ndikusindikizira ENTER kwachinsinsi pa kambokosi
3. Dinani pa selo E2
4. Lembani February mu selo E2 ndikusindikizira ENTER kwachinsinsi pa kambokosi
5. Mtengo wa $ 1,345 - malonda a oatmeal ma cookies mwezi wa February - ayenera kuwonetsedwa mu selo F2
6. Pano, tsamba lanu la ntchito liyenera kufanana ndi chitsanzo patsamba 1 la phunziroli
7. Yesani njira yowonjezeramo polemba mtundu uliwonse wa mitunduyi ndi miyezi yomwe ilipo mu Table_array ndipo owonetsera malonda ayenera kuwonetsedwa mu selo F2
8. Gawo lomaliza la phunzirolo limaphatikizapo kukopera fomu yojambula pogwiritsira ntchito Lembani Mankhwala .

Ngati uthenga wolakwika monga #REF! imapezeka mu selo F2, mndandanda wa mauthenga olakwika a VLOOKUP angakuthandizeni kudziwa komwe vuto liri.

06 ya 06

Kujambula Zopangidwe Zachiwiri Zowonjezera ndi Kudzaza Mankhwala

Kujambula Zopangidwe Zachiwiri Zowonjezera ndi Kudzaza Mankhwala

Kuti mukhale ophweka kuyerekezera deta ya miyezi yosiyanasiyana kapena ma cookies osiyana, fomuyi ingakopilidwe ku maselo ena kuti ndalama zambiri ziwonetsedwe panthawi yomweyo.

Popeza kuti detayi imayikidwa mwatsatanetsatane mu tsamba la ntchito, tikhoza kufotokozera fomu yojambulira mu selo F2 ku selo F3.

Pamene njirayi imakopedwa, Excel idzasintha mafotokozedwe ofanana omwe ali nawo kuti afotokoze malo atsopanowo. Pankhaniyi D2 imakhala D3 ndipo E2 imakhala E3,

Ndiponso, Excel imasunga mndandanda wa maselo amodzimodzi kuti $ D $ 5: $ G $ 5 azikhala chimodzimodzi pamene chikhomocho chikukopedwa.

Pali njira imodzi yokha yosinthira deta ku Excel, koma mwinamwake njira yosavuta ndiyo kugwiritsa ntchito Mankhwala Odzaza.

Maphunziro Otsogolera

1. Dinani pa selo D3 mu tsamba lanu la ntchito
2. Lembani Oatmeal mu selo D3 ndikusindikizira ENTER kwachinsinsi pa kambokosi
3. Dinani pa selo E3
4. Sakani March mu selo E3 ndikusindikizira ENTER makiyi pa makiyi
5. Dinani pa selo F2 kuti mupange selo yogwira ntchito
6. Ikani zojambula pamanja pamtunda wakuda kumbali ya kumanja. Chojambulacho chidzasintha ku chizindikiro chowonjezera "+" - ichi ndi Chidzanja Chodzaza
7. Dinani batani lamanzere lachitsulo ndikukoka gwiritsirani mankhwalawo mpaka selo F3
8. Tulutsani botani la mouse ndi selo F3 ziyenera kukhala ndi mawonekedwe awiri oyang'ana
9. Mtengo wa $ 1,287 - malonda a Oatmeal ma cookies mwezi wa March- ayenera kuwonetsedwa mu selo F3