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

 

[CISCO]

Bonjour à tous

Olivier777, merci pour ces informations.
Vu la composition de la plage A1: D18 il serait plus logique, et plus simple, de ne pas avoir les mêmes résultats en ayant coché des cases différentes (Pour avoir le total 1026, il faudrait tout cocher, et on n'aurait ce résultat que dans ce cas)...
Mais bon, j'imagine que ce n'est pas toi qui as fixé les règles du "jeu"...
J'essayerai plus tard...

@ plus

PS : Comment est fait le lien entre les cases à cocher et la plage R20:R24 ? Je ne vois pas de macro dans ton fichier...

 

[CISCO]

Bonjour

Et comme ça, est-ce que c'est mieux...
C'est faisable comme ça parce qu'on a "que" 2 * 3 = 6 cas à traiter...
Mais si tu viens à prendre en compte la colonne B, cela va faire 2 * 2 * 3 = 12 cas, donc une formule de 12 lignes. Assez "facile" à comprendre mais pas très pratique...

@ plus

 

[CISCO]

Bonjour

Idem, mais en un peu plus court, dans M18, toujours en matriciel.

@ plus

 

[CISCO]

Re

Et en encore plus court, toujours dans M18. Çà parait tellement plus simple comme ça... si c'est correct !!! C'est toujours la même méthode, mais en regroupant les choses grâce aux possibilités offertes par la validation en matriciel.

@ plus

P.S : Avec cette formulation, il est assez facile de rajouter un critère en plus, par exemple le lieu de l'achat, ville ou hors ville, sans trop rallonger l'ensemble. Si tu n'y arrives pas, signale le moi.

 

[CISCO]

Re

On peut encore un peu simplifier :

SOMME(ESTNUM(EQUIV(A2:A18;SI(R20:R21+(R20=R21)*(R22+R23+R24);P20:P21);0)*EQUIV(C2:C18;SI(R22:R24+(R22=R23)*(R23=R24)*(R20+R21);P22:P24);0))*D2:D18)

toujours en matriciel

@ plus

 

[Collins]

Bonjour CISCO, bonjour à tous

CISCO, j'ai un fichier ou ta dernière solution en #28 pourrait m'aider. car j'ai fait l'essai avec sur le fichier que tu as mis en #27 en le modifiant mais peux tu me compléter cette dernière formule pour avoir un autre critère par exemple "ville"dans le fichier. c'est à dire de prendre en compte 3 critères sur les 3 colonnes. Je rajouterai une case à cocher je pense avoir compris pour le faire.
puis avec modestie je l'adapterai à mes besoins.
Et une question sur la validation en matriciel. Pourquoi une formule marche avec le matriciel et non sans. A quoi sert le matriciel en général. quel est son but ?
Bonne après-midi

 

[CISCO]

Bonjour

Cf. en pièce jointe une possibilité en prenant en compte la colonne B. J'ai mis à la main les valeurs VRAI/FAUX correspondantes dans R31 et R32.

Bonjour CISCO, bonjour à tous
....
Et une question sur la validation en matriciel. Pourquoi une formule marche avec le matriciel et non sans. A quoi sert le matriciel en général. quel est son but ?
Bonne après-midi

Plusieurs fonctions dans Excel travaillent par rapport à une cellule et pas par rapport à une plage.
Si tu utilises par exemple EQUIV(A12;B25:B50;0) cela fonctionne correctement car le premier paramètre doit correspondre à une seule valeur. SI tu écris EQUIV(A12:A15;B25:B50;0), Excel ne recherchera que la position de la valeur/du texte dans A12 dans la plage B25:B50, pas de celles dans A13, A14 et A15 dans la plage B25:B50. Si tu valides EQUIV(A12:A15;B25:B50;0) en matriciel, Excel donnera bien ces 4 valeurs, et pas uniquement la première. Tu ne verras pas forcément ces 4 valeurs à l'écran, car elles sont souvent utilisées dans une autre formule, du style SOMME(... ou INDEX(....;....).

Autrement dit, la validation matricielle permet de travailler sur une plage, avec plusieurs valeurs/textes, et pas seulement avec une. Elle permet de simplifier beaucoup de formules, mais prend beaucoup de temps de calcul (Dans certaines formules, la réponse convenant peut être trouvée sur la ligne 200, mais si tu as mis une plage comprenant 3000 cellules, Excel fera les calculs correspondant à ces 3000 cellules, et pas uniquement sur les 200 premières).

Un autre exemple simple là.

@ plus

 

[Collins]

Voila une affaire de régler.
En essai j'ai rajouté la case à cocher et çà marche à merveille.
Maintenant je comprend mieux la fonction matricielle, ton explication m'apporte beaucoup. Simple et concis. J'aurai quand même toujours des difficultés à construire de grandes formules. Mais les comprendre c'est bien çà fait progresser.
Je te remercie infiniment.
Bonne soirée
@+

 

[excfl]

Re,

 

[olivier777]

Wouah !!!
je n'ai pas pu me connecter hier
je découvre les avancées !
euh... ça m'a l'air top !

Il me reste un truc à rajouter :
la possibilité de découper le résultat selon différentes conditions
Par exemple, dans le fichier joint j'ai rajouté des dates
je souhaiterai donc pouvoir afficher le résultat en ajoutant un "bornage"

Merci d'avance et surtout pour tout ce qui a déjà été fait

 

[CISCO]

Bonjour

Dans M23, tu peux faire en transformant le début de la formule comme çà :

SOMME((L23<=A$2:A$18)*(A$2:A$18<L24)*ESTNUM...

(J'ai exclu L24 pour que les données correspondant à ce jour ne soient pas prises en compte dans M23. Elles le seront dans M24).
Si tu veux pouvoir tirer cette formule vers le bas, il faut rajouter un $ devant tous les nombres suivants dans cette formule (mais pas L23 et L24), ce qui donne, uniquement pour le début :

SOMME((L23<=A$2:A$18)*(A$2:A$18<L24)*ESTNUM(EQUIV(C$2:C$18;SI(R$20:R$21+(R$20=R$21)*(R$22+R$23+R$24+R$25+R$26);P$20:P$21);0)*...

toujours en matriciel

Pour ma culture, une question (et une réponse j'espère ) ...

PS : Comment est fait le lien entre les cases à cocher et la plage R20:R24 ? Je ne vois pas de macro dans ton fichier...

@ plus

 

[olivier777]

Hello CISCO
Merci pour ta réponse, ça fonctionne !
sauf pour la dernière ligne

Mais c'est parce qu'il n'y a pas de date ensuite, donc la formule ne peut pas faire le job correctement, L30 étant vide : =SOMME((L29<=A$2:A$18)*(A$2:A$18<L30)

Il faut rajouter une condition ?

Pour ta culture, Il n'y a pas de chose secrête, au début je voulais remplir ces case via boutons de sélection et macro
donc une partir est restée.
Mais finalement pas besoin de faire de macro tant que les formules suffisent
donc les cases sont maintenant remplies juste avec les liens du contrôle case à cocher :

Sinon je vais avoir d'autres données, je vais voir ce que ça donne (Au niveau de la performance si il y a bcp de données pour voir si les formules matricielles tiennent le coup et ne mettent pas trop excel dans les choux le temps de faire les calculs)

 

[CISCO]

Bonjour

Hello CISCO
Merci pour ta réponse, ça fonctionne !
sauf pour la dernière ligne
Regarde la pièce jointe 1062724
Mais c'est parce qu'il n'y a pas de date ensuite, donc la formule ne peut pas faire le job correctement, L30 étant vide : =SOMME((L29<=A$2:A$18)*(A$2:A$18<L30)

Il faut rajouter une condition ?

Et oui, il faut rajouter une condition, une date dans la cellule L30 du style 31:12:2020, ou tu peux faire dans M29 avec =SOMME((L29<=A$2:A$18)*(A$2:A$18<SI(L30<>"";L30;DATE(2020;12;31)))... formule que tu peux ensuite faire remonter vers le haut.

@ plus

 

[olivier777]

Bonjour CISCO, bonjour tous
Alors ça a l'air de pas mal fonctionner
seul bémol : les performances en terme de temps de recalcul
ma base excel s'enrichit, les filtres se font de la même manière que dans cette exemple mais en terme de lignes dans la base j'ai environ 30 000 lignes
je ne sais pas si on peut optimiser cela...
Merci

 

[CISCO]

Bonjour

Il est certain qu'avec 30 000 lignes, ça doit ramer. Les formules matricielles consomment beaucoup de temps de calcul... et là, je ne vois pas trop comment simplifier celle utilisée dans M19. Pour gagner en temps de calcul, il faut passer par du VBA. Il ne te reste plus qu'à espérer qu'un VBAiste compétent passe par là...

@ plus