Letzte Nacht in Craig Crossmans Computer America-Radiosendung hatte Joe aus Boston eine Frage:
Ich habe eine Spalte mit Rechnungsnummern. Wie kann ich Excel verwenden, um die Duplikate zu markieren?
Ich schlug vor, bedingte Formate und die COUNTIF-Formel zu verwenden. Hier sind die Details, wie das funktioniert.
Wir möchten die bedingte Formatierung für den gesamten Bereich einrichten, aber es ist einfacher, ein bedingtes Format für die erste Zelle im Bereich einzurichten und dieses bedingte Format dann zu kopieren. In unserem Fall hat Zelle A1 eine Überschrift mit der Rechnungsnummer, daher wähle ich Zelle A2 aus und wähle im Menü Format> Bedingte Formatierung. Das Dialogfeld "Bedingte Formatierung" beginnt mit der ersten Dropdown-Liste "Zellenwert ist". Wenn Sie den Pfeil daneben berühren, können Sie "Formel ist" auswählen.
Nach Auswahl von "Formel ist" ändert das Dialogfeld das Erscheinungsbild. Anstelle von Feldern für "Zwischen x und y" gibt es jetzt ein einzelnes Formelfeld. Diese Formelbox ist unglaublich leistungsfähig. Sie können jede Formel eingeben, die Sie sich vorstellen können, solange diese Formel als WAHR oder FALSCH ausgewertet wird.
In unserem Fall müssen wir eine COUNTIF-Formel verwenden. Die Formel, die in das Feld eingegeben werden muss, lautet
=COUNTIF(A:A,A2)>1
Auf Englisch heißt es: "Sehen Sie sich den gesamten Bereich von Spalte A an. Zählen Sie, wie viele Zellen in diesem Bereich den gleichen Wert haben wie in A2. (Es ist wirklich wichtig, dass das" A2 "in der Formel auf das zeigt Aktuelle Zelle - die Zelle, in der Sie die bedingte Formatierung einrichten. Wenn sich Ihre Daten in Spalte E befinden und Sie die erste bedingte Formatierung in E5 einrichten, lautet die Formel =COUNTIF(E:E,E5)>0). Dann vergleichen wir, ob diese Anzahl vorhanden ist ist> 1. Idealerweise ist die Anzahl ohne Duplikate immer 1 - da Zelle A2 im Bereich liegt - sollten wir genau eine Zelle in Spalte A finden, die den gleichen Wert wie A2 enthält.
Klicken Sie auf die Schaltfläche Formatieren…
Jetzt ist es Zeit, ein widerliches Format auszuwählen. Oben in diesem Dialogfeld "Zellen formatieren" befinden sich drei Registerkarten. Die Registerkarte "Schriftart" ist normalerweise die erste, sodass Sie eine fette, rote Schriftart auswählen können, aber ich mag etwas Abscheulicheres. Normalerweise klicke ich auf die Registerkarte Muster und wähle entweder leuchtend rot oder hellgelb. Wählen Sie die Farbe aus und klicken Sie auf OK, um das Dialogfeld Zellen formatieren zu schließen.
Das ausgewählte Format wird im Feld "Vorschau des zu verwendenden Formats" angezeigt. Klicken Sie auf OK, um das Dialogfeld "Bedingte Formatierung" zu schließen.
… Und nichts passiert. Wow. Wenn Sie zum ersten Mal eine bedingte Formatierung einrichten, wäre es sehr schön, hier ein Feedback zu erhalten, dass dies funktioniert hat. Aber wenn Sie nicht das Glück haben, dass der 1098 in Zelle A2 ein Duplikat einer anderen Zelle ist, ist die Bedingung nicht wahr und es sieht so aus, als wäre nichts passiert.
Sie müssen die bedingte Formatierung von A2 in die anderen Zellen in Ihrem Bereich kopieren. Führen Sie mit der Cursorschwelle in A2 Bearbeiten> Kopieren aus. Drücken Sie Strg + Leertaste, um die gesamte Spalte auszuwählen. Bearbeiten> Spezial einfügen. Klicken Sie im Dialogfeld "Inhalte einfügen" auf "Formate". OK klicken.
Dadurch wird die bedingte Formatierung in alle Zellen in der Spalte kopiert. Jetzt - endlich - sehen Sie einige Zellen mit der roten Formatierung, die darauf hinweisen, dass Sie ein Duplikat haben.
Es ist informativ, in Zelle A3 das bedingte Format nach der Kopie zu überprüfen. Wählen Sie A3 und drücken Sie od, um die bedingte Formatierung aufzurufen. Die Formel im Feld Formel ist geändert, um zu zählen, wie oft A3 in der Spalte A: A angezeigt wird.
Anmerkungen
In Joes Frage hatte er nur 1700 Rechnungen im Sortiment. Ich habe 65536 Zellen mit bedingter Formatierung eingerichtet und jede Zelle vergleicht die aktuelle Zelle mit 65536 anderen Zellen. In Excel 2005 - mit mehr Zeilen - wird das Problem noch schlimmer. Technisch könnte die Formel im ersten Schritt gewesen sein:=COUNTIF($A$2:$A$1751,A2)>1
Wenn Sie das bedingte Format in die gesamte Spalte kopieren, können Sie stattdessen auch nur die Zeilen mit Daten auswählen, bevor Sie die speziellen Formate einfügen ausführen.
Mehr
Das andere Problem, das ich nach der Frage beschrieben habe, ist, dass Sie eine Spalte wirklich nicht nach einem bedingten Format sortieren können. Wenn Sie diese Daten so sortieren müssen, dass sich die Duplikate in einem Bereich befinden, führen Sie die folgenden Schritte aus. Fügen Sie zunächst B1 eine Überschrift mit dem Namen "Duplizieren?" Hinzu. Geben Sie diese Formel in B2 ein : =COUNTIF(A:A,A2)>1.
Klicken Sie mit dem Zellenzeiger in B2 auf das AutoFill-Handle (das kleine Quadrat in der unteren rechten Ecke der Zelle), um die Formel im gesamten Bereich zu kopieren.
Sie können jetzt nach Spalte B absteigend und A aufsteigend sortieren, um die Problemrechnungen oben im Bereich zu haben.
Bei dieser Lösung wird davon ausgegangen, dass Sie BEIDE doppelten Rechnungen markieren möchten, damit Sie manuell herausfinden können, welche Rechnungen gelöscht oder korrigiert werden sollen. Wenn Sie das erste Auftreten des Duplikats nicht markieren möchten, können Sie die Formel wie folgt anpassen : =COUNTIF($A$2:$A2,A2)>1. Es ist wichtig, die Dollarzeichen genau wie gezeigt einzugeben. Dadurch werden nur alle Zellen ab der aktuellen Zelle nach doppelten Einträgen durchsucht.
Vielen Dank an Joe aus Boston für die Frage!
