Zum Inhalt springen

Google Sheets-Kochbuch: Tabellen vergleichen

Links hinzufügen
Aus Wikibooks


<<<Zurück
 ↑ Inhaltsverzeichnis ↑ Weiter>>>

Hier geht es darum herauszufinden, ob zwei Tabellen gleiche Einträge enthalten. Als gleich betrachten wir den Eintrag, wenn ein Primärschlüssel in beiden Tabellen enthalten ist. Der Primärschlüssel muss entsprechend eindeutig sein. Dass der Primärschlüssel eindeutig ist, muss auf andere Weise sichergestellt werden (so könnte ich diese Liste aus einer Datenbank erhalten haben).

Neben den hier vorgestellten Methoden können Sie auch noch bedingte Formatierung verwenden.

Wir haben zwei Tabellen:

A1:C6
Name Ort eindeutige ID
Peter Halle 1 3892
Marek Halle 1 3810
Nasim Halle 1 3280
Peter Halle 2 2381
Esra Halle 2 4839
A9:D13
e-Mail Telefon Passwort eindeutige ID
a@b.de 13-20 **** 4839
b@b.de 13-19 **** 3810
c@b.de 13-20 **** 7839
d@b.de 13-20 **** 2381

Wir haben also IDs in Tabelle 1 die es in Tabelle 2 nicht gibt und andersherum. Die IDs sind auch nicht sortiert.

Arbeit mit VLOOKUP

[Bearbeiten]

Eine typische Aufgabe für VLOOKUP (SVERWEIS).

  • =IFERROR(VLOOKUP(C1;$D$9:$D$13;1;false))
    • Diese Formel kommt in D1, und wird dann nach unten kopiert.
    • Wir suchen den Wert in Zelle C1 im Bereich D9:D13. Wir lassen uns die erste Spalte ausgeben (wir haben sowieso nur eine), die Daten sind nicht sortiert.
    • Da sich der Datenbereich in dem wir suchen nicht ändert, verwenden wir einen absoluten Zellbezug.
    • Einen Fehler - es wurde der Wert nicht gefunden - fangen wir mit IFERROR ab.

VLOOKUP hat zwei "Probleme":

  1. Der zu suchende Wert muss in der ersten Spalte des Suchbereiches stehen.
    • Hier muss man dann evtl. die Tabelle erst umsortieren.
  2. Doppelte Werte werden nicht gefunden, nach dem ersten gefundenen Wert bricht die Suche ab.

Arbeit mit INDEX und MATCH (VERGLEICH)

[Bearbeiten]
  • =IFERROR(INDEX($A$9:$D$13;MATCH(C1;$D$9:$D$13;0);MATCH($C$1;$A$9:$D$9;0)))
    • INDEX: Es werden Werte aus dem Zellbereich $A$9:$D$13 zurückgegeben.
    • Die Zeile wird über MATCH(C1;$D$9:$D$13;0) gefunden, beim Kopieren der Werte nach unten ändert sich der relative Zellbezug, und es wird nach der entsprechenden ID gesucht.
    • Die Spalte wird über MATCH($C$1;$A$9:$D$9;0) gefunden, wir suchen die Spalte mit der Spaltenüberschrift "eindeutige ID".
    • Ist im Ergebnis identisch mit dem oberen VLOOKUP
    • Der Suchbereich kann jetzt in einer beliebigen Spalte stehen.
    • Wir können uns eine beliebige andere Spalte ausgeben lassen.
  • =IFERROR(INDEX($A$9:$D$13;MATCH(D1;$D$9:$D$13;0);MATCH("Telefon";$A$9:$D$9;0)))
    • Hier suchen wir nach der Zeile, in der die ID übereinstimmt, lassen uns dann die Spalte mit Telefonnummer ausgeben.
A1:D6 - INDEX und MATCH
Name Ort eindeutige ID =IFERROR(INDEX($A$9:$D$13;MATCH(C1;$D$9:$D$13;0);MATCH("Telefon";$A$9:$D$9;0)))
Peter Halle 1 3892 =IFERROR(INDEX($A$9:$D$13;MATCH(C2;$D$9:$D$13;0);MATCH("Telefon";$A$9:$D$9;0)))
Marek Halle 1 3810 =IFERROR(INDEX($A$9:$D$13;MATCH(C3;$D$9:$D$13;0);MATCH("Telefon";$A$9:$D$9;0)))
Nasim Halle 1 3280 =IFERROR(INDEX($A$9:$D$13;MATCH(C4;$D$9:$D$13;0);MATCH("Telefon";$A$9:$D$9;0)))
Peter Halle 2 2381 =IFERROR(INDEX($A$9:$D$13;MATCH(C5;$D$9:$D$13;0);MATCH("Telefon";$A$9:$D$9;0)))
Esra Halle 2 4839 =IFERROR(INDEX($A$9:$D$13;MATCH(C6;$D$9:$D$13;0);MATCH("Telefon";$A$9:$D$9;0)))



Bei doppelten Einträgen in der zweiten Tabelle - die ja fehlerhaft wären - würden wir aber keine Fehlermeldung bekommen.

Arbeit mit QUERY

[Bearbeiten]

Welche von den IDs aus Tabelle 1 ist in Tabelle 2 vorhanden? Dazu überprüfen wir, ob die ID aus C2/C3/C4 usw. in der 2. Tabelle vorhanden ist, und lassen sie uns ausgeben.

  • =QUERY($A$9:$D$13;"select D where D matches ('"&C2&"') limit 1 label D ''")
    • Diese Zeile muss in D2 und dann herunterkopiert werden.
A1:D6
Name Ort eindeutige ID ist ID in Tabelle 2?
Peter Halle 1 3892 #N/A
Marek Halle 1 3810 3810
Nasim Halle 1 3280 #N/A
Peter Halle 2 2381 2381
Esra Halle 2 4839 4839

Da die IDs 3892 und 3280 in Tabelle 2 nicht vorhanden sind, erhalten wir eine leere Ausgabe, die mit #N/A angezeigt wird.

Die Ausgabe wollen wir in der Form Wahr/Falsch haben, und keine Fehlermeldungen wegen nicht gefundener Werte.

  • =IFERROR(ISNUMBER(QUERY($A$9:$D$13;"select D where D matches ('"&C2&"') limit 1 label D ''"));false)
    • IFERROR() gibt FALSE aus, wenn das Ergebnis der QUERY-Funktion keine Zahl ist (z.B. ein Fehler).
A1:D6
Name Ort eindeutige ID ist ID in Tabelle 2?
Peter Halle 1 3892 FALSE
Marek Halle 1 3810 TRUE
Nasim Halle 1 3280 FALSE
Peter Halle 2 2381 TRUE
Esra Halle 2 4839 TRUE

Genauso könnten wir dann mit Tabelle 2 verfahren.

Um die beiden Tabellen miteinander zu verbinden, können wir auch QUERY verwenden.

  • =IFERROR(QUERY($A$9:$D$13;"select A,B,C where D matches ('"&C2&"') limit 1 label A '', B '', C ''"))
    • Hier fügen wir die Werte der zweiten Tabelle in den Zeilen ein, in denen die IDs übereinstimmen.
A1:F6
Name Ort eindeutige ID e-Mail Telefon Passwort
Peter Halle 1 3892
Marek Halle 1 3810 b@b.de 13-19 ****
Nasim Halle 1 3280
Peter Halle 2 2381 d@b.de 13-20 ****
Esra Halle 2 4839 a@b.de 13-20 ****

Mehrfache Einträge finden

[Bearbeiten]

Wenn Einträge in der 2. Tabelle mehrfach vorhanden sind, und wir alle Einträge finden wollen, muss man etwas anders vorgehen. Hier liegt also ein Fehler in den Daten vor, und wir wollen diesen finden.

A9:D14 - mit Fehler
e-Mail Telefon Passwort eindeutige ID
a@b.de 13-20 **** 4839
b@b.de 13-19 **** 3810
c@b.de 13-20 **** 7839
d@b.de 13-20 **** 2381
e@b.de 13-21 **** 3810
  • =IFERROR(QUERY($A$9:$D$14;"select count(D) where D matches ('"&C2&"') label count(D)''"))
    • Wir zählen jetzt die Anzahl der Treffer und lassen uns diese ausgeben.
    • Wenn die "eindeutige ID" mehr als einmal in der zweiten Tabelle vorkommt, müssen wir diesen Fehler korrigieren.

Jetzt wissen wir, dass die ID zweimal vorkommt, aber noch nicht, wo.

Wir erweitern die zweite Tabelle mit der Angabe der jeweiligen Reihe:

A9:D14 - mit Fehler - mit Zeilennummer
e-Mail Telefon Passwort eindeutige ID Zeilennummer
a@b.de 13-20 **** 4839 =row()
b@b.de 13-19 **** 3810 =row()
c@b.de 13-20 **** 7839 =row()
d@b.de 13-20 **** 2381 =row()
e@b.de 13-21 **** 3810 =row()

Die erste Tabelle wird auch erweitert um die Anzahl der vorkommenden IDs und die Angabe der Zeilennummern.

  • =TRANSPOSE(IFERROR(QUERY($A$9:$E$14;"select E where D matches ('"&C2&"') label E''")))
    • Man füllt jeweils die erste Zeile aus, und kopiert die Formel dann herunter.
    • Da mehrere Ergebnisse gefunden werden können, wird die Ergebnismatrix mit TRANSPOSE von einer Spalte in eine Zeile transponiert.
A1:F6 - Formeln
Name Ort eindeutige ID Anzahl IDs Zeile 1 Zeile 2
Peter Halle 1 3892 =IFERROR(QUERY($A$9:$E$14;"select count(D) where D matches ('"&C2&"') label count(D)''")) =TRANSPOSE(IFERROR(QUERY($A$9:$E$14;"select E where D matches ('"&C2&"') label E''")))
Marek Halle 1 3810 =IFERROR(QUERY($A$9:$E$14;"select count(D) where D matches ('"&C3&"') label count(D)''")) =TRANSPOSE(IFERROR(QUERY($A$9:$E$14;"select E where D matches ('"&C3&"') label E''")))
Nasim Halle 1 3280 =IFERROR(QUERY($A$9:$E$14;"select count(D) where D matches ('"&C4&"') label count(D)''")) =TRANSPOSE(IFERROR(QUERY($A$9:$E$14;"select E where D matches ('"&C4&"') label E''")))
Peter Halle 2 2381 =IFERROR(QUERY($A$9:$E$14;"select count(D) where D matches ('"&C5&"') label count(D)''")) =TRANSPOSE(IFERROR(QUERY($A$9:$E$14;"select E where D matches ('"&C5&"') label E''")))
Esra Halle 2 4839 =IFERROR(QUERY($A$9:$E$14;"select count(D) where D matches ('"&C6&"') label count(D)''")) =TRANSPOSE(IFERROR(QUERY($A$9:$E$14;"select E where D matches ('"&C6&"') label E''")))

Unsere Ergebnistabelle sieht dann so aus:

A1:F6 - Ausgabe
Name Ort eindeutige ID Anzahl IDs Zeile 1 Zeile 2
Peter Halle 1 3892
Marek Halle 1 3810 2 11 14
Nasim Halle 1 3280
Peter Halle 2 2381 1 13
Esra Halle 2 4839 1 10


Die Beispiele auf dieser Seite wurden getestet: 03/2022


↑ Inhaltsverzeichnis ↑
Index →
Abgerufen von „https://de.wikibooks.org/w/index.php?title=Google_Sheets-Kochbuch:_Tabellen_vergleichen&oldid=995674“