So erstellen Sie Verknüpfungen zwischen Tabellen in Excel. Excel-Arbeitsmappe. Kommunikation zwischen Arbeitsblättern. Daten teilen. Microsoft Excel-Lab

Informationen in einer Microsoft Excel-Tabelle können mit Daten kombiniert werden, die aus einer Zeichnung extrahiert wurden.

Indem Sie einen Link zu einer Excel-Tabelle erstellen, können Sie Informationen aus dem gesamten Arbeitsblatt oder nur einem Teil davon, d. h. benannter Bereich. Im Dialogfeld Link zu externen Daten können Sie Folgendes tun:

• Legen Sie die Datenverknüpfung fest, indem Sie die XLS-Datei auswählen, mit der Sie die aus der Zeichnung abgerufenen Daten verknüpfen möchten.
• Ordnen Sie die extrahierten Zeichnungsdaten den Tabellendaten zu.
• Wählen Sie die Spalten in der XLS-Datei aus, die Sie mit den extrahierten Zeichnungsdaten zusammenführen möchten.

Die Seite „Datenkorrektur“ bietet Zugriff auf das Dialogfeld „Verknüpfung mit externen Daten“, in dem Sie die extrahierten Daten mit der gesamten Excel-Tabelle oder mit einer Reihe von Zeilen und Spalten verknüpfen können.

Für die Verknüpfung mit einer Tabelle muss Microsoft Excel auf dem Computer installiert sein, der den Datenabruf durchführt. Wenn Microsoft Excel nicht installiert ist oder die XLS-Datei nicht lokal auf dem Computer gespeichert ist, auf dem die Daten abgerufen werden, kann die Datenverbindung nicht hergestellt werden.

Nach dem Ändern des verknüpften Arbeitsblatts, z. B. nach dem Hinzufügen einer Zeile oder Spalte, können Sie die Tabelle in der Zeichnung aktualisieren, um die Änderung widerzuspiegeln, indem Sie den Befehl AKTUALISIEREN verwenden. Wenn Sie eine Tabelle in einer Zeichnung ändern, kann das zugehörige Arbeitsblatt auf ähnliche Weise mit demselben Befehl aktualisiert werden.

Hinweis: Verwenden Sie den REFER-Befehl, um eine Datenverbindung zu entfernen. Sie können keine Datenverknüpfungen aus der Palette „Externe Referenzen“ entfernen.

Über Datenverknüpfung und -zuordnung

Die Datenverknüpfung besteht aus der Auswahl einer Microsoft Excel-Tabelle, um die gesamte oder einen Teil der Tabelle mit den extrahierten Zeichnungsdaten zu kombinieren. Weitere Informationen zur Datenbindung finden Sie unter „Erstellen einer Tabellenverknüpfung mit externen Daten“.

Der Datenzuordnungsprozess erstellt eine Verknüpfung zwischen zwei Datensätzen: den Daten in der Spalte der extrahierten Zeichnungsdatentabelle und den Daten in der Spalte der Excel-Tabelle.

Das folgende Beispiel betrachtet eine einfache Stückliste. Aus der Zeichnung werden drei Datenspalten extrahiert. Diese Spalten enthalten die folgenden Daten: Teile-Nr., Redaktion und Menge. Zwei zusätzliche Datenspalten aus der Tabelle können in die Spezifikation aufgenommen werden. Diese Spalten enthalten die folgenden Daten Preis und Anbieter.

In diesem Beispiel enthält das Arbeitsblatt eine Teilenummernspalte mit einer Überschrift Katalognummer und entspricht der Spalte Detailnummer in der extrahierten Datentabelle. Wählen Sie im Dialogfeld Mit externen Daten verknüpfen aus "Detailnummer" als Spalte zum Zeichnen von Daten und "Katalognummer" als Spalte für externe Daten. Da sowohl in der Zeichnungsdatenspalte als auch in der externen Datenspalte mindestens eine entsprechende Datenzelle vorhanden ist, weiß die Datenextraktionsfunktion, wie die beiden Quellen zusammenhängen.

Die Datenkonsistenz kann durch Klicken auf die Schaltfläche „Übereinstimmung prüfen“ im Dialogfeld „Verknüpfung mit externen Daten“ überprüft werden. Bei einer Nichtübereinstimmung wird eine Warnmeldung mit Angabe des Grundes angezeigt. In einigen Fällen ist es nicht möglich, eine Verknüpfung zwischen Zeichnungsdaten und Tabellendaten korrekt herzustellen.

Kartierte Daten prüfen

Ein Fehler bei der Datenkonsistenzprüfung bedeutet, dass keine gültige Übereinstimmung zwischen den Zeichnungsdaten und den externen Daten (in der Tabelle) gefunden werden kann. Die folgenden Fehlermeldungen werden angezeigt.

Es wurde keine Übereinstimmung zwischen den Werten in den Zeichnungsdaten und den externen Daten gefunden.

• Wenn das Problem weiterhin besteht, werden die externen Daten nicht zusammengeführt. Vergleichen Sie die Tabellenspaltendaten mit den Zeichnungsdaten und versuchen Sie, übereinstimmende Werte zu finden. Beispielsweise können Zeichnungsdaten und externe Daten unterschiedliche Genauigkeitswerte haben.

Die Datenspalte in externen Daten enthält keine eindeutigen Werte.

• Eine externe Datenspalte enthält eine oder mehrere Zellen mit denselben Daten. In dieser Situation ist es nicht möglich, die Zeichnungsdaten eindeutig den externen Daten zuzuordnen.

In diesem Artikel erfahren Sie, wie Sie Daten aus zwei Excel-Tabellen schnell zusammenführen, wenn es keine exakten Übereinstimmungen in Schlüsselspalten gibt. Zum Beispiel, wenn die eindeutige Kennung aus der ersten Tabelle die ersten fünf Zeichen der Kennung aus der zweiten Tabelle sind. Alle in diesem Artikel vorgeschlagenen Lösungen wurden von mir in Excel 2013, 2010 und 2007 getestet.

Es gibt also zwei Excel-Tabellen, die für die weitere Datenanalyse kombiniert werden müssen. Angenommen, eine Tabelle enthält Preise (Spalte Preis) und Beschreibungen von Waren (Spalte Bier), die Sie verkaufen, und die zweite Tabelle enthält Daten zur Verfügbarkeit von Waren auf Lager (Spalte Auf Lager). Wenn Sie oder Ihre Kollegen beide Katalogtabellen erstellt haben, müssen beide mindestens eine Schlüsselspalte mit eindeutigen Produktkennzeichen enthalten. Die Produktbeschreibung oder der Preis können sich ändern, aber die eindeutige Kennung bleibt immer gleich.

Schwierigkeiten beginnen, wenn Sie einige Tische vom Hersteller oder aus anderen Abteilungen des Unternehmens erhalten. Die Dinge können noch komplizierter werden, wenn plötzlich ein neues Format eindeutiger Kennungen eingeführt wird oder wenn sich die Bezeichnungen der Bestandsnomenklatur (SKUs) nur geringfügig ändern. Und Sie stehen vor der Aufgabe, neue und alte Tabellen mit Daten in Excel zu kombinieren. Auf die eine oder andere Weise entsteht eine Situation, in der Datensätze in den Schlüsselspalten nur teilweise übereinstimmen, z. B. „ 12345 " und " 12345-new_suffix“. Sie verstehen, dass dies dieselbe SKU ist, aber der Computer ist nicht so schlagfertig! Diese nicht exakte Übereinstimmung macht es unmöglich, reguläre Excel-Formeln zu verwenden, um Daten aus zwei Tabellen zu kombinieren.

Und was wirklich schlimm ist, ist, dass die Korrespondenzen völlig verschwommen sein können, und „ Irgendeine Gesellschaft” in einer Tabelle kann sich in “ CJSC "Einige Gesellschaft"“ in einer anderen Tabelle und „ Neue Firma (früher Some Company)" und " Alte Firma“ wird sich auch als eine Aufzeichnung über dieselbe Firma herausstellen. Sie wissen das, aber wie erklären Sie es Excel?

Kommentar: Die in diesem Artikel beschriebenen Lösungen sind universell. Sie können sie für die weitere Verwendung mit beliebigen Standardrezepturen wie z SVERWEIS(VLOOKUP) , PASSEN(PASSEN) , GPR(WVERWEIS) und so weiter.

Wählen Sie das entsprechende Beispiel, um direkt zur richtigen Lösung zu springen:

Eine Schlüsselspalte in einer der Tabellen enthält zusätzliche Zeichen

Betrachten Sie zwei Tabellen. Die Spalten der ersten Tabelle enthalten die Artikelnummer (SKU), den Biernamen (Beer) und seinen Preis (Price). Die zweite Tabelle enthält die SKU und die Anzahl der auf Lager befindlichen Flaschen (In stock). Anstelle von Bier kann es ein beliebiges Produkt geben, und die Anzahl der Spalten im wirklichen Leben kann viel größer sein.

In der Tabelle mit zusätzlichen Symbolen erstellen wir eine Hilfsspalte. Sie können ihn am Ende der Tabelle einfügen, am besten fügen Sie ihn aber sichtbar rechts nach der Schlüsselspalte ein.

Der Schlüssel in der Tabelle in unserem Beispiel ist die Spalte EIN mit den SKU-Daten, und Sie müssen die ersten 5 Zeichen daraus extrahieren. Fügen wir eine Hilfsspalte hinzu und benennen Sie sie SKU-Helfer:

Bereit! Wir haben jetzt Schlüsselspalten mit exakten Übereinstimmungswerten – Spalte SKU-Helfer in Haupttabelle und Spalte Artikelnummer in der zu durchsuchenden Tabelle.

Nutze jetzt die Funktion SVERWEIS(SVERWEIS) erhalten wir das gewünschte Ergebnis:

Andere Formeln

• Erstmal extrahieren X Zeichen rechts: z. B. 6 Zeichen rechts vom Eintrag „DSFH-164900“. Die Formel wird wie folgt aussehen:

  RECHTS (A2;6)
  =RECHTS(A2,6)

• Überspringen Sie die erste X Zeichen, extrahieren Sie Folgendes Y Zeichen: Sie müssen beispielsweise „0123“ aus dem Eintrag „PREFIX_0123_SUFF“ extrahieren. Hier müssen wir die ersten 8 Zeichen überspringen und die nächsten 4 Zeichen extrahieren. Die Formel wird wie folgt aussehen:

  MITTE(A2;8;4)
  =MITTEL(A2,8,4)

• Extrahieren Sie alle Zeichen bis zum Trennzeichen, die Länge der resultierenden Sequenz kann unterschiedlich sein. Sie möchten beispielsweise „123456“ und „0123“ aus den Einträgen „123456-Suffix“ bzw. „0123-Suffix“ extrahieren. Die Formel wird wie folgt aussehen:

  LINKS(A2,FINDEN("-",A2)-1)
  =LINKS(A2,FINDEN("-",A2)-1)

Kurz gesagt, Sie können Excel-Funktionen wie verwenden LINKS(LINKS) RECHTS(RECHTS), PSTR(MITTEL), FINDEN(FIND), um beliebige Teile eines zusammengesetzten Index abzurufen. Wenn Sie dabei Schwierigkeiten haben, werden wir unser Bestes tun, um Ihnen zu helfen.

Daten aus einer Schlüsselspalte in der ersten Tabelle werden in zwei oder mehr Spalten in der zweiten Tabelle aufgeteilt

Angenommen, die durchsuchte Tabelle enthält eine Spalte mit Bezeichnern. Die Zellen dieser Spalte enthalten Datensätze des Formulars XXXX-YYYY, wo XXXX ist eine Codebezeichnung für eine Warengruppe (Mobiltelefone, Fernseher, Videokameras, Fotoapparate) und JJJJ ist der Produktcode innerhalb der Gruppe. Die Haupttabelle besteht aus zwei Spalten: eine enthält Warengruppencodes (Gruppe), die zweite enthält Warencodes (ID). Wir können die Produktgruppencodes nicht einfach verwerfen, da sich der gleiche Produktcode in verschiedenen Gruppen wiederholen kann.

In einer Zelle C2 wir schreiben die folgende Formel:

VERKETTEN(A2;"-";B2)
=VERKETTEN(A2,"-",B2)

Hier A2 die Adresse der Zelle ist, die den Gruppencode enthält; Symbol " – ” ist das Trennzeichen; B2 ist die Adresse der Zelle, die den Produktcode enthält. Kopieren Sie die Formel in die restlichen Zeilen.

Jetzt wird es nicht schwierig sein, die Daten aus unseren beiden Tabellen zu kombinieren. Wir passen die Spalte an Vollständige ID erste Tabelle mit Spalte ICH WÜRDE zweiter Tisch. Wenn eine Übereinstimmung gefunden wird, werden die Einträge aus den Spalten Beschreibung und Preis Die zweite Tabelle wird der ersten Tabelle hinzugefügt.

Daten in Schlüsselspalten stimmen nicht überein

Hier ein Beispiel: Sie sind Inhaber eines kleinen Ladens, Sie beziehen Waren von einem oder mehreren Lieferanten. Jeder von ihnen hat seine eigene Nomenklatur, die sich von Ihrer unterscheidet. Daher gibt es Situationen, in denen Ihr Eintrag „Case-Ip4S-01“ mit dem Eintrag „SPK-A1403“ in der vom Lieferanten erhaltenen Excel-Datei übereinstimmt. Solche Diskrepanzen treten zufällig auf und es gibt keine allgemeine Regel, "SPK-A1403" automatisch in "Case-Ip4S-01" umzuwandeln.

Schlechte Nachrichten: Die in diesen beiden Excel-Tabellen enthaltenen Daten müssen manuell bearbeitet werden, um sie später zusammenführen zu können.

Gute Nachrichten: Dies muss nur einmal durchgeführt werden, und die resultierende Hilfstabelle kann für eine spätere Verwendung gespeichert werden. Dann können Sie diese Tabellen automatisch zusammenführen und auf diese Weise viel Zeit sparen

1. Erstellen Sie eine Hilfstabelle für die Suche.

Erstellen Sie eine neue Excel-Tabelle und benennen Sie sie SKU-Konverter. Kopieren der gesamten Spalte Unsere.SKU aus einem Blatt Speichern zu einem neuen Blatt, entfernen Sie Duplikate und lassen Sie nur eindeutige Werte darin.

Fügen Sie daneben eine Spalte hinzu Supp.SKU und manuell nach Übereinstimmungen zwischen Spaltenwerten suchen Unsere.SKU und Supp.SKU(Die Beschreibungen aus der Spalte helfen uns dabei Beschreibung). Das ist ein langweiliger Job, lass dir den Gedanken gefallen, dass du es nur einmal machen musst :-).

Als Ergebnis haben wir folgende Tabelle:

2. Aktualisieren Sie die Haupttabelle mit den Daten aus der Nachschlagetabelle.

Fügen Sie eine neue Spalte in die Haupttabelle ein (Speicherblatt) Supp.SKU.

Als nächstes verwenden Sie die Funktion SVERWEIS(SVERWEIS) Blätter vergleichen Speichern und SKU-Konverter Unsere.SKU, und für aktualisierte Daten - eine Spalte Supp.SKU.

Spalte Supp.SKU mit Original-Herstellercodes ausgefüllt.

Kommentar: Wenn in einer Spalte Supp.SKU leere Zellen erscheinen, dann müssen Sie alle Codes nehmen Artikelnummer entsprechend diesen leeren Zellen, fügen Sie sie der Tabelle hinzu SKU-Konverter und finden Sie den entsprechenden Code aus der Anbietertabelle. Wiederholen Sie danach Schritt 2.

3. Daten aus der Nachschlagetabelle in die Haupttabelle übertragen

Unsere Haupttabelle hat eine Schlüsselspalte, die genau mit den Elementen der Nachschlagetabelle übereinstimmt, sodass diese Aufgabe jetzt ein Kinderspiel ist.

Verwendung der Funktion SVERWEIS(VLOOKUP) Blattdaten verketten Speichern mit Blattdaten Großhandelslieferant 1, indem Sie eine Spalte verwenden, um nach Übereinstimmungen zu suchen Supp.SKU.

Hier ist ein Beispiel für aktualisierte Daten in einer Spalte Großhandelspreis:

Es ist einfach, nicht wahr? Stellen Sie Ihre Fragen in den Kommentaren zum Artikel, ich werde versuchen, so schnell wie möglich zu antworten.

Eine verknüpfte Tabelle ist ein Datensatz, der als einzelne Entität verwaltet werden kann.

Die Schaltfläche dient zum Erstellen einer verknüpften Tabelle. "Als Tabelle formatieren" auf dem Panel "Stile" Bänder "Heim".

Wählen Sie den gewünschten Stil für die zukünftige Tabelle aus und legen Sie den Zellbereich fest, auf dessen Grundlage die verknüpfte Tabelle erstellt wird.

Nachdem alle Parameter eingestellt sind, erhalten wir eine verknüpfte Tabelle. Neben den Spaltenüberschriften erscheinen Schaltflächen mit Pfeilen (ähnlich wie beim Filtern) und im Programmfenster erscheint ein Kontext-Tool "Arbeiten mit Tabellen", die das Band enthält "Konstrukteur".

Jede verknüpfte Tabelle erhält einen eindeutigen Namen. Standardmäßig - "Tabellennummer". Sie können den Namen der Tabelle im Panel ändern "Eigenschaften".

Auf Platte "Tabellenstile" Sie können den Stil der verknüpften Tabelle nach Bedarf ändern oder anpassen.

Eine verknüpfte Tabelle kann wieder in einen regulären Zellbereich umgewandelt werden. Dafür ist der Knopf da. "In Bereich umwandeln" auf dem Panel "Service" kontextbezogenes Band "Konstrukteur".

Wie bereits erwähnt, befinden sich neben den Spaltenüberschriften die Sortier- und Filterschaltflächen, mit denen Sie die Daten sortieren und Filter anwenden können.

In einer verknüpften Tabelle können Sie Summen in Spalten zählen. Aktivieren Sie dazu das Kontrollkästchen "Gesamtzeile" auf dem Panel "Optionen für Tabellenstile".

Über die Dropdown-Liste können Sie den gewünschten Wert der Endfunktion auswählen.

Sie können der verknüpften Tabelle Zeilen und Spalten hinzufügen/entfernen.

Dies kann auf mehrere Arten erfolgen.

1. Verwenden Sie die Schaltfläche "Tabellengröße ändern" auf dem Panel "Eigenschaften".

2. Setzen Sie den Cursor in die Zelle der verknüpften Tabelle, neben der Sie eine neue Spalte (Zeile) hinzufügen möchten, und auf das Panel "Zellen" Bänder "Heim" verwenden Sie die Schaltfläche "Einfügung".

Bei bestimmten Aufgaben in Excel hat man es manchmal mit mehreren Tabellen zu tun, die auch miteinander in Beziehung stehen. Das heißt, Daten aus einer Tabelle werden in andere gezogen, und wenn sie sich ändern, werden die Werte in allen zugehörigen Tabellenbereichen neu berechnet.

Verknüpfte Tabellen sind sehr nützlich, um große Informationsmengen zu verarbeiten. Außerdem ist es nicht sehr praktisch, alle Informationen in einer Tabelle zu haben, wenn diese nicht homogen ist. Es ist schwierig, mit solchen Objekten zu arbeiten und sie zu durchsuchen. Dieses Problem dient nur dazu, zusammengehörige Tabellen zu eliminieren, zwischen denen Informationen verteilt, aber gleichzeitig miteinander verbunden sind. Zusammengehörige Tabellenbereiche können sich nicht nur innerhalb eines Blattes oder eines Buches befinden, sondern auch in separaten Büchern (Dateien). Die letzten beiden Optionen werden in der Praxis am häufigsten verwendet, da der Zweck dieser Technologie darin besteht, von der Anhäufung von Daten wegzukommen, und das Anhäufen auf einer Seite das Problem nicht grundlegend löst. Lassen Sie uns lernen, wie man diese Art der Datenverwaltung erstellt und damit arbeitet.

Lassen Sie uns zunächst auf die Frage eingehen, auf welche Weise es möglich ist, eine Beziehung zwischen verschiedenen Tischprogrammen herzustellen.

Methode 1: Tabellen direkt mit einer Formel verknüpfen

Der einfachste Weg, Daten zu verknüpfen, ist die Verwendung von Formeln, die auf andere Tabellenbereiche verweisen. Nennt sich Direktbindung. Diese Methode ist intuitiv, da sie das Verknüpfen ähnlich wie das Verknüpfen mit Daten in einem einzelnen Tabellenarray durchführt.

Sehen wir uns an einem Beispiel an, wie wir eine Verknüpfung per Direktverlinkung herstellen können. Wir haben zwei Tabellen auf zwei Blättern. In einer Tabelle werden die Löhne anhand einer Formel berechnet, indem die Mitarbeiterquote mit einem einzigen Koeffizienten für alle multipliziert wird.

Auf dem zweiten Blatt befindet sich ein Tabellenbereich, in dem sich eine Liste der Mitarbeiter mit ihren Gehältern befindet. Die Liste der Mitarbeiter wird in beiden Fällen in der gleichen Reihenfolge angezeigt.

Es ist darauf zu achten, dass die Kursdaten aus dem zweiten Blatt in die entsprechenden Zellen des ersten gezogen werden.

Methode 2: Verwenden einer Reihe von Operatoren INDEX - MATCH

Aber was ist, wenn die Liste der Mitarbeiter in den Tabellenfeldern nicht in der gleichen Reihenfolge ist? In diesem Fall besteht, wie bereits erwähnt, eine Möglichkeit darin, jede der zu verknüpfenden Zellen manuell zu verknüpfen. Das ist aber nur für kleine Tische geeignet. Bei großen Reichweiten dauert die Implementierung einer solchen Option bestenfalls sehr lange, und schlimmstenfalls ist sie in der Praxis überhaupt nicht durchführbar. Dieses Problem kann jedoch mit einer Reihe von Operatoren gelöst werden INDEX – PASSEN. Sehen wir uns an, wie dies bewerkstelligt werden kann, indem die Daten in den Tabellenbereichen verknüpft werden, die in der vorherigen Methode besprochen wurden.

1. Wählen Sie das erste Element einer Spalte aus "Gebot". Gehe zu Funktionsassistent indem Sie auf das Symbol klicken "Funktion einfügen".



2. BEI Funktionsassistent in einer Gruppe "Referenzen und Arrays" Suchen Sie den Namen und markieren Sie ihn "INDEX".



3. Dieser Operator hat zwei Formen: eine Form zum Arbeiten mit Arrays und eine Referenzform. In unserem Fall ist die erste Option erforderlich, also wählen Sie sie im nächsten sich öffnenden Formularauswahlfenster aus und klicken Sie auf die Schaltfläche OK.



4. Das Fenster Operatorargumente wurde gestartet INDEX. Die Aufgabe der angegebenen Funktion besteht darin, den Wert anzuzeigen, der sich im ausgewählten Bereich in der Zeile mit der angegebenen Nummer befindet. Allgemeine Operatorformel INDEX ist:

   INDEX(Array, Zeilennummer, [Spaltennummer])

   "Array"- ein Argument, das die Adresse des Bereichs enthält, aus dem wir Informationen nach der Nummer der angegebenen Zeile extrahieren werden.

   "Zeilennummer"- Argument, das die Nummer dieser Zeile ist. Wichtig zu wissen ist, dass die Zeilennummer nicht relativ zum gesamten Dokument angegeben werden sollte, sondern nur relativ zum ausgewählten Array.

   "Spaltennummer" ist ein optionales Argument. Um unser spezifisches Problem zu lösen, werden wir es nicht verwenden, und daher ist es nicht notwendig, sein Wesen separat zu beschreiben.

   Setzen Sie den Cursor in das Feld "Array". Danach gehen wir zu Blatt 2 und markieren Sie bei gedrückter linker Maustaste den gesamten Inhalt der Spalte "Gebot".



5. Nachdem die Koordinaten im Bedienerfenster angezeigt werden, setzen Sie den Cursor in das Feld "Zeilennummer". Wir werden dieses Argument mit dem Operator anzeigen PASSEN. Dazu klicken wir auf das Dreieck, das sich links neben der Funktionszeile befindet. Eine Liste der zuletzt verwendeten Operatoren wird geöffnet. Wenn Sie darunter einen Namen finden "SUCHE", können Sie darauf klicken. Klicken Sie andernfalls auf das allerletzte Element in der Liste - "Mehr Funktionen...".



6. Das Standardfenster startet Funktionsassistenten. Wir geben es an dieselbe Gruppe weiter "Referenzen und Arrays". Wählen Sie diesmal das Element aus der Liste aus. "SUCHE". Ausführen eines Tastenklicks OK.



7. Das Fenster Operatorargumente ist aktiviert PASSEN. Die angegebene Funktion soll die Nummer des Werts in einem bestimmten Array mit seinem Namen anzeigen. Dank dieser Funktion berechnen wir die Zeilennummer eines bestimmten Werts für die Funktion INDEX. Syntax PASSEN so präsentiert:

   MATCH(Suchwert, Sucharray, [Übereinstimmungstyp])

   "Gewünschter Wert"— ein Argument, das den Namen oder die Adresse der Drittanbieterbereichszelle enthält, in der es sich befindet. Es ist die Position dieses Namens im Zielbereich, die berechnet werden soll. In unserem Fall wird das erste Argument Zellreferenzen sein Blatt1, die die Namen der Mitarbeiter enthält.

   "Sichtbares Array"- ein Argument, das ein Verweis auf ein Array ist, in dem der angegebene Wert gesucht wird, um seine Position zu bestimmen. In unserem Fall wird diese Rolle von der Adresse der Spalte " Name" auf der Blatt 2.

   "Übereinstimmungstyp"- ein optionales Argument, aber im Gegensatz zum vorherigen Operator benötigen wir dieses optionale Argument. Es gibt an, wie der Operator den gesuchten Wert mit dem Array abgleicht. Dieses Argument kann einen von drei Werten haben: -1 ; 0 ; 1 . Wählen Sie für ungeordnete Arrays die Option aus «0» . Diese Option ist für unseren Fall geeignet.

   Beginnen wir also damit, die Felder des Argumentfensters auszufüllen. Setzen Sie den Cursor in das Feld "Gewünschter Wert", klicken Sie auf die erste Zelle der Spalte "Name" auf der Blatt1.



8. Nachdem die Koordinaten angezeigt werden, setzen Sie den Cursor in das Feld "Sichtbares Array" und gehe zum Etikett "Blatt 2", das sich am unteren Rand des Excel-Fensters über der Statusleiste befindet. Halten Sie die linke Maustaste gedrückt und wählen Sie mit dem Cursor alle Zellen in der Spalte aus "Name".



9. Nachdem ihre Koordinaten im Feld angezeigt werden "Sichtbares Array", geh aufs Feld "Übereinstimmungstyp" und über die Tastatur die Nummer dort einstellen «0» . Danach kehren wir wieder auf das Feld zurück. "Sichtbares Array". Tatsache ist, dass wir die Formel kopieren, wie wir es in der vorherigen Methode getan haben. Es wird eine Verschiebung der Adressen geben, aber wir müssen die Koordinaten des angezeigten Arrays fixieren. Er darf sich nicht bewegen. Markieren Sie die Koordinaten mit dem Cursor und drücken Sie die Funktionstaste F4. Wie Sie sehen können, wurde vor den Koordinaten ein Dollarzeichen angezeigt, was bedeutet, dass sich die Verknüpfung von relativ zu absolut geändert hat. Klicken Sie dann auf die Schaltfläche OK.



10. Das Ergebnis wird in der ersten Zelle der Spalte angezeigt "Gebot". Aber vor dem Kopieren müssen wir noch einen Bereich reparieren, nämlich das erste Argument der Funktion INDEX. Wählen Sie dazu das Spaltenelement aus, das die Formel enthält, und wechseln Sie in die Bearbeitungsleiste. Extrahieren Sie das erste Argument des Operators INDEX (B2:B7) und klicken Sie auf die Schaltfläche F4. Wie Sie sehen können, erschien das Dollarzeichen neben den ausgewählten Koordinaten. Wir klicken auf den Schlüssel Eintreten. Im Allgemeinen nahm die Formel die folgende Form an:

    INDEX(Blatt2!$B$2:$B$7;MATCH(Blatt1!A4;Blatt2!$A$2:$A$7;0))



11. Jetzt können Sie mit dem Füllgriff kopieren. Wir nennen es auf die gleiche Weise, über die wir zuvor gesprochen haben, und erweitern es bis zum Ende des Tabellenbereichs.



12. Wie Sie sehen können, werden trotz der Tatsache, dass die Reihenfolge der Zeilen in den beiden zugehörigen Tabellen nicht übereinstimmt, alle Werte dennoch nach den Namen der Mitarbeiter abgerufen. Dies wurde durch die Verwendung einer Kombination von Operatoren erreicht INDEX—PASSEN.

Wenn wir müssen Übertragen Sie Daten automatisch von einer Excel-Tabelle in eine andere, müssen Sie diese Tabellen verknüpfen. Es gibt mehrere Möglichkeiten, Daten aus zwei Tabellen in Excel zu verknüpfen. Einweg - Verwenden einer Formel finden Sie im Artikel "Verknüpfen von Tabellen in Excel".
Hier, überlegen wir So verknüpfen Sie zwei Tabellen in verschiedenen Excel-Arbeitsmappen, indem Beziehungen zwischen Tabellen hergestellt werden. Wenn alles eingerichtet ist, reicht es, eine Tabelle zu öffnen, damit die Daten von einer anderen Tabelle in diese Tabelle übertragen werden. Sie müssen nicht alle Bücher öffnen, damit die Verbindung funktioniert. Es funktioniert mit einer geöffneten Arbeitsmappe.
Wir haben also zwei Excel-Arbeitsmappen – Book1 und Book2. Es gibt eine solche Tabelle in Buch 1, bereits ausgefüllt. Tabelle 1.Aufmerksamkeit!
Wenn die Tabelle Zellen verbunden hat, funktioniert der Link in ihnen nicht oder geht verloren.
In Buch 2 erstellen wir die gleiche Tabelle, füllen sie aber nicht aus.

Eine andere Funktion "Link einfügen" kann so aufgerufen werden. Wir kopieren auch, aber klicken Sie im Kontextmenü auf die Funktion "Inhalte einfügen". Klicken Sie im erscheinenden Dialogfenster auf die Schaltfläche „Link einfügen“.