Alléger un fichier

[Sarahlala]

Bonjour le forum,

J'aurai besoin de de vos avis et conseil sur ce point: je souhaiterai savoir s'il est possible de créer des liens entre plusieurs fichiers excel? Je vous explique mon cas de figure. J'avais posté il y a quelques semaines un message afin de savoir comment réduire mon fichier excel qui plante tout le temps tellement il est énorme, sans réel succès.

D'où mon idée: puis je avoir un fichier par mois puis un autre qui regrouperait toutes les données dans différents fichiers MOIS dans un TCD??? De cette façon, le fichier récapitulatif serait moins lourd et les données pourraient s'actualiser mois après mois lorsque mes collègues ajouteraient des mois (donc des fichiers) au dossier.

Vos avis donc donc les bienvenus!

Bonne journée!

 

[Misange]

Re : Alléger un fichier

"En faite je fais 4 TCD car j'ai 2 types de livraisons. Donc pour la livraison A j'ai un TCD de l'année dernière et un de cette année car visuellement c'est simple à comparer. Pour le type de livraison B j'ai un autre TCD pour l'année dernière et un autre pour cette année. Le TCD de l'année dernière ne bouge pas mais je veux pouvoir choisir quel(s) mois je compare donc j'ai laissé le TCD et non le tableau."

Si tu faisais un seul TCD, avec en champ de page la livraison et en faisant apparaitre côte à côte les données de l'année et celles de l'année dernière, la comparaison se ferait bien plus facilement, et surtout tu économiserais grandement des ressources !
Le plus simple dans ce cas c'est de ne faire qu'une seule base de données, surtout pas une par année et de la mettre à jour par macro qui ira faire ce qui est fait par formule aujourd'hui
Par exemple dans le fichier joint, la feuille "par macro" est une copie de la feuille précédente mais sans les formules. Pour la mettre à jour, tu cliques sur le bouton. Les résultats sont calculés dans la macro. C'est très rapide et du coup ton fichier ne contient plus de formule.

 

[Sarahlala]

Re : Alléger un fichier

Bonjour,

Misange merci pour ta réponse et le fichier! Je comprends le principe et je vais essayer de faire de même. Par contre je ne sais pas du tout comment il faut faire pour transformer mes formules en "langage macro" ^^ Si tu as des astuces je suis preneuse.

Autre question, si la feuille 'avec macro' est la même que celle avec les formules mais sans celles-ci, je devrais tout de même refaire mes TCD c'est ça?

Merci par avance,
S.

 

[Misange]

Re : Alléger un fichier

Bonjour

Pour ce qui est des formules mises en macro c'est là :

Sub miseajour()
Dim NbLign As Long
Dim i As Long
Sheets("par macro").Activate
NbLign = Range("A1").End(xlDown).Row
For i = 2 To NbLign
If Range("AS" & i) = "" Then
    Range("AX" & i) = DateSerial(Left(Range("A" & i), 4), Mid(Range("A" & i), 5, 2), Right(Range("A" & i), 2))
    Range("As" & i) = Format(Range("AX" & i), "mmmm")
    Range("AT" & i) = WorksheetFunction.SumIfs(Range("Price"), Range("entrepôts"), Range("C" & i), Range("FROM"), Range("G" & i))
    Range("AU" & i) = Application.WorksheetFunction.Index(Range("place"), Application.WorksheetFunction.Match(Range("AF" & i), Range("from"), 0))
    Range("AV" & i) = IIf(Application.WorksheetFunction.CountIf(Range("D2:D" & i), Range("D" & i)) > 1, "", 1)
    Range("AW" & i) = Application.WorksheetFunction.Index(Range("catégorie"), Application.WorksheetFunction.Match(Range("J" & i), Range("produit"), 0))
End If
Next i

End Sub

c'est le code que tu trouves dans l'éditeur VBA.
Si tu n'as jamais fait de macro, il est possible que tu n'aies même pas l'onglet développeur affiché. Si c'est le cas va dans fichier/options/personnaliser le ruban et coches l'onglet développeur.
Dans cet onglet clique sur le bouton tout à gauche ce qui ouvre l'éditeur de macro.
Pour la suite, je te propose un peu de lecture de base ici
Ce lien n'existe plus

Pour transformer une formule excel en macro, il y a deux solutions.
La mauvaise hélas (car c'est outil est par ailleurs génial) c'est de lancer l'enregistreur de macro. C'est la mauvaise car l'enregistreur écrit la formule dans la cellule et c'est justement ce qu'on veut éviter en général quand on utilise une macro à la place d'une formule. Il n'y a en effet aucun intérêt à écrire par macro =A1*2 dans une cellule : on ne gagne absolument rien en temps (en fait on perd !) par rapport au fait d'écrire à la main la formule dans la cellule.
Ce qu'on veut c'est que la macro fasse le calcul et mette seulement le résultat dans la cellule.

L'enregistreur est quand même bien utile : comme le VBA ne cause qu'engliche, il faut connaître le nom anglais des formules qu'on veut utiliser. Par exemple somme.si.ens en anglais c'est sumifs (plus court !)
Comme ça ne s'invente pas, une façon très simple c'est de sélectionner une cellule, de lancer l'enregistreur de macro et d'écrire la formule contenant le terme à traduire, d'arrêter l'enregistreur puis de regarder ce qu'il a écrit.

Après il faut écrire ça vraiment en VBA. Ca ne s'invente pas complètement non plus... d'où la nécessité d'avoir quelques bases et d'avoir sous la main un forum sympa où venir se faire aider .

"si la feuille 'avec macro' est la même que celle avec les formules mais sans celles-ci, je devrais tout de même refaire mes TCD c'est ça?"
Non ! le TCD se fiche de savoir si les valeurs dans les cellules sont le résultat d'une formule ou si ce sont des valeurs écrites en dur donc pas besoin de refaire.

 

[Sarahlala]

Re : Alléger un fichier

Merci pour les informations, je vais fouiller ça dés que j'en aurai le temps!! Une bonne chose à apprendre en tout cas.
Je reviendrai sur le forum quand je coincerai!!!

 

[Herdet]

Re : Alléger un fichier

Bonjour
If Range("AS" & i) = "" Then
Range("AX" & i) = DateSerial(Left(Range("A" & i), 4), Mid(Range("A" & i), 5, 2), Right(Range("A" & i), 2))
Range("As" & i) = Format(Range("AX" & i), "mmmm")
.....
[/CODE]

Bonsoir Sarahlala et Misange,
Une petite correction au code de Misange : If Range("$A" & i) <> "" Then

Pour 31566 lignes à traiter, j'ai bien peur que l'écriture avec Range dans 6 * 31566 cellules soit beaucoup trop lente.
En rajoutant les lignes ci-après, ça devrait un peu accélérer le traitement :
en tête :
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
à la fin :
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Sinon, une autre solution sûrement plus rapide en passant par un tableau VBA et un transfert unique sur les 31556 lignes (ou plus) :

Sub miseajour_RD2()
   Dim NbLign, i, j As Long
   Dim tRESULT() As Variant
   
   Sheets("par macro").Activate
   Application.ScreenUpdating = False
   NbLign = Range("A1").End(xlDown).Row
   j = 6
   ReDim tRESULT(1 To NbLign - 1, 1 To j)
   For i = 2 To NbLign
      If Range("A" & i) <> "" Then        ' RD
         tRESULT(i - 1, 6) = DateSerial(Left(Range("$A" & i), 4), Mid(Range("$A" & i), 5, 2), Right(Range("$A" & i), 2))
         tRESULT(i - 1, 1) = Format(tRESULT(i - 1, 6), "mmmm")
         tRESULT(i - 1, 2) = WorksheetFunction.SumIfs(Range("Price"), Range("entrepôts"), Range("$C" & i), Range("FROM"), Range("$G" & i))
         tRESULT(i - 1, 3) = Application.WorksheetFunction.Index(Range("place"), Application.WorksheetFunction.Match(Range("$AF" & i), Range("from"), 0))
         tRESULT(i - 1, 4) = IIf(Application.WorksheetFunction.CountIf(Range("$D2:$D" & i), Range("$D" & i)) > 1, "", 1)
         tRESULT(i - 1, 5) = Application.WorksheetFunction.Index(Range("catégorie"), Application.WorksheetFunction.Match(Range("$J" & i), Range("produit"), 0))
      End If
   Next i
   
   Range("$AS2").Resize(UBound(tRESULT), j).Value = tRESULT
   Application.ScreenUpdating = True
End Sub

A plus
Robert