Sommeprod multi-critères formule trop longue => formule matricielle ?

[remit38]

Bonjour,

Je gère actuellement un fichier dans lequel je fais la somme de montants selon de multiples critères (somme si le critère A ou B ou C... est correct).

Cela fonctionne bien avec sommeprod (comme dans le fichier joint en exemple) mais mon problème est que pour certaines cellules j'ai une liste trop importante de critères à prendre en compte (ici les codes pays) qui rendent la formule trop longue et me bloque sur excel (j'ai excel 2003, à priori des solutions existent sur 2007).

Dans mon exemple joint cela ne se voit pas vraiment mais en fait je vais rechercher les données dans un classeur excel différent donc cela rallonge d'autant la formule, je suis trop limité. Je cherche en fait pour réduire la longueur à éviter de devoir répéter la plage de recherche pour chaque critère.

En cherchant je pense qu'il faut que je me tourne vers une formule matricielle mais je n'arrive pas à comprendre comme cela fonctionne, je cherche à avoir quelque chose du type somme(si(A2:A40=ou("ci";"za";"ba";"bg");B2:B40;0)... sachant que je ne maitrise pas les formules matricielles je ne sais pas si cela fonctionne vraiment ni comment entrer correctement la formule.

Sur le site support de Microsoft il y a une section à ce sujet mais là aussi je n'arrive pas à reproduire cela dans mon fichier (formule du type SOMME(SI(A2:A9=\{"A","B"\},B2:B9,0))), voir détails sur ce lien : Quand utiliser une formule matricielle SOMME (SI())

Je vous remercie par avance pour vos conseils.

Cordialement,

 

[hoerwind]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Salut Bruno,

Merci pour ta réaction, mais ce sont bien les deux premiers points que j'ai vérifié, sans y trouver réponse.

Je crois me souvenir avoir vu quelques part une option \, mais où ?

Je ne perds pas espoir !

 

[remit38]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Bonjour,

Désolé je n'ai pas pensé à refaire l'exemple. J'ai repris mon premier fichier en modifiant la formule en conséquence, et je souhaiterais donc savoir s'il est possible d'arriver à quelque chose comme SOMMEPROD((A2:A40={"ci"."za"."ba"."bg"."hr"."cz"."hu"})*(B2:B40)) mais qui serait du genre SOMMEPROD((A2:A40<>{"ci"."za"."ba"."bg"."hr"."cz"."hu"})*(B2:B40)) avec a la place des point (équivalents aux "+"=>OU) quelque chose qui serait équivalent aux "*" (=>ET).

Merci par avance pour votre aide.

 

[hoerwind]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Bonjour,

Je ne trouve dans le sens que tu demandes, mais bien comme ceci :
=SOMME(B2:B40)-SOMMEPROD((A2:A40={"ci"."za"."ba"."bg"."hr"."cz"." hu"})*(B2:B40))

 

[remit38]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Bonjour hoerwind,

Merci pour ta réponse, mais le problème c'est que dans mon "vrai" tableau les données que je veux additionner (ici avec le code pays) et celles que je veux exclure (on va dire qu'il s'agit de type d'article) sont en fait sur 2 colonnes différentes, et je peux donc avoir pour un code pays donné des articles à additionner et d'autre à ne pas additionner.

Je joins un nouvel exemple plus proche de la réalité. J'aimerais donc pouvoir tout inclure dans une formule sommeprod sans devoir répéter les plages... (comme avec les {"".""} mais avec ET au lieu de OU comme notion pour les conditions exclues).

Merci par avance.

 

[hoerwind]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Bonjour,

Il est toujours préférable de fournir dès le départ un fichier reflétant exactement le problème à résoudre.

J'ai cherché une solution, mais n'en ai pas trouvé.
Je constate aussi que deux conditions avec des {} ne sont pas acceptées par Excel.
A titre d'exemple, la formule suivante renvoie un message d'erreur :
=SOMMEPROD((A2:A26={"c"."e"})*(B2:B26={"AT"."BE"."CH"})*(C2:C26)),
alors que séparement cela fonctionne, et je ne vois pas pourquoi.

Je regrette vraiment que ne puisse t'aider.

 

[david84]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Bonjour à tous, Hoerwind,
je n'ai pas suivi ce fil donc je ne sais pas ce que vous cherchez à faire.
Cependant, j'ai testé 2 formules sur le fichiers qui donnent des résultats:
- la formule d'Hoerwind légèrement remaniée (en rajoutant une valeur dans la 1ère matrice de la formule donne 13
=SOMMEPROD((A2:A26={"c"."e"."a"})*(B2:B26={"AT"."BE"."CH"})*(C2:C26))
alors que la formule initiale d'Hoerwind renvoie "#N/A"
Autre exemple : =SOMMEPROD((A2:A26<>{"A"."B"."D"})*(B2:B26={"AT"."BE"."CH"})*(C2:C26)) donne 48.
Faut-il équilibrer les valeur de recherche lorsque l'on utilise ce type de rédaction de formule ({"A"."B"."D"}) ?

 

[remit38]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Hoerwind, David

Merci encore pour ces réponses.

J'ai aussi essayé la formule SOMMEPROD((B2:B26={"AT"."BE"."CH"})*(C2:C26))-SOMMEPROD((A2:A26={"A"."B"."D"})*(B2:B26={"AT"."BE"."CH"})*(C2:C26)) toujours dans le meme exemple et j'ai bien une réponse mais le resultat semble faux et il est différent de celui obtenu avec la formule SOMMEPROD((A2:A26<>"A")*(A2:A26<>"B")*(A2:A26<>"D")*(B2:B26={"AT"."BE"."CH"})*(C2:C26)) (48 pour la premiere formule, 25 pour la 2eme, 25 étant sauf erreur de ma part la bonne réponse)...

Je ne vois pas d'ou vient le probleme.

 

[remit38]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

David en fait ce que je cherchais à faire c'est trouver une formule qui ressemble à SOMMEPROD((A2:A26<>{"A"ET"B"ET"D"})*(B2:B26={"AT"."BE"."CH"})*(C2:C26)) donc quand la lettre n'est NI "A" NI "B" NI "D". J'ai bien compris le principe du "." qui équivaut à OU mais je voudrais savoir s'il existe un moyen de faire passer la notion de ET dans ce type de formule.

 

[david84]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Re,
la formule =SOMMEPROD(($A$2:$A$26<>{"c"."e".""})*($B$2:$B$26={"AT"."BE"."CH"})*($C$2:$C$26)) renvoie 67.
Est-ce le résultat attendu ?
Sinon place manuellement sur ton fichier le résultat attendu en expliquant ton raisonnement.

 

[hoerwind]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Salut david,

Le fichier et le résultat attendu sont en pièce jointe sous le message #19

Je confirme que la formule doit bien renvoyer 25 comme résultat.

 

[remit38]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Rebonjour,

Je vous ai fait un nouvel exemple qui se rapproche plus de la réalité (c'est un tout petit extrait sachant que le fichier de données global comporte des centaines de ligne, des dizaines de colonnes et le tableau des dizaines avec des lignes et d'autres conditions.. Mais en gros ce que je veux se trouve dans l'onglet résultat, mon objectif est de "réduire" au maximum la taille des formules et donc d'éviter la répétition des plages, formules, etc... car j'arrive vite à saturation au niveau de la longueur...

Merci par avance pour vos conseils, je ne sais pas s'il est possible d'améliorer le résultat...

 

[david84]

Re : Sommeprod multi-critères formule trop longue => formule matricielle ?

Re;
merci de la précision Hoerwind.
Je ne sais pas si ce que demande Remit38 est faisable.
La seule possibilité que je connaisse pour signifier le "et" dans une formule matricielle est le "*". De plus, la syntaxe utilisée (le "." placé entre chaque valeur ne veut, à mon sens pas dire "ou", comme le pense remit38 mais signifie que la matrice sélectionnée correspond à une plage de x lignes (matrice verticale (en colonne, la syntaxe serait ";")).
Ceci dit, je suis peut-être à côté de la plaque...