Lister les nombres manquants d'une série

[Niloula]

Bonjour à tous,

Je reviens vers vous avec mes nombres car je n'arrive pas à en faire ce que je veux et ce n'est pas faute d'essayer.

Voilà, j'ai une colonne de nombres de 0 à 1057 (pour l'instant) mais qui ne sont pas continus (il manque des nombres).

Ma demande est : puis-je, dans une autre colonne, faire apparaitre les nombres manquants de ma première colonne ainsi que le dernier nombre utilisé +1 (ex : mon dernier nombre est 1200 et dans ma colonne s'affiche 1201).

Merci d'avance pour votre aide.

Sarah

 

[R@chid]

Re : Lister les nombres manquants d'une série

Bonjour,
voir si j'ai bien compris ta demande
Amicalement

 

[Niloula]

Re : Lister les nombres manquants d'une série

Pardon R@chid, je n'avais pas vu votre réponse ! Eh bien votre formule à l'air de marcher sur l'exemple mais je ne sais pas bien comment elle fonctionne et je n'arrive pas à l'intégrer à mon fichier. J'ai selectionné la deuxième cellule de ma colonne (D) et copier la formule de votre fichier :
=SI(LIGNES($2:2)<=SOMME(N(NB.SI(MesNums;ListeNums)=0));MIN(SI((NB.SI(MesNums;ListeNums)=0)*(NB.SI(D$11;ListeNums)=0);ListeNums));SI(LIGNES($2:2)=SOMME(N(NB.SI(MesNums;ListeNums)=0))+1;MAX(MesNums)+1;""))
mais cela écrit : #NOMS

Sarah

 

[JJ1]

Re : Lister les nombres manquants d'une série

Bonsoir,
La formule (Bravo rachid !) comporte des noms, il faut les adapter ( ruban formule-gestionnaire de noms) à ton fichier.
Quand la formule est matricielle, la valider par ctrl alt entrée.
Bonne soirée

 

[Niloula]

Re : Lister les nombres manquants d'une série

Bonsoir,
La formule (Bravo rachid !) comporte des noms, il faut les adapter ( ruban formule-gestionnaire de noms) à ton fichier.
Quand la formule est matricielle, la valider par ctrl alt entrée.
Bonne soirée

Mon Gestionnaire de noms est vide. De plus CTRL ALT Entréé ne fonctionne pas. Je n'ai pas du comprendre la manip'.

Sarah

 

[Niloula]

Re : Lister les nombres manquants d'une série

J'ai réussi la partie Gestionnaire de nom. Mais la formule ne marche toujours pas dans ma situation. Le résultat est #ref et #nom? .

Que faire ?!
Merci pour votre aide

 

[CISCO]

Re : Lister les nombres manquants d'une série

Bonsoir

Une variante

SIERREUR(PETITE.VALEUR(SI(NON(ESTNUM(EQUIV(LIGNE(INDIRECT(MIN(B$2:B$14)&":"&MAX(B$2:B$14)+1));B$2:B$14;0)));LIGNE(INDIRECT(MIN(B$2:B$14)&":"&MAX(B$2:B$14)+1)));LIGNES($1:1));"")

en matriciel

@ plus

 

[Jocelyn]

Re : Lister les nombres manquants d'une série

Bonjour le Forum,
Bonjour Niloula, Rachid,JJ1,

Hum hum, le message d'erreur #NOM vient du fait que ta formule comporte soit une fonction mal écrite et excel ne la reconnait pas soit le nom créé dans le gestionnaire mal retranscrit dans la formule et la même résultat pour excel ou alors du texte dans ta formule qui n'est pas entouré de " ".

ref un soucis dans tes références a priori tu utilises dans tes nommée un indirect cela peut venir de lui si mal écrit

mais sans voir le fichier qui peche pas facile de t'aider plus.

par contre et cela ne changera rien a tes message d'erreur mais une formule matricielle ne se valide pas du moins il me semble par les touches "ctrl alt entrée" mais ,

CTRL, MAJ (au dessus de CTRL) et entrée si tu as bien validé ta formule excel vas mettre { devant le signe = et } à la fin de ta formule attention ces signes ne doivent pas être tapés

Cordialement

EDIT : Bonjour CISCO

 

[Niloula]

Re : Lister les nombres manquants d'une série

Merci à tous pour votre aide. J'ai essayé bien des techniques mais je ne tombe jamais sur le même résultat !
Je dois m'y prendre comme un manche...
Alors voici où j'en suis de mes essais !

Merci à vous

 

[Jocelyn]

Re : Lister les nombres manquants d'une série

re,

tu as en ce moment cette formule

=SI(LIGNES(2:2)<=SOMME(N(NB.SI(MesNums;ListeNums)=0));MIN(SI((NB.SI(MesNums;ListeNums)=0)*(NB.SI(C$1:C1;ListeNums)=0);ListeNums));SI(LIGNES(#REF!)=SOMME(N(NB.SI(MesNums;ListeNums)=0))+1;MAX(MesNums)+1;""))

elle a 2 petite erreurs tu peux corriger de cette façon

=SI(LIGNES($2:2)<=SOMME(N(NB.SI(MesNums;ListeNums)=0));MIN(SI((NB.SI(MesNums;ListeNums)=0)*(NB.SI(C$1:C1;ListeNums)=0);ListeNums));SI(LIGNES($2:2)=SOMME(N(NB.SI(MesNums;ListeNums)=0))+1;MAX(MesNums)+1;""))

le LIGNES(2:2) à remplacer par LIGNES($2:2) et le LIGNES(#REF!) à remplacer par LIGNES($2:2)

Par contre vu le nombre de données que tu as les calculs vont être très lent tu ferrais peut être mieux de passer par du VBA, mais la je suis une bille et je ne pourrais pas t'aider désolé

cordialement

 

[Si...]

Re : Lister les nombres manquants d'une série

salut

une solution VBA avec double clic sur C1 pour lancer la macro (clic, bouton droit sur le nom de l'onglet puis Visualiser le code) :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Address <> "$C$1" Then Exit Sub
  Dim R As Range, n As Long
  'nettoyage colonne C
  Columns(3).ClearContents
  Cells(1, 3) = "Numéros manquants"
  'plage de recherche
  Set R = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
  'pour tester chaque nombre jusqu"au plus grand avec l'équivalent de NB.SI...
  For n = 0 To Application.Max(R)
    If Application.CountIf(R, n) = 0 Then  Range("C" & Cells(Rows.Count, 3).End(xlUp).Row + 1) = n
  Next
  Cancel = True
End Sub

 

[R@chid]

Re : Lister les nombres manquants d'une série

Bonsoir @ tous,

re,
.........................
Par contre vu le nombre de données que tu as les calculs vont être très lent........

Comme il a dit Jocelyn, avec des plages de milliers de lignes les formules matricielles ralentissent le fonctionnement du fichier...
On peut rendre ma formule un peu plus rapide avec cette astuce,
@ partir de C2, sélectionner une plage avec un nombre de ligne équivalent aux MAX de la Plage MesNums,
coller cette formule dans la barre des formules,

=SIERREUR(SI(LIGNE(INDIRECT("1:"&MAX(MesNums)))-1=SOMME(N(NB.SI(MesNums;ListeNums)=0));MAX(MesNums)+1;PETITE.VALEUR(SI(NB.SI(MesNums;ListeNums)=0;ListeNums);LIGNE(INDIRECT("1:"&MAX(MesNums)))));"")

@ valider par Ctrl+Maj+Entree
@ ne pas tirer vers le bas
Si la plage n'est pas suffisante tu peux sélectionner une plage un peu plus grande et taper F2 et revalider encore une fois...

Voir PJ

Amicalement

 

[Niloula]

Re : Lister les nombres manquants d'une série

Merci à tous pour vos nombreuses propositions.
J'ai tout testé, même le code !, et j'ai trouvé la dernière formule de R@chid plus efficace dans ma situation. Par contre j'ai perdu la possibilité d'arrêter ma liste de nombres manquants au "dernier nombre +1" de ma liste de référence. Puis-je changer cela ?

 

[R@chid]

Re : Lister les nombres manquants d'une série

Bonjour @ tous,
As-tu bien vu le fichier, le Max dans la plage MesNums est 1104 et la formule s’arrête @ 1105...
Amicalement

 

[Niloula]

Re : Lister les nombres manquants d'une série

Bonjour @ tous,
As-tu bien vu le fichier, le Max dans la plage MesNums est 1104 et la formule s’arrête @ 1105...
Amicalement

Oui excuse moi R@chid, j'allais m'auto-éditer ! j'ai fait une erreur de frappe et j'ai rentré 1793 au lieu de 179, ce qui explique pourquoi ma liste ne s’arrêtait jamais !
Merci encore pour ton aide