XL 2016 Fichier Excel trop "lourd" aide pour optimisation

[Ragnarr]

Bonjour à tous,



J’arrive au bout d'un projet pour lequel le forum m'a déjà été d'un grand secours.
Je souhaiterai, une nouvelle fois, bénéficier de votre conseil / assistance

nous avons créer un fichier Excel nous permettant le suivi de la maintenance de notre parc roulant.
L’ensemble des formules fonctionnait parfaitement jusqu’à ce qu’on étoffe notre base de données en appliquant les formules sur des milliers de lignes


Le problème que je rencontre est donc que la taille, et le contenu, du fichier Excel que nous avons développé le rendent presque inutilisable.
Le temps de sauvegarde, de modification, traitement, calcul est beaucoup trop long voir bloque littéralement le document.



Après avoir cherché sur le forum j’ai réduit les formules, paramètres et données inutiles.

J’ai ensuite sauvegardé le fichier sous format « xlsb » afin de diminuer un peu sa taille

Je ne sais aujourd’hui comment le rendre plus digeste.

Auriez-vous des pistes ?



Je crois avoir lu que des formules matricielles alourdissaient les documents.

ci dessous un lien vers le projet en question.

merci d'avance pour le temps que vous consacrerez à la lecture de cette demande

Julien



https://mon-partage.fr/f/jqnBIfmA/

 

[Dan]

Bonjour,

Je pense que vous auriez tout intérêt à faire appel à VBA pour alléger et augmenter la rapidité de votre fichier
Mais cela implique une révision de votre fichier pour supprimer les formules matricielles qui ralentissent le fonctionnement du fichier

Crdlt

 

[Ragnarr]

Merci pour ce prompt retour.

je prends bonne note de ce conseil et vous en remercie

je ne maitrise malheureusement pas le langage VBA et ne pourrai donc faire l'adaptation moi même

si je vous expliques les besoins qui nous ont pousser à créer certaines formules matricielles,

Pensez vous pouvoir m'aider à les transposer VBA?



Julien

 

[merinos]

Salut @jdidier-thillpaysage@oran ,

Les formules appliquées a de grandes quantitées de lignes ...
Je ne connais qu'un seule solution: powerquery...
Tu peux travailler avec des tables contenant des millions de lignes... et y joindre des info venant d'autres tables...

 

[Bernard_XLD]

Bonjour le fil, le forum

@jdidier-thillpaysage@oran , il y a en fait peu de données dans votre fichier, le problème vient de vos trois formules matricielles de la feuille "base de données" dans lesquelles vous faites, avec ces matricielles, une recherche sur les colonnes entières de la feuille archive ( soit 1048576 lignes ) et qui ne contient actuellement que 1990 lignes de données.

=MAX(SI(Archives!$B:$B='Base de données '!$E3;Archives!$C:$C))

Donnez donc une taille raisonnable à vos recherches en modifiant vos formules matricielles (en mettant à la place de 10000 le nombre de lignes que vous voulez) et le fichier redeviendra très véloce. (instantané chez moi avec 10000 lignes) alors que l'original mettait à genoux mon I7 4790K 16Go de mémoire et SSD système enterrait mon ordinateur et rebouchait le trou.

=MAX(SI(Archives!$B$4:$B$10000='Base de données '!$E3;Archives!$C$4:$C$10000))

la taille du fichier ayant réduit, il passe en zippé, voir pièce jointe.
Il n'était de toute façon pas si gros à l'origine, 1.5 Mo

Bien cordialement, @+

 

[Dan]

Bonjour

si je vous expliques les besoins qui nous ont pousser à créer certaines formules matricielles,

Oui je comprends et comme je vous l'ai écrit ce sont ces formules qui ralentissent le fichier. C'est toujours à éviter quand on peut.

Pensez vous pouvoir m'aider à les transposer VBA?

Oui je pensais commencer mais vu que vous avez d'autres propositions, dites moi si intérêt ou pas par VBA.
Sinon je vous laisse continuer avec les autres intervenants.

Cordialement

 

[Ragnarr]

Salut @jdidier-thillpaysage@oran ,

Les formules appliquées a de grandes quantitées de lignes ...
Je ne connais qu'un seule solution: powerquery...
Tu peux travailler avec des tables contenant des millions de lignes... et y joindre des info venant d'autres tables...

merci pour la solution "powerquery" que je ne connaissais pas.
je la garde dans un coin de ma tête pour l'étudié lorsque je serai confronté à cette problématique .

 

[Ragnarr]

Bonjour le fil, le forum

@jdidier-thillpaysage@oran , il y a en fait peu de données dans votre fichier, le problème vient de vos trois formules matricielles de la feuille "base de données" dans lesquelles vous faites, avec ces matricielles, une recherche sur les colonnes entières de la feuille archive ( soit 1048576 lignes ) et qui ne contient actuellement que 1990 lignes de données.

=MAX(SI(Archives!$B:$B='Base de données '!$E3;Archives!$C:$C))

Donnez donc une taille raisonnable à vos recherches en modifiant vos formules matricielles (en mettant à la place de 10000 le nombre de lignes que vous voulez) et le fichier redeviendra très véloce. (instantané chez moi avec 10000 lignes) alors que l'original mettait à genoux mon I7 4790K 16Go de mémoire et SSD système enterrait mon ordinateur et rebouchait le trou.

=MAX(SI(Archives!$B$4:$B$10000='Base de données '!$E3;Archives!$C$4:$C$10000))

la taille du fichier ayant réduit, il passe en zippé, voir pièce jointe.
Il n'était de toute façon pas si gros à l'origine, 1.5 Mo

Bien cordialement, @+

Bonjour

merci pour le temps consacré à l'étude de ma problématique
je vais clairement garder en mémoire devoir définir des plages de recherche...
et non laisser 1 000 000 de lignes de recherche


la solution me convient parfaitement,
nous retrouvons un temps de réponse instantané sauf pour la macro "archivage" de la feuille " saisie qui met environ 5mn a basculer 30 lignes d'une feuille à l'autre mais nous ferons avec.



vous nous avez permis de revenir a une utilisation " normale" +++
encore un grand merci !

Désolé pour l'expérience désagréable qu'a subit votre ordinateur ^^ ( cela m'a beaucoup fait rire d'ailleurs)

et félicitation pour la nomination de modérateur membre du staff


Julien

 

[Ragnarr]

Bonjour

Oui je comprends et comme je vous l'ai écrit ce sont ces formules qui ralentissent le fichier. C'est toujours à éviter quand on peut.

Oui je pensais commencer mais vu que vous avez d'autres propositions, dites moi si intérêt ou pas par VBA.
Sinon je vous laisse continuer avec les autres intervenants.

Cordialement

Bonjour,


merci pour le retour, l'intérêt de transposé en VBA n'est plus "vital", nous sommes revenu à un traitement moins chronophage grâce à la proposition de Bernard_XLD

merci Dan pour votre disponibilité et vos conseils



Julien

 

[Bernard_XLD]

Bonjour le fil, le forum

"sauf pour la macro "archivage" de la feuille " saisie qui met environ 5mn a basculer 30 lignes d'une feuille à l'autre mais nous ferons avec."
@jdidier-thillpaysage@oran , votre macro d'archivage un peu modifiée devrait être un peu moins lente. (juste un peu ! )

Bien cordialement, @+

[édition : code modifié en post #17]

 

[Bernard_XLD]

Bonjour le fil, le forum

@jdidier-thillpaysage@oran , après réflexion et comme vous utilisez une macro d'archivage, il serait certainement plus simple, pour régler votre problème de façon pérenne, de redimensionner vos matricielles à la fin de l'archivage de nouvelles données pendant que le calcul automatique est désactivé. De cette façon, vos matricielles seront toujours correctement dimensionnées et le temps de calcul au plus juste.

Bien cordialement, @+

[édition : code modifié en post #17]

 

[Ragnarr]

Bonjour le fil, le forum

@jdidier-thillpaysage@oran , après réflexion et comme vous utilisez une macro d'archivage, il serait certainement plus simple, pour régler votre problème de façon pérenne, de redimensionner vos matricielles à la fin de l'archivage de nouvelles données pendant que le calcul automatique est désactivé. De cette façon, vos matricielles seront toujours correctement dimensionnées et le temps de calcul au plus juste.

Bien cordialement, @+

Sub Archive()
    Dim DLsaisie%, DLArchives%, Mem_Calculation
    Mem_Calculation = Application.Calculation
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    On Error GoTo Gere_Erreurs
    DLsaisie = Worksheets("Saisie").Range("F65500").End(xlUp).Row   ' dernière ligne de Saisie
    If DLsaisie > 3 Then
        With Worksheets("Archives")
            .Cells(1 + .Range("B65500").End(xlUp).Row, 2).Range("A1:G" & DLsaisie - 3).Value = Worksheets("Saisie").Cells(4, 6).Range("A1:G" & DLsaisie - 3).Value ' copie de la colonne F vers la colonne B
            DLArchives = .Range("B" & .Rows.Count).End(xlUp).Row
        End With
        Worksheets("Saisie").Range("F4:k" & DLsaisie).ClearContents ' effacer tableau de saisie
        With Worksheets("Base de données ")
            .Range("G3:G" & .Range("B" & .Rows.Count).End(xlUp).Row).FormulaArray = "=MAX(IF(Archives!R4C2:R" & DLArchives & "C2='Base de données '!RC5,Archives!R4C3:R" & DLArchives & "C3))"
            .Range("H3:H" & .Range("B" & .Rows.Count).End(xlUp).Row).FormulaArray = "=MAX(IF(Archives!R4C2:R" & DLArchives & "C2='Base de données '!RC5,Archives!R4C6:R" & DLArchives & "C6))"
            .Range("I3:I" & .Range("B" & .Rows.Count).End(xlUp).Row).FormulaArray = "=MAX(IF(Archives!R4C2:R" & DLArchives & "C2='Base de données '!RC5,Archives!R4C5:R" & DLArchives & "C5))"
        End With
        MsgBox "Archivage terminé.", vbOKOnly + vbInformation
    Else
        MsgBox "Aucune donnée à archiver.", vbOKOnly + vbInformation
    End If
Gere_Erreurs:
    With Application
        .Calculation = Mem_Calculation
        .ScreenUpdating = True
    End With
End Sub
[/CO
[/QUOTE]

[QUOTE="Yeahou, post: 20432278, member: 568"]
Bonjour le fil, le forum

[USER=340859]@jdidier-thillpaysage@oran[/USER] , après réflexion et comme vous utilisez une macro d'archivage, il serait certainement plus simple, pour régler votre problème de façon pérenne, de redimensionner vos matricielles à la fin de l'archivage de nouvelles données pendant que le calcul automatique est désactivé. De cette façon, vos matricielles seront toujours correctement dimensionnées et le temps de calcul au plus juste.

Bien cordialement, @+

[CODE=vb]Sub Archive()
    Dim DLsaisie%, DLArchives%, Mem_Calculation
    Mem_Calculation = Application.Calculation
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    On Error GoTo Gere_Erreurs
    DLsaisie = Worksheets("Saisie").Range("F65500").End(xlUp).Row   ' dernière ligne de Saisie
    If DLsaisie > 3 Then
        With Worksheets("Archives")
            .Cells(1 + .Range("B65500").End(xlUp).Row, 2).Range("A1:G" & DLsaisie - 3).Value = Worksheets("Saisie").Cells(4, 6).Range("A1:G" & DLsaisie - 3).Value ' copie de la colonne F vers la colonne B
            DLArchives = .Range("B" & .Rows.Count).End(xlUp).Row
        End With
        Worksheets("Saisie").Range("F4:k" & DLsaisie).ClearContents ' effacer tableau de saisie
        With Worksheets("Base de données ")
            .Range("G3:G" & .Range("B" & .Rows.Count).End(xlUp).Row).FormulaArray = "=MAX(IF(Archives!R4C2:R" & DLArchives & "C2='Base de données '!RC5,Archives!R4C3:R" & DLArchives & "C3))"
            .Range("H3:H" & .Range("B" & .Rows.Count).End(xlUp).Row).FormulaArray = "=MAX(IF(Archives!R4C2:R" & DLArchives & "C2='Base de données '!RC5,Archives!R4C6:R" & DLArchives & "C6))"
            .Range("I3:I" & .Range("B" & .Rows.Count).End(xlUp).Row).FormulaArray = "=MAX(IF(Archives!R4C2:R" & DLArchives & "C2='Base de données '!RC5,Archives!R4C5:R" & DLArchives & "C5))"
        End With
        MsgBox "Archivage terminé.", vbOKOnly + vbInformation
    Else
        MsgBox "Aucune donnée à archiver.", vbOKOnly + vbInformation
    End If
Gere_Erreurs:
    With Application
        .Calculation = Mem_Calculation
        .ScreenUpdating = True
    End With
End Sub

"
Bonjour le fil, le forum

@jdidier-thillpaysage@oran , après réflexion et c"

je change de pseudo dès que possible...





Effectivement cela va """un peu""",, plus vite





Vous allez au-delà de mes espérances.


Non content de me débloquer la situation et nous faire gagner quotidiennement un temps précieux, le redimensionnement des matricielles automatiques grâce à l'activation de la macro permet d'être au plus juste et une nouvelle fois de nous faire économiser des ressources.


Cela me permet également d'annuler la procédure créée pour ma collaboratrice qui visait à redimensionner les matricielles mensuellement, on fait avec les moyen du bord^^

Le moldu que je suis vous remercie une énième fois pour ces solutions et surtout pour le temps développement / analyse consacré à notre projet.

 

[Bernard_XLD]

Re, Julien

je change de pseudo dès que possible..

Le moldu que je suis ...

 

[Ragnarr]

@Bernard_XLD

petite interrogation
sauf erreur de ma part j'ai un petit soucis avec la génération de la matricielle effectuée par la macro,

je vais essayer d'être clair avec mes mots (désolé si je n'y parviens pas de suite)
je pense avoir isolé, par déduction, le problème dans le code mais suis incapable de faire davantage



With Worksheets("Base de données ")
.Range("G3:G" & .Range("B" & .Rows.Count).End(xlUp).Row).FormulaArray = "=MAX(IF(Archives!R4C2:R" & DLArchives & "C2='Base de données '!RC5,Archives!R4C3:R" & DLArchives & "C3))"
.Range("H3:H" & .Range("B" & .Rows.Count).End(xlUp).Row).FormulaArray = "=MAX(IF(Archives!R4C2:R" & DLArchives & "C2='Base de données '!RC5,Archives!R4C6:R" & DLArchives & "C6))"
.Range("I3:I" & .Range("B" & .Rows.Count).End(xlUp).Row).FormulaArray = "=MAX(IF(Archives!R4C2:R" & DLArchives & "C2='Base de données '!RC5,Archives!R4C5:R" & DLArchives & "C5))"
End With



initialement la formule matricielle doit être égale à :
en G3 =MAX(SI(Archives!$B$4:$B$10000=$E3;Archives!$C$4:$C$10000))
G4 =MAX(SI(Archives!$B$4:$B$10000=$E4;Archives!$C$4:$C$10000))
G5 =MAX(SI(Archives!$B$4:$B$10000=$E5;Archives!$C$4:$C$10000))
ect ect ect ..................



la génération de la matricielle par la macro produit :
en G3 =MAX(SI(Archives!$B$4:$B$10000=$E3;Archives!$C$4:$C$10000))
G4 =MAX(SI(Archives!$B$4:$B$10000=$E3;Archives!$C$4:$C$10000))
G5 =MAX(SI(Archives!$B$4:$B$10000=$E3;Archives!$C$4:$C$10000))
ect ect ect ..................


du coup toutes les valeurs sont identiques car elles se basent sur la même cellule
j'ai tenté de comprendre dans la macro mais c'est encore bien trop illisible pour moi

 

[Bernard_XLD]

Re,

je n'avais pas fait attention à cela, je regarde, utilisez la macro du post 10 en attendant.