PDF herunterladen
PDF herunterladen
Eine der vielen Fähigkeiten von Microsoft Excel besteht darin, zwei Listen mit Daten zu vergleichen, übereinstimmende Daten in den Listen zu finden und zu erkennen, welche Daten nur in einer Liste gefunden werden. Dies ist hilfreich, wenn du finanzielle Daten vergleichst oder überprüfst, ob sich ein bestimmter Name in einer Datenbank befindet. Mit der Funktion VERGLEICH kannst du übereinstimmende oder nicht übereinstimmende Datensätze finden und markieren oder mit der Funktion ZÄHLENWENN eine bedingte Formatierung anwenden. In den folgenden Schritten zeigen wir dir, wie du mit den beiden Funktionen übereinstimmende Daten findest.
Vorgehensweise
Methode 1
Methode 1 von 2:
Mit der Funktion VERGLEICH übereinstimmende Daten finden
PDF herunterladen
-
Kopiere die Liste mit den Daten in ein Arbeitsblatt. Excel kann mit verschiedenen Arbeitsblättern in einer Arbeitsmappe arbeiten oder mit mehreren Arbeitsmappen, aber das Vergleichen von Listen ist einfacher, wenn du die Daten in ein einzelnes Arbeitsblatt kopierst.
-
Gib jeder Liste eine eindeutige ID. Wenn deine beiden Listen keine eigenen haben, musst du vielleicht eine weitere Spalte in jede Liste einfügen, die die Daten in Excel identifiziert, so dass Excel erkennen kann, ob ein Eintrag in einer Liste einen Zusammenhang zu einem Eintrag in der anderen Liste hat. Die Art dieser ID hängt von der Art der Daten ab, die du vergleichen möchtest. Für jede Liste brauchst du eine ID.
- Bei finanziellen Daten in Bezug auf einen gegebenen Zeitraum, z. B. bei Steuerdaten, könnte dies die Beschreibung eines Vermögensgegenstands sein, das Datum, an dem der Vermögensgegenstand angeschafft wurde, oder beides. In einigen Fällen kann ein Eintrag durch eine Codenummer identifiziert werden. Wenn allerdings nicht für beide Listen das gleiche System angewandt wird, kann diese ID Übereinstimmungen liefern, wo es keine gibt, oder bestehende Übereinstimmungen ignorieren.
- In einigen Fällen kannst du Einträge aus einer Liste mit Einträgen aus einer anderen Liste kombinieren, um eine ID zu erstellen, z. B. die Beschreibung eines Assets und das Jahr der Anschaffung. Um eine solche ID zu erstellen, kombinierst du Daten aus zwei oder mehr Zellen mit dem kaufmännischen Und (&). Um eine Beschreibung in Zelle F3 mit einem Datum in Zelle G3 mit einem Leerzeichen dazwischen zu kombinieren, würdest du in eine andere Zelle, z. B. E3, die Formel =F3&" "&G3 eingeben. Wenn du nur das Jahr in der ID angeben möchtest (weil in einer Liste das komplette Datum und in der anderen nur das Jahr verwendet wird), würdest du die Funktion JAHR mit angeben, indem du dies eingibst: =F3&" "&JAHR(G3).
- Wenn du die Formel erstellt hast, kannst du sie in alle anderen Zellen der ID-Spalte kopieren, indem du die Zelle mit der Formel markierst und das Füllkästchen über die anderen Zellen der Spalte ziehst, in die du die Formel kopieren möchtest. Wenn du die Maustaste loslässt, werden alle diese Zellen mit der Formel gefüllt und die Zellverweise werden entsprechend angepasst.
-
Standardisiere Daten wo möglich. Während dein Verstand weiß, dass "Inc." und "Incorporated" das Gleiche bedeuten, kann Excel das nicht, außer du lässt das eine Wort in das andere umwandeln. Du kannst auch Werte wie 11.950 € und 11.999,95 € als nah genug beieinander für eine Übereinstimmung ansehen, aber Excel wird dies nicht tun, außer du sagst es ihm.
- Du kannst Abkürzungen behandeln, z. B. "Co" für "Company" und "Inc" für "Incorporated", indem du mit der Funktion LINKS die restlichen Zeichen abschneidest. Mit anderen Abkürzungen wie z. B. "Assn" für "Association" kannst du am besten umgehen, indem du einen Style Guide für die Dateneingabe erstellst und dann ein Programm schreibst, das nicht richtige Formate sucht und korrigiert.
- Bei Zahlenreihen wie z. B. Postleitzahlen kannst du ebenfalls wieder die Funktion LINKS anwenden, um z. B. nach Regionen zu filtern. Damit Excel numerische Werte erkennt, die nahe beieinander liegen, aber nicht gleich sind, kannst du sie mit der Funktion RUNDEN auf die gleiche Zahl runden und dann Übereinstimmungen finden.
- Zusätzliche Leerzeichen, z. B. zwei statt einem Leerzeichen zwischen Wörtern kannst du mit der Funktion GLÄTTEN beseitigen.
-
Erstelle Spalten für die Vergleichsformel. Genauso wie du Spalten für die IDs der Listen erstellen musstest, musst du Spalten für die Formel erstellen, die den Vergleich für dich übernimmt. Du brauchst für jede Liste eine Spalte.
- Du kannst diese Spalten beschriften mit "Fehlt?" oder etwas in der Art.
-
Gib in jede Zelle die Vergleichsformel ein. Hierfür verwendest du die in die ISTNV verschachtelte Funktion VERGLEICH.
- Die Formel sieht so aus: =ISTNV(VERGLEICH(G3,$L$3:$L$14,FALSE)). Hier wird eine Zelle der ID-Spalte der ersten Liste mit allen IDs in der zweiten Liste verglichen, um zu sehen, ob es Übereinstimmungen gibt. Wenn nein, dann fehlt ein Eintrag und in dieser Zelle wird "TRUE" (wahr) angezeigt. Wenn ja, dann gibt es den Datensatz und das Wort "FALSE" (falsch) wird angezeigt.
- Du kannst die Formel so in die restlichen Zellen kopieren wie die ID-Formel oben. In diesem Fall verändert sich nur der Zellverweis für die ID-Zelle, da ein Dollarzeichen vor den Spalten- und Zeilenverweisen für die ersten und letzten Zellen in der Liste der zweiten Zell-IDs sie zu absoluten Referenzen macht.
- Du kannst die Vergleichsformel für die erste Liste in die erste Zelle der Spalte für die zweite Liste kopieren. Dann musst du die Zellverweise bearbeiten, so dass "G3" ersetzt wird durch den Verweis auf die erste ID-Zelle der zweiten Liste und "$L$3:$L$14" durch die erste und letzte ID-Zelle der zweiten Liste. Lasse die Dollarzeichen und Doppelpunkte unberührt. Du kannst dann diese bearbeitete Formel in die restlichen Zellen in der Vergleichsreihe der zweiten Liste kopieren.
-
Sortiere die Listen wenn nötig, um leichter Werte erkennen zu können, die nicht übereinstimmen. Wenn deine Listen groß sind, musst du sie vielleicht sortieren, um alle nicht übereinstimmenden Werte zusammenzubringen. Die Anleitung in den folgenden Unterschritten wandelt die Formeln in Werte um, um Fehler zu vermeiden, und wenn deine Listen groß sind, wird auch eine lange Berechnungsdauer vermieden.
- Fahre mit der Maus zum Markieren über alle Zellen in einer Liste.
- Wähle "Kopieren" (in Excel 2003 im Menü "Bearbeiten" oder in Excel 2007 oder 2010 in der Gruppe Zwischenablage im Start-Ribbon).
- Wähle "Inhalte einfügen" (in Excel 2003 im Menü "Bearbeiten" oder in Excel 2007 oder 2010 im Dropdown-Menü "Einfügen" in der Gruppe Zwischenablage im Start-Ribbon).
- Wähle aus der Liste "Werte" aus. Klicke zum Schließen des Dialogs auf OK.
- Wähle "Sortieren". In Excel 2003 findest du dies im Menü "Daten", in Excel 2007 oder 2010 in der Gruppe Sortieren und Filtern im Daten-Ribbon.
- Wähle aus dem Dialog "Sortieren nach" den Eintrag "Zeilenüberschrift" aus unter "Meine Daten haben", wähle "Fehlt?" (oder den Namen, den du deiner neuen Spalte gegeben hast) aus und klicke auf OK.
- Wiederhole diese Schritte für die andere Liste.
-
Vergleiche die nicht übereinstimmenden Werte selbst, um zu sehen, warum sie nicht übereinstimmen. Wie bereits erwähnt dient Excel dazu, nach genauen Übereinstimmungen zu suchen, es sei denn, du hast es so eingestellt, dass es nach groben Übereinstimmungen sucht. Die Nicht-Übereinstimmung könnte einfach aus einem Fehler bei der Umwandlung von Buchstaben oder Zahlen kommen. Sie könnte außerdem eine unabhängige Überprüfung erfordern, z. B. dass du schauen musst, ob die aufgeführten Assets überhaupt gemeldet werden müssen.Werbeanzeige
-
Kopiere die Datenlisten in ein einzelnes Arbeitsblatt.
-
Entscheide, in welcher Liste du übereinstimmende oder nicht übereinstimmende Datensätze markieren möchtest. Wenn du Datensätze nur in einer Liste markieren möchtest, wirst du wahrscheinlich die Werte markieren wollen, die es nur in dieser Liste gibt, also Einträge, für die es keine übereinstimmenden Einträge in der anderen Liste gibt. Wenn du Einträge in beiden Listen markieren möchtest, solltest du übereinstimmende Datensätze markieren. Für dieses Beispiel werden wir davon ausgehen, dass die erste Liste sich von Zelle G3 bis G14 erstreckt und die zweite von L3 bis L14.
-
Wähle die Einträge in der Liste, in der du einmalige oder übereinstimmende Daten markieren möchtest. Wenn du übereinstimmende Einträge in beiden Listen markieren möchtest, musst du die Listen nacheinander auswählen und auf jede Liste die Vergleichsformel anwenden (siehe nächster Schritt).
-
Wende die entsprechende Vergleichsformel an. Hierfür musst du du den Dialog für die bedingte Formatierung in Excel aufrufen. In Excel 2003 wählst du dies aus dem Menü "Format", in Excel 2007 und 2010 klickst du im Start-Ribbon in der Stile-Gruppe auf den Button für die bedingte Formatierung. Wähle den Typ der Regel als "Formel" und gib deine Formel im Feld "Beschreibung der Regel ändern" ein.
- Wenn du Einträge markieren möchtest, die es nur in der ersten Liste gibt, würde die Formel so aussehen: =ZÄHLENWENN($L$3:$L$14,G3=0). Hierbei werden der Zellbereich der zweiten Liste als absolute Werte und der Verweis auf die erste Zelle der ersten Liste als relativer Wert behandelt.
- Wenn du Einträge markieren möchtest, die es nur in der zweiten Liste gibt, würde die Formel so aussehen: =ZÄHLENWENN($G$3:$G$14,L3=0)". Hier werden der Zellbereich der ersten Liste als absolute Werte und der Verweis auf die erste Zelle der zweiten Liste als relativer Wert behandelt.
- Wenn du in jeder Liste die Einträge markieren möchtest, die auch in der anderen Liste gefunden werden, brauchst du zwei Formeln - eine für die erste und eine für die zweite Liste. Die Formel für die erste Liste lautet: =ZÄHLENWENN($L$3:$L$14,G3>0), die Formel für die zweite Liste: =ZÄHLENWENN($G$3:$G$14,L3>0). Wie bereits erwähnt wählst du die erste Liste aus, wendest die Formel an, markierst dann die zweite Liste und wendest die Formel an.
- Wende die gewünschte Formatierung für die markierten Datensätze an. Klicke zum Schließen des Dialogs auf OK.
Werbeanzeige
Tipps
- Statt eines Zellverweises mit der ZÄHLENWENN Funktion und der bedingten Formatierung kannst du auch einen Wert eingeben, nach dem gesucht werden soll, und eine oder mehrere Listen bei Instanzen dieses Werts markieren.
- Um die Vergleichsformeln zu vereinfachen, kannst du Namen für deine Listen erstellen, z. B. "Liste 1" und "Liste 2". Wenn du dann die Formeln schreibst, können diese Namen die in den Beispielen oben verwendeten absoluten Zellbereiche ersetzen.
Werbeanzeige
Referenzen
Über dieses wikiHow
Diese Seite wurde bisher 26.919 mal abgerufen.
Werbeanzeige
Melde dich für den kostenlosen wikiHow Newsletter an!
Du erhältst hilfreiche Tipps und Anleitungen jede Woche direkt in deine Inbox.
Registriere mich!
