Verschiedene Methoden zum Abgleichen von Daten in Excel
Es gibt verschiedene Methoden, um Daten in Excel abzugleichen. Wenn wir die Daten in derselben Spalte abgleichen möchten, nehmen wir an, wir möchten auf Duplizität prüfen, können wir die bedingte Formatierung auf der Registerkarte "Startseite" verwenden oder wenn wir die Daten in zwei oder mehr abgleichen möchten Bei verschiedenen Spalten können wir bedingte Funktionen wie if-Funktionen verwenden.
- Methode 1 - Verwenden der Vlookup-Funktion
- Methode 2 - Verwenden der Index + Match-Funktion
- Methode 3 - Erstellen Sie Ihren eigenen Suchwert
Lassen Sie uns nun jede der Methoden im Detail diskutieren
# 1 - Daten mit der VLOOKUP-Funktion abgleichen
VLOOKUP wird nicht nur verwendet, um die erforderlichen Informationen aus der Datentabelle abzurufen. Vielmehr kann es auch als Abstimmungsinstrument verwendet werden. Wenn es um die Abstimmung oder den Abgleich der Daten geht, führt die VLOOKUP-Formel die Tabelle an.
Schauen Sie sich zum Beispiel die folgende Tabelle an.
Wir haben hier zwei Datentabellen, die erste ist Daten 1 und die zweite ist Daten 2.
Jetzt müssen wir abgleichen, ob die Daten in den beiden Tabellen übereinstimmen oder nicht. Die allererste Möglichkeit, die Daten abzugleichen, ist die SUMME-Funktion in Excel zu zwei Tabellen, um den Gesamtumsatz zu erhalten.
Daten 1 - Tabelle
Daten 2 - Tabelle
Ich habe die SUMME-Funktion sowohl für die Spalte "Verkaufsbetrag" der Tabelle angewendet. Zu Beginn selbst haben wir den Unterschied in den Werten erhalten. Tabelle Daten 1 mit dem Gesamtumsatz von 2,16,214 und Tabelle 2 mit dem Gesamtumsatz von 2,10,214 .
Jetzt müssen wir dies im Detail untersuchen. Wenden wir also die VLOOKUP-Funktion für jedes Datum an.
Wählen Sie das Tabellenarray als Daten 1- Bereich aus.
Wir benötigen die Daten aus der zweiten Spalte, und der Suchbereich ist FALSE, dh Exact Match.
Die Ausgabe ist unten angegeben:
Ziehen Sie in der nächsten Zelle den ursprünglichen Wert vom Ankunftswert ab.
Nach Abzug erhalten wir das Ergebnis als Null.
Kopieren Sie nun die Formel und fügen Sie sie in alle Zellen ein, um die Varianzwerte zu erhalten.
In Zelle G6 und G12 haben wir die Unterschiede erhalten.
In Daten 1 haben wir 12104 für das Datum 04-Mar-2019 und in Daten 2 haben wir 15104 für dasselbe Datum, also gibt es einen Unterschied von 3000.
In ähnlicher Weise haben wir für das Datum 18-Mar-2019 in Daten 1 19351 und in Daten 2 10351, sodass die Differenz 9000 beträgt.
# 2 - Daten mit INDEX + MATCH-Funktion abgleichen
Für die gleichen Daten können wir die INDEX + MATCH-Funktion verwenden. Wir können dies als Alternative zur VLOOKUP-Funktion verwenden.
Die INDEX-Funktion wird verwendet, um den Wert aus der ausgewählten Spalte basierend auf der angegebenen Zeilennummer abzurufen. Um die Zeilennummer anzugeben, müssen wir die MATCH-Funktion verwenden, die auf dem LOOKUP-Wert basiert.
Öffnen Sie die INDEX-Funktion in der F3-Zelle.
Wählen Sie das Array als Ergebnisspaltenbereich aus, dh B2 bis B14.
Um die Zeilennummer zu erhalten, öffnen Sie jetzt die MATCH-Funktion als nächstes Argument.
Wählen Sie den Suchwert als D3-Zelle aus.
Wählen Sie als Nächstes das Lookup-Array als Spalte Verkaufsdatum in Daten 1 aus.
Wählen Sie im Übereinstimmungstyp "0 - Genaue Übereinstimmung ".
Schließen Sie zwei Klammern und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
Dies ergibt auch das gleiche Ergebnis wie nur VLOOKUP. Da wir dieselben Daten verwendet haben, haben wir die Zahlen so wie sie sind
# 3 - Erstellen Sie Ihren eigenen Suchwert
Jetzt haben wir gesehen, wie Daten mit Excel-Funktionen abgeglichen werden. Jetzt werden wir die verschiedenen Szenarien in Echtzeit sehen. Schauen Sie sich für dieses Beispiel die folgenden Daten an.
In den obigen Daten haben wir zonenweise und datumsbezogene Verkaufsdaten, wie oben gezeigt. Wir müssen den Datenabgleich erneut durchführen. Wenden wir die VLOOKUP-Funktion wie im vorherigen Beispiel an.
Wir haben viele Abweichungen. Lassen Sie uns jeden Fall von Fall zu Fall untersuchen.
In Zelle I5 haben wir die Varianz von 8300. Schauen wir uns die Haupttabelle an.
Obwohl der Wert der Haupttabelle 12104 ist, haben wir den Wert 20404 von der VLOOKUP-Funktion erhalten. Der Grund dafür ist, dass VLOOKUP den Wert des zuerst gefundenen Suchwerts zurückgeben kann.
In diesem Fall ist unser Suchwert ein Datum, dh der 20. März 2019. In der obigen Zelle für die Nordzone zum selben Datum haben wir den Wert 20404, daher hat VLOOKUP diesen Wert auch für die Ostzone zurückgegeben.
Um dieses Problem zu beheben, müssen wir eindeutige Suchwerte erstellen. Kombinieren Sie Zone, Datum und Verkaufsbetrag in Daten 1 und Daten 2.
Daten 1 - Tabelle
Daten 2 - Tabelle
Jetzt haben wir für jede Zone einen eindeutigen Wert mit dem kombinierten Wert aus Zone, Verkaufsdatum und Verkaufsbetrag erstellt.
Verwenden Sie diese eindeutigen Werte, um die VLOOKUP-Funktion anzuwenden.
Wenden Sie die Formel auf alle Zellen an. Wir werden die Varianz von Null in allen Zellen erhalten.
Auf diese Weise können wir mithilfe von Excel-Funktionen die Daten abgleichen und die Abweichungen ermitteln. Bevor wir die Formel anwenden, müssen wir uns die Duplikate im Suchwert ansehen, um eine genaue Abstimmung zu erzielen. Das obige Beispiel ist die beste Darstellung von doppelten Werten im Suchwert. In solchen Szenarien müssen wir unsere eigenen eindeutigen Suchwerte erstellen und zum Ergebnis gelangen.
