Wie werden Daten in Excel abgeglichen?

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

Sie können diese Match Data Excel-Vorlage hier herunterladen - Match Data Excel-Vorlage

# 1 - Daten mit der VLOOKUP-Funktion abgleichen

VLOOKUP wird nicht nur verwendet, um die erforderlichen Informationen aus der Datentabelle abzurufen, sondern kann auch als Abstimmungswerkzeug verwendet werden. Wenn es um die Abstimmung oder den Abgleich der Daten geht, führt die VLOOKUP-Formel die Tabelle an.

Ein Beispiel finden Sie in der folgenden Tabelle.

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 zwei Tabellen übereinstimmen oder nicht. Die allererste Möglichkeit, die Daten abzugleichen, ist die SUM-Funktion in Excel in 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 Wertunterschied festgestellt. Tabelle Daten 1 zeigt den Gesamtumsatz von 2,16,214 und Tabelle 2 zeigt den 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:

In der nächsten Zelle den ursprünglichen Wert mit dem Ankunftswert abziehen.

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.

In Daten 1 haben wir 12104 für das Datum 04-Mar-2019 und in Daten 2 haben wir 15104 für das gleiche 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.

INDEX-Funktion zum Abrufen des Werts aus der ausgewählten Spalte basierend auf der angegebenen Zeilennummer. 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.

Wählen Sie als Nächstes das Sucharray als Spalte Verkaufsdatum in Daten 1 aus.

Wählen Sie im Match-Typ "0 - Exact Match".

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 das unterschiedliche Szenario der 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 in 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 das Datum, dh der 20. März 2019. In der obigen Zelle für die Nordzone haben wir für dasselbe Datum den Wert 20404, daher hat VLOOKUP diesen Wert auch für die Ostzone zurückgegeben.

Um dieses Problem zu beheben, müssen eindeutige Suchwerte erstellt werden. 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.

Mit diesen eindeutigen Werten können Sie die VLOOKUP-Funktion anwenden.

Wenden Sie die Formel auf alle Zellen an. Wir erhalten die Varianz Null in allen Zellen.

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 zeigt am besten doppelte Werte im Suchwert. In solchen Szenarien müssen wir unsere eigenen eindeutigen Suchwerte erstellen und das Ergebnis ermitteln.