XL 2013 Alternative a SUMPRODUCT - Processus de calcul trop long

[elise1990]

Bonjour a tous,

J'ai cree un fichier permettant de regrouper les ventes de l'annee.
Je me suis arrangee pour n'avoir qu'a entrer chaque semaine les quantites vendues et ensuite les autres feuilles se chargeront de faire le calcul sur le mois, le cumul annee, le calcul par categorie...
Le seul probleme est que j'utilise SUMPRODUCT et que le calcul de la feuille peut prendre jusqu'a 30 minutes...
1896 articles differents et ce nombre risque d'augmenter pendant l'annee!!

Je vous joins le fichier en piece jointe (j'ai change les noms par soucis de confidentialite et je ne vous ai laisse qu'une petite liste d'articles pour que le fichier soit assez leger pour vous l'envoyer. Aussi j'ai indique les formules uniquement pour la premiere ligne).
Les cellules en rouge sont celles qui utilisent SUMPRODUCT et pour lesquelles j'aimerais trouver une autre formule evitant le processus de calcul.

Connaissez vous une alternative?

Merci pour votre aide,

Tres bonne journee,

Elise.

 

[Dranreb]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Bonjour.
Où sont les données de base dans votre classeur ? Celle qui ne contiendraient que peu de colonnes, une seule chose par ligne avec une date dedans ?

 

[elise1990]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Bonjour Danreb,

J'ai joint le fichier excel a mon message. (Dossier compresse)
Parvenez vous a l'ouvrir?

 

[Victor21]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Bonjour, Elise, Dranreb.

Je m'immisce
Dranreb veut vous faire comprendre que votre base de données ( Per Week - Units) ne se prête pas facilement à analyse :
Les lignes 4 (dates) et 5 (W5, LY, WS, ∆, %) devraient être en colonne, et non en ligne.

 

[elise1990]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Je viens de comprendre votre message danreb.
Ma base de donnee varient selon les clients. Le format est toujours different.

Elles sont envoyees par semaine, donc j'ai toujours les donnees sur la semaine entire. Je fais ensuite une recherche V selon le code du produit pour avoir le ventes et je copie colle sur mon fichier, dans l'onglet per week-units.

Pour la valeur, de meme si le CA est donne sur la base de donnee et je copie colle dans l'onglet per week- value.

@Victor21: pour moi la date et "W5, LY W5, ∆", % sont en colonnes. Il me semble impossible de les permuter en ligne, et mes produits en colonnes. Je suis ammenee a avoir 2000 produits en catalogue avec des categories et sous categories differentes.

Merci pour votre aide,

Elise.

 

[CISCO]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Bonjour à tous

Autre question : A quoi correspond la ligne 2 dans la feuille Per week-Units à partir de G2 ?

@ plus

 

[elise1990]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

C'est tres homemade...
J'ai ecrit des dates pour ensuite utiliser sumproduct pour faire le calcul sur le mois.

La cellule G2 m'aide par exemple a calcule la cellule G5 dans l'onglet per month. J'ai utilise sumproduct avec la date que j'avais ecrite en G2 de l'onglet per week-units, et j'ai le total sur le mois.
G2, K2, O2, S2 onglet per week-units: 01/02/2016
et ensuite onglet per months sum product, et la formule me fait le total des colonnes G, K, O, S pour le produit.

Oui... Ce n'est pas parfait!!

 

[merinos]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Salut Elise,

Je me permet de rentrer dans la conversation.

Désolé mais tu n'emploie pas le bon type de solution.

Tu dois structurer tes données autrement.
une ligne unique par transaction.
Et tous les rapports sont juste des Tableaux Croisés Dynamiques TCD (PivotTables) C'est super rapide et simple.
Et il n'y a aucune formule dans les rapports.

 

[CISCO]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Bonsoir

Essayes en Per month!G5 avec

SOMMEPROD(('Per Week - Units'!$D$6:$D$36=$D5);DECALER('Per Week - Units'!$F$6:$F$36;;EQUIV(G$1;'Per Week - Units'!$G$2:$GP$2;0);;NB.SI('Per Week - Units'!$G$2:$GP$2;G$1)))

N'ayant pas de valeur, je ne peux pas tester sérieusement la validité de cette proposition.

@ plus

 

[elise1990]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

@Merinos:

J'ai pense a cette solution mais pour analyser mes donnees j'ai besoin de voir a la suite les ventes de cette annee, les ventes de l'annee derniere, la difference et l'evolution. Est ce possible d'integrer ces parametres dans un TCD?

@CISCO

je ne suis pas tres a l'aise avec les codes mais je vais essayer ta solution! Merci!

 

[CISCO]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Bonjour

Ma dernière proposition ne fonctionnera correctement que si toutes les cellules Per Week - Units, ligne 2, contenant la valeur G$1 sont les unes à coté des autres.

@ plus

P.S : Si tu n'y arrives pas, reposte ton fichier avec quelques valeurs, histoire de voir ce que nous pouvons faire.

 

[Victor21]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Re,

@Victor21: pour moi la date et "W5, LY W5, ∆", % sont en colonnes. Il me semble impossible de les permuter en ligne, et mes produits en colonnes. Je suis ammenee a avoir 2000 produits en catalogue avec des categories et sous categories differentes.

C'est vous qui voyez, mais la multitude de sommeprod() sera toujours aussi gourmande en ressources. La réorganisation me semble obligatoire, et notamment pour pouvoir utiliser les TCD comme le préconise justement merinos -sauf à passer au VBA. Attendons donc le passage des VBAistes .

 

[CISCO]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Re

Il y avait une * au lieu d'un point-virgule avant le DECALER dans ma dernière proposition. C'est corrigé.

@ plus

 

[merinos]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

@Merinos:

J'ai pense a cette solution mais pour analyser mes donnees j'ai besoin de voir a la suite les ventes de cette annee, les ventes de l'annee derniere, la difference et l'evolution. Est ce possible d'integrer ces parametres dans un TCD?

@CISCO

je ne suis pas tres a l'aise avec les codes mais je vais essayer ta solution! Merci!

Cela marche sans problème...

Soit tu passe par des tableaux stabilisés... et alors une formule contenant un getpivotdata (.... "year";"2015"... ) - getpivotdata(.... "year";"2014"...)

C'est pas ma solution préférée... car comme tout rapport contenant des formules, tu perds le dynamisme.

L'autre solution est de travailler avec 2 etages de pivots.

données brutes

1° tableau croisé qui agrège le données
des formules qui tansforment ces données en données de base agrégées.

2° niveau de TCD = le rapport final.


Depuis 2008 je ne fais que cela. je vais essayer d'adapter tes données dans un example.

 

[merinos]

Re : Alternative a SUMPRODUCT - Processus de calcul trop long

Re,


C'est vous qui voyez, mais la multitude de sommeprod() sera toujours aussi gourmande en ressources. La réorganisation me semble obligatoire, et notamment pour pouvoir utiliser les TCD comme le préconise justement merinos -sauf à passer au VBA. Attendons donc le passage des VBAistes .

j'emploie eventuellment VBA pour générer les données intermédiaires... une macro vérifie qu'il y a assez de lignes....