la fonction sommeprod couplée à la fonction max

[lounes]

Bonjour à tous les membres ,

Depuis un certain temps , je me heurte à une difficulté qui est celle qui consiste à coupler à la fonction sommeprod , la fonction max ; l'objectif étant de trouver dans un emploi donné ( exemple : gardien) , le salaire de base maxi perçu par l'un des travailleurs occupant le poste de gardien.

Pour celà , j'utilise un fichier des effectifs structuré de plusieurs informations dont la fonction et le salaire de base.

Pour les besoins de l'exemple , le fichier joint comportera , uniquement , la fonction et le salaire de base.

 

[Guest]

Re : la fonction sommeprod couplée à la fonction max

Bonjour,

En nommant les colonnes et par formules matricielles à valider par CTRL+MAJ+ENTREE

=MAX(SI(Fonctions="Gardien";Salaires;0))

A+

 

[Compte Supprimé 979]

Re : la fonction sommeprod couplée à la fonction max

Bonjour Lounes et bienvenue sur ce forum
Salut Hasco 😉

Sinon avec SOMMEPROD()

=SOMMEPROD(MAX((B7:B506="Gardien")*(C7:C506)))

A+

 

[david84]

Re : la fonction sommeprod couplée à la fonction max

Bonsoir lounes, Hasco🙂, Bruno😉,
si je peux me permettre pour éclaircir ta compréhension de sommeprod et en se basant sur l'exemple de Hasco que je salue : dans le cas présent, sommeprod ne te sert à rien puisque tu cherches un max. Tu travailles donc sur une cellule. La formule utilisée par Hasco est donc pertinente et va te ramener l'information recherchée

=MAX(SI(Fonctions="Gardien";Salaires;0))

Tu peux même la réduire en enlevant la valeur si faux (0) qui n'est pas utile à la formule même si elle peut servir à la compréhension de la formule :

=MAX(SI(Fonctions="Gardien";Salaires))

Tu peux même ne pas utiliser si et l'écrire

=MAX((Fonctions="Gardien")*Salaires)

ce qui dans le cas présent ne change rien mais qui peut être utile dans les formules où les conditions se multiplient (cf.

https://www.excel-downloads.com/threads/imbriquer-nbval-et-si.148615/

).
Concernant Sommeprod, comme son nom l'indique et selon l'aide d'Excel :"Multiplie les valeurs correspondantes des matrices spécifiées et calcule la somme de ces produits.
Dans le cas présent il pourrait te permettre par ex de ramener la somme des salaires des gardiens :

=SOMMEPROD((Fonctions="Gardien")*Salaires)

. Mais là, sommeprod ne te montre qu'une partie de ses possibilités : il calcule la somme de ces produits puisque sur les 2 matrices spécifiées, seule salaire comporte des nombres. Tu ne peux donc te rendre compte de la partie "Multiplie les valeurs correspondantes des matrices spécifiées" (attention, je ne dis pas qu'il ne le fais pas, je dis que cette partie n'est pas mise en valeur dans l'exemple).
D'ailleurs dans le cas présent, tu peux très bien remplacer sommeprod par somme ce qui te donnera le même résultat (sauf que sommeprod permet une validation classique alors que somme demande dans ce cas précis une validation matricielle dans ce cas précis):

=SOMME((Fonctions="Gardien")*Salaires)

Ainsi, dans l'exemple fourni par Hasco, les 2 formules te ramèneront 740572.
Pour vraiment te rendre compte de l'intérêt de sommeprod et de sa différence avec somme dans le cas présent, à savoir la partie "Multiplie les valeurs correspondantes des matrices spécifiées..." tu dois te servir à nouveau de la matrice "salaire".
Admettons que tu veuilles par exemple connaître la somme des salaires max touchés par les catégories "gardien" et "coffreur N1" par ex, en reprenant la formule de Hasco (sans la partie "0"), si tu utilises la fonction somme (validation matricielle)

=SOMME((MAX(SI(Fonctions="Gardien";Salaires)));(MAX(SI(Fonctions="coffreur N1";Salaires))))

, cela te ramènera 26020 (normal puisque

=MAX(SI(Fonctions="Gardien";Salaires))

te ramène 12825 et

=MAX(SI(Fonctions="coffreur N1";Salaires;0))

te ramène 13195 ).
Remplace maintenant simplement la fonction somme par sommeprod (validation matricielle dans ce cas précis) :

=SOMMEPROD((MAX(SI(Fonctions="Gardien";Salaires)));(MAX(SI(Fonctions="coffreur N1";Salaires))))

.
Tu constateras que ton résultat n'est plus 26020 mais 169225875. Normal puisqu'il te ramène non pas la somme des 2 valeurs max, mais leur produit.
Dans cet exemple Sommeprod n'est bien sûr pas pertinent mais c'est simplement pour que tu puisses comprendre ses spécificités.
A+

 

[Compte Supprimé 979]

Re : la fonction sommeprod couplée à la fonction max

Bonsoir David84 🙄

sommeprod ne te sert à rien puisque tu cherches un max

Et alors !? on cherche un max mais avec 2 conditions !

Tu travailles donc sur une cellule

Ah bon ! Relis donc correctement le sujet

La formule utilisée par Hasco est donc pertinente et va te ramener l'information recherchée

Et ça sert donc à quoi de valider la formule par CTRL+MAJ+ENTREE 😕
La mienne fonctionne également ...

Ne t'en déplaise, il s'agit bien là d'un calcul matriciel, donc on peut tout à fait utiliser SOMMEPROD()

Sinon bravo pour l'explication, mais il faut avant tout savoir utiliser la fonction !
Dans ton exemple, pour combiner des postes, il ne faut pas les multiplier mais les additionner

=SOMMEPROD(MAX((B7:B506="Gardien")*(C7:C506))+MAX((B7:B506="coffreur N1")*(C7:C506)))

A+

 

[david84]

Re : la fonction sommeprod couplée à la fonction max

Re Bruno🙂

Ne t'en déplaise, il s'agit bien là d'un calcul matriciel, donc on peut tout à fait utiliser SOMMEPROD()

Tu as raison Bruno, la preuve en est que ta formule fonctionne et le seul intérêt que je vois dans cette utilisation est en effet la validation classique comme je l'avais mentionnée

sauf que sommeprod permet une validation classique

même si j'aurais dû être plus précis🙄.

Tu travailles donc sur une cellule

ce que je voulais dire, c'est que la valeur cherchée n'est pas le produit d'une somme mais simplement une valeur max, donc une info à rechercher dans une matrice si tu préfères...

Simplement, mis à part ce paramètre de validation, dis-moi STP ce que sommeprod apporte de plus ?

Si je reprends ta formule :

=SOMMEPROD(MAX((B7:B506="Gardien")*(C7:C506)))

te ramène le bon résultat, tout comme :

=SOMME(SOMMEPROD(MAX((B7:B506="Gardien")*(C7:C506))))

tout comme :

=MOYENNE(SOMMEPROD(MAX((B7:B506="Gardien")*(C7:C506))))

tout comme

=MOYENNE(SOMME(SOMMEPROD(MAX((B7:B506="Gardien")*(C7:C506)))))

, etc., et le tout toujours en validation classique...

Le but de mon propos ne t'étais pas destiné (perso, j'avais compris que tu apportais une réponse à la demande de lounes qui voulait utiliser sommeprod). Je tentais simplement d'expliquer à lounes que dans ce cas précis, sommeprod n'apportait rien de plus (si ce n'est la validation classique comme tu l'as précisé).
A+😉

Edit :

Sinon bravo pour l'explication, mais il faut avant tout savoir utiliser la fonction !
Dans ton exemple, pour combiner des postes, il ne faut pas les multiplier mais les additionner

Relis moi bien :

Dans cet exemple Sommeprod n'est bien sûr pas pertinent mais c'est simplement pour que tu puisses comprendre ses spécificités.

Si je reprends ta formule :

=SOMMEPROD(MAX((B7:B506="Gardien")*(C7:C506))+MAX((B7:B506="coffreur N1")*(C7:C506)))

Remplace sommeprod par somme (avec validation matricielle) et tu obtiens le même résultat.
Dans ce cas, une fois encore, il me semble que le seul intérêt (qui n'est pas négligeable j'en conviens !) est le type de validation, et non les spécificités de la fonction en tant que telle😉.

 

[Celeda]

Re : la fonction sommeprod couplée à la fonction max

Bonsoir,

Au délà des sommesprod et etc...la version des étoiles....du TCD dans ce ciel nuageux : une ptite lueur en sélectionnant le Max dans le salaire de base et en affichant le choix de la fonction en champ page.😎

 

[Compte Supprimé 979]

Re : la fonction sommeprod couplée à la fonction max

Bonsoir Celeda
Bien vu avec le TCD 😉😛

@David84 : l'utilisation de SOMMEPROD() n'apporte rien de plus (je n'ai jamais dis ça) sauf ... qu'il n'y a pas besoin de valider avec CTRL+MAJ+ENTREE

Combien de post ai-je pu voir avec ce problème d'oubli d'appuie sur les 3 touches, c'est pour ça que je préfère utiliser la fonction.

Mais cela n'engage que moi 😉

Et ce qui m'ennuie vraiment, c'est la grossière erreur que tu fait à la fin de ton post

Pour vraiment te rendre compte de l'intérêt de sommeprod et de sa différence avec somme dans le cas présent ...

=SOMMEPROD((MAX(SI(Fonctions="Gardien";Salaires)));(MAX(SI(Fonctions="coffreur N1";Salaires))))

et qu'apparemment tu ne veux pas corriger 🙄

A+

 

[david84]

Re : la fonction sommeprod couplée à la fonction max

Re

ce qui m'ennuie vraiment, c'est la grossière erreur que tu fait à la fin de ton post

Je t'ai répondu en complétant le message #6.
Je n'ai pris cet exemple que pour tenter de faire comprendre à lounes la spécificité du sommeprod. Cet exemple est donc à prendre hors contexte de la question initiale.
Et tu as raison, si l'on applique ta formule

=SOMMEPROD(MAX((B7:B506="Gardien")*(C7:C506))+MAX( (B7:B506="coffreur N1")*(C7:C506)))

on obtient une somme et non un produit.
Mais ce que je voulais tenter d'expliquer à lounes, c'est la particularité de sommeprod en tant que fonction et telle que son utilisation est présentée dans l'aide d'excel :SOMMEPROD(matrice1;matrice2;matrice3,...)avec des";" et non des "*" ou des "+" )car il me semble que c'est là que l'on peut réellement différencier sommeprod de somme), et ce indépendamment de son mode de validation. Je sais que toi tu le sais mais je ne sais pas si lounes le sait.
Maintenant, peut-être n'ai-je pas été assez clair, peut-être suis-je dans l'erreur🙄.
Si c'est le cas, merci de bien vouloir m'en excuser😱.

@David84 : l'utilisation de SOMMEPROD() n'apporte rien de plus (je n'ai jamais dis ça) sauf ... qu'il n'y a pas besoin de valider avec CTRL+MAJ+ENTREE

Donc nous sommes d'accord😉
A+

 

[Guest]

Re : la fonction sommeprod couplée à la fonction max

Bonjour tous,

Bruno🙂
David🙂

Interessant que tout ceci. Merci.

A+