Excel – Verwenden von VLOOKUP, um zwei Listen zu vergleichen

Schritt 1: Es gibt einige Regeln, daher haben wir diese zuerst überprüft

Der Kunde hatte ein sehr einfaches Excel-Arbeitsblatt gesendet, in dem eine Liste seiner Zahlungsaufzeichnungen nach Bestellung, Rechnungsnummer, Betrag und Datum angezeigt wurde.

Der Administrator (nennen wir ihn Guy) erstellte dann einen Excel-Bericht aus dem Buchhaltungssystem seines Unternehmens. Wir haben dann beide Excel-Arbeitsblätter verwendet, um unseren Abgleich durchzuführen.

Bevor wir jedoch mit der VLOOKUP-Funktion beginnen konnten, erklärte ich die Feinheiten, Ihre Daten in ein Format zu bringen, das sicherstellt, dass die VLOOKUP funktioniert.

Excel benötigte Folgendes:

• In der Arbeitsmappe des Kunden und in der Arbeitsmappe von Guy musste für jeden Datensatz mindestens eine übereinstimmende Information vorhanden sein. Beispielsweise hatten beide Arbeitsmappen die Rechnungsnummer und die Bestellnummer für jeden Transaktionsdatensatz. Wir haben uns entschieden, die Rechnungsnummer in der Arbeitsmappe von Guy zu verwenden, um eine Übereinstimmung für dieselbe Rechnungsnummer in der Arbeitsmappe des Kunden zu finden. Diese Information wird als ‚Lookup-Wert‘ bezeichnet.
• Der Lookup-Wert muss eindeutig sein, d.h. er kann in der Kundenliste nicht mehrfach wiederholt werden. Zum Glück für Guy gab es nur einen Datensatz für jede Rechnung. In Unternehmen sind Rechnungs- und Bestellnummern fast immer eindeutig für eine einzelne Transaktion, sodass sie hervorragend als Nachschlagewerte verwendet werden können. Weitere Beispiele sind Produktcodes, Kunden- oder Kontonummern, Mitarbeiternummern oder Abteilungscodes. Es empfiehlt sich immer zu überprüfen, ob keine Duplikate vorhanden sind, bevor Sie Ihren VLOOKUP durchführen.
• Der Suchwert musste in der ersten Spalte der Liste in der Arbeitsmappe des Kunden stehen. Leider war es nicht. Es befand sich in der 2. Spalte (Spalte B), also haben wir die Spalte vor der P / O-Nr. ausgeschnitten und eingefügt. Spalte. Dies ist eine wichtige Regel bei der Verwendung der VLOOKUP-Funktion. Wenn sich der Nachschlagewert nicht in der ersten Spalte befindet, funktioniert die Suche nicht.

Nachdem wir uns für unseren Lookup-Wert entschieden und die Liste in der Arbeitsmappe des Kunden neu organisiert hatten, konnten wir unser VLOOKUP erstellen.

Schritt 2: Einfügen der VLOOKUP-Funktion

Das ‚V‘ in VLOOKUP steht für „vertikal“. Eine vertikale Suche wird verwendet, um nach bestimmten Daten in der ersten Spalte einer Datentabelle zu suchen. Sobald die Zeile mit den gesuchten Daten gefunden wurde, springt sie zu einer anderen Spalte in derselben Datentabelle und gibt Informationen daraus zurück.

Wir haben beschlossen, die VLOOKUP-Funktion in Guys Arbeitsmappe einzufügen. Auf diese Weise könnten wir die Funktion verwenden, um die Rechnungsnummer aus seiner Liste zu referenzieren und in der ersten Spalte der Kundenliste nach derselben Rechnungsnummer zu suchen. Wenn es eine Übereinstimmung fand, würde es dann den Betrag, den der Kunde gegen die Rechnungsnummer hatte, in die Zelle zurückgeben. Wir würden dann den Betrag von Guy und den Betrag des Kunden im selben Arbeitsblatt erhalten. Wir konnten dann leicht die beiden vergleichen und herausfinden, welche Transaktionen nicht übereinstimmten.

Wir haben eine neue Spaltenüberschrift mit dem Namen ‚Customer’s Record‘ erstellt und sie am Ende der vorhandenen Liste von Guy platziert. Platzieren des Zellenselektors in Zelle E5 Wir haben die VLOOKUP-Funktion eingefügt.

Um die Funktion einzufügen, klickten wir auf die Registerkarte Formeln und dann in der Gruppe Funktionsbibliothek auf die Schaltfläche Funktion einfügen.

Tipp: Die VLOOKUP-Funktion kann auch durch Klicken auf die Schaltfläche Funktion einfügen in der Formelleiste eingefügt werden.

Im Feld Kategorie auswählen haben wir Nachschlagen und Referenz ausgewählt. Im Listenfeld Funktion auswählen haben wir VLOOKUP ausgewählt und dann auf OK geklickt.

An dieser Stelle mussten wir die entsprechenden Werte in die Funktionsargumentfelder eingeben. Ich werde Sie durch jedes der Argumente führen.

• Lookup_value – Dies war die Rechnungsnummer in Zelle C5. Sobald wir dies in das Suchwertfeld eingegeben hatten, hielt Excel es im Speicher und wartete darauf, dass wir die verbleibenden Argumente vervollständigten.

• Table_array – Das Tabellenarray ist die Liste, die die Transaktionsdetails auf dem Arbeitsblatt des Kunden enthält. VLOOKUP sucht nach einer Übereinstimmung für den ‚Lookup Value‘ in der ersten Spalte des Tabellenarrays. Guy navigierte zum Arbeitsblatt des Kunden und klickte dann und zog über den gesamten Bereich, der alle Transaktionsdetails enthielt. Er hat die Spalte ‚Datum‘ eingefügt, aber A4: C26 wäre auch in Ordnung gewesen, da sich die Spalte ‚Betrag‘ in diesem Bereich befindet. Bitte beachten Sie, dass Guy den Bereich absolut gemacht hat, damit die Formel leicht kopiert werden kann.

• Col_index_num – Dies ist die Spalte, aus der Guy den Rechnungsbetrag des Kunden abrufen wollte. Dies war Spalte 3 in der Transaktionsliste des Kunden. Spalten werden von links nach rechts nummeriert, wobei die erste Spalte im Tabellenarraybereich Spalte 1 ist. Achten Sie darauf, Spaltennummern und Spaltenbuchstaben nicht zu verwechseln. Obwohl der Rechnungsbetrag in Spalte ‚C‘ gespeichert ist, möchte Excel nur die Position der Indexnummer der Spalte, nicht den Spaltenbuchstaben.

• Range_lookup – Die Bereichssuche steuert, ob Sie nach einer genauen Übereinstimmung mit Ihrem ‚Lookup-Wert‘ suchen. Ich erklärte Guy, dass Excel, wenn er TRUE eingibt oder das Feld leer lässt, nach seiner Rechnungsnummer suchen würde, aber wenn es keine Übereinstimmung finden könnte, würde es den nächstgrößeren Wert zurückgeben, der kleiner als die Rechnungsnummer ist. Wenn es beispielsweise die Rechnung 2466 nicht finden könnte, würde es den Datensatz für 2465 finden und stattdessen den Betrag dafür zurückgeben. Damit dies funktionierte, musste die Kundenliste zuerst nach Rechnungsnummer sortiert werden. Wenn jedoch ‚FALSE‘ in das Feld eingegeben wird, sucht Excel nach einer genauen Übereinstimmung für den Suchwert. Guy wollte eine genaue Übereinstimmung für seine Rechnungsnummer, also gab er FALSE in das Feld ein. Ich habe auch den Tipp weitergegeben, dass Sie anstelle des Wortes ‚FALSE‘ 0 (Null) eingeben können, und Excel wird dasselbe tun.

Mit allen eingegebenen Argumenten Habe ich auf OK geklickt.

Excel hat dann jeden der im Argumentfeld angegebenen Werte durchlaufen. Die Funktion hat die Rechnungsnummer (Lookup-Wert) verwendet, um in der Spalte ganz links in den Datensätzen des Kunden (Tabellenarray) nach einer passenden Rechnungsnummer zu suchen. Sobald die Rechnungsnummer gefunden wurde, sprang sie über die angegebene Anzahl von Spalten (Spaltenindexnummer) in derselben Zeile, um den Rechnungsbetrag zurückzugeben.

Zum Glück hatte der Kunde, wie Sie unten sehen können, den gleichen Rechnungswert wie Guy.

Guy kopierte dann die VLOOKUP-Funktion für alle Rechnungstransaktionen.
Innerhalb von Sekunden konnte er drei # N / A-Fehler sehen (Wert nicht verfügbar Fehler). Dies zeigte, wo der Kunde tatsächlich keine Aufzeichnung dieser Rechnungen hatte. Er fand dann schnell die Unterschiede in seinen und den Rechnungsbeträgen des Kunden. Sofort konnte er sehen, dass Rechnung 2464 kurz war $ 100.00, höchstwahrscheinlich ein Keying-Fehler.

Schritt 3: der double-check

Wir hatten nun erfolgreich unsere Aufzeichnungen mit denen des Kunden verglichen. Als double-check entschieden wir uns dann jedoch für einen Vergleich aus den Aufzeichnungen des kunden, nur für den fall, dass wir etwas verpasst hatten.

Dieses Mal haben wir den VLOOKUP im Arbeitsblatt des Kunden erstellt. Als erstes mussten wir die Rechnungsspalte so verschieben, dass sie die erste Spalte in unserer Transaktionsliste (Tabellenarray) war. Danach haben wir die VLOOKUP-Funktion in eine neue Spalte namens ‚Our record‘ eingefügt.

Guy kopierte die Funktion nach unten und erstellte dann die Spalte ‚Vergleich‘. Unsere doppelte Überprüfung bestätigte, dass der Kunde bereits US $ 100 auf Rechnung 2464 bezahlt hatte. Unsere Prüfung ergab auch, dass der Kunde die Rechnungsnummer für Rechnung 2466 durchaus als 2646 erfasst hatte.

Fazit

Guy konnte nun zum Kunden zurückkehren, um die Informationen zu besprechen, die möglicherweise falsch eingegeben wurden, und Kopien der Rechnungen zu liefern, die der Kunde nicht erhalten hat.

Unnötig zu erwähnen, dass die VLOOKUP-Funktion Guy und seinen Arbeitskollegen viel Zeit gespart hat. Der Schulungs- und Entwicklungsleiter kontaktierte mich nach dem Training, um zu sagen, dass die verbesserte Effizienz in der Abteilung ihrem Unternehmen 10s von 1000s von Dollar gespart hatte.

Wir behandeln die VLOOKUP-Funktion in unseren Excel Stage 3-Kursen. Wenn Sie mit großen Datenmengen zu tun haben, ist dieses Training definitiv für Sie.