XL 2016 Fichier Excel lent, macro nécessaire ?

[clacou12]

Bonjour à tous et à toutes,

Je suis tout nouveau sur ce forum, et je suis aussi novice dans Excel. Jusqu’à maintenant, je me suis toujours débrouillé en vous lisant, en contournant bon nombre de problème que j’ai rencontré. Excusez-moi pour le pater qui suit, mais nécessaire pour une compréhension rapide du problème. Merci par avance à ceux qui prendront le temps de me lire.

Pour tout vous expliquer, je travaille sur un fichier qui me permettrait de visualiser l’ensemble des carnets de commande clients pour tous les projets de l’entreprise. Jusqu’à aujourd’hui, tout marchaient bien, j’ai mis au point toutes les formules et indicateurs pour 1 projet « projet test ». Je voulais maintenant intégrer tous les autres projets, mais je me suis rendu compte que le fichier rame énormément quand je lance la macro.

Voici comment marche le tableur :

Je rentre la nouvelle date en A2 (feuille PIC) : date du jour +7.

Cette date correspond au nombre de jour minimum pour une commande ferme (le client doit nous donner une commande ferme 7 jours avant la date d’expédition de celle-ci).

Ensuite je lance la macro (macro 1) qui supprime toutes les lignes de données de la feuille « extraction SAP » qui sont supérieures à la date A2.

Puis je colle m’a nouvelle extraction à la suite des données restantes (feuille « extraction SAP ») (l’extraction peut faire 4000 lignes)

Je répète l’opération tous les jours.

Cette manipulation me permet d’avoir l’historique des commandes ferme et une mise à jour des données quotidiennes.


L’exploitation des données se fait sur la feuille PIC :

Elle est composée tout en haut à l’horizontale d’un calendrier annuel. Il me donne automatiquement le jour de la semaine et le numéro de semaine. Une mise en forme conditionnelle met en couleur les semaines paires et les impairs pour une meilleure visibilité. J’ai également fait des groupements par mois pour mieux naviguer dans le tableur.

Sur le côté gauche : J’aurais l’ensemble des projets, j’ai donc mis un filtre vertical pour pouvoir les visualiser ou les cacher. Chaque projet peut avoir plusieurs références, le projet 1 en a 3. Chaque référence à deux lignes de recherche appelée (quantité de pièces ; prévisionnel ou ferme).

Au milieu, il y a un ensemble de formules RechercheV + Concatener qui me servent de recherche à deux critères (références et dates) pour afficher :

- les quantités commandées

- le type d’échéance (prévisionnel ou ferme)

L’extraction me donne le type d’échéance avec un numéro, 4 = prévision, 1 ou 9 = Ferme

Dans un deuxième temps, des mises en forme conditionnelles colore la case de la quantité commandée en fonction du type d’échéance (ferme ou prévisionnel) de la ligne d’en dessous.

Ferme = vert ; prévision = jaune

Le but de tout ce montage est seulement de connaitre les quantités commandées par jour, et savoir si la commande est ferme ou prévisionnelle.

Les lignes « prévisionnel ou ferme » ne me servent à rien d’autre, c’est pour cela que je ne les filtres pas avec les projets.


Problème : le fichier rame énormément et il n’y a que 2 projets pour le moment. Par la suite il y en aura une quinzaine avec Multi références.

Questions :

Est-ce normal que le fichier rame autant au lancement de la macro ?

Serait-il possible de remplir tout le milieu de la feuille avec le lancement d’une macro afin de gagner de la vitesse ? (c’est-à-dire éviter toutes les formules de rechercheV + concatener et les mises en forme conditionnelles).

Merci pour votre aide.

PS : je vous joint le fichier, pour voir les données de l'extraction, il faut regarder le projet 1 a partir du mois de février.

 

[cp4]

Bonsoir et bienvenu sur XLD,

Je veux bien essayer, je dis bien essayer de t'aider.
Mais avant d'ouvrir ton fichier dis-moi sur quelle version d'Excel tu travailles.
Car l'icône de ton fichier n'est pas conforme aux fichiers Excel standard.

edit: pas fait attention, tu es à jour Excel2016!

Désolé, viens de jeter un coup d’œil au fichier. Je crains ne pas pouvoir t'aider.

Bon courage.

 

[eriiic]

Bonjour,

pour supprimer les lignes SAP trie par date, recherche la 1ère date à garder et supprime toutes les lignes en une fois.
Ca ira beaucoup plus vite.

Pour le reste tu as l'air d'avoir un nombre considérable de formules, il faut le temps qu'elles s'évaluent.
Déjà utilise =sierreur(ta_formule;0) au lieu de =SI(ESTNA(ta_formule);0; ta_formule), tu vas diviser par 2 les calculs.
Et ta formule en CA8 (désolé, je me suis posé au milieu) peut avantageusement être remplacée par :

=SIERREUR(INDEX(DECALER('extraction SAP'!$D$1;EQUIV(B8;'extraction SAP'!$B:$B;0)-1;;NB.SI('extraction SAP'!$B:$B;B8));EQUIV(CA$3;DECALER('extraction SAP'!$C$1;EQUIV(B8;'extraction SAP'!$B:$B;0)-1;;NB.SI('extraction SAP'!$B:$B;B8));0));0)

Elle recherche la plage du n° de commande, y recherche la date, puis retourne la valeur même ligne. Ca sera également beaucoup plus rapide. Même principe pour les autres.
Plus de concaténations multiples lentes.
Du coup tu peux aussi supprimer la colonne A de SAP.
eric

 

[clacou12]

Bonjour à tous,

Merci pour vos réponses si rapide,

dommage cp4, c'est l'intention qui compte.

et merci eriiiic pour tes conseils, je vais les mettre en place dans la journée pour voir le gain de temps.

Pensez vous qu'il est possible de remplir tout le milieu du tableau (F8 à JR100) avec le lancement d'une macro ?
Car je vais avoir pas loin de 100 références entre tout, et quand je vois la lenteur pour seulement 3 références dans le projet 1. Je me dis que c'est bourrin de m'a part de remplir toutes les cellules de formule sur 100 lignes et 260 colonnes. ça fait 26000 formules à calculer et 26000 mises en forme conditionnelles.

Je ne connais pas grand chose aux macros, peut être que c'est trop compliqué a réaliser.

 

[eriiic]

Bonjour,

ayant eu un peu de temps disponible j'ai regardé de plus près.
1) tu es vraiment obligé de mettre tes totaux mois en plein milieu ?
On pourrait aller vers qq chose de plus efficace en les reportant soit devant, soit à la fin des dates.

2) quand tu as une commande dans SAP est-ce PIC est entièrement vide où il peut déjà y avoir des valeurs à conserver sur cette commande ?
eric

 

[clacou12]

Bonjour Eric,

Merci pour l'attention que tu portes à mon problème.
1) Les totaux des mois peuvent être mis à la fin. Je les avais placés ainsi pour mieux naviguer entre les regroupements par mois. En effet, quand tous les) regroupements sont réduits, on ne sait pas à quels mois ils correspondent. j'aurais préféré que l'on puisse écrire le nom du regroupement à côté du "+" où "-" sur lesquels on clique.

2) Chaque jour, la feuille extraction SAP sera mise à jour;
exemple :
aujourd'hui nous sommes le 05/03/2018, l'extraction SAP a des valeurs allant du 05/03/2018 au 05/06/2018 (soit trois mois de données)
demain, nous serons le 06/03/2018 l'extraction SAP de demain aura des valeurs allant du 06/03/2018 au 06/06/2018 (soit trois mois de données)

Chaque jour, l'extraction de données se décale d'une journée (toujours 3 mois de données), et il faut garder la dernière valeur des journées passé.
dans notre exemple, toutes les quantités de produits du 05/03/2018 doivent être enregistrées car elles n’apparaîtront plus dans l'extraction du 06/03/2018 qui correspond aux valeurs du 06/03/2018 au 06/06/2018.
ainsi de suite ...

Du coup, pour répondre à ta question, il peut y avoir des données pour les dates inférieures à la date inscrire en cellule A2 de la feuille PIC.

Je suis actuellement sur l'ébauche d'une macro, mais avoir de l'aide de quelqu'un d’extérieur et très enrichissant. On a pas forcement les mêmes idées pour répondre a une problématique.
Ta première réponse m'a déjà bien aidé, et je ne dis pas non a de nouvelles idées d'amélioration.
Merci beaucoup Eriiic.

 

[eriiic]

Bonjour,

dommage...
Je pense qu'il faut privilégier l'ergonomie après et conserver tes totaux pour les groupements.
On ne pourra donc inscrire les cellules que 2 par 2, ce qui sera plus lent qu'en blocs complets.
Une proposition :

Sub maj()
    Dim datas, c As Range
    Dim lig As Long, ligPIC As Long, colPIC As Long
    Dim docVente As Long, tmp(1 To 2, 1 To 1) As Double
    datas = [A1].CurrentRegion.Value
    Application.ScreenUpdating = False
    With Sheets("PIC")
        For lig = 2 To UBound(datas)
            ' lig docVente
            If datas(lig, 1) <> docVente Then
                docVente = datas(lig, 1)
                Set c = .Columns(2).Find(docVente, , xlValues, xlWhole)
                If c Is Nothing Then
                    ligPIC = 0
                    MsgBox "Document de vente " & docVente & " absent"
                Else
                    ligPIC = c.Row
                End If
            End If
            ' col date
            On Error Resume Next
            colPIC = Application.Match(CLng(datas(lig, 2)), .[3:3], 0)
            If Err Then
                colPIC = 0: Err.Clear
                MsgBox Format(datas(lig, 2), "dd/mm/yyyy") & " absent"
            End If
            On Error GoTo 0
            ' inscription
            tmp(1, 1) = datas(lig, 3): tmp(2, 1) = datas(lig, 4)
            If ligPIC And colPIC Then .Cells(ligPIC, colPIC).Resize(2) = tmp
        Next lig
    End With
End Sub

Voir si le timing est supportable avec une extraction complète.
En compliquant on pourrait regrouper les écritures par mois ce qui ferait gagner pas mal de temps.

Tu peux réduire tes MFC à 1.
Mettre le fond vert d'office en manuel, et grouper les tests avec un OU pour la MFC jaune :

=ou(F$3>=$A$2;F9=4)

Toujours ça de gagné en plus
eric

 

[clacou12]

Bonjour Eriiic,

Désolé de revenir vers toi que maintenant,
J'ai monté le fichier avec les 200 références de produits, et je dois dire que ta macro est monstrueuse !!
En deux secondes, elle transfère toutes les données.
Elle est très rapide, et je suis très contant car elle pourra m'inspirer pour mes prochains travaux.
Je ne connais pas les fonctions que tu as utilisé, mais je vais les apprivoiser.
Je n'ai pas encore essayé de mettre les mises en formes conditionnelles, je ne vais pas tarder. Je vais suivre ton conseil en utilisant un OU.
En tout cas, ton aide est très précieuse.
Je te remercie beaucoup.

 

[eriiic]

Bonjour,

tant mieux si ça suffit
Bonne cvontinuation
eric

 

[clacou12]

Bonjour Eriiic,

Excuses moi d'abuser de ta gentillesse, je suis a nouveau bloqué.
En faisant des essais hier soir, je me suis rendu compte qu'il y a des ratés dans le transfert de données.
Je m'en suis rendu compte que maintenant alors que j'aurais pu le voir direct si j'avais vérifié l'ensemble des données transferées.
Je ne comprends pas du tout quelle logique se cache dernière ce problème, car certaines données sont transférées, d'autres non. Et ça n'a pas l'air de venir du format des dates.

Dans le fichier que tu m'as renvoyé, on peut voir le problème, par exemple pour la Ref 35413098 : il manque les données du
27/02/2018
01/03/2018
02/03/2018
05/03/2018
06/03/2018

puis a nouveau du
20/03/2018
21/03/2018
22/03/2018
23/03/2018
26/03/2018
27/03/2018
28/03/2018

Et a chaque référence, il y a de temps en temps des ratés, et jamais aux même dates ....

Si tu as une idée d'où cela peut venir, ça m'aiderai énormément.

Merci d'avance Eriiic

 

[eriiic]

Bonjour,

à la fin remplace la ligne par:

If ligPIC > 0 And colPIC > 0 Then .Cells(ligPIC, colPIC).Resize(2) = tmp

bizarre qu'il faille rajouter >0 vu que 0=False mais bon...
eric
A la réflexion il doit faire un AND bit à bit ce qui explique

 

[clacou12]

Pouhhh, quelle vitesse pour avoir trouvé la réponse au problème !
Merci beaucoup pour ton aide.

Je viens de détecter un autre problème qui peut arriver de temps en temps :
Sur ton fichier, tu ne peux pas le voir car il n'y avait pas cette exception.

Exemple 1 :
Doc vente : Date: Qté Type:
35304656 20/02/2018 3 600 9
35304656 20/02/2018 5 850 9


Exemple 2 :
Doc vente : Date: Qté Type:
35304656 20/02/2018 3 600 9
35304656 20/02/2018 5 850 4
35304656 20/02/2018 5 850 1

Il est possible qu'un client fasse 2 commandes (voir plus) avec le même numéro de
commande (car c'est des commandes ouvertes) pour le même jour.
Du coup, il faudrait arriver à fusionner les lignes en additionnant les quantités avant de
lancer la macro de transfert. Dans l'exemple 2 avec différents types, s'il y a un type 4, il est prioritaire.
Sinon c'est le 9.

Résultats :
Exemple 1 :
Doc vente : Date: Qté Type:
35304656 20/02/2018 9 450 9

Exemple 2 :
Doc vente : Date: Qté Type:
35304656 20/02/2018 13 300 4

J'ai conscience que cette exception complique énormément la macro.
Si tu pense que c'est trop compliqué de gérer le "type" en plus.
A défaut je ferai une macro qui fusionnera les lignes sans ce préoccuper du "type".

En attendant ton retour,

 

[eriiic]

Bonjour,

je regarderai ce soir ou demain.
Peux-tu fournir un fichier (court) avec ces cas.

1) Doc vente et date sont bien triés à l'ouverture ?
2) On considère toujours que la cellule doc vente-date sera bien vide au début ? Tous les éléments seront obligatoirement dans le même fichier ?
3) Si en type on a 9-1 ou 4-1 ? Le 1 n'est jamais prioritaire ?
eric

 

[clacou12]

Bonjour Eriiic,

1) Doc vente et date sont toujours triés à l'ouverture.
2) La première ligne de la feuille 2 est toujours la ligne de titre et tous les éléments sont sur le même fichier oui.
3) En réalité, le type 1 et le type 9 sont les mêmes, du coup 4 est toujours prioritaire, sinon c'est soit 1 ou 9, pas d'importance.

Dans le fichier joint, j'ai disposé dans la deuxième feuille 2 tableaux :
Avant : données brutes
Après : données additionnées et lignes fusionnées

Pour mieux comprendre, j'ai encadré par date les commandes.
Et sur la feuille principale, j'ai mis un petit tableau des résultats attendus.

Si c'est possible et que ça ne surcharge pas la macro, dans la deuxième feuille, le "Après" pourrait remplacer le "avant" en terme d'emplacement de cellule.

En attendant de voir comment on peut solutionner le problème, merci beaucoup Eriiic !

 

[eriiic]

Bonjour,

tu as vraiment besoin de la feuille avec les cumuls ?
Parce qu'on peut remplir directement le tableau.
eric