Når du utfører visse oppgaver i Excel, må det noen ganger håndtere flere tabeller som også er relatert til hverandre. Det vil si at dataene fra ett bord strammes til andre, og verdiene i alle relaterte tabeller blir omregnet når de endres.
Beslektede tabeller er veldig praktiske å bruke til å håndtere en stor mengde informasjon. Plasser all informasjonen i ett bord, dessuten, hvis det ikke er homogent, ikke veldig praktisk. Det er vanskelig å jobbe med slike objekter og å søke etter dem. Det angitte problemet er bare designet for å eliminere relaterte tabeller, informasjonen mellom som distribueres, men samtidig er sammenkoblet. Beslektede tabeller kan ikke bare være i ett ark eller en bok, men også å være lokalisert i separate bøker (filer). De to siste alternativene i praksis brukes oftest, siden formålet med denne teknologien er bare å komme vekk fra akkumulering av data, og deres nikket på en side løser ikke en fundamentalt. La oss lære å lage og hvordan du skal jobbe med en slik type datahåndtering.
Skape relaterte tabeller
Først av alt, la oss fokusere på spørsmålet, på hvilken måte det er mulig å skape en forbindelse mellom ulike tabeller.Metode 1: Direkte bindende tabeller formel
Den enkleste måten å binde data på er bruken av formler der det er referanser til andre tabeller. Det kalles direkte bindende. Denne metoden er intuitiv, siden når den binder den nesten som å skape referanser til data i ett tabellarrangement.
La oss se hvordan eksemplet du kan danne kommunikasjon ved direkte binding. Vi har to bord på to ark. På samme tabell beregnes lønnen ved hjelp av formelen ved å multiplisere medarbeiderprisene for en enkelt koeffisient.
På det andre arket er det et bordområde der det er en liste over ansatte med lønnen. Listen over ansatte i begge tilfeller presenteres i en ordre.
Det er nødvendig å gjøre dataene på innsatsen fra det andre arket strammet inn i de tilsvarende cellene i den første.
- På det første arket allokerer vi den første cellen i kolonnen "Bet". Vi legger i tegnet "=". Deretter klikker du på "Ark 2" -merket, som er plassert på den venstre delen av Excel-grensesnittet over statuslinjen.
- Det er en bevegelse i det andre området av dokumentet. Klikk på den første cellen i kolonnen "Bet". Deretter klikker du på Enter-knappen på tastaturet for å angi dataene i cellen der "like" skiltet tidligere ble installert.
- Deretter er det en automatisk overgang til det første arket. Som vi kan se, blir verdien av den første medarbeider fra det andre bordet trukket inn i den tilsvarende cellen. Ved å installere markøren på en celle som inneholder en innsats, ser vi at den vanlige formelen brukes til å vise data på skjermen. Men foran koordinatene til cellen, hvor dataene er utgang, er det et uttrykk "List2!", Som angir navnet på dokumentområdet der de befinner seg. Den generelle formelen i vårt tilfelle ser slik ut:
= List2! B2
- Nå må du overføre data om alle andre ansatte i bedriften. Selvfølgelig kan dette gjøres på samme måte som vi oppfylte oppgaven for den første medarbeider, men vurderer at begge arbeidslister er plassert i samme rekkefølge, kan oppgaven bli betydelig forenklet og akselerert av sin beslutning. Dette kan gjøres ved ganske enkelt ved å kopiere formelen til området nedenfor. På grunn av det faktum at referansene til Excel er relative, når de kopierer verdiene, blir verdiene skiftet som vi trenger. Kopieringsprosedyren selv kan gjøres ved hjelp av en fyllingsmarkør.
Så, vi legger markøren til det nedre høyre området av elementet med formelen. Etter det må markøren konvertere til fyllingsmarkøren i form av et svartkors. Vi utfører klemmen på venstre museknapp og trekker markøren til nummeret på kolonnen.
- Alle data fra en lignende kolonne på et ark 2 ble trukket inn i et bord på et ark 1. Når dataene endres på et ark 2, endres de automatisk på den første.
Metode 2: Bruke blundering av operatørindeks - Søk
Men hva skal jeg gjøre hvis listen over ansatte i tabellarrayer ikke er plassert i samme rekkefølge? I dette tilfellet, som nevnt tidligere, er en av alternativene å installere forholdet mellom hver av de cellene som skal være tilhørende manuelt. Men det er egnet bortsett fra små bord. For massive områder vil dette alternativet i beste fall ta mye tid på implementeringen, og i verste fall vil det generelt være urealistisk. Men dette problemet kan løses ved hjelp av en haug med operatørindeks - søk. La oss se hvordan det kan gjøres ved å tilsette dataene i tabellene som samtalen var i den forrige metoden.
- Vi markerer det første elementet i kolonnen "Bet". Gå til funksjonene Funksjoner ved å klikke på ikonet "Sett inn funksjon".
- I veiviseren av funksjoner i gruppen "Lenker og Arrays" finner vi og tildeler navnet "Index".
- Denne operatøren har to former: et skjema for arbeid med arrays og referanse. I vårt tilfelle er det første alternativet nødvendig, så i vinduet neste form som åpnes, velg det og klikk på "OK" -knappen.
- Operatørens argumenterindeks begynner å løpe. Oppgaven med den angitte funksjonen er utgangen av verdien som ligger i det valgte området i linjen med det angitte nummeret. Generell formeloperatørindeks slik:
= Indeks (array; nummernavn; [nummer_stolbits])
"Array" er et argument som inneholder rekkevidden av området som vi vil trekke ut informasjon med nummeret på den angitte raden.
"Row nummer" er et argument som er nummeret på denne linjen. Det er viktig å vite at linjenummeret skal spesifiseres, ikke i forhold til hele dokumentet, men bare i forhold til den tildelte arrayet.
"Antallet av kolonnen" er et argument som er valgfritt. For å løse den spesifikt av vår oppgave, vil vi ikke bruke den, og derfor er det ikke nødvendig å beskrive det separat.
Vi setter markøren i "Array" -feltet. Etter det, gå til arket 2 og hold venstre museknapp, velg hele innholdet i kolonnen "Rate".
- Etter at koordinatene vises i operatørens vindu, legger vi markøren i feltet "Row Number". Vi vil trekke dette argumentet ved hjelp av søkeoperatøren. Derfor, klikk på en trekant som ligger til venstre for funksjonsstrengen. En liste over nyanserte operatører åpnes. Hvis du finner navnet "Søkefirmaet" blant dem, kan du klikke på den. I motsatt tilfelle klikker du på det siste punktet på listen - "Andre funksjoner ...".
- Veiviservinduet Standardvinduet starter. Gå til det i samme gruppe "linker og arrays". Denne gangen i listen, velg elementet "Søk Company". Utfør et klikk på "OK" -knappen.
- Aktivering av argumentene til argumentene i søkeoperatøren utføres. Den angitte funksjonen er utformet for å utføre verdienummeret i et bestemt utvalg av navnet. Det er takket være denne funksjonen at vi beregner nummeret på en rekke en bestemt verdi for funksjonsfunksjonen. Syntaxen til søket er presentert:
= Søkstavle (search_name; Viewing__Nassive; [Type_station])
"Det ønskede" er et argument som inneholder navnet eller adressen til cellen i tredjepartsområdet der den er plassert. Det er posisjonen til dette navnet i målområdet og bør beregnes. I vårt tilfelle vil rollen som det første argumentet bli referert til celler på et ark 1, hvor ansatte er lokalisert.
"Listful Array" er et argument, som er en referanse til en matrise, som utfører søket etter den angitte verdien for å bestemme sin posisjon. Vi vil ha denne rollen å utføre adressen til "Navn" -kolonnen på et ark 2.
"Type sammenligning" - Et argument som er valgfritt, men i motsetning til den forrige operatøren, vil dette valgfrie argumentet være nødvendig. Det indikerer hvordan du samsvarer med operatøren er ønsket verdi med en matrise. Dette argumentet kan ha en av tre verdier: -1; 0; 1. For uorganiserte arrays, velg alternativet "0". Dette alternativet er egnet for vårt tilfelle.
Så fortsett å fylle argumentvinduene. Vi setter markøren i feltet "fogulær verdi", klikk på den første cellen "Navn" -kolonnen på et ark 1.
- Etter at koordinatene vises, må du angi markøren i feltet "Listing Massive" og gå til "Ark 2" -merket, som er plassert nederst i Excel-vinduet over statuslinjen. Clement den venstre museknappen og merk markøren alle celler i kolonnen "Navn".
- Etter at koordinatene vises i feltet "Listing massive", går du til feltet "Mapping type" og sett nummeret "0" fra tastaturet. Etter det kommer vi igjen tilbake til feltet "ser gjennom arrayet". Faktum er at vi skal utføre å kopiere formelen, som vi gjorde i forrige metode. Det vil være et skifte av adresser, men her ser koordinatene til matrisen vi må sikre seg. Han burde ikke skifte. Vi markerer koordinatene med markøren og klikker på F4-funksjonstasten. Som du kan se, dukket opp dollarskiltet før koordinatene, noe som betyr at referanse fra relativ ble til absolutt. Klikk deretter på "OK" -knappen.
- Resultatet vises i den første cellen i kolonnen "Bet". Men før du kopierer, må vi fikse et annet område, nemlig den første argumentfunksjonsindeksen. For å gjøre dette, velg kolonneelementet, som inneholder en formel, og flytt til formelstrengen. Allokere det første argumentet til operatørindeksen (B2: B7) og klikk på F4-knappen. Som du kan se, dukket opp dollarskiltet nær de valgte koordinatene. Klikk på Enter-tasten. Generelt tok formelen følgende skjema:
= Indeks (ark2! $ B $ 2: $ B $ 7; Søkstavle (ark1! A4; List2! $ A $ 2: $ A $ 7; 0))
- Nå kan du kopiere ved hjelp av en fyllingsmarkør. Vi kaller det på samme måte som vi har snakket tidligere, og strekker seg til slutten av tabellområdet.
- Som du kan se, til tross for at rekkefølgen av strenger i to relaterte tabeller ikke faller sammen, men alle verdier strammes i henhold til navnene på arbeidstakere. Dette ble oppnådd takket være bruken av kombinasjonen av operatørindekssøk.
Metode 4: Spesialinnsats
Tie tabell arrays i Excel kan også bruke en spesiell innføring.
- Velg verdiene du vil "stramme" til et annet bord. I vårt tilfelle er dette "Bet" -kolonneområdet på et ark 2. Klikk på det dedikerte fragmentet med høyre museknapp. I listen som åpnes, velg "Kopier" -elementet. En alternativ kombinasjon er Ctrl + C-tastekombinasjonen. Etter det flytter vi til arket 1.
- Flytter til området i boken du trenger, allokere cellene der verdiene må strammes. I vårt tilfelle er dette "BID" -kolonnen. Klikk på det dedikerte fragmentet med høyre museknapp. I kontekstmenyen i verktøylinjen "Sett inn parametere", klikk på ikonet "Sett inn kommunikasjon".
Det er også et alternativ. Han, forresten, er den eneste for eldre versjoner av Excel. I kontekstmenyen tar vi markøren til "Special Setting" -elementet. I den ekstra menyen som åpnes, velg posisjonen med samme navn.
- Etter det åpnes et spesialinnsatsvindu. Klikk på "Sett inn kommunikasjon" -knappen i nedre venstre hjørne av cellen.
- Uansett hvilket alternativ du velger, vil verdier fra ett tabell-array bli satt inn i en annen. Når de endrer data i kilden, endres de også automatisk i det innsatte området.
Leksjon: Spesialinnsats i Excel
Metode 5: Kommunikasjon mellom tabeller i flere bøker
I tillegg kan du organisere en kobling mellom bordområdene i forskjellige bøker. Dette bruker et spesialinnsatsverktøy. Handlingene vil være helt lik de som vi vurderte i forrige metode, bortsett fra at navigasjonen under formlene ikke vil ha mellom områdene i en bok, men mellom filene. Naturligvis bør alle relaterte bøker åpnes.
- Velg datasområdet som skal overføres til en annen bok. Klikk på høyre museknapp og velg "Kopier" -posisjonen i den åpne menyen.
- Deretter flytter vi til boken der disse dataene skal settes inn. Velg ønsket område. Klikk på høyre museknapp. I kontekstmenyen i "Sett innstillinger" -gruppen velger du "Sett inn kommunikasjonen".
- Etter det vil verdiene bli satt inn. Når du endrer data i kildeboken, vil en tabellruppe fra arbeidsboken stramme dem automatisk. Og det er ikke nødvendig for å sikre at begge bøkene er åpne. Det er nok å åpne en eneste arbeidsbok, og den vil automatisk tailes data fra et lukket relatert dokument hvis det var tidligere endringer i det.
Men det skal bemerkes at innsamlingen i dette tilfellet skal bli produsert i form av et uendret utvalg. Når du prøver å endre en hvilken som helst celle med de innsatte dataene, vil en melding bli befolket å informere om manglende evne til å gjøre dette.
Endringer i en slik matriss som er knyttet til en annen bok, kan bare bryte forbindelsen.
Tittel bryter mellom tabeller
Noen ganger er det nødvendig å bryte forbindelsen mellom tabellene. Årsaken til dette kan være det ovenfor beskrevne når du vil endre en matrise satt inn fra en annen bok og bare brukerens motvilje, slik at dataene i samme tabell automatisk oppdateres fra den andre.Metode 1: Kommunikasjonsbryter mellom bøker
For å bryte forbindelsen mellom bøker i alle celler, ved å utføre faktisk en operasjon. I dette tilfellet vil dataene i cellene forbli, men de vil allerede være statiske ikke oppdaterte verdier som ikke er avhengige av andre dokumenter.
- I boken i hvilke verdier fra andre filer er strammet, gå til kategorien Data. Klikk på ikonet "Endre lenker", som er plassert på båndet i verktøylinjen "Tilkobling". Det skal bemerkes at hvis den nåværende boken ikke inneholder tilkoblinger med andre filer, er denne knappen inaktiv.
- Link Endre vinduet lanseres. Velg fra listen over relaterte bøker (hvis det er flere av dem) filen som vi ønsker å bryte tilkoblingen. Klikk på knappen "Bryt forbindelsen".
- Et informasjonsvindu åpnes, som gir en advarsel om konsekvensene av ytterligere handlinger. Hvis du er sikker på at du skal gjøre, klikker du på "Break Communication" -knappen.
- Etter det vil alle referanser til den angitte filen i det nåværende dokumentet bli erstattet med statiske verdier.
Metode 2: Sette inn verdier
Men ovenstående metode er egnet bare hvis du trenger å helt bryte alle koblingene mellom de to bøkene. Hva om du trenger å koble fra de tilknyttede tabellene i samme fil? Du kan gjøre dette ved å kopiere dataene, og deretter sette inn det samme stedet som verdier. Forresten kan denne metoden brytes mellom de enkelte datatrådene i forskjellige bøker uten å bryte et felles forhold mellom filer. La oss se hvordan denne metoden fungerer i praksis.
- Vi fremhever området der vi ønsker å slette kommunikasjon med et annet bord. Klikk på den høyre museknappen. I den åpne menyen velger du "Kopier" -elementet. I stedet for de angitte handlingene kan du ringe en alternativ kombinasjon av hurtigtaster Ctrl + C.
- Deretter, uten å fjerne valget fra det samme fragmentet, klikker du igjen på den med høyre museknapp. Denne gangen i handlekursen, klikk på "Verdi" -ikonet, som er lagt ut i Sett inn Parameters-gruppen.
- Deretter vil alle referanser i det dedikerte området bli erstattet med statiske verdier.
Som du kan se, har Excel måter og verktøy for å knytte flere bord blant seg selv. Samtidig kan tabelldata være på andre ark og til og med i forskjellige bøker. Om nødvendig kan denne tilkoblingen enkelt bli ødelagt.