Vinna með tengd töflur í Microsoft Excel

Þegar þú framkvæmir ákveðin verkefni í Excel verðurðu stundum að fást við nokkrar töflur, sem einnig tengjast. Það er, gögn frá einni töflu eru dregin í aðra og þegar þeim er breytt eru gildi endurútreiknuð í öllum skyldum töflu sviðum.

Tengd töflur eru mjög þægilegar til notkunar við vinnslu á miklu magni af upplýsingum. Til að setja allar upplýsingar í eina töflu, að auki, ef þær eru ekki einsleitar, eru það ekki mjög þægilegar. Það er erfitt að vinna með slíka hluti og leita að þeim. Fyrirhugað vandamál er hannað til að eyða með tengdum töflum þar sem upplýsingum er dreift á milli en samtímis samtengd. Tengt svið svið getur verið staðsett ekki aðeins innan eins blaðs eða einnar bókar, heldur getur það einnig verið staðsett í aðskildum bókum (skrám). Síðustu tveir valkostir eru oftast notaðir í reynd, þar sem tilgangur þessarar tækni er að komast burt frá gagnaöflun, og að hrúga þeim á einni síðu leysir ekki vandamálið í grundvallaratriðum. Við skulum læra hvernig á að búa til og hvernig á að vinna með þessa tegund gagnastjórnunar.

Að búa til tengd töflur

Í fyrsta lagi skulum við dvelja við spurninguna um hvaða aðferðir það er tækifæri til að skapa samband milli mismunandi töfluflokka.

Aðferð 1: tengja töflur beint við formúlu

Auðveldasta leiðin til að binda gögn er að nota formúlur sem vísa til annars borðsviðs. Það er kallað bein binding. Þessi aðferð er leiðandi, þar sem tengingin er framkvæmd á næstum sama hátt og að búa til tengla á gögn í einni töflukerfi.

Við skulum sjá hvernig til dæmis er hægt að mynda tengsl með beinni bindingu. Við erum með tvö borð á tveimur blöðum. Á einni töflu er launaskrá reiknuð með formúlunni með því að margfalda starfsmannatíðni með einum stuðli fyrir alla.

Á öðru blaði er töflusvið, sem inniheldur lista yfir starfsmenn með laun sín. Listi yfir starfsmenn í báðum tilvikum er kynntur í sömu röð.

Nauðsynlegt er að ganga úr skugga um að gögnin um tíðni frá öðru blaði séu dregin inn í samsvarandi frumur þess fyrsta.

1. Veldu fyrsta reitinn í dálknum Bjóða. Við setjum inn skilti í það "=". Næst skaltu smella á flýtileiðina „Blað 2“, sem er staðsett vinstra megin við Excel tengi fyrir ofan stöðustikuna.



2. Færir á annað svæði skjalsins. Við smellum á fyrstu reitinn í dálknum Bjóða. Smelltu síðan á hnappinn Færðu inn á lyklaborðinu til að færa gögn inn í hólfið sem skiltið var áður sett í jafngildir.



3. Síðan eru sjálfvirk umskipti yfir á fyrsta blaðið. Eins og þú sérð er gengi fyrsta starfsmanns frá öðru borði dregið inn í samsvarandi reit. Með því að setja bendilinn á reitinn sem inniheldur veðmálið, sjáum við að venjulega formúlan er notuð til að birta gögn á skjánum. En áður en hnit frumunnar þaðan sem gögnin eru send út er til staðar "Sheet2!", sem gefur til kynna nafn skjalsvæðisins þar sem þau eru staðsett. Almenna uppskriftin í okkar tilfelli lítur svona út:

   = Blað2! B2



4. Nú þarftu að flytja gögnin um gengi allra annarra starfsmanna fyrirtækisins. Auðvitað er hægt að gera þetta á sama hátt og við kláruðum verkefnið fyrir fyrsta starfsmanninn, en í ljósi þess að báðir starfsmannalistar eru raðað í sömu röð er hægt að einfalda verkefnið verulega og flýta fyrir lausn þess. Þetta er hægt að gera með því einfaldlega að afrita formúluna á svæðið hér að neðan. Vegna þess að hlekkirnir í Excel eru sjálfkrafa afstæður, þegar þeir eru afritaðir, eru gildin færð, og það er það sem við þurfum. Afritunarferlið sjálft er hægt að gera með því að nota áfyllingarmerkið.

   Svo skaltu setja bendilinn neðst til hægri í hlutanum með formúlunni. Eftir það ætti að breyta bendilnum í áfyllingarmerki í formi svarts kross. Klemmdu vinstri músarhnappinn og dragðu bendilinn alveg neðst í dálkinn.



5. Öll gögn úr svipuðum dálki á Blað 2 voru dregin að borðinu á Blað 1. Þegar gögnum er breytt í Blað 2 þær breytast sjálfkrafa á þeim fyrsta.

Aðferð 2: að nota fullt af INDEX rekstraraðilum - SÖK

En hvað ef listi yfir starfsmenn í töflunni fylki er ekki í sömu röð? Í þessu tilfelli, eins og áður sagði, er einn af kostunum að koma á tengingu milli hverrar þessara frumna sem ætti að vera tengdur handvirkt. En þetta hentar aðeins fyrir lítil borð. Fyrir stórfelld svið tekur slíkur kostur í besta falli mikinn tíma í framkvæmd og í versta falli verður hann í reynd ekki geranlegur. En þetta vandamál er hægt að leysa með fullt af rekstraraðilum INDEX - SÖK. Við skulum sjá hvernig hægt er að gera þetta með því að tengja gögnin í töflunni sem fjallað var um í fyrri aðferð.

1. Veldu fyrsta dálkinn Bjóða. Fara til Lögun töframaðurmeð því að smella á táknið „Setja inn aðgerð“.



2. Í Aðgerðarhjálp í hópnum Tilvísanir og fylki finndu og auðkenndu nafnið INDEX.



3. Þessi rekstraraðili hefur tvö form: eyðublað til að vinna með fylki og tilvísun. Í okkar tilviki er fyrsti kosturinn nauðsynlegur, þess vegna, í næsta glugga til að velja formið sem opnast, veldu það og smelltu á hnappinn „Í lagi“.



4. Rofa rekstraraðila ræst INDEX. Verkefni þessarar aðgerðar er að framleiða gildi sem er á völdum sviðum í línunni með tilgreindu númeri. Almenn rekstrarformúla INDEX slíkt er:

   = INDEX (fylki; röð_númer; [dálkur_númer])

   Fylking - rifrildi sem inniheldur heimilisfang sviðsins sem við munum draga upplýsingar út úr með númeri tilgreindrar línu.

   Línunúmer - rökin, sem er fjöldi þessarar línu. Það er mikilvægt að vita að línunúmerið ætti ekki að vera tilgreint miðað við allt skjalið, heldur aðeins miðað við valinn fylking.

   Súlanúmer - rök sem eru valkvæð. Við munum ekki nota það til að leysa okkar sérstaka vandamál og þess vegna er ekki nauðsynlegt að lýsa kjarna þess sérstaklega.

   Settu bendilinn í reitinn Fylking. Eftir það, farðu til Blað 2 og haltu vinstri músarhnappi niður og veldu allt innihald dálksins Bjóða.



5. Eftir að hnitin hafa verið sýnd í glugga stjórnandans skaltu setja bendilinn í reitinn Línunúmer. Við munum framleiða þessi rök með stjórnandanum SÖK. Þess vegna smellum við á þríhyrninginn, sem er staðsettur vinstra megin við aðgerðalínuna. Listi yfir nýlega notaða stjórnendur opnast. Ef þú finnur nafn á meðal þeirra „SEARCH“þá geturðu smellt á það. Annars skaltu smella á síðasta hlutinn á listanum - „Aðrir eiginleikar ...“.



6. Venjulegur gluggi byrjar Töframaður töframaður. Við sendum það í sama hóp Tilvísanir og fylki. Að þessu sinni skaltu velja hlutinn á listanum „SEARCH“. Smelltu á hnappinn. „Í lagi“.



7. Gluggi rekstraraðila er virkur SÖK. Tilgreindu aðgerðinni er ætlað að birta númer gildi í tiltekinni fylki með nafni hennar. Þökk sé þessum eiginleika munum við reikna út línunúmer ákveðins gildi fyrir aðgerðina INDEX. Setningafræði SÖK táknað sem hér segir:

   = SEARCH (search_value; lookup_array; [match_type])

   „Að leita að gildi“ - rök sem innihalda nafn eða heimilisfang hólfsins í þriðja aðila sviðinu sem það er staðsett í. Það er staða þessa nafns í markviðinu sem ætti að reikna út. Í okkar tilviki verður fyrsta rifrildið vísanir í frumur sem eru á Blað 1þar sem nöfn starfsmanna eru staðsett.

   Skoðað fylking - rifrildi sem táknar tilvísun í fylki þar sem leitað er að tilteknu gildi til að ákvarða staðsetningu þess. Heimild dálksins "mun leika þetta hlutverk hér."Fornafn á Blað 2.

   Passagerð - rök, sem er valkvæð, en ólíkt fyrri fullyrðingu munum við þurfa þessi valfrjálsu rök. Það gefur til kynna hvernig rekstraraðili mun passa leitargildið við fylkinguna. Þessi rök geta haft eitt af þremur gildum: -1; 0; 1. Veldu fyrir óskipulagðar fylki "0". Þessi valkostur hentar okkar máli.

   Svo skulum byrja að fylla út reitina í rifrildaglugganum. Settu bendilinn í reitinn „Að leita að gildi“smelltu á fyrstu reitinn í dálknum „Nafn“ á Blað 1.



8. Eftir að hnitin hafa verið sýnd skaltu stilla bendilinn á reitinn Skoðað fylking og smelltu á flýtileiðina „Blað 2“, sem er staðsett neðst í Excel glugganum fyrir ofan stöðustikuna. Haltu vinstri músarhnappi og veldu allar hólf í dálkinum með bendilinn „Nafn“.



9. Eftir að hnit þeirra birtast á þessu sviði Skoðað fylkingfarðu á akurinn Passagerð og stilltu númerið þar á lyklaborðinu "0". Eftir það förum við aftur á akurinn Skoðað fylking. Staðreyndin er sú að við munum afrita formúluna, eins og við gerðum í fyrri aðferð. Heimilisskipti breyting mun eiga sér stað, en hér þurfum við að laga hnit fylkisins sem verið er að skoða. Það ætti ekki að vera á flótta. Veldu hnit með bendilinn og ýttu á aðgerðartakkann F4. Eins og þú sérð birtist dollaramerkið fyrir framan hnitin, sem þýðir að hlekkurinn hefur snúist frá tiltölulega í algera. Smelltu síðan á hnappinn „Í lagi“.



10. Niðurstaðan birtist í fyrstu reit dálksins. Bjóða. En áður en við afritum verðum við að laga annað svæði, nefnilega fyrstu rök aðgerðanna INDEX. Til að gera þetta skaltu velja dálkareininguna sem inniheldur formúluna og fara yfir í formúlulínuna. Við veljum fyrstu rök rekstraraðila INDEX (B2: B7) og smelltu á hnappinn F4. Eins og þú sérð birtist dollaramerkið nálægt völdum hnitum. Smelltu á hnappinn Færðu inn. Almennt tók formúlan eftirfarandi form:

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



11. Nú er hægt að afrita með áfyllingarmerkinu. Við köllum það á sama hátt og við ræddum um áðan, og teygjum það til loka töflunni.



12. Eins og þú sérð, þrátt fyrir þá staðreynd að röð röð tveggja tengdra taflna passar ekki, engu að síður, eru öll gildi dregin upp samkvæmt nöfnum starfsmanna. Þetta var náð með því að nota blöndu af rekstraraðilum INDEX-SÖK.

Lestu einnig:
EXEX aðgerð í Excel
EXCEL aðgerð í Excel

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

Bein gagnabinding er líka góð vegna þess að hún gerir þér kleift að sýna ekki aðeins gildi sem birtast í öðrum töflu sviðum í einni af töflunum, heldur einnig 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 útfært í reynd. Við skulum gera það áfram Blað 3 almenn launagögn fyrir fyrirtækið verða birt án sundurliðunar starfsmanna. Til að gera þetta verður verð starfsmanna dregið frá Blað 2, dregið saman (með því að nota aðgerðina SUM) og margfalda með stuðlinum með formúlunni.

1. Veldu hólfið þar sem niðurstaða útreiknings launa skal birt. Blað 3. Smelltu á hnappinn. „Setja inn aðgerð“.



2. Gluggi ætti að byrja Töframaður töframaður. Farðu í hópinn „Stærðfræði“ og veldu nafnið þar SUM. Næst skaltu smella á hnappinn „Í lagi“.



3. Aðgerðarrökin eru færð í gluggann SUM, sem er hannað til að reikna summan af völdum tölum. Það hefur eftirfarandi setningafræði:

   = SUM (fjöldi1; fjöldi2; ...)

   Reitirnir í glugganum samsvara rökum tiltekins aðgerðar. Þó að fjöldi þeirra geti orðið 255 mun aðeins einn duga fyrir okkar tilgang. Settu bendilinn í reitinn „Fjöldi1“. Smelltu á flýtileiðina „Blað 2“ fyrir ofan stöðustikuna.



4. Eftir að við höfum flutt til viðeigandi hluta bókarinnar skaltu velja dálkinn sem ætti að taka saman. Við gerum þetta með bendilnum á meðan við höldum vinstri músarhnappi. Eins og þú sérð eru hnit valda svæðisins strax birt í reitnum rifrildaglugganum. Smelltu síðan á hnappinn „Í lagi“.



5. Eftir það flytjum við sjálfkrafa til Blað 1. Eins og þú sérð er heildarfjárhæð tilboða starfsmanna þegar birt í samsvarandi þætti.



6. En það er ekki allt. Eins og við munum eru launin reiknuð út með því að margfalda gildi gengisins með stuðli. Þess vegna veljum við aftur reitinn þar sem samanlagða gildið er staðsett. Eftir það förum við yfir í formúlulínuna. Bættu við formúluna í henni margföldunarmerki (*), og smelltu síðan á frumefnið sem stuðullinn vísir er í. Smelltu á hnappinn til að framkvæma útreikninginn Færðu inn á lyklaborðinu. Eins og þú sérð reiknaði áætlunin út heildarlaun fyrirtækisins.



7. Aftur að Blað 2 og breyta gengi hvers starfsmanns.



8. Eftir það förum við aftur á síðuna með heildarupphæðina. Eins og þú sérð, vegna breytinga á tengdu töflunni, var niðurstaða heildarlauna sjálfkrafa endurútreiknuð.

Aðferð 4: sérsniðin innskot

Þú getur einnig tengt töflufáma í Excel með sérstöku innskoti.

1. Við veljum gildin sem þarf að „draga“ í aðra töflu. Í okkar tilviki er þetta svið súlunnar Bjóða á Blað 2. Við smellum á valda brotið með hægri músarhnappi. Veldu á listanum sem opnast Afrita. Annar flýtilykill er Ctrl + C. Eftir það flytjum við til Blað 1.



2. Þegar við höfum flutt á svæðið í bókinni sem við þurfum, veljum við frumurnar sem við munum þurfa að draga gildin upp í. Í okkar tilviki er þetta dálkur Bjóða. Við smellum á valda brotið með hægri músarhnappi. Í samhengisvalmyndinni í verkfærablokkinni Settu inn valkosti smelltu á táknið Límdu hlekk.

   

   Það er líka val. Tilviljun, það er sú eina fyrir eldri útgáfur af Excel. Sveifðu yfir á samhengisvalmyndina „Sérstakt innlegg“. Veldu viðbótina sem opnast, veldu staðsetningu með sama nafni.



3. Eftir það opnast sérstaki innskotsglugginn. Smelltu á hnappinn Límdu hlekk í neðra vinstra horni klefans.



4. Hvaða valkost sem þú velur, gildi úr einni töflu fylki verður sett inn í annan. Þegar gögnum er breytt í uppruna munu þau einnig breytast sjálfkrafa á settu sviðinu.

Lexía: Sérstök sett inn í Excel

Aðferð 5: hlekkur á milli töfla í mörgum bókum

Að auki geturðu skipulagt samskipti milli borðsvæða í mismunandi bókum. Sérstakt innskotstæki er notað. Aðgerðirnar munu vera algerlega svipaðar þeim sem við töldum í fyrri aðferð, nema að þú verður að sigla meðan þú gerir formúlur ekki á milli svæða í sömu bók, heldur á milli skráa. Auðvitað ættu allar tengdar bækur að vera opnar.

1. Veldu gagnasviðið sem þú vilt flytja í aðra bók. Hægrismelltu á það og veldu staðsetningu í valmyndinni sem opnast. Afrita.



2. Síðan förum við yfir í bókina sem þessi gögn þarf að setja inn í. Veldu viðeigandi svið. Hægri smellur. Í samhengisvalmyndinni í hópnum Settu inn valkosti veldu hlut Límdu hlekk.



3. Eftir það verða gildin sett inn. Þegar gögn í vinnubókinni breytast mun töfluúrvalið úr vinnubókinni sjálfkrafa draga það upp. Ennfremur er ekki nauðsynlegt að báðar bækurnar séu opnar fyrir þessu. Það er nóg að opna aðeins eina vinnubók og hún mun sjálfkrafa draga gögn úr lokuðu tengdu skjali ef breytingar hafa verið gerðar á henni áður.

En það skal tekið fram að í þessu tilfelli verður innskotið gert sem óbreytanlegt fylki. Þegar þú reynir að breyta einhverri reit með gögnum sem sett er inn birtast skilaboð sem segja þér að það sé ómögulegt að gera þetta.

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

Bil á milli borða

Stundum þarftu að rjúfa tenginguna milli borðsviða. Ástæðan fyrir þessu getur verið annað hvort ofangreint tilfelli, þegar þú þarft að breyta fylki sem sett er inn úr annarri bók, eða einfaldlega tregðu notandans við að gögnin í einni töflu séu sjálfkrafa uppfærð úr annarri bók.

Aðferð 1: að rjúfa tenginguna á milli bóka

Þú getur rofið tenginguna á 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 eru nú þegar stöðug gildi sem ekki er hægt að uppfæra, sem á engan hátt háð öðrum skjölum.

1. Farðu í flipann þar sem gildi úr öðrum skrám eru dregin „Gögn“. Smelltu á táknið „Breyta samskiptum“staðsett á borði í verkfærakistunni Tengingar. Þess má geta að ef núverandi bók inniheldur ekki tengla á aðrar skrár, þá er þessi hnappur óvirkur.



2. Glugginn á hlekknum breytist. Við veljum skrána sem við viljum rjúfa tenginguna frá lista yfir tengdar bækur (ef það eru nokkrar). Smelltu á hnappinn Brjóttu hlekkinn.



3. Upplýsingagluggi opnast þar sem varað er við afleiðingum frekari aðgerða. Ef þú ert viss um hvað þú ert að fara, smelltu síðan á hnappinn „Brjóta bönd“.



4. Eftir það verður öllum tenglum á tiltekna skrá í núverandi skjali skipt út fyrir truflanir.

Aðferð 2: Settu gildi inn

En ofangreind aðferð hentar aðeins ef þú þarft að slíta alla tengslin á milli bókanna tveggja fullkomlega. Hvað á að gera ef þú þarft að aðgreina skyldar töflur sem eru innan sömu skráar? Þú getur gert þetta með því að afrita gögnin og líma þau síðan á sama stað og gildin. Við the vegur, á sama hátt, geturðu rofið tenginguna milli einstakra gagnasviða mismunandi bóka án þess að brjóta almenna tengingu milli skráanna. Við skulum sjá hvernig þessi aðferð virkar í reynd.

1. Veldu svið sem við viljum fjarlægja hlekkinn í aðra töflu. Við smellum á það með hægri músarhnappi. Veldu í valmyndinni sem opnast Afrita. Í staðinn fyrir þessar aðgerðir geturðu slegið aðra samsetningu af heitum lyklum Ctrl + C.



2. Ennfremur, án þess að fjarlægja valið úr sama brotinu, hægrismellt aftur á það. Að þessu sinni á lista yfir aðgerðir skaltu smella á táknið „Gildi“sem er staðsett í verkfærahópnum Settu inn valkosti.



3. Eftir það verður öllum tenglum á völdum sviðum skipt út fyrir truflanir.

Eins og þú sérð, í Excel eru leiðir og tæki til að tengja nokkrar töflur saman. Á sama tíma geta töflugögn verið á öðrum blöðum og jafnvel í mismunandi bókum. Ef nauðsyn krefur er auðvelt að brjóta þessa tengingu.