Microsoft 365 Fichier Excel trop lourd (transformer mes formules en macro?)

Thomexcel

XLDnaute Nouveau
Bonjour à tous,

J'ai besoin de faire une analyse hebdomadaire de suivi de commande à partir de deux fichiers Excel "résultat1" et "résultat2" (les fichiers "résultat1" et "résultat2" peuvent avoir un nombre de lignes différent de semaine en semaine)

J'ai créé un fichier ou je n'ai qu’à faire un copié-collé des fichiers reçus et l'analyse se fait automatiquement.
Cependant, mon fichier actuel est très lourd et met beaucoup de temps à charger à la moindre modification.

Est-il possible de l'alléger à l'aide de macro ou autre solution?

Les deux fichiers reçus chaque semaine sont :
-"résultat1" comprenant un ensemble de données, dont les commandes que les magasins se sont engagés à faire en quantités par produit
-"résultat2" trois jours après, la mise à jour de ce fichier.

Ce que je dois faire :
Pour le pays (total) :
-Le pourcentage d'engagement de commande par "RGDF" (pas besoin de la quantité commandée)
-L'évolution des engagements de commandes entre le fichier "résultat1" et "résultat2"

Par magasins:
-Le pourcentage d'engagement de commande par "RGDF" (pas besoin de la quantité commandée)
-L'évolution des engagements de commandes entre le fichier "résultat1" et "résultat2"

Mon fichier comprend :
-une feuille "data1" ou je colle le fichier "résultat1"
-une feuille "data2" ou je colle le fichier "résultat2"
-une feuille "compilation_data1&2"
-une feuille "Rapport"

Dans la feuille "compilation_data1&2" j'ai :
En première colonne, les numéros RGDF (='data1'!G3)
En deuxième colonne, les numéros produits (='data1!J3)

-Pour le total "data1":
1.1 Une colonne "total engagement" avec la formule =NB_SI_COULEUR(H5:CY5;14348258)
1.2 Une colonne "pourcentage" avec la formule =C5/32 (32 équivaut au nombre de magasins)

-Pour le total "data2" :
1.1 Une colonne "total engagement" avec la formule =NB_SI_COULEUR(H5:CY5;9359529)
1.2 Une colonne "pourcentage" avec la formule =E5/32 (32 équivaut au nombre de magasins)

-Évolution entre "data1"et "data2" avec la formule =D5-F5

-Par magasins (de la colonne H à la colonne CY)
1.1 Une colonne "engagement par produit" pour les data1 avec la formule =SI.(INDEX(EQUIV)>0;1;0) m'indique si le magasin à passer commande dans la feuille "data1" par produit
.1' mise en forme des cellules en couleur 14348258 si >0
1.2 Une colonne "engagement par produit" pour les data2 avec la formule =SI.(INDEX(EQUIV)>0;1;0) m'indique si le magasin à passer commande dans la feuille "data2" par produit
.2' mise en forme des cellules en couleur 9359529 si >0
1.3 Une colonne "Evolution" avec la formule (=colonne"engagement1"-colonne"engagement2") m'indique l'évolution entre le fichier "résultat1" et "résultat2"

Dans la feuille "Rapport" j'ai :
En première colonne, les numéros RGDF avec la formule =TRIER(UNIQUE('compilation_data1&2'!A5:A500))
En deuxième colonne, le total de produits par RGDF avec la formule =NB.SI('compilation_data1&2'!$A$5:$A$500;A5)
En troisième colonne, le total de produits par RGDF par magasins avec la formule =B5*32 (32 équivaut au nombre de magasin)

-Pour le total "data1"
1.1 une colonne "total engagement par RGDF" avec la formule =SOMME.SI('compilation_data1&2'!$A$5:$A$500;A6;'compilation_data1&2'!$C$5:$C$500)
1.2 une colonne "pourcentage de magasin engagé par RGDF" avec la formule =D6/C6

-Pour le total "data2"
1.1 une colonne "total engagement par RGDF" avec la formule =SOMME.SI('compilation_data1&2'!$A$5:$A$500;A6;'compilation_data1&2'!$E$5:$E$500)
1.2 une colonne "pourcentage de magasin engagé par RGDF" avec la formule =F6/C6

-Évolution entre "data1"et "data2" avec la formule =G6-E6

-Par magasins (de la colonne I à la colonne FL)
1.1 une colonne "engagement par RGDF" pour les data1 avec la formule =SOMME.SI('compilation_data1&2'!$A$5:$A$500;A6;'compilation_data1&2'!$H$5:$H$500)
1.2 une colonne "pourcentage d'engagement par RGDF" pour les data1 avec la formule =I6/B6

2.1 une colonne "engagement par RGDF" pour les data2 avec la formule =SOMME.SI('compilation_data1&2'!$A$5:$A$500;A6;'compilation_data1&2'!$I$5:$I$500)
2.2 une colonne "pourcentage d'engagement par RGDF" pour les data1 avec la formule =K6/B6

3.1 une colonne "Evolution" avec la formule =L6-J6 m'indique l'évolution entre le fichier "résultat1" et "résultat2"

En copie le fichier (il n'est pas complet, j'ai du supprimer énormément de ligne pour pouvoir l'envoyer)
PS: je travail sur Mac

D'avance merci pour votre aide.

Thomas
 

Pièces jointes

  • Resultat AFT-Macro3.xlsm.zip
    634.6 KB · Affichages: 9
Solution
Bonjour,

Votre fichier comporte des liaisons qui ne peuvent être résolues sur nos machines. Notamment votre xlam qui comporte la fonction NB_SI_COULEUR. Les erreurs se propageant....hic !

Formule qui dans votre exemple (C5 de Feuille compilation1&2) et si je ne me trompes pas pourrait être remplacée par :
=NB.SI.ENS($H5:$CY5;">0";$H$3:$CY$3;"Data1")
soit le nombre de cellules supérieures à 0 des colonnes contenant "Data1" sur la ligne 3
Et pour la colonne E:
NB.SI.ENS($H5:$CY5;">0";$H$3:$CY$3;"Data2")

Déjà cela devrait vous faire gagner du temps.

Si votre version excel vous donne accès à PowerQuery je vous recommanderai de faire vos 'compilation1&2' par requête powerquery, sans affichage en feuille des données de...

Hasco

XLDnaute Barbatruc
Bonjour,

Votre fichier comporte des liaisons qui ne peuvent être résolues sur nos machines. Notamment votre xlam qui comporte la fonction NB_SI_COULEUR. Les erreurs se propageant....hic !

Formule qui dans votre exemple (C5 de Feuille compilation1&2) et si je ne me trompes pas pourrait être remplacée par :
=NB.SI.ENS($H5:$CY5;">0";$H$3:$CY$3;"Data1")
soit le nombre de cellules supérieures à 0 des colonnes contenant "Data1" sur la ligne 3
Et pour la colonne E:
NB.SI.ENS($H5:$CY5;">0";$H$3:$CY$3;"Data2")

Déjà cela devrait vous faire gagner du temps.

Si votre version excel vous donne accès à PowerQuery je vous recommanderai de faire vos 'compilation1&2' par requête powerquery, sans affichage en feuille des données de data1 et data 2.

cordialement
 
Dernière édition:

Thomexcel

XLDnaute Nouveau
Bonjour,

Vous êtes sous MAC ?
Votre fichier comporte des liaisons qui ne peuvent être résolues sur nos machines. Notamment votre xlam qui comporte la fonction NB_SI_COULEUR. Les erreurs se propageant....hic !

Si votre version excel vous donne accès à PowerQuery je vous recommanderai de faire vos 'compilation1&2' par requête powerquery, sans affichage en feuille des données de data1 et data 2.

cordialement

Bonjour Hasco,

Merci pour votre réponse,
Oui, je suis sous mac.

J'ai accès à PowerQuery mais ne l'ai encore jamais utilisé, je vais essayer cette approche ce wk.

Je vais également réfléchir à une autre méthode afin de calculer le nombre d'engagements total par "data" au lieu de la fonction NB_SI_COULEUR (j'ai dû installer le pack de fonction XLP pour cette fonction) .

J'espère qu'en utilisant PowerQuery cela allégera mon fichier.
Je vous tiens informé

Cordialement,
 

Hasco

XLDnaute Barbatruc
Je vais également réfléchir à une autre méthode afin de calculer le nombre d'engagements total par "data" au lieu de la fonction NB_SI_COULEUR (j'ai dû installer le pack de fonction XLP pour cette fonction) .
Re,

Je vous ai donné 2 formules pour ça !

Il suffit pour ça de calculer le nombre de cellule répondant à vos conditions de mise en forme conditionnelles.
Ce que les formules données plus font me semble-t-il. Non ?

Cordialement
 

Thomexcel

XLDnaute Nouveau
Re,

Je vous ai donné 2 formules pour ça !

Il suffit pour ça de calculer le nombre de cellule répondant à vos conditions de mise en forme conditionnelles.
Ce que les formules données plus font me semble-t-il. Non ?

Cordialement
Re,

Oui de fait, je n'avais pas vu 😅
Et elles fonctionnent, j'ai pu donc supprimer la mise en forme sous condition ce qui allège fortement le fichier.

Plus qu'à tester le PowerQuery

Merci,
 

Discussions similaires

Réponses
12
Affichages
694

Statistiques des forums

Discussions
299 915
Messages
1 980 049
Membres
206 965
dernier inscrit
Mithanne