01 ea 01
Excel HO BUKA Tutorial ea Mosebetsi Tlhahlobo ea Array
Mosebetsi oa Excel LOOKUP o na le mefuta e 'meli: Foromo ea Vector le Foromo ea Array .
Sebopeho sa mosebetsi oa LOOKUP se tšoana le mesebetsi e meng ea Excel ea ho batla ho tšoana le VLOOKUP le HLOOKUP ka hore e ka sebelisoa ho fumana kapa ho sheba litekanyetso tse ikhethileng tse fumanehang tafoleng ea data.
Se fapane hakaakang ke hore:
- Ka VLOOKUP le HLOOKUP, o ka khetha hore na ke hokae kapa mohala ho khutlisa bohlokoa ba data ho tloha, ha LOOKUP e khutlisa kakaretso ho tsoa molaong oa ho qetela kapa kholatsong ea sehlopha .
- Ha u leka ho fumana papali bakeng sa boleng bo boletsoeng - e tsejoang e le Lookup_value - VLOOKUP feela e batlisisa karolo ea pele ea data le HLOOKUP feela mola oa pele, ha mosebetsi oa LOOKUP o tla sheba mola kapa oa mohala oa pele ho ea ka sebōpeho sa marang-rang .
BUKA TŠEBELETSO LE TŠEBELISO
Sebopeho sa maralo - ebang ke sekoti (palo e lekanang ea lihlopha le mela) kapa lekarete (palo e sa lekanyetsoang ea mela le mela) - e ama moo mosebetsi oa LOOKUP o batlisisang data:
- Haeba sehlopha se lekanngoe kapa se le lehlakoreng le lelelele (le lelelele ho feta le leholo), LOOKUP e nka hore data e hlophisitsoe ka meqomo 'me ka hona e batlisisa papaling ho Lookup_value ka lehong la pele la sehlopha.
- Haeba sehlopha ke lehlakore le leholo (ho lekaneng ho feta le lelelele), LOOKUP e nka hore data e hlophisitsoe ka mela 'me kahoo e batlisisa papaling ho Lookup_value molaong oa pele oa sehlopha.
Tšebeliso ea HO SEBELISA Syntax le Arguments - Fomati ea Letoto
Tlhaloso ea Sebopeho sa Array ea mosebetsi oa LOOKUP ke:
= LOOKUP (Lookup_value, Array)
Nako ea ho batla (e hlokehang) - bohlokoa boo mosebetsi o bo batlisang kahare. The Lookup_value e ka ba nomoro, mongolo, bohlokoa bo utloahalang, kapa lebitso kapa selefouno e bolelang bohlokoa.
Lethathamo (le hlokehang) - lisele tse ngata tseo mosebetsi o li batlang ho fumana Lookup_value. Dintlha e ka ba melaetsa, lipalo, kapa litekanyetso tse utloahalang.
Lintlha:
- Bakeng sa mosebetsi o shebahalang o sebetsang ka mokhoa o nepahetseng, khang ea Array e lokela ho hlophisoa ho nyoloha ka tatellano (A ho ea ho Z kapa e nyenyane ka ho fetisisa ho ea ho e kholo ka palo)
- Haeba mosebetsi o sitoa ho fumana papiso e tobileng ea Lookup_value, e khetha boleng bo boholo ka ho fetisisa ho Lethathamo le ka tlaase ho kapa le lekanang le boleng ho Lookup_value
- Haeba ho lookup_value e haella kapa e nyane ho feta litekanyetso tsohle tsa Array, mosebetsi oa LOOKUP o tla khutlisa phoso ea # N / A
Mohlala o Sebelisang Foromo e Ncha ea Mosebetsi oa HO RELELA
Joalokaha ho bonngoe setšoantšong se ka holimo, mohlala ona o tla sebelisa Foromo ea Array ea mosebetsi oa LOOKUP ho fumana theko ea Whachamacallit lenaneng la lihlahisoa.
Sebopeho sa marang-rang ke lehlaka le lelelele . Ka lebaka leo, ts'ebetso ena e tla khutlisa boleng bo fumanehang karolong ea ho qetela ea lenane la liphuputso.
Ho hlophisa Dintlha
Joalokaha ho bontšitsoe lintlheng tse ka holimo, lintlha tse tsamaisoang li tlameha ho hlophisoa ho nyoloha ka taelo e le hore mosebetsi oa LOOKUP o sebetse hantle.
Ha o hlophisa boitsebiso ho Excel ho hlokahala hore o qale ho khetha lihlopha le mela ea data e lokelang ho hlophisoa. Hangata sena se kenyelletsa lihlooho tsa lihlooho.
- Totobatsa lisele tsa A4 ho C10 tshebetsong ea mosebetsi
- Tobetsa tabeng ea Data ea menu ea lithane
- Tlanya ka khetho ea mofuta o bohareng ba lekareche ho bula Lenaneo la lebokose la Mofuta
- Tlas'a Column e lebang lebokoseng la lebokose khetha ho hlophisa ka Karolo ho tloha hothathamo le theoha
- Haeba ho hlokahala, tlas'a Lethathamo ha u e-ea pele u khetholle Melao ea lits'oants'o ho khethoa hothathamo
- Haeba ho hlokahala, tlas'a Tlhokomelo ea Leano khetha khetho ea A ho ea Z ho tloha hothathamo le theoha
- Tobetsa OK ho hlakola dintlha mme o koale lebokose la lebokose
- Tsamaiso ea data e tlameha joale e bapane le e bonngoeng setšoantšong ka holimo
MOSEBETSI OA HO SEBELISA Mohlala
Le hoja ho ka khoneha ho thaepa mosebetsi oa LOOKUP
= LOOKUP (A2, A5: C10)
ka seleng ea lipampiri, batho ba bangata ba fumana ho le bonolo ho sebelisa lebokose la lebokose la mosebetsi.
Lebokose la lihlooho le etsa hore u kenelle khang e 'ngoe le e' ngoe ka mokhoa o fapaneng ntle le ho tšoenyeha ka syntax ea ts'ebetso - e kang li-parenthesis le li-separator tse nang le likarabo pakeng tsa likhang.
Mehato e ka tlaase e qaqileng kamoo mosebetsi oa LOOKUP o kene ka cell B2 ho sebelisa lebokose la lebokose.
- Tobetsa ka seleng B2 kahare ho etsa hore e be sele e sebetsang ;
- Tobetsa Taba ea Formula ;
- Khetha ho lata le ho buella ho tswa ho lithane ho bula lenane la ho theoha mosebetsing;
- Tobetsa ho RELETSA lethathamong la ho hlahisa lebokose la lihlooho tsa lebokose la lihlooho;
- Tobetsa ho lookup_value, khetho e kgolo lenaneng;
- Tobetsa OK ho hlahisa lebokose la lihlopha tsa lebokose la Function ;
- Ka lebokoseng la lebokose, tobetsa molaong oa Lookup_value ;
- Tobetsa ho seleng ea A2 leqepheng la mosebetsi oa ho kenya lengolo leo la lihlooho ka lebokoseng la lebokose la litaba;
- Tobetsa ho line ea marang-rang lebokoseng la lebokose
- Totobatsa lisele tsa A5 ho C10 kahare ea lipampiri ho kenya lebokose lena ho lebokoseng la lebokose - mobu ona o na le boitsebiso bohle bo lokelang ho batlisisoa ke mosebetsi
- Tobetsa OK ho qeta mosebetsi le ho koala lebokose la lebokose
- Phoso ea # N / A e hlahella seleng ea E2 hobane ha re e-s'o ka re thaepa lebitso la sele ka seleng ea D2
Ho kenya Letlotlo la ho Lokelap
- Tobetsa ho cell A2, mofuta oa Whachamacallit 'me u tobetse ho kena ka key ka lebokose;
- Nako ea $ 23.56 ea bohlokoa e lokela ho hlaha seleng ea B2 kaha ena ke tefo ea Whachamacallit e fumanehang karolong ea ho qetela ea tafoleng ea data;
- Hlahloba mosebetsi ka ho thapa mabitso a mang a karolo ka seleng ea A2. Theko ea karolo e 'ngoe le e' ngoe lethathamong le tla hlaha seleng ea B2;
- Ha o tobetsa ka cell E2 mosebetsi o feletseng = LOOKUP (A2, A5: C10) e hlaha kahare ea foromo ka holim'a leqephe la mosebetsi.