XL 2021 Poids fichier - formules -> VBA

[La_Mouette]

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 ?

 

[Phil69970]

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

 

[TooFatBoy]

Bonjour,

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 ?

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.

 

[JHA]

Bonjour à tous,

Peut-être une approche avec power query, as-tu essayé?

JHA

 

[mapomme]

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 .

 

[La_Mouette]

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

 

[La_Mouette]

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 ?"

 

[mapomme]

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.

 

[La_Mouette]

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.

 

[mapomme]

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.

 

[Staple1600]

Bonsoir le fil

@La_Mouette (bon retour sur XLD )

J'ai un fichier avec des rechercheH, index equiv

laisse supposer que tu ne disposes pas d'Excel 365
Et donc des nouvelles fonctions qui permettent plus de perspectives
(notamment sur un gros volumes de données)
Question: Quelle version d'Excel utilises-tu ?