Tsvaga Mashoko muDheta Mathesi neExcel LOOKUP Function

01 ye 01

Excel LOOKUP Function Tutorial mu Array Form

Basa re Excel LOOKUP rine mafomu maviri: Vector Form uye Array Form .

Mhando yakasiyana yeHOUOKUP basa yakafanana nedzimwewo Excel lookup mabasa akadai saVLOOKUP neHLOOKUP pakuti inogona kushandiswa kuwana kana kutarisa pamusoro pemaonero anokosha ari mutafura ye data.

Iyo yakasiyana sei ndeyokuti:

1. NeVLOOKUP neHLOOKUP, unogona kusarudza imwe korombo kana musara kuti udzorere nhamba ye data kubva, apo LOOKUP nguva dzose inodzorera kukosha kubva kumutsara wekupedzisira kana koromo mukati.
2. Mukuedza kutsvaga kutarisana kwehuwandu hwakatarwa - unozivikanwa seLookup_value - VLOOKUP inongotsvaka kori yekutanga ye data uye HLOOKUP mutsara wekutanga chete, apo basa re LOOKUP richatsvaga mutsara wokutanga kana koromo zvichienderana nehuwandu hwemhando .

TARIRA Kushanda uye Array Shape

Chimiro chemafuro - ingave akaenzana (nhamba yakaenzana yemakoroni nemitsara) kana rutsanga (isina kufanana nendima nemitsetse) - inopinda apo basa re LOOKUP rinotsvaga deta:

• Kana ruvara rwakagadzirwa nehupamhi kana kuti rutsanga rurefu ( rurefu kupfuura rwakafara), LOOKUP inotora kuti data yakarongerwa mumakoroni uye naizvozvo inotsvaga mutsara kuLookup_value mukomboranga yekutanga yerudzi.
• Kana ruvara rutsanga rwakakura (rwakakura kudarika rwakareba), LOOKUP inotora kuti data yakarongwa mumitsara uye saka inotsvaga mutsara kuLookup_value mumutsara wokutanga wezera.

IYOOKUP Function Syntax neArguments - Array Form

Mutsara weArray Form ye LOOKUP basa ndewokuti:

= LOOKUP (Lookup_value, Array)

Lookup_value (inotarisirwa) - kukosha kwokuti basa rinotsvaga pane zvakarongwa. Iko Lookup_value inogona kuva nhamba, zvinyorwa, kukosha kwepamusoro, kana zita kana sero rekuti rinoreva kukosha.

Mhando (inodikanwa) - masero akareba ayo basa rinotsvaga kuti riwane Lookup_value. Dheta inogona kuva mutsara, nhamba, kana kuti maonero akanaka.

Notes:

• Nekuda kwekuita basa kuti rishande zvakanaka, Argum argument inofanira kurongerwa mukukwira (A kusvika Z kana duku kusvika kune yakakura kune nhamba)
• Kana basa racho risingagoni kuwana mutsara chaiwo weLookup_value, inosarudza kukosha kukuru pane Array iyo isingapfuuri kana yakaenzana inokosha kuLookup_value
• Kana iLookup_value isipo kana kuti iduku kudarika yose inokosha muArray, basa re LOOKUP richadzoka # N / A kukanganisa

Muenzaniso Uchishandisa Fomu yeAraw yoKUTAIWA KUSA

Sezvakaratidzwa mumufananidzo uri pamusoro, muenzaniso uyu uchishandisa Fomu yeAraray yeO LOOKUP basa kuti uwane mutengo weWhachamacallit mumutauro wekutsvaga.

Chimiro chemafuro ndecherekete refu . Nekudaro, basa racho richadzoka kukosha kunowanikwa muhomwe yekupedzisira yenhamba yekutsvaga.

Kuronga Dhiyabhorosi

Sezvinoratidzwa mundima dziri pamusoro, dheti iri mumutambo inofanira kuenzanisirwa mukukwira urongwa kuitira kuti basa re LOOKUP rishande zvakanaka.

Paunenge uchinyora data muExcel zvakakosha kuti utange kusarudza zvinyorwa uye mitsetse yedata kuti igadziriswe. Kazhinji izvi zvinosanganisira zvinyorwa.

1. Simbisai masero machena A4 kusvika C10 mubasa rekushanda
2. Dzvanya paData rebhu re menu yebhoni
3. Dzvanya paGadziriro sarudzo mukati mebhoni kuti uzarure Hurongwa hwebhokisi yebhokisi
4. Pasi peColumn iri mubhokisi rebhokisi rinosarudza kugadzirisa neChikamu kubva pane zvinyorwa zvinyorwa
5. Kana zvichidikanwa, pasi peMutungamiri uri kutungamira sarudza Maitiro kubva pakaderera pasi mazita
6. Kana zvichidikanwa, pasi peMutungamiri weMutungamiri sarudza A kusvika Z kubva pakaderera pasi mazita
7. Dzvanya OK kuti urongedze data uye uvhare bhokisi rebhokisi
8. Mutemo we data unofanirwa kuenderana zvinoonekwa mumufananidzo wepamusoro

LOOKUP Function Example

Kunyange zvazvo zvichibvira kungonyora basa LOOKUP

= LOOKUP (A2, A5: C10)

muchengeti remashandiro, vanhu vazhinji vanoona zviri nyore kushandisa bhokisi rebasa rebasa.

Bhokisi rebhokisi rinokubvumira kupinda mhirizhonga imwe neimwe kune imwe mutsara usina kunyunyuta pamusoro peshoko rekushanda - sevabereki uye separma vatengesi pakati pemakakatanwa.

Nhanho iri pasi apa tsanangudzo iyo basa re LOOKUP rakapinda sei muchitsi B2 uchishandisa bhokisi rebhokisi.

1. Dzvanya pachitokisi B2 mune pepa rekushanda kuti uite sero rinoshanda ;
2. Dzvanya paMafomu eTom;
3. Sarudza Kuona uye Kutarisa kubva kubhoni kuti uzarure basa rinoderedza pasi;
4. Dzvanya pa LOOKUP munyaya kuti uuye neSarudzo dzinopikisana nebhokisi rebhokisi;
5. Dzvanya pane lookup_value, array chisarudzo mukati mutsamba;
6. Dzvanya OK kuti uwedzere Basa reArguments dialog box;
7. Mubhokisi rebhokisi, tora paLowokup_value line;
8. Dzvanya pane sero A2 mune pepa rekushandisa kuti uise iyo seferensi yekero mubhokisi rebhokisi;
9. Dzvanya paIndaneti mubhokisi rebhokisi
10. Simbisai masero A5 kusvika kuC10 mubasa rekushandisa kuti uise hutabhenakeri iyi mubhokisi rebhokisi - urwu hune mitsara yose inotsvakwa nehutano
11. Dzvanya OK kuti upedze basa uye uvhare bhokisi rebhokisi
12. An # N / A kukanganisa kunowanikwa muchitokisi E2 nokuti hatisati tinyora zita remukati muchitokisi D2

Kupinda Kukosha Kwekuona

1. Dzvanya pachitokisi A2, tora Whachamacallit uye tapinda kuIta yekicha pane keyboard;
2. Iko inokosha $ 23.56 inofanira kuonekwa muchitokisi B2 sezvo ichi chiri mutengo weWhachamacallit iri mukupedzisira koromo yedatafura yedata;
3. Edza basa racho nekunyora mamwe mazita mazita muchitokisi A2. Mutengo wezvikamu chimwe nechimwe muurongwa uchaonekwa muchitokisi B2;
4. Paunenge uchinge uri pa cell E2 yakakwana basa = LOOKUP (A2, A5: C10) inowanikwa mubhairi yepamusoro pane pepa pepaji.