Je cherche à optimiser mon fichier Budget Familial, qui devient très lent à cause de nombreuses formules matricielles utilisées dans les feuilles :
« BA Prévisionnel »
« BA Réalisé »
Les feuilles mensuelles (« Janvier », « Février », etc.)
Optimisation des formules
Les formules actuelles ralentissent fortement le fichier. Je me demande s’il est possible de les remplacer par des alternatives plus légères.
Centralisation des graphiques
Chaque feuille mensuelle contient ses propres graphiques, ce qui alourdit encore plus le classeur. mon Objectif est de regrouper tous les graphiques dans une seule feuille de synthèse, avec un menu déroulant permettant de sélectionner le mois affiché.
Si vous avez des idées ou des pistes d’optimisation, je suis preneur !
Pourquoi vous posez votre question sur un autre fil et que vous ouvrez un ici ?
si vous regardez dans la colonne L des onglets janvier et février, pourquoi avez-vu mis une formule matricielle.
Si je ne me trompe avec cette formule matricielle n’a pas d’utilité. Essayez déjà en L28 → =INDEX($C$20:$G$426;EQUIV($J28;$C$20:$C$426;);5)
Puis recopier vers le bas
Si ok, vous pourriez déjà corriger sur les autres feuilles mois
Après les formules Sommeprod sont d’ordre matricielles et cela fait toujours ramer les calculs. Essayez avec Somme.si.ens en colonne E à P dans l’onglet BA réalisé
J’ai ouvert ce nouveau fil après que @Cow18 ait mentionné l’impact des formules matricielles sur les performances d’Excel. Pour ne pas mélanger les discussions, j’ai préféré séparer les sujets et me concentrer ici sur l’optimisation des formules, la centralisation des graphiques dans une feuille Synthèse et l’organisation des données du classeur.
Pour la formule proposée, elle fonctionne parfaitement et simplifie bien les calculs !
J’aimerais maintenant aller plus loin en regroupant ces calculs dans une feuille Synthèse.
L’idée serait de pouvoir choisir un mois via une liste déroulante, et que les calculs ainsi que les graphiques liés s’adaptent automatiquement.
Aurais-tu une idée pour adapter cette formule du tableau J27:M47 ainsi que les formules dans les tableaux dans les colonnes AA:AI et AK:AN afin qu’elles fonctionnent dynamiquement selon le mois sélectionné ?
je n’a plus réagi parce que j’étais surpris par le fichier. A mon avis, il est trop compliqué, un tableau avec tous les données de plusieurs moins/années suffit et puis une feuille dans laquelle on choisit le mois/année est fini. La colonne de (par exemple) Janvier est intéressant/pratique
Pour la formule proposée, elle fonctionne parfaitement et simplifie bien les calculs !
Oui et non. Si vous regardez en J34, la valeur en L34 est 0 alors que vous avez bien un montant de 25 eur dans cette rubrique. Si vous voulez que ce soit correct, veillez à ne pas avoir deux fois le même nom dans la colonne C. Exemple : C61 et C95 ont le nom « Investissements ». Du coup la formule prend le montant en G61 au lieu de G65.
Rem : Après vous voulez aller plus loin mais le souci de « râmer » n’est pas résolu. Il reste d’autres formules notamment les Sommeprod. Quid ?
Autre formule que vous pouvez modifier : feuille Janvier en G22, vous utilisez la totalité des lignes de votre feuille. Du coup excel calcule sur 1048756 lignes…
vu que le tableau de la feuille Transaction est au format struturé, mettez ceci en G22 → =SOMME.SI.ENS(TbTransactions[Crédit];TbTransactions[Catégorie];C$20;TbTransactions[Sous-Catégorie];C22;TbTransactions[Mois];$F$2)
Ensuite recopiez vers le bas en colonne G et faire de même sur la feuille Février
Aurais-tu une idée pour adapter cette formule du tableau J27:M47
Par exemple dans votre feuille Synthèse, vous faites une liste déroulante dans une cellule avec le choix du mois et en dessous le tableau J27:M47 qui récupère les infos selon le mois choisi
Sinon comme le souligne Cow18 votre fichier est complexe et pour ma part je me demande ce que cela concerne comme société.
EDIT : Une autre idée, plutôt que d’avoir une feuille par mois, pourquoi ne faites-vous pas une seule feuille « Mois » dont les résultats sont choisis via la liste déroulante en F2 et ensuite faire une feuille qui reprend les données que vous entrez manuellement dans chaque feuille Mois (exemple : le montant en D18)
Cette idée parce que vous avez tellement de formules dans chaque feuille mois que faire une feuille par mois va multiplier le nombre de formules et donc faire ramer encore plus. A réfléchir…
Pour éviter toute confusion, je vais la renommer la sous-catégorie 'investissement" en « Fond d’investissement » et laisser l’autre « INVESTISSEMENT » en tant que catégorie pour être est utilisée dans la formule.
Effectivement, j’aimerais éliminer toutes sources de ralentissement du classeur que se soit les formules SOMMEPROD ou autres matricielles qui produiraient ce problème.
Ta suggestion d’utiliser SOMME.SI.ENS en G22 est excellente ! Ça devrait accélérer le calcul, je vais l’appliquer aux autres feuilles aussi.
Pour répondre à ta question, il s’agit bien d’un budget familial, mais il est assez détaillé pour bien suivre les dépenses et les prévisions de l’année à l’aide d’un tableau de bord bien clair des ressources et les differentes dépenses par catégorie.
L’idée d’avoir une seule feuille « Mois » avec une liste déroulante est très intéressante ! Cela permettrait d’avoir non seulement les graphiques regroupés, mais aussi toutes les données de chaque mois sur une seule feuille, ce qui réduirait significativement la charge de calcul.
Petit point à éclaircir : j’ai remarqué que le graphique en camembert (O1:T22) « Répartition des dépenses » par catégorie ne représente pas les mêmes taux que ceux affichés dans le tableau J28:M47 et son graphique associé. Aurais-tu une idée de l’origine de ce problème ?
Pour répondre à ta question, il s’agit bien d’un budget familial,…
Hum, ok mais c’est tellement complexe que je doute que beaucoup vont vouloir utilisez ce fichier ou alors vous avez l’intention d’en tirer profit en le commercialisant.
Effectivement, j’aimerais éliminer toutes sources de ralentissement du classeur que se soit les formules SOMMEPROD ou autres matricielles qui produiraient ce problème.
Dans la feuille Ba réalisé, en G35 à la place d’utiliser Sommeprod, essayez ceci → =SOMME.SI.ENS(TrsCrédit;TrsFamille;$B35;TrsCatégorie;$C35;TrsSousCatégorie;$D35;TrsMois;E$34)-SOMME.SI.ENS(TrsDébit;TrsFamille;$B35;TrsCatégorie;$C35;TrsSousCatégorie;$D35;TrsMois;E$34)
C’est un peu plus complexe à lire mais on évite la formule matricielle.A vérifier si cela aide.
Petit point à éclaircir : j’ai remarqué que le graphique en camembert (O1:T22) « Répartition des dépenses » par catégorie ne représente pas les mêmes taux que ceux affichés dans le tableau J28:M47 et son graphique associé. Aurais-tu une idée de l’origine de ce problème ?
En fait je ne sais pas comment vous avez réalisé ce graphique mais à votre place je le referais
Sélectionnez J28 à J47 et en utilisant la touche CTRL M28 à M47
Insérer un graphique puis choisis le modèle
via la l’icône Disposition rapide, vous pourriez obtenir ce genre de figure.
pour la ou les feuille Mois. Les formules que vous avez dans la tableau C7 à F13 ne pointent pas au bon endroit. Exemple F11 pointe sur D306 alors qu’elle devrait pointer sur D327. Là on devrait trouver 2750 eur. Actuellement cela fausse les résultats du tableau J28-M47
Vous pourriez par exemple créer des noms pour les cellules F7 à F11. Cela éviterait ce genre d’erreur
Autre exemple : la cellule D7 pointe sur D47. Ne devrait-elle pas pointer sur D18 ?
Le fichier posté fait référence à ces liens externes.
D’autre part cela râme chez moi depuis que vous avez placé les codes.
Dans le code dans la feuille BA Réalisé, pourquoi vous désactivez les événements ?
Cela n’a pas de sens d’autant que vous refaites la même chose dans le code CalculerMoyennesAnnuelles.
Votre code comme ceci
Private Sub Worksheet_Activate()
' Vérifier si le mois sélectionné est "janvier"
If Range("V2").Value = "janvier" Then
' Appeler la procédure pour calculer les moyennes annuelles
CalculerMoyennesAnnuelles
End If
End Sub
NB : attention qu’avec ce code, il est exécuté à chaque fois que vous cliquez sur l’onglet BA Réalisé. Maintenant avec le critère janvier, le code calculmoyennesannuelles ne sera exécuté que durant tout le mois de janvier
Dans le code calcumoyenneannuelles,
vous avez des lignes debug.print, cela ne sert pas une fois votre code fonctionnel. Vous pouvez les supprimer.
L’instruction enableevents ne sert à rien dans votre cas. Vous pouvez la désactiver aussi dans le calculmoyennesannuels.
Il faudrait regarder aussi vos références circulaires. Il y en a dans pas mal de feuilles
Que penses-tu du tableau AA1:AI21, qui permet de comparer les moyennes, ainsi que du graphique associé ?
D’autre part cela râme chez moi depuis que vous avez placé les codes.
En fait, les codes VBA ajoutés font ramer le classeur, je cherche toujours à résoudre ce problème !
Maintenant avec le critère janvier, le code calculmoyennesannuelles ne sera exécuté que durant tout le mois de janvier
pour dire que la moyenne annuelle ne devrait être calculée qu’au début de chaque année au mois de janvier.
Il faudrait regarder aussi vos références circulaires. Il y en a dans pas mal de feuilles
je vais voir comment les résoudre.
Il est créé comment ?
Le tableau AA1:AI21 compare le taux de dépenses de chaque catégorie par rapport au revenu mensuel. Ceci est basé sur les moyennes calculées dans le tableau AK2:AN22 ou dans la feuille « Evaluations ».
