Discussion :

[Neutthsch]

Bonjour à tous,

J'ai ecrit du code pour rassembler dans un même tableau le croisement de plusieurs tableaux. Dans un onglet réference je stock le nom et chemins de mes differents tableaux et dans mon tableau conca, je mets en ligne 1 la colonne source et en ligne 2 le tableau source.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 
Private Sub GenerateVBA_Click()
debut = Now
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
ActiveSheet.DisplayPageBreaks = False
 
Set Calculateur = ThisWorkbook.ActiveSheet
 
CreaClasseurs
 
ReDim TabCalcul(Classeur(5).Feuille.Cells(1, 5).End(xlDown).Row + 2, Cells(1, 2).End(xlToRight).Column)
 
Range(Calculateur.Cells(4, 1), Calculateur.Cells(UBound(TabCalcul, 1), UBound(TabCalcul, 2))) = "" 'on nettoie la zone
 
For y = 1 To UBound(TabCalcul, 2)
    For x = 1 To 3
        TabCalcul(x, y) = Calculateur.Cells(x, y).Value
    Next
Next
 
Chargement.ProgressBarGlobal.Value = Chargement.LabelGlobal.Caption = 0 & "%"
DoEvents
 
For y = 1 To UBound(TabCalcul, 2)
 
      If TabCalcul(4, y) = "" Then
        For x = 4 To UBound(TabCalcul, 1)
            Select Case TabCalcul(2, y)
            Case Is = Classeur(1).AffNom 'Catalogue
                Recherche Classeur(1)
            Case Is = Classeur(2).AffNom 'Marche
                Recherche Classeur(2)
            Case Is = Classeur(3).AffNom 'Attendus
                Recherche Classeur(3)
            Case Is = Classeur(4).AffNom 'Livre
                Recherche Classeur(4)
            Case Is = Classeur(5).AffNom 'ME2M
                For z = y To UBound(TabCalcul, 2)
                    If TabCalcul(2, z) =Classeur(5).AffNom Then
                       TabCalcul(x, z) = Classeur(5).Feuille.Cells(x - 2, TabCalcul(1, z))
                   End If
                Next
            Case Is = Classeur(6).AffNom 'Referentiel
                'x = UBound(TabCalcul, 1)
                Recherche Classeur(6)
            Case Is = Classeur(7).AffNom 'Criticite
               Recherche Classeur(7)
            Case Else 'Calcul
                Calculateur.Cells(x, y).formulelocal = Calculateur.Cells(1, y)
            End Select
 
            Chargement.ProgressBarDetail.Value = Round(100 * (x / UBound(TabCalcul, 1)), 0)
            Chargement.LabelDetail.Caption = Chargement.ProgressBarDetail.Value & "% " & y & " - " & TabCalcul(2, y)
            DoEvents
        Next
    End If
 
    Chargement.ProgressBarGlobal.Value = Round(100 * (y / UBound(TabCalcul, 2)), 0)
   Chargement.LabelGlobal.Caption =              Chargement.ProgressBarGlobal.Value & "%"
   DoEvents
Next
 
Range(Calculateur.Cells(1, 1), Calculateur.Cells(UBound(TabCalcul, 1), UBound(TabCalcul, 2))) = TabCalcul
 
Chargement.LabelGlobal.Caption = "Terminé en " & Now - debut
 
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
 
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Private Sub Recherche(RefClasseur As CFile)
 
With RefClasseur
Set RefFound =.Feuille.Range(.Feuille.Cells(1, .RefCol),.Feuille.Cells(.DerLigne,.RefCol)).Find( _
What:=TabCalcul(x, .ColIndexCalculateur), After:=.Feuille.Cells(1,.RefCol),LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 
    For z = y To UBound(TabCalcul, 2)
        If Not RefFound Is Nothing Then
            If TabCalcul(2, z) = RefClasseur.AffNom Then
               TabCalcul(x, z) = .Feuille.Cells(RefFound.Row, Calculateur.Cells(1, z))
            End If
        Else
            If TabCalcul(2, z) = RefClasseur.AffNom Then
                TabCalcul(x, z) = "-"
            End If
        End If
    Next
End With
 
End Sub

Ca marche bien mais... C'est très long à l'exécution. Et pour cause, chacune de mes sources contient environ 30 000 lignes.

Que ca prenne un peu de tps je veux bien l'entendre, mais c'est plus long qu'un bon gros bloc plein de recherchev.

Est ce que quelqu'un aurait une astuce pour accélérer le bousin?

Merci,
Nutch

[eriiic]

Bonjour,

franchement, sauter 1, voire 2 et même 3 (!) lignes à chaque fois ne donne pas envie de lire.
Pourquoi pas 12 ?
eric

[Neutthsch]

Dsl, surement un bug lors du copier/coller.
Faut dire j'écris depuis mon tel. Je vais rectifier ca!!!

[Al__22]

Je soupçonne le progress bar d'être (en partie du moins) une des causes de la lenteur

[Neutthsch]

Je soupçonne le progress bar d'être (en partie du moins) une des causes de la lenteur

pas tant que ça. Je l'ai juste intégré pour avoir une lecture de ce qui se passe en direct mais le temps est peu rallongé avec.

[Marc-L]

Bonjour !

Oh que si ‼     Comme utilisée dans ce code
cette barre de progression est un facteur de ralentissement sensible avec l'augmentation du nombre total d'itérations …

Me rappelant d'une procédure prenant 48 secondes avec cette même logique d'affichage
de barre de progression n'en nécessitait pas plus de 18 secondes sans !

___________________________________________________________________________________________________________

Je suis Paris, London, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

[clementmarcotte]

Bonjour,

Bonjour à tous,

J'ai ecrit du code pour rassembler dans un même tableau le croisement de plusieurs tableaux. Dans un onglet réference je stock le nom et chemins de mes differents tableaux et dans mon tableau conca, je mets en ligne 1 la colonne source et en ligne 2 le tableau source.

Ca marche bien mais... C'est très long à l'exécution. Et pour cause, chacune de mes sources contient environ 30 000 lignes.


Nutch

Donc, tu explores deux tableaux de 30 000 lignes pour en faire un autre de 60 000 lignes. Et tu répètes le même manège plusieurs fois ?

Quand on sait qu'Excel a toujours besoin de tous les classeurs ouverts entièrement en mémoire vive pour fonctionner, ce n'est pas surprenant que cela soit long et périlleux. Dès que la mémoire vive est saturée, ou presque, Windows doit prendre un temps fou juste pour allouer de la mémoire vive à Excel. Et, si jamais Windows fait une fausse manœuvre, c'est le plantage. Avec le plantage, vient le risque de perdre des données, ou de corrompre les classeurs.

Première conclusion, c'est un plan qui n'a déjà pas d'allure.

Et puis s'ajoute la dure réalité que VBA est un langage interprété et que chaque instruction doit être réinterprétée à chaque passage ...

Deuxième conclusion, version a : repars à zéro dans Access...

Deuxième conclusion, version b : Au lieu de le faire en VBA, fais-le en VB.net. Au moins tu vas avoir un exécutable compilé et un peu plus de mémoire vive utilisable.

P.S. Tu peux me mettre un pouce rouge si tu veux. Je m'en sacre.

P.P.S. Et puis, avec VB.net, ou même C#, on peut manipuler des fichiers Excel sans avoir Excel. Pour cela, il existe même deux bibliothèques .net gratuites

[Pierre Fauconnier]

Salut.

Pleinement d'accord avec Marc-L

Penser Excel avant de penser VBA
Unir les deux tableaux en sql avec une requête select de type inner join... Même le moteur sql d'Excel est optimisé et plus performant que du vba

Cela dit: Si tu as découvert que RECHERCHEV allait plus vite que des boucles, pourquoi ne pas utiliser RECHERCHEV, éventuellement par VBA?

[eriiic]

Bonjour,

Une cause de lenteur de ton code : tu as l'air de lire les cellules une par une

Les formules feuilles utilisent tous les coeurs, vba un seul. Ceci explique sans doute en partie cela.
Comme dit Pierre, si c'est plus rapide par formule pourquoi s'en passer.

De plus si tes tableaux s'y prêtent tu as le double recherchev() qui est mystérieusement plus rapide que tout le monde (y compris vba et SQL) : http://analystcave.com/excel-vlookup...l-performance/
eric

[Pierre Fauconnier]

[...]tu as le double recherchev() qui est mystérieusement plus rapide que tout le monde (y compris vba et SQL) : http://analystcave.com/excel-vlookup...l-performance/
eric

Pas si mystérieux, en fait. Avec le paramètre VRAI, RECHERCHEV réalise une recherche dichotomique, qui lui permet de trouver le résultat juste (ou l'absence de résultat) en 20 itérations maximum pour un million de lignes, contre 500.000 en moyenne) si l'on met FAUX en quatrième paramètre, puisque FAUX oblige RECHERHEV à parcourir toutes les lignes jusqu'à trouver la bonne... ou pas.

Dès lors, le double RECHERCHEV réalise son opération en 40 itérations max... (Là où le sql va devoir lire les lignes en séquentiel puisqu'il n'est pas possible d'indexer une colonne en SQL pour Excel)...

Cela étant, le gros défaut du double RECHERCHEV, c'est qu'il impose que le tableau soit trié sur la première colonne. Et cela enfreint ce qui pour moi est une règle capitale d'Excel: Le résultat d'une formule ne peut jamais dépendre du tri des données. Cette règle impose, pour moi, que le tableau doit être trié sur la première colonne uniquement s'il n'y a aucune raison qu'il en soit autrement (le seul cas que je connaisse est le tableau de plage de valeurs qui justifie que les plages soient classées par ordre croissant des bornes inférieures et que l'on n'aurait pas d'utilité à trier autrement, et qui justifie le seul cas d'utilisation de VRAI en quatrième paramètre).

De plus, d'une manière générale (et justement pas dans le cas du double RECHERCHEV puisque la condition teste que l'on a trouvé la bonne valeur), le paramètre VRAI amène à avoir des résultats autres que #N/A dans la majeure partie des recherches, n'envoyant #N/A que lorsque la valeur cherchée "n'est pas trouvée" (sous-entendu selon la recherche dichotomique) ET qu'elle est inférieure à la valeur de la première ligne. Et lorsqu'il renverra autre chose que #N/A, le "client" de la fonction n'aura aucun avertissement lui disant que la donnée renvoyée peut ne pas être la bonne!

[eriiic]

Merci pour le pourquoi :-)
Oui, le tri sur la 1ère colonne est une grosse limitation, d'où le si ton tableau s'y prête
Cette méthode est sensée remplacer le recherchev(,,,faux) dans les cas où la valeur cherchée est toujours présente. Dans ce cadre pas de soucis de #N/A.
eric

[Pierre Fauconnier]

[...]Cette méthode est sensée remplacer le recherchev(,,,faux) dans les cas où la valeur cherchée est toujours présente. Dans ce cadre pas de soucis de #N/A.
eric

[EDIT] Je n'avais pas compris toute la portée de ta remarque [/EDIT]

Mais je ne dirais pas dans les cas où la valeur cherchée est toujours présente puisque justement le premier RECHERCHEV prévoit qu'elle peut ne pas être "présente" et teste donc cette présence et, inclus dans un SI, permet de renvoyer une valeur définie (éventuellement autre que #N/A) au cas où justement la valeur ne serait pas présente.

Oui, dans le cadre du double RECHERCHEV, nonobstant l'obligation du tri, il n'y a pas de souci avec #N/A, mais uniquement dans ce cadre, puisque le premier RECHERCHEV détectera l'absence éventuelle de la valeur. C'est ce que je soulignais dans mon précédent message.

Car c'est là le piège du paramètre VRAI (dans le cas du RECHERCHEV unique)... C'est que tu peux avoir un #N/A même si la donnée est présente puisque la recherche dichotomique va peut-être "jeter" la portion de lignes qui contient la ligne recherchée.

Dans l'illustration ci-dessous, RECHERCHEV divise la zone de recherche en deux parties, et regarde si Alain est dans la première ou la seconde, en regardant la ligne Zoé, comme dans un dictionnaire. RECHERCHEV conclut que ALAIN se trouve AVANT puisque < que Zoé. Il recherche donc dans la première partie du tableau et "jette" les lignes 3 et 4, et donc celle contenant Alain.

Puis il regarde dans son "nouveau" tableau qui contient moitié moins de lignes que le précédent, et recommence la recherche. Il bute donc sur la première ligne dont la valeur est > que Alain, et comme le paramètre VRAI autorise de renvoyer la valeur inférieure la plus proche, il va chercher la valeur avant la ligne Pierre et ne peut pas la trouver puisque Pierre est en première ligne. Il va donc renvoyer #N/A alors que la valeur ALAIN est bien dans le tableau.

[Pierre Fauconnier]

C'est un poil plus complexe...

Le double RECHERCHEV est souvent présenté ainsi, mais à mon avis est incomplet: =SI(RECHERCHEV(E1;Tableau1;1)=E1;RECHERCHEV(E1;Tableau1;2);"Inconnu")

De toutes façons, le tableau doit être trié sur la première colonne.

La valeur cherchée peut ne pas exister mais doit alors être plus grande que la valeur de la première ligne du tableau (Manon n'est pas dans le tableau)





Si la valeur est plus petite que la première ligne du tableau, alors, on reçoit #N/A (Alain n'est pas dans le tableau et est < que Bernard)





Pour contrer cela, je propose de tester la disponibilité de la valeur avec SI.NON.DISP. Ici, on trouve le service si la valeur est présente dans le tableau





Avec ce test supplémentaire, une valeur non présente et plus petite que la valeur de la première ligne du tableau renverra la Valeur_Si_Faux du SI et pas #N/A.




Si le tableau n'est pas trié, avec l'ajout du test de disponibilité, on risque de récupérer "inconnu" alors que la valeur est dans le tableau, mais on n'aura de toute façon jamais #N/A puisqu'il est "capturé" par le SI.NON.DISP

[eriiic]

Merci pour le temps pris pour illustrer.
SI.NON.DISP est un peu trop frais pour moi, toujours sur 2010... :-) Bon, il y a sierreur() ou nb.si()

J'ai un doute sur la 2nde partie, non pas sur son fonctionnement mais sur le fait que tu utilises FAUX.
Là je crains que tu perdes l'avantage vitesse du double recherchev() et du coup son intérêt.
eric

[Pierre Fauconnier]

[...]
J'ai un doute sur la 2nde partie, non pas sur son fonctionnement mais sur le fait que tu utilises FAUX.
Là je crains que tu perdes l'avantage vitesse du double recherchev() et du coup son intérêt.
eric

Non, FAUX n'est pas utilisé à l'intérieur de RECHERCHEV, mais à l'intérieur de SI.NON.DISP (ou SI.ERREUR). Il ne ralentit donc pas le RECHERCHEV, mais permet de renvoyer FAUX si la recherche n'aboutit pas ( => renvoie #N/A), ce qui permet au SI de renvoyer Inconnu.

Mais comme il faut que la liste soit triée, cette utilisation est à mon avis dangereuse pour qui veut s'assurer de la justesse du résultat, et je ne peux que la déconseiller, dans l'absolu, sauf dans un environnement contrôlé où je suis certain que la liste est triée.

Cela dit, avec une bécane convenable, UN RECHERCHEV(...,...,...,FAUX) va très vite, même sur un million de lignes. C'est lorsqu'on en utilise beaucoup sur de grands tableaux que ça ralentit. Il est alors parfois intéressant de passer par EQUIV dans une colonne intermédiaire puis INDEX pour récupérer les données. Ce n'est pas la seule raison qui me fait préférer INDEX/EQUIV à RECHERCHEV, ceci dit, mais c'est une autre histoire.

Tant qu'à faire, il faut noter que si c'est pour une utilisation des "données à plat" recollées à coup de RECHERCHEV pour un tableau croisé dynamique, il ne faut pas hésiter et passer alors par PowerPivot. Ce sera beaucoup plus rapide...


Comme quoi, il y a plusieurs chemins, en fonction de la finalité du projet...

[Neutthsch]

Déjà merci à tous pour ces différents éclairages qui vont me permettre d'avancer plus proprement la prochaine fois et de relativiser bcp plus l'utilisation du VBA.

Sans une présentation claire avec tenants & aboutissements, sans compter la réaction précédente,
je m'en tiendrais à rappeler la première règle de développement en VBA :

« Penser d'abord Excel avant VBA ! »

A savoir tout ce qui peut être réalisé par Excel, et ce même par code, est forcément plus rapide
qu'une logique de boucles en VBA standard !

Et pour de gros volumes, ne pas oublier le traitement via SQL par exemple …

Sur ce, bonne continuation !

Je me permets de signaler, et j'espère que ceci viendra clore le sujet, que toute critique est bonne à prendre mais que le ton utilisé a toute son importance surtout à l'écrit et sur des forums. Marc-L j'apprécie clairement ce genre de réponse (ci-dessus) qui bouscule ma façon de penser et de coder et qui est bien plus en adéquation avec le niveau d'intelligence qu'on peut trouver sur developpez.net que celle que j'ai signalé plus haut.

Tout ceci améne des axes de réflexions nouveaux pour moi, qu'il va me falloir digérer et retranscrire. en tout cas le sujet est résolu.