01 ea 06
Ho qala Mosebetsi oa MATCH oa Nested
Ho kenya mosebetsi oa MATCH e le Khohlano ea Khoeli ea Index
Ka tloaelo VLOOKUP e khutlisa feela data ho tloha ho e 'ngoe ea tafole ea data mme khoeli ena e behiloe ke khang ea nomoro ea index .
Leha ho le joalo, mohlala ona re na le lihlopha tse tharo tseo re lakatsang ho li fumana kahoo re hloka mokhoa oa ho fetola habonolo nomoro ea lenane la khoeli ntle le ho hlophisa foromo ea rona ea ho batla.
Ke hona moo mosebetsi oa MATCH o kenang teng. E tla re lumella hore re tšoane le nomoro ea molumo ho lebitso la tšimo - ebang ke la January, la February kapa la March - leo re le kenyang seleng ea E2 ea leqephe la mosebetsi.
Mesebetsi ea ho betla
Ka hona, tshebetso ea MATCH e sebetsa joaloka khokahano ea nomoro ea nomoro ea VLOOKUP.
Sena se finyelloa ka ho senya mosebetsi oa MATCH ka hare ho VLOOKUP ho Col_index_num line ea lebokose la lebokose la lihlooho.
Ho kenya mosebetsi oa MATCH ka letsoho
Ha sehlaha se sebetsa, Excel ha e re lumelle hore re bule lebokose la lebokose la mosebetsi oa bobeli ho kenya lintlha tsa lona.
Ka hona, mosebetsi oa MATCH o tlameha ho kenngoa ka letsoho Col_index_num line.
Ha o kena mesebetsing ka letsoho, lintlha tsohle tsa mosebetsi li lokela ho aroloa ke " comma ", " .
Lithuto tsa Thuto
Ho kena tumellano ea MATCH's Lookup_value
Mohato oa pele oa ho kena mosebetsing oa MATCH o nang le sehlaha ke ho kena khang ea Lookup_value .
The Lookup_value e tla be e le sebaka kapa lisele tsa selefouno tseo re batlang ho li bapisa lethathamong.
- Ketsahaleng ea lebokose la mosebetsi oa VLOOKUP, tobetsa molaleng oa Col_index_num .
- Tlanya sebopeho sa lebitso la mosebetsi le lateloa ke betere e bulehileng " ( "
- Tobetsa ho cell E2 ho kenya lengolo leo la boitsebiso ka lebokoseng la lebokose.
- Tlatsa comma "," ka mor'a hore e-ea lekala la E3 e kenyeletsang ho kena ha khang ea MATCH's Lookup_value function .
- Tlosa lebokose la lebokose la mosebetsi oa VLOOKUP o bulehileng mohato o latelang ho thuto.
Mohato oa ho qetela oa thupelo ea Lookup_values e tla kenngoa ka lisele tsa D2 le E2 ea leqephe la mosebetsi .
02 ea 06
E eketsa Lookup_array bakeng sa Mosebetsi oa MATCH
E eketsa Lookup_array bakeng sa Mosebetsi oa MATCH
Mohato ona o akaretsa phapang ea Lookup_array bakeng sa sehlaha sa MATCH sa sehlaha.
The Lookup_array ke lisele tsa lisele tseo mosebetsi oa MATCH o tla li batla ho fumana khang ea Lookup_value e kenyelitsoe mohato o fetileng oa thupelo.
Mohlala ona, re batla mosebetsi oa MATCH ho batla lisele tsa D5 ho G5 bakeng sa papiso ho ea khoeli e tla kenngoa seleng ea E2.
Lithuto tsa Thuto
Mehato ena e lokela ho kenngoa ka mor'a hore sefahleho se kenngoe mohato o fetileng ho Col_index_num line ka lebokoseng la boitsebiso la mosebetsi oa VLOOKUP.
- Haeba ho hlokahala, tobetsa molaleng oa Col_index_num ka mor'a hore sefahleho se behoe ho kenya ntlha e kenang qetellong ea keno ea hona joale.
- Totobatsa lisele tsa D5 ho G5 lefatsheng la ho kenya litlaleho tsa lisele tse kang mosebetsi o motle oa ho batlisisa.
- Tobetsa konopo ea F4 lebokoseng ho fetola lebotho lena ho li- cell reference tse feletseng . Ho etsa joalo ho tla etsa hore ho khonehe ho kopitsa foromo e felletseng ea ho batla ho ea libakeng tse ling mosebetsing oa mohato oa ho qetela oa thuto
- Tlatsa comma "," ka mor'a hore e-ea e- eale ea E3 e sebelise sefahleho sa ho qetela ho kena ha ngangano ea MATCH's Lookup_array .
03 ea 06
Ho eketsa mofuta oa Match le ho tlatsa mosebetsi oa MATCH
Ho eketsa mofuta oa Match le ho tlatsa mosebetsi oa MATCH
Khang ea boraro le ea ho qetela ea mosebetsi oa MATCH ke taba ea Match_type.
Khang ena e bolella Excel hore na e ka bapisa le Lookup_value joang le litekanyetso tsa Lookup_array. Liqeto ke: -1, 0, kapa 1.
Khang ena ke khethollo. Haeba e tlohetse mosebetsi ona o sebelisa boleng bo sa feleng ba 1.
- haeba Match_type = 1 kapa e sa tlohe: MATCH e fumana boleng bo boholo ka ho fetisisa kapa bo lekanang le Lookup_value. Haeba boleng bona bo khethoa, dintlha tsa Lookup_array li lokela ho hlophisoa ho nyoloha ka tatellano.
- haeba Match_type = 0: MATCH e fumana boleng ba pele bo lekanang le Lookup_value. Lintlha tsa Lookup_array li ka hlophisoa ka tatellano efe kapa efe.
- haeba Match_type = 1: MATCH e fumana boleng bo nyenyane haholo ho feta kapa bo lekanang le Lookup_value. Haeba boleng bona bo khethoa, dintlha tsa Lookup_array li lokela ho hlophisoa ka ho theoha.
Lithuto tsa Thuto
Mehato ena e lokela ho kenngoa ka mor'a hore sekhetho se kene mohato o fetileng ho Row_num line ka lebokoseng la boitsebiso la mosebetsi oa VLOOKUP.
- Ho latela sehlahisoa sa bobeli ho Col_index_num line, thatha "zero" ho tloha ha re batla mosebetsi oa sehlaha ho khutlisa se bapalang hantle ho khoeli e kenang seleng ea E2.
- Tlanya lebanta le koalang " ) " ho tlatsa mosebetsi oa MATCH.
- Tlosa lebokose la lebokose la mosebetsi oa VLOOKUP o bulehileng mohato o latelang ho thuto.
04 ea 06
Ho kena Phatlalatsong ea Litokelo tsa VLOOKUP
Tlhaloso ea Range Lookup
Puo ea Range_lookup ea VLOOKUP ke ea bohlokoa (TRUE kapa FALSE feela) e bontšang hore na u batla VLOOKUP ho fumana se nepahetseng kapa se bapisoang le Lookup_value.
- Haeba TRUE kapa haeba khang ena e sa lumelloe, VLOOKUP e khutlisetsa kapa e bapala hantle le Lookup_value, kapa, haeba ho sa fumanehe hantle, VLOOKUP e khutlisa boleng bo latelang bo boholo. Bakeng sa foromo ea ho etsa sena, boitsebiso bo ka lebokoseng la pele la Table_array bo tlameha ho hlophisoa ho nyoloha ka tatellano .
- Haeba FALSE, VLOOKUP e tla sebelisa feela ho bapala hantle le Lookup_value. Haeba ho na le litekanyetso tse peli kapa tse fetang tse ka holimo ho sehlopha sa Table_array se bapisoang le boleng ba ho batla, boleng ba pele bo fumanoang bo sebelisoa. Haeba papiso e tobileng e sa fumanoe, phoso ea # N / A e khutlisetsoa.
Thutong ena, kaha re batla litekanyetso tsa khoebo khoeling e itseng, re tla beha Range_lookup e lekanang le Bohata .
Lithuto tsa Thuto
- Tobetsa molaong oa Range_lookup lebokoseng la lebokose
- Ngola lentsoe " Bohata" molaong ona ho bontša hore re batla VLOOKUP ho khutlisetsa hantle papaling ea data eo re e batlang
- Tlanya Kantle ho tlatsa foromo ea bobeli ea mochine o kenyelletsoeng le lebokose la bobeli
- Kaha ha re e-s'o kene lits'ebetsong tsa ho batla ho kena lisele tsa D2 le E2, phoso ea # N / A e tla ba teng ka seleng ea F2
- Phoso ena e tla lokisoa mohato o latelang tulong ea thuto ha re tla eketsa litekanyetso tsa ho batla ho latela mohato o latelang oa thupelo.
05 ea 06
Ho lekola Tsela e 'Meli ea ho Fumana Tsela
Ho lekola Tsela e 'Meli ea ho Fumana Tsela
E le hore u sebelise mokhoa ona oa bobeli oa ho fumana mokhoa oa ho fumana li-cookie tse fapaneng tse thathamisitsoeng tafoleng, thathamisa lebitso la cookie ka seleng ea D2, khoeli ka seleng ea E2 'me u tobetse ENTER ka lebokose.
Lintlha tsa thekiso li tla bontšoa ka seleng ea F2.
Lithuto tsa Thuto
- Tobetsa ka cell cell D2 ka mosebetsing oa hau
- Tlanya Oatmeal ka seleng ea D2 mme o tobetsa ENTER ka lebokose
- Tobetsa ho cell E2
- Tlanya February ho kenya seleng E2 'me u tobetse ENTER ka lebokose
- Chelete ea $ 1,345 ea bohlokoa - tekanyo ea theko ea li-cookie tsa Oatmeal ka khoeli ea February - e lokela ho bontšoa ka seleng ea F2
- Hona joale, leqephe la hau la mosebetsi le lokela ho bapisa mohlala o leqepheng la 1 la thuto ena
- Hlahloba foromo ea ho batla ho feta ka ho thapa mefuta eohle ea li-cookie mefuta le likhoeli tse hlahang tafoleng_array 'me lipalo tsa khoebo li lokela ho bontšoa ka seleng ea F2
- Bohato ba ho qetela thupelong bo akaretsa ho kopitsa foromo ea ho batla ka ho sebelisa Fill Handle .
Haeba molaetsa oa phoso o kang #REF! e hlaha ka seleng ea F2, lethathamo lena la melaetsa ea phoso ea VLOOKUP e ka u thusa ho fumana hore na bothata ke bofe.
06 ea 06
Ho kopitsa Sebopeho sa Bobeli ba Liteboho le Fill Handle
Ho kopitsa Sebopeho sa Bobeli ba Liteboho le Fill Handle
Ho nolofatsa ho bapisa le data bakeng sa likhoeli tse fapaneng kapa li-cookie tse fapaneng, foromo ea ho batla e ka kopitsoa lisele tse ling e le hore chelete e ngata e ka bontšoa ka nako e le 'ngoe.
Kaha data e behiloe ka mokhoa o tloaelehileng leqepheng la lipampiri, re ka kopitsa foromo ea ho batla ho kenya seleng F2 ho seleng ea F3.
Ha foromo e kopitsoa, Excel e tla ntlafatsa litšobotsi tse amanang le lisele ho bonts'a sebaka se secha sa foromo. Tabeng ena D2 e fetoha D3 le E2 e fetoha E3,
Hape, Excel e boloka sebaka se feletseng sa lisele se lekana ka tsela e fapaneng ka ho fetisisa hore $ D $ 5: $ G $ 5 e lule e tšoana le ha foromo e kopitsoa.
Ho na le mekhoa e fetang e le 'ngoe ea ho kopitsa data ho Excel, empa mohlomong tsela e bonolo ka ho fetisisa ke ho sebelisa Tlhaloso ea Tlhaloso.
Lithuto tsa Thuto
- Tobetsa ho cell D3 kahare ea hau ea mosebetsi
- Tšoaea Oatmeal ka seleng ea D3 'me u tobetse ENTER ka lebokose
- Tobetsa ho cell E3
- Tlanya March ho kenya seleng E3 'me u tobetse ENTER ka lebokose
- Tobetsa seleng F2 ho e etsa sele e sebetsang
- Beha pointer ea mouse holim'a seterata se setšo ka ho le letona le letona. Pointer e tla fetela ho pontšo e eketsehileng "+" - ena ke Fill Handle
- Tobetsa konopo ea letsohong le letšehali 'me u hulele letsoho la ho tlatsa fatše seleng ea F3
- Ho lokolla konopo ea mouse mme sele f3 e lokela ho ba le foromo e 'meli ea ho nkoa e le' ngoe
- Chelete ea $ 1,287 ea bohlokoa - theko ea theko ea li-cookie tsa Oatmeal ka khoeli ea March- e lokela ho bontšoa ka seleng ea F3