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

[Thomexcel]

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

 

[Thomexcel]

Re-bonjour,

Serait-il préférable que je segmente ma demande afin qu'elle soit plus digeste?

 

[Hasco]

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

 

[Thomexcel]

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]

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]

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,