Vid utförande av vissa uppgifter i Excel måste ibland hantera flera tabeller som också är relaterade till varandra. Det vill säga att data från ett bord är åtdragen till andra och värdena i alla relaterade tabeller omräknas när de ändras.
Relaterade tabeller är mycket lämpliga att använda för att hantera en stor mängd information. Placera all information i ett bord, förutom, om det inte är homogent, inte särskilt bekvämt. Det är svårt att arbeta med sådana föremål och att söka efter dem. Det angivna problemet är bara utformat för att eliminera relaterade tabeller, informationen mellan vilken distribueras, men samtidigt är sammankopplad. Relaterade tabeller kan inte bara vara inom ett ark eller en bok, men också att vara placerad i separata böcker (filer). De sista två alternativen i praktiken används oftast, eftersom syftet med denna teknik bara är att komma ifrån ackumulering av data, och deras nickning på en sida löser inte en fundamentalt. Låt oss lära dig hur man skapar och hur man arbetar med en sådan typ av datahantering.
Skapa relaterade tabeller
Först och främst, låt oss fokusera på frågan, på vilket sätt det är möjligt att skapa en koppling mellan olika tabeller.Metod 1: Direktbindningsbordsformel
Det enklaste sättet att binda data är användningen av formler där det finns referenser till andra tabeller. Det kallas direktbindning. Denna metod är intuitiv, eftersom när den binder det nästan bara som att skapa referenser till data i en tabellmatris.
Låt oss se hur exemplet du kan bilda kommunikation genom direktbindning. Vi har två tabeller på två ark. På samma bord beräknas lönen med formeln genom att multiplicera medarbetarnas priser för en enda koefficient.
På det andra arket finns ett bordsortiment där det finns en lista över anställda med sin lön. Listan över anställda i båda fallen presenteras i en order.
Det är nödvändigt att göra den data på satsningarna från det andra arket åtminstone i motsvarande celler i den första.
- På det första arket allokerar vi den första cellen i kolumnen "Bet". Vi lägger in det tecknet "=". Klicka sedan på "ark 2" -etiketten, som placeras på den vänstra delen av Excel-gränssnittet över statusfältet.
- Det finns en rörelse i det andra området i dokumentet. Klicka på den första cellen i kolumnen "Bet". Klicka sedan på ENTER-knappen på tangentbordet för att mata in data i den cell där "lika" tecknet tidigare installerats.
- Då finns det en automatisk övergång till det första arket. Som vi kan se dras värdet av den första medarbetaren från det andra bordet till motsvarande cell. Genom att installera markören på en cell som innehåller en insats ser vi att den vanliga formeln används för att visa data på skärmen. Men framför cellens koordinater, varifrån data matas ut, finns det ett uttryck "list2!", Vilket indikerar namnet på det dokumentområde där de är belägna. Den allmänna formeln i vårt fall ser ut så här:
= List2! B2
- Nu behöver du överföra data om alla andra anställda i företaget. Naturligtvis kan detta ske på samma sätt som vi uppfyllde uppgiften för den första medarbetaren, men med tanke på att båda medarbetarlistorna är belägna i samma ordning, kan uppgiften vara betydligt förenklad och accelererad av sitt beslut. Detta kan göras genom att helt enkelt kopiera formeln till intervallet nedan. På grund av det faktum att referenserna till Excel är relativt, när du kopierar sina värden, flyttas värdesskiftet som vi behöver. Kopieringsförfarandet i sig kan göras med hjälp av en fyllnadsmarkör.
Så sätter vi markören till det nedre högra delen av elementet med formeln. Därefter måste markören konvertera till fyllnadsmarkören i form av ett svart kors. Vi utför klämman på vänster musknapp och dra markören till kolumnens nummer.
- Alla data från en liknande kolumn på ett ark 2 drogs in i ett bord på ett ark 1. När data ändras på ett ark 2, ändras de automatiskt den första.
Metod 2: Använda blundring av operatörsindex - Sök
Men vad ska man göra om listan över anställda i bordsarrayer inte finns i samma ordning? I det här fallet, som tidigare nämnts, är ett av alternativen att installera förhållandet mellan var och en av de celler som ska associeras manuellt. Men det är lämpligt förutom små bord. För massiva intervall kommer det här alternativet att i bästa fall ta mycket tid på genomförandet, och i värsta fall kommer det i allmänhet att vara orealistiskt. Men det här problemet kan lösas med hjälp av en massa operatörsindex - sök. Låt oss se hur det kan göras genom att tillföra data i tabellerna om vilka konversationen var i föregående metod.
- Vi markerar det första elementet i kolumnen "Bet". Gå till funktionsguiden genom att klicka på ikonen "Infoga funktion".
- I guiden av funktioner i gruppen "Länkar och arrays" hittar vi och allokerar namnet "Index".
- Denna operatör har två former: en form för att arbeta med arrays och referens. I vårt fall krävs det första alternativet, så i fönstret Nästa formulär som öppnas, välj det och klicka på "OK" -knappen.
- Operatörens argumentindex börjar springa. Uppgiften för den angivna funktionen är utgången på det värde som ligger i det valda området i linjen med det angivna numret. Allmänna Formula Operator Index, sådan:
= Index (array; number_name; [number_stolbits])
"Array" är ett argument som innehåller intervallet av det intervall som vi kommer att extrahera information med numret på den angivna raden.
"Radnummer" är ett argument som är numret på den här raden. Det är viktigt att veta att linjenummeret ska anges inte i förhållande till hela dokumentet, men endast i förhållande till den tilldelade arrayen.
Numret på kolumnen "är ett argument som är valfritt. För att lösa den specifikt av vår uppgift, kommer vi inte att använda det, och därför är det inte nödvändigt att beskriva det separat.
Vi sätter markören i fältet "Array". Därefter, gå till arket 2 och, med vänster musknapp, välj hela innehållet i kolumnen "Rate".
- Efter att koordinaterna visas i operatörens fönster sätter vi markören i fältet "radnummer". Vi kommer att dra tillbaka detta argument med sökoperatören. Därför klickar du på en triangel som ligger till vänster om funktionsträngen. En lista över nyanvända operatörer öppnas. Om du hittar namnet "Sökbolag" bland dem, kan du klicka på den. I motsatt fall klickar du på den senaste punkten i listan - "Övriga funktioner ...".
- Standard Window Wizard Window startar. Gå till det i samma grupp "länkar och arrays". Den här gången i listan väljer du objektet "Sökföretag". Utför ett klick på "OK" -knappen.
- Aktivering av argumenten i sökoperatörens argument utförs. Den angivna funktionen är utformad för att mata ut värdetumret i en specifik matris med namnet. Det är tack vare den här funktionen att vi beräknar antalet en sträng av ett visst värde för funktionsfunktionen. Syntaxen för sökkortet presenteras:
= Sökbordet (Search_Name; visning__nassiv; [type_station])
"Det önskade" är ett argument som innehåller namnet eller adressen till cellen i det tredje partsområdet där det är beläget. Det är stället för detta namn i målområdet och bör beräknas. I vårt fall kommer den första argumentets roll att refereras till celler på ett blad 1, där anställda är belägna.
"Listful array" är ett argument, vilket är en referens till en array, som utför sökandet efter det angivna värdet för att bestämma sin position. Vi kommer att få den här rollen att utföra adressen till kolumnen "Namn" på ett ark 2.
"Typ av jämförelse" - ett argument som är valfritt, men, till skillnad från den tidigare operatören, kommer detta valfria argument att behövas. Det indikerar hur man matchar operatören är det önskade värdet med en array. Detta argument kan ha ett av tre värden: -1; 0; 1. För oordnade arrays, välj alternativet "0". Det här alternativet är lämpligt för vårt fall.
Så fortsätt att fylla i fältfönstret för argument. Vi sätter markören i fältet "Fogular Value", klicka på kolumnen First Cell "Namn" på ett ark 1.
- När koordinaterna visas, sätt markören i fältet "Listning Massive" och gå till "ark 2" -etiketten, som ligger längst ner i Excel-fönstret ovanför statusfältet. CLEME den vänstra musknappen och markera markören alla celler i kolumnen "Namn".
- Efter att deras koordinater visas i fältet "Listing Massive", gå till fältet "Kartläggningstyp" och ställ in numret "0" från tangentbordet. Därefter återvänder vi igen till fältet "Titta igenom arrayen". Faktum är att vi kommer att utföra kopiering av formeln, som vi gjorde i föregående metod. Det kommer att bli ett skifte av adresser, men här visade koordinaterna för den array vi behöver säkra. Han borde inte flytta. Vi markerar koordinaterna med markören och klickar på F4-funktionsknappen. Som du kan se uppträdde dollartecknet före koordinaterna, vilket innebär att referens från släkting förvandlas till absolut. Klicka sedan på "OK" -knappen.
- Resultatet visas i den första cellen i kolumnen "Bet". Men innan du kopierar måste vi fixa ett annat område, nämligen det första argumentfunktionsindexet. För att göra detta, välj kolumnelementet, som innehåller en formel och flytta till formelsträngen. Allokera det första argumentet för operatörsindexet (B2: B7) och klicka på F4-knappen. Som du kan se uppträdde dollartecknet nära de valda koordinaterna. Klicka på Enter-tangenten. I allmänhet tog formeln följande form:
= Index (Sheet2! $ B $ 2: $ B $ 7; Sökbordet (Sheet1! A4; list2! $ A $ 2: $ a $ 7; 0))
- Nu kan du kopiera med en fyllnadsmarkör. Vi kallar det på samma sätt som vi har talat tidigare och sträcker sig till slutet av tabellområdet.
- Som du kan se, trots att ordningen av strängar i två relaterade tabeller inte sammanfaller, ändå är alla värden åtdragna enligt namnen på arbetstagare. Detta uppnåddes tack vare användningen av kombinationen av operatörsindexsökningen.
Metod 4: Särskild insats
Tie tabell arrays i Excel kan också använda en speciell införing.
- Välj de värden som du vill "Dra åt" till ett annat bord. I vårt fall är det här kolumnområdet "Bet" på ett ark 2. Klicka på det dedikerade fragmentet med höger musknapp. I listan som öppnas väljer du objektet "Kopiera". En alternativ kombination är CTRL + C-tangentkombinationen. Därefter flyttar vi till arket 1.
- Flytta till det område av den bok du behöver, allokera cellerna där värdena måste stramas. I vårt fall är det här "BID" -kolumnen. Klicka på det dedikerade fragmentet med höger musknapp. I snabbmenyn i verktygsfältet "Infoga parametrar" klickar du på ikonen "Infoga kommunikation".
Det finns också ett alternativ. Han är förresten den enda för äldre versioner av Excel. I snabbmenyn tar vi markören till "Special Insert" -posten. I den extra menyn som öppnas väljer du positionen med samma namn.
- Därefter öppnas ett speciellt inläggsfönster. Klicka på knappen "Infoga kommunikation" i det nedre vänstra hörnet av cellen.
- Oavsett vilket alternativ du väljer, kommer värden från en tabellmatris att sättas in i en annan. Vid byte av data i källan ändras de automatiskt i det infogade intervallet.
Lektion: Särskild insats i Excel
Metod 5: Kommunikation mellan tabeller i flera böcker
Dessutom kan du organisera en länk mellan tabellområdena i olika böcker. Detta använder ett speciellt insatsverktyg. Åtgärder kommer absolut att likna de som vi anser i föregående metod, förutom att navigeringen under formlerna inte kommer att ha mellan områdena i en bok, men mellan filerna. Naturligtvis bör alla relaterade böcker öppnas.
- Välj det dataintervall som ska överföras till en annan bok. Klicka på den högra musknappen och välj "Kopiera" -positionen i den öppnade menyn.
- Då flyttar vi till den bok där dessa data ska införas. Välj önskat intervall. Klicka på höger musknapp. I snabbmenyn i gruppen "Infoga inställningar" väljer du objektet "Infoga kommunikation".
- Därefter kommer värdena att införas. Vid byte av data i källboken kommer en tabellmatris från arbetsboken att dra åt dem automatiskt. Och det är inte alls nödvändigt för att båda böckerna är öppna. Det är nog att öppna en enda arbetsbok, och det kommer automatiskt att skatta data från ett slutet relaterat dokument om det fanns tidigare ändringar i den.
Men det bör noteras att insatsen i det här fallet kommer att produceras i form av ett oförändrat array. När du försöker ändra någon cell med den infogade data, kommer ett meddelande att fyllas i att informera om oförmågan att göra detta.
Förändringar i en sådan matris som är associerad med en annan bok kan bara bryta anslutningen.
Titelbrott mellan tabeller
Ibland är det nödvändigt att bryta förbindelsen mellan tabellerna. Anledningen till detta kan vara det ovan beskrivna när du vill ändra en array införd från en annan bok och helt enkelt användarens motvilja så att data i samma tabell uppdateras automatiskt från den andra.Metod 1: Kommunikation bryter mellan böcker
Att bryta sambandet mellan böcker i alla celler, genom att faktiskt utföra en operation. I det här fallet kommer data i cellerna att förbli, men de kommer redan att vara statiska inte uppdaterade värden som inte beror på andra dokument.
- I boken där värden från andra filer är åtdragen, gå till fliken Data. Klicka på ikonen "Ändra länkar", som ligger på tejpen i verktygsfältet "anslutning". Det bör noteras att om den aktuella boken inte innehåller anslutningar med andra filer, är den här knappen inaktiv.
- Länkändringsfönstret lanseras. Välj från listan över relaterade böcker (om det finns flera av dem) den fil som vi vill bryta förbindelsen. Klicka på knappen "Bryt anslutningen".
- Ett informationsfönster öppnas, vilket ger en varning om konsekvenserna av ytterligare åtgärder. Om du är säker på att du ska göra, klicka på knappen "Break Communication".
- Därefter kommer alla referenser till den angivna filen i det aktuella dokumentet att ersättas med statiska värden.
Metod 2: Infoga värden
Men ovanstående metod är endast lämplig om du behöver helt bryta alla länkar mellan de två böckerna. Vad händer om du behöver koppla bort de tillhörande tabellerna i samma fil? Du kan göra det genom att kopiera data och sedan sätta in samma plats som värden. Förresten kan den här metoden sprängas mellan de enskilda datakombinerna från olika böcker utan att bryta ett gemensamt förhållande mellan filer. Låt oss se hur den här metoden fungerar i praktiken.
- Vi markerar det intervall där vi vill ta bort kommunikation med ett annat bord. Klicka på den högra musknappen. I menyn Öppna väljer du objektet "Kopiera". Istället för de angivna åtgärderna kan du ringa en alternativ kombination av snabbtangenter Ctrl + C.
- Därefter, utan att ta bort valet från samma fragment, klicka igen på den med höger musknapp. Den här gången i listan över åtgärd, klicka på ikonen "Värde", som är postad i Infoga Parameters-gruppen.
- Därefter kommer alla referenser i det dedikerade intervallet att ersättas med statiska värden.
Som du kan se har Excel vägar och verktyg för att associera flera bord bland dem själva. Samtidigt kan tablar data vara på andra ark och även i olika böcker. Om det behövs kan den här anslutningen enkelt brytas.