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,

 

[JCGL]

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

Bonjour à tous,

Il est bon de savoir que SOMMEPROD() est une formule matricielle

Je regarde ton fichier

A+ à tous

Édition : Pourquoi ne pas utiliser plusieurs SOMMEPROD() et une liste de Validation ?

Un essai en pièce jointe

 

[hoerwind]

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

Bonjour, salut JCGL,

La formule suivante renvoie la même résultat (96) :

=SOMMEPROD((A2:A40={"ci"\"za"\"ba"\"bg"\"hr"\"cz"\"hu"})*(B2:B40))

 

[remit38]

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

Bonjour,

Merci pour vos réponses.


JCGL en fait les données viennent directement d'un outil de reporting, et le tableau des résultats doit être "propre" donc le résultat doit être obtenu directement dans la cellule (pas possible de rajouter des somme de plusieurs cellules, etc...). Au départ j'avais trouvé comment réduire la taille de la formule en nommant la plage mais quand les données sont mises à jour (nouvelle extraction) cela saute donc il faut retravailler le fichier de base...

Hoerwind je ne dois pas comprendre comment entrer la formule que tu proposes car si je fais un copier coller j'ai un message d'erreur : "la formule que vous avez tapé contient une erreur". Y a-t-il une manipulation à faire (par ex pour les formules matricielles). A noter que j'ai excel 2003 si cela peut aider...

Merci par avance.

 

[hoerwind]

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

Bonjour,

Vois sous la pièce jointe ce que donne un simple copier/coller (E4).

 

[jeanpierre]

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

Bonjour hoerwind, remit38,

Dans la formule : =SOMMEPROD((A2:A40={"ci"\"za"\"ba"\"bg"\"hr"\"cz"\"hu"})*(B2:B40)) j'ai le même problème "erreur" (avec un Copier/Coller depuis le forum ou par simple saisie) et grâce à ton fichier joint qui, lui, comporte des points . et non des \ cela fonctionne.

Je saurai pour la prochaine fois.

Merci et bonne journée.

Jean-Pierre

 

[remit38]

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

Rebonjour,

Cela semble marcher effectivement la formule apparait comme SOMMEPROD((A2:A40={"ci"."za"."ba"."bg"."hr"."cz"."hu"})*(B2:B40)) sur mon PC et non comme SOMMEPROD((A2:A40={"ci"\"za"\"ba"\"bg"\"hr"\"cz"\ "hu"})*(B2:B40)).

En tout cas merci beaucoup cela devrait me permettre de fortement simplifier les choses.

Bonne fin de journée

 

[hoerwind]

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

Salut à vous tous,

A mon tour de me poser une question !

Pourquoi cela fonctionne chez moi avec un \ comme séparateur et non avec un . (point) ?
J'ai testé sur XL2003 et XL2007, avec Windows XP et Vista

Il s'agit probablement d'une option, que je ne trouve pas
A moins que la version Excel belge soit légèrement différente de la française

Si quelqu'un a une explication ... ?

 

[jeanpierre]

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

Re,

J'avais, en son temps, cherché mais en vain.

Dernièrement, ROGER à déposé une formule de ce genre et je ne pense pas qu'il ait une version belge d'Excel.

Néanmoins sur ton fichier de 9h39, ce qui est curieux si tu as écrit ta formule avec des \, à l'ouverture ce sont des .

Mystère pour l'instant.

Bon après-midi.

Jean-Pierre

 

[hoerwind]

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

Salut jeanpierre,

Sur le fichier joint, la formule a bien été écrite avec des \ et fonctionne (chez moi)
Je suppose que je ne dois pas te joindre une capture d'écran et que tu me crois.

J'ai tendance de croire qu'il s'agit d'une option, la preuve en est qu'Excel transforme automatiquement les \ en . (point), tout comme les fonctions écrites en français sont automatiquement traduites dans la langue de la version d'Excel qui ouvre le fichier (sauf pour les macros complémentaires).

Par contre ce que je ne comprends pas c'est qu'Excel ne semble pas le faire pour la formule suivante :

=SI(A1="";"";RECHERCHEV(A1;{0\"mauvais";10\"satisfaisant";12\"bien";14\"très bien";16\"excellent"};2))

Saisi un nombre en A1 et vois si tu obtiens un résultat.

 

[jeanpierre]

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

Re,

Copiée depuis le forum ou saisie manuellement elle ne fonctionne pas : Erreur et surligné mauvais comme la précédente.

Modifiée avec le point, parfait.

Espérons que ROGER, entre autres, passe sur ce fil, peut-être aura-t'il des idées.

Jean-Pierre

 

[remit38]

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

Bonsoir,

Je reviens sur ce sujet car j'ai un nouveau petit souci, cette fois je cherche toujours dans la meme formule sommeprod a avoir toujours plusieurs critères mais cette fois avec la condition ET au lieu de OU et avec le critère différent au lieu de égal. J'ai essayé par exemple ($H$16:$H$500<>{"999"."1PL"."433"}) mais je pense que cela veux dire prendre en compte si $H$16:$H$500 est différent de "999" OU "1PL" OU "433", or je souhaite prendre en compte si $H$16:$H$500 est différent à la fois des 3 critères...

J'imagine qu'il faut modifier soit au niveau des "{" soit au niveau des ".", merci par avance pour votre réponse.

 

[hoerwind]

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

Bonjour,

On veut bien t'aider, mais n'est-ce pas un peu trop demandé que de devoir construire nous-même un exemple pour tester un morceau de la formule, sans la connaitre dans son entièreté, ni le ou les formats de la plage H16:H500 ?

Alors, un petit exemple avec la formule et le résultat souhaité ?

 

[hoerwind]

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

Salut jeanpierre,

Merci pour ta confirmation de test.
Je continue à chercher le pourquoi du comment, en attendant une âme charitable qui connait la réponse à cette question.

 

[Compte Supprimé 979]

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

Bonjour le fil,

hoerwind, ne serait-ce pas un problème d'options régionales
Menu windows -> Panneau de configuration -> Options régionales


Ou alors d'options dans Excel
Menu Outils -> Options


Mes amitiés