Verwante tabelle in Excel: Gedetailleerde instruksies

By die uitvoering van sekere take in Excel moet soms verskeie tabelle hanteer wat ook met mekaar verband hou. Dit is, die data van een tafel is aan ander strenger en die waardes in alle verwante tabelle word herbereken wanneer hulle verander word.

Verwante tabelle is baie gerieflik om te gebruik om 'n groot hoeveelheid inligting te hanteer. Plaas al die inligting in een tafel, behalwe as dit nie homogeen is nie, nie baie gerieflik nie. Dit is moeilik om met sulke voorwerpe te werk en om na hulle te soek. Die gespesifiseerde probleem is net ontwerp om verwante tabelle uit te skakel, die inligting tussen wat versprei word, maar terselfdertyd onderling verbind word. Verwante tabelle kan nie net binne een blad of een boek wees nie, maar ook in aparte boeke (lêers). Die laaste twee opsies in die praktyk word meestal gebruik, aangesien die doel van hierdie tegnologie net wegkom van die ophoping van data, en hulle knik op een bladsy op een bladsy nie 'n fundamenteel oplos nie. Kom ons leer hoe om te skep en hoe om met so 'n soort databestuur te werk.

Skep verwante tafels

Eerstens, laat ons fokus op die vraag, op watter manier is dit moontlik om 'n verband tussen verskillende tabelle te skep.

Metode 1: Direkte bindende tafelsformule

Die maklikste manier om data te bind, is die gebruik van formules waarin daar verwysings na ander tabelle is. Dit word direkte binding genoem. Hierdie metode is intuïtief, aangesien wanneer dit byna byna bind, net soos om verwysings na data in een tafelskikking te skep.

Kom ons kyk hoe die voorbeeld u kommunikasie kan vorm deur direkte binding. Ons het twee tafels op twee lakens. Op dieselfde tabel word die salaris bereken deur die formule te gebruik deur die werknemers se tariewe vir 'n enkele koëffisiënt te vermenigvuldig.

Op die tweede laag is daar 'n tabelreeks waarin daar 'n lys van werknemers met hul salaris is. Die lys van werknemers in albei gevalle word in een bestelling aangebied.

Dit is nodig om die data op die verbintenis van die tweede vel te maak, strenger die ooreenstemmende selle van die eerste.

1. Op die eerste blad ken ons die eerste sel van die "Bet" -kolom toe. Ons sit dit in die teken "=". Klik dan op die "Sheet 2" etiket, wat op die linker gedeelte van die Excel-koppelvlak oor die statusbalk geplaas word.



2. Daar is 'n beweging in die tweede area van die dokument. Klik op die eerste sel in die "weddenskap" kolom. Klik dan op die Enter-knoppie op die sleutelbord om die data in te voer in die sel waarin die "gelyke" teken voorheen geïnstalleer is.



3. Dan is daar 'n outomatiese oorgang na die eerste vel. Soos ons kan sien, word die waarde van die eerste werknemer van die tweede tabel in die ooreenstemmende sel getrek. Deur die wyser op 'n sel te installeer wat 'n weddenskap bevat, sien ons dat die gewone formule gebruik word om data op die skerm te vertoon. Maar voor die koördinate van die sel, vanwaar die data uitset is, is daar 'n uitdrukking "LYS2!", Wat die naam van die dokumentarea aandui waar hulle geleë is. Die algemene formule in ons geval lyk soos volg:

   = Lys2! B2



4. Nou moet jy data oordra oor die tariewe van alle ander werknemers van die onderneming. Dit kan natuurlik op dieselfde manier gedoen word dat ons die taak vir die eerste werknemer vervul het, maar aangesien beide werknemerlyste in dieselfde volgorde geleë is, kan die taak aansienlik vereenvoudig en versnel word deur sy besluit. Dit kan gedoen word deur bloot die formule op die onderstaande reeks te kopieer. As gevolg van die feit dat die verwysings na Excel relatief is, word die waardes verskuif wanneer hulle hul waardes kopieer, verskuif wat ons nodig het. Die kopieprosedure self kan gemaak word met behulp van 'n vuler.

   So, ons sit die wyser aan die onderste regterkantste area van die element met die formule. Daarna moet die wyser in die vorm van 'n swart kruis omskep word na die vuler. Ons voer die klem van die linker muisknop uit en trek die wyser na die nommer van die kolom.



5. Alle data van 'n soortgelyke kolom op 'n laken 2 is in 'n tafel op 'n laken getrek 1. Wanneer die data op 'n blad 2 verander, sal hulle outomaties verander op die eerste.

Metode 2: Gebruik die blundering van operateursindeks - Soek

Maar wat om te doen as die lys van werknemers in tabel skikkings nie in dieselfde volgorde geleë is nie? In hierdie geval, soos vroeër gesê, is een van die opsies om die verhouding tussen elk van die selle wat handmatig geassosieer moet word, te installeer. Maar dit is geskik, behalwe vir klein tafels. Vir massiewe reekse sal hierdie opsie op die beste tyd neem op die implementering, en in die ergste geval - in die praktyk sal dit oor die algemeen onrealisties wees. Maar hierdie probleem kan opgelos word met behulp van 'n klomp operateurs indeks - soek. Kom ons kyk hoe dit gedoen kan word deur die data in die tabelle te bewerkstellig waaroor die gesprek in die vorige metode was.

1. Ons beklemtoon die eerste element van die "weddenskap" kolom. Gaan na die Wizard Funksies deur op die "Insert Function" -ikoon te kliek.



2. In die towenaar van funksies in die groep "skakels en skikkings" vind en ken ons die naam "indeks" toe.



3. Hierdie operateur het twee vorme: 'n vorm vir die werk met skikkings en verwysing. In ons geval is die eerste opsie benodig, dus in die volgende vorm seleksie venster wat oopmaak, kies dit en klik op die "OK" knoppie.



4. Die operateur se argumente-indeks begin hardloop. Die taak van die vermelde funksie is die uitset van die waarde in die geselekteerde reeks in die lyn met die gespesifiseerde nommer. Algemene Formule-operateursindeks soos:

   = Indeks (skikking; nommer_name; [number_stolbits])

   Die "Array" is 'n argument wat die omvang van die omvang bevat waaruit ons inligting sal onttrek deur die nommer van die gespesifiseerde ry.

   "Ry nommer" is 'n argument wat die nommer van hierdie lyn is. Dit is belangrik om te weet dat die lynnommer nie gespesifiseer moet word nie relatief tot die hele dokument nie, maar slegs relatief tot die toegewysde skikking.

   Die "nommer van die kolom" is 'n argument wat opsioneel is. Om die spesifiek van ons taak op te los, sal ons dit nie gebruik nie, en daarom is dit nie nodig om dit afsonderlik te beskryf nie.

   Ons sit die wyser in die "Array" -veld. Daarna, gaan na die laken 2 en hou die linker muisknop, kies die hele inhoud van die "koers" kolom.



5. Nadat die koördinate in die operateur se venster vertoon word, sit ons die wyser in die veld "Ry nommer". Ons sal hierdie argument onttrek deur die soekoperateur te gebruik. Klik dus op 'n driehoek wat aan die linkerkant van die funksie string geleë is. 'N Lys van nuut gebruikte operateurs maak oop. As u die naam "soekmaatskappy" onder hulle vind, kan u daarop klik. Klik in die teenoorgestelde geval op die nuutste punt van die lys - "Ander funksies ...".



6. Standaard venster Wizard venster begin. Gaan dit in dieselfde groep "skakels en skikkings". Hierdie keer in die lys, kies die item "Soek Company". Doen 'n klik op die "OK" knoppie.



7. Aktivering van die argumente van die argumente van die soekoperateur word uitgevoer. Die gespesifiseerde funksie is ontwerp om die waarde nommer in 'n spesifieke skikking met sy naam uit te voer. Dit is te danke aan hierdie kenmerk dat ons die aantal 'n reeks van 'n spesifieke waarde vir die funksie funksie bereken. Die sintaksis van die soekbord word aangebied:

   = Soek Board (Search_Name; View__Nassive; [Type_station])

   "Die verlangde" is 'n argument wat die naam of adres van die sel van die derdeparty-reeks bevat waarin dit geleë is. Dit is die posisie van hierdie naam in die teikenreeks en moet bereken word. In ons geval sal die rol van die eerste argument na selle op 'n laken 1 verwys word, waarin werknemers geleë is.

   "Neeglike skikking" is 'n argument, wat 'n verwysing na 'n skikking is wat die soeke na die gespesifiseerde waarde verrig om sy posisie te bepaal. Ons sal hierdie rol hê om die adres van die "Naam" -kolom op 'n blad 2 uit te voer.

   "Soort vergelyking" - 'n argument wat opsioneel is, maar, in teenstelling met die vorige operateur, sal hierdie opsionele argument nodig wees. Dit dui aan hoe om die operateur te pas, is die verlangde waarde met 'n skikking. Hierdie argument kan een van drie waardes hê: -1; 0; 1. Kies die opsie "0" vir wanordelike skikkings. Hierdie opsie is geskik vir ons saak.

   So, gaan voort om die argument venstervelde te vul. Ons sit die wyser in die veld "Fogular waarde", klik op die eerste sel "Naam" -kolom op 'n blad 1.



8. Nadat die koördinate vertoon word, stel die wyser in die "notering massiewe" veld en gaan na die "Sheet 2" -tetiket, wat onderaan die Excel-venster bo die statusbalk geleë is. Clement die linker muisknop en beklemtoon die wyser alle selle van die "naam" kolom.



9. Nadat hul koördinate in die "notering massiewe" veld vertoon word, gaan na die "karteringstipe" -veld en stel die nommer "0" van die sleutelbord af. Daarna kom ons weer terug na die veld "kyk deur die skikking". Die feit is dat ons die formule sal kopieer, soos ons in die vorige metode gedoen het. Daar sal 'n verskuiwing van adresse wees, maar hier moet die koördinate van die skikking wat ons aangevra het, verseker. Hy moet nie verskuif nie. Ons beklemtoon die koördinate met die wyser en klik op die F4-funksie sleutel. Soos u kan sien, het die dollarbord voor die koördinate verskyn, wat beteken dat verwysing van familielid tot absolute verander het. Klik dan op die "OK" -knoppie.



10. Die resultaat word vertoon in die eerste sel van die "weddenskap" -kolom. Maar voordat ons kopieer, moet ons 'n ander gebied regmaak, naamlik die eerste argumentfunksie-indeks. Om dit te doen, kies die kolom element, wat 'n formule bevat, en beweeg na die formule string. Ken die eerste argument van die operateurindeks (B2: B7) toe en klik op die F4-knoppie. Soos u kan sien, het die dollarbord naby die gekose koördinate verskyn. Klik op die sleutel sleutel. Oor die algemeen het die formule die volgende vorm geneem:

    = Indeks (sheet2! $ B $ 2: $ B $ 7; Soekbord (Sheet1! A4; Lys2! $ A $ 2: $ A $ 7; 0))



11. Nou kan jy 'n vulmark kopieer. Ons noem dit op dieselfde manier as wat ons vroeër gepraat het, en strek tot die einde van die tabelreeks.



12. Soos u kan sien, ten spyte van die feit dat die volgorde van snare in twee verwante tabelle nie saamval nie, word alle waardes volgens die name van werkers verskerp. Dit is behaal, te danke aan die gebruik van die kombinasie van operateurs indeks soek.

Metode 4: Spesiale Invoegsel

Bind tabel skikkings in Excel kan ook 'n spesiale invoeging gebruik.

1. Kies die waardes wat u op 'n ander tabel wil "toeneem". In ons geval is dit die "weddenskap" -kolom op 'n blad 2. Klik op die toegewyde fragment met die regter muisknop. In die lys wat oopmaak, kies die "kopie" -item. 'N Alternatiewe kombinasie is die Ctrl + C-sleutel kombinasie. Daarna beweeg ons na die laken 1.



2. Om na die gebied van die boek te beweeg, moet u die selle toeken waarin die waardes toeneem moet word. In ons geval is dit die "bod" kolom. Klik op die toegewyde fragment met die regter muisknop. In die konteks kieslys in die "Insert Parameters" nutsbalk, klik op die "Insert Communication" ikoon.

   

   Daar is ook 'n alternatief. Hy is terloops die enigste vir ouer weergawes van Excel. In die konteks kieslys bring ons die wyser na die "Spesiale Insert" -item. In die addisionele spyskaart wat oopmaak, kies die posisie met dieselfde naam.



3. Daarna open 'n spesiale invoegingsvenster. Klik op die "Insert Communication" -knoppie in die onderste linkerhoek van die sel.



4. Wat u opsie kies, sal waardes van een tabel skikking in 'n ander ingevoeg word. Wanneer data in die bron verander word, sal hulle ook outomaties in die ingevoegde reeks verander.

Les: Spesiale Invoegsel in Excel

Metode 5: Kommunikasie tussen tabelle in verskeie boeke

Daarbenewens kan u 'n skakel tussen die tafelareas in verskillende boeke organiseer. Dit gebruik 'n spesiale invoegmiddel. Aksies sal absoluut soortgelyk wees aan diegene wat ons in die vorige metode oorweeg het, behalwe dat die navigasie tydens die formules nie tussen die gebiede van een boek sal hê nie, maar tussen die lêers. Natuurlik moet alle verwante boeke geopen word.

1. Kies die data-reeks wat na 'n ander boek oorgedra moet word. Klik op die regtermuisknop en kies die "kopie" -posisie in die geopende spyskaart.



2. Dan beweeg ons na die boek waarin hierdie data ingevoeg moet word. Kies die verlangde reeks. Klik regs muis knoppie. In die konteks kieslys in die "Insert Insitions" -groep, kies die "Insert Communication" -item.



3. Daarna sal die waardes ingevoeg word. Wanneer data in die bronboek verander word, sal 'n tafelskikking van die werkboek hulle outomaties toeneem. En dit is glad nie nodig om te verseker dat beide boeke oop is nie. Dit is genoeg om een ​​enigste werkboek oop te maak, en dit sal outomaties data van 'n geslote verwante dokument aanstuur indien daar vorige veranderinge daarin was.

Maar dit moet op gelet word dat in hierdie geval die inset in die vorm van 'n onveranderde skikking geproduseer sal word. Wanneer u enige sel met die ingevoegde data probeer verander, sal 'n boodskap bevolk word oor die onvermoë om dit te doen.

Veranderinge in so 'n verskeidenheid wat met 'n ander boek geassosieer word, kan slegs die verbinding verbreek.

Titel breek tussen tabelle

Soms is dit nodig om die verband tussen die tabelle te verbreek. Die rede hiervoor kan bogenoemde beskryf word wanneer u 'n skikking van 'n ander boek wil verander en bloot die gebruiker se onwilligheid sodat die data in dieselfde tabel outomaties van die ander opgedateer word.

Metode 1: Kommunikasie breek tussen boeke

Om die verband tussen boeke in alle selle te breek, deur eintlik een operasie uit te voer. In hierdie geval sal die data in die selle bly, maar hulle sal reeds staties wees nie opgedateerde waardes wat nie van ander dokumente afhanklik is nie.

1. In die boek waarin waardes van ander lêers strenger word, gaan na die data-oortjie. Klik op die "Change Links" -ikoon, wat op die band geleë is in die "Connection" nutsbalk. Daar moet kennis geneem word dat indien die huidige boek nie verbindings met ander lêers bevat nie, dan is hierdie knoppie onaktief.



2. Die skakelverandering venster word van stapel gestuur. Kies uit die lys van verwante boeke (indien daar verskeie van hulle is) die lêer waarmee ons die verbinding wil verbreek. Klik op die knoppie "Breek die verbinding".



3. 'N Inligtingsvenster open, wat 'n waarskuwing bied oor die gevolge van verdere aksies. As jy seker is dat jy gaan doen, klik op die "Break Communication" -knoppie.



4. Daarna sal alle verwysings na die gespesifiseerde lêer in die huidige dokument vervang word met statiese waardes.

Metode 2: Waardes invoeg

Maar die bogenoemde metode is slegs geskik as u al die skakels tussen die twee boeke heeltemal moet verbreek. Wat as jy die geassosieerde tabelle binne dieselfde lêer moet ontkoppel? U kan dit doen deur die data te kopieer en dan dieselfde plek as waardes in te voeg. Terloops, hierdie metode kan tussen die individuele data wissel van verskillende boeke gebreek word sonder om 'n gemeenskaplike verhouding tussen lêers te breek. Kom ons kyk hoe hierdie metode in die praktyk werk.

1. Ons beklemtoon die reeks waarin ons kommunikasie met 'n ander tafel wil verwyder. Klik op die regter muis knoppie. Kies in die oop spyskaart die "kopie" -item. In plaas van die gespesifiseerde aksies kan u 'n alternatiewe kombinasie van warm sleutels Ctrl + C skakel.



2. Volgende, sonder om die keuse van dieselfde fragment te verwyder, klik weer daarop met die regter muisknop. Hierdie keer in die lys van aksie, klik op die "Waarde" -ikoon, wat in die Insert Parameters-groep geplaas word.



3. Daarna sal alle verwysings in die toegewyde reeks vervang word met statiese waardes.

Soos u kan sien, het Excel maniere en gereedskap om verskeie tabelle onder mekaar te assosieer. Terselfdertyd kan tabelfolle op ander lakens en selfs in verskillende boeke wees. Indien nodig, kan hierdie verband maklik gebreek word.