XL 2016 Liste détaillé en utilisant beaucoup de SOMMEPROD

[elgringo123456]

Bonjour,

Je suis à la recherche de piste afin de réaliser un listing de conditions relatifs à un SOMMEPROD.
En effet, situé dans un onglet j'ai plusieurs sommeprod permettant de réaliser des calculs.
Je souhaite créer une liste de chaque ratio calculé.

Quelles sont selon vous les options possibles ou techniques à emloyer ?
Je joins un fichier en exemple. Il faut imaginer que ce fichier est beaucoup plus lourd (peut contenir beaucoup de conditions et donc de sommeprod afin de réaliser le calcul).
Dans ce fichier, l'onglet Report contient des SOMMEPROD, je souhaiterais réaliser un listing des informations calculées (piste d'audit du calcul)
Dois-je créer une analyse lexicale du sommpeprod pour réaliser des filtres et donc obtenir cette pisetd d'audit ?
Je n'utilise pas les TCD (peut être du au fait que j'ai un méconnaisance à ce sujet. Par contre, si j'utilise les TCD, je risque d'avoir besoin de beaucoup de TCD différents.

J'ai effectué des recherches sur Excel-Download et Google mais je n'arrive pas à réaliser des requêtes correspondant à mon besoin.

Auriez-vous des idées ?
Merci d'avance de votre participation.

 

[chris]

Bonjour

SOMMEPROD en grande quantité c'est moyen du fait des temps de calculs

Les TCD sont bien plus souples, notamment en terme de filtrage, et évite le recalcul permanent

Puisque tu as 2016, tu as aussi la possibilité d'utiliser PowerQuery, totalement intégré à Excel, pour prétraiter (remplacer tes fonctions persos) et soit amalgammer pour finaliser par TCD, soit tout calculer

 

[sylvanu]

Bonjour Elgringo, Chris,
1- Qu'entendez vous par "analyse lexicale" ? La liste de tous les paramètres entrant dans les formules pour audit ?
Ne pourrait o pas passer par une liste déroulante pour choisir les paramètres à analyser ? Auriez vous la liste des paramètres à auditer ?

2- Comme vous avez beaucoup de Sommeprod, il serait peut être avantageux de les remplacer par des Somme.si.ens. 4 fois plus rapide :

Différence de rapidité de calcul entre les 4 couteaux suisses d'Excel [niveau intermédiaire] - XLérateur

Excel VBA cours formation

www.xlerateur.com

Cela donnerait par ex pour Fds1 C2 :

=SOMME.SI.ENS(Data1_PM;Data1_Portefeuilles;"Fonds 1";Data1!$F$2:$F$2389;A2)
au lieu de
=SOMMEPROD((Data1_Portefeuilles="Fonds 1")*(Data1!$F$2:$F$2389=A2)*(Data1_PM))

Idem dans la feuille Report.

Fonction SOMME.SI.ENS - Support Microsoft

Comment utiliser la fonction SOMME.SI.ENS dans Excel, l'une des fonctions mathématiques et trigonométriques d'Excel, pour additionner les arguments qui répondent à plusieurs critères.

support.microsoft.com

 

[elgringo123456]

Bonjour Chris et Sylvanu,

Tout d'abord je tiens à vous remercier de m'avoir répondu avec un super niveau de réactivité.

Pour Sylvanu, je ne savais pas que SOMME.SI.ENS est plus rapide et souple pour certains cas en mode calculatoire. En général j'utilise les 2 (SOMMEPROD et SOMME.SI.ENS). En effet SOMMEPROD permet de
1. Réaliser les calculs sur un classeur fermé (je crois de mémoire)
2. Structurant en terme de gestion des erreurs (plus stricte quand il y a une erreur de fonction car cela permet d'identifier les erreurs de paramétrages).
3. Ajouter des conditions OU au sein de la même fonction
SOMME.SI.ENS, je l'utilise avec les jokers => C'est vrai que certaines fois cela me facilite la vie

La piste d'audit c'est d'obtenir la liste de ces filtres à un endroit (un peu comme une extraction)

Pour Chris,
Effectivement j'ai peur des TCD car j'aurais besoin de créer plusieurs TCD (autant de TCD qu'il y a de portefeuilles et de catégories de ratios). A l'époque j'avais cru comprendre qu'un TCD effectue une copie de données sources et le souci c'est qu'il devrait y avoir une quarantaine de TCD.
Peut être que je ne sais pas vraiment les utiliser et surtout les exploiter.

Côté Power Query, j'ai Microsoft Excel 2016 (nouvellement installé), j'ai regardé quelques docs pour activer le Power Query. Je dirai à priori qu'il est installé mais je ne vois pas le COM Addins Power Query.
A priori cela fonctionne. Je ne connaissais pas et cela à l'air d'être un super tools.

Merci encore de m'avoir fait avancer dans ma compréhension.
Je vais essayer de me trouver un bon tuto sur les TCD pour voir comment en créer beaucoup sur un classeur et ainsi avoir une Piste d'audit des ratios calculés

 

[chris]

RE

Ce qui prend de la place c'est le cache du TCD mais ensuite chaque TCD est assez léger car ils piochent tous dans le même cache (depuis la version 2007)

En plus si besoin on peut synchroniser le filtrage d'un TCD d'un clic

PowerQuery est un add on pour 2010 et 2013 mais à partir de 2016 il est totalement intégré à Excel et accessible depuis l'onglet Données

A noter que les données source peuvent être dans un autre fichier, tant pour une exploitation directe par TCD, que via PowerQuery, ce qui évite la surcharge pondérale

 

[elgringo123456]

Bonjour Chris,
Super et merci beaucoup. Le power query a l'air d'être un monstre atomique !
Quelle puissance ! Encore faut il maitriser la bête de course
Merci encore de ces précisions techniques très utiles !!!!
Passe une bonne soirée,
Cordialement.
El Gringo 123456

 

[chris]

Bonjour

Déjà comme je l'ai mentionné, tu peux charger les données ainsi que les 2 tables de paramètres et compléter les données par des fusions de requête et des formules afin d'obtenir l'équivalent de tes colonnes calculées.

Ensuite en fonction de l'exploitation prévue, voir s'il faut synthétiser et à quels niveaux...

N'hésite pas à reposter si tu coinces

 

[elgringo123456]

Hello Chris,

Merci de toutes ces précisions.
Effectivement PowerQuery doit être un super module pour Excel.
Cela fait 10 ans que je fais du vba et je me suis orienté sur beaucoup de sujet tels que les API Windows, chaînes de caractères et plein d'autres choses, ...
J'avais même à l'époque réalisé un peu de Microsoft Query et des fonctions de requêtes. Je pense que ce module est énormissime !!!!
Encore merci
J'aime bien ta citation