Bij het uitvoeren van bepaalde taken in Excel moeten soms omgaan met verschillende tabellen die ook met elkaar gerelateerd zijn. Dat wil zeggen, de gegevens van de ene tabel worden aangedraaid met anderen en de waarden in alle gerelateerde tabellen worden opnieuw berekend wanneer ze worden gewijzigd.
Verwante tabellen zijn erg handig om te gebruiken om een grote hoeveelheid informatie aan te pakken. Plaats alle informatie in één tabel, behalve, als het niet homogeen is, niet erg handig. Het is moeilijk om met dergelijke objecten te werken en naar ze te zoeken. Het opgegeven probleem is net ontworpen om gerelateerde tabellen te elimineren, de informatie tussen die wordt verdeeld, maar tegelijkertijd is onderling verbonden. Verwante tafels kunnen niet alleen binnen één blad of één boek zijn, maar ook in afzonderlijke boeken (bestanden). De laatste twee opties in de praktijk worden meestal gebruikt, omdat het doel van deze technologie gewoon is om weg te komen van de accumulatie van gegevens, en hun knikken op één pagina niet fundamenteel oplost. Laten we leren te maken en hoe je met zo'n type gegevensbeheer kunt werken.
Verwante tafels maken
Laten we eerst ons concentreren op de vraag, op welke manier het mogelijk is om een verbinding tussen verschillende tabellen te creëren.Methode 1: Directe bindingstabellen formule
De eenvoudigste manier om gegevens te binden is het gebruik van formules waarin er verwijzingen naar andere tafels zijn. Het wordt directe binding genoemd. Deze methode is intuïtief, omdat het bijna net als het creëren van verwijzingen naar gegevens in één tabelarray bindt.
Laten we eens kijken hoe het voorbeeld je communicatie kunt vormen door directe binding. We hebben twee tafels op twee vellen. Op dezelfde tabel wordt het salaris berekend met behulp van de formule door de werknemersnelheden voor een enkele coëfficiënt te vermenigvuldigen.
Op het tweede blad is er een tafelbereik waarin er een lijst met werknemers met hun salaris is. De lijst met werknemers in beide gevallen wordt in één bestelling gepresenteerd.
Het is noodzakelijk om die gegevens op de weddenschappen uit het tweede vel vast te zetten in de overeenkomstige cellen van de eerste.
- Op het eerste blad wijzen we de eerste cel van de kolom "BET" toe. We stoppen het het bord "=". Klik vervolgens op het label "Sheet 2", dat op het linkerdeel van de Excel-interface over de statusbalk wordt geplaatst.
- Er is een beweging in het tweede deel van het document. Klik op de eerste cel in de kolom "BET". Klik vervolgens op de knop Enter op het toetsenbord om de gegevens in de cel in te voeren waarin het teken "Gelijk" eerder is geïnstalleerd.
- Dan is er een automatische overgang naar het eerste blad. Zoals we kunnen zien, wordt de waarde van de eerste werknemer uit de tweede tabel in de overeenkomstige cel getrokken. Door de cursor op een cel te installeren die een gok bevat, zien we dat de gebruikelijke formule wordt gebruikt om gegevens op het scherm weer te geven. Maar voor de coördinaten van de cel, van waaruit de gegevens worden uitgevoerd, is er een uitdrukking "LIST2!", Die de naam van het documentgebied aangeeft waar ze zich bevinden. De algemene formule in onze zaak ziet er als volgt uit:
= LIST2! B2
- Nu moet u gegevens overdragen over de tarieven van alle andere medewerkers van de onderneming. Natuurlijk kan dit op dezelfde manier worden gedaan die we de taak voor de eerste werknemer vervullen, maar gezien het feit dat beide werknemerslijsten zich in dezelfde volgorde bevinden, kan de taak aanzienlijk worden vereenvoudigd en versneld door zijn beslissing. Dit kan worden gedaan door eenvoudigweg door de formule naar het onderstaande bereik te kopiëren. Vanwege het feit dat de verwijzingen naar Excel relatief zijn, worden de waarden verschuiving verschoven die we nodig hebben bij het kopiëren van hun waarden. De kopieerprocedure zelf kan worden gemaakt met behulp van een vulmarkering.
We plaatsen dus de cursor naar het rechterveld van het element met de formule. Daarna moet de cursor converteren naar de vulmarkering in de vorm van een zwart kruis. We voeren de klem van de linkermuisknop uit en trek de cursor naar het nummer van de kolom.
- Alle gegevens uit een vergelijkbare kolom op een vel 2 werden in een tabel op een vel getrokken 1. Wanneer de gegevens op een vel 2 wijzigt, zullen ze automatisch wijzigen op de eerste.
Methode 2: het gebruik van de index van de operatoren - Zoeken
Maar wat te doen als de lijst met werknemers in tabelarrays zich niet in dezelfde volgorde bevindt? In dit geval, zoals eerder vermeld, is een van de opties om de relatie tussen elk van die cellen die handmatig moet worden geassocieerd, te installeren. Maar het is geschikt, behalve voor kleine tafels. Voor enorme reeksen zal deze optie in het beste geval veel tijd nemen over de implementatie, en in het slechtste geval is het in het ergste onrealistisch. Maar dit probleem kan worden opgelost met behulp van een stel operatorsindex - zoek. Laten we eens kijken hoe het kan worden gedaan door de gegevens in de tafels te bewerken waarover het gesprek in de vorige methode was.
- We markeren het eerste element van de kolom "BET". Ga naar de wizard Functies door op het pictogram "Functie-functie" te klikken.
- In de tovenaar van functies in de groep "Links en Arrays" vinden we de naam "Index".
- Deze operator heeft twee vormen: een formulier voor het werken met arrays en referentie. In ons geval is de eerste optie vereist, dus selecteert u in het venster Volgende formulier selecteren deze en klikt u op de knop "OK".
- De argumentenindex van de operator begint met werken. De taak van de opgegeven functie is de uitvoer van de waarde die zich in het geselecteerde bereik in de regel met het opgegeven nummer bevindt. Algemene Formule Operator-index zoals:
= Index (array; nummer_name; [nummer_stolbits])
De "array" is een argument met het bereik van het bereik waaruit wij informatie zullen extraheren op het nummer van de opgegeven rij.
"Rijnummer" is een argument dat het nummer van deze lijn is. Het is belangrijk om te weten dat het lijnnummer niet ten opzichte van het volledige document moet worden gespecificeerd, maar alleen ten opzichte van de toegewezen array.
Het "nummer van de kolom" is een argument dat optioneel is. Om het specifiek van onze taak op te lossen, zullen we het niet gebruiken en daarom is het niet nodig om het afzonderlijk te beschrijven.
We plaatsen de cursor in het veld "Array". Ga daarna naar het vel 2 en selecteer de linkermuisknop, selecteer de volledige inhoud van de kolom "Prijs".
- Nadat de coördinaten in het venster van de operator worden weergegeven, plaatsen we de cursor in het veld "Rij nummer". We zullen dit argument opnemen met behulp van de zoekoperator. Klik daarom op een driehoek die zich aan de linkerkant van de functiestring bevindt. Een lijst met nieuw gebruikte operators wordt geopend. Als u de naam "Search Company" onder hen vindt, kunt u erop klikken. Klik in het tegenovergestelde geval op het laatste punt van de lijst - "Andere functies ...".
- Standaard Wizard Wizard Start. Ga ernaar in dezelfde groep "Links and Arrays". Kies deze keer in de lijst het item "Search Company". Voer een klik op de knop "OK" uit.
- Activering van de argumenten van de argumenten van de zoekoperator wordt uitgevoerd. De opgegeven functie is ontworpen om het waardenummer in een specifieke array met zijn naam uit te voeren. Het is dankzij deze functie die we het nummer van een reeks van een specifieke waarde voor de functiefunctie berekenen. De syntaxis van het zoekbord wordt gepresenteerd:
= Zoekbord (search_name; kijk__nassief; [Type_station])
"De gewenste" is een argument met de naam of het adres van de cel van het bereik van derden waarin het zich bevindt. Het is de positie van deze naam in het doelbereik en moet worden berekend. In ons geval zal de rol van het eerste argument worden verwezen naar cellen op een vel 1, waarin werknemers zich bevinden.
"Listige array" is een argument, dat een verwijzing naar een array is, die de zoektocht naar de opgegeven waarde uitvoert om zijn positie te bepalen. We zullen deze rol hebben om het adres van de kolom "Naam" op een blad 2 uit te voeren.
"Type vergelijking" - een argument dat optioneel is, maar in tegenstelling tot de vorige operator, is dit optionele argument nodig. Het geeft aan hoe deze overeenkomt met de operator is de gewenste waarde met een array. Dit argument kan een van de drie waarden hebben: -1; 0; 1. Selecteer voor gewordende arrays de optie "0". Deze optie is geschikt voor onze zaak.
Ga dus verder met het vullen van de velden van het argumentvenster. We plaatsen de cursor in het veld "fogelwaarde", klik op de kolom van de eerste cel "NAAM" op een vel 1.
- Nadat de coördinaten worden weergegeven, stelt u de cursor in het veld "Mogelijk" en gaat u naar het label "Sheet 2", dat zich onderaan het Excel-venster bevindt boven de statusbalk. Clement de linkermuisknop en markeer de cursor alle cellen van de kolom "NAAM".
- Nadat hun coördinaten worden weergegeven in het veld "Massief", gaat u naar het veld "Type mapping" en stelt u het nummer in "0" in het toetsenbord. Daarna keren we opnieuw terug naar het veld "Doorkijken door de array". Het is een feit dat we het kopiëren van de formule zullen uitvoeren, zoals we deden in de vorige methode. Er zal een verschuiving van adressen zijn, maar hier moeten we de coördinaten van de bekeken array nodig hebben om te beveiligen. Hij zou niet moeten verschuiven. We markeren de coördinaten met de cursor en klikken op de F4-functietoets. Zoals je kunt zien, verscheen het dollarteken vóór de coördinaten, wat betekent dat referentie van familielid is veranderd in absoluut. Klik vervolgens op de knop "OK".
- Het resultaat wordt weergegeven in de eerste cel van de kolom "BET". Maar voordat ze kopiëren, moeten we een ander gebied oplossen, namelijk de eerste argumentfunctionele index. Om dit te doen, selecteert u het kolomelement, dat een formule bevat en naar de formule-string gaat. Wijs het eerste argument toe van de operatorindex (B2: B7) en klik op de knop F4. Zoals je kunt zien, verscheen het teken van de dollar in de buurt van de geselecteerde coördinaten. Klik op de toets Enter. In het algemeen nam de formule de volgende vorm:
= Index (Sheet2! $ B $ 2: $ B $ 7; Zoekbord (Sheet1! A4; Lijst2! $ A $ 2: $ A $ 7; 0))
- Nu kunt u kopiëren met behulp van een vulmarkering. We noemen het op dezelfde manier die we eerder hebben gesproken en strekken zich uit tot het einde van het tabelbaan.
- Zoals je kunt zien, ondanks het feit dat de volgorde van snaren in twee gerelateerde tabellen niet samenvallen, worden niettemin, alle waarden aangedraaid volgens de namen van werknemers. Dit werd bereikt dankzij het gebruik van de combinatie van operatorsindex-zoekopdracht.
Methode 4: Speciale inzetstuk
Tie Table Arrays in Excel kunnen ook een speciale insertie gebruiken.
- Selecteer de waarden die u wilt "Draai" naar een andere tabel. In ons geval is dit het kolom "BET" op een blad 2. Klik op het speciale fragment met de rechtermuisknop. Selecteer het item "Kopiëren" in de lijst die opent. Een alternatieve combinatie is de CTRL + C-toetscombinatie. Daarna gaan we naar het vel 1.
- Verhuizen naar het gebied van het boek dat u nodig hebt, wijst de cellen toe waarin de waarden moeten worden vastgedraaid. In ons geval is dit de kolom "Bid". Klik op het speciale fragment met de rechtermuisknop. Klik in het contextmenu in de werkbalk "PARAMETERS" op het pictogram "Communicatie invoegen".
Er is ook een alternatief. Hij is trouwens de enige voor oudere versies van Excel. In het contextmenu brengen we de cursor naar het item "Special Insert". Selecteer in het extra menu dat opent de positie met dezelfde naam.
- Daarna wordt een speciaal invoegvenster geopend. Klik op de knop "Communicatie invoegen" in de linkerbenedenhoek van de cel.
- Welke optie u ook kiest, waarden van één tabelarray worden ingevoegd in een andere. Bij het veranderen van gegevens in de bron, zullen ze ook automatisch in het ingevoegde bereik veranderen.
Les: Speciale insert in Excel
Methode 5: communicatie tussen tabellen in verschillende boeken
Bovendien kunt u een verband tussen de tabelgebieden in verschillende boeken organiseren. Dit gebruikt een speciaal inzetstuk. Acties zullen absoluut vergelijkbaar zijn met degenen die we in de vorige methode overwogen, behalve dat de navigatie tijdens de formules niet heeft tussen de gebieden van één boek, maar tussen de bestanden. Natuurlijk moeten alle gerelateerde boeken worden geopend.
- Selecteer het gegevensbereik dat moet worden overgedragen aan een ander boek. Klik op IT RECHTS MUIS-knop en selecteer de positie "Kopiëren" in het menu Geopende geopende.
- Dan gaan we naar het boek waarin deze gegevens moeten worden ingevoegd. Selecteer het gewenste bereik. Klik op de rechtermuisknop. Selecteer in het contextmenu in de groep "Instellingen invoegen" het item "Communicatie invoegen".
- Daarna worden de waarden ingevoegd. Bij het wijzigen van gegevens in het bronboek, wordt een tabelarray van het werkboek ze automatisch vastgedraaid. En het is helemaal niet nodig om ervoor te zorgen dat beide boeken open zijn. Het is genoeg om één alleen werkmap te openen en het automatisch afspelen van gegevens van een gesloten gerelateerd document als er eerdere wijzigingen erin waren.
Maar er moet worden opgemerkt dat de inzet in dit geval zal worden geproduceerd in de vorm van een onveranderde array. Wanneer u probeert elke cel te wijzigen met de ingevoegde gegevens, wordt een bericht ingevuld met het informeren van het onvermogen om dit te doen.
Wijzigingen in een dergelijke array die aan een ander boek is gekoppeld, kunnen de verbinding alleen maar breken.
Titel breekt tussen tabellen
Soms is het nodig om de verbinding tussen de tabellen te verbreken. De reden hiervoor kan het bovenstaande zijn beschreven wanneer u een array wilt wijzigen die is ingevoegd uit een ander boek en gewoon de terughoudendheid van de gebruiker, zodat de gegevens in dezelfde tabel automatisch van de andere worden bijgewerkt.Methode 1: Communicatie-pauzes tussen boeken
Om de verbinding tussen boeken in alle cellen te verbreken, door eigenlijk één werking uit te voeren. In dit geval zullen de gegevens in de cellen blijven, maar ze zijn al statische niet bijgewerkte waarden die niet afhankelijk zijn van andere documenten.
- In het boek in welke waarden van andere bestanden zijn vastgedraaid, ga dan naar het tabblad Data. Klik op het pictogram "Links wijzigen", dat zich op de tape bevindt in de werkbalk "Verbinding". Opgemerkt moet worden dat als het huidige boek geen verbindingen met andere bestanden bevat, dan is deze knop inactief.
- Het venster Link Wijzig wordt gelanceerd. Kies uit de lijst met gerelateerde boeken (als er meerdere van hen zijn) het bestand waarmee we de verbinding willen doorbreken. Klik op de knop "Break de verbinding".
- Een informatievenster wordt geopend, dat een waarschuwing biedt over de gevolgen van verdere acties. Als u er zeker van bent dat u het gaat doen, klikt u op de knop "Break Communicatie".
- Daarna worden alle verwijzingen naar het opgegeven bestand in het huidige document vervangen door statische waarden.
Methode 2: Waarden invoegen
Maar de bovenstaande methode is alleen geschikt als u alle links tussen de twee boeken volledig moet doorbreken. Wat als u de bijbehorende tabellen binnen hetzelfde bestand koppelt? U kunt dit doen door de gegevens te kopiëren en vervolgens dezelfde plaats als waarden in te voegen. Trouwens, deze methode kan worden verbluft tussen de individuele data-reeksen van verschillende boeken zonder een gemeenschappelijke relatie tussen bestanden te verbreken. Laten we eens kijken hoe deze methode in de praktijk werkt.
- We markeren het bereik waarin we communicatie met een andere tabel willen verwijderen. Klik op IT RECHTS MUISE-knop. Selecteer in het menu Open het item "Kopiëren". In plaats van de opgegeven acties kunt u een alternatieve combinatie van hete toetsen Ctrl + C kiezen.
- Vervolgens, zonder de selectie uit hetzelfde fragment te verwijderen, klikt u er opnieuw op met de rechtermuisknop. Deze keer in de lijst met actie, klikt u op het pictogram "Value", dat is geplaatst in de groepsparametersgroep.
- Daarna worden alle referenties in het toegewijde bereik vervangen door statische waarden.
Zoals je kunt zien, heeft Excel manieren en hulpmiddelen om verschillende tafels onderling te associëren. Tegelijkertijd kunnen tabulaire gegevens op andere vellen staan en zelfs in verschillende boeken. Indien nodig kan deze verbinding eenvoudig worden verbroken.