Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

Comment identifier la séquence des numéros manquants dans Excel?

Imaginons que vous ayez une longue liste de numéros de séquence pour marquer des articles, tels que des numéros de chèques dans des relevés bancaires, normalement nous parcourons et localisons les numéros de séquence manquants manuellement. Parfois, cela est assez ardu et prend du temps. Vous pouvez penser à des moyens délicats pour y faire face. Oui, il existe plusieurs manières simples d'identifier et de localiser rapidement et facilement la séquence des numéros manquants dans Excel 2007, Excel 2010 et Excel 2013.

Identifier la séquence des nombres manquants avec la formule IF

Identifier la séquence des nombres manquants avec une formule matricielle

Identifier rapidement la séquence des numéros manquants avec Kutools for Excel


Trouver et mettre en évidence les numéros manquants de la séquence:

Kutools for Excel's Trouver un numéro de séquence manquant La fonction peut vous aider à trouver rapidement et facilement la séquence manquante, et insérer les numéros manquants ou les lignes vides dans la séquence de données existante, ou remplir la couleur d'arrière-plan lorsque vous rencontrez la séquence manquante.

doc-identifier-missing-numbers 6

Kutools for Excel: avec plus que 200 compléments Excel pratiques, libre d'essayer sans limitation dans les jours 60. Téléchargez et essai gratuit maintenant!


flèche bleue droite bulle Identifier la séquence des nombres manquants avec la formule IF


Comme nous le savons tous, la plupart des numéros de séquence sont à incrément fixe de 1, tels que 1, 2, 3, ..., N. Par conséquent, si vous pouvez identifier le nombre n'est pas moins 1 que le nombre suivant, il y a un nombre manquant .

Nous allons vous montrer les tutoriels avec un exemple comme les captures d'écran suivantes:

doc identifie les numéros manquants 1

1. Dans une cellule vide, entrez la formule de = IF (A3-A2 = 1, "", "Manquant")et appuyez sur Entrer clé. Dans ce cas, nous entrons la formule dans la cellule B2.

doc-identifier-missing-numbers2

S'il n'y a pas de nombres manquants, cette formule ne retournera rien; si des numéros manquants existent, il retournera le texte de "Missing" dans la cellule active.

2. Sélectionnez la cellule B2 et faites glisser la poignée de remplissage sur la plage de cellules que vous souhaitez contenir cette formule. Maintenant, il identifie les numéros manquants avec le texte de "manquant" dans les cellules correspondantes de la colonne B. Voir la capture d'écran suivante:

doc-identifier-missing-numbers3


flèche bleue droite bulle Identifier la séquence des nombres manquants avec une formule matricielle

Parfois, il faut non seulement identifier la séquence des numéros manquants, mais également indiquer les numéros manquants. Vous pouvez le traiter avec les étapes suivantes:

1. dans la cellule adjacente, entrez la formule = SMALL(IF(ISNA(MATCH(ROW(A$1:A$30),A$1:A$30,0)),ROW(A$1:A$30)),ROW(A1))

A1: A30 = plage de nombres, la séquence à vérifier va de 1 à 30

2. appuie sur le Ctrl + Maj + Entrée Clés ensemble pour finir la formule. Copiez la formule jusqu'à ce que vous obteniez #NUM! Des erreurs signifient que tous les numéros manquants ont été listés. Voir la capture d'écran:

doc-identifier-missing-numbers4


flèche bleue droite bulle Identifier rapidement la séquence des numéros manquants avec Kutools for Excel

Les méthodes ci-dessus peuvent seulement identifier la séquence de nombres purs manquante, si vous avez la séquence telle que AA-1001-BB, AA-1002-BB, ils peuvent ne pas fonctionner avec succès. Mais, ne t'inquiète pas, Kutools for ExcelLa puissante fonctionnalité de Trouver un numéro de séquence manquant peut vous aider à identifier rapidement la séquence manquante.

Kutools for Excel : avec plus de 300 compléments Excel pratiques, libre d'essayer sans limitation dans 60 jours.

Après l'installation de Kutools for Excel, s'il vous plaît faites comme suit:

1. Sélectionnez la séquence de données dans laquelle vous souhaitez rechercher la séquence manquante.

2. Cliquez Kutools > insérer > Trouver un numéro de séquence manquant, voir capture d'écran:

doc-identifier-missing-numbers 5

3. dans le Trouver un numéro de séquence manquant boite de dialogue:

(1.) Si vous choisissez Insérer une nouvelle colonne avec le marqueur manquant suivant option, tous les numéros de séquence manquants ont été marqués avec le texte Manquant dans une nouvelle colonne à côté de vos données. Voir la capture d'écran:

doc-identifier-missing-numbers 6

(2.) Si vous choisissez Insérer un numéro de séquence manquant option, tous les numéros manquants ont été insérés dans la liste des séquences. Voir la capture d'écran:

doc-identifier-missing-numbers 7

(3.) Si vous choisissez Insertion de lignes vides lors de la création de numéros de séquence manquants option, toutes les lignes vides sont insérées lorsqu'il manque des nombres. Voir la capture d'écran:

doc-identifier-missing-numbers 8

(4.) Si vous choisissez Remplir la couleur de fond En option, l'emplacement des numéros manquants sera mis en évidence en même temps. Voir la capture d'écran:

doc-identifier-missing-numbers 9


flèche bleue droite bulle Identifier rapidement la séquence des numéros manquants avec Kutools for Excel

Kutools for Excel: avec plus que 200 compléments Excel pratiques, libre d'essayer sans limitation dans les jours 60. Téléchargez et essai gratuit maintenant!


Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe Créer une liste de diffusion et envoyer des emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit le nombre de clics de souris pour vous chaque jour!
fond officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Inder · 1 years ago
    Thankyou... It helped a lot... God bless you
  • To post as a guest, your comment is unpublished.
    Brooke · 2 years ago
    I have a list and this has been extremely helpful. I am trying to list the existing numbers in a sequence and then skip the missing (ext. 1, 2, 3, 5, 7 to show as 1 - 3, 5, 7). Is this possible to formulate?
  • To post as a guest, your comment is unpublished.
    sanchit Bhardwaj · 3 years ago
    Hello Community and excel Gurus;

    I wan to search missing numbers in a row of excel ,I tried to use the Kutool for excel ,After selecting the option to find missing numbers ,i am getting error the range can only contain number or text ?Though the range or row i had selected contains only number.
    Any expert advice ?

    Best,
    Sanchit Bhrdwaj
  • To post as a guest, your comment is unpublished.
    Mansur · 3 years ago
    Thank u Mannnnnn . Cheers
  • To post as a guest, your comment is unpublished.
    Sekhar · 3 years ago
    its good working but this formula very big how can remember this = SMALL(IF(ISNA(MATCH(ROW(A$1:A$30),A$1:A$30,0)),ROW(A$1:A$30)),ROW(A1))
  • To post as a guest, your comment is unpublished.
    Faizan · 4 years ago
    [google]send formul[/google]
  • To post as a guest, your comment is unpublished.
    Rick · 4 years ago
    Thats a beautiful post however the VBA macro code has a serious error, when you give it start and end number, it misses many randomly and thus the missing number list is incorrect. For example : in 1 to 100, I found 67 is not generated hence the missing number list never list 67 even if its not there.

    Any idea how to fix it ?
  • To post as a guest, your comment is unpublished.
    Shivi · 4 years ago
    Hi, After using the missing formula I am able to find how many values are missing from my data. However when I try to find what exact values (numbers) are missing its giving me an error. I have more than 7700 rows of data missing. Now these values missing are from multiple cheque book and each cheque book has 50 leafs. These 7700 are the missing leafs and missing numbers are in no order. By no order I mean 1st cheque or 2nd or 5th 35th 48th number anything could be missing. Plus I cannot even define a range as in case above. What should I do, please advice.
  • To post as a guest, your comment is unpublished.
    Shivi Bhatia · 4 years ago
    Hi Sir,

    This example above is nothing short of a miracle similar to what a pace maker is to a heart patient. Cant express my thanks in words but you are a god saver
  • To post as a guest, your comment is unpublished.
    Chris · 5 years ago
    Is there a way to include a blank row for each missing number without losing the format/data
  • To post as a guest, your comment is unpublished.
    Bruce · 5 years ago
    The =small(if(isna>>>>> formula causes excel to lock up. Any ideas folks?
  • To post as a guest, your comment is unpublished.
    Bruce · 5 years ago
    The =small(if(isna>>>>>>>>> formula causes my excel programe to lock up and cease to function. Any Ideas folks.
  • To post as a guest, your comment is unpublished.
    Ashish · 5 years ago
    "You can find out the missing number in array by following algorithm:

    /* getMissingNumber takes array and size of array as arguments*/
    int getMissingNumber (int arr[], int num)
    {
    int i;
    /* For xor of all the elemets in arary */
    int x1 = a[0];
    /* For xor of all the elemets from 1 to n+1 */
    int x2 = 1;

    for (i = 1; i< n; i++)
    x1 = x1^a[i];

    for ( i = 2; i
  • To post as a guest, your comment is unpublished.
    marty · 5 years ago
    will any of these work for phone numbers if I'm looking for phone numbers that are missing in a sequence?
  • To post as a guest, your comment is unpublished.
    jawale Jyotsna · 5 years ago
    Dear sir
    I want to find out d missing numbers from S-1 to S-27000 and J-1 to J-6500. Please send me the vba code for d same.
    The code available on net , when executed gives compilation error with WS.Sort
    Thaniking you,

    Jawale Jyo
  • To post as a guest, your comment is unpublished.
    jawale Jyotsna · 5 years ago
    This script was not executed, compile error generator as "With WS.Sort"[/quote]
  • To post as a guest, your comment is unpublished.
    jawale Jyotsna · 5 years ago
    Dear sir
    I want to find out d missing numbers from S-1 to S-27000 and J-1 to J-6500. Please send me the vba code for d same
    Thaniking you,

    Jawale Jyo
  • To post as a guest, your comment is unpublished.
    jawale Jyotsna · 5 years ago
    Dear,
    I want to find out missing numbers from S-1 to S-27000 and J-1 to J-7000 , U-1 to U-27000, C-1 to C-1848
  • To post as a guest, your comment is unpublished.
    jawale Jyotsna · 5 years ago
    Dear Sir,
    I want to find out missing numbers starting from S-1 to S-30,000 and J-1 to J-7000, U-1 to U-26932 and C-1 to C-1836 please send code, Thanking You. :-)
  • To post as a guest, your comment is unpublished.
    Chetan Sonawane · 5 years ago
    I want to find missing numbers starting from B00001 to B11223 which starts with alphabets, kindly send code for this.
  • To post as a guest, your comment is unpublished.
    Valda · 5 years ago
    Awesome. It worked. Grateful
  • To post as a guest, your comment is unpublished.
    Hama Chigubu · 5 years ago
    It worked for me, many thanx!!! :-)
  • To post as a guest, your comment is unpublished.
    Srikant.n · 5 years ago
    Thank you, it was a very useful tool as i had to search 1 missing number in 1-7000.

    thanking you once again
  • To post as a guest, your comment is unpublished.
    shanmugasundaram · 5 years ago
    Hi sir,

    It is working and very useful this formula thank you
  • To post as a guest, your comment is unpublished.
    midnightsquall · 5 years ago
    I tried the VBA code, and it doesn't work for dates. By that I mean if the range of data I'm using to determine for missing values is in the format: dd/mm/yyyy as opposed to a single integer. Is there an alteration to the VBA code that allows for the same process to be carried out?

    I used the =IF function and that works fine, but I'd be keen to get it working with the macros as well.
  • To post as a guest, your comment is unpublished.
    Farooq · 5 years ago
    I really enjoyed by executing all three functions in my excel sheet. It was marvelous.
  • To post as a guest, your comment is unpublished.
    bobo · 5 years ago
    Great VBA code! Thank you very much for it.

    As I have no idea about how to create a VBA code, could you please let me know if there is any chance to adapt this code to my needs as follows:
    1. the selected range to be by default (e.g. $C$2:$C$5000);
    2. the new worksheet to have only the column with the missing numbers (and without "missing values" header);
    3. to be able to add exceptions (e.g. numbers 4550432, 4552357, etc) that will not displayed in the new worksheet;
    4. if some missing numbers are subsequently added in the original worksheet, these numbers to be automatically erased from the new worksheet, without running again the macro;
    5. when I run again the macro, to create the same worksheet, with a particularly name.

    Thank you so much in advance!
    • To post as a guest, your comment is unpublished.
      Dlady · 4 years ago
      The instructions are clear about how to add the VB code... it is very easy.
  • To post as a guest, your comment is unpublished.
    Satya · 6 years ago
    Dear Sir,
    It is working fine sir,
    I put this formula in excel VB module , later I saved as *.xla.
    now pl help me, how to use it?
  • To post as a guest, your comment is unpublished.
    C N Murthy · 6 years ago
    1. VB script is not working as the same returned error as "With WS.Sort" as reported earlier by Mr.Reddy.

    2. The formula =SMALL is working for series starting from 1 and NOT for series starting from say 201 to 535. Then this formula is returning all missing nos. starting from 1 whereas ideally we want the missing nos. within the range from 201 to 535.
  • To post as a guest, your comment is unpublished.
    Sreenivasulu Reddy · 6 years ago
    This script was not executed, compile error generator as "With WS.Sort"
  • To post as a guest, your comment is unpublished.
    ROBERT123 · 6 years ago
    my report is like this....

    iam uploading an excel file called abc into another excel called xyz....

    That excel file abc rows directly writes into my Database...upto here i'm ok....

    i need solution like this below....

    no of rows read from abc excel while uploading into xyz excel...
    no of rows inserted into database from abc excel...
    if any rows while inserting into databse missing i need a message like "some rows were not inserted"
    i need those missing rows into separate sheet

    my code is in VBA,macros only...
  • To post as a guest, your comment is unpublished.
    ROBERT123 · 6 years ago
    Hi all....

    Here you are finding missing numbers and pasting into other sheet...

    But i need to findout missing rows insted of missing numbers...and i need it to paste that missing rows into a new sheet...

    is there any solution for me.....

    Thnaks
    • To post as a guest, your comment is unpublished.
      KALPANA · 5 years ago
      hi
      i need to find missimg number in total no of book.
      i have 20000 book in my library . i have tally 18000 no of book .iam find the miss no's of books.
      sir pleace help me.
      • To post as a guest, your comment is unpublished.
        Chetan · 5 years ago
        Press Alt+F11 button to open VB Screen, Press Insert then Module and paste above mentioned code and Press F5, it'll show you all the Missing nos in adjacent coloum, if your Acc.No. Start with letter like B, replace it with 0, & then do above operation.
        • To post as a guest, your comment is unpublished.
          Yelubolu Venkata Suryanarayana · 2 years ago
          Sir
          Please send me VBA code for finding missing numbers among 65000 in a column in a less than three minutes
        • To post as a guest, your comment is unpublished.
          Y.V.SURYANARAYANA · 2 years ago
          Sir
          Can u send me the vba code to find missing numbers in 65000 numbers in a column. It should take three minutes time