[TABLEUR]trouver des nombres manquants

FMP_thE_mAd · Décembre 16, 2011, 10:55

Salut les Geek.

Bon comme je sais qu’il y a des pros du Tableur ici (hein CBen76 ) je m’adresse à vous parce que là je craque.

Le problème : j’ai une liste de nombres assez énorme (7000 environs) et dans cette liste, je dois trouver les nombres manquants. Exemple, j’ai la liste 1,2,3,5,6,7,8,10. Je dois trouver 4 et 9.

premier soucis : la liste est en fait une liste de chaine de caractère. En effet les nombres sont entre guillemets : ‘1’ ‘2’ ‘3’…
Bon ça j’arrive à les changer en nombre avec =CNUM(SUBSTITUE(A3,"’","")) (ou A3 est la case où se situe le truc)
Ca fait le taf’, sauf que je dois écrire cette formule sur les 7000 cases… Et c’est long.
Comment copier cette formule rapidement sur toutes les cases ? (la liste des chaines est dans la colonne A, je mets les nombres transformés dans la colonne B .

Ce que je veux obtenir donc :
Colonne A Colonne B
’1’ 1
’2’ 2
’3’ 3
Etc

Deuxième soucis : le coeur du problème, comment trouver les nombres manquant dans la colonne B rapidement ?
J’ai fait une macro qui marche avec 2 boucles, mais ça prend un temps fou vu que je teste tous les nombres sur toute la liste. Donc niet.

J’ai aussi pensé utiliser la formule de math qui donne la somme des nombres ( n(n+1)/2 pour la somme de 1 à n). J’utilise cette formule sur les deux premières cases, et je la compare à la somme des deux cases. S’il y a une différence, il y a un nombre manquant et je fais la soustraction pour le trouver.
Puis j’utilise la formule sur les 3 premières cases, que je compare de nouveau avec la somme des 3 premières cellules, puis sur les 4 premières, etc etc.
Mais ça foire s’il y a deux nombres manquants de suite.

Bref, si vous avez des pistes (surtout pour le 1) en fait, le reste je peux me débrouiller), je prends.

Merci d’avance.

PS : idéalement je fais ça dans Open Office. Mais Excel ça me va aussi.

Patoche · Décembre 16, 2011, 11:09

CTRL+C puis CTRL+V.
Si t’as pas fixer les cellules dans ta formule, c’est tout bon.

Tu copie la formule pour la première cellule puis tu la colle dans les autres au lieu de la recopier.

FMP_thE_mAd · Décembre 16, 2011, 11:49

Euh ben non, je ne vais pas faire CTRL+V 7000 fois quand même ! Ou alors j’ai pas compris ta manip’.

Hoaxymore · Décembre 16, 2011, 11:58

Duplication de la formule 1 : Tu cliques sur le coin bas/droit de la cellule et tu étires sur toute la colonne en descendant, les numéros de cellule au sein de ta formule s’implementent automatiquement.
Test de la continuité : tu te fais une colone C avec comme formule en C2 : =si(b2-b1=1;" ";“ALEEEEEERTE”) , tu dupliques également sur 7000 lignes en cliqué-étiré

Tu dezoome à mort et en quelques scroll tu vois tes lignes à problème d’un coup d’oeil
�?DIT : plus simple : tu tries par la colonne C. (“données”>“trier”)

FMP_thE_mAd · Décembre 17, 2011, 12:17

Ouais cliqué-étiré j’y avais pensé, et j’avais commencé à le faire, mais 7000 lignes ça prend des plombes ! Mais bon s’il n’y a pas mieux…

Sinon pas bête de tester par rapport au nombre précédent ! Bon sang mais c’est bien sûr. Il restera à gérer les “gros” trous, mais du coup en testant la différence entre un nombre et le suivant, j’aurai automatiquement le nombre de nombre manquant !
Merci de l’idée tiens !

Hoaxymore · Décembre 17, 2011, 12:28

Oui enfin le temps de cette discussion tu aurais pu le faire une bonne centaine de fois

Ben · Décembre 17, 2011, 12:46

Alors, pour copier la formule, plusieurs facon super rapide :[ul]
[li]Tu doubles clic sur le petit carré qui permet d’etirer : il va aller direct en bas des 7000 lignes en recopiant la formule[/li][li]si ca marche pas, parce qu’il y a des trou dans ta cellule, tu selectionnes la cellule du haut qui contient la formule, puis la 7000eme cellule ou tu veux copier la formule, et tu fais ctrl+B. Ca va tout copier de la premiere a la derniere.[/li][/ul]
Pour le coeur du probleme, VBA est super rapide, si tu l’utilises comme il faut. En ce moment, au boulot, je traite des paquets de 30 000 données en 20 secondes sous excel.

Le truc, c’est de ne surtout pas utiliser les données en cellule, car c’est ca qui est hyper lent ! Les lectures ecriture dans le worksheet sont tres tres tres lent.
Donc tu charges tout en memoire (et d’ailleurs du coup, tu te fou de faire ta premiere formule, tu transformeras en nombre une fois dans ta macro VBA) :

[CODE]
Dim monTableau as Variant
dim i as long
Dim chiffreManquants as String

monTableau = Worksheet(“ta feuille”).usedRange

’ la tu travaille sur ton tableau
for i = LBound(MonTableau,1) to UBound(MonTableau,1)-1
if MonTableau(i) +1 <> MonTableau(i+1) Then
’ on a un trou
ChiffreManquants = ChiffreManquants & (MonTableau(i)+1)
End if
Next
debug.pring "Resutat : " & ChiffreManquants
[/CODE]

Bon la, c’est pour l’exemple hein et de memoire. Et la partie test des trous est pourrie, c’etait juste pour mettre un truc.

Au pire, envoi moi ton ficiher, je te le fais e 10 min.

Phil · Décembre 17, 2011, 11:35

Pour chercher les trous, il peut y avoir un autre moyen sans VBA, plus lent mais plus facile à mettre en œuvre.[ol]
[li]Faire une liste de 1 à 7000. Créer la cellule. Puis la 2é égale à la première +1. Puis cliquer tirer jusqu’a 7000 lignes.[/li][li]A partir de la liste précédente, faire une RECHERCHEV() sur la liste à contrôler. Et hop on copie automatiquement la formule par cliquer tirer.[/li][li]Puis créer en haut une listbox automatique et sélectionner “N/A” qui renverra toutes les cellules non trouvées.[/li][/ol]

Après on peut jouer avec les SI(test;si oui;si non), les ESTNULL, etc… pour parfaire la présentation.

edit: ne pas oublier le caractère $ dans les coordonnées pour le RECHERCHEV(), pour éviter les recherches relatives, pour avoir une zone de recherche fixe qui ne décalera pas l’adresse au moment du copier coller.

FMP_thE_mAd · Décembre 17, 2011, 1:17

Alors merci à tous, je pense que je vais m’inspirer de ce qu’à fait CBen76. Ca doit être la méthode la plus simple et la plus rapide, et surtout ça m’arrange que ce soit une macro (cf plus bas). Mais dans ton truc, il faut d’abord que je transforme les chaines en nombres ou ça va marcher directement avec la liste des valeurs sous forme ‘1’ ‘2’… ?

J’avais aussi oublié de préciser un truc : ce n’est pas pour moi, mais pour une collègue. Elle sait manipuler le tableur, mais l’idéal, c’est de lui faire un fichier vide avec tout ce qu’il faut, elle copie sa liste de nombres dans la colonne A, elle appuie sur un bouton, et hop, elle a les nombres manquants. Tout doit être transparent quoi.

Mais là j’ai quand même des bonnes pistes !

Ben · Décembre 17, 2011, 1:29

pour que ce soit automatique : tu rajoutes une petite image ou un petit bouton sur ta page, que tu lis à une macro (bouton droit -> affecter une macro), et quand elle clique dessus ca la lance.

Et normalement, si tu charges bien les données en memoire, tu auras pas de pb de perf. Tu peux aussi remplacer le Debug.print pour le resultat en un MSGBOX pour que ca s’affiche à l’ecran, ou copier la string de resultat dans une cellule, ou la transformer en tableau (tableau = split(ChiffreManquants;";")) puis la copier dans la feuille (Worksheet(“feuille”).Range(“A1”).Resize(TailleTableau;1) = tableau).

Le truc a bien garder en memoire, c’est que les boucles sur les cellules (range, cell …) sont super lente. Il faut utiliser les fonctions de chargement de tableau en memoire ou d’ecriture de tableau dans une worksheet d’un bloc.

Hesite pas a m’envoyer ton fichier si tu veux un coup de main.

FMP_thE_mAd · Décembre 17, 2011, 11:53

Re !

Bon je suis arrivé à un truc pas trop dégueulasse, et rapide :

[CODE]Sub Cherche()

’ Cherche les n° manquants
’
Dim i As Integer, j As Integer
Dim tableau(1 To 7000) As Integer 'le tableau qui contiendra les références.'
Dim resultat() As Integer 'le tableau qui contiendra les résultats’
Dim nbresultat As Integer 'le nombre de résultats qu on aura’
Dim nbtrou As Integer, rang As Integer

’ transforme la chaine de caractère en nombre (en enlevant les guillemets)‘
Range(“C3”).FormulaR1C1 = “=VALUE(SUBSTITUTE(RC[-2],”"’"",""""))"
Range(“C3”).Select
Selection.AutoFill Destination:=Range(“C3:C7000”), Type:=xlFillDefault

'trie la colonne où on recopie les nombres’
Columns(“C:C”).Select
ActiveWorkbook.Worksheets(“Feuil1”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“Feuil1”).Sort.SortFields.Add Key:=Range(“C1”), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Feuil1”).Sort
.SetRange Range(“C1:C7000”)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

For i = 1 To 7000 'cette boucle remplit le tableau avec les références, qui doivent être dans la colonne C, sous forme de nombres, à partir de C3 !'
If IsEmpty(Cells(i + 2, 1)) Then
tableau(i) = 0
Else
tableau(i) = Cells(i + 2, 3)
End If
Next i

rang = 0
nbresultat = 0

For i = 1 To 6999 'boucle qui compare les valeurs les unes à la suite des autres’
If tableau(i) = 0 Then 'si on est en fin de tableau, on arrête de suite’
i = 6999
Else
If tableau(i) - 1 <> tableau(i + 1) Then 'on compare la référence et la suivante. Si elles ne se suivent pas, c est un trou.'
nbtrou = tableau(i) - tableau(i + 1) - 1
nbresultat = nbresultat + nbtrou
ReDim Preserve resultat(1 To nbresultat)
For j = 1 To nbtrou
rang = rang + 1
resultat(rang) = tableau(i) - j
Next j
End If
End If
Next i

For i = 1 To nbresultat
Cells(i, 9) = resultat(i)
Next i

End Sub

[/CODE]

Donc ça marche nickel, on met la liste dans la colonne A, et hop, après on appuie sur le bouton et ça fonctionne. la macro fait tout toute seule.

Bon je pourrais rajouter un petit test pour voir si les nombres commencent toujours à la ligne n°3, parce que sinon, ça foire, mais bon on verra plus atrd. Et puis a priori ça n’arrivera pas (en fait, c’est un autre logiciel qui fournit la liste, et il met les nombres à partir de la ligne 3).

Du coup je suis content, et ma collègue le sera aussi !
Merci à tous !

Ben · Décembre 18, 2011, 12:07

Niquel
C’est un tres bon premier pas.
Je l’aurai pas forcement fait comme ca, parce que tu utilises beaucoup de référence statique (taille fixe des tableaux, positionnement des données et appelle de fonction Excel dans VBA …).

Sinon, pour convertir un string en nombre, tu as beaucoup plus simple : CInt(chaine) pour les integer, ou CLng(chaine) pour les long. Pas besoin de substitute

Enfin, tu peux vraiment gagner en temps de traitement en supprimant tes boucles de lecture des données sources et d’écriture des résultat en copiant les tableaux d’un bloc.

FMP_thE_mAd · Décembre 18, 2011, 8:13

[quote=CBen76]Je l’aurai pas forcement fait comme ca, parce que tu utilises beaucoup de référence statique (taille fixe des tableaux, positionnement des données et appelle de fonction Excel dans VBA …).
Sinon, pour convertir un string en nombre, tu as beaucoup plus simple : CInt(chaine) pour les integer, ou CLng(chaine) pour les long. Pas besoin de substitute :)[/quote]

Pour les deux premiers blocs de code, en fait j’ai fait « enregistrer la macro » et j’ai fait le truc dans le tableur directement.
Et après j’ai copié le code généré par Excel dans ma propre macro. Donc évidemment c’est sans doute pas optimisé, mais au moins ça va vite

Pour CInt et CLng j’ai vu sur Internet que c’était des fonctions d’arrondis. Tu es sûr que ça transforme une chaine en nombre ?

Et le soucis que j’avais c’est que les références générées par le logiciel sont des chaines de caractères entourées des signes ’ (il y a ‹ 1 › ‹ 2 › ‹ 3 ›…). Donc quand j’utilise la fonction CNUM directement sur ces chaines, ça foire et ça ne me donne pas le nombre voulu (=CNUM(‹ 3 ›) par exemple renvoi une erreur, au lieu de me renvoyer 3).
C’est pour ça que j’utilise d’abord SUBSTITUE, pour enlever les '.
J’ai pas trouvé plus simple que ça.

J’ai pas trouvé comment faire pour copier directement les valeurs d’une colonne dans un tableau D’où mes boucles.

Ben · Décembre 18, 2011, 8:23

C’est des fonctions de cast, ca transforme une variable d’un autre type en type Int, Long … Et ca arrondi si necessaire (si tu casts un nombre à virgule dans un entier, c’est normal que ca arrondisse).

A ok. Mais utilise plutot les fonctions VBA plutot que les fonctions worksheet d’excel pour ca.

Je te l’ai mis plus haut
Tab = worksheet(« Feuille »).Range(« A1:C7000 ») → renvoi un tableau 7000x3 qui contient les valeurs dans la zone A1:C7000.

Dans l’autre sens : worksheet(« Feuille »).Range(« A1:C7000 ») = Tab. Faut juste s’assurer que les zones sont de bonne dimension. Check les formules LBound et UBound

Lelolo · Décembre 19, 2011, 1:37

L’arrondi se gère en paramètre d’affichage sous excel.
Et pour les puristes, il y a ARRONDI, ARRONDI.INF et ARRONDI.SUP comme formules.
Si c’est juste ce que tu veux c’est peut-être plus simple que de transformer en entier ou en entier long.

FMP_thE_mAd · Décembre 19, 2011, 2:42

Ben non justement c’est pas DU TOUT ce que je veux…
Donc bon merci, mais y a des fois je me demande si les gens lisent les posts avant d’y répondre !

J’ai pas trouvé les fonctions équivalentes sous VBA, donc plutôt que de chercher 3 plombes…

Sinon j’ai changé le remplissage du tableau, pour faire mieux, comme l’a dit CBen76.
C’est histoire que ce soit plus joli quoi De toute façon j’avais envoyé le fichier déjà à la collègue, donc bon.

Lelolo · Décembre 20, 2011, 9:36

J’avais bien tout lu…
Mais mon erreur vient de cette phrase, qui m’as fait penser que tu avais aussi besoin de l’arrondi . car ici il n’est que la résultante de la conversion.
Et je vais continuer de suivre, car les solutions proposées par Cben76 sont super intéressantes.