Matafura anoenderana muExcel: Yakadzama mirayiridzo

Paunenge uchiita mamwe mabasa muExcel Dzimwe nguva unofanirwa kubata nematafura akati wandei anoenderana neumwe neumwe. Ndokunge, iyo data kubva kune imwe tafura inosimbiswa kune vamwe uye tsika mumatafura ese ane hukama hwakadzokororwa kana dzashandurwa.

Matafura ane hukama zvakanyanya kuti ashandise kubata huwandu hukuru hweruzivo. Isa ruzivo rwese mune imwe tafura, kunze kweimwe, kana isiri homogeneous, kwete nyore nyore. Zvakaoma kushanda nezvinhu zvakadaro uye kuzvitsvaga. Dambudziko rakataurwa rakangogadzirirwa kubvisa matafura akakosha, ruzivo urwo rwakagoverwa, asi panguva imwe chete yakarongwa. Matafura anoenderana anogona kunge asiri chete mukati me pepa rimwe kana rimwe bhuku, asi zvakare kuwanikwa mumabhuku akapatsanurwa (mafaera). Sarudzo mbiri dzekupedzisira dziri muitiro dzinowanzo shandiswa, sezvo chinangwa cheiyi tekinoroji ingangobva kure nekuunganidzwa kwedata, uye nekudzungudza kwavo pane rimwe peji hakugadzirisike. Ngatidzidzei kuti tishandise sei uye tinoshanda sei nemhando yakadaro yedata manejimendi.

Kugadzira matafura anoenderana

Chekutanga pane zvese, ngatitarisei pamubvunzo, munzira iyo zvinokwanisika kugadzira kubatana pakati pematafura akasiyana siyana.

Nzira 1: Direct kusunga tafura formula

Nzira iri nyore yekubatanidza data ndeyekushandisa mafomula iyo kune zvinongedzo kune mamwe matafura. Inonzi yakananga kusungwa. Iyi nzira ndeye intuitive, sezvo kana ikasunga kunge ichingoita sekugadzira mareferensi kune data mune imwe tafura.

Ngatione kuti muenzaniso waunogona kuumba kutaurirana nekutasunga zvakananga. Tine matafura maviri pamashiti maviri. Patafura imwe chete iyoyo, muhoro unoverengerwa uchishandisa formula nekuwanza mitengo yemushandi kuti ive imwechete coefficient.

Pepa repiri pane iyo tafura iyo iyo iripo rondedzero yevashandi nemubhadharo wavo. Rondedzero yevashandi mune zvese zviitiko zvinounzwa mune imwe odhi.

Izvo zvinodikanwa kuti uite kuti data idhodhi iri pamabheti kubva kune yechipiri pepa inosimbisa masero anoenderana eyekutanga.

1. Pepa rekutanga, isu tinogovera iyo yekutanga cell ye "Bet" ikholamu. Isu tinoisa mariri chiratidzo "=". Tevere, tinya pane "pepa 2" Label, iyo inoiswa kuruboshwe chikamu cheExpress Interface pamusoro peiyo Status Bar.



2. Iko kune kufamba munzvimbo yechipiri yegwaro. Dzvanya pane yekutanga sero mu "Bheti" ikhoramu. Wobva wadzvanya pane bhatani rePeturu pane iyo keyboard kuti upinde iyo data muchitokisi umo iyo "yakaenzana" chiratidzo chakamboiswa.



3. Uyezve pane otomatiki shanduko kune yekutanga pepa. Sezvatinogona kuona, kukosha kwemushandi wekutanga kubva patafura yechipiri kunodhonza musero rinoenderana. Nekuisa iyo curtor pane imwe cell ine kubheja, tinoona kuti iyo yakajairwa fomula inoshandiswa kuratidza data pachiratidziri. Asi pamberi pevaironga zvechitokisi, kubva kupi iyo data iri kubuda, pane chirevo "runyorwa2!", Izvo zvinoratidza zita renzvimbo yegwaro ivaiwanikwa. Iyo general formula mune yedu kesi inotaridzika seizvi:

   = Rondedzero2! B2



4. Iye zvino iwe unofanirwa kuendesa data pamusoro pemitero yevamwe vese vashandi vebhizinesi. Ehezve, izvi zvinogona kuitwa nenzira imwe chete kuti isu tikamuzadzisa iro basa rekutanga mushandi, asi tichifunga kuti zvinyorwa zveMushandi zviri kuwanikwa mune imwecheteyo, basa racho rinogona kurerutswa uye rakakurudzirwa nesarudzo. Izvi zvinogona kuitwa kuburikidza nekungotevedzera fomula kune huwandu pazasi. Nekuda kwekuti izvo zvinongedzo zvekubuditsa ihama, kana uchitevedzera zvavanokoshesa, izvo zvinokoshesa zvinwiwa zvatinoda. Iyo Copy maitiro pachayo inogona kuitwa uchishandisa mucherechedzo unozadza.

   Saka, isu tinoisa iyo inotuka kunzvimbo yepazasi yeiyo chinhu chine formula. Mushure meizvozvo, iyo yekukwira inofanira kushandura kucherechedzo kuzadza muchimiro chemuchinjiko mutema. Isu tinoita iyo clamp yebhatani rekuruboshwe uye kudhonza iyo inotuka kuhuwandu hwekamuri.



5. Yese data kubva kune imwe ikoramu pane pepa 2 yakadhonzwa mune tafura pane pepa 1. Kana data shanduko pane pepa 2, ivo vanongoerekana zvachinja pane yekutanga.

Nzira yechipiri: Uchishandisa iyo Blunding of Opersator Index - Tsvaga

Asi zvekuita kana rondedzero yevashandi muTafura Arrays haisi mune imwecheteyo? Mune ino kesi, sezvazviri kare, imwe yesarudzo ndeyekuisa hukama pakati peimwe yeaya masero ayo anofanirwa kubatana pamurume. Asi zvakakodzera kunze kwematafura madiki. Zvekuita mitezo yakakura, iyi sarudzo ichazotora nguva yakawanda pane kuitwa, uye zvakanyanya - mukuita kazhinji zvichave zvisingaite. Asi dambudziko iri rinogona kugadziriswa uchishandisa boka revashandi index - Tsvaga. Ngatione kuti zvinogona kuitwa sei nekuzunza data mumatafura pamusoro pekuti hurukuro yaive munzira yekare.

1. Isu tinosimbisa chinhu chekutanga che "Bet" ikhoramu. Enda kumabasa emununde wizard nekudzvanya pane "isa basa" icon.



2. MuWizard yemabasa muboka "link uye arrays" tinowana uye kugovera zita rekuti "index".



3. Iyi inoshanda ine mafomu maviri: fomu yekushanda ne arrays uye revo. Kwedu, sarudzo yekutanga inodiwa, saka mune inotevera fomu yekusarudza hwindo rinovhura, sarudza uye tinya pane "ok" bhatani.



4. Iyo Operator nharo Index inotanga kumhanya. Basa rebasa rakatsanangurwa ndiko kubuda kweiyo kukosha kuri mune yakasarudzwa renji mumutsara neiyo nhamba yakatarwa. General Formula Operator Index yakadaro:

   = Index (array; nhamba_name; [nhamba_stallbits])

   Iyo "array" kupokana kune huwandu hwehuwandu hwatichabvisa ruzivo nehuwandu hwemutsara wakataurwa.

   "Mutsetse nhamba" kupokana ndiko kuwanda kweiyi mutsara. Izvo zvakakosha kuti uzive kuti nhamba yemutsetse inofanira kutaurwa kwete nehurumende yese, asi chete hama yakapihwa kune yakagoverwa.

   Iyo "nhamba yekhoramu" kupokana kunosarudzika. Kugadzirisa zvakananga nezvebasa redu, hatizorisire, uye saka hazvifanirwe kurondedzera zvakasiyana.

   Isu tinoisa iyo inotuka mu "Array" munda. Mushure meizvozvo, enda kune pepa 2 uye, akabata bhatani rekuruboshwe bhatani, sarudza izvo zvese zve "chiyero" chikamu.



5. Mushure mekurongeka marongerwo aratidzwa muhwindo remuridzi, tinoisa iyo inotuka mu "mutsara nhamba" munda. Isu tichabvisa kupokana uku tichishandisa iyo yekutsvaga opareta. Naizvozvo, tinya pane imwe triangle iyo iri kuruboshwe kwetambo yebasa. Rondedzero yevashandisi vatsva vanoshandiswa vanovhura. Kana iwe ukawana zita rekuti "Kambani yekutsvaga" pakati pavo, unogona kudzvanya pairi. Mune yakatarisana kesi, tinya pane yazvino nzvimbo yerondedzero - "mamwe mabasa ...".



6. Standard window wizard hwindo rinotanga. Enda kwazviri muboka rimwe chete "zvinongedzo uye artays". Ino nguva mune rondedzero, sarudza chinhu "kambani yekutsvaga". Ita tinya pane "ok" bhatani.



7. Kugadziriswa kwehasha dzekupokana kweshanduro yekutsvaga kunoitwa. Basa rakatsanangurwa rakagadzirirwa kuburitsa kukosha nhamba mune yakatarwa nezita rayo. Iyo inotenda kune ichi chimiro chatinenge tichiverenga huwandu hwetambo yeimwe kukosha kwekukosha kwebasa basa. Iyo syntax yebhodhi yekutsvaga inounzwa:

   = Tsvaga bhodhi (kutsvaga_name; kuona__nassive; [Type_station])

   "Iyo yaunoda" kupokana kune zita kana kero yechitokisi chechitatu-bato renzvimbo iyo iyo iripo. Iyo inzvimbo yezita iri mune yakanangwa huwandu uye inofanira kuverengerwa. Kwedu isu, basa rekupokana kwekutanga richaratidzwa masero pane pepa 1, mune izvo vashandi vanowanikwa.

   "Rondedzero yakanyorwa" kupokana, iyo inoreva kune yakarongeka, iyo inoita kutsvaga kukosha kwayo kuti isarudze chinzvimbo chayo. Tichava nebasa iri rekuita kero ye "zita" rikhoramu pane pepa 2.

   "Rudzi rwekuenzanisa" - kupokana kunosarudzika, asi, kusiyana neyepamberi oparidza, iyi sarudzo yekusarudzika ichadiwa. Zvinoratidza maitiro ekufananidza anoshanda ndeye kukosha kwaunoda nehurongwa. Uku kupopotedzana kunogona kuve neimwe yetsika nhatu: -1; 0; 1. Zvekuremara arrays, sarudza sarudzo "0". Iyi sarudzo yakakodzera nyaya yedu.

   Saka, enderera kuzadza iyo nharo yemavheni. Isu tinoisa chikwangamo mumunda "kukosha kwakashata", tinya pane yekutanga cell "zita" chikamu pane pepa 1.



8. Mushure mekuratidzwa kweratidziro, seta iyo yekunyepedzera mu "runyorwa ruzhinji" Clement bhatani rekuruboshwe uye simbisa kutuka kwese kwe "zita" ikholamu.



9. Mushure mekuratidzana kwavo kuratidzwa mu "runyorwa rukuru" munda, enda ku "Mepu Type" munda uye isa nhamba "0" kubva kukhibhodi. Mushure meizvozvo, tinodzokera zvakare kusango "kutarisa nehuremu". Chokwadi ndechekuti isu tichaita kutevedzera formula, sezvatakaita nenzira yekare. Kuchave nekuchinja kwemakero, asi pano kuronga kwouranga kwaida isu tinofanira kuchengetedza. Haafanire kuchinja. Isu tinosimbisa maironga nejista uye tinya pane iyo F4 basa kiyi. Sezvauri kuona, chiratidzo chedhora chakasangana pamberi pezvisikwa, zvinoreva kuti chinongedzo chinobva mumukana chakatendeukira mumhedziso. Wobva wadzvanya pane "OK" bhatani.



10. Mhedzisiro yacho inoratidzwa muchitokisi chekutanga ye "Bet" ikholamu. Asi usati watevedzera, isu tinofanirwa kugadzirisa imwe nzvimbo, kureva kuti yekutanga nharo yekuita index. Kuti uite izvi, sarudza iyo column element, iyo ine formula, uye uende ku formula tambo. Govera nharo yekutanga yeiyo Operator Index (B7) uye tinya pane iyo F4 bhatani. Sezvauri kuona, chikwangwani chedhora chakaonekwa padhuze nedunhu rakasarudzwa. Dzvanya kiyi yekupinda. Kazhinji, formula yakatora fomu inotevera:

    = Index (Sheet2! $ B $ 2: $ B $ 7; Tsvaga bhodhi (pepa1! Rondedzero2! $ $ 2: $ $ 7; 0)



11. Iye zvino unogona kutevedzera uchishandisa mucherechedzo unozadza. Isu tinozvidaidza nenzira imwechete yatataura kare, uye kutambanudza kusvika kumagumo eiyo tabular huwandu.



12. Sezvauri kuona, kunyangwe paine chokwadi chekuti kurongeka kwetambo mumatafura maviri anoenderana hakufambi, zvisinei, tsika dzese dzakasimbiswa maererano nemazita evashandi. Izvi zvakawanikwa nekutenda nekushandiswa kwesanganiswa yevanoshanda index yekutsvaga.

Nzira yechina: Inokosha Insert

TIE TAX ATRAYS MUKUSHUMIRA UNOGONA KUTI UVE KUTI UCHIDZE KUKOSHESA KWEMAHARA.

1. Sarudza iyo kukosha kwaunoda 'kusimbisa "kune imwe tafura. Pane yedu nyaya, iyi ndiyo "kubheja" column renji pane pepa 2. Dzvanya pane iyo yakazvitsaurira chidimbu neiyo kurudyi mbeva bhatani. Mune rondedzero inovhura, sarudza iyo "Kopa" chinhu. Imwe musanganiswa ndiyo ctrl + c kiyi musanganiswa. Mushure meizvozvo, isu tinoenda kune pepa 1.



2. Kutamira kunzvimbo yebhuku raunoda, kugovera masero umo maitiro acho anofanirwa kusimbiswa. Mamiriro edu ezvinhu, iyi ndiyo "bidhi" ikhoramu. Dzvanya pane iyo yakazvitsaurira chidimbu neiyo kurudyi mbeva bhatani. Mune iyo mamiriro ezvinhu menyu mune "isa parameter" Toolbar, tinya pane "isa kutaurirana" icon.

   

   Kune zvakare imwe nzira. Iye, nenzira, ndiyo yega yega yekare shanduro dzeBudiriro. Muzvinyorwa zvemhando yepamusoro, tinounza iyo inotuka kune iyo "yakakosha kuisa" chinhu. Mumhando yekuwedzera inovhura, sarudza chinzvimbo nezita rimwe chete.



3. Mushure meizvozvo, iyo inokosha quice inovhura. Dzvanya pane "isa kutaurirana" bhatani mune zasi kuruboshwe kona yechitokisi.



4. Chero ipi sarudzo yaunosarudza, tsika kubva kune imwe titfura zvirongwa zvichaiswa mune imwe. Kana uchichinja data mune sosi, ivo vanozochinjawo zvoga mune yakaiswa renji.

Chidzidzo: Inokosha Insert Mukurinda

Nzira 5: Kukurukurirana pakati pematafura mumabhuku akati wandei

Mukuwedzera, iwe unogona kuronga kubatana pakati pembizi nzvimbo mumabhuku akasiyana. Izvi zvinoshandisa chinhu chakakosha isa chishandiso. Zviito zvichave zvakafanana nezvevatakurukura nenzira dzapfuura, kunze kwekuti kufamba kwenzvimbo panguva iyo mafomula hakuzove pakati penzvimbo dzebhuku rimwe, asi pakati pefaira. Nomuzvarirwo, mabhuku ese ane hukama anofanirwa kuvhurwa.

1. Sarudza iyo data renji kuti iendese kune rimwe bhuku. Dzvanya pairi kurudyi mbeva bhatani uye sarudza iyo "Kopa" chinzvimbo mune yakavhurwa menyu.



2. Ipapo tinobva taenda kubhuku umo data iri rinofanira kuiswa. Sarudza iyo yaunoda huwandu. Tinya kurudyi mbeva bhatani. Mune iyo mamiriro emamenyu mune iyo "isa marongero" boka, sarudza "isa kutaurirana" chinhu.



3. Mushure meizvozvo, izvo zvinokoshesa zvichaiswa. Paunenge uchichinja data mubhuku reSurce, tafura yakarongeka kubva kubhuku rekushanda richavasimbisa otomatiki. Uye hazvisi zvachose kuve nechokwadi chekuti mabhuku ese ari maviri akavhurika. Izvo zvakaringana kuvhura rimwe chete bhuku rekuita basa rekuita, uye rinongogadzirisa data kubva kugwaro rakavharwa rakavharwa kana paine yakambochinja mairi.

Asi zvinofanirwa kucherechedzwa kuti mune iyi nyaya iyo inset ichagadzirwa muchimiro chehurongwa husina kuchinjika. Kana uchiedza kuchinja chero sero neiyo data yakaiswa, meseji ichaiswa kuizivikanwa nezvekutadza kuita izvi.

Shanduko mune yakarongeka yakabatana neimwe bhuku inogona kungotyora kubatana.

Musoro wapaza pakati pematafura

Dzimwe nguva zvinodikanwa kuputsa kubatana pakati pematafura. Chikonzero ichi chinogona kunge chiri pamusoro chinotsanangurwa paunoda kushandura rondedzero kubva kune rimwe bhuku uye kungozvidemba kwemushandisi kuitira kuti data racho riwedzere patafura inogadziriswa kubva kune imwe.

Nzira 1: Kukurukurirana Kunopaza Pakati peMabhuku

Kuputsa kubatana pakati pemabhuku mumasero ese, nekuita chaiko kushanda. Mune ino kesi, iyo data iri mumasero icharamba iripo, asi ivo vachatove vatambi vasingagadziriswe izvo zvisingavimbi nedzimwe zvinyorwa.

1. Mubhuku iri tsika nezvimwe mafaira vari akasungirirwa, uende Data Tab. Dzvanya pane iyo "Shanduko inobatanidza" icon, iyo iri patepi mu "kubatana" Toolbar. Izvo zvinofanirwa kucherechedzwa kuti kana bhuku razvino harina hukama nemafaira, saka bhatani iri haringashande.



2. Iyo link yekuchinja hwindo rakatangwa. Sarudza kubva pamazita mabhuku anoritsanangura (kana pane zvakawanda kwavari) faira nako tinoda kuputsa papi. Dzvanya pane bhatani "Pwanya kubatana".



3. An hwindo ruzivo anovhura, chinopa yambiro nezvemigumisiro kwohumwe nezviito. Kana iwe uine chokwadi chekuti iwe uchaita, tinya pane "Break Kutaurirana" bhatani.



4. Pashure paizvozvo, anotaura zvose kune raanotaura faira iri gwaro ano ichatsiviwa kunofambira mberi tsika.

Nzira 2: Kuisa tsika

Asi nzira iri pamusoro yakakodzera chete kana iwe uchida kutyora zvakakwana zvibodzwa zvese pakati pemabhuku maviri. Ko kana iwe uchifanira kudzima matafura akabatana mukati mefaira rimwe chete? Iwe unogona kuita izvi nekuteedzera iyo data, uye wozoisa iyo nzvimbo yakafanana nekwadi. Nenzira, nzira iyi inogona kupururwa pakati peiyo yega data yemabhuku akasiyana pasina kutyora hukama hwakajairika pakati pefaira. Ngatione kuti sei nzira iyi inoshanda mukuita.

1. Isu tinosimbisa huwandu hwatinoshuvira kudzima kutaurirana neimwe tafura. Dzvanya pairi kurudyi mbeva bhatani. Mune iyo yakavhurika menyu, sarudza iyo "Kopa" chinhu. Panzvimbo yezviito zvakatsanangurwa, iwe unogona kufonera imwe musanganiswa wekupisa makiyi ctrl + c.



2. Tevere, pasina kubvisa kusarudzwa kubva kune imwecheteyo chidimbu, zvakare tinya pairi neiyo kurudyi mbeva bhatani. Ino nguva mune rondedzero yekuita, tinya pane "kukosha" icon, iyo inotumirwa mune inoisa parameter boka.



3. Mushure meizvozvo, mareferenzi ese mune yakazvipira mhando ichatsiviwa neyekutarisirwa tsika.

Sezvauri kuona, Excel ine nzira uye zvishandiso zvekubatanidza matafura akati wandei pakati pavo. Panguva imwecheteyo, tabular data inogona kunge iri pane mamwe mashiti uye kunyange mumabhuku akasiyana. Kana zvichidikanwa, kubatana uku kunogona kuputswa zviri nyore.