Verwandte Tabellen in Excel: Ausführliche Anweisungen

Bei der Durchführung bestimmter Aufgaben in Excel müssen man manchmal mit mehreren Tischen umgehen, die auch miteinander verwandt sind. Das heißt, die Daten von einer Tabelle sind an anderen festgezogen und die Werte in allen verwandten Tabellen werden bei der Änderung neu berechnet.

Verwandte Tabellen sind sehr praktisch, um eine große Menge an Informationen zu verarbeiten. Legen Sie alle Informationen in einen Tisch, außerdem, wenn es nicht homogen ist, nicht sehr praktisch. Es ist schwierig, mit solchen Objekten zu arbeiten und nach ihnen zu suchen. Das angegebene Problem ist nur so konzipiert, dass sie verwandte Tabellen beseitigt, die Informationen, die zwischen dem verteilt sind, aber gleichzeitig miteinander verbunden ist. Verwandte Tabellen können nicht nur in einem Blatt oder einem Buch sein, sondern auch in separaten Büchern (Dateien). Die letzten beiden Optionen in der Praxis werden am häufigsten verwendet, da der Zweck dieser Technologie nur darin besteht, sich von der Ansammlung von Daten zu entfliehen, und ihr Nicken auf einer Seite löst nicht grundsätzlich. Lernen Sie, wie man mit einem solchen Art von Datenverwaltung erstellt wird und wie Sie mit einem solchen Art von Datenmanagement arbeiten können.

Verwandte Tabellen erstellen.

Lassen Sie uns zunächst auf die Frage konzentrieren, inwas ist es möglich, eine Verbindung zwischen verschiedenen Tabellen zu erstellen.

Methode 1: Direkte Bindungstabellen-Formel

Der einfachste Weg, um Daten zu binden, ist der Einsatz von Formeln, in denen es Verweise auf andere Tabellen gibt. Es wird direkte Bindung genannt. Diese Methode ist intuitiv, da es anschließt, wann er es bindet, fast nur wie das Erstellen von Referenzen auf Daten in einem Tabellenarray.

Mal sehen, wie das Beispiel Sie durch direkte Bindung kommunizieren können. Wir haben zwei Tische auf zwei Blätter. Auf derselben Tabelle wird das Gehalt mit der Formel berechnet, indem die Mitarbeiterraten für einen einzelnen Koeffizienten multipliziert.

Auf dem zweiten Blatt befindet sich ein Tabellenbereich, in dem es eine Liste von Mitarbeitern mit ihrem Gehalt gibt. Die Liste der Mitarbeiter in beiden Fällen wird in einer Bestellung dargestellt.

Es ist notwendig, dass Daten auf den Wetten des zweiten Blatts in die entsprechenden Zellen des ersten festziehen.

1. Auf dem ersten Blatt ordieren wir die erste Zelle der Spalte "Wetten". Wir legen das Zeichen "=" ein. Klicken Sie anschließend auf das Etikett "Blatt 2", das auf dem linken Teil der Excel-Schnittstelle über die Statusleiste angeordnet ist.



2. Es gibt eine Bewegung im zweiten Bereich des Dokuments. Klicken Sie in der Spalte "Wetten" auf die erste Zelle. Klicken Sie dann auf die ENTER-Taste auf der Tastatur, um die Daten in die Zelle einzugeben, in der das "Gleiche" -Zeichen zuvor installiert wurde.



3. Dann gibt es einen automatischen Übergang zum ersten Blatt. Wie wir sehen können, wird der Wert des ersten Mitarbeiters aus der zweiten Tabelle in die entsprechende Zelle gezogen. Durch die Installation des Cursors in einer Zelle, die eine Wette enthält, sehen wir, dass die übliche Formel verwendet wird, um Daten auf dem Bildschirm anzuzeigen. Vor den Koordinaten der Zelle, von wo aus den Daten ausgegeben werden, gibt es einen Ausdruck "list2!", Der den Namen des Dokumentbereichs angibt, in dem sie sich befinden. Die allgemeine Formel in unserem Fall sieht so aus:

   = List2! B2



4. Jetzt müssen Sie Daten über die Preise aller anderen Mitarbeiter des Unternehmens übertragen. Dies kann natürlich auf dieselbe Weise erfolgen, dass wir die Aufgabe für den ersten Mitarbeiter erfüllt haben, aber wenn man bedenkt, dass sowohl Mitarbeiterlisten in derselben Reihenfolge befinden, kann die Aufgabe erheblich vereinfacht und durch seine Entscheidung beschleunigt werden. Dies kann einfach durch Kopieren der Formel in den untenstehenden Bereich erfolgen. Aufgrund der Tatsache, dass die Verweise auf Excel relativ sind, werden beim Kopieren ihrer Werte die Werteverschiebung verschoben, die wir brauchen. Die Kopiervorgang selbst kann mit einem Füllmarker erfolgen.

   Also setzen wir den Cursor mit der Formel in den unteren rechten Bereich des Elements. Danach muss der Cursor in Form eines schwarzen Kreuzes in den Füllmarker umwandeln. Wir führen die Klammer der linken Maustaste durch und ziehen den Cursor an die Nummer der Spalte.



5. Alle Daten aus einer ähnlichen Säule auf einem Blatt 2 wurden in eine Tabelle auf einem Blatt 1 gezogen. Wenn sich die Daten auf einem Blatt 2 ändern, ändern sie sich automatisch auf der ersten.

Methode 2: Verwenden des Blunterns von Betreiberindex - Suche

Was tun aber, wenn die Liste der Mitarbeiter in Tabellenarrays nicht in derselben Reihenfolge lokalisiert wird? In diesem Fall ist, wie bereits erwähnt, eines der Optionen, die Beziehung zwischen jedem dieser Zellen zu installieren, die manuell zuordnen sollen. Es ist jedoch mit Ausnahme von kleinen Tischen geeignet. Für massive Reichweite nimmt diese Option in der Regel viel Zeit in der Umsetzung, und im schlimmsten Fall wird es in der Praxis im Allgemeinen unrealistisch sein. Dieses Problem kann jedoch mit einem Bündel der Operatorenindex-Suche gelöst werden. Mal sehen, wie es getan werden kann, indem Sie die Daten in den Tabellen erstellen, über die sich das Gespräch in der vorherigen Methode befand.

1. Wir legen das erste Element der "Wetten" -Säuerung hervor. Gehen Sie zu dem Funktionen-Assistenten, indem Sie auf das Symbol "Funktion einfügen" klicken.



2. Im Assistenten der Funktionen in der Gruppe "Links und Arrays" finden wir den Namen "Index".



3. Dieser Bediener hat zwei Formulare: ein Formular zum Arbeiten mit Arrays und Referenz. In unserem Fall ist die erste Option erforderlich, also wählen Sie im nächsten Formularauswahlfenster aus, das eröffnet, und klicken Sie auf die Schaltfläche "OK".



4. Der Argument-Index des Bedieners beginnt mit dem Betrieb. Die Aufgabe der angegebenen Funktion ist die Ausgabe des Werts, der sich im ausgewählten Bereich in der Zeile mit der angegebenen Nummer befindet. Allgemeiner Formel-Operatorindex wie:

   = Index (Array; number_name; [nummer_stolbits])

   Das "Array" ist ein Argument, das den Bereich des Bereichs enthält, von dem wir Informationen durch die Nummer der angegebenen Zeile extrahieren.

   "Zeilennummer" ist ein Argument, das die Nummer dieser Zeile ist. Es ist wichtig zu wissen, dass die Zeilennummer nicht relativ zum gesamten Dokument angegeben werden sollte, sondern nur relativ zu dem zugewiesenen Array.

   Die "Nummer der Spalte" ist ein Argument, das optional ist. Um das gesäumte unserer Aufgabe zu lösen, werden wir es nicht verwenden, und daher ist es nicht notwendig, es separat zu beschreiben.

   Wir legen den Cursor in das Feld "Array". Gehen Sie danach zum Blatt 2 und halten Sie die linke Maustaste, wählen Sie den gesamten Inhalt der Spalte "Rate".



5. Nachdem die Koordinaten im Fenster des Bedieners angezeigt werden, setzen wir den Cursor in das Feld "Zeilennummer". Wir werden dieses Argument mit dem Suchoperator zurückziehen. Klicken Sie daher auf ein Dreieck, das sich links von der Funktionszeichenfolge befindet. Eine Liste der neu verwendeten Bediener öffnet sich. Wenn Sie den Namen "Suchfirma" unter ihnen finden, können Sie darauf klicken. Klicken Sie im entgegengesetzten Fall auf den neuesten Punkt der Liste - "Andere Funktionen ...".



6. Standardfenster-Assistentfenster beginnt. Gehen Sie dazu in derselben Gruppe "Links und Arrays". Wählen Sie diesmal in der Liste den Artikel "Firmenfirma". Führen Sie einen Klick auf die Schaltfläche "OK" aus.



7. Die Aktivierung der Argumente der Argumente des Suchoperators wird durchgeführt. Die angegebene Funktion soll die Wertnummer in einem bestimmten Array mit seinem Namen ausgeben. Dank dieser Funktion berechnen wir die Anzahl einer Zeichenfolge eines bestimmten Werts für die Funktionsfunktion. Die Syntax der Suchplatine wird dargestellt:

   = Suchplatine (SEARCH_NAME; Ansicht__nassive; [type_station])

   "Das gewünschte" ist ein Argument, das den Namen oder die Adresse der Zelle des Drittanbieters enthält, in dem sich er befindet. Es ist die Position dieses Namens im Zielbereich und sollte berechnet werden. In unserem Fall wird die Rolle des ersten Arguments auf Zellen auf einem Blatt 1 bezeichnet, in dem sich die Mitarbeiter befinden.

   "Listiges Array" ist ein Argument, das ein Verweis auf ein Array ist, das die Suche nach dem angegebenen Wert ausführt, um seine Position zu bestimmen. Wir haben diese Rolle, um die Adresse der Spalte "Name" auf einem Blatt 2 auszuführen.

   "Art des Vergleichs" - ein Argument, das optional ist, aber im Gegensatz zum vorherigen Bediener wird dieses optionale Argument erforderlich. Es zeigt an, wie der Bediener übereinstimmt, ist der gewünschte Wert mit einem Array. Dieses Argument kann einen von drei Werten haben: -1; 0; 1. Wählen Sie für ungeordnete Arrays die Option "0". Diese Option eignet sich für unseren Fall.

   Fahren Sie also fort, die Argumentenfensterfelder zu füllen. Wir legen den Cursor in das Feld "Nebogularwert", klicken Sie auf die erste Zelle "Name" auf einem Blatt 1.



8. Nachdem die Koordinaten angezeigt werden, setzen Sie den Cursor in das Feld "Massive" auflisten "und gehen Sie zum" Blatt 2 "-Alle, das sich am unteren Rand des Excel-Fensters über der Statusleiste befindet. Klammer der linken Maustaste und markieren Sie den Cursor alle Zellen der Spalte "Name".



9. Gehen Sie nachdem ihre Koordinaten im Feld "Massive" auflisten "angezeigt werden, gehen Sie zum Feld" Mapping Typ "und setzen Sie die Nummer" 0 "von der Tastatur ein. Danach kehren wir wieder in das Feld "schauen durch das Array" zurück. Tatsache ist, dass wir das Kopieren der Formel, wie in der vorherigen Methode, durchführen. Es wird eine Adressverschiebung geben, aber hier sind die Koordinaten des Arrays angesehen, an denen wir sichern müssen. Er sollte nicht verschieben. Wir markieren die Koordinaten mit dem Cursor und klicken Sie auf die Funktionstaste F4. Wie Sie sehen, erschien das Dollarzeichen vor den Koordinaten, was bedeutet, dass Referenz von Relativ in absolut umgewandelt wurde. Klicken Sie dann auf die Schaltfläche "OK".



10. Das Ergebnis wird in der ersten Zelle der Säule "WET" angezeigt. Aber vor dem Kopieren müssen wir einen anderen Bereich beheben, nämlich den ersten Argument-Funktionsindex. Wählen Sie dazu das Spaltenelement aus, das eine Formel enthält, und wechseln Sie in die Formelzeichenfolge. Ordnen Sie das erste Argument des Operator-Index (B2: B7) zuordnen, und klicken Sie auf die Taste F4. Wie Sie sehen, erschien das Dollarzeichen in der Nähe der ausgewählten Koordinaten. Klicken Sie auf die Eingabetaste. Im Allgemeinen hat die Formel das folgende Formular genommen:

    = Index (Sheet2! $ B $ 2: $ B $ 7; SUCHE BOARD (SHEET1! A4; LIST2! $ A $ 2: $ A $ 7; 0))



11. Jetzt können Sie mit einem Füllmarker kopieren. Wir nennen es auf dieselbe Weise, wie wir früher gesprochen haben, und strecken sich bis zum Ende des tabellarischen Bereichs.



12. Wie Sie sehen, obwohl die Reihenfolge der Saiten in zwei verwandten Tabellen nicht zusammenfällt, werden jedoch alle Werte entsprechend den Namen der Arbeiter angezogen. Dies wurde dank der Verwendung der Kombination der Operatoren-Indexsuche erreicht.

Methode 4: Spezialeinsatz

Tie-Tabellen-Arrays in Excel können auch ein spezielles Insertieren verwenden.

1. Wählen Sie die Werte aus, die Sie in eine andere Tabelle "festziehen möchten". In unserem Fall ist dies der Spaltenbereich "Wetten" auf einem Blatt 2. Klicken Sie mit der rechten Maustaste auf das spezielle Fragment. Wählen Sie in der Liste auf, die eröffnet, wählen Sie den Punkt "Kopieren". Eine alternative Kombination ist die Tastenkombination Strg + C. Danach bewegen wir uns zum Blatt 1.



2. Umzug in den Bereich des Buches, den Sie benötigen, ordnen Sie die Zellen, in denen die Werte festgezogen werden müssen. In unserem Fall ist dies die Spalte "Gebot". Klicken Sie mit der rechten Maustaste auf das spezielle Fragment. Klicken Sie im Kontextmenü in der Symbolleiste "Parameter einfügen" klicken Sie auf das Symbol "Kommunikation einfügen".

   

   Es gibt auch eine Alternative. Er ist übrigens der einzige für ältere Versionen von Excel. Im Kontextmenü bringen wir den Cursor auf den "Spezialeinsatz". Wählen Sie in dem zusätzlichen Menü, das öffnet, die Position mit demselben Namen auswählen.



3. Danach wird ein spezielles Einfügenfenster geöffnet. Klicken Sie in der unteren linken Ecke der Zelle auf die Schaltfläche "Kommunikation".



4. Welche Option, die Sie wählen, werden Werte von einem Tabellenarray in eine andere eingefügt. Beim Ändern von Daten in der Quelle ändern sie sich auch automatisch im eingefügten Bereich.

Lektion: Spezieller Einsatz in Excel

Methode 5: Kommunikation zwischen Tabellen in mehreren Büchern

Darüber hinaus können Sie einen Link zwischen den Tischbereichen in verschiedenen Büchern organisieren. Dies verwendet ein spezielles Insert-Tool. Die Maßnahmen sind absolut ähnlich wie bei der vorherigen Methode, mit der Ausnahme, dass die Navigation während der Formeln nicht zwischen den Bereichen eines Buches, jedoch zwischen den Dateien hat. Natürlich sollten alle verwandten Bücher geöffnet werden.

1. Wählen Sie den Datenbereich aus, der in ein anderes Buch übertragen wird. Klicken Sie auf die rechte Maustaste, und wählen Sie die Position "Kopieren" im geöffneten Menü aus.



2. Dann ziehen wir in das Buch, in dem diese Daten eingefügt werden sollen. Wählen Sie den gewünschten Bereich aus. Klicken Sie auf die rechte Maustaste. Wählen Sie im Kontextmenü in der Gruppe "Einfügen einfügen" die Option "Kommunikation einfügen" aus.



3. Danach werden die Werte eingefügt. Beim Ändern von Daten im Quellbuch wird sie automatisch ein Tabellenarray aus dem Arbeitsbuch festziehen. Es ist überhaupt nicht erforderlich, um sicherzustellen, dass beide Bücher offen sind. Es reicht aus, ein einziges Arbeitsbuch zu öffnen, und es wird automatisch Daten aus einem geschlossenen Dokument abgestimmt, wenn es vorherige Änderungen vorgenommen hat.

Es sei aber darauf hingewiesen, dass in diesem Fall das Inset in Form eines unveränderten Arrays hergestellt wird. Wenn Sie versuchen, jede Zelle mit den eingefügten Daten zu ändern, wird eine Nachricht mit der Information über die Unfähigkeit befreit.

Änderungen in einem solchen Array, das mit einem anderen Buch verbunden ist, kann nur die Verbindung brechen.

Titel bricht zwischen Tabellen

Manchmal ist es erforderlich, die Verbindung zwischen den Tischen zu brechen. Der Grund dafür kann das oben beschriebene sein, wenn Sie ein von ein anderes Buch eingefügter Array ändern möchten, und einfach nur die Reluktanz des Benutzers, so dass die Daten in derselben Tabelle automatisch von der anderen aktualisiert werden.

Methode 1: Kommunikation bricht zwischen Büchern zusammen

Um die Verbindung zwischen Büchern in allen Zellen zu brechen, indem Sie tatsächlich einen Betrieb durchführen. In diesem Fall bleiben die Daten in den Zellen, aber sie sind bereits statische nicht aktualisierte Werte, die nicht von anderen Dokumenten abhängen.

1. Gehen Sie in dem Buch, in dem Werte aus anderen Dateien angezogen sind, auf die Registerkarte Daten. Klicken Sie auf das Symbol "Links ändern", das sich auf dem Band in der Symbolleiste "Verbindung" befindet. Es sei darauf hingewiesen, dass, wenn das aktuelle Buch keine Verbindungen mit anderen Dateien enthält, diese Taste nicht inaktiv ist.



2. Das Link-Change-Fenster wird gestartet. Wählen Sie aus der Liste der verwandten Bücher aus (wenn mehrere von ihnen vorhanden sind) die Datei, mit der wir die Verbindung brechen möchten. Klicken Sie auf die Schaltfläche "Die Verbindung brechen".



3. Ein Informationsfenster öffnet sich, was eine Warnung über die Folgen weiterer Aktionen bietet. Wenn Sie sicher sind, dass Sie möchten, klicken Sie auf die Schaltfläche "Break Communication".



4. Danach werden alle Verweise auf die angegebene Datei im aktuellen Dokument durch statische Werte ersetzt.

Methode 2: Werte einfügen

Das obige Verfahren ist jedoch nur geeignet, wenn Sie alle Verbindungen zwischen den beiden Büchern vollständig brechen müssen. Was ist, wenn Sie die zugehörigen Tabellen in derselben Datei trennen müssen? Sie können dies tun, indem Sie die Daten kopieren, und dann denselben Ort wie Werte einsetzen. Diese Methode kann übrigens zwischen den einzelnen Datenbereiche verschiedener Bücher aufgebrochen werden, ohne eine gemeinsame Beziehung zwischen den Dateien zu brechen. Mal sehen, wie diese Methode in der Praxis arbeitet.

1. Wir zeigen den Bereich, in dem wir die Kommunikation mit einer anderen Tabelle löschen möchten. Klicken Sie auf die rechte Maustaste. Wählen Sie im öffnenen Menü das Element "Kopieren". Anstelle der angegebenen Aktionen können Sie eine alternative Kombination von Hot-Tasten-Strg + C wählen.



2. Klicken Sie anschließend, ohne die Auswahl aus demselben Fragment zu entfernen, wieder mit der rechten Maustaste darauf klicken. Klicken Sie dieses Mal in der Liste der Aktion auf das Symbol "Wert", das in der Gruppe Insert-Parameter gepostet wird.



3. Danach werden alle Referenzen im speziellen Bereich durch statische Werte ersetzt.

Wie Sie sehen, hat Excel Möglichkeiten und Werkzeuge, mehrere Tische untereinander zuzusetzen. Gleichzeitig können tabellarische Daten auf anderen Blättern und sogar in verschiedenen Büchern liegen. Bei Bedarf kann diese Verbindung leicht gebrochen werden.