Hier ist unsere Formel

Question

Ich muss Zellen mit unterschiedlichen Werten in denselben Spalten bedingt formatieren.WennIhr Wert für eine bestimmte Spalte ist derselbe.

Szenario

Meine Tabelle ist eine Kontaktedatenbank, in der jede Zeile ein Kontaktdatensatz ist und jede Spalte einen Datentyp (Nachname, Straße 1, Straße 2, Stadt, Bundesland usw.) für jeden Kontaktdatensatz enthält.

In der Tabelle sind die Kontaktdaten der verschiedenen Büros unseres Unternehmens zusammengefasst. Wir haben alle Kontaktdaten in derselben Quelle zusammengeführt, damit wir etwaige Abweichungen klären und alle unsere Kontakte künftig am selben Ort aufbewahren können.

Einige Kontakte haben nur einen Datensatz – beispielsweise wenn nur eines unserer Büros Daten für diesen Kontakt hatte, sodass diese nicht mit den Daten anderer Büros übereinstimmen können; andere Kontakte haben mehrere Datensätze (d. h. es könnte nur eine Zeile für Fred Johnson, aber vier Zeilen für Jane Smith geben). Jeder Kontakt muss genau dieselben Kontaktinformationen haben, bevor wir ihn in eine Cloud-Datenbank hochladen können, sonst entstehen Duplikate.

Wir haben lange versucht, die Daten zwischen den Datensätzen derselben Person abzugleichen. Nun müssen wir herausfinden, wo noch Diskrepanzen bestehen.

Ich habe die Felder „Nachname“ und „Vorname“ verkettet, sodass Zeilen mit demselben Wert in diesem Feld miteinander verglichen werden können. Ziel ist es, Zellen mit unterschiedlichen Daten in derselben Spalte hervorzuheben, wenn sie mit anderen Datensätzen mit demselben Wert in der Spalte „Nachname“ (sofern vorhanden) verglichen werden.

Wie kann ich das machen?

Mir ist bisher bewusst, dass die Funktion „Spezial suchen“ Zellen hervorheben kann, wenn sie mit einer bestimmten Zeile oder Spalte verglichen werden, aber ich muss die Vergleiche mit Zeilen durchführen, die den gleichen Wert für eine bestimmte Spalte aufweisen.

Um diese Funktion auszuführen, habe ich Zugriff auf Excel 2010 und 2013.

Danke schön!

Answer 1

Es gibt möglicherweise elegantere Lösungen, aber Sie sollten Vor- und Nachnamen nicht aneinanderreihen müssen, um eine Spalte „FullName“ zu erstellen.

Vorschau unserer Ausgabe

(die weißen Zellen auf der rechten Seite zeigen, welche Ausgabe unsere Formel zur Steuerung der bedingten Formatierung erzeugt):

Hier ist unsere Formel

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
   -COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

Denken Sie daran, Anpassungen an die Zeilenanzahl in Ihrem Datensatz und an die Position Ihrer Vor- und Nachnamenspalten vorzunehmen, wenn diese nicht in A und B stehen.

Ich bin außerdem davon ausgegangen, dass Ihre ersten Nicht-Namensdaten in Zelle C2 beginnen – das heißt, dies sind die Daten ganz oben links, die Sie auf Nichtübereinstimmungen prüfen möchten. Bearbeiten Sie die „C“s in dieser Formel, falls nötig.

Anwenden der bedingten Formatierung

Kopieren Sie die obige Formel (nehmen Sie alle erforderlichen Änderungen vor) und klicken Sie dann auf die Zelle ganz oben links, die Sie auf Nichtübereinstimmungen prüfen möchten (ich beziehe mich ab hier auf „C2“). Wählen Sie nun die Conditional FormattingSchaltfläche in der Multifunktionsleiste aus und wählen Sie New Rule.

Wählen Sie aus Use a formula to determine which cells to formatund fügen Sie dann Ihre Formel in das Textfeld ein (achten Sie darauf, dass keine Leerzeichen vorhanden sind – wenn Sie von oben kopiert haben, müssen Sie einige entfernen). Jetzt können Sie klicken, Formatum festzulegen, wie Sie Nichtübereinstimmungen hervorheben möchten. Ich habe mich für eine (knallige!) rote Füllung entschieden. Klicken Sie, OKbis Sie zur Tabelle zurückkehren.

Wenn in Zelle C2 keine Nichtübereinstimmung vorliegt, sieht es möglicherweise so aus, als sei nichts passiert. Das liegt jedoch daran, dass wir die Regel noch auf Ihren gesamten Datensatz anwenden müssen. Lassen Sie C2 ausgewählt und klicken Sie Manage Rulesim Conditional FormattingMenüband auf .

Jetzt können Sie den gesamten Bereich auswählen, den Sie auf Nichtübereinstimmungen prüfen möchten. Klicken und ziehen Sie im Applies toFeld, um alles auszuwählen, was Sie vergleichen möchten (oder geben Sie zur Beschleunigung einfach den Zellbezug ein, wenn Sie viele Zeilen haben =$C$2:$Z$999) .

Klicken Sie auf „OK“ und das war‘s!

Wie es funktioniert

Mit dieser Formel wird COUNTIFS()ermittelt, wie viele Zeilen für diese Person vorhanden sind:

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)

Dann ermitteln wir, wie viele Zeilen den Namen dieser Person UND den Wert in der von Ihnen überprüften Spalte enthalten. Wenn alle Zeilen identisch sind, sollte diese Zahl genau mit der ersten übereinstimmen COUNTIFS().

COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

Wenn wir Letzteres vom Ersteren subtrahieren und alle Zeilen übereinstimmen, gibt die Formel 0 aus und führt keine bedingte Formatierung durch. Wenn jedoch irgendetwas anders ist, ist die Ausgabe 1 oder höher und löst die bedingte Formatierung aus.

Notiz

Ich musste das letzte COUNTIFS()Kriterium in eine IF()Anweisung einschließen, um mit Leerzeichen umgehen zu können – COUNTIFSmag Leerzeichen nicht besonders (es scheint unsicher zu sein, ob sie als 0 oder "" gezählt werden sollen. Merkwürdig).

---

Datei download

Dieses Beispieldokument ist auchzum Download verfügbar.