Relaterede borde i Excel: Detaljerede instruktioner

Når der udføres visse opgaver i Excel, skal nogle gange håndtere flere tabeller, der også er relateret til hinanden. Det vil sige, at dataene fra et bord strammes til andre, og værdierne i alle relaterede tabeller genberegnes, når de ændres.

Relaterede tabeller er meget bekvemme at bruge til at håndtere en stor mængde information. Placer alle oplysninger i et bord, foruden, hvis det ikke er homogent, ikke meget praktisk. Det er svært at arbejde med sådanne objekter og for at søge efter dem. Det angivne problem er netop designet til at eliminere relaterede tabeller, de oplysninger, der er distribueret, men samtidig er sammenkoblet. Relaterede tabeller kan ikke kun være inden for et ark eller en bog, men også at være placeret i separate bøger (filer). De sidste to muligheder i praksis bruges oftest, da formålet med denne teknologi er blot at komme væk fra akkumulering af data, og deres nikkende på en side løser ikke fundamentalt. Lad os lære at oprette og hvordan man arbejder med en sådan type datahåndtering.

Oprettelse af relaterede tabeller

Først og fremmest, lad os fokusere på spørgsmålet, på hvilken måde det er muligt at skabe en forbindelse mellem forskellige tabeller.

Metode 1: Direkte bindende tabeller Formel

Den nemmeste måde at binde data på er brugen af ​​formler, hvor der henvises til andre tabeller. Det hedder direkte binding. Denne metode er intuitiv, da når den binder det næsten ligesom at oprette referencer til data i et bord array.

Lad os se, hvordan eksemplet du kan danne kommunikation ved direkte binding. Vi har to tabeller på to ark. På samme tabel beregnes lønnen ved hjælp af formlen ved at multiplicere medarbejderraten for en enkelt koefficient.

På det andet ark er der et bordområde, hvor der er en liste over medarbejdere med deres løn. Listen over medarbejdere i begge tilfælde præsenteres i en rækkefølge.

Det er nødvendigt at gøre disse data på væddemålene fra det andet ark stramt i de tilsvarende celler i den første.

1. På det første ark tildeler vi den første celle i kolonnen "BET". Vi sætter ind i tegnet "=". Klik derefter på "Sheet 2" -mærket, som er placeret på venstre del af Excel-grænsefladen over statuslinjen.



2. Der er en bevægelse i det andet område af dokumentet. Klik på den første celle i kolonnen "Bet". Klik derefter på Enter-knappen på tastaturet for at indtaste dataene i cellen, hvori "Equal" -skiltet tidligere blev installeret.



3. Derefter er der en automatisk overgang til det første ark. Som vi kan se, er værdien af ​​den første medarbejder fra den anden tabel trukket ind i den tilsvarende celle. Ved at installere markøren på en celle, der indeholder et væddemål, ser vi, at den sædvanlige formel bruges til at vise data på skærmen. Men foran koordinaterne for cellen, hvorfra dataene udsendes, er der et udtryk "List2!", Som angiver navnet på det dokumentområde, hvor de er placeret. Den generelle formel i vores tilfælde ser sådan ud:

   = List2! B2



4. Nu skal du overføre data om satserne for alle andre medarbejdere i virksomheden. Selvfølgelig kan dette gøres på samme måde som vi opfyldte opgaven for den første medarbejder, men i betragtning af at begge medarbejderlister er placeret i samme rækkefølge, kan opgaven forenkles væsentligt og fremskyndet af sin beslutning. Dette kan gøres ved blot at kopiere formlen til nedenstående område. På grund af det faktum, at referencerne til Excel er relativ, skiftes værdierne, som vi har brug for, når de kopierer deres værdier, som vi har brug for. Kopiproceduren selv kan laves ved hjælp af en påfyldningsmarkør.

   Så vi sætter markøren til det nederste højre område af elementet med formlen. Derefter skal markøren konvertere til påfyldningsmarkøren i form af et sort kryds. Vi udfører klemmen på venstre museknap og trækker markøren til nummeret på søjlen.



5. Alle data fra en lignende kolonne på et ark 2 blev trukket ind i et bord på et ark 1. Når dataene ændres på et ark 2, ændres de automatisk på den første.

Metode 2: Brug af blunderen af ​​operatørindeks - Søg

Men hvad skal man gøre, hvis listen over medarbejdere i bordopgaver ikke er placeret i samme rækkefølge? I dette tilfælde, som tidligere nævnt, er en af ​​mulighederne at installere forholdet mellem hver af de celler, der skal tilhindes manuelt. Men det er egnet bortset fra små borde. For massive intervaller vil denne mulighed i bedste fald tage meget tid på implementeringen, og i værste fald - i praksis vil det generelt være urealistisk. Men dette problem kan løses ved hjælp af en flok operatørindeks - søgning. Lad os se, hvordan det kan gøres ved at tillade dataene i de tabeller, som samtalen var i den forrige metode.

1. Vi fremhæver det første element i kolonnen "Bet". Gå til guiden Funktioner ved at klikke på ikonet "Indsæt funktion".



2. I guiden af ​​funktioner i gruppen "links og arrays" finder vi og tildele navnet "Index".



3. Denne operatør har to former: en formular til at arbejde med arrays og reference. I vores tilfælde kræves den første mulighed, så i det næste formularvalgsvindue, der åbnes, skal du vælge det og klikke på knappen "OK".



4. Operatørens argumenter indeks begynder at køre. Opgaven med den angivne funktion er udgangen af ​​værdien i det valgte område i linjen med det angivne nummer. General Formula Operator Index Sådanne:

   = Indeks (array; nummer_name; [nummer_stolbits])

   "Array" er et argument, der indeholder rækkevidden af ​​det område, hvorfra vi udvinder oplysninger med nummeret på den angivne række.

   "Rownummer" er et argument, der er antallet af denne linje. Det er vigtigt at vide, at linjenummeret skal specificeres ikke i forhold til hele dokumentet, men kun i forhold til det tildelte array.

   "Nummeret af kolonnen" er et argument, der er valgfrit. For at løse det specifikt af vores opgave vil vi ikke bruge det, og derfor er det ikke nødvendigt at beskrive det særskilt.

   Vi sætter markøren i feltet "Array". Derefter skal du gå til arket 2 og holde venstre museknap, vælg hele indholdet i kolonnen "Rate".



5. Når koordinaterne vises i operatørens vindue, sætter vi markøren i feltet "ROW NUMBER". Vi trækker dette argument tilbage ved hjælp af søgeoperatøren. Klik derfor på en trekant, der er placeret til venstre for funktionsstrengen. En liste over nyankendte operatører åbnes. Hvis du finder navnet "Søgfirmaet" blandt dem, kan du klikke på den. I det modsatte tilfælde skal du klikke på det seneste punkt på listen - "Andre funktioner ...".



6. Standard vinduesguiden vinduet starter. Gå til det i samme gruppe "links og arrays". Denne gang på listen skal du vælge varen "Søgfirmaet". Udfør et klik på "OK" -knappen.



7. Aktivering af argumenterne for ansættelsesoperatørens argumenter udføres. Den angivne funktion er designet til at udskrive værdalnummeret i et bestemt array ved navn. Det er takket være denne funktion, at vi beregner antallet af en streng af en bestemt værdi for funktionsfunktionen. Syntaksen af ​​søgebrættet præsenteres:

   = Search Board (Search_Name; Viewing__Nassive; [TYPE_STATION])

   "Det ønskede" er et argument, der indeholder navnet eller adressen på cellen i tredjepartsintervallet, hvor den er placeret. Det er placeringen af ​​dette navn i målområdet og bør beregnes. I vores tilfælde vil det første arguments rolle blive henvist til celler på et ark 1, hvor medarbejderne er placeret.

   "Vistende array" er et argument, som er en henvisning til en matrix, som udfører søgningen efter den angivne værdi for at bestemme dens position. Vi vil få denne rolle til at udføre adressen på kolonnen "Navn" på et ark 2.

   "Type sammenligning" - et argument, der er valgfrit, men i modsætning til den forrige operatør, vil dette valgfrie argument være nødvendigt. Det angiver, hvordan man matcher operatøren, er den ønskede værdi med en matrix. Dette argument kan have en af ​​tre værdier: -1; 0; 1. For uordnede arrays skal du vælge indstillingen "0". Denne mulighed er egnet til vores sag.

   Så fortsæt med at udfylde felterne Argument. Vi sætter markøren i feltet "Fogular Value", klik på kolonnen First Cell "på et ark 1.



8. Når koordinaterne vises, skal du indstille markøren i feltet "Listing Massive" og gå til "Sheet 2" -mærket, som er placeret nederst i Excel-vinduet over statuslinjen. Clement venstre museknap og fremhæv markøren alle celler i kolonnen "Navn".



9. Når deres koordinater vises i feltet "Listing Massive", skal du gå til feltet "Mapping type" og indstille nummeret "0" fra tastaturet. Derefter vender vi igen tilbage til feltet "Kigger gennem arrayet". Faktum er, at vi vil udføre kopiering af formlen, som vi gjorde i den foregående metode. Der vil være et skift af adresser, men her kræver koordinaterne for den array, vi har brug for at sikre. Han burde ikke skifte. Vi fremhæver koordinaterne med markøren og klikker på F4-funktionstasten. Som du kan se, dukkede dollarskiltet før koordinaterne, hvilket betyder, at reference fra relativet blev til absolutte. Klik derefter på knappen "OK".



10. Resultatet vises i den første celle i kolonnen "Bet". Men før du kopierer, skal vi rette et andet område, nemlig det første argumentfunktionsindeks. For at gøre dette skal du vælge kolonneelementet, som indeholder en formel, og flyt til formlen streng. Fordele det første argument for operatørindekset (B2: B7) og klik på F4-knappen. Som du kan se, dukkede dollarskiltet i nærheden af ​​de valgte koordinater. Klik på Enter-tasten. Generelt tog formlen følgende formular:

    = Indeks (Sheet2! $ B $ 2: $ B $ 7; Search Board (Sheet1! A4; List2! $ A $ 2: $ A $ 7; 0))



11. Nu kan du kopiere ved hjælp af en påfyldningsmarkør. Vi kalder det på samme måde som vi har talt tidligere og strækker sig til slutningen af ​​det tabulære område.



12. Som du kan se, på trods af at rækkefølgen af ​​strenge i to relaterede tabeller ikke falder sammen, er alle værdier strammet i overensstemmelse med navnene på arbejdstagere. Dette blev opnået takket være brugen af ​​kombinationen af ​​operatører indeks søgning.

Metode 4: Specialindsats

TIE Table Arrays i Excel kan også bruge en særlig indsættelse.

1. Vælg de værdier, du vil "stramme" til et andet bord. I vores tilfælde er dette "BET" -kolonneområdet på et ark 2. Klik på det dedikerede fragment med højre museknap. I listen, der åbnes, skal du vælge "Kopier" -genstanden. En alternativ kombination er CTRL + C-tasten kombination. Derefter flytter vi til arket 1.



2. Flytning til området af den bog, du har brug for, tildele de celler, hvor værdierne skal skærpes. I vores tilfælde er dette "BID" -kolonnen. Klik på det dedikerede fragment med højre museknap. I kontaktmenuen i værktøjslinjen "Indsæt parametre" skal du klikke på ikonet "Indsæt kommunikation".

   

   Der er også et alternativ. Han er forresten den eneste for ældre versioner af Excel. I kontekstmenuen bringer vi markøren til "Special Insert" -genstanden. I den ekstra menu, der åbnes, skal du vælge den position med samme navn.



3. Derefter åbnes et specielt indsatsvindue. Klik på knappen "Indsæt kommunikation" i det nederste venstre hjørne af cellen.



4. Uanset hvilken mulighed du vælger, vil værdier fra et bord array blive indsat i en anden. Når du ændrer data i kilden, ændres de også automatisk i det indsatte område.

Lektion: Specialindsats i Excel

Metode 5: Kommunikation mellem tabeller i flere bøger

Derudover kan du organisere et link mellem bordområderne i forskellige bøger. Dette bruger et specielt indsatsværktøj. Handlinger vil være absolut ligner dem, vi overvejede i den foregående metode, bortset fra at navigationen under formlerne ikke vil have mellem de områder af en bog, men mellem filerne. Naturligvis skal alle relaterede bøger åbnes.

1. Vælg det datainterval, der skal overføres til en anden bog. Klik på den højre museknap og vælg "Kopier" -positionen i menuen Åbnet.



2. Så flytter vi til den bog, hvor disse data skal indsættes. Vælg det ønskede interval. Klik på højre museknap. I kontekstmenuen i gruppen "Indsæt indstillinger", skal du vælge "Insert Communication" -genstanden.



3. Derefter vil værdierne blive indsat. Når du ændrer data i kildebogen, vil en tabel array fra arbejdsbogen stramme dem automatisk. Og det er slet ikke nødvendigt at sikre, at begge bøger er åbne. Det er nok at åbne en eneste projektmappe, og det vil automatisk skræddersy data fra et lukket relateret dokument, hvis der var tidligere ændringer i det.

Men det skal bemærkes, at indsatsen i dette tilfælde vil blive produceret i form af et uændret array. Når du forsøger at ændre nogen celle med de indsatte data, vil en besked blive befolket informere om manglende evne til at gøre dette.

Ændringer i en sådan art, der er forbundet med en anden bog, kan kun bryde forbindelsen.

Titelbrud mellem tabeller

Nogle gange er det nødvendigt at bryde forbindelsen mellem tabellerne. Årsagen til dette kan være det ovenfor beskrevne, når du vil ændre en array indsat fra en anden bog og blot brugerens modvilje, så dataene i samme tabel automatisk opdateres fra den anden.

Metode 1: Kommunikation bryder mellem bøger

At bryde forbindelsen mellem bøger i alle celler ved at udføre faktisk en operation. I dette tilfælde forbliver dataene i cellerne, men de vil allerede være statiske ikke opdaterede værdier, der ikke afhænger af andre dokumenter.

1. I den bog, hvor værdier fra andre filer strammes, skal du gå til fanen Data. Klik på ikonet "Skift links", som er placeret på båndet i værktøjslinjen "Connection". Det skal bemærkes, at hvis den nuværende bog ikke indeholder forbindelser med andre filer, så er denne knap inaktiv.



2. Vinduet Link Change lanceres. Vælg fra listen over relaterede bøger (hvis der er flere af dem) den fil, som vi ønsker at bryde forbindelsen på. Klik på knappen "Break the Connection".



3. Et informationsvindue åbnes, hvilket giver en advarsel om konsekvenserne af yderligere handlinger. Hvis du er sikker på, at du skal gøre, skal du klikke på knappen "Break Communication".



4. Derefter erstattes alle referencer til den angivne fil i det aktuelle dokument med statiske værdier.

Metode 2: Indsætningsværdier

Men ovennævnte metode er kun egnet, hvis du har brug for helt at bryde alle sammenkoblingerne mellem de to bøger. Hvad hvis du har brug for at afbryde de tilknyttede tabeller inden for samme fil? Du kan gøre dette ved at kopiere dataene, og derefter indsætte det samme sted som værdier. Forresten kan denne metode sprænges mellem de enkelte dataområder af forskellige bøger uden at bryde et fælles forhold mellem filer. Lad os se, hvordan denne metode fungerer i praksis.

1. Vi fremhæver det område, hvor vi ønsker at slette kommunikationen med et andet bord. Klik på det højre museknap. I menuen Åbn skal du vælge "Copy" -genstanden. I stedet for de specificerede handlinger kan du ringe til en alternativ kombination af hot keys Ctrl + C.



2. Næste, uden at fjerne markeringen fra det samme fragment, skal du igen klikke på det med højre museknap. Denne gang i listen over handling, klik på ikonet "Value", som er bogført i indsæt parametre-gruppen.



3. Derefter vil alle referencer i det dedikerede område blive erstattet med statiske værdier.

Som du kan se, har Excel måder og værktøjer til at forbinde flere tabeller indbyrdes. Samtidig kan tabeldata være på andre lagner og endda i forskellige bøger. Om nødvendigt kan denne forbindelse nemt blive brudt.