Trouver les chiffres manquants dans une suite excel (2007)

[jeronimo83]

Bonjour à toutes et à tous,

Je suis nouveau sur le forum, 1er message !

J'ai un tableau excel 2007 avec en colonne A, la liste de mes numeros de factures qui doivent être par nature une suite de chiffres,
ex : A1 : 20000
A2 : 20001
A3 : 20002
....

Mon tableau fait plusieurs milliers de lignes, comment puis je faire pour identifier d'éventuelles factures manquantes dans cette suite.

NB : je ne me sers pas de VBA ni de macro.
Je suis à la recherche d'une formule excel.

Mille mercis pour votre aide

 

[job75]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Bonjour le fil, le forum,

Une autre solution matricielle en B2 :

=MIN(SI(NB.SI(plage;matrice)+NB.SI(B$1:B1;matrice)=0;matrice))

Les zéros permettent de vérifier qu'on a bien tous les manquants mais on peut facilement les masquer.

Fichier (4).

A+

 

[sand13]

Re : Trouver les chiffres manquants dans une suite excel (2007)

merci pour votre solution job je vais l'incorporer à mon fichier pour le faire tourner !

si.... je voudrais bien tester votre fichier mais je ne peux lire les xlsm

 

[Misange]

Re : Trouver les chiffres manquants dans une suite excel (2007)

merci pour votre solution job je vais l'incorporer à mon fichier pour le faire tourner !

si.... je voudrais bien tester votre fichier mais je ne peux lire les xlsm

Bonjour

d'où l'intérêt de préciser dans cotre profil quelle version d'excel vous utilisez, et de commencer une nouvelle discussion (quitte à mettre un lien vers celle-ci) si votre problème n'est pas EXACTEMENT celui décrit dans un fil existant...

 

[Si...]

Re : Trouver les chiffres manquants dans une suite excel (2007)

salut

voir ton nouveau post.

Si... d'autres sont dans ce cas ...

 

[Misterpat]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Bonjour à tous

Un essai avec une fonction personnalisée en VisualBasic.​

ROGER2327
#6280

Jeudi 12 Sable 140 (Sainte Choupe, mère de famille - fête Suprême Quarte)
22 Frimaire An CCXXI, 6,7546h - bruyère
2012-W50-3T16:12:40Z

Bonjour,
Je viens de trouver ce forum et je m'y suis inscrit, car j'ai trouvé cette fonction élégante et qui répond bien à mon problème.
J'aurais souhaité l'intégrer dans mon classeur de macros personnel, mais même écrite dans un module, elle n'apparait pas quand je l'utilise dans la barre de formule.
Je "bricole" dans les macros, mais je dois rater quelque chose.
Pourriez vous m'aider?
J'utilse Excel 2007
Merci

 

[bidonman]

Bonjour, je me permets de déterrer ce très vieux topic parce que j'ai exactement le même besoin mais que la solution proposé ne marche pas terrible chez moi (excel 2019). J'ai en effet plus de 30 000lignes à vérifier. Les soutions proposés marchent très bien mais dès que je met un certain nombre de lignes, mon pc crash... Y a t'il une solution moins consommatrice en ressources (sachant que mes numéros sont triés par ordre croissant) ? Merci d'avance

 

[patricktoulon]

Bonjour
ma fois 30 000 lignes avec une formule matricielle c'est pas étonnant il te faut passer par vba
et peut être en la déclenchant par une seule formule avec la ref de ta plage et l'inscription des manquants se ferait par vba

 

[job75]

Bonjour bidonman, patricktoulon, le forum,

L'auteur du fil ne voulait pas de VBA mais sur un grand tableau la formule matricielle prend du temps;

Avec cette macro c'est simple et très rapide :

Sub Manquants()
Dim F As Worksheet, tablo, resu&(), i&, deb&, manque&, j&, n&
Set F = Feuil1 'CodeName de la feuille, à adapter
Application.ScreenUpdating = False
If F.FilterMode Then F.ShowAllData 'si la feuille est filtrée
With F.[A1].CurrentRegion
    .Cells(1).EntireColumn.Insert 'colonne auxiliaire
    .Cells(1, 0) = 1: .Columns(0).DataSeries 'numérotation
    .EntireRow.Sort .Cells(1), xlAscending, Header:=xlYes 'tri croissant
    tablo = .Resize(, 2)   'matrice, plus rapide, au moins 2 éléments
    .EntireRow.Sort .Cells(1, 0), xlAscending 'ordre initial
    .Columns(0).EntireColumn.Delete 'supprime la colonne auxiliaire
End With
ReDim resu(1 To Rows.Count, 1 To 1)
For i = 3 To UBound(tablo)
    deb = tablo(i - 1, 1)
    manque = tablo(i, 1) - deb - 1
    For j = 1 To manque
        n = n + 1
        resu(n, 1) = deb + j
Next j, i
'---restitution---
With F.[B2] '1ère cellule de restitution, à adapter
    If n Then .Resize(n) = resu
    .Offset(n).Resize(F.Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
With F.UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Testez sur vos 30 000 lignes, quelle est la durée d'exécution ?

A+

 

[patricktoulon]

Bonjour @job75
5000 manquants pour 30000 lignes
quasiment une demie seconde c'est clean

 

[job75]

Avec la macro Quick sort c'est un peu plus rapide car il n'y a qu'un seul tri :

Sub Manquants()
Dim F As Worksheet, tablo, resu&(), i&, deb&, manque&, j&, n&
Set F = Feuil1 'CodeName de la feuille, à adapter
If F.FilterMode Then F.ShowAllData 'si la feuille est filtrée
tablo = F.[A1].CurrentRegion.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
tri tablo, 1, UBound(tablo)
ReDim resu(1 To Rows.Count, 1 To 1)
For i = 2 To UBound(tablo) - 1
    deb = tablo(i - 1, 1)
    manque = tablo(i, 1) - deb - 1
    For j = 1 To manque
        n = n + 1
        resu(n, 1) = deb + j
Next j, i
'---restitution---
With F.[B2] '1ère cellule de restitution, à adapter
    If n Then .Resize(n) = resu
    .Offset(n).Resize(F.Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
With F.UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Sub tri(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2, 1)
g = gauc: d = droi
Do
    Do While a(g, 1) < ref: g = g + 1: Loop
    Do While ref < a(d, 1): d = d - 1: Loop
    If g <= d Then
      temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub

 

[patricktoulon]

re
0.54 sec

 

[job75]

J'ai testé avec 30 000 lignes et 7400 manquants, chez moi sur Excel 2019 :

- fichier (1) post #23 => 0,13 seconde

- fichier (2) post #25 => 0,09 seconde.

 

[patricktoulon]

re oui différence de pc j'ai des disque qui ne sont pas en SSD sur celui là
il travaille un peu moins vite j'ai pourtant 32 giga mémoire dispo
cela dit on est loin du whitescreen avec les formules