Verwendung von COUNTIFS () zum Vergleichen mehrerer Datensätze

Bild: iStock / jacoblund

Sie können Formeln und integrierte Regeln verwenden, um Unterschiede zwischen zwei Listen hervorzuheben. Sie können diese Route versuchen, um zwei völlig unterschiedliche Datensätze zu vergleichen, aber COUNTIFS () ist einfach und erledigt die Aufgabe. In diesem Artikel kombinieren wir COUNTIFS () mit einem Filter und einem bedingten Format - Sie wählen aus, welches Ihren Anforderungen am besten entspricht.

Ich verwende Excel 2016 (Desktop) auf einem Windows 10-System, aber Sie können beide Techniken in früheren Versionen verwenden. Sie können mit Ihren eigenen Daten arbeiten oder die .xls- und .xlsx-Demonstrationsdateien herunterladen. Die Browser-Edition unterstützt bedingte Formatierungsregeln, Sie können jedoch keine benutzerdefinierte Regel im Browser anwenden.

Hinweis: Dies ist der dritte von drei Artikeln in einer Reihe. Die vorherigen Artikel finden Sie unter Verwenden der bedingten Formatierung von Excel zum Vergleichen von Listen und Verwenden von Excel-Formeln zum Vergleichen mehrspaltiger Listen.

Die Beispieldaten

Die beiden in Abbildung A gezeigten Datensätze teilen nur einen Datensatz, Zeilen 5 und 13. Die anderen Datensätze haben gemeinsame Werte in zwei Spalten, jedoch nicht alle drei. In diesem Fall kümmern wir uns nicht um sich wiederholende Daten in derselben Spalte. Es ist uns nur wichtig, wenn diese Werte zusammen einen vollständigen doppelten Datensatz erstellen. Sie können jedoch die Funktion COUNTIFS () anpassen, um Teilmengen anstelle eines gesamten Datensatzes zu finden.

Abbildung A.

Wir werden die Duplikate zwischen den beiden Datensätzen verfügbar machen.

Um Datensatzduplikate schnell verfügbar zu machen, fügen wir zunächst eine COUNTIFS () - Funktion hinzu, die 1 zurückgibt, wenn ein Duplikat vorhanden ist. Die Funktion COUNTIFS () verwendet die folgende Syntax, um mehrere Kriterien anzugeben, um zu bestimmen, welche Werte in einem Bereich gezählt werden sollen:

 COUNTIFS (Land1, Kriterien1, Land2, Kriterien2 ...) 

Dieses Beispiel enthält alle drei Spalten für einen vollständigen Datensatzvergleich. Sie können jedoch Teildatensätze vergleichen, indem Sie nur die Spalten einschließen, die Sie vergleichen möchten. Wenn sich die Datensätze auf verschiedenen Blättern befinden, müssen Sie außerdem die Blattkennung angeben.

COUNTIFS () und Filter

In diesem ersten Beispiel kombinieren wir die Ergebnisse einer COUNTIFSIO-Funktion mit einem Filter. Geben Sie zunächst die folgende Funktion in E3 ein und kopieren Sie sie in die verbleibenden Zeilen desselben Datensatzes:

 = COUNTIFS ($ B $ 11: $ B $ 15, B3, $ C $ 11: $ C $ 15, C3, $ D $ 11: $ D $ 15, D3) 

Wie Sie in Abbildung B sehen können, gibt nur ein Datensatz 1 zurück - den Datensatz in Zeile 5. Beachten Sie, dass die Funktionen in E3: E7 auf die entsprechenden Spalten in den Zeilen 11 bis 15 verweisen und diese Werte mit den Werten im ersten Datensatz vergleichen . Dies ist möglich, da die Kriterien in jedem Argumentensatz eine relative Referenz sind.

Abbildung B.

In beiden Datensätzen tritt ein Datensatz auf.

In einem so einfachen Datensatz ist es leicht, das Duplikat zu erkennen, aber es ist nicht so einfach, wenn das Blatt mit vielen Datensätzen gefüllt ist. Um die Aufgabe abzuschließen, können Sie einen Filter verwenden, um nur den doppelten Datensatz im ersten Datensatz wie folgt anzuzeigen:

  1. Wählen Sie die Zelle über den Funktionen COUNTIFS () aus, E2.
  2. Klicken Sie auf die Registerkarte Daten und dann in der Gruppe Sortieren und Filtern auf Filtern. Dadurch werden den Spalten B bis E Filter-Dropdowns hinzugefügt.
  3. Deaktivieren Sie in der Dropdown-Liste in Zelle E2 die Option (Alle auswählen) und aktivieren Sie 1 (siehe Abbildung C) .
  4. Klicken Sie auf OK, um den in Abbildung D gezeigten gefilterten Satz anzuzeigen .

Abbildung C.

Filtern Sie nach dem Wert 1, der einen doppelten Datensatz kennzeichnet.

Abbildung D.

Der gefilterte Datensatz.

Wenn Sie wie ich sind, finden Sie wahrscheinlich einen Filter weniger als zufriedenstellend; Sie sehen das Duplikat nur in einem Datensatz. Sie können einen zweiten Satz von COUNTIFS () hinzufügen und auch den zweiten Satz filtern, aber Sie sehen nur die Duplikate. Wenn Sie Duplikate ohne Filterung unterscheiden möchten, versuchen Sie die bedingte Formatierung.

Bedingte Formatierung und COUNTIFS ()

Durch die Kombination der bedingten Formatierung und derselben COUNTIFS () -Funktionen können Sie ein größeres Bild erzielen. Der erste Datensatz ist einfach, aber entfernen Sie zuerst den Filter, den Sie im letzten Abschnitt hinzugefügt haben. Gehen Sie dann wie folgt vor:

  1. Wählen Sie B3: D7.
  2. Klicken Sie auf die Registerkarte Startseite, klicken Sie in der Gruppe Stile auf Bedingte Formatierung, und wählen Sie in der Dropdown-Liste die Option Neue Regel aus.
  3. Wählen Sie die Option Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
  4. Geben Sie im Formelsteuerelement = $ E3 = 1 ein .
  5. Klicken Sie auf Format, klicken Sie auf die Registerkarte Füllen, wählen Sie eine Farbe aus und klicken Sie auf OK. Abbildung E zeigt das Format und die Regel.
  6. Klicken Sie auf OK, um zu dem in Abbildung F gezeigten Blatt zurückzukehren.

Abbildung E.

Geben Sie eine Regel ein und wählen Sie ein Format.

Abbildung F.

Die neue Regel hebt das einzige Duplikat in Zeile 5 hervor.

Fügen Sie als Nächstes die folgenden COUNTIFS () -Funktionen zum zweiten Datensatz hinzu:

 = COUNTIFS ($ B $ 3: $ B $ 7, B11, $ C $ 3: $ C $ 7, C11, $ D $ 3: $ D $ 7, D11) 

Wiederholen Sie nun die obigen Schritte, wählen Sie jedoch in Schritt 1 B11: D15 aus und geben Sie in Schritt 4 die Formel = $ E11 = 1 ein. Abbildung G zeigt die Ergebnisse. In unserem Beispiel befinden sich die doppelten Zeilen innerhalb des Datensatzes an derselben Position, die Lösung funktioniert jedoch unabhängig von ihrer Position. Es ist eine einzigartige Lösung für eine einzigartige Situation.

Abbildung G.

Die bedingte Formatierung hebt beide doppelten Datensätze ohne Filterung hervor.

Microsoft Weekly Newsletter

Seien Sie der Microsoft-Insider Ihres Unternehmens mithilfe dieser Windows- und Office-Tutorials und der Analysen unserer Experten zu Microsoft-Unternehmensprodukten. Wird montags und mittwochs geliefert

Heute anmelden

Senden Sie mir Ihre Frage zu Office

Ich beantworte die Fragen der Leser, wenn ich kann, aber es gibt keine Garantie. Senden Sie keine Dateien, es sei denn, Sie werden dazu aufgefordert. Erste Hilfeanfragen, die mit angehängten Dateien eingehen, werden ungelesen gelöscht. Sie können Screenshots Ihrer Daten senden, um Ihre Frage zu klären. Seien Sie so genau wie möglich, wenn Sie mich kontaktieren. Beispiel: "Bitte beheben Sie Fehler in meiner Arbeitsmappe und beheben Sie, was falsch ist" wird wahrscheinlich keine Antwort erhalten, aber "Können Sie mir sagen, warum diese Formel nicht die erwarteten Ergebnisse zurückgibt?" könnte. Bitte erwähnen Sie die App und Version, die Sie verwenden. TechRepublic erstattet mir weder meine Zeit noch mein Fachwissen, wenn ich Lesern helfe, noch verlange ich von den Lesern, denen ich helfe, eine Gebühr. Sie können mich unter kontaktieren.

© Copyright 2021 | mobilegn.com