Nhanganyaya:
Munyika yekutonga kwedata, kugona kukopa otomatiki ruzivo kubva kune imwe sero kuenda kune imwe Excel sheet kunogona kubatsira zvakanyanya. Kunyangwe kuchengetedza data yakagadziridzwa kana kuve nehurongwa huri nani mufaira redu, kudzidza kuita basa iri otomatiki kunogona kutichengetera nguva nesimba.
Maitiro ekukopa otomatiki sero kune rimwe pepa reExcel
Kopira otomatiki sero kune rimwe pepa reExcel
Kana uchida kukopa otomatiki zviri mukati me cell papepa kubva kuExcel kuenda kune rimwe pepa, wauya kunzvimbo chaiyo! Muchinyorwa chino, iwe uchadzidza nzira yekumisikidza fomula kana macro kuitira kuti basa iri riitwe otomatiki pasina kuriita nemaoko pese paunogadzirisa data. Izvi zvinokusevha nguva nesimba mubasa rako rezuva nezuva neExcel.
Imwe yedzakareruka nzira dzekukopa otomatiki sero kune rimwe shizha Iri kushandisa fomula. Kuti uite izvi, ingosarudza source cell (pane zvirimo zvauri kuda kukopa), wobva waenda kupepa rekwauri kuenda uye wosarudza sero paunoda kuti zvirimo zvikope. Tevere, isa fomura rinotevera munzvimbo yauchaenda. sero: =NombreDeHojaDeOrigen!ReferenciaDeCelda. Ita shuwa kuti watsiva NombreDeHojaDeOrigen nezita chairo remabviro bepa uye ReferenciaDeCelda nereferensi yesero yaunoda kukopa. Semuenzaniso, kana iwe uchida kukopa sero B2 pane pepa "Sheet1" kune cell A1 papepa "Sheet2," iwe unoisa fomula. =Hoja1!B2 muchitokisi A1 yepepa "Sheet2".
Imwe nzira yekuita otomatiki kopi kubva pane imwe sero kuenda kune rimwe pepa ndeye kushandisa macro. A macro inoteedzana yemirairo inoudza Excel maitiro ekuita zvimwe zviito otomatiki. Kuti ugadzire macro otomatiki anokopa sero kune rimwe pepa, iwe unozofanirwa kuenda kune "Developer" tebhu paExcel ribhoni uye tinya "Rekodha Macro." Wobva wakopa nemawoko sero yobva yanamira musero rekuenda kune rimwe pepa. Kana wapedza, dzokera ku "Developer" tab uye dzvanya "Misa Kurekodha." zvaitwa otomatiki.
Kunzwisisa basa reVLOOKUP muExcel kukopa maseru kune rimwe pepa
Kana iwe uri munhu anoshanda nemaspredishiti muExcel, iwe zvechokwadi wakanzwa kudiwa kwekukopa otomatiki sero kune rimwe pepa. Neraki, Excel inopa basa rinobatsira rakadaidzwa kuti VLOOKUP iro rinokutendera kuti uite basa iri nemazvo uye zvisina simba.
Chii chinonzi VLOOKUP basa uye ndingaishandisa sei kukopa maseru?
Basa reVLOOKUP, rinomirira kuti "Vertical Lookup" muChirungu, ibasa rine simba rinokubvumira kuti utarise kukosha kwakasiyana mukoramu yespredishiti uye kukopa kukosha kwakabatana kubva kune imwe koramu mumutsara mumwe chete. Iri basa rinonyanya kushandiswa kutsvaga makuru edata seti uye kutora ruzivo rwakakodzera nekukurumidza uye nemazvo. Kuti ushandise VLOOKUP basa, unofanirwa kuziva zvinotevera zvinhu:
- Kukosha kwaunoda kutsvaga
- The search range, inova iyo column ichatsvagwa kukosha
- Nhamba yecolumn ye yekutsvaga mhando ine kukosha kwaunoda kukopa
- Nharo yesarudzo inonzi range_true/false, iyo inotara kuti unoda kutsvaga chaiyo inofananidzwa here kana kuti "approximate" mechi.
Matanho ekukopa maseru uchishandisa VLOOKUP basa mu Excel:
- Sarudza sero paunoda kuti kukosha kwakakopwa kuoneke pane rimwe pepa.
- Nyora basa reVLOOKUP mu bhari yeformula, ichiteverwa nehukoshi hwaunoda kutsvaga uye huwandu hwekutsvaga. Semuenzaniso, kana iwe uchida kuwana kukosha "123" muchikamu A cheshiti »Sheet1″, fomula yacho ingave:
=VLOOKUP(123,Hoja1!A:A, - Inotsanangura nhamba yekoramu yetsvakiridzo ine kukosha kwaunoda kukopa. Semuenzaniso, kana kukosha kwaunoda kukopa kuri muchikamu chechitatu chetsvakiridzo, fomula yacho ingave:
3, - Kana uchida kutsvaga inofanana chaiyo, nyora “FALSE” nenhamba column. Kana iwe uchida kutsvaga "fuzzy" mechi kana mutambo wakapusa, unogona kusiya nharo iyi. Semuenzaniso, kana iwe uchida chaiyo mechi, fomula yacho inotaridzika seizvi:
FALSE) - Dzvanya Enter kuti uwane kukosha kunokopwa kune yakasarudzwa sero.
Maitiro ekushandisa INDEX uye MATCH formula kukopa maseru kune rimwe shizha otomatiki
Kana iwe uri mushandisi weExcel uye uchida kuendesa data kubva kune rimwe shizha kuenda kune rimwe nenzira yeotomatiki, iyo INDEX uye MATCH fomula inogona kuve yakanakisa shamwari yako. Nekusanganiswa uku kwemabasa, iwe unozokwanisa kukopa maseru kubva kune rimwe pepa kuenda kune rimwe nekukurumidza uye nemazvo. Iyo INDEX formula inokutendera iwe kusarudza kukosha mu a huwandu hwemasero, uye MATCH basa rinotsvaga mutambo mumutsara iwoyo. Pamwe chete, zvinhu izvi zvinogadzira chishandiso chine simba chekushanda nacho data muExcel.
Chikamu chekutanga cheINDEX uye MATCH formula ine kutsanangura huwandu hwemasero kwatinoda kukopa data. Tichishandisa iyo INDEX basa, tinogona kutsanangura rondedzero ine hutsika hwatinoda kutamisa. Iyi matrix inogona kuve koramu kana mutsara, zvichienderana nekuti data rakarongwa sei mupepa rekutanga. Izvo zvakakosha kuve nechokwadi chekuti iyo yakasarudzwa yakasarudzika inofanana nedata yatinoda kutevedzera.
Kana tangotsanangura huwandu hwesero, Chikamu chinotevera che MATCH formula chinotitendera kutsvaga mutambo weiyo yakakosha kukosha. Somuenzaniso, ngatiti tinoda kukopa zita racho yechigadzirwa kubva pane rimwe shizha kuenda pane rimwe. Tichishandisa MATCH basa, tinokwanisa kutsvaga zita iroro mukoromo yepepa rekutanga towana nzvimbo yaro. Izvi zvinotitendera kusarudza iyo chinzvimbo muINDEX formula uye nekudaro tokopa zita rechigadzirwa kune rimwe pepa.
Kukosha kwekushandisa mareferenzi akakwana pakukopa maseru kune rimwe pepa muExcel
Paunenge uchishanda nemaspredishiti muExcel, zvakajairika kuti isu tide kukopa maseru kubva kune rimwe pepa kuenda kune rimwe. Zvisinei, zvakakosha kuziva kuti pakuita izvi, mafomula nemareferensi esero anofanirwawo kukopwa nemazvo. Zvikasadaro, mhedzisiro inogona kunge isina kururama uye kukonzera kukanganisa mukuverenga. Imwe nzira yekudzivisa matambudziko aya ndeyekushandisa mareferensi akakwana paunenge uchikopa masero kune rimwe pepa muExcel.
Iyo mareferensi chaiwo Iyo inzira yekutsanangura nzvimbo chaiyo yesero muspredishiti pasina kurangarira nzvimbo yaro. Izvi zvinoreva kuti, kana ukakopa sero kune rimwe shizha, sereferensi yakakwana inoramba iripo uye haizongozvichinjika zvichienderana nenzvimbo itsva. Izvi zvinonyanya kukosha kana uchishanda nemafomu akaoma kunzwisisa anoenderana nemamwe maseru uye mitsara papepa.
Kuti tishandise mareferensi akakwana pakukopa maseru kune rimwe pepa muExcel, isu tinongofanira kudaro wedzera peso sign ($) pamberi pemutsara uye makoramu mareferensi mufomula. Izvi zvinoudza Excel kuti mareferensi anofanirwa kuramba akamira kana iwe uchikopa sero kune imwe nzvimbo. Semuenzaniso, kana isu tiine fomula inonongedza sero A1 mune shizha razvino, kuikopa kune rimwe shizha kunozongogadziridza fomula kunzvimbo nyowani yesero. Zvisinei, kana tikashandisa chirevo chakakwana ($ A $ 1), fomula yacho inochengetedza chirevo chesero A1, zvisinei nenzvimbo yesero rakakopwa.
Maitiro ekushandisa Excel Macros kukopa otomatiki Sero kune rimwe Sheet
Maitiro ekukopa otomatiki sero kune rimwe pepa reExcel
Excel macro kukopa maseru:
Kushandisa macros muExcel kunogona kubatsira zvakanyanya patinenge tichida kudzokorora mabasa mumaspredishiti edu. Muchiitiko ichi, isu tichadzidza kushandisa macro kukopa otomatiki sero kune rimwe pepa mukati meExcel bhuku rebasa redu.
Danho 1: Gonesa iyo developer tab:
Tisati tatanga, isu tinofanirwa kuve neshuwa kuti tine iyo yekuvandudza tebhu inogoneswa muExcel. Kuti tiite izvi, tinofanira kutevera matanho anotevera:
- Dzvanya pane "Faira" tab.
- Sarudza "Sarudzo".
- Enda ku "Gadzirisa ribhoni".
- Tarisa bhokisi re "Developer" uye tinya "OK".
Kana tangogonesa tebhu yekuvandudza, isu tichave takagadzirira kutanga kushandisa macros muExcel.
Danho 2: Gadzira itsva macro:
Mune yekuvandudza tebhu, isu tinodzvanya pa "Rekodha macro" kugadzira itsva macro. Ipapo, hwindo rinovhura kwatinofanira kuisa zita reiyo macro uye sarudza iro iro Excel bhuku rebasa ratinoda kuti richengetedzwe. Isu tinogona zvakare kugovera nzira yekudimbudzira yekhibhodi kuti ita macro nekukurumidza.
Mushure mekudzvanya "OK", Excel ichatanga kurekodha zviito zvedu. Panguva ino, isu tinofanira kusarudza sero yatinoda kukopa tobva taenda kune pepa rekuenda. Kana tave ipapo, isu tichasarudza sero kwatinoda kuti zvirimo zvikopwe, tinodzvanya pa "Misa kurekodha" mune yekuvandudza tebhu kuti tipedze kugadzira yedu macro.
Zvino, pese patinomhanya iyi macro, sero rakasarudzwa rinozokopwa otomatiki kune pepa rekuenda. Ichi chingori chimwe chemabasa mazhinji atinokwanisa kuwana nema macros muExcel, izvo zvinotitendera kuchengetedza nguva nekuvandudza kugadzirwa kwedu.
Kuongorora sarudzo dzekusefa muExcel kukopa otomatiki maseru kune rimwe pepa
MuExcel, kopi masero kubva pane rimwe pepa kuenda kune rimwe rinogona kuve basa rinofinha kana uchifanira kuriita nemaoko. Nekudaro, kune sarudzo dzekusefa dzinokutendera kuti uite otomatiki iyi uye kuchengetedza nguva mumabasa ako ezuva nezuva Muchiposvo ichi, isu tichaongorora akasiyana mafirita sarudzo muExcel anozokubatsira kukopa otomatiki sero kune rimwe bepa nekukurumidza uye nemazvo.
Sarudzo 1: Advanced Sefa
Yepamberi kusefa muExcel chishandiso chine simba chinokutendera kuti utsanangure maitiro ekukopa chete maseru anozadzisa iwo maitiro kune pepa idzva. Kuti ushandise sarudzo iyi, sarudza huwandu hwedata hwaunoda kusefa, enda pakanzi “Data” wodzvanya “Sefa”. Pakupedzisira, sarudza iyo "Kopira kune imwe nzvimbo" sarudzo uye sarudza nzvimbo yaunoda kunamira iyo yakakopwa data.
Sarudzo 2: Maformula ane mabasa ekutsvaga
Imwe nzira yekuita kukopa otomatiki sero kune rimwe pepa ndeyekushandisa mafomula ane mabasa ekutsvaga, akadai seVLOOKUP kana INDEX-MATCH Aya mabasa anobvumidza iwe kutsvaga kukosha muhuwandu hwe data uye kudzosera chaiyo mhedzisiro. Kuti ushandise mabasa aya, iwe unofanirwa kutsanangura huwandu hwekutsvaga, kukosha kwaunoda kutsvaga, uye koramu umo kukosha kwaunoda kukopa kunowanikwa. Zvadaro, ingo dhonzera fomula pasi kuti uikope kumaseru aunoda pabepa idzva.
Sarudzo 3: Excel Macro
Kana uchida kukopa otomatiki sero kune rimwe shizha nguva nenguva, unogona kushandisa Excel macro kuita otomatiki maitirwo acho. A macro seti yemirairo inongoita yega uye inogona kuita kudzokorora mabasa. Kuti ugadzire macro, enda kune "Scheduler" tebhu muExcel, tinya "Rekodha Macro" uye ita zviito zvaunoda kuita otomatiki. Zvadaro, iwe unogona kugovera iyo macro kune bhatani kana keyboard mapfupi kuti ikurumidze kuimhanyisa pese paunoda.
Uchishandisa iyo consolidate basa muExcel kukopa maseru kune rimwe pepa otomatiki
MuExcel, pane basa rinobatsira kwazvo rinonzi Consolidate iro rinokutendera kuti utore otomatiki maseru kune rimwe pepa. Iri basa rinonyanya kukosha kana tine ruzivo rwakapararira pamapepa akasiyana uye tinoda kuunza pamwe chete papepa rimwe chete kuti tiongorore kana kuratidzwa. Basa rekubatanidza muExcel rinotibvumira kuchengetedza nguva uye kurerutsa kudzokorora kopi uye kunama mabasa.
Kuti tishandise consolidate basa, tinofanira kutevera mamwe matanho ari nyore. Kutanga, tinosarudza pepa rekuenda kwatinoda kuti masero akodzwe Zvadaro, tinoenda kuData tab uye tinotarisa kuConsolidate sarudzo. Paunodzvanya pairi, bhokisi renhaurirano rinovhura kwatinogona kutsanangura mashiti uye mitsara yatinoda kubatanidza. Isu tinokwanisa kusarudza akawanda mashiti uye mitsara yekukopa ruzivo rwese rwakakodzera mune rimwe bepa rimwe chete.
Kana tangosarudza mapepa uye mitsara yekubatanidza, tinogona kugadzirisa sarudzo maererano nezvatinoda. Tinogona kusarudza kana tichida kuti maseru akopwe akachinjika kana kuti akachinjika, pamwe nekuti tichida kugadzira chinongedzo chedata rekutanga kana kungochikopa. Sarudzo idzi dzinotipa kuchinjika kuti tigadzirise kubatanidzwa kune zvatinoda chaizvo. Kana tangogadzirisa sarudzo, tinodzvanya bhatani rekuti OK uye Excel inozokopa otomatiki maseru akasarudzwa kune pepa rekuenda.
Zvinokurudzira kudzivirira kukanganisa kana uchikopa maseru kune rimwe pepa muExcel otomatiki
Paunenge uchikopa maseru kubva kune imwe Excel sheet kuenda kune imwe nenzira otomatiki, zvakajairika kuita zvikanganiso zvinogona kushandura data uye kukonzera matambudziko mukuongorora kunotevera. Kuti udzivise zvikanganiso izvi, zvakakosha kutevedzera mamwe mazano anozovimbisa kopi chaiyo yemasero. Imwe yeanonyanya kukosha kurudziro ndeye kuona kuti huwandu hwemasero akasarudzwa hwakarurama usati waita otomatiki kopi.. Kana huwandu husina kunaka, maseru asingadiwe anokopwa kana mamwe anofanirwa kuverengerwa achasiyiwa.
Imwe kurudziro yakakosha ndeyekuona kuti pepa rekuenda rine fomati yakafanana neyepepa rekwakabva. Izvi zvinosanganisira nhamba yakafanana yemakoramu nemitsara, zvakafanana zvigadziriso zvemafomati, uye chero zvimwe zvine chekuita nekurongeka kwemaseru. Nekuchengetedza chimiro chakafanana, iwe unodzivirira matambudziko ekusawirirana kana kukanganisa data nekukopa maseru otomatiki. Kuti uite izvi, unogona kushandisa zvishandiso senge fomati chishandiso masero muExcel.
Pamusoro pezvo, zvakakosha kufunga nezvekuvapo kwemafomula kana mareferensi kune mamwe maseru ari mumaseru anozokopwa. Izvo zvinodikanwa kuve nechokwadi chekuti mareferensi uye mafomula ari kushanda mubepa rekuenda. Kana kunobva masero ereferensi maseru asipo pashizha rekuenda, pane chikanganiso chinogadzirwa uye data rakakopwa rinogona kunge risiri iro kana kusada. Kuti udzivise izvi, unogona kushandisa "Tsvaga uye Tsiva" basa kugadzirisa mareferensi usati waita kopi yega.
Ini ndiri Sebastián Vidal, injiniya wekombuta anofarira nezve tekinoroji uye DIY. Uyezve, ndini musiki we tecnobits.com, kwandinogovera zvidzidzo kuti tekinoroji iwanikwe uye inonzwisisika kumunhu wese.