Eine der vielen Funktionen von Microsoft Excel ist die Möglichkeit, zwei Datenlisten zu vergleichen, Übereinstimmungen zwischen den Listen zu identifizieren und zu identifizieren, welche Elemente in nur einer Liste gefunden werden. Dies ist nützlich, wenn Sie Finanzdatensätze vergleichen oder überprüfen, ob ein bestimmter Name in einer Datenbank enthalten ist. Sie können die MATCH-Funktion verwenden, um übereinstimmende oder nicht übereinstimmende Datensätze zu identifizieren und zu markieren, oder Sie können die Konditionierungsformatierung mit der ZÄHLENWENN-Funktion verwenden. In den folgenden Schritten erfahren Sie, wie Sie jeden verwenden, um Ihre Daten abzugleichen.
Schritte
Methode 1 von 2: Identifizieren von Datensätzen mit der MATCH-Funktion
Schritt 1. Kopieren Sie die Datenlisten auf ein einzelnes Arbeitsblatt
Excel kann mit mehreren Arbeitsblättern in einer einzelnen Arbeitsmappe oder mit mehreren Arbeitsmappen arbeiten, aber Sie können die Listen einfacher vergleichen, wenn Sie deren Informationen auf ein einzelnes Arbeitsblatt kopieren.
Schritt 2. Geben Sie jedem Listenelement eine eindeutige Kennung
Wenn Ihre beiden Listen keine gemeinsame Methode zur Identifizierung haben, müssen Sie möglicherweise jeder Datenliste eine zusätzliche Spalte hinzufügen, die dieses Element für Excel identifiziert, damit es sehen kann, ob ein Element in einer bestimmten Liste mit einem Element verknüpft ist in der anderen Liste. Die Art dieser Kennung hängt von der Art der Daten ab, die Sie abgleichen möchten. Sie benötigen für jede Spaltenliste eine Kennung.
- Bei Finanzdaten, die mit einem bestimmten Zeitraum verknüpft sind, beispielsweise bei Steuerunterlagen, kann dies die Beschreibung eines Vermögenswerts, das Datum des Erwerbs des Vermögenswerts oder beides sein. In einigen Fällen kann ein Eintrag mit einer Codenummer identifiziert werden; Wenn jedoch nicht dasselbe System für beide Listen verwendet wird, kann dieser Bezeichner Übereinstimmungen erstellen, wo keine vorhanden sind, oder Übereinstimmungen ignorieren, die vorgenommen werden sollten.
- In einigen Fällen können Sie Artikel aus einer Liste nehmen und sie mit Artikeln aus einer anderen Liste kombinieren, um eine Kennung zu erstellen, z. Um einen solchen Bezeichner zu erstellen, verketten (hinzufügen, kombinieren) Sie Daten aus zwei oder mehr Zellen mit dem kaufmännischen Und (&). Um eine Artikelbeschreibung in Zelle F3 mit einem Datum in Zelle G3 zu kombinieren, getrennt durch ein Leerzeichen, geben Sie die Formel '=F3&" "&G3" in eine andere Zelle in dieser Zeile ein, z. B. E3. Wenn Sie nur das Jahr in die Kennung aufnehmen möchten (da eine Liste vollständige Datumsangaben und die andere nur Jahre verwendet), würden Sie die Funktion JAHR einschließen, indem Sie stattdessen '=F3&" "&YEAR(G3)" in Zelle E3 eingeben. (Fügen Sie die einfachen Anführungszeichen nicht ein; sie dienen nur der Veranschaulichung des Beispiels.)
- Nachdem Sie die Formel erstellt haben, können Sie sie in alle anderen Zellen der Bezeichnerspalte kopieren, indem Sie die Zelle mit der Formel auswählen und den Ausfüllpunkt über die anderen Zellen der Spalte ziehen, in die Sie die Formel kopieren möchten. Wenn Sie die Maustaste loslassen, wird jede Zelle, über die Sie gezogen haben, mit der Formel gefüllt, wobei die Zellbezüge an die entsprechenden Zellen in derselben Zeile angepasst werden.
Schritt 3. Daten nach Möglichkeit standardisieren
Während der Verstand erkennt, dass "Inc." und "Inkorporiert" bedeuten dasselbe, Excel nicht, es sei denn, Sie formatieren das eine oder andere Wort neu. Ebenso können Sie Werte wie 11, 950 und 11, 999,95 als nahe genug betrachten, aber Excel wird dies nicht tun, es sei denn, Sie sagen es.
- Sie können mit einigen Abkürzungen umgehen, wie "Co" für "Firma" und "Inc" für "Incorporated, indem Sie die LEFT-String-Funktion verwenden, um die zusätzlichen Zeichen zu kürzen. Andere Abkürzungen wie "Assn" für "Association" sind möglicherweise am besten behandelt, indem man einen Styleguide für die Dateneingabe erstellt und dann ein Programm schreibt, um falsche Formate nachzuschlagen und zu korrigieren.
- Bei Zahlenfolgen, wie z. B. Postleitzahlen, bei denen einige Einträge das Suffix PLZ+4 enthalten und andere nicht, können Sie wieder die Zeichenfolgenfunktion LINKS verwenden, um nur die primären Postleitzahlen zu erkennen und abzugleichen. Damit Excel nahe beieinander liegende, aber nicht gleiche numerische Werte erkennt, können Sie mit der Funktion ROUND nahe beieinanderliegende Werte auf dieselbe Zahl runden und abgleichen.
- Zusätzliche Leerzeichen, wie z. B. das Eingeben von zwei Leerzeichen zwischen Wörtern anstelle von einem, können mithilfe der TRIM-Funktion entfernt werden.
Schritt 4. Erstellen Sie Spalten für die Vergleichsformel
Genauso wie Sie Spalten für die Listenbezeichner erstellen mussten, müssen Sie Spalten für die Formel erstellen, die den Vergleich für Sie durchführt. Sie benötigen eine Spalte für jede Liste.
Sie sollten diese Spalten mit etwas wie "Fehlend?" beschriften
Schritt 5. Geben Sie die Vergleichsformel in jede Zelle ein
Für die Vergleichsformel verwenden Sie die MATCH-Funktion, die in einer anderen Excel-Funktion, ISNA, verschachtelt ist.
- Die Formel hat die Form "=ISNA(MATCH(G3, $L$3:$L$14, FALSE))", wobei eine Zelle der Bezeichnerspalte der ersten Liste mit jedem der Bezeichner in der zweiten Liste verglichen wird um sehen, ob es zu einem von ihnen passt. Wenn es nicht übereinstimmt, fehlt ein Datensatz und das Wort "TRUE" wird in dieser Zelle angezeigt. Wenn sie übereinstimmt, ist der Datensatz vorhanden und das Wort "FALSE" wird angezeigt. (Geben Sie bei der Eingabe der Formel keine Anführungszeichen ein.)
- Sie können die Formel auf die gleiche Weise in die verbleibenden Zellen der Spalte kopieren, wie Sie die Zellbezeichnerformel kopiert haben. In diesem Fall ändert sich nur der Zellbezug für die Bezeichnerzelle, da das Setzen der Dollarzeichen vor den Zeilen- und Spaltenbezügen für die erste und letzte Zelle in der Liste der zweiten Zellbezeichner absolute Bezüge macht.
- Sie können die Vergleichsformel der ersten Liste in die erste Zelle der Spalte der zweiten Liste kopieren. Sie müssen dann die Zellbezüge so bearbeiten, dass "G3" durch die Referenz für die erste Bezeichnerzelle der zweiten Liste und "$L$3:$L$14" durch die erste und letzte Bezeichnerzelle der ersetzt wird zweite Liste. (Lassen Sie die Dollarzeichen und den Doppelpunkt weg.) Sie können diese bearbeitete Formel dann in die verbleibenden Zellen der Vergleichszeile der zweiten Liste kopieren.
Schritt 6. Sortieren Sie die Listen, um bei Bedarf nicht übereinstimmende Werte leichter zu erkennen
Wenn Ihre Listen groß sind, müssen Sie sie möglicherweise sortieren, um alle nicht übereinstimmenden Werte zusammenzufassen. Die Anweisungen in den folgenden Unterschritten wandeln die Formeln in Werte um, um Neuberechnungsfehler zu vermeiden und bei großen Listen eine lange Neuberechnungszeit zu vermeiden.
- Ziehen Sie Ihre Maus über alle Zellen in einer Liste, um sie auszuwählen.
- Wählen Sie Kopieren aus dem Menü Bearbeiten in Excel 2003 oder aus der Gruppe Zwischenablage des Menübands Home in Excel 2007 oder 2010.
- Wählen Sie Inhalte einfügen aus dem Menü Bearbeiten in Excel 2003 oder aus der Dropdown-Schaltfläche Einfügen in der Gruppe Zwischenablage des Menübands Home von Excel 2007 oder 2010 aus.
- Wählen Sie „Werte“aus der Liste „Einfügen als“im Dialogfeld „Inhalte einfügen“. Klicken Sie auf OK, um das Dialogfeld zu schließen.
- Wählen Sie Sortieren aus dem Menü Daten in Excel 2003 oder der Gruppe Sortieren und Filtern des Menübands Daten in Excel 2007 oder 2010 aus.
- Wählen Sie im Dialogfeld "Sortieren nach" aus der Liste "Mein Datenbereich hat" die Option "Kopfzeile", wählen Sie "Fehlend?" (oder den Namen, den Sie tatsächlich der Vergleichsspaltenüberschrift gegeben haben) und klicken Sie auf OK.
- Wiederholen Sie diese Schritte für die andere Liste.
Schritt 7. Vergleichen Sie die nicht übereinstimmenden Elemente visuell, um zu sehen, warum sie nicht übereinstimmen
Wie bereits erwähnt, ist Excel so konzipiert, dass es nach genauen Datenübereinstimmungen sucht, es sei denn, Sie richten es so ein, dass nach ungefähren Übereinstimmungen gesucht wird. Ihre Nichtübereinstimmung kann so einfach sein wie eine versehentliche Vertauschung von Buchstaben oder Ziffern. Es könnte auch etwas sein, das eine unabhängige Überprüfung erfordert, z. B. die Überprüfung, ob aufgelistete Vermögenswerte überhaupt gemeldet werden müssen.
Methode 2 von 2: Bedingte Formatierung mit ZÄHLENWENN
Schritt 1. Kopieren Sie die Datenlisten auf ein einzelnes Arbeitsblatt
Schritt 2. Entscheiden Sie, in welcher Liste Sie übereinstimmende oder nicht übereinstimmende Datensätze hervorheben möchten
Wenn Sie Datensätze in nur einer Liste hervorheben möchten, möchten Sie wahrscheinlich die Datensätze hervorheben, die für diese Liste eindeutig sind. dh Datensätze, die nicht mit Datensätzen in der anderen Liste übereinstimmen. Wenn Sie Datensätze in beiden Listen hervorheben möchten, sollten Sie Datensätze hervorheben, die miteinander übereinstimmen. Für dieses Beispiel nehmen wir an, dass die erste Liste die Zellen G3 bis G14 und die zweite Liste die Zellen L3 bis L14 belegt.
Schritt 3. Wählen Sie die Elemente in der Liste aus, in denen Sie einzigartige oder übereinstimmende Elemente hervorheben möchten
Wenn Sie übereinstimmende Elemente in beiden Listen hervorheben möchten, müssen Sie die Listen einzeln auswählen und die Vergleichsformel (im nächsten Schritt beschrieben) auf jede Liste anwenden.
Schritt 4. Wenden Sie die entsprechende Vergleichsformel an
Dazu müssen Sie in Ihrer Excel-Version auf das Dialogfeld Bedingte Formatierung zugreifen. In Excel 2003 tun Sie dies, indem Sie im Menü Format die Option Bedingte Formatierung auswählen, während Sie in Excel 2007 und 2010 auf die Schaltfläche Bedingte Formatierung in der Gruppe Stile des Menübands Start klicken. Wählen Sie als Regeltyp "Formel" aus und geben Sie Ihre Formel in das Feld Regelbeschreibung bearbeiten ein.
- Wenn Sie Datensätze hervorheben möchten, die für die erste Liste eindeutig sind, lautet die Formel "=COUNTIF($L$3:$L$14, G3=0)", wobei der Zellbereich der zweiten Liste als absolute Werte und die Referenz wiedergegeben wird als relativer Wert in die erste Zelle der ersten Liste. (Geben Sie nicht die engen Anführungszeichen ein.)
- Wenn Sie Datensätze hervorheben möchten, die für die zweite Liste eindeutig sind, lautet die Formel "=COUNTIF($G$3:$G$14, L3=0)", wobei der Zellbereich der ersten Liste als absolute Werte und die Referenz wiedergegeben wird als relativer Wert in die erste Zelle der zweiten Liste. (Geben Sie nicht die engen Anführungszeichen ein.)
- Wenn Sie die Datensätze in jeder Liste hervorheben möchten, die in der anderen Liste zu finden sind, benötigen Sie zwei Formeln, eine für die erste Liste und eine für die zweite. Die Formel für die erste Liste lautet "=ZÄHLENWENN($L$3:$L$14, G3>0)", während die Formel für die zweite Liste ist ZÄHLENWENN($G$3:$G$14, L3>0)". Wie bereits erwähnt, wählen Sie die erste Liste aus, um ihre Formel anzuwenden, und wählen Sie dann die zweite Liste aus, um ihre Formel anzuwenden.
- Wenden Sie die gewünschte Formatierung an, um die markierten Datensätze hervorzuheben. Klicken Sie auf OK, um das Dialogfeld zu schließen.
Video - Durch die Nutzung dieses Dienstes können einige Informationen an YouTube weitergegeben werden
Tipps
- Anstatt einen Zellbezug mit der bedingten Formatierungsmethode ZÄHLENWENN zu verwenden, können Sie einen zu suchenden Wert eingeben und eine oder mehrere Listen für Instanzen dieses Werts kennzeichnen.
- Um die Vergleichsformulare zu vereinfachen, können Sie Namen für Ihre Liste erstellen, z. B. "Liste1" und "Liste2". Beim Schreiben der Formeln können diese Listennamen dann die absoluten Zellbereiche ersetzen, die in den obigen Beispielen verwendet wurden.
