Hoe kun je ontbrekende opeenvolgende nummers in Excel identificeren?

Stel, je hebt een lange lijst met opeenvolgende nummers om items te markeren, zoals chequenummers in bankafschriften. Meestal scrollen we handmatig door de lijst om ontbrekende nummers te vinden, wat soms behoorlijk lastig en tijdrovend kan zijn. Je denkt misschien aan slimme manieren om dit aan te pakken. Gelukkig zijn er verschillende eenvoudige methoden om snel en gemakkelijk ontbrekende nummers in Excel2007, Excel2010 en Excel2013 te identificeren en te lokaliseren.

Ontbrekende opeenvolgende nummers identificeren met een IF-formule

Ontbrekende opeenvolgende nummers identificeren met een matrixformule

Ontbrekende opeenvolgende nummers snel identificeren met Kutools voor Excel

Ontbrekende opeenvolgende nummers identificeren met een IF-formule

Zoals we allemaal weten, hebben de meeste opeenvolgende nummers een vaste toename van1, zoals1,2,3, …, N. Dus als je ziet dat een nummer niet precies1 lager is dan het volgende nummer, ontbreekt er een nummer.

We laten je de stappen zien aan de hand van het volgende voorbeeld en screenshot:

1. Voer in een lege cel de formule =IF(A3-A2=1,"","Missing") in en druk op Enter. In dit geval voeren we de formule in in cel B2.

Als er geen ontbrekende nummers zijn, geeft deze formule niets terug; als er wel ontbrekende nummers zijn, verschijnt de tekst "Missing" in de actieve cel.

2. Selecteer cel B2 en sleep de vulgreep over het bereik waarin je deze formule wilt toepassen. Nu worden de ontbrekende nummers aangeduid met de tekst "Missing" in de overeenkomstige cellen van Kolom B. Zie de volgende screenshot:

Ontbrekende opeenvolgende nummers identificeren met een matrixformule

Soms wil je niet alleen de ontbrekende nummers identificeren, maar ze ook daadwerkelijk weergeven. Volg hiervoor deze stappen:

1. Voer in de aangrenzende cel de formule = SMALL(IF(ISNA(MATCH(ROW(A$1:A$30),A$1:A$30,0)),ROW(A$1:A$30)),ROW(A1)) in.

A1:A30 = bereik van nummers; de reeks die gecontroleerd wordt loopt van1 tot30

2. Druk tegelijkertijd op Ctrl + Shift + Enter om de formule af te ronden. Kopieer de formule naar beneden totdat je #GETAL! fouten ziet; dit betekent dat alle ontbrekende nummers zijn weergegeven. Zie screenshot:

Ontbrekende opeenvolgende nummers snel identificeren met Kutools voor Excel

De bovenstaande methoden werken alleen voor zuivere nummerreeksen. Als je een reeks hebt zoals AA-1001-BB, AA-1002-BB, werken ze mogelijk niet goed. Geen zorgen, de krachtige functie Find Missing Sequence Number van Kutools voor Excel helpt je snel de ontbrekende reeks te vinden.

Na het installeren van Kutools voor Excel, ga je als volgt te werk:

1. Selecteer de gegevensreeks waarin je de ontbrekende nummers wilt vinden.

2. Klik op Kutools > Invoegen > Ontbrekende reeks vinden, zie screenshot:

3. In het dialoogvenster Ontbrekende reeks vinden:

(1.) Als je de optie Een nieuwe kolom maken om opgegeven inhoudsmarkers in te vullen kiest, worden alle ontbrekende nummers gemarkeerd met de tekst Missing in een nieuwe kolom naast je gegevens. Zie screenshot:

(2.) Als je de optie Ontbrekende gegevens invoegen kiest, worden alle ontbrekende nummers ingevoegd in de reekslijst. Zie screenshot:

(3.) Als je de optie Lege rijen invoegen bij ontbrekende nummers kiest, worden er lege rijen ingevoegd waar nummers ontbreken. Zie screenshot:

(4.) Als je de optie Achtergrondkleur invullen kiest, wordt de locatie van de ontbrekende nummers direct gemarkeerd. Zie screenshot:

Demo: Ontbrekende opeenvolgende nummers snel identificeren met Kutools voor Excel