Mosebetsi oa HLOOKUP oa Excel, o khutšoanyane bakeng sa ho sheba ho hokae , o ka u thusa hore u fumane boitsebiso bo tobileng ka litafole tse kholo tsa data tse kang lenane la lipatlisiso tsa likarolo kapa lenane le leholo la ho kopanya le litho.
HLOOKUP e sebetsa haholo mosebetsi o tšoanang oa VLOOKUP oa Excel. Phapang e le 'ngoe feela ke hore VLOOKUP e batlisisa data litoleng ha HLOOKUP e batla data ka mela.
Ho latela litepisi lihloohong tse latelang ho tsamaea ka ho sebelisa mosebetsi oa HLOOKUP ho fumana tlhahisoleseding e tobileng ho database ya Excel.
Bohato ba ho qetela ba thuto bo akaretsa melaetsa ea phoso e atisang ho etsahala ka mosebetsi oa HLOOKUP.
Thuto Lihlooho
- Ho kenya Tutorial Data
- Ho qala mosebetsi oa HLOOKUP
- Boleng ba Lookup
- Table Array
- Nomoro ea Index ea Row
- Range Lookup
- Ho sebelisa HLOOKUP ho Retrieve Data
- Melaetsa e Tloaelehileng ea Phoso bakeng sa Excel HLOOKUP
01 ea 09
Ho kenya Tutorial Data
Ha u kenya data ho Excel ea lipampiri, ho na le melao e mengata e lokelang ho latela:
- Nako le nako ha ho khoneha, u se ke ua tlohela mela kapa li-column tse se nang letho ha u kenya data ea hau.
- Ho siea mela e sa na letho le melafo ka litafole tsa data ho ka etsa hore ho be thata ho sebelisa mesebetsi e mengata ea Excel - ho akarelletsa le HLOOKUP.
- Kenya data ea hau ka mela.
- Ha u beha leqephe la hau la mosebetsi, thathamisa mabitso a hlalosang boitsebiso ka lehlakoreng la pele la tafoleng 'me, ka tlaase ho moo, data ka boeona.
- Haeba ho na le lihlopha tse fetang tse le 'ngoe tsa lintlha, li thathamise tsona ka mor'a lihlopha tse nang le sehlooho sa lihlopha tse ling tsa data ka seleng ea pele ka ho le letšehali.
Bakeng sa thupelo ena
- Kenya data joalokaha ho bonngoe setšoantšong ka holimo ho lisele D4 ho I5.
- Mokha oa pele oa lintlha (mola o 4) o na le mabitso a karolo. Moleng oa bobeli (mola o 5) theko ea karolo ka 'ngoe.
02 ea 09
Ho qala mosebetsi oa HLOOKUP
Pele o qala mosebetsi oa HLOOKUP hangata ke khopolo e ntle ea ho eketsa lihlooho ho ea shebeleng ho bonts'a hore na data e nkoa ke HLOOKUP. Bakeng sa thupelo ena kenya lihlooho tse latelang ka lisele tse bontšitsoeng. Mosebetsi oa HLOOKUP le boitsebiso boo o bo fumanang boitsebisong bo tla ba liseleng ho ea ho le letona la lihlooho tsena.
- D1 - Karolo ea Karolo
E1 - Theko
Le hoja ho ka khoneha ho thaepa mosebetsi oa HLOOKUP ka selefouno, batho ba bangata ba fumana ho le bonolo ho sebelisa lebokose la lebokose la mosebetsi.
Bakeng sa Thuto ena
- Tobetsa ho cell E2 ho etsa hore e be sele e sebetsang . Mona ke moo re tla qala mosebetsi oa HLOOKUP.
- Tobetsa Thabiso ea Tlhahlobo .
- Khetha Lits'oants'o le Litlhahiso tse tsoang leboneng ho bula lenane la ho theoha mosebetsing.
- Tobetsa ho HLOOKUP lenaneng ho hlahisa lebokose la lebokose la mosebetsi.
Litaba tseo re kenang ka tsona melaong e mene e sa lekaneng lebokoseng la lebokose le tla etsa hore ho be le liqeto tsa mosebetsi oa HLOOKUP. Lipuisano tsena li bolella mosebetsi hore na re na le boitsebiso bofe ka mor'a moo le hore na o lokela ho batla ho bo fumana.
03 ea 09
Boleng ba Lookup
Ntoa ea pele ke Lookup_value . E bolella HLOOKUP ka ntho efe kapa efe e ho database eo re batlang tlhahisoleseding. Sebopeho sa Lookup se karolong ea pele ea mobu o khethiloeng.
Boitsebiso boo HLOOKUP e tla khutla bo lula bo tsoa mohloling o le mong oa database e le Lookup_value.
The Lookup_value e ka ba mochine oa mantsoe, boleng bo utloahalang (TRUE kapa FALSE feela), palo kapa seleng ea reference ho bohlokoa.
Bakeng sa thupelo ena
- Tlanya molaong oa Lookup_value lebokoseng la lebokose
- Tobetsa seleng ea D2 ho kenyelletsa sebaka sena sa lisele ho Lookup_value line. Ena ke sele eo re tla thaepa lebitso la karolo eo re batlang tlhahisoleseding ho eona.
04 ea 09
Table Array
Lethathamo_arute ke karabo ea data eo mosebetsi oa HLOOKUP o e batlang ho fumana boitsebiso ba hau. Hlokomela hore lebala lena ha le hloke ho kenyelletsa mela eohle kapa esita le mola oa pele oa database .
Letlapa_array le tlameha ho na le bonyane mela e 'meli ea data, le mola oa pele o nang le Lookup_value (bona mohato o fetileng).
Haeba u kenyelletsa lisele tsa lisele bakeng sa khang ena ke khopolo e ntle ea ho sebelisa lipolelo tse feletseng tsa lisele. Lintlha tse feletseng tsa lisele li hlalosoa ka Excel ka letšoao la dollar ( $ ). Mohlala o ka ba $ E $ 4.
Haeba u sa sebelise litšupiso tse feletseng 'me u kopitsa mosebetsi oa HLOOKUP lisele tse ling, ho na le monyetla oa hore u fumane melaetsa ea phoso ka liseleng tseo mosebetsi o kopitsoang ho oona.
Bakeng sa thupelo ena
- Tobetsa Letlapeng_array line ho lebokoseng la lebokose.
- Totobolla lisele tsa E4 ho I5 ka spreadsheet ho kenya lebala lena tafoleng_array line. Ena ke lethathamo la data eo HLOOKUP e tla e batla.
- Tobetsa konopo ea F4 lebokoseng ho etsa phapang e feletseng ($ E $ 4: $ I $ 5).
05 ea 09
Nomoro ea Index ea Row
Khang ea nomoro ea nomoro ea mohala (Row_index_num) e bontša mola ofe oa Table_array e nang le data eo ue fumaneng.
Ka mohlala:
- haeba u kenya nomoro ea 1 ea moqolo, HLOOKUP e khutlisetsa boleng ho tloha moeling oa pele tafoleng_array;
- haeba nomoro ea nomoro ea molaetsa e le 2, e khutlisa bohlokoa ho tloha moleng oa bobeli tafoleng_array.
Bakeng sa thupelo ena
- Tobetsa ho Row_index_num line ho lebokoseng la lebokose
- Ngola e 2 mohloling ona ho bontša hore re batla HLOOKUP ho khutlisa boitsebiso ho tloha mohloling oa bobeli oa tafole.
06 ea 09
Range Lookup
Puisano ea Range_lookup ke boleng bo utloahalang (TRUE kapa FALSE feela) e bonts'ang hore na u batla HLOOKUP ho fumana se nepahetseng kapa se bapisoang le Lookup_value .
- Haeba TRUE kapa haeba khang ena e sa lumelloe, HLOOKUP e tla sebelisa papiso e lekanyelitsoeng haeba e ke ke ea fumana se bapalang hantle le Lookup_value. Haeba ho bapala hantle ho sa fumanoe, HLOOKUP e khutlisa boleng bo latelang bo boholo bo ka tlaase ho Lookup_value.
- Haeba FALSE, HLOOKUP e tla sebelisa feela ho bapala hantle le Lookup_value. Haeba ho na le litekanyetso tse peli kapa tse fetang tse ka hare ho leqhoa la pele la Table_array tse tšoanang le Lookup_value, boleng ba pele bo fumanoang bo sebelisoa. Haeba papiso e tobileng e sa fumanoe, phoso ea # N / A e khutlisetsoa.
Bakeng sa Thuto ena
- Tobetsa molaong oa Range_lookup lebokoseng la lebokose
- Ngola lentsoe " Bohata" molaong ona ho bontša hore re batla HLOOKUP hore e khutlisetse hantle papaling ea data eo re e batlang.
- Tobetsa OK ho koala lebokose la lebokose.
- Haeba u latetse mehato eohle ea thupelo ena u lokela ho ba le mosebetsi o feletseng oa HLOOKUP ka cell E2.
07 ea 09
Ho sebelisa HLOOKUP ho Retrieve Data
Hang ha mosebetsi oa HLOOKUP o phethiloe o ka sebelisoa ho fumana boitsebiso bo tsoang ho database .
E le ho etsa joalo, thathamisa lebitso la ntho eo u lakatsang ho e fumana ka seleng ea Lookup_value 'me u tobetse ENTER ka lebokose.
HLOOKUP e sebelisa Nomoro ea Index ea Row ho fumana hore na boitsebiso bofe bo lokela ho bontšoa ka seleng ea E2.
Bakeng sa Thuto ena
- Tobetsa ho cell E1 ka spreadsheet ea hau.
- Tšoara Bolt ka seleng ea E1 'me u tobetse ENTER ka lebokose.
- Theko ea bolt - $ 1.54 - e lokela ho bontšoa ka seleng ea E2.
Hlahloba mosebetsi oa HLOOKUP ka ho tlitsa mabitso a mang likarolong tsa E1 le ho bapisa lintlha tse khutliselitsoeng seleng ea E2 le theko e thathamisitsoeng liseleng tsa E5 ho ea ho I5.
08 ea 09
Excel HLOOKUP Melao ea Phoso
Melaetsa e latelang ea phoso e amahanngoa le HLOOKUP.
# N / A phoso:
- Phoso ena e bonts'a haeba boleng ba ho batla bo sa fumanoe karolong ea pele ea tafole.
- E tla boela e bontšoe haeba marang-rang bakeng sa khang ea tafole e sa nepahala. Haeba khang ena e kenyelletsa mela e se nang letho ka holimo ho tafole.
#REF !:
- Phoso ena e bonts'a haeba khang ea nomoro ea nomoro ea mohala e kholo ho feta palo ea mela e hlahang tafoleng. Setšoantšong se ka holimo, #REF! phoso e etsahala hobane nomoro ea nomoro ea mola o behiloe ho 3 ha ho ntse ho e-na le mela e 'meli feela Table_array.
Sena se qetella lithupelo ho theha le ho sebelisa mosebetsi oa HLOOKUP ho Excel 2007.
09 ea 09
Mohlala ka Sebeliso ea HLOOKUP ea Excel 2007
Kenya data e latelang ka liseleng tse bontšitsoeng:
Cell Data
- D3 - Karolo
- E3 - Ho beha
- F3 - Bolt
- G3 - Cog
- H3 - Gear
- I3 - Motsoako
- D4 - Theko
- E4 - $ 17.34
- F4 - $ 1.54
- G4 - $ 20.21
- H4 - $ 23.56
- I4 - $ 1.43
Tobetsa ho cell E1 - sebaka seo liphetho li tla bonts'a ho sona.
Tobetsa Thabiso ea Tlhahlobo.
Khetha Lits'oants'o le Litlhahiso tse tsoang leboneng ho bula lenane la ho theoha mosebetsing.
Tobetsa ho HLOOKUP lenaneng ho hlahisa lebokose la lebokose la mosebetsi.
Ka lebokoseng la lebokose, tobetsa molaetsa oa Leokup _value.
Tobetsa ho cell D1 ka spreadsheet. Mona ke moo re tla thaepa lebitso la karolo eo re batlang ho e reka.
Ka lebokoseng la lebokose, tobetsa tafoleng_array line.
Totobatsa lisele tsa E3 ho I4 ka spreadsheet ho kenya marang-rang ho lebokoseng la lebokose. Ena ke lethathamo la data eo re batlang hore HLOOKUP e batlisise.
Ka lebokoseng la lebokose, tobetsa molaleng oa Row_index_num.
Ngola nomoro ea 2 ho bonts'a hore data eo re e batlang e khutlisitsoe e karolong ea 2 ea tafoleng_arapeling.
Ka lebokoseng la lebokose, tobetsa molaleng oa Range_lookup.
Ngola lentsoe la Bohata ho bontša hore re batla ho bapala hantle le data ea rona.
Tlanya OK.
Ka seleng ea D1 ea spreadsheet, thaba lentsoe bolt.
Nako ea $ 1.54 ea bohlokoa e lokela ho hlaha ka seleng ea E1 e bonts'a theko ea bolt joalokaha ho bontšitsoe tafoleng_e leng.
Haeba u tobetsa seleng E1, mosebetsi o feletseng = HLOOKUP (D1, E3: I4, 2, FALSE) e hlaha kahare ea foromo ka holim'a leqephe la mosebetsi.