Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2021 Poids fichier - formules -> VBA

La_Mouette

XLDnaute Nouveau
Bonjour à tous (un ancien membre sur le retour),

J'ai un fichier avec des rechercheH, index equiv, répétés (c'est l'objectif en tout cas), sur des dizaines de milliers de ligne dans un tableau.
J'ai essayé d'épurer au maximum, néanmoins je bute devant le problème suivant : le fichier ne peut être utilisé in fine et finit par planter. Pour le moment je fait tous les calculs sans macro.

Avant de rentrer potentiellement dans le dur, j'ai une question : est-il préférable de faire tous ces calculs via une macro VBA, pour ne venir afficher qu'à la fin les résultats ?
Ou finalement, le temps pris par les formules excel, retranscrites en VBA par mes soins, sera le même ? Et la difficulté du fichier à tourner la même ?
 
Trier par date Trier par votes

Phil69970

XLDnaute Barbatruc
Bonjour @La_Mouette

J'ai pas vu ton fichier représentatif.

C'est quoi représentatif ?

- représentatif, même organisation des lignes et des colonnes, mêmes libellés, même nom des TS, mêmes noms de feuilles...
- anonymisé, pas de données personnelles réelles tels nom, n° sécu, adresse ... remplacé par Nom1, Nom2 etc ....
- simplifié, une quinzaine de lignes reproduisant l'ensemble des différents cas envisageables (Avec le résultat souhaité éventuellement)
- ordre de grandeur de tes données (10 000 lignes ou 500 000 lignes .....)

Et bien sur les macros/formules que tu as dans ton fichier ......

Bonne lecture
 
Vote positif 0 Vote négatif

TooFatBoy

XLDnaute Barbatruc
Bonjour,

Je dirais que oui, vu les problèmes que tu as, une macro serait mieux même si elle risque d'être éventuellement un chouillat plus lente que des formules.
 
Dernière édition:
Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @La_Mouette, bienvenue sur XLD et bonjour à tous ,

Quelquefois un simple tri des données source ou des tables de recherche ou encore une colonne auxiliaire permet d'accélérer considérablement vitesse de calcul et recalcul. Mais sans classeur représentatif, on parle dans le vide .
 
Vote positif 0 Vote négatif

La_Mouette

XLDnaute Nouveau
Merci à tous pour vos réponses.
Phil --> je prépare un fichier représentatif simplifié.
JHA --> j'utilise en effet Power Query dans le fichier, pour importer des données d'une BDD, mais je n'avais pas pensé à faire directement des calculs dedans. Je vais regarder ça.

mapomme --> j'avais un peu cherché sur ce qui ralentissait un fichier (fonctions volatiles type DECALER, ou actions volatiles type "insérer une ligne" (qui en effet fait planter le fichier), calcul manuel plutôt qu'auto, etc), j'ai essayé de réduire le nombre de calculs. Mais le fichier représentatif va vous aider.
A venir
 
Vote positif 0 Vote négatif

La_Mouette

XLDnaute Nouveau
Voici un fichier simplifié et anonymisé au possible, que j'ai essayé de rendre clair et représentatif donc.
Merci pour vos réponses, qui dépassent clairement le cadre de ce fichier, à la question suivante :

"Un trop grand nombre de formules (plus marqué encore avec des formules complexes) semble ralentir considérablement (voire trop pour le faire tourner) un fichier excel ; dans ce cas, vaut-il mieux aller faire les calculs via VBA ou pas ?"
 

Pièces jointes

  • Classeur1_représentatif (excel-downloads).xlsx
    28.7 KB · Affichages: 12
Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @La_Mouette et bienvenue sur XLD ,
Bonjour aux autres ,

Vos formules ne sont pas extraordinaires (au niveau complexité) selon moi.

J'ai testé le calcul de vos formules sur les colonnes E à J pour 175 000 lignes :
  1. sur une plage ordinaire (par copie des formules de la ligne 1 sur les autres)
  2. puis sur un tableau structuré (on complète la première ligne du TS et le TS propage lui-même les formules)
Dans les deux cas sur ma bécanes, les durées des calculs sont d'environ 2,5 s. Ce qui est raisonnable pour
6 175 000 cellules du moins me semble-t-il.
 

Pièces jointes

  • La mouette-Classeur1_représentatif-v1.xlsm
    31.4 KB · Affichages: 5
Dernière édition:
Vote positif 0 Vote négatif

La_Mouette

XLDnaute Nouveau
Bonsoir mapomme, merci pour ton investissement.
En effet le fichier semble poser beaucoup moins de problèmes que mon fichier originel (par exemple je fais une "action volatile" comme supprimer une ligne entière d'une feuille, ça ne se passe pas immédiatement mais plutôt rapidement).
Peut-être ai-je trop simplifié le fichier qui contient normalement beaucoup plus de choses en troisième feuille (mais rien d'insurmontable non plus, quelques fonctions recherches sur quelques colonnes etc).

Néanmoins en ajoutant 15 colonnes comme nécessaires, avec ces formules, je sens que ça commence à ramer sérieusement, et en ajoutant le reste du fichier normal, sans doute que je reviendrai à mon problème originel.

Je vais creuser pour voir ce qui ralentissait le plus. Merci encore.


Si quelqu'un a une réponse plus générale à ma question formules en dur dans le fichier ou plutôt en vba (peut-être via dictionnaires ?)
Je vais également creuser la solution powerquery.
 
Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,

Oui, avec VBA ou PQ, on peut rendre le classeur plus réactif :
  • en supprimant les formules pénalisantes ou volatiles pour les remplacer par des valeurs calculées par macro
  • en limitant les MFC sur des grandes plages
  • en limitant les macros évènementielles qui peuvent conduire à des recalculs des feuilles du classeur.
  • etc.
Mais tout dépend des traitements ou formules du classeur.

On pourrait aussi envisager de passer par Access ou un autre système gestion de base de données.
 
Vote positif 0 Vote négatif

La_Mouette

XLDnaute Nouveau
Bonjour Staple,
J'ai bien la dernière version d'excel, il faudrait que j'envisage la formule rechercheX ?
Je ne m'y suis juste pas encore mis, par confort de connaître les autres.^^
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

L
  • Question
XL 2016 Document Excel VBA finalisé - Besoin d'un avis
Réponses
10
Affichages
615
Phil69970
S
  • Question
XL 2016 Formules matricielles max si deux conditions en VBA
2 3
Réponses
44
Affichages
3 K
safranien
S
J
  • Question
XL 2016 Mise en forme conditionnelle en vert de noms, si ces noms figurent dans une liste de noms d'un autre onglet (formules dans MFormCond, ou VBA)
Réponses
5
Affichages
869
sylvanu
S
  • Question
Autres Lourdeur du fichier excel et lenteur du traitement macro : comment identifier les éléments générant ces lenteurs et lourdeurs et comment optimiser ?
Réponses
9
Affichages
5 K
TooFatBoy
T
  • Question
Autres Alternative à formules matricielles complexes (fichier lent)
Réponses
12
Affichages
2 K
CISCO
K
  • Question
XL 2013 VBA - EXCEL - Aide pour simplifier mon fichier
Réponses
0
Affichages
1 K
Khalidoune
K
R
  • Question
XL 2010 Poids du fichier
Réponses
5
Affichages
572
bof
B
S
  • Résolu(e)
Microsoft 365 Problème de transfert de données
Réponses
1
Affichages
1 K
SCorbeil
S
S
  • Question
XL 2013 Alléger une feuille Excel comprenant (beaucoup) des formules
Réponses
5
Affichages
2 K
Dranreb
H
  • Question
XL 2013 VBA FORMULES calcul en auto
Réponses
7
Affichages
842
CPk
C
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…