Um wiederholte Anlagenbezeichnungen zu verknüpfen, ohne Zeilen zu vervielfachen, nummerieren Sie die Vorkommen jeder Bezeichnung in beiden Listen und suchen Sie dann nach dem Paar aus Bezeichnung und Vorkommensnummer. Diese Anleitung für Excel für Mac behält jede Zeile der linken Liste bei und kennzeichnet fehlende Übereinstimmungen.

Was bedeutet Abgleichen nach Vorkommen?

Angenommen, zwei Listen mit Kunstobjekten enthalten jeweils mehrere Zeilen mit der Bezeichnung Arc Tile. Ihre Aufgabe ist es, die erste Quellmenge der ersten Arc Tile-Zeile in der linken Liste zuzuordnen, die zweite Quellmenge der zweiten und so weiter. Zwischen diesen Zeilen können andere Bezeichnungen stehen; die Vorkommensnummer wird für jede Bezeichnung separat gezählt.

Eine Verknüpfung allein anhand der Bezeichnung ist nicht eindeutig. Wenn eine Liste drei Arc Tile-Zeilen und die andere zwei enthält, würden durch die Zuordnung aller möglichen Kombinationen sechs Kombinationen für diese Bezeichnung entstehen. Eine einfache Suche hat noch eine weitere Einschränkung: Microsoft dokumentiert, dass XLOOKUP den ersten gefundenen Treffer zurückgibt. Eine wiederholte Suche nur anhand der Bezeichnung geht daher nicht zum nächsten Vorkommen weiter. XVERWEIS-Funktion

Die Lösung besteht darin, den Suchschlüssel genauer zu definieren: Arc Tile|1, Arc Tile|2 und Arc Tile|3. Jeder nummerierte Schlüssel identifiziert ein Vorkommen. Die folgende Methode ist eine durchgespielte Konstruktion mit dokumentierten Excel-Funktionen; die Microsoft-Dokumentation beschreibt das Verhalten dieser Funktionen.

Verwenden Sie diesen Ansatz, wenn die Zeilenreihenfolge die Zuordnung bestimmt. Sollen die Zeilen stattdessen anhand einer separaten Anlagenkennung oder eines Revisionsfelds abgeglichen werden, verwenden Sie diese Information. Vorkommensnummern können nicht bestimmen, welche zwei Datensätze zusammengehören, wenn die Reihenfolge keine Bedeutung hat.

Welche Excel-Versionen für Mac unterstützen diese Methode?

Die Hauptformeln verwenden COUNTIF, die Verkettung mit & und XLOOKUP. Microsoft führt auf den relevanten Funktionsseiten Excel für Microsoft 365 für Mac, Excel 2024 für Mac und Excel 2021 für Mac auf. In der Dokumentation zu XLOOKUP steht ausdrücklich, dass die Funktion in Excel 2016 und Excel 2019 nicht verfügbar ist. XVERWEIS-Funktion, Die ZÄHLENWENN-Funktion in Microsoft Excel verwenden

Beginnen Sie mit der unten ausgefüllten Suchformel. Eine spätere Variante gibt dieselben sieben Ergebnisse mit einer einzigen überlaufenden Matrixformel zurück. Microsoft dokumentiert, dass überlaufende Formeln außerhalb von Excel-Tabellen im Arbeitsblattraster stehen müssen. Dynamische Matrixformeln und Verhalten überlaufender Matrizen

Zwei Beispielslisten mit Anlagen einrichten

Verwenden Sie ein leeres Arbeitsblatt in einer Arbeitsmappe. Geben Sie die folgende Liste auf der linken Seite in A1:B8 ein. Die Bezeichnungen und Mengen dienen nur zur Veranschaulichung; die linke Menge bleibt Bestandteil der ursprünglichen Liste und wird nicht zur Bestimmung der Übereinstimmung verwendet.

Anlagenbezeichnung Linke Menge
Arc Tile 4
Dot Panel 2
Arc Tile 6
Line Print 3
Dot Panel 5
Arc Tile 1
Ring Card 2

Geben Sie die Quellliste in G1:H7 ein. Behalten Sie die angezeigte Zeilenreihenfolge bei, damit Ihre Ergebnisse mit der Anleitung übereinstimmen.

Anlagenbezeichnung Quellmenge
Dot Panel 12
Arc Tile 8
Line Print 7
Arc Tile 9
Dot Panel 0
Grid Sheet 11

Fügen Sie die Überschriften Vorkommen, Verknüpfungsschlüssel und Zugeordnete Quellmenge in C1, D1 und E1 ein. Fügen Sie Vorkommen, Verknüpfungsschlüssel und Von linker Liste verwendet in I1, J1 und K1 ein. Lassen Sie Spalte F leer, damit die Listen optisch getrennt sind.

Verwenden Sie in diesem Beispiel nicht leere Textbezeichnungen mit weniger als 255 Zeichen, einheitlicher Groß- und Kleinschreibung sowie einheitlichen Leerzeichen und ohne Platzhalterzeichen wie *, ? oder ~. Geben Sie für jede Quellzeile eine numerische Quellmenge ein, gegebenenfalls auch null. Diese Voraussetzungen sorgen für eine eindeutige Auswertung der einfachen Formeln.

Vorkommen nummerieren und Suchschlüssel erstellen

Jede Bezeichnung von oben in ihrer eigenen Liste zählen

Geben Sie diese Formel in C2 ein, drücken Sie Return und kopieren Sie sie bis C8 nach unten:

=COUNTIF($A$2:A2,A2)

Der erste Endpunkt, $A$2, bleibt unverändert. Der zweite Endpunkt erweitert sich beim Kopieren nach unten. In jeder Zeile zählt die Formel, wie oft die jeweilige Bezeichnung bis dahin bereits vorkam. COUNTIF zählt Zellen, die einem angegebenen Kriterium entsprechen. Die ZÄHLENWENN-Funktion in Microsoft Excel verwenden

Die resultierenden Vorkommensnummern der linken Liste sollten 1, 1, 2, 1, 2, 3, 1 lauten. Insbesondere erhält das dritte Arc Tile den Wert 3, obwohl andere Bezeichnungen zwischen den Zeilen stehen.

Geben Sie die entsprechende Quellformel in I2 ein und kopieren Sie sie anschließend bis I7:

=COUNTIF($G$2:G2,G2)

Die Vorkommensnummern der Quelle sollten 1, 1, 1, 2, 2, 1 lauten. Zählen Sie die beiden Listen unabhängig voneinander: Die Vorkommensnummer einer Zeile gibt ihre Position unter den Zeilen mit derselben Bezeichnung innerhalb ihrer eigenen Liste an.

Bezeichnung und Vorkommen kombinieren

Geben Sie diese Formel in D2 ein und kopieren Sie sie bis D8:

=A2&"|"&C2

Geben Sie diese Formel in J2 ein und kopieren Sie sie bis J7:

=G2&"|"&I2

Die linke Liste enthält nun Arc Tile|1, Arc Tile|2 und Arc Tile|3. Die Quelle enthält nur die ersten beiden dieser Schlüssel. So wird das fehlende dritte Vorkommen sichtbar, bevor Mengen zurückgegeben werden.

Eine Quellmenge pro Zeile der linken Liste zurückgeben

Geben Sie die folgende Formel in E2 ein und kopieren Sie sie anschließend bis E8:

=XLOOKUP(D2,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

Die Formel durchsucht die Quellschlüssel, gibt die entsprechende Quellmenge zurück und zeigt Keine Übereinstimmung an, wenn der nummerierte Schlüssel fehlt. Das letzte 0 fordert ausdrücklich eine exakte Übereinstimmung an; dies ist auch die Standardeinstellung von XLOOKUP. XVERWEIS-Funktion

Das fertige Ergebnis für die linke Liste sollte so aussehen:

Anlagenbezeichnung Linke Menge Vorkommen Verknüpfungsschlüssel Zugeordnete Quellmenge
Arc Tile 4 1 Arc Tile\ 1
Dot Panel 2 1 Dot Panel\ 1
Arc Tile 6 2 Arc Tile\ 2
Line Print 3 1 Line Print\ 1
Dot Panel 5 2 Dot Panel\ 2
Arc Tile 1 3 Arc Tile\ 3
Ring Card 2 1 Ring Card\ 1

Es sind weiterhin sieben Zeilen in der linken Liste vorhanden. Das zweite Arc Tile erhält 9 und nicht erneut 8, den Wert des ersten Vorkommens. Für das dritte gibt es keinen passenden Eintrag in der Quelle. Auch für Ring Card gibt es keinen passenden Eintrag, während die zugeordnete Null für das zweite Dot Panel sichtbar bleibt.

Lassen Sie Keine Übereinstimmung von null unterscheidbar. In diesem Beispiel ist null eine echte Quellmenge; würde bei einer fehlenden Übereinstimmung null verwendet, wäre dieser Unterschied nicht mehr sichtbar. Klären Sie außerdem leere Quellmengen, bevor Sie das Ergebnis auswerten, damit eine leere Quellzelle nicht mit einer erfassten Menge verwechselt wird.

Optional: Ergebnisse als eine Matrix zurückgeben

Nachdem Sie die ausgefüllten Ergebnisse geprüft haben, löschen Sie nur E2:E8 und geben Sie diese Alternative in E2 ein:

=XLOOKUP(D2:D8,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

Damit werden alle sieben Verknüpfungsschlüssel der linken Liste als Suchwerte verwendet. Mit den Beispieldaten belegt das Ergebnis die Zellen E2:E8 in der Reihenfolge der linken Liste. Drücken Sie Return; die angrenzenden Ausgabefelder müssen leer sein. Microsoft bezeichnet diese automatische Platzierung von Matrixergebnissen als Überlauf. Dynamische Matrixformeln und Verhalten überlaufender Matrizen

Wählen Sie die ausgefüllte Version, wenn Sie lieber in jeder Zeile eine einzeln überprüfbare Formel haben. Wählen Sie die Überlaufversion, wenn eine einzelne Formel die Ergebnisspalte steuern soll. Beide beruhen auf denselben Vorkommensschlüsseln.

Fehlende Übereinstimmungen und nicht verwendete Quellzeilen prüfen

Die Anzahl der Zeilen der linken Liste beizubehalten bedeutet nicht, dass jede Quellzeile verwendet wurde. Fügen Sie eine umgekehrte Prüfung hinzu, damit zusätzliche Quellvorkommen sichtbar bleiben. Geben Sie in K2 die folgende Formel ein und kopieren Sie sie bis K7:

=COUNTIF($D$2:$D$8,J2)

Bei diesem Beispiel liefern die ersten fünf Quellzeilen den Wert 1, und Grid Sheet|1 liefert 0. Diese Null kennzeichnet eine Quellzeile ohne passenden Eintrag in der linken Liste. Ein Wert größer als 1 würde bedeuten, dass auf einen Quellschlüssel von mehreren linken Schlüsseln verwiesen wird und dies untersucht werden muss.

Verwenden Sie diese Prüfungen gemeinsam:

  • Bestätigen Sie, dass die Ausgabe genau die sieben ursprünglichen Zeilen der linken Liste umfasst.
  • Bestätigen Sie die Mengenfolge: 8, 12, 9, 7, 0, Keine Übereinstimmung, Keine Übereinstimmung.
  • Bestätigen Sie, dass das dritte Arc Tile und Ring Card weiterhin sichtbar als nicht zugeordnet gekennzeichnet sind.
  • Bestätigen Sie, dass Grid Sheet|1 in der Quellenprüfung als nicht verwendet gekennzeichnet ist.

Die erwartete Folge und das Ergebnis für die nicht verwendete Quelle wurden mit einer kleinen deterministischen Berechnung der Verknüpfungslogik anhand der Vorkommenspaare geprüft. Die Excel-Formeln selbst wurden anhand der von Microsoft dokumentierten Syntax überprüft, aber nicht in Excel für Mac ausgeführt.

Ergebnisse prüfen, bevor Sie die Arbeitsmappe erweitern

Zuordnungen ändern sich nach dem Sortieren

Die Vorkommensformeln verwenden die aktuelle Zeilenreihenfolge. Wenn Sie eine der Listen sortieren und neu berechnen, kann sich ändern, welche wiederholte Zeile die Vorkommensnummer 1 oder 2 erhält. Legen Sie die gewünschte Reihenfolge vor der Nummerierung fest. Wenn eine dauerhafte Zuordnung erforderlich ist, behalten Sie eine stabile Kennung bei, die sie festhält, statt sich allein auf eine neu berechnete Position zu verlassen.

Bezeichnungen sehen gleich aus, werden aber unerwartet abgeglichen

Microsoft weist darauf hin, dass COUNTIF die Groß- und Kleinschreibung ignoriert, Platzhalterkriterien erkennt und bei führenden oder nachgestellten Leerzeichen sowie anderen unerwünschten Zeichen unerwartete Ergebnisse liefern kann. Außerdem dokumentiert Microsoft Einschränkungen beim Abgleich von Zeichenfolgen mit mehr als 255 Zeichen. Vereinheitlichen Sie die Bezeichnungen in beiden Listen, bevor Sie sie nummerieren; die kurzen, einheitlich formatierten Bezeichnungen oben vermeiden diese Bedingungen. Die ZÄHLENWENN-Funktion in Microsoft Excel verwenden

Die Matrixformel zeigt #SPILL! an

Prüfen Sie, ob in E3:E8 bereits Inhalte vorhanden sind, einschließlich der zuvor ausgefüllten Formeln. Verschieben Sie alle Inhalte, die Sie behalten möchten, und entfernen Sie dann das Hindernis. Befindet sich die Formel in einer Excel-Tabelle, verwenden Sie den ausgefüllten Ansatz oder platzieren Sie die Überlaufausgabe außerhalb der Tabelle. Diese Einschränkungen für den Überlauf sind dokumentiert. Dynamische Matrixformeln und Verhalten überlaufender Matrizen

Sie benötigen eine Suche ohne XLOOKUP

Dieselben Hilfsschlüssel können eine Suche mit INDEX/MATCH ermöglichen. Als Alternative zu den beiden Formeln oben geben Sie diese Formel in E2 ein und kopieren sie bis E8:

=INDEX($H$2:$H$7,MATCH(D2,$J$2:$J$7,0))

Hier ermittelt MATCH mit 0 die genaue Position des Verknüpfungsschlüssels, und INDEX gibt die Menge an dieser Position zurück. Fehlende Schlüssel bleiben absichtlich als #N/A sichtbar. Microsoft dokumentiert beide Funktionen für aktuelle Mac-Editionen. Diese skalare Formel benötigt keine Überlaufausgabe. VERGLEICH-Funktion, INDEX-Funktion

Neue Zeilen fehlen in den Ergebnissen

Die Anleitung verwendet feste Bereiche. Wenn Sie Zeilen hinzufügen, erweitern Sie die Formeln für Vorkommen und Verknüpfungsschlüssel, die Such- und Rückgabebereiche der Quellliste sowie den Bereich der umgekehrten Prüfung gemeinsam. Erweitern Sie bei der Überlaufversion außerdem den Suchbereich der linken Liste über D8 hinaus. Werden Daten nur unterhalb dieser Bereiche hinzugefügt, fließen sie nicht in die Berechnung ein.

Sobald die Ergebnisfolge und die umgekehrte Prüfung der gewünschten Reihenfolge entsprechen, haben Sie eine nutzbare Verknüpfung nach Vorkommen: Jede Anlagenzeile der linken Liste bleibt an ihrem Platz, erhält höchstens eine Quellmenge und zeigt weiterhin sichtbar an, wenn das passende Vorkommen fehlt.

Quellen