Een vriend belde me en vroeg of er een manier is om serienummers zo te hebben dat ze geen duplicatie in serienummers in Excel zijn.
Iets zoals hieronder weergegeven:
Hij wilde dat het serienummer voor India overal 1 zou zijn. Evenzo is de VS het 2e land en moet het altijd 2 als serienummer hebben.
Dit zette me aan het denken.
En hier zijn de twee manieren die ik kon bedenken om duplicatie in serienummers in Excel te voorkomen.
Methode #1 - Functie VERT.ZOEKEN gebruiken
De eerste manier is om onze geliefde VERT.ZOEKEN-functie te gebruiken.
Om dit te doen, hebben we eerst een unieke lijst met landen nodig. Hier zijn de stappen om dat te doen:
- Maak een kopie van de lijst met landen (kopieer en plak deze in hetzelfde werkblad of een ander werkblad).
- Selecteer de gekopieerde gegevens en ga naar Gegevens -> Duplicaten verwijderen. Het opent het dialoogvenster duplicaat verwijderen.
- Zorg ervoor dat de optie - Mijn gegevens hebben kopteksten is aangevinkt (in het geval uw gegevens de koptekst hebben. Schakel het anders uit).
- Selecteer de kolom waaruit u de duplicaten wilt verwijderen.
- Klik OK.
- Dat is het. U krijgt een lijst met unieke landnamen.
Zie ook: De ultieme gids voor het zoeken en verwijderen van duplicaten in Excel.
Wijs nu de serienummers toe aan elk land. Zorg ervoor dat deze nummers rechts van de unieke landenlijst zijn ingevoerd, aangezien VERT.ZOEKEN geen gegevens kan ophalen aan de linkerkant van de opzoekwaarde.
Gebruik in de cel waar u de serienummers wilt (B3:B15), de onderstaande VERT.ZOEKEN-formule:
=VERT.ZOEKEN(C3,$F$3:$G$8,2,0)
Deze VERT.ZOEKEN-formule neemt de landnaam als opzoekwaarde, controleert deze in de gegevens in F3:G8 en retourneert het serienummer.
Methode #2 - Een dynamische formule
Hoewel de VERT.ZOEKEN-methode een prima manier is om dit te doen, is deze niet dynamisch.
Dus als ik een nieuw land toevoeg of een bestaand land wijzig, zou deze methode niet werken en zul je het hele proces van methode #1 opnieuw moeten herhalen.
Hier is een formule die het dynamisch maakt:
=IF(COUNTIF($C$3:$C4,$C4)=1,MAX($B$3:$B3)+1,INDEX($B$3:$C$18,MATCH($C4,$C$3:$ C4,0),1))
Om deze formule te gebruiken, moet u handmatig 1 invoeren in de eerste cel en de bovenstaande formule in alle andere resterende cellen.
Hoe het werkt:
Het gebruikt een ALS-functie die het aantal keren controleert dat een land vóór die rij is opgetreden. Als de landnaam voor het eerst voorkomt, is de telling 1 en is de voorwaarde WAAR, en als de landnaam ook eerder is voorgekomen, is de telling meer dan 1 en is de voorwaarde ONWAAR.
- Wanneer de voorwaarde WAAR is:
=MAX($B$3:$B3)+1
Als de waarde TRUE is, wat betekent dat de landnaam voor het eerst verschijnt, identificeert het de maximale waarde van het serienummer tot dan toe en voegt er 1 aan toe om het volgende serienummer te geven.
- Wanneer Waarde indien ONWAAR:
=INDEX($B$3:$C$18,MATCH($C4,$C$3:$C4,0),1)
Als het land al eerder is voorgekomen, gaat deze formule naar de cel waar het het eerst voorkomt en retourneert het serienummer van het eerste voorkomen van dat land.
Download het voorbeeldbestand
Misschien vind je de volgende Excel-zelfstudies misschien ook leuk:
- Hoe gebruik je Flash Fill in Excel.
- Sorteer automatisch gegevens in alfabetische volgorde met behulp van formule.
- Snel getallen in cellen vullen zonder te slepen.
- Hoe vulhandgreep in Excel te gebruiken.
