XL 2013 Optimisation forumle ou macro

[julien91080]

Bonjour à la communauté,


Encore une fois, je me permets de faire appel à vos services suites à 2 semaines de travail intensif autour de mon problème.

Comme vous allez le voir, j’ai un gros fichier à traiter (sous Excel 2013) avec des formules à droite à gauche.

Une première formule (colonne W) teste si une personne à un code (colonne 7) renseigné dans l’onglet « OK ». Si la cellule est vide ou ne contenant pas la bonne valeur (par rapport à l’onglet « OK ») alors « non », si le code est présent, la formule test la colonne E.



L’autre grosse formule, procède à plusieurs tests :

Colonne F si même matricule, Colonne T si « NON », Colonne O si au moins une fois supérieur au 01/01/2014 et si colonne W au moins une fois « oui » => « Ok depuis 2014 »

Sinon

Colonne F si même matricule, Colonne T si « NON », Colonne O si au moins une fois supérieur au 01/01/2014 et si colonne W au moins une fois « Pas suffisant » => « Uniquement »

Sinon

Colonne F si même matricule, Colonne T si « NON », Colonne O si au moins une fois supérieur au 01/01/2014 et si colonne W au différent de « oui » => « En risque »

Sinon

« NON »


Mon soucis est que j’ai réussi à faire fonctionner (calculer) le tout une fois et puis plus rien.

La formule doit être trop lourde.

Existe-t-il une manière plus simple (par macro) à faire ou un moyen d’accélérer le calcule ?


Merci par avance pour votre aide.


Cordialement


Julien

 

[julien91080]

Je tiens à préciser que le fichier en question fait 246180 lignes.

 

[julien91080]

Pas d'idées ou de pistes ?

 

[tbft]

bonjour
mal à la tête
pas moyen de comprendre ton explication.... pas moyen

bonjour Paf

 

[Paf]

Bonjour,

le temps de calcul est énorme effectivement,mais, remplacer les formules par des fonctions personnalisées VBA pourrait allègerait le temps de traitement, ce qui n'éviterait pas le recalcule sur toutes les lignes.

On pourrait envisager un traitement global qui remplacerait les formules par leur valeur sur toutes les lignes, à lancer une seule fois, et une macro vba évènementielle venant recalculer la ligne où des valeurs sont modifiées.

A voir dans quelle colonne la modification doit déclencher la mise à jour.

A+

Edit : Il faudrait aussi préciser en clair ce que doit faire chaque formule pour pouvoir l'adapter en VBA , d'autant que des #REF apparaissent dans certaines.

 

[julien91080]

Bonjour messieurs et merci.

Je ne vois pas de ref sur le fichier.
Pour ce qui est des colonnes, la disposition actuelle me convient à savoir le premier résultat en W et le deuxième en X.

En claire,

En colonne W: vérifier si un collaborateur a validé dans l'onglet OK une matière, si celle ci est suffisante ou pas.
En colonne X : je vérifie plusieurs critères : si toujours présent (T="NON") , si validé au moins une matière depuis le 01/01/2014...

Merci pour votre aide.

 

[Paf]

Re,

Je ne vois pas de ref sur le fichier.

en colonne S : =SI(ESTNA(RECHERCHEV(F:F;#REF!;7;0))=VRAI;"";RECHERCHEV(F:F;#REF!;7;0))
mais peut-être est-ce dû à la conversion du classeur en XL 2003 ?
par ailleurs, pour cette formule, faire la recherche sur la colonne entière (F:F) prends beaucoup de temps (plus de 1000000 lignes à traiter pour 246180 renseignées)

Pour ce qui est des colonnes, la disposition actuelle me convient à savoir le premier résultat en W et le deuxième en X.

Il ne s'agit pas de modifier l'ordre des données, mais de déterminer ( s'il y a des modifications manuelles dans les colonnes , à préciser ) quelle colonne déclenchera le code qui renseignera les colonnes W et X.
Edit : D'ailleurs y aura-t-il des modification ou est ce un tableau figé ?

Pour la formule en W :

=SI(M3="";"non";SI(ESTNA(RECHERCHEV(M3;OK!$A$5:$E$683;5;FAUX)="x");"Pas suffisant";SI(ESTNA(RECHERCHEV(M3;OK!$A$5:$E$1683;4;FAUX));"non";RECHERCHEV(M3;OK!$A$5:$E$1683;4;FAUX))))

Quelle est la bonne plage de recherche ?

Pour la formule enX :

SOMMEPROD(($F$3:$F$28516=F3)*($T$3:$T$28516="NON")*($O$3:$O$28516>DATE(2014;1;1))*($W$3:$W$28516="oui")) si >0 => "Ok depuis le 01/01/2014"

SOMMEPROD(($F$3:$F$28516=F3)*($T$3:$T$28516="NON")*($O$3:$O$28516>DATE(2014;1;1))*($W$3:$W$28516="Pas suffisant")) si >1 => "Uniquement"

SOMMEPROD(($F$3:$F$28516=F3)*($T$3:$T$28516="NON")*($O$3:$O$28516>DATE(2014;1;1))*($W$3:$W$28516<>"oui")) si >1 => "En risque"

si $W$3:$W$28516<>"oui" comprend également les "Pas suffisant", est ce voulu, faut-il corriger ?

A+

PS : Je ne sais pas encore par quel bout commencer, mais ça va bientôt démarrer.

 

[Paf]

Re bonjour,

une macro qui remplace les formules en colonnes W et X par leur valeur.

Sub SupFormul()
Dim Tablo, WS1 As Worksheet

Application.ScreenUpdating = False
Set WS1 = Worksheets("Histo")
Tablo = WS1.Range("w3:X" & WS1.Range("F" & Rows.Count).End(xlUp).Row)

WS1.Range("w3").Resize(UBound(Tablo, 1), UBound(Tablo, 2)) = Tablo
Application.ScreenUpdating = True
End Sub

Pour les mises à jour en cas de rajout de lignes au tableau ou de modifications dans le tableau:
1)pour la formule en colonne X, c'est quasi terminé, malgré les précisions attendues ; et "oui?" en W est traité comme étant différent de "oui".

2)pour la formule en colonne W,

En claire,
En colonne W: vérifier si un collaborateur a validé dans l'onglet OK une matière, si celle ci est suffisante ou pas.

Clair pour vous certainement!
j'ai quasi terminé sur le principe suivant:
Si le code (colonne M), dans la feuille OK :
-n'existe pas, alors en W =>"non"
- existe et que colonne E="*" , alors en W=> "Pas suffisant"
- existe et que rien en E , alors en W=> valeur de la colonne D
Nb : si le principe est bon, la formule actuelle en W ne 'ramène' pas forcément le bon résultat ( qui sera validé par le code de la macro proposée)


Dans l'attente de confirmation et de précisions, je regarde où vous en êtes sur les autres forums.

A+

 

[julien91080]

Bonjour Paf,

"j'ai quasi terminé sur le principe suivant:
Si le code (colonne M), dans la feuille OK :
-n'existe pas, alors en W =>"non"
- existe et que colonne E="*" , alors en W=> "Pas suffisant"
- existe et que rien en E , alors en W=> valeur de la colonne D
Nb : si le principe est bon, la formule actuelle en W ne 'ramène' pas forcément le bon résultat ( qui sera validé par le code de la macro proposée)"

=> C'est exactement ça


Pour ce qui est du colonage, il ne changera pas ou je saurai l'adapter. Le fichier d'origine commence en A2 pour finir en X246176 sachant que l'objectif et de le mettre à jour de manière annuel (donc il peut grossir).

"si $W$3:$W$28516<>"oui" comprend également les "Pas suffisant", est ce voulu, faut-il corriger ?"
=> Oui je teste le résultat de W

Merci de ton aide et du temps que tu y consacre.

Cordialement.

 

[Paf]

re,

Un essai, qui malgré quelques incompréhensions mutuelles, semble fonctionner. A tester.

Sub Julien()
'***** http://excel-downloads.com/threads/optimisation-forumle-ou-macro.20010202/*****
'***** 26/07/2016
'*****
Dim WS1 As Worksheet, WS2 As Worksheet, Dico1, Dico2, TabTmp(1 To 2)
Dim Code As String, i As Long, Clé
Dim TabVerif, Clair As String
Set WS1 = Worksheets("Histo")
Set WS2 = Worksheets("OK")
Set Dico1 = CreateObject("Scripting.Dictionary")
Set Dico2 = CreateObject("Scripting.Dictionary")

'**************  colonne W
'**creation dico des codes
Tablo = WS2.Range("A5:E" & WS2.Range("A" & Rows.Count).End(xlUp).Row)
For i = LBound(Tablo) To UBound(Tablo)
    TabTmp(1) = Tablo(i, 4)
    TabTmp(2) = Tablo(i, 5)
    Dico2(CStr(Tablo(i, 1))) = TabTmp
Next

Tablo = WS1.Range("F3:W" & WS1.Range("F" & Rows.Count).End(xlUp).Row)

'**mise à jour de la colonne W  dans le tablo histo
For i = LBound(Tablo) To UBound(Tablo)
    Code = CStr(Tablo(i, 8))
    If Not Dico2.Exists(Code) Then
        Tablo(i, 18) = "non"
        'WS1.Cells(i + 2, 23) = "non"
    ElseIf Dico2(Code)(2) = "x" Then
        Tablo(i, 18) = "Pas suffisant"
        'WS1.Cells(i + 2, 23) = "Pas suffisant"
    Else
        Tablo(i, 18) = Dico2(Code)(1)
        'WS1.Cells(i + 2, 23) = Dico1(Code)(1)
    End If
Next

'**************  colonne X

'* creation dico nombre de  oui,pas suffisant, <> oui
For i = LBound(Tablo) To UBound(Tablo)
    Matric = CStr(Tablo(i, 1))
    If Not Dico1.Exists(Matric) Then Dico1(Matric) = Array(0, 0, 0)
    If Tablo(i, 15) = "NON" Then
        If Tablo(i, 10) >= CDate("01/01/2014") Then
            TabVerif = Dico1.Item(Matric)
            If Tablo(i, 18) = "oui" Then
                TabVerif(0) = TabVerif(0) + 1
            ElseIf Tablo(i, 18) = "Pas suffisant" Then
                TabVerif(1) = TabVerif(1) + 1
            ElseIf Tablo(i, 18) <> "oui" Then
                TabVerif(2) = TabVerif(2) + 1
            End If
            Dico1(Matric) = TabVerif
        End If
    End If
Next
' traduction des nombres en clair
For Each Clé In Dico1
    If Dico1(Clé)(0) > 0 Then Clair = "Ok depuis le 01/01/2014"
    If Dico1(Clé)(1) > 0 Then Clair = "Uniquement"
    If Dico1(Clé)(2) > 0 Then Clair = "En risque"
    If Dico1(Clé)(0) = 0 And Dico1(Clé)(1) = 0 And Dico1(Clé)(2) = 0 Then Clair = "NON"
    Dico1(Clé) = Clair
  Next

'******** préparation  et copie du Tableau final colonne W et X
ReDim TabFin(1 To UBound(Tablo), 1 To 2)
For i = LBound(Tablo) To UBound(Tablo)
    TabFin(i, 1) = Tablo(i, 18)
    TabFin(i, 2) = Dico1(CStr(Tablo(i, 1)))
Next

WS1.Range("W3").Resize(UBound(TabFin, 1), 2) = TabFin
End Sub

A lancer dès que des modifications sont apportées en feuille Histo ou OK

Nota : en écrivant ce post, je m'aperçois que j'ai oublié un élément dans l'évaluation de la colonne X :SI(SOMMEPROD((N52="")*(T52="NON"))>0;"Pas depuis son arrivée" .

Une correction dans les prochains jours.

En attendant faite part de vos commentaires et du temps de traitement .

A+

 

[julien91080]

Bonjour Paf,

Honnêtement, ça déboite.

A priori, il y a un hic sur la condition "If Dico1(Clé)(0) > 0 Then Clair = "Ok depuis le 01/01/2014"".

Si tu regarde le tableau en ligne 130: la personne à bien en colonne O une date supérieur au 01/01/2014, "NON" en colonne T et "oui" en X
=> la macro met "en risque" alors que cela devrait être "Ok depuis le 01/01/2014".

C'est super n'empêche.

 

[Paf]

Re,

oui oui, énorme erreur!

Tout est à reprendre ....

j'essaie de corriger ....

Et quel temps de traitement des 240000 lignes ( en heures ? secondes ?) ? La correction serait du même acabit voire pire!

A+

 

[Paf]

Re
heu ... finalement quelque chose m'échappe .

En fait on ne traite pas les groupe de matricules comme le laissaient entendre les sommeprod de la formule initiale, mais ligne par ligne !! ??
En travaillant ligne par ligne, la formule devient plus légère, et plus besoin de VBA; en X129 on devrait simplement avoir:
=SI(ET(T129="NON";O129>=DATE(2014;1;1); W129="oui");"Ok depuis le 01/01/2014";SI(ET(T129="NON";O129>=DATE(2014;1;1);W129="Pas suffisant"); "Uniquement"; SI(ET(T129="NON";O129>=DATE(2014;1;1);W129<>"oui"); "En risque"; SI(ET(N129="";T129="NON"); "Pas depuis son arrivée"; "NON"))))

formule à tirer vers le haut et le bas.

Bonne suite

 

[julien91080]

Oui il ne faut pas faire du ligne par ligne.
Comme l'indique le SOMMEPROD, la formule me dit "Ok depuis le 01/0/2014" si pour une personne (un matricule) a au moins passé une fois une matière à "oui" (de l'onglet "OK") depuis le 01/01/2014.

De ce fait, ta formule ne répond pas à cela.

Bonne suite à toi

 

[Paf]

Re,

j'avais du mal à me remettre dans le bain!

Dans la macro, remplacer :

' traduction des nombres en clair
For Each Clé In Dico1
    If Dico1(Clé)(0) > 0 Then Clair = "Ok depuis le 01/01/2014"
    If Dico1(Clé)(1) > 0 Then Clair = "Uniquement"
    If Dico1(Clé)(2) > 0 Then Clair = "En risque"
    If Dico1(Clé)(0) = 0 And Dico1(Clé)(1) = 0 And Dico1(Clé)(2) = 0 Then Clair = "NON"
    Dico1(Clé) = Clair
  Next

par :

' traduction des nombres en clair
For Each Clé In Dico1
    If Dico1(Clé)(0) > 0 Then
        Clair = "Ok depuis le 01/01/2014"
    ElseIf Dico1(Clé)(1) > 0 Then
        Clair = "Uniquement"
    ElseIf Dico1(Clé)(2) > 0 Then
        Clair = "En risque"
    End If
    If Dico1(Clé)(0) = 0 And Dico1(Clé)(1) = 0 And Dico1(Clé)(2) = 0 Then Clair = "NON"
    Dico1(Clé) = Clair
  Next

Ce doit être bon pour ça !

A+