Microsoft 365 Somme avec plusieurs critères

[olivier777]

Hello,
alors moi j'ai une question
Si dans ma formule je veux mettre le contenu d'une cellule et non pas une valeur figée
=SOMME(SOMME.SI.ENS(B1:B11;A1:A11;{"A";"B"}))

Au lieu de mettre "A" et "B" je veux avoir les valeur se trouvant dans 2 cellules
si par exemple j'ai une donnée dans la cellule D2 et une autre en D3, je ne peux pas faire un truc du style =SOMME(SOMME.SI.ENS(B1:B11;A1:A11;{D2;D3}))
Comment faire ?
Merci

 

[chris]

Bonjour

@olivier777 : tu as quelle version Excel ?

 

[olivier777]

Bonjour Chris
Ma version d'excel :

Je confirme que les formules matricielles fonctionnent bien mais que les performances sont désastreuses des lors que le nombre de lignes de la base commencent à grandir

Du coup je ne sais pas quoi faire

 

[chris]

Bonjour

2 solutions ci-joint :

• soit ajout d'une colonne à la source et TCD
• soit PowerQuery intégré dans ta version Excel

 

[olivier777]

Bonjour Chris,
merci pour ton retour,
en revanche je ne comprends pas bien le fonctionnement
De plus, sur le TCD, cela ne réagit pas comme cela devrait

Merci de ton aide

 

[chris]

RE

De plus, sur le TCD, cela ne réagit pas comme cela devrait

Le TCD est lié uniquement à la 1ère solution : peux tu préciser ce qui ne fonctionne pas ?

en revanche je ne comprends pas bien le fonctionnement

Peux-tu préciser car comme il ya 2 solutions...

 

[olivier777]

ok
Donc cela doit réagir un peu comme si on jouait avec les filtres de colonne Excel, avec une nuance c'est qu'on ne peut pas faire un filtre sans item (Dans les filtres Excel)

Imaginons que que je souhaite connaitre le montant de "Alimentation", quelque soit le "type de magasin" ou de "l'agglomération"

Dans le fichier joint, le montant est de 620
pour obtenir ce montant il faut que
* Alimentation soit coché ET textile décoché ET loisir décoché

en revanche j'obtiendrai le même résultat si je décoche les 2 items ville et hors ville
et/ou les 2 items hyper super
--> Si aucun item d'un filtre n'est sélectionné, c'est comme si tous étaient sélectionnés

Dans les filtres Excel on ne peut pas désélectionner tous items possibles,
je peux faire tous, ou 1 à N items, mais pas aucun

Je ne sais pas si je suis très clair

ou

 

[CISCO]

Bonsoir

Si Chris t'a posé cette question, c'est que cela doit être faisable, et beaucoup plus rapidement, avec Power Query.

@ plus

P.S : Je n'avais pas vu les dernières propositions de Chris...

 

[olivier777]

Hello CISCO,
oui je n'en doute pas
Pour cela que j'ai essayé de reformuler tout ça et mettre à jour le fichier avec toutes les choses déjà réalisées

 

[chris]

RE

Ceci semble OK cette fois

 

[olivier777]

Merci Chris
en revanche je ne comprends pas comment ça fonctionne (pour obtenir le résultat global de ce qui est coché)
+ les résultats intermédiaires (Voir mon dernier fichier ou je découpe les résultats en fonction des filtres cochés, par bornes de date)

 

[chris]

RE
Tu veux dire qu'on ne retient pas toutes les dates ?
Je ne vois pas où sont saisies les dates début et fin de la plage à retenir si on ne prend pas tout
C'est un peu fouillis tout ça...

De même il n'est pas très clair su tu veux juste le total ou de total par jour...

Sinon cela est fait par PowerQuery donc il faut ouvrir PowerQuery pour voir (comme tu ouvres VBE pour voir le VBA)

Données, Obtenir des Données, Lancer l'éditeur PowerQuery

Vois déjà si cela marche bien sur tes 30 000, après on verra pour les explications

 

[olivier777]

Bonjour chris
je peux "jouer" avec les dates, en faisant des sommes (par années, pas mois, par semaines, par jour, ....)
Avec PowerQuery, le résultat se rafraîchit automatiquement lors des clics dé-clics des filtres ?
j'ai ajouté un onglet dans le fichier (feuil2)
cela montre le genre de choses que je pourrais avoir
(les tableaux, graphiques, se mettent à jour des qu'une action est faite sur un filtre)

La base ensuite s'enrichie en nombre de lignes bien évidemment, mais je peux avoir des colonnes supplémentaires pour avoir d'autres données, comme des quantité, des coûts, ...
J'ai limité en nombre de colonnes, de calculs, de lignes dans le fichier joint pour ne pas qu'il soit trop gros et enregistré en xlsb pour pouvoir le charger ici

Pourquoi pas de nouveaux filtres également.

Est-ce plus clair ?

Merci

 

[chris]

RE

J'ai modifié pour pouvoir cocher tout ce qu'on veut ou rien

Rien équivaut à aucun filtre mais je peux changer

Le résultat des filtres est traité par TCD
J'en ai créé un avec des segments qui permet soit d'obtenir un total
mais tu peux soit l'adapter soit en créer d'autres pour analyser comme tu veux avec le(s) GCD associé(s)

Si tu veux conserver d'autres colonne il faut remanier : je n'ai gardé que les dates et montants sinon le cache TCD risque d'alourdir le fichier

 

[olivier777]

Je regarde
Merci Chris
Si, rien = rien. Si aucune case cochée ça doit renvoyer 0
Pas très utile certes

Sinon sur mon fichier de travail j'ai beaucoup + de colonnes, d'indicateurs que dois pouvoir utiliser pour faire des calculs identiques, mais avec les bonnes colonnes

Du coup, la ou je vais être bloqué, c'est que je suis complètement incapable de reproduire ce que tu as fais sur un autre fichier pour prendre en compte une structure enrichie

 

[chris]

RE

On demande toujours de poster un fichier REPRESENTATIF

Si ni le nombre de thématiques, ni le nombre de colonnes ne correspond on travaille pour rien...