Excel Nzira mbiri dzekushandisa kushandisa VLOOKUP Chikamu 2

01 of 06

Kutanga Nested MATCH Function

Dzokera Kune Chikamu 1

Kupinda maMATCH Function seNoram Index Index Argument

Kazhinji VLOOKUP inongodzorera data kubva kune imwe koromo yedhafura ye data uye iyi column inogadziriswa nenhamba yekorodho yenhamba yenyaya.

Zvisinei, mumuenzaniso uyu tine matanhatu matatu atinoda kuwana dhidhiyo saka tinoda nzira yekushandura nyore nyore nhamba yekorodho yenhamba kunze kwekugadzirisa maitiro edu ekutsvaga.

Apa ndipo pane basa reMATCH rinouya. Ichatibvumira kufananidza nhamba yekorodho kune zita remunda - kana munaJanuary, February, kana March - izvo zvatinonyora muchitokisi E2 chebasa rekushanda.

Nesting Mabasa

Iko basa reMATCH, naizvozvo, rinoshanda seVLOOKUP's column column index argument .

Izvi zvinoitwa nekugadzira basa reMATCH mukati meVLOOKUP muCol_index_num line yebhokisi rebhokisi.

Kupinda kweMATCH Function Manually

Apo nesting inoshanda, Excel haitibvumiri kuti tive nemukana wechipiri webhokisi rebasa rekuti tiise mhinduro dzayo.

Iko MATCH basa, saka, inofanira kuiswa manyore muCol_index_num line.

Paunopinda maitiro nemaoko, nharo imwe neimwe yebasa inofanira kuparadzaniswa nekma "," .

Tutorial Steps

Kupinda muMATCH Function's Lookup_value Argument

Nhanho yekutanga yekupinda munzvimbo yakasvibiswa MATCH basa ndeyokupinza mhirizhonga ye Lookup_value .

The Lookup_value ichava nzvimbo kana sero renhoroondo yezwi rekutsvaga ratinofanira kufanana naro mu database.

1. Mune VLOOKUP basa bhokisi rebhokisi, tora paCol_index_num line.
2. Tora iyo basa basa zita rinoteverwa nebhokisi rakashama rakasvibira " ( "
3. Dzvanya pane cell E2 kuti uise iyo seferensi mubhokisi rebhokisi.
4. Tora comma "," mushure memutero wechiratidzo E3 kukwanise kupinda kweMATCH function's Lookup_value argument.
5. Siya basa reVLOOKUP rebhokisi rebhokisi rakasununguka kuitira danho rinotevera mune zvidzidzo.

Muchikamu chekupedzisira chekudzidzisa maLookup_values ​​achapinda mumasero D2 neE2 emushandi webasa.

02 of 06

Kuwedzera Lookup_array nokuda kweMATCH Function

Kuwedzera Lookup_array nokuda kweMATCH Function

Ichi chinyorwa chinowedzera kuwedzera mhinduro yeLookup_array yeNATED yakasikwa basa.

Iko Lookup_array ndiyo mhando yemasero ayo MATCH basa ichatsvaga kuwana mhinduro ye Lookup_value yakawedzerwa muchigamba chekutanga chekudzidzisa.

Mumuenzaniso uyu, tinoda basa reMATCH kutsvaga masero D5 kusvika kuG5 kuitira mutsara kune zita remwedzi unozopinda muchitokisi E2.

Tutorial Steps

Aya matanho anofanira kupinda mushure mokunge komma inopinda mudanho rekare pamusoro peCol_index_num mutsara muVLOOKUP basa rebhokisi rebhokisi.

1. Kana zvichidikanwa, chengeta paCol_index_num line mushure mokunge comma iise iyo insertion point pamagumo ekupinda ikozvino.
2. Simbisai masero D5 kusvika kuG5 mune pepa rekushandisa kuti uise izvi zvinyorwa zvinyorwa sezvenzira iyo basa iri kutsvaga.
3. Dhinda f4 key pabhobhoyo kuti uchinje uwandu huno kuva zvizere zvese zvinyorwa . Kuita izvi kuchaita kuti zvikwanise kukopa iyo yekupedzesera yekutsvaga fomu kune dzimwe nzvimbo mune pepa rekushanda mune danho rekupedzisira rekudzidzisa
4. Tora comma "," mushure memutsetse wekare E3 kukwanise kupinda kweMATCH function's Lookup_array argument.

03 of 06

Kuwedzera Mhando yeMatch uye Kuzadza Basa reMATCH

Kuwedzera Mhando yeMatch uye Kuzadza Basa reMATCH

Nharo yechitatu uye yokupedzisira yeMATCH basa ndiyo Match_type argument.

Iyi nhaurirano inoudza Excel nzira yekufananidza neLookup_value nemitemo muLookup_array. Zvisarudzo ndezvi: -1, 0, kana 1.

Iyi nharo inofanirwa. Kana ikasiyiwa basa racho rinoshandisa kukosha kwekutengesa kwe 1.

• kana Match_type = 1 kana kuti yakasiyiwa: MATCH inowana kukosha kukuru kudarika kana kuenzana neLookup_value. Kana ichi chinokosha chasarudzwa, data yeLookup_array inofanira kusarudzwa mukukwira.
• kana Match_type = 0: MATCH inowana kukosha kokutanga yakakodzana chaizvo neLookup_value. Dhora reLookup_array rinogona kugadziriswa chero ripi zvaro.
• kana Match_type = 1: MATCH inowana kukosha kuduku kudarika kana kuenzana neLookup_value. Kana iyi inokosha yakasarudzwa, data yeLookup_array inofanira kugadziriswa ichidzika pasi.

Tutorial Steps

Aya matanho anofanira kupinda mushure mokunge komma inopinda mudanho rekutanga pane Row_num line mubato reVLOOKUP rebhokisi rebhokisi.

1. Kutevedzera komupiri wechipiri paCol_index_num line, tora zero " 0 " sezvo tichida basa rakasvibiswa kuti tidzorere mutsara chaiyo kusvika mwedzi upinda muchitokisi E2.
2. Tora chivharo chakavhara " ) " kupedzisa MATCH basa.
3. Siya basa reVLOOKUP rebhokisi rebhokisi rakasununguka kuitira danho rinotevera mune zvidzidzo.

04 of 06

Kupinda muVLOOKUP Range Lookup Argument

Range Lookup Argument

VLOOKUP's Range_lookup mhirizhonga inokosha (TRUE kana FALSE chete) inoratidza kana iwe uchida VLOOKUP kuwana mhosho chaiyo kana kuti yakatarisana neLookup_value.

• Kana CHOKWADI kana kana mhirizhonga iyi isingabvumirwi, VLOOKUP inodzorera kana yakanyatsoenderana neLookup_value, kana, kana mutsara chaiwo usingawanikwi, VLOOKUP inodzorera iyo inotevera kukosha kukuru. Nokuda kwemuumbiro wekuita izvi, data iri muhombe yekutanga yeTable_array inofanira kuenzanisirwa mukukwira .
• Kana FALSE, VLOOKUP achangoshandisa mhando chaiyo yeLookup_value. Kana pane zviyero zviviri kana kupfuura muhombe yekutanga yeTable_array iyo inofanidza kukosha kwekuona, kukosha kokutanga kunowanikwa kunoshandiswa. Kana mutsara chaiwo usina kuwanikwa, # N / A kukanganisa kunodzorerwa.

Muchidzidzo ichi, sezvo isu tichitsvaga vatengesi vekutengesa kweimwe mwedzi, tichaisa Range_lookup yakaenzana neNhema.

Tutorial Steps

1. Dzvanya paRange_lookup mutsara mubhokisi rebhokisi
2. Tora shoko rokuti Manyepo munharaunda iyi kuratidzira kuti tinoda VLOOKUP kudzorera mamiriro chaiwo ye data yatinenge tichitsvaka
3. Dzvanya OK kuti upedze mapepa maviri ekutsvaga fomu uye pedyo nebhokisi rebhokisi
4. Sezvo isu tisati tava kunyoreswa kwekutsvaga mumasero D2 uye E2 n # N / A kukanganisa kuchavapo muchitokisi F2
5. Iko kukanganisa kuchagadziriswa mune danho rinotevera mune zvidzidzo patinozowedzera zvidzidzo zvekutsvaga mune danho rinotevera rekudzidzisa.

05 of 06

Kuedza nzira mbiri yekuverenga

Kuedza nzira mbiri yekuverenga

Kuti ushandise nzira mbiri yekutsvaga mazano kuti uwane dhizimusi rekutengesa dheteti yemakisi akasiyana akanyorwa mutafura yepafura, nyora zita rekiokie muchitokisi D2, mwedzi uri muchitsetse E2 uye shandisa ENTER key pa keyboard.

Nhamba yekutengesa ichave yakaratidzwa muchitokisi F2.

Tutorial Steps

1. Dzvanya pane cell D2 mubasa rako rekushanda
2. Tora Oatmeal mu cell D2 uye shandisa ENTER key pabhodi
3. Dzvanya pane cell E2
4. Tora February mu cell E2 uye shandisa ENTER key pa keyboard
5. Iko inokwana $ 1,345 - iyo yekutengesa yeOatmeal cookies mumwedzi waFebruary - inofanira kuiswa muchitokisi F2
6. Panguva ino, pepa rako rekushanda rinofanira kufanana nemuenzaniso uri papeji 1 yemudzidzi uyu
7. Edzai nzira yekutsvaga ichiwedzera nekunyora chero kusanganiswa kwemhando dzekio uye mwedzi iripo muTable_array uye vatengesi mafananidzo vanofanira kuiswa muchitokisi F2
8. Nhanho yekupedzisira mune zvidzidzo inosanganisira kukopa fomu yekutsvaga kushandisa Fill Handle .

Kana mhinduro yekanganiso yakadai se #REF! inowanikwa muchitokisi F2, urongwa urwu hweVLOOKUP mhosho dzedambudziko dzinogona kukubatsira kuziva kuti chinetso chiripi.

06 of 06

Kupikira Dhiviri Dimensional Lookup Formu ne Fill Handle

Kupikira Dhiviri Dimensional Lookup Formu ne Fill Handle

Kuti zvive nyore kuenzanisa nhoroondo yemwedzi yakasiyana-siyana kana majiki akasiyana, fomu yekutsvaga inogona kunyorwa kune mamwe masero kuti zviyero zvakawanda zvigone kuratidzwa panguva imwe chete.

Sezvo deta iri kuiswa mumutambo wekugara mune pepa rekushanda, tinogona kukopa iyo yekutsvaga fomu muchitokisi F2 kusvika pane sefaira F3.

Sezvo chirevo chacho chakakopihwa, Excel ichavandudza nhamba yezvikamu zvinyorwa kuratidza nzvimbo itsva yefomula yacho. Munyaya iyi D2 inova D3 uye E2 inova E3,

Uyewo, Excel inochengetedza sero rinoshandiswa zvakafanana zvakafanana nechengeti $ D $ 5: $ G $ 5 inoramba iri yakafanana kana fomu yacho ikakopizwa.

Pane nzira inopfuura imwe yekukopa deta muExcel, asi zvichida nzira iri nyore ndeyokushandisa Fill Handle.

Tutorial Steps

1. Dzvanya pane cell D3 mune pepa rako rekushanda
2. Tora Oatmeal mu cell D3 uye shandisa ENTER key pabhodi
3. Dzvanya pane cell E3
4. Tora Marita muSerus E3 uye shandisa ENTER key pabhodi
5. Dzvanya pane seferi F2 kuti uite sero rinoshanda
6. Isa iyo pointer yeguru pamusoro pekona mutsara muzasi kwekona yekona. Iyo pointer ichashandura kune imwe chiratidzo "+" - iyi ndiyo Fill Handle
7. Dzvanya kumusana webhenekisi mutsara uye wedhonzai kuzadza tsvimbo pasi kune cell F3
8. Bvisa mutsara ye mouse uye seferi F3 inofanira kuva neyese dimensional lookup formula
9. Iko inosvika $ 1,287 - mutengo wekutengesa weOatmeal cookies mumwedzi waMarch- inofanira kuiswa muchitokisi F3