Fichier qui semble devenir trop lourd pour être enregistré

[williamss]

Bonjour,

Voici un fichier Excel Document Cjoint où tout les jours à la colonne A j'enregistre des données d'une station météo mais qui sont dans une seul cellule donc le premier tableau (colonne C à AJ) sépare les données dans différentes colonnes pour mieux lire ces données. Puis un 2ème tableaux va chercher les données du 1er tableau suivant leur critère (maximum, minimum de la température,...).

Le problème est que si je tire la ligne du 1er tableau alors après quand je tente d'enregistrer le fichier cela ne me l'enregistre pas et mets (ne répond pas). Le problème semblerait venir du premier tableau car si avant d'enregistrer le fichier je sélectionne le 1er tableau puis fait copier-collage spécial/valeur alors là l'enregistrement se réalise sans problème.

Donc comment peut-on faire pour avoir des formules nous donnant les mêmes résultats au 1er tableau mais en étant bien plus léger car en plus il faudrait que ce tableau soit au moins 2 fois plus long (6 mois) ??

merci

Williams

 

[job75]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonjour williamss,

Le problème vient je pense des formules avec MAX et SOMMEPROD de votre 2ème tableau.

Vous allez jusqu'à la ligne 65536 !!! C'est rédhibitoire.

Traitez le tableau en utilisant un nom défini dynamiquement.

A+

 

[williamss]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonjour williamss,

Le problème vient je pense des formules avec MAX et SOMMEPROD de votre 2ème tableau.

Vous allez jusqu'à la ligne 65536 !!! C'est rédhibitoire.

Traitez le tableau en utilisant un nom défini dynamiquement.

A+

Bonjour Job,

J'ai tenté ce dont vous me conseillez en nommant les plages utilisés comme AI5:AI65536 par DATE mais voilà qu'il y a toujours le même problème qui peut-être semble venir du premier tableau.

merci

Williams

 

[Chris24]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonjour

Ce que tu as fait n'est pas un nom dynamique

exemple: Formule pour nommer dynamiquement la colonne AI

Insertion , Nom, définir

Nom = DATE
Formule= DECALER(Feuil1!$AI$3;;;NBVAL(Feuil1!$AI:$AI)-1)

Dans tes formules remplacer la référence "AI:AI" par DATE

Cordialement

 

[job75]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Salut Chris24,

Oui, mais ici c'est =DECALER(Feuil1!$AI$5;;;NBVAL(Feuil1!$AI:$AI)-1)

A+

 

[Chris24]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonjour job75

Effectivement, je n'ai pas vu que les cellules étaient fusionnées sur 3 lignes

 

[williamss]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonjour

Ce que tu as fait n'est pas un nom dynamique

exemple: Formule pour nommer dynamiquement la colonne AI

Insertion , Nom, définir

Nom = DATE
Formule= DECALER(Feuil1!$AI$3;;;NBVAL(Feuil1!$AI:$AI)-1)

Dans tes formules remplacer la référence "AI:AI" par DATE

Cordialement

Bonsoir Chris24,

Ok, alors au Gestionnaire des Noms j'ai remplacé les plages que j'avais sélectionnées et nommées pour chaque formules (comme AI:AI ou bien L:L etc) par des formules comme celles que tu as as données (DECALER(Feuil1!$AI$5;;;NBVAL(Feuil1!$AI:$AI)-1) ou bien DECALER(Feuil1!$L$5;;;NBVAL(Feuil1!$L:$L)-1) , etc). Mais malheureusement cela ne veut toujours pas enregistrer le fichier après avoir tiré la ligne du 1er tableau

Quel est donc le problème ??

Merci

Williams

 

[Chris24]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonsoir Williamss

As-tu essayé de mettre calcul sur ordre et décoché Recalcul avant l'enregistrement

actuellement tu as 127106 formules dont un nombre non négligeable de matricielles très gourmandes en ressources

Bonne soirée

 

[williamss]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonsoir Williamss

As-tu essayé de mettre calcul sur ordre et décoché Recalcul avant l'enregistrement

actuellement tu as 127106 formules dont un nombre non négligeable de matricielles très gourmandes en ressources

Bonne soirée

Même en mettant en mode manuel cela n'enregistre pas

La seul solution pour l'instant que j'ai trouvé c'est en faisant que le 1er tableau n'ai plus de formule mais que les valeurs.

Donc si quelqu'un a une bonne idée n'hésitez pas à me le dire.

Williams

 

[chris]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonjour

Sur des cas similaires (planning qui s'enrichit au fil du temps, j'ai mis un code qui remplace les formules des n premières lignes par leur valeurs.

Dans la mesure où on ne revint plus sur les données passées on peut donc valoriser en dur et alléger ainsi les formules.

Prévoir de le faire à l'ouverture en indiquant la date où ce code à tourné dans une cellule afin de ne pas le relancer n fois le même jour.

 

[williamss]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonjour

Sur des cas similaires (planning qui s'enrichit au fil du temps, j'ai mis un code qui remplace les formules des n premières lignes par leur valeurs.

Dans la mesure où on ne revint plus sur les données passées on peut donc valoriser en dur et alléger ainsi les formules.

Prévoir de le faire à l'ouverture en indiquant la date où ce code à tourné dans une cellule afin de ne pas le relancer n fois le même jour.

C'est pour cela que je crois bien ne pas avoir le choix de faire comme ce que j'ai dis à l'avant dernier message.

Williams

 

[job75]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonjour williams, Chris24, Chris,

Voyez ce fichier :

Document Cjoint

Edit : chez moi le fichier ne s'ouvre pas correctement sur le lien, il faut "Enregistrer la cible du lien".

Pour le 1er tableau :

- placez cette macro dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ncol%, r As Range, t, s, ub%, rest(), i&, j%, dat$, h$
ncol = 34 'colonnes C à AJ
Set r = Intersect(Target, Range("A5:A" & Rows.Count), Me.UsedRange)
If r Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each r In r.Areas
  t = r.Resize(, 2) 'au moins 2 éléments
  ReDim rest(1 To UBound(t), 1 To ncol)
  For i = 1 To UBound(t)
    If t(i, 1) <> "" Then
      s = Split(t(i, 1), ",")
      ub = UBound(s) + 2
      For j = 2 To IIf(ub < ncol - 2, ub, ncol - 2) 'colonnes D à AH
        rest(i, j) = s(j - 2)
      Next j
      If rest(i, 9) <> "" Then rest(i, 9) = Val(rest(i, 9)) / 100 'colonne K
      If rest(i, 11) <> "" Then rest(i, 11) = Val(rest(i, 11)) / 100 'colonne M
      If rest(i, 26) <> "" Then rest(i, 26) = 22.5 * Val(rest(i, 26)) 'colonne AB
      dat = rest(i, 5) & "/" & rest(i, 4) & "/" & rest(i, 2)
      h = rest(i, 6) & ":" & rest(i, 7)
      rest(i, 1) = " " 'pour ne pas laisser la cellule vide
      If IsDate(dat) And IsDate(h) Then
        rest(i, 1) = CDate(dat) + CDate(h)
        rest(i, ncol - 1) = CDate(dat): rest(i, ncol) = CDate(h)
      End If
    End If
  Next i
  r(1, 3).Resize(UBound(rest), ncol) = rest
Next r
'---nom défini utilisé par la fonction Col (Module1)---
Set r = Range("A" & Rows.Count).End(xlUp)
If r.Row < 5 Then Set r = [A5]
ThisWorkbook.Names.Add "DL", 0 'force le recalcul
ThisWorkbook.Names.Add "DL", r.Row
Application.Calculation = xlCalculationAutomatic
End Sub

- copier-coller la colonne A sur elle-même : les formules sont supprimées et remplacées par les valeurs.

Sur le 2ème tableau :

- les formules utilisent cette fonction VBA [Edit] avec 2 arguments :

Function Col(r As Range, DL&) As Range
Set Col = r.Resize(DL - r.Row + 1)
End Function

qui définit dynamiquement les colonnes du 1er tableau grâce au nom DL.

Le temps de calcul des formules du 2ème tableau reste élevé, mais il n'y a plus de problème d'enregistrement.

A+

 

[job75]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Re,

Complément avec la mise en place des bordures du 1er tableau :

Document Cjoint

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ncol%, r As Range, t, s, ub%, rest(), i&, j%, dat$, h$
ncol = 34 'colonnes C à AJ
Set r = Intersect(Target, Range("A5:A" & Rows.Count), Me.UsedRange)
If r Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each r In r.Areas
  t = r.Resize(, 2) 'au moins 2 éléments
  ReDim rest(1 To UBound(t), 1 To ncol)
  For i = 1 To UBound(t)
    If t(i, 1) <> "" Then
      s = Split(t(i, 1), ",")
      ub = UBound(s) + 2
      For j = 2 To IIf(ub < ncol - 2, ub, ncol - 2) 'colonnes D à AH
        rest(i, j) = s(j - 2)
      Next j
      If rest(i, 9) <> "" Then rest(i, 9) = Val(rest(i, 9)) / 100 'colonne K
      If rest(i, 11) <> "" Then rest(i, 11) = Val(rest(i, 11)) / 100 'colonne M
      If rest(i, 26) <> "" Then rest(i, 26) = 22.5 * Val(rest(i, 26)) 'colonne AB
      dat = rest(i, 5) & "/" & rest(i, 4) & "/" & rest(i, 2)
      h = rest(i, 6) & ":" & rest(i, 7)
      rest(i, 1) = " " 'pour ne pas laisser la cellule vide
      If IsDate(dat) And IsDate(h) Then
        rest(i, 1) = CDate(dat) + CDate(h)
        rest(i, ncol - 1) = CDate(dat): rest(i, ncol) = CDate(h)
      End If
    End If
  Next i
  '---restitution et bordures---
  r(1, 3).Resize(UBound(rest), ncol) = rest
  r.Columns(3).Resize(, ncol).Borders.Weight = xlThin
  If r.Row = 5 Then r(1, 3).Resize(, ncol).Borders(xlEdgeTop).Weight = xlThick
  r.Columns(3).Borders(xlEdgeLeft).Weight = xlThick
  r.Columns(3).Borders(xlEdgeRight).Weight = xlThick
  r.Columns(ncol + 1).Borders(xlEdgeLeft).Weight = xlThick
  r.Columns(ncol + 1).Borders(xlEdgeRight).Weight = xlThick
  r.Columns(ncol + 2).Borders(xlEdgeRight).Weight = xlThick
Next r
'---nom défini utilisé par la fonction Col (Module1)---
Set r = Range("A" & Rows.Count).End(xlUp)
If r.Row < 5 Then Set r = [A5]
ThisWorkbook.Names.Add "DL", 0 'force le recalcul
ThisWorkbook.Names.Add "DL", r.Row
'---suppression des lignes superflues et actualisation de la barre de défilement---
Range("C" & r.Row + 1 & ":C" & Rows.Count).Resize(, ncol).Delete xlUp
With Me.UsedRange: End With
Application.Calculation = xlCalculationAutomatic
End Sub

Bonne nuit.

 

[job75]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Bonjour le fil, le forum,

Modification des 2 posts précédents.

Le recalcul de la fonction Col ne se faisait pas, je lui ai ajouté un 2ème argument : le nom défini DL.

Bonne journée.

 

[job75]

Re : Fichier qui semble devenir trop lourd pour être enregistré

Re,

Durées d'exécution du 2ème fichier chez moi sur Win 8 - Excel 2013 :

- avec 88 lignes de formules dans le 2ème tableau => 9 secondes

- avec 365 lignes de formules => 37 secondes, c'est proportionnel.

A+