Ein Freund rief mich an und fragte, ob es eine Möglichkeit gibt, Seriennummern so zu erstellen, dass sie keine Duplizierung in Seriennummern in Excel sind.
Etwas wie unten gezeigt:
Er wollte, dass die Seriennummer für Indien überall dort steht, wo sie vorkommt. Ebenso ist USA das zweite Land und sollte immer 2 als Seriennummer haben.
Das hat mich zum Nachdenken gebracht.
Und hier sind die zwei Möglichkeiten, die ich mir einfallen lassen könnte, um Duplikate in Seriennummern in Excel zu vermeiden.
Methode #1 - Verwenden der SVERWEIS-Funktion
Die erste Möglichkeit besteht darin, unsere beliebte SVERWEIS-Funktion zu verwenden.
Dazu benötigen wir zunächst eine eindeutige Länderliste. Hier sind die Schritte dazu:
- Erstellen Sie eine Kopie der Länderliste (kopieren Sie sie in dasselbe Arbeitsblatt oder ein anderes Arbeitsblatt).
- Wählen Sie die kopierten Daten aus und gehen Sie zu Daten -> Duplikate entfernen. Es öffnet das Dialogfeld Duplikat entfernen.
- Stellen Sie sicher, dass die Option - Meine Daten haben Kopfzeilen aktiviert ist (falls Ihre Daten die Kopfzeile haben. Andernfalls deaktivieren Sie sie).
- Wählen Sie die Spalte aus, aus der Sie die Duplikate entfernen möchten.
- OK klicken.
- Das ist es. Sie haben eine Liste mit eindeutigen Ländernamen.
Siehe auch: Der ultimative Leitfaden zum Suchen und Entfernen von Duplikaten in Excel.
Ordnen Sie nun jedem Land die Seriennummern zu. Stellen Sie sicher, dass diese Zahlen rechts neben der eindeutigen Länderliste eingegeben werden, da SVERWEIS keine Daten von der linken Seite des Nachschlagewerts abrufen kann.
Verwenden Sie in der Zelle, in der Sie die Seriennummern haben möchten (B3: B15), die folgende SVERWEIS-Formel:
=SVERWEIS(C3,$F$3:$G$8,2,0)
Diese SVERWEIS-Formel verwendet den Ländernamen als Nachschlagewert, sucht in den Daten in F3:G8 danach und gibt seine Seriennummer zurück.
Methode #2 – Eine dynamische Formel
Die SVERWEIS-Methode ist zwar eine vollkommen gute Möglichkeit, dies zu tun, sie ist jedoch nicht dynamisch.
Wenn ich also ein neues Land hinzufüge oder ein bestehendes Land ändere, würde diese Methode nicht funktionieren und Sie müssen den gesamten Vorgang von Methode 1 erneut wiederholen.
Hier ist eine Formel, die es dynamisch macht:
=IF(COUNTIF($C$3:$C4,$C4)=1,MAX($B$3:$B3)+1,INDEX($B$3:$C$18,MATCH($C4,$C$3:$ C4,0),1))
Um diese Formel zu verwenden, müssen Sie 1 manuell in die erste Zelle und die obige Formel in alle anderen verbleibenden Zellen eingeben.
Wie es funktioniert:
Es verwendet eine IF-Funktion, die überprüft, wie oft ein Land vor dieser Zeile aufgetreten ist. Wenn der Ländername zum ersten Mal auftritt, ist die Zählung 1 und die Bedingung ist TRUE, und ist der Ländername auch früher aufgetreten, ist die Zählung größer als 1 und die Bedingung ist FALSE.
- Wenn die Bedingung WAHR ist:
=MAX($B$3:$B3)+1
Wenn der Wert TRUE ist, was bedeutet, dass der Ländername zum ersten Mal erscheint, identifiziert er den maximalen Wert der Seriennummer bis dahin und addiert 1 zum nächsten Seriennummernwert.
- Wenn Wert wenn FALSE:
=INDEX($B$3:$C$18,MATCH($C4,$C$3:$C4,0),1)
Wenn das Land bereits früher aufgetreten ist, geht diese Formel in die Zelle, in der es zuerst erscheint, und gibt die Seriennummer des ersten Auftretens dieses Landes zurück.
Laden Sie die Beispieldatei herunter
Die folgenden Excel-Tutorials könnten Ihnen auch gefallen:
- So verwenden Sie Flash Fill in Excel.
- Sortieren Sie Daten automatisch in alphabetischer Reihenfolge mithilfe von Formeln.
- So füllen Sie schnell Zahlen in Zellen aus, ohne sie zu ziehen.
- So verwenden Sie das Füllhandle in Excel.
