Vergleichen Sie zwei verschiedene Excel-Spalten. So vergleichen Sie zwei Spalten in Excel - Excel-Datenvergleichstechniken
Oft besteht die Aufgabe darin, zwei Listen von Elementen zu vergleichen. Die manuelle Durchführung ist zu mühsam und außerdem sind Fehler nicht auszuschließen. Excel vereinfacht diesen Vorgang. IN dieser Rat beschreibt eine Methode, die bedingte Formatierung verwendet.
Auf Abb. 164.1 ist ein Beispiel für zwei mehrspaltige Namenslisten. Durch die Anwendung bedingter Formatierung können Unterschiede in Listen deutlicher hervortreten. Diese Beispiellisten enthalten Text, diese Methode funktioniert jedoch auch mit numerischen Daten.
Die erste Liste ist A2:B31, dieser Bereich heißt alte Liste. Die zweite Liste ist D2:E31 , der Bereich heißt neue Liste. Die Bereiche wurden mit dem Befehl benannt Formeln Definierte Namen Vergeben Sie einen Namen. Die Benennung der Bereiche ist optional, erleichtert jedoch die Arbeit mit ihnen.
Beginnen wir damit, der alten Liste eine bedingte Formatierung hinzuzufügen.
- Wählen Sie Bereichszellen aus alte Liste.
- Wählen .
- Im Fenster Erstellen Sie eine Formatierungsregel Wählen Sie das genannte Element aus Verwenden Sie die Formel
- Geben Sie diese Formel in das Fensterfeld ein (Abb. 164.2): =COUNTIF(NewList;A2)=0 .
- Drück den Knopf Format und legen Sie die Formatierung fest, die angewendet werden soll, wenn die Bedingung wahr ist. Am besten wählen Sie unterschiedliche Füllfarben.
- OK klicken.
Zellen in einem Bereich neue Liste Verwenden Sie eine ähnliche Formel für die bedingte Formatierung.
- Wählen Sie Bereichszellen aus neue Liste.
- Wählen Startseite Bedingte Formatierung Regel erstellen um ein Dialogfeld zu öffnen Erstellen Sie eine Formatierungsregel.
- Im Fenster Erstellen Sie eine Regel Formatierung Element auswählen Verwenden Sie die Formel um die zu formatierenden Zellen zu definieren.
- Geben Sie diese Formel in das Fensterfeld ein: =COUNTIF(OldList;D2)=0 .
- Drück den Knopf Format und legen Sie die Formatierung fest, die angewendet wird, wenn die Bedingung wahr ist (andere Füllfarbe).
- OK klicken.
Dadurch werden die Namen hervorgehoben, die in der alten Liste, aber nicht in der neuen Liste enthalten sind (Abb. 164.3). Darüber hinaus werden Namen in der neuen Liste, die nicht in der alten Liste enthalten sind, ebenfalls hervorgehoben, jedoch in einer anderen Farbe. Namen, die in beiden Listen erscheinen, werden nicht hervorgehoben.
Beide bedingten Formatierungsformeln verwenden die Funktion ZÄHLENWENN. Es zählt, wie oft ein bestimmter Wert in einem Bereich vorkommt. Wenn die Formel 0 zurückgibt, bedeutet dies, dass das Element nicht im Bereich liegt. Auf diese Weise, bedingte Formatierung kommt zur Sache und die Hintergrundfarbe der Zelle ändert sich.
Beim Arbeiten in Excel stößt man häufig auf die Aufgabe, verschiedene Arten von Wertelisten zu vergleichen. Um Wertebereiche im Allgemeinen und Wertespalten im Besonderen zu vergleichen, kann Folgendes verwendet werden: Standardmittel Excel mag bedingte Formatierung und Funktionen. Darüber hinaus können Sie solche Probleme mithilfe von VBA-Makros und darauf basierenden Excel-Add-Ins lösen.
Die Notwendigkeit, Bereiche im Allgemeinen und Spalten im Besonderen zu vergleichen, entsteht, wenn verschiedene in Excel erstellte Berichte, Listen und Datenbanken aktualisiert werden und im Laufe der Zeit Änderungen erforderlich sind. Im Internet werden zahlreiche Beispiele beschrieben, die den Vergleich von Werten in Spalten ermöglichen verschiedene Funktionen(Formeln) und bedingte Formatierung. Für diejenigen, für die solche Methoden aus irgendeinem Grund nicht geeignet sind, kann ein anderes Tool verwendet werden, mit dem sowohl Spalten als auch beliebige Wertebereiche, die aus beliebig vielen Zeilen und Spalten bestehen, verglichen werden können. Mit dem Add-in für Excel können Sie zwei beliebige Bereiche vergleichen bzw. jedes Element eines Bereichs mit jedem Element des anderen vergleichen und sowohl Übereinstimmungen als auch Unterschiede Ihrer Wahl zwischen ihnen finden.
Add-In zum Vergleichen von Werten in zwei Excel-Bereichen
Das Add-In, auf das weiter unten eingegangen wird, dient dem Vergleich von Werten in zwei Bereichen, die aus einer beliebigen Anzahl von Zeilen und Spalten bestehen. Dieses Add-In verwendet das Verfahren, jedes Element eines Bereichs mit jedem Element eines anderen Bereichs zu vergleichen, bestimmt, welche Elemente dieser Bereiche übereinstimmen und welche sich unterscheiden, und füllt die Zellen mit einer vom Benutzer definierten Farbe.
Wie Sie dem Dialogfeld entnehmen können, ist alles ganz einfach: Der Benutzer wählt zwei verglichene Bereiche aus, legt fest, wonach gesucht werden soll, unterschiedliche oder übereinstimmende Werte, wählt eine Farbe aus und startet das Programm. Endresultat Add-In-Anwendungen sind mit einer bestimmten Farbe gefüllte Zellen, die Unterschiede oder Übereinstimmungen in den Werten der ausgewählten Bereiche anzeigen.
Beim Vergleichen von Werten in Excel gibt es eine Einschränkung. Zahlen können als Text formatiert werden, der nicht immer visuell erkennbar ist (). Das heißt, eine Zahl in Excel kann sowohl ein numerischer Wert als auch ein Textwert sein, und diese beiden Werte sind einander nicht gleich. Sehr oft ist dieses Phänomen die Ursache für verschiedene Arten von Fehlern. Um solche Fehler auszuschließen, wird die Option „Zahlen als Text vergleichen“ verwendet, die standardmäßig aktiviert ist. Mit dieser Option können Sie nicht numerische Werte vergleichen, sondern daraus konvertierte Textwerte.
Das Add-on ermöglicht:
1. Rufen Sie mit einem Mausklick den Makrodialog direkt aus der Excel-Symbolleiste auf;
2. Elemente des Bereichs Nr. 1 finden, die nicht im Bereich Nr. 2 liegen;
3. Elemente des Bereichs Nr. 2 finden, die nicht im Bereich Nr. 1 liegen;
4. Elemente des Bereichs Nr. 1 finden, die im Bereich Nr. 2 liegen;
5. Elemente des Bereichs Nr. 2 finden, die im Bereich Nr. 1 liegen;
6. Wählen Sie eine von neun Füllfarben für Zellen mit den erforderlichen Werten.
7. Wählen Sie Bereiche schnell mit der Option „Bereiche begrenzen“ aus. Sie können zwar ganze Zeilen und Spalten auswählen, die Reduzierung des ausgewählten Bereichs auf den verwendeten erfolgt jedoch automatisch.
8. Anstatt numerische Werte zu vergleichen, verwenden Sie den Vergleich Textwerte Verwenden der Option „Zahlen als Text vergleichen“;
9. Vergleichen Sie die Werte in den Zellen des Bereichs, ohne zusätzliche Leerzeichen zu berücksichtigen;
10. Vergleichen Sie Werte in Bereichszellen ohne Berücksichtigung der Groß- und Kleinschreibung.
Wie vergleiche ich zwei Spalten mithilfe eines Makros (Add-Ins) für Excel?
Der Spaltenvergleich ist ein Sonderfall des willkürlichen Bereichsvergleichs. In den Bereichen Nr. 1 und Nr. 2 wählen wir zwei Spalten aus, und Sie können genau die Spalten auswählen und den Auswahlrahmen nicht mit der Maus über die Bereiche mit Zellen ziehen (der Einfachheit halber ist die Option „Bereiche begrenzen“ aktiviert). Standardeinstellung, die, wenn Spalten oder Zeilen vollständig ausgewählt sind, diese Auswahl auf den verwendeten Bereich einschränkt), wählen Sie aus nötige Aktion Um nach Unterschieden oder Übereinstimmungen zu suchen, wählen Sie die Zellenfüllfarbe aus und führen Sie das Programm aus. Unten sehen Sie das Ergebnis der Suche nach übereinstimmenden Werten in zwei Spalten.
Wenn Sie mit großen Tabellenkalkulationsdokumenten (viele Daten/Spalten) arbeiten, ist es sehr schwierig, die Genauigkeit/Relevanz aller Informationen zu kontrollieren. Daher ist es sehr oft notwendig, zwei oder mehr Spalten in einem Excel-Dokument zu analysieren, um Duplikate zu erkennen. Und wenn der Benutzer nicht über alle Funktionen des Programms informiert ist, hat er möglicherweise logischerweise eine Frage: Wie vergleicht man zwei Spalten in Excel?
Die Antwort darauf haben sich die Entwickler dieses Programms schon lange ausgedacht, indem sie zunächst Befehle eingebaut haben, die beim Vergleich von Informationen helfen. Wenn Sie sich eingehend mit diesem Thema befassen, finden Sie im Allgemeinen etwa ein Dutzend verschiedene Möglichkeiten, einschließlich des Schreibens einzelner Makros und Formeln. Doch die Praxis zeigt: Es reicht aus, drei oder vier verlässliche Wege zu kennen, um den aufkommenden Vergleichsbedürfnissen gerecht zu werden.
So vergleichen Sie zwei Spalten in Excel auf Übereinstimmungen
Daten in Excel werden normalerweise zwischen Zeilen und Spalten verglichen, wobei ein Wert als Benchmark angegeben wird. Wenn Spalten verglichen werden müssen, können Sie die integrierte Funktionalität verwenden, nämlich die Aktionen „Match“ und „If“. Und alles, was Sie brauchen, ist Excel, frühestens im siebten Jahr der „Veröffentlichung“.
Wir beginnen mit der Funktion „Zufall". Die verglichenen Daten befinden sich beispielsweise in Spalten mit den Adressen C3 und B3. Das Ergebnis des Vergleichs muss in einer Zelle abgelegt werden, zum Beispiel D3. Wir klicken auf diese Zelle, rufen das Menüverzeichnis „Formeln“ auf, suchen die Zeile „Funktionsbibliothek“, öffnen die in der Dropdown-Liste platzierten Funktionen, suchen das Wort „Text“ und klicken auf „Zufall“.
Gleich wird auf dem Display angezeigt neue Form, wobei es nur zwei Felder gibt: „Text eins“, „Text zwei“. Sie müssen nur die Adressen der verglichenen Spalten (C3, B3) eingeben und dann auf die übliche Taste „OK“ klicken. Als Ergebnis sehen Sie das Ergebnis mit den Worten „Wahr“ / „Falsch“. Im Prinzip nichts besonders Kompliziertes, auch für einen unerfahrenen Benutzer! Dies ist jedoch bei weitem nicht die einzige Methode. Lassen Sie uns die „Wenn“-Funktion aufschlüsseln.
Möglichkeit, zwei Spalten in Excel auf Übereinstimmungen mit „Wenn“ zu vergleichen ermöglicht die Eingabe von Werten, die am Ende nach dem Vorgang angezeigt werden. Der Cursor wird in die Zelle gesetzt, in der die Eingabe erfolgen soll, das Menüverzeichnis „Bibliothek der Funktionen“ öffnet sich, in der Dropdown-Liste wird die Zeile „logisch“ ausgewählt und die erste Position darin wird durch „ Wenn“-Befehl. Wir wählen es.
Weiter geht die Form der begründeten Füllung hervor. „Logic_expression“ ist die Formulierung der Funktion selbst. In unserem Fall handelt es sich um einen Vergleich zweier Spalten, daher geben wir „B3 = C3“ (oder Ihre Spaltenadressen) ein. Weitere Felder „value_if true“, „value_if_false“. Hier sollten Sie die Daten (Beschriftungen/Wörter/Zahlen) eingeben, die einem positiven/negativen Ergebnis entsprechen sollen. Klicken Sie nach dem Ausfüllen wie gewohnt auf „OK“. Machen wir uns mit dem Ergebnis vertraut.
Wenn Sie eine zeilenweise Analyse von zwei Spalten durchführen möchten, fügen wir in die dritte Spalte eine beliebige oben besprochene Funktion ein („wenn“ oder „Koinzidenz“). Seine Wirkung erstreckt sich auf die gesamte Höhe der gefüllten Säulen. Wählen Sie als Nächstes die dritte Spalte aus, klicken Sie auf die Registerkarte „Haupt“ und suchen Sie in der angezeigten Gruppe nach dem Wort „Stile“. Die „Spalten-/Zellenauswahlregeln“ werden geöffnet.
In ihnen müssen Sie den Befehl „equals“ auswählen, dann auf die erste Spalte klicken und „Enter“ drücken. Das Ergebnis ist, dass die Spalten, in denen übereinstimmende Ergebnisse vorliegen, „eingefärbt“ werden. Und Sie sehen sofort die Informationen, die Sie benötigen. Lassen Sie uns bei der Analyse des Themas „Wie vergleicht man die Werte zweier Spalten in Excel“ weiter zu einer Methode wie der bedingten Formatierung in Excel übergehen.
Excel: Bedingte Formatierung
Durch die Formatierung eines bedingten Typs können Sie nicht nur zwei verschiedene Spalten/Zellen/Zeilen vergleichen, sondern auch verschiedene Daten darin mit einer bestimmten Farbe (rot) hervorheben. Das heißt, wir suchen nicht nach Zufällen, sondern nach Unterschieden. Um es zu bekommen, verhalten wir uns so. Wir wählen die erforderlichen Spalten aus, ohne ihre Namen zu berühren, gehen zum Menüverzeichnis „Haupt“ und suchen dort nach dem Unterabschnitt „Stile“.
Es enthält die Zeile „bedingte Formatierung“. Wenn wir darauf klicken, erhalten wir eine Liste, in der wir den Funktionspunkt „Regel erstellen“ benötigen. Nächster Schritt: In der Zeile „Format“ müssen Sie die Formel = $ A2 eingeben<>$B2. Diese Formel hilft Excel zu verstehen, was genau wir brauchen, nämlich alle Werte von Spalte A rot einzufärben, die nicht mit den Werten von Spalte B übereinstimmen. Eine etwas kompliziertere Art, Formeln anzuwenden, bezieht sich darauf zur Beteiligung von Strukturen wie HLOOKUP / VLOOKUP. Diese Formeln beziehen sich auf horizontale/vertikale Wertsuchen. Betrachten wir diese Methode genauer.
HLOOKUP und VLOOKUP
Mit diesen beiden Formeln können Sie horizontal/vertikal nach Daten suchen. Das heißt, H ist horizontal und V ist vertikal. Wenn sich die zu vergleichenden Daten in der linken Spalte relativ zu der Spalte befinden, in der sich die verglichenen Werte befinden, verwenden Sie das VLOOKUP-Konstrukt. Befinden sich die zu vergleichenden Daten jedoch horizontal oben in der Tabelle ab der Spalte, in der die Referenzwerte angegeben sind, verwenden wir das HLOOKUP-Konstrukt.
Um zu verstehen, wie man Daten in zwei Excel-Spalten vertikal vergleicht, sollten Sie dies verwenden vollständige Formel: lookup_value,table_array,col_index_num,range_lookup.
Der zu findende Wert wird als „lookup_value“ bezeichnet. Die Suchspalten werden wie ein „Tabellenarray“ eingefahren. Die Spaltennummer sollte als „sol_index_num“ angegeben werden. Darüber hinaus ist dies die Spalte, deren Wert übereinstimmt und die zurückgegeben/korrigiert werden muss. Der Befehl „Range Lookup“ fungiert hier als zusätzlicher Befehl. Es kann angeben, ob der Wert exakt oder ungefähr angegeben werden muss.
Wenn dieser Befehl nicht angegeben wird, werden Werte für beide Typen zurückgegeben. Die HLOOKUP-Formel sieht vollständig wie folgt aus: lookup_value,table_array,row_index_num,range_lookup. Die Bedienung ist nahezu identisch mit der oben beschriebenen. Es stimmt, hier gibt es eine Ausnahme. Dies ist der Index der Zeichenfolge, der die Zeichenfolge angibt, deren Werte zurückgegeben werden sollen. Wenn Sie lernen, alle oben genannten Methoden klar anzuwenden, wird klar: Es gibt kein komfortableres und universelleres Programm für die Arbeit mit großen Datenmengen. verschiedene Typen als Excel. Der Vergleich zweier Spalten in Excel ist jedoch nur die halbe Arbeit. Schließlich muss mit den erhaltenen Werten noch etwas anderes gemacht werden. Das heißt, die gefundenen Übereinstimmungen müssen noch irgendwie verarbeitet werden.
Umgang mit doppelten Werten
Angenommen, in der ersten Spalte finden sich Zahlen, die sich in der zweiten Spalte vollständig wiederholen. Es ist klar, dass die manuelle Korrektur von Wiederholungen eine unrealistische Arbeit ist und viel wertvolle Zeit kostet. Daher sollten Sie eine vorgefertigte Technik zur automatischen Korrektur verwenden.
Damit dies funktioniert, müssen Sie zunächst die Spalten benennen, falls diese nicht vorhanden sind. Wir stellen den Cursor auf die erste Zeile, klicken mit der rechten Maustaste und wählen im erscheinenden Menü „Einfügen“. Nehmen wir an, die Überschriften lauten „name“ und „duplicate“. Als nächstes benötigen Sie das Verzeichnis „Datum“, darin befindet sich „Filter“. Klicken Sie auf den kleinen Pfeil neben dem „Duplikat“ und entfernen Sie alle „Vögel“ aus der Liste. Klicken Sie nun auf OK und die doppelten Werte von Spalte A werden sichtbar.
Als nächstes wählen wir diese Zellen aus, da wir nicht nur den Text in zwei Spalten in Excel vergleichen, sondern auch Duplikate entfernen mussten. Ausgewählt, mit der rechten Maustaste angeklickt, „Zeile löschen“ ausgewählt, auf „OK“ geklickt und eine Tabelle ohne übereinstimmende Werte erhalten. Die Methode funktioniert, wenn sich die Spalten auf derselben Seite befinden, also nebeneinander liegen.
Daher haben wir verschiedene Möglichkeiten analysiert, zwei Spalten in Excel zu vergleichen. Ich habe euch bewusst keine Screenshots gezeigt, weil ihr dadurch verwirrt werdet.
ABER ich habe ein tolles Video von einem der beliebtesten und vorbereitet einfache Wege Ich vergleiche zwei Spalten des Dokuments und jetzt schlage ich vor, dass Sie sich damit vertraut machen, um den behandelten Stoff zu konsolidieren.
Wenn der Artikel für Sie immer noch nützlich war, dann teilen Sie ihn in den sozialen Medien. Netzwerke oder bewerten Sie, indem Sie auf die Anzahl der Sterne klicken, die Sie für richtig halten. Vielen Dank, das ist alles für heute, bis bald.
© Alexander Ivanov.
Die Möglichkeit, zwei Datensätze in Excel zu vergleichen, ist häufig für Personen nützlich, die große Datenmengen verarbeiten und mit riesigen Tabellenkalkulationen arbeiten. Der Vergleich kann beispielsweise bei der Richtigkeit der Dateneingabe oder der rechtzeitigen Eingabe von Daten in eine Tabelle verwendet werden. Der folgende Artikel beschreibt mehrere Tricks, um zwei Spalten mit Daten in Excel zu vergleichen.
Verwendung der bedingten IF-Anweisung
Die Methode zur Verwendung des bedingten IF-Operators unterscheidet sich darin, dass zum Vergleich zweier Spalten nur der für den Vergleich erforderliche Teil und nicht das gesamte Array verwendet wird. Die Schritte zur Implementierung dieser Methode werden im Folgenden beschrieben:
Platzieren Sie beide Spalten zum Vergleich in den Spalten A und B des Arbeitsblatts.
Geben Sie in Zelle C2 die folgende Formel ein =IF(ISERROR(MATCH(A2,$B$2:$B$11,0)),"";A2) und ziehen Sie sie in Zelle C11. Diese Formel sucht nacheinander nach dem Vorhandensein jedes Elements aus Spalte A in Spalte B und gibt den Wert des Elements zurück, wenn es in Spalte B gefunden wurde.
Verwenden der SVERWEIS-Ersetzungsformel
Das Funktionsprinzip der Formel ähnelt der vorherigen Methode, der Unterschied liegt in MATCH anstelle von MATCH. Besonderheit Zu dieser Methode gehört auch die Möglichkeit, zwei horizontale Arrays mithilfe der HLOOKUP-Formel zu vergleichen.
Um zwei Spalten mit den Daten in den Spalten A und B zu vergleichen (ähnlich vorherige Methode), geben Sie die folgende Formel =VLOOKUP(A2,$B$2:$B$11,1,0) in Zelle C2 ein und ziehen Sie sie in Zelle C11.
Diese Formel untersucht jedes Element aus dem Hauptarray im verglichenen Array und gibt seinen Wert zurück, wenn es in Spalte B gefunden wurde. Andernfalls gibt Excel einen #N/A-Fehler zurück.
Verwenden eines VBA-Makros
Durch den Einsatz von Makros zum Vergleich zweier Spalten können Sie den Prozess vereinheitlichen und die Zeit für die Datenvorbereitung verkürzen. Die Entscheidung, welches Vergleichsergebnis angezeigt werden soll, liegt ganz bei Ihrer Vorstellungskraft und Ihren Makrofähigkeiten. Nachfolgend finden Sie eine Technik, die auf der offiziellen Microsoft-Website veröffentlicht wurde.
1 | Sub Find_Matches() |
In diesem Code wird der Variable CompareRange ein Bereich mit dem zu vergleichenden Array zugewiesen. Anschließend wird eine Schleife ausgeführt, die jedes Element im ausgewählten Bereich durchläuft und es mit jedem Element im verglichenen Bereich vergleicht. Wurden Elemente mit identischen Werten gefunden, trägt das Makro den Wert des Elements in Spalte C ein.
Um das Makro zu verwenden, kehren Sie zum Arbeitsblatt zurück, wählen Sie den Hauptbereich aus (in unserem Fall sind dies die Zellen A1:A11) und drücken Sie Alt + F8. Wählen Sie im angezeigten Dialogfeld ein Makro aus Finden_Streichhölzer und klicken Sie auf die Schaltfläche „Ausführen“.
Nach der Ausführung des Makros sollte das Ergebnis so aussehen:
Verwendung des Inquire-Add-ons
Ergebnis
Deshalb haben wir uns verschiedene Möglichkeiten zum Vergleichen von Daten in Excel angesehen, die Ihnen bei der Lösung einiger analytischer Probleme helfen und die Suche nach doppelten (oder eindeutigen) Werten erleichtern.
