Vinna með tengdum borðum í Microsoft Excel

Þegar þú framkvæmir ákveðnar verkefni í Excel þarf stundum að takast á við nokkrar töflur sem einnig tengjast hvert öðru. Þannig eru gögnin frá einni töflunni dregin inn í aðra, og þegar þau breytast eru endurreiknar gildin í öllum tengdum borðum.

Tengdir töflur eru mjög gagnlegar til að vinna mikið magn upplýsinga. Það er ekki mjög þægilegt að hafa allar upplýsingar í einu borði og ef það er ekki einsleitt. Það er erfitt að vinna með slíkar hlutir og leita þeirra. Þetta vandamál er ætlað að útrýma tengdum borðum, upplýsingum á milli er dreift, en á sama tíma er tengt. Tengd borðviðfangsefni geta verið staðsettar ekki aðeins innan eins blaða eða ein bók, heldur einnig í sérstökum bækur (skrár). Í reynd eru síðustu tvær valkostir notaðar oftast, þar sem tilgangur þessarar tækni er að komast í burtu frá uppsöfnun gagna og hala þeim á sömu síðu leysir ekki í grundvallaratriðum vandamálið. Við skulum læra hvernig á að búa til og hvernig á að vinna með þessa tegund af gagnastjórnun.

Búa til tengda töflur

Fyrst af öllu, skulum við búast við spurningunni um hvernig hægt er að búa til tengsl milli mismunandi borðvalla.

Aðferð 1: Bein tenging tafla með formúlu

Auðveldasta leiðin til að tengja gögn er að nota formúlur sem tengjast öðrum borðum. Það er kallað bein bindandi. Þessi aðferð er innsæi, þar sem bindan er framkvæmd á næstum sama hátt og að búa til tilvísanir í gögn í einni töfluflokki.

Lítum á hvernig dæmi geta myndað skuldabréf með beinni bindingu. Við höfum tvær töflur á tveimur blöðum. Í einni töflu er launaskrá reiknuð með því að nota formúlu með því að margfalda hlutfall starfsmanna með einu hlutfalli fyrir alla.

Á annarri blaðinu er tíðnisvið þar sem listi yfir launþega er með laun. Listi yfir starfsmenn í báðum tilvikum er kynnt í sömu röð.

Nauðsynlegt er að gera þannig að gögnin á tíðni frá öðru blaði er dregin upp í samsvarandi frumum fyrstu.

1. Á fyrsta blaðinu skaltu velja fyrsta dálkinn. "Veðja". Við settum í hana merki "=". Næst skaltu smella á merkimiðann "Sheet 2"Sem er staðsett vinstra megin við Excel-tengið fyrir ofan stöðustikuna.



2. Færir á annað svæði skjalsins. Smelltu á fyrsta reitinn í dálknum. "Veðja". Smelltu síðan á hnappinn. Sláðu inn á lyklaborðinu til að framkvæma gagnadagsetningu í reitnum sem táknið var sett áður jafngildir.



3. Þá er sjálfvirk umskipti í fyrsta blaðið. Eins og þú sérð er hlutfall fyrstu starfsmanns frá seinni töflunni dreginn inn í viðeigandi reit. Þegar þú hefur sett bendilinn á reitinn sem inniheldur veðmálið, sjáum við að venjuleg formúla er notuð til að birta gögn á skjánum. En fyrir hnit frumunnar þar sem gögnin birtast, er tjáning "Sheet2!"sem gefur til kynna heiti svæðisins skjalsins þar sem þau eru staðsett. Almennt formúlan í okkar tilfelli er sem hér segir:

   = Sheet2! B2



4. Nú þarftu að flytja gögnin á verð allra annarra starfsmanna fyrirtækisins. Auðvitað má gera þetta á sama hátt og við náðum að vinna fyrir fyrsta starfsmanninn, en með því að bæði lista yfir starfsmenn er raðað í sömu röð getur verkefnið verið verulega einfalt og flýtt fyrir lausnina. Þetta er hægt að gera með því einfaldlega að afrita formúluna á bilinu hér að neðan. Vegna þess að tenglar í Excel eru tiltölulega sjálfgefin, þegar þau eru afrituð breytist gildi, sem er það sem við þurfum. Afritunarferlið sjálft er hægt að framkvæma með því að nota fylla merkið.

   Svo skaltu setja bendilinn á neðri hægra megin við frumefni með formúlunni. Eftir það ætti bendillinn að breyta í fyllingu í formi svört kross. Við framkvæmum klemma vinstri músarhnappsins og dregur bendilinn niður á botn dálksins.



5. Öll gögn frá sömu dálki á Blað 2 voru dregin til borðsins Blað 1. Þegar gögn breytast í Blað 2 Þeir breytast sjálfkrafa fyrst.

Aðferð 2: Notaðu fullt af rekstraraðilum INDEX - MATCH

En hvað ef listi yfir starfsmenn í töflunni er ekki raðað í sömu röð? Í þessu tilfelli, eins og fyrr segir, er einn af valkostunum að setja upp tengingu á milli þessara frumna sem á að tengja handvirkt. En þetta er aðeins hentugur fyrir litlum borðum. Fyrir miklum sviðum mun þessi valkostur í besta falli taka mikinn tíma til að hrinda í framkvæmd og í versta falli - í raun er það ekki raunhæft. En þú getur leyst þetta vandamál með fullt af rekstraraðila INDEX - MATCH. Við skulum sjá hvernig hægt er að gera þetta með því að tengja gögn í töfluflokki, sem rætt var um í fyrri aðferð.

1. Veldu fyrsta atriði í dálknum. "Veðja". Fara til Virka Wizardmeð því að smella á táknið "Setja inn virka".



2. Í Virka töframaður í hópi "Tenglar og fylki" finna og veldu nafnið INDEX.



3. Þessi rekstraraðili hefur tvær gerðir: form til að vinna með fylki og tilvísun. Í okkar tilviki er fyrsta kosturinn krafinn, svo í næsta glugga til að velja eyðublað sem opnast velurðu það og smellir á hnappinn "OK".



4. Rammaglugga símafyrirtækisins hefur verið keyrt. INDEX. Verkefni tilgreintra aðgerða er að sýna gildi sem er á völdu bili í línu með tilgreint númer. Almennar notendahópar INDEX er þetta:

   = INDEX (array; line_number; [dálknúmer])

   "Array" - rökin sem inniheldur heimilisfang sviðsins sem við munum draga úr upplýsingum með fjölda tilgreindra strenga.

   "Línanúmer" - rökin sem er fjöldi þessa línu sjálfs. Það er mikilvægt að vita að lína númerið ætti ekki að vera tilgreint miðað við allt skjalið, en aðeins miðað við valið fylki.

   "Dálknúmer" - Rökin eru valfrjáls. Til að leysa vandamálið sérstaklega, munum við ekki nota það, og því er ekki nauðsynlegt að lýsa kjarnanum sérstaklega.

   Settu bendilinn í reitinn "Array". Eftir það fara til Blað 2 og haltu vinstri músarhnappi, veldu allt innihald dálksins "Veðja".



5. Eftir að hnitin eru birt í rekstrarglugganum skaltu setja bendilinn í reitinn "Línanúmer". Við munum sýna þetta rök með því að nota símafyrirtækið MATCH. Því skaltu smella á þríhyrninginn sem er staðsett til vinstri við aðgerðalínuna. Listi yfir nýlega notuð rekstraraðila opnar. Ef þú finnur meðal þeirra nafn "MATCH"þá getur þú smellt á það. Annars skaltu smella á nýjustu hlutinn í listanum - "Aðrar aðgerðir ...".



6. Venjulegur gluggi byrjar. Virkni meistarar. Farðu í það í sama hópi. "Tenglar og fylki". Í þetta sinn á listanum skaltu velja hlutinn "MATCH". Framkvæma smelltu á hnappinn. "OK".



7. Virkir gluggaglugga stjórnanda MATCH. Tilgreindu aðgerðin er ætlað að birta fjölda gildis í tilteknu fylki með nafni þess. Þökk sé þessu tækifærið munum við reikna út raðnúmerið fyrir tiltekið gildi fyrir virkni. INDEX. Setningafræði MATCH kynnt sem:

   = MATCH (leitargildi; leitarsvið; [samsvörun])

   "Leitaði gildi" - rökin sem inniheldur nafn eða heimilisfang þriðja aðila svið klefi þar sem það er staðsett. Það er staða þessarar nafns í markhópnum sem ætti að reikna út. Í okkar tilviki mun fyrsta rökin vera tilvísanir í klefi til Blað 1þar sem eru nöfn starfsmanna.

   "Skoðað array" - rök sem táknar tengil á fylki þar sem tilgreint gildi er leitað til að ákvarða stöðu sína. Við munum spila þennan þáttatölu dálk "Fornafn á Blað 2.

   "Kortlagningartegund" - Rök sem er valfrjálst, en ólíkt fyrri yfirlýsingu, munum við þurfa þetta valfrjálsa rök. Það gefur til kynna hvernig rekstraraðili muni passa við viðeigandi gildi með fylkinu. Þetta rök getur haft eitt af þremur gildum: -1; 0; 1. Fyrir óraunaðar fylki skaltu velja valkostinn "0". Þessi valkostur er hentugur fyrir málið okkar.

   Svo, við skulum byrja að fylla út reitina á rökglugganum. Settu bendilinn í reitinn "Leitaði gildi", smelltu á fyrsta reit dálksins "Nafn" á Blað 1.



8. Eftir að hnitin eru sýnd skaltu stilla bendilinn í reitinn "Skoðað array" og farðu á flýtivísann "Sheet 2"sem er staðsett neðst á Excel glugganum yfir stöðustikunni. Haldið niðri vinstri músarhnappi og auðkennið öll frumurnar í dálknum. "Nafn".



9. Eftir hnit þeirra birtast í reitnum "Skoðað array"fara á völlinn "Kortlagningartegund" og stilla númerið úr lyklaborðinu "0". Eftir þetta ferum við aftur á svæðið aftur. "Skoðað array". Staðreyndin er sú að við munum afrita formúluna, eins og við gerðum í fyrri aðferðinni. Það mun vera á móti heimilisföngum en við þurfum að laga hnit þessarar fylkis. Það ætti ekki að breytast. Veldu hnit bendilsins og smelltu á virka takkann F4. Eins og þú sérð birtist dollara skilti fyrir framan hnitin, sem þýðir að tengillinn frá ættingjum hefur orðið alger. Smelltu síðan á hnappinn "OK".



10. Niðurstaðan birtist í fyrsta reitnum í dálknum. "Veðja". En áður en þú afritar þurfum við að laga annað svæði, þ.e. fyrsta rök aðgerðarinnar INDEX. Til að gera þetta skaltu velja hlutann í dálknum sem inniheldur formúluna og fara í formúlunni. Veldu fyrsta rök rekstraraðila INDEX (B2: B7) og smelltu á hnappinn F4. Eins og þú sérð birtist dollara skilti nálægt völdum hnitum. Smelltu á hnappinn Sláðu inn. Almennt tók formúlan eftirfarandi form:

    = INDEX (Sheet2! $ B $ 2: $ B $ 7; MATCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0))



11. Nú er hægt að afrita með fylla merkinu. Hringdu það á sama hátt og við ræddum um áður og teygðu það til loka borðsins.



12. Eins og þið getið séð, þrátt fyrir að röðin af röðum þessara tveggja tafla samræmist ekki, eru öll gildi aukin í samræmi við nöfn starfsmanna. Þetta var náð með því að nota samsetningu rekstraraðila INDEX-MATCH.

Sjá einnig:
Excel virka INDEX
Samsvörunin virkar í Excel

Aðferð 3: Framkvæma stærðfræðilegar aðgerðir með tengdum gögnum

Bein gagnabinding er einnig góð þar sem það gerir ekki aðeins kleift að sýna gildi sem birtast á öðrum borðum í einu af töflunum heldur einnig til að framkvæma ýmsar stærðfræðilegar aðgerðir með þeim (viðbót, skipting, frádráttur, margföldun osfrv.).

Við skulum sjá hvernig þetta er gert í reynd. Við skulum gera það á Blað 3 Almenn fyrirtæki launagögn verður birt án starfsmanna sundurliðun. Fyrir þetta verða starfsmenntaverð frá Blað 2, samantekt (með aðgerðinni SUM) og margfaldað með stuðlinum sem nota formúluna.

1. Veldu reitinn þar sem heildarlaunaskráin birtist á Blað 3. Smelltu á hnappinn "Setja inn virka".



2. Það ætti að ræsa gluggann Virkni meistarar. Fara í hópinn "Stærðfræði" og veldu nafnið þar "SUMM". Næst skaltu smella á hnappinn "OK".



3. Að flytja til aðgerðarglugga gluggans SUMsem er hannað til að reikna summan af völdum tölum. Það hefur eftirfarandi setningafræði:

   = SUM (númer1; númer2; ...)

   Reitarnir í glugganum eru í samræmi við rök af tilgreindum aðgerðum. Þrátt fyrir að fjöldi þeirra geti náð 255 stykki, þá mun aðeins einn okkar nægja okkur. Settu bendilinn í reitinn "Númer1". Smelltu á merkimiðann "Sheet 2" fyrir ofan stöðustikuna.



4. Eftir að við fluttum til viðkomandi hluta bókarinnar skaltu velja dálkinn sem á að samantekt. Við gerum það bendilinn, haltu vinstri músarhnappnum. Eins og þú sérð eru hnit valda svæðisins strax birt í reitarglugganum. Smelltu síðan á hnappinn. "OK".



5. Eftir það flytum við sjálfkrafa til Blað 1. Eins og þú sérð er heildarupphæð launa starfsmanna þegar sýndur í samsvarandi hlutanum.



6. En það er ekki allt. Eins og við munum, er launin reiknuð með því að margfalda gildi gengisins með stuðlinum. Þess vegna veljum við aftur klefann þar sem summa gildi er staðsett. Eftir það að fara á formúlu bar. Við bætum margföldunarskilti við formúluna (*) og smelltu síðan á þáttinn sem stuðullinn er staðsettur í. Til að framkvæma útreikninginn smellirðu á Sláðu inn á lyklaborðinu. Eins og þú sérð, reiknar forritið heildarlaun fyrir fyrirtækið.



7. Fara aftur til Blað 2 og breyta stærð gengis hvers starfsmanns.



8. Eftir þetta skaltu fara aftur á síðuna með heildarfjárhæðinni. Eins og þú sérð, vegna breytinga á tengdum töflu var niðurstaða heildarlauna sjálfkrafa endurreiknaður.

Aðferð 4: sérstök innsetning

Þú getur einnig tengt borðatölur í Excel með sérstöku innskoti.

1. Veldu gildin sem þurfa að vera "þéttari" í annað borð. Í okkar tilviki er þetta dálkur. "Veðja" á Blað 2. Smelltu á valda brotið með hægri músarhnappi. Í skránni sem opnast skaltu velja hlutinn "Afrita". Önnur lykill samsetning er Ctrl + C. Eftir það fara til Blað 1.



2. Þegar þú ferð á viðkomandi svæði bókarinnar veljum við þau frumur sem þú vilt draga gildin í. Í okkar tilviki er þetta dálkur. "Veðja". Smelltu á valda brotið með hægri músarhnappi. Í samhengisvalmyndinni á tækjastikunni "Valkostir innsetningar" smelltu á táknið "Setja inn hlekk".

   

   Það er líka val. Við the vegur, það er sú eina fyrir eldri útgáfur af Excel. Í samhengisvalmyndinni skaltu færa bendilinn á hlutinn "Paste Special". Í viðbótarvalmyndinni sem opnast skaltu velja hlutinn með sama nafni.



3. Eftir það opnast sérstakur innsláttargluggi. Við ýtum á hnappinn "Setja inn hlekk" í neðri vinstra horninu á reitnum.



4. Hvort sem þú velur valið verður gildi frá einni töfluflokki í annað. Þegar þú breytir gögnum í upptökunni breytast þau sjálfkrafa sjálfkrafa á því bilinu sem er í boði.

Lexía: Límið sérstakt í Excel

Aðferð 5: Tengsl milli tafla í mörgum bókum

Að auki geturðu skipulagt tengingu milli borðstofa í mismunandi bækur. Þetta notar sérstakt innsetningar tól. Aðgerðir munu vera algerlega svipaðar þeim sem við héldu í fyrri aðferðinni, nema að siglingar við innleiðingu formúlanna þurfi ekki að eiga sér stað milli svæða í einum bók, en milli skráa. Auðvitað skulu allar tengdar bækur vera opnir.

1. Veldu fjölda gagna sem þú vilt flytja í aðra bók. Smelltu á það með hægri músarhnappi og veldu stöðu í valmyndinni sem opnast "Afrita".



2. Þá ferum við í bókina þar sem þessi gögn verða að vera sett inn. Veldu viðkomandi svið. Smelltu á hægri músarhnappinn. Í samhengisvalmyndinni í hópnum "Valkostir innsetningar" veldu hlut "Setja inn hlekk".



3. Eftir þetta verða gildin sett inn. Þegar þú breytir gögnum í upprunabókinni færðu töflureiknirnar úr vinnubókinni sjálfkrafa þær. Og það er alls ekki nauðsynlegt fyrir báðar bækurnar að vera opin fyrir þetta. Það er nóg að opna aðeins eina vinnubók og það mun sjálfkrafa draga í gögnin úr lokuðum tengdum skjali ef breytingar voru gerðar áður.

En það ætti að hafa í huga að í þessu tilfelli verður innsetningin gerð í formi óbreytanlegs fjölda. Ef þú reynir að breyta hvaða reit með gögnum sem eru settar inn birtist skilaboðin upp að því að ekki er hægt að gera þetta.

Breytingar á slíku fylki sem tengjast öðrum bók er aðeins hægt að gera með því að brjóta tengilinn.

Aftengingu milli tafla

Stundum er nauðsynlegt að brjóta tengslin milli borðvalla. Ástæðan fyrir þessu getur verið, eins og um er að ræða hér að ofan, þegar þú þarft að breyta fylki sem sett er í annan bók eða einfaldlega vegna þess að notandinn vill ekki að gögnin í einu borði séu sjálfkrafa uppfærðar frá öðru.

Aðferð 1: Aftengja bækur

Þú getur skemmt tengingu milli bóka í öllum frumum með því að framkvæma nánast eina aðgerð. Á sama tíma verða gögnin í frumunum áfram, en þau munu þegar vera truflanir sem ekki eru uppfærðar og ekki háð öðrum skjölum.

1. Í bókinni, þar sem gildi frá öðrum skrám eru dregnar, farðu í flipann "Gögn". Smelltu á táknið "Breyta tenglum"sem er staðsett á borði í blokkinni af verkfærum "Tengingar". Það skal tekið fram að ef núverandi bók inniheldur ekki tengla við aðrar skrár, er þessi hnappur óvirkt.



2. Glugginn til að breyta tenglum er hleypt af stokkunum. Veldu úr listanum yfir tengdar bækur (ef það eru nokkrir) skráin sem við viljum slökkva á tengingunni. Smelltu á hnappinn "Brjótaðu tengilinn".



3. Upplýsingaskjár opnast, þar sem viðvörun er um afleiðingar frekari aðgerða. Ef þú ert viss um hvað þú ert að gera þá skaltu smella á hnappinn. "Brotabindingar".



4. Eftir það verða allir tilvísanir í tilgreindri skrá í núverandi skjali skipt út fyrir truflanir.

Aðferð 2: Setjið gildi

En ofangreind aðferð er aðeins hentug ef þú þarft að slíta öllum tenglum á milli tveggja bókanna. Hvað á að gera ef þú vilt aftengja tengdar töflur sem eru innan sömu skrá? Þú getur gert þetta með því að afrita gögnin og síðan klíra það inn á sama stað og gildin.Við the vegur, sama aðferð er hægt að nota til að brjóta tengsl milli sérstakra gagnasvið mismunandi bækur án þess að brjóta almenn tengsl milli skráa. Við skulum sjá hvernig þessi aðferð virkar í reynd.

1. Veldu svið þar sem við viljum fjarlægja tengilinn í aðra töflu. Smelltu á það með hægri músarhnappi. Í valmyndinni sem opnast skaltu velja hlutinn "Afrita". Í stað þessara aðgerða er hægt að slá inn aðra heiti með lykilatriðum. Ctrl + C.



2. Þá, án þess að fjarlægja valið úr sama broti, smellum við aftur á það með hægri músarhnappi. Í þetta sinn á lista yfir aðgerðir smellum við á táknið "Gildi"sem er sett í hóp verkfæra "Valkostir innsetningar".



3. Eftir það verða allir tenglar á völdu bilinu skipt út fyrir truflanir.

Eins og þú getur séð, Excel hefur aðferðir og verkfæri til að tengja nokkrar töflur saman. Í þessu tilfelli getur tafla gögnin verið á öðrum blöðum og jafnvel í mismunandi bókum. Ef nauðsyn krefur getur þetta tenging auðveldlega brotið.