encore moi avec... Sommeprod

[pango]

encore moi avec... Sommeprod (et SI)

Bonjour à tous,

Je me débrouille assez bien avec cette fonction (sommeprod) qui a révolutionné mon utilisation d'Excel (merci encore aux experts de ce forum) Comme je ne suis pas un spécialiste, il a certains problèmes que je n'arrive pas à résoudre.

Dans le fichier joint à ce message, ma fonction déraille en G6, G9, etc. Je connais la raison, c'est à cause qu'il ne retrouve pas l'un des critères et l'une des plages (c'est le critère "important" qui se trouve à la place du critères "Très important"). Je sais que je pourrais reviser tous le fichier et ajouter les cellules manquantes, mais le travail va devenir lourd.

J'ai trouvé une façon de contourner le problème qui est relativement facile à utiliser dans ce cas, car il y a seulement deux critères, mais dans d'autres fichiers, j'ai 4-5 critères et la formule ci-dessous devient lourde à utiliser et gérer.
=SOMMEPROD(($A$4:$A$466=$G6)*($B$4:$B$466=H$3);$D$5:$D$467)+SOMMEPROD(($A$4:$A$466=$G6)*($B$4:$B$466=I$3);$D$5:$D$467)

Je me demandais s'il cela était possible de combiner sommeprod avec SI, ET ou OU.

J'aimerais avoir quelque chose dans le genre :

=SOMMEPROD(($A$4:$A$466=$G6)*($B$4:$B$466=H$3 OU I$3 );$D$5:$D$467)

Je sais que la formule ci-dessus est erronée, mais c'est pour illustrer mon exemple.

Merci à l'avance pour vos réponses

 

[Samantha]

Re : encore moi avec... Sommeprod

Bonsoir,

et en essayant :

=SOMMEPROD(($A$4:$A$466=$G6)*(ou($B$4:$B$466=H$3;$B$4:$B$466=I$3);$D$5:$D$467)
Ca donne quoi ?

A te lire

Sam

 

[pango]

Re : encore moi avec... Sommeprod

Bonsoir,

et en essayant :

=SOMMEPROD(($A$4:$A$466=$G6)*(ou($B$4:$B$466=H$3;$B$4:$B$466=I$3);$D$5:$D$467)
Ca donne quoi ?

A te lire

Sam

Bonjour Samantha,

Cela donne une erreur, mais c'est à cause qu'il manque une parenthèse.

=SOMMEPROD(($A$4:$A$466=$G6)*(ou($B$4:$B$466=H$3;$B$4:$B$466=I$3));$D$5:$D$467)

Merci beaucoup! Je n'aurais jamais trouvé sans ton aide. J'ai un peu de difficulté à comprendre la logique sur l'emplacement des tests conditionnelles lorsque l'on doit les combiner avec d'autres fonctions.

 

[pango]

Re : encore moi avec... Sommeprod

Bonsoir à tous,

Inspiré par la combinaision "sommeprod" et "ou" de Samantha, J'ai essayé:

=SOMMEPROD(($A$4:$A$466=$G4)*(SI(($B$4:$B$466=I$3);$D$5:$D$467;$D$7:$D$469)))


Pourquoi que cela ne fonctionne pas en I4 ou ailleurs dans la colonne I.

Je lui demande si tu trouves "important" en B4 mets la valeur de la ligne D5 . Si c'est faux mets la valeur de la ligne D7, puisque par conséquent c'est "très important" qui va se retrouver en B4 dans une telle situation.

J'espère que je suis clair. Mon fichier se trouve dans le premier message.

Merci encore

 

[Monique]

Re : encore moi avec... Sommeprod

Bonjour,

Avec SommeProd Nb.Si associé à Decaler(), tu peux mettre autant de critères que tu veux :
SommeProd(Nb.Si(PlageCritères;Plage);Plage à sommer)

Pour un seul critère :
SommeProd(Nb.Si(Critère;Plage);Plage à sommer)

Dans le fichier joint, tu as une feuille avec quelques explications

 

[pango]

Re : encore moi avec... Sommeprod

Bonjour,

Avec SommeProd Nb.Si associé à Decaler(), tu peux mettre autant de critères que tu veux :
SommeProd(Nb.Si(PlageCritères;Plage);Plage à sommer)

Pour un seul critère :
SommeProd(Nb.Si(Critère;Plage);Plage à sommer)

Dans le fichier joint, tu as une feuille avec quelques explications

Bonsoir Monique,

Trop fort!!!!

Je vais devoir prendre quelques minutes pour comprendre toute la logique de cette formule, mais je devrais être capable de m'en sortir.

Merci beaucoup!

 

[pango]

Re : encore moi avec... Sommeprod

Bonjour Monique et tous les autres,

J'ai réussi à comprendre l'essentiel de ta formule. Cependant un élément m'intrigue!

Si je comprends bien la fonction "décaler", il y a 4 décalages possibles : ligne, colonne, hauteur, largeur. Les deux dernières semblent facultatives, puisque dans ta formule tu en utilises seulement 2 (-1;0). Ce qui m'échappe c'est lorsque je tente de changer les valeurs de l'un des nombres, Excel affiche un message d'erreur. Ainsi en remplacant -1 par 0 ou 1, il corrige en affichant *0, par exemple.

Autres mystères si je mets -2;0 dans le "decaler" de la zonea, c'est la valeur 1 qui apparait. Mais si je mets -2 dans le deuxième "decaler", c'est la valeur 9 qui apparait. Dans le premier "decaler" il va donc chercher la valeur située une ligne plus bas,mais dans le second c'est la valeur située une ligne plus haut!

Ce signe -1 m'intrigue donc...

Merci à l'avance d'éclairer ma lanterne.

 

[Monique]

Re : encore moi avec... Sommeprod

Bonjour,

Les deux plages bleues sont nommées de façon décalée, dès le départ
parce que les titres "Important" etc de la colonne B et leur correspondance en colonne D… ne correspondent pas.
SommeProd calcule en "face à face". Ici, B4 correspond à D5 :
=SOMMEPROD((B4:B9="important")*1;D5: D10)
renvoie la valeur de D5 si B4 contient "Important"
renvoie la valeur de D7 si B6 contient "Important"

Decaler(Réf; nb de lignes; nb de colonnes; Hauteur; Largeur)
DECALER(ZoneA;EQUIV($G4;$A$4:$A$500;0)-1;0)
DECALER(B4:B8;(Position de G4 dans A4:A500)-1;0 colonne; Hauteur = hauteur de B4:B8 donc omis; largeur = 1 colonne donc omis)
Position de G4 dans A4:A500) est donnée par Equiv(G4; A4:A500; 0)
Equiv renvoie 1 pour la position 1 (normal), 2 pour la position 2, etc, donc on soustrait 1 au résultat de Equiv()

Celle-ci revient au même (en H4) :
=SOMMEPROD(NB.SI(H$3;DECALER(ZoneA;EQUIV($G4;$A$3:$A$500;0)-2;0));DECALER(ZoneB;EQUIV($G4;$A$3:$A$500;0)-2;0))

Est-ce que tu as fait des essais dans la feuille "Explic" ?
Tu changes la valeur en H12 et tu regardes les "formules-phrases" en K16 et K17

 

[pango]

Re : encore moi avec... Sommeprod

Bonjour Monique,

Je comprends mieux la logique de la formule "decaler" maintenant:

Réf = ZoneA
nb de lignes = EQUIV($G4;$A$4:$A$500;0)-1
nb de colonnes = 0

largeur et hauteur omis

Je n'avais pas saisis que le "-1" était associé à EQUIV. Je croyais à tort qu'il s'agissait du nombre de ligne. C'est pas mal costaud comme formule.

Merci encore pour cette formule et tes explications