So vergleichen Sie zwei Listen in Excel

Excel ist ein effizientes Datenverarbeitungsprogramm. Und eine der Methoden der Informationsanalyse ist der Vergleich zweier Listen. Wenn Sie zwei Listen in Excel korrekt vergleichen, ist die Organisation dieses Prozesses sehr einfach. Es reicht aus, nur einigen der Punkte zu folgen, die heute diskutiert werden. Die praktische Umsetzung dieser Methode hängt ganz von den Bedürfnissen der Person oder Organisation zu einem bestimmten Zeitpunkt ab. Daher sollten mehrere mögliche Fälle in Betracht gezogen werden.

Vergleich zweier Listen in Excel

Natürlich können Sie zwei Listen manuell vergleichen. Aber es wird lange dauern. Excel verfügt über ein eigenes intelligentes Toolkit, mit dem Sie Daten nicht nur schnell vergleichen, sondern auch Informationen erhalten, die mit Ihren Augen nicht so leicht zu erfassen sind. Angenommen, wir haben zwei Spalten mit den Koordinaten A und B. Einige Werte werden darin wiederholt.

Formulierung des Problems

Also müssen wir diese Spalten vergleichen. Das Verfahren zum Vergleichen zweier Dokumente ist wie folgt:

1. Wenn die eindeutigen Zellen jeder dieser Listen gleich sind und die Gesamtzahl der eindeutigen Zellen gleich ist und die Zellen gleich sind, dann können diese Listen als gleich betrachtet werden. Die Reihenfolge, in der die Werte in dieser Liste gestapelt werden, spielt keine so große Rolle.
2. Wir können von teilweiser Übereinstimmung von Listen sprechen, wenn die eindeutigen Werte selbst gleich sind, aber die Anzahl der Wiederholungen unterschiedlich ist. Daher können solche Listen eine unterschiedliche Anzahl von Elementen enthalten.
3. Die Tatsache, dass die beiden Listen nicht übereinstimmen, wird durch einen unterschiedlichen Satz eindeutiger Werte angezeigt.

Alle diese drei Bedingungen sind gleichzeitig die Bedingungen unseres Problems.

Die Lösung des Problems

Lassen Sie uns zwei dynamische Bereiche generieren, um das Vergleichen von Listen zu erleichtern. Jeder von ihnen entspricht jeder der Listen.

Um zwei Listen zu vergleichen, gehen Sie wie folgt vor:

1. In einer separaten Spalte erstellen wir eine Liste mit eindeutigen Werten, die für beide Listen spezifisch sind. Dazu verwenden wir die Formel: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Die Formel selbst muss als Matrixformel geschrieben werden.
2. Lassen Sie uns bestimmen, wie oft jeder eindeutige Wert im Datenarray vorkommt. Hier sind die Formeln dafür: =ZÄHLENWENN(Liste1,D5) und =ZÄHLENI(Liste2,D5).
3. Wenn sowohl die Anzahl der Wiederholungen als auch die Anzahl der eindeutigen Werte in allen Listen, die in diesen Bereichen enthalten sind, gleich sind, gibt die Funktion den Wert 0 zurück. Dies zeigt an, dass die Übereinstimmung XNUMX% beträgt. In diesem Fall werden die Überschriften dieser Listen grün hinterlegt.
4. Wenn sich alle eindeutigen Inhalte in beiden Listen befinden, werden sie von Formeln zurückgegeben =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) der Wert wird Null sein. Wenn E1 keine Null enthält, aber ein solcher Wert in den Zellen E2 und F2 enthalten ist, werden die Bereiche in diesem Fall als übereinstimmend erkannt, jedoch nur teilweise. In diesem Fall werden die Überschriften der entsprechenden Listen orange.
5. Und wenn eine der oben beschriebenen Formeln einen Wert ungleich Null zurückgibt, stimmen die Listen überhaupt nicht überein.

Dies ist die Antwort auf die Frage, wie Spalten mithilfe von Formeln auf Übereinstimmungen analysiert werden können. Wie Sie sehen, können Sie mit Hilfe von Funktionen nahezu jede Aufgabe umsetzen, die auf den ersten Blick nichts mit Mathematik zu tun hat.

Beispielprüfung

In unserer Version der Tabelle gibt es drei Arten von Listen jeder oben beschriebenen Art. Es hat teilweise und vollständige Übereinstimmung sowie Nichtübereinstimmung.

Um Daten zu vergleichen, verwenden wir den Bereich A5:B19, in den wir abwechselnd diese Listenpaare einfügen. Was das Ergebnis des Vergleichs sein wird, werden wir anhand der Farbe der Originallisten verstehen. Wenn sie völlig unterschiedlich sind, dann wird es ein roter Hintergrund sein. Wenn ein Teil der Daten gleich ist, dann gelb. Bei vollständiger Identität sind die entsprechenden Überschriften grün. Wie erstelle ich eine Farbe, je nachdem, was das Ergebnis ist? Dies erfordert eine bedingte Formatierung.

Finden von Unterschieden in zwei Listen auf zwei Arten

Lassen Sie uns zwei weitere Methoden zum Finden von Unterschieden beschreiben, je nachdem, ob die Listen synchron sind oder nicht.

Option 1. Synchrone Listen

Dies ist eine einfache Option. Angenommen, wir haben solche Listen.

Um zu bestimmen, wie oft die Werte nicht konvergiert sind, können Sie die Formel verwenden: =SUMMENPRODUKT(—(A2:A20<>B2:B20)). Wenn wir als Ergebnis 0 erhalten, bedeutet dies, dass die beiden Listen gleich sind.

Option 2: Mischlisten

Wenn die Listen in der Reihenfolge der enthaltenen Objekte nicht identisch sind, müssen Sie eine Funktion wie bedingte Formatierung anwenden und doppelte Werte einfärben. Oder nutzen Sie die Funktion COUNTIF, mit der wir bestimmen, wie oft ein Element aus einer Liste in der zweiten vorkommt.

So vergleichen Sie 2 Spalten Zeile für Zeile

Wenn wir zwei Spalten vergleichen, müssen wir oft Informationen vergleichen, die sich in verschiedenen Zeilen befinden. Dabei hilft uns der Betreiber OB. Schauen wir uns an, wie es in der Praxis funktioniert. Dazu stellen wir einige anschauliche Situationen vor.

Beispiel. So vergleichen Sie 2 Spalten auf Übereinstimmungen und Unterschiede in einer Zeile

Um zu analysieren, ob die Werte, die sich in derselben Zeile, aber in verschiedenen Spalten befinden, gleich sind, schreiben wir die Funktion IF. Die Formel wird in jede Zeile eingefügt, die in der Hilfsspalte platziert ist, wo die Ergebnisse der Datenverarbeitung angezeigt werden. Aber es ist überhaupt nicht notwendig, es in jeder Zeile vorzuschreiben, kopieren Sie es einfach in die restlichen Zellen dieser Spalte oder verwenden Sie die Autocomplete-Markierung.

Wir sollten eine solche Formel aufschreiben, um zu verstehen, ob die Werte in beiden Spalten gleich sind oder nicht: =WENN(A2=B2, „Übereinstimmung“, „“). Die Logik dieser Funktion ist sehr einfach: Sie vergleicht die Werte in den Zellen A2 und B2, und wenn sie gleich sind, zeigt sie den Wert „Koinzidieren“ an. Wenn die Daten unterschiedlich sind, wird kein Wert zurückgegeben. Sie können die Zellen auch überprüfen, um zu sehen, ob es eine Übereinstimmung zwischen ihnen gibt. In diesem Fall lautet die verwendete Formel: =IF(A2<>B2, „Nicht übereinstimmen“, „“). Das Prinzip ist das gleiche, zuerst wird die Prüfung durchgeführt. Stellt sich heraus, dass die Zellen das Kriterium erfüllen, wird der Wert „trifft nicht zu“ angezeigt.

Es ist auch möglich, die folgende Formel im Formelfeld zu verwenden, um sowohl „Match“ anzuzeigen, wenn die Werte gleich sind, als auch „Do not match“, wenn sie unterschiedlich sind: =WENN(A2=B2; „Übereinstimmung“, „Nicht übereinstimmen“). Anstelle des Gleichheitsoperators können Sie auch den Ungleichheitsoperator verwenden. Nur die Reihenfolge der angezeigten Werte wird in diesem Fall etwas anders sein: =WENN(A2<>B2, „Nicht übereinstimmen“, „Übereinstimmen“). Nach der Verwendung der ersten Version der Formel sieht das Ergebnis wie folgt aus.

Bei dieser Variation der Formel wird die Groß-/Kleinschreibung nicht beachtet. Wenn sich also die Werte in einer Spalte von anderen nur dadurch unterscheiden, dass sie in Großbuchstaben geschrieben sind, bemerkt das Programm diesen Unterschied nicht. Um den Vergleich zwischen Groß- und Kleinschreibung zu unterscheiden, müssen Sie die Funktion in den Kriterien verwenden GENAU. Die restlichen Argumente bleiben unverändert: =IF(EXACT(A2,B2), „Übereinstimmung“, „Eindeutig“).

So vergleichen Sie mehrere Spalten auf Übereinstimmungen in einer Zeile

Es ist möglich, die Werte in den Listen nach einer ganzen Reihe von Kriterien zu analysieren:

1. Finden Sie die Zeilen, die überall die gleichen Werte haben.
2. Finden Sie die Zeilen, in denen Übereinstimmungen in nur zwei Listen vorhanden sind.

Sehen wir uns einige Beispiele an, wie in jedem dieser Fälle vorzugehen ist.

Beispiel. So finden Sie Übereinstimmungen in einer Zeile in mehreren Spalten einer Tabelle

Angenommen, wir haben eine Reihe von Spalten, die die benötigten Informationen enthalten. Wir stehen vor der Aufgabe, diejenigen Zeilen zu ermitteln, in denen die Werte gleich sind. Dazu müssen Sie die folgende Formel verwenden: =WENN(UND(A2=B2,A2=C2), „Übereinstimmung“, „ “).

Wenn die Tabelle zu viele Spalten enthält, müssen Sie sie nur zusammen mit der Funktion verwenden IF Operator COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;“match“;“ „). Die in dieser Formel verwendete Zahl gibt die Anzahl der zu überprüfenden Spalten an. Wenn es anders ist, müssen Sie so viel schreiben, wie es für Ihre Situation zutrifft.

Beispiel. So finden Sie Übereinstimmungen in einer Zeile in zwei beliebigen Spalten einer Tabelle

Nehmen wir an, wir müssen prüfen, ob die Werte in einer Zeile in zwei Spalten mit denen in der Tabelle übereinstimmen. Dazu müssen Sie die Funktion als Bedingung verwenden OR, wobei abwechselnd die Gleichheit jeder der Spalten in die andere geschrieben wird. Hier ist ein Beispiel.

Wir verwenden diese Formel: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Es kann vorkommen, dass die Tabelle viele Spalten enthält. In diesem Fall ist die Formel riesig und es kann viel Zeit in Anspruch nehmen, alle erforderlichen Kombinationen auszuwählen. Um dieses Problem zu lösen, müssen Sie die Funktion verwenden COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; „Eindeutiger String“; „Nicht eindeutiger String“)

Wir sehen, dass wir insgesamt zwei Funktionen haben COUNTIF. Bei der ersten ermitteln wir abwechselnd, wie viele Spalten eine Ähnlichkeit zu A2 haben, und bei der zweiten prüfen wir die Anzahl der Ähnlichkeiten mit dem Wert von B2. Wenn wir als Ergebnis der Berechnung nach dieser Formel einen Nullwert erhalten, zeigt dies an, dass alle Zeilen in dieser Spalte eindeutig sind, wenn mehr, gibt es Ähnlichkeiten. Wenn wir also als Ergebnis der Berechnung mit zwei Formeln und der Addition der Endergebnisse einen Nullwert erhalten, wird der Textwert „Eindeutige Zeichenfolge“ zurückgegeben. Wenn diese Zahl größer ist, wird geschrieben, dass diese Zeichenfolge nicht eindeutig ist.

So vergleichen Sie 2 Spalten in Excel auf Übereinstimmungen

Nehmen wir nun ein Beispiel. Nehmen wir an, wir haben eine Tabelle mit zwei Spalten. Sie müssen prüfen, ob sie übereinstimmen. Dazu müssen Sie die Formel anwenden, in der auch die Funktion verwendet wird IF, und der Betreiber COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, „Keine Übereinstimmungen in Spalte B“, „Es gibt Übereinstimmungen in Spalte B“)

Sind keine weiteren Maßnahmen erforderlich. Nachdem wir das Ergebnis mit dieser Formel berechnet haben, erhalten wir den Wert des dritten Arguments der Funktion IF Streichhölzer. Wenn es keine gibt, dann der Inhalt des zweiten Arguments.

So vergleichen Sie 2 Spalten in Excel auf Übereinstimmungen und markieren sie mit Farbe

Um übereinstimmende Spalten visuell leichter identifizieren zu können, können Sie sie farblich hervorheben. Dazu müssen Sie die Funktion „Bedingte Formatierung“ verwenden. Mal sehen in der Praxis.

Suchen und Hervorheben von Übereinstimmungen nach Farbe in mehreren Spalten

Um die Übereinstimmungen zu ermitteln und hervorzuheben, müssen Sie zunächst den Datenbereich auswählen, in dem die Überprüfung durchgeführt werden soll, und dann auf der Registerkarte „Startseite“ den Punkt „Bedingte Formatierung“ öffnen. Wählen Sie dort als Zellauswahlregel „Doppelte Werte“ aus.

Danach erscheint ein neues Dialogfeld, in dem wir in der linken Popup-Liste die Option „Wiederholen“ finden und in der rechten Liste die Farbe auswählen, die für die Auswahl verwendet wird. Nachdem wir auf die Schaltfläche „OK“ geklickt haben, wird der Hintergrund aller Zellen mit Ähnlichkeiten ausgewählt. Dann vergleichen Sie einfach die Spalten mit dem Auge.

Passende Linien finden und hervorheben

Die Technik zum Überprüfen, ob Zeichenfolgen übereinstimmen, ist etwas anders. Zuerst müssen wir eine zusätzliche Spalte erstellen und dort werden wir die kombinierten Werte mit dem &-Operator verwenden. Dazu müssen Sie eine Formel der Form schreiben: =A2&B2&C2&D2.

Wir wählen die Spalte aus, die erstellt wurde und die kombinierten Werte enthält. Als nächstes führen wir dieselbe Abfolge von Aktionen aus, die oben für die Spalten beschrieben wurde. Doppelte Zeilen werden in der von Ihnen angegebenen Farbe hervorgehoben.

Wir sehen, dass es nicht schwierig ist, nach Wiederholungen zu suchen. Excel enthält dafür alle notwendigen Werkzeuge. Es ist wichtig, nur zu üben, bevor Sie all dieses Wissen in die Praxis umsetzen.