Somme prod et texte

frusciantefan

XLDnaute Junior
Bonjour,

J'ai fait quelques recherches préalables sur cette fonction sur ce même forum, j'ai trouvé certaines réponse mais pas dans le cadre de mon axemple raison pour laquelle je me permets d'ouvrir le sujet.

Je cherche a faire une SOMME PROD sur 4 colonnes. Comme il s'agit d'un document pour mon entreprise je ne peux pas divulguer le fichier ni le contenu, je vais donc prendre un exemple :

A B C D E
1 Employé bleu annuel 23% 0*

* =SOMMEPROD(($A$87:$A$104=$A$2)*($B$87:$B$104=$B$2)*($C$87:$C$104=$C$2)*($D$87:$D$104=$D$2);E87:E104)

Le résultat doit être le contenu "texte" du tableau auquel je fais reference, mais la formule ne me donne qu'un "0".

Comment puis je faire pour que la valeur donnée soit bien la valeur de mon tableau?

J'avais trouvé un exemple sur 2 colonnes qui marchait très bien. (j'ai donc repris cette formule pour l'étirer à 4 colonnes, plutôt que de passer par une RECHERCHEV que je ne peux pas faire sur 4 entrées...)

Quelqu'un peut il m'aider sur ce problème là?

Encore merci,

Stephane
 

Tibo

XLDnaute Barbatruc
Re : Somme prod et texte

Bonjour,

Comme son nom l'indique, la fonction SOMMEPROD fait une somme.

Elle ne peut donc retourner du texte.

On peut ruser en intégrant à la formule la fonction LIGNE qui permettra de retourner le n° de ligne qui t'intéresse et à l'intégrer ensuite dans une fonction INDEX

Sinon, il existe une autre solution en passant par une formule matricielle.

Mais pour t'en dire plus, il faudrait que tu joignes un extrait de ton fichier

@+
 

mth

XLDnaute Barbatruc
Re : Somme prod et texte

Bonjour frusciantefan,

Chez moi, la formule ne génère pas d'erreur, appliquée à l'ensemble des exemples fournis ... :rolleyes:

Mais que veux tu obtenir exactement ?

Le résultat doit être le contenu "texte" du tableau
c'est à dire concrètement ? :confused:

Par ailleurs qu'y a-t-il dans les cellules A2, B2 et autres?

Si tu fournissais un exemple avec des données bidons, et en expliquant ce que tu cherches à faire, ça serait sans doute plus facile pour t'aider, parce que là .... :confused:

Bon après-midi,

mth

Edit: :) :) Bonjour Tibo :)
Edit 2: Arf, y a plein de monde :)
Bonjour BCharef, coucou Bruno, Contente de vous croiser :)
m
 
Dernière édition:

bcharef

XLDnaute Accro
Re : Somme prod et texte

Bonjour Stephane,
Bonjour à toutes et à tous.

Un essai en E2:

Code:
SOMMEPROD(($A$87:$A$104=$A$2)*($B$87:$B$104=$B$2) *($C$87:$C$104=$C$2)*($D$87:$D$104=$D$2)*(E87:E104))

En attente d'un fichiers exemple sans données confidentielles.

A te (re) lire.

Amicalement.

BCharef


Edit : Salut Tibo & mth
 
Dernière édition:

BrunoM45

XLDnaute Barbatruc
Re : Somme prod et texte

Salut,

Si tu veux récupérer une valeur texte, tu es obligé d'utiliser INDEX()
en espérant que tu n'ai pas plusieurs lignes correpondant à tes critères

Code:
=INDEX(E$87:E$104;SOMMEPROD(($A$87:$A$104=$A$2)*($B$87:$B$104=$B$2)*($C$87:$C$104=$C$2)*($D$87:$D$104=$D$2);LIGNE(E87:E104)))

A+
 

frusciantefan

XLDnaute Junior
Re : Somme prod et texte

Voici donc le fichier en question.

1ere feuille : le test que je souhaite mettre en place en fonction de 4 paramètres.

2me feuille : le test sur lequel je me suis basé et qui fonctionne !

Je souhaite donc que dans les cellules E1, F1, G1..... s'affichent les valeurs correspondantes dans le tableau qui débute ligne 87 !

(Si je ne suis pas clair, n'hésitez pas à me le dire!!!)

Merci pour votre aide!

Stephane
 

Pièces jointes

  • TEST.XLS
    48.5 KB · Affichages: 78
  • TEST.XLS
    48.5 KB · Affichages: 84
  • TEST.XLS
    48.5 KB · Affichages: 96

mth

XLDnaute Barbatruc
Re : Somme prod et texte

Bonsoir,

Un essai avec cette formule, placée en E2 et tirée vers la droite:

Code:
{=INDEX(E$87:E$104;EQUIV($A2&$B2&$C2&$D2;$A$87:$A$104&$B$87:$B$104&$C$87:$C$104&$D$87:$D$104;0);1)}
(matricielle à valider par CTRL SHIFT ENTER)

Bonne soirée,

mth
 

mth

XLDnaute Barbatruc
Re : Somme prod et texte

Bonsoir frusciantefan,

En remplissant ton tableau avec cet exemple ...



Voici ce que fait la formule en E2 en la décortiquant:

Deux fonctions sont utilisées ici, INDEX() et EQUIV()

INDEX() renvoie la valeur d’un élément d’un tableau sélectionné à partir des index de numéros de ligne et de colonne.

Syntaxe: INDEX(matrice;no_lig;no_col)
Appliquée à ton fichier: matrice est la plage de cellules E$87:E$104
no_lig va être calculé grâce à la fonction EQUIV()
no_col est égal à 1, car on ne lit ici qu'une colonne à la fois, pas besoin de calculer ce numéro de colonne.

Concernant la fonction EQUIV(), extrait de l'aide Excel:
La fonction EQUIV recherche un élément spécifique dans une plage de cellules, puis renvoie la position relative de l’élément dans la plage.
Par exemple, si la plage A1:A3 contient les valeurs 5, 25 et 38, la formule
=EQUIV(25,A1:A3,0)
renvoie le nombre 2, car le deuxième élément de la plage est 25.

La fonction EQUIV renvoie la position de la valeur équivalente dans l’argument matrice_recherche et non la valeur en elle-même. Par exemple, EQUIV("b".{"a","b","c"};0) renvoie 2, c’est-à-dire la position relative de « b » dans la matrice {"a","b","c"}.

Syntaxe: EQUIV(valeur_cherchée, matrice_recherche, [type])

Appliquée à ton fichier:

valeur_cherchée: $A2&$B2&$C2&$D2
Ici je cherche la chaîne de caractères qui est la concaténation des cellules A à C de la ligne2, par exemple je vais cherche dans le tableau la chaîne "CADREFORFAIT HEUREANNUALISE0.8"

matrice_recherche: $A$87:$A$104&$B$87:$B$104&$C$87:$C$104&$D$87:$D$104

Un peu la même chose que la ligne ci-dessus mais concernant l'ensemble du tableau de A87 à D104. Cette partie de la formule renvoie une matrice composée de la concaténation des 4 colonnes ligne à ligne du tableau, soit:
{"CADREFORFAIT HEUREHEBDOMADAIRE0.9";"CADREFORFAIT HEUREHEBDOMADAIRE0.8";"CADREFORFAIT HEUREHEBDOMADAIRE0.6";"CADREFORFAIT HEUREHEBDOMADAIRE0.5";"CADREFORFAIT HEUREANNUALISE0.9";"CADREFORFAIT HEUREANNUALISE0.8";"CADREFORFAIT HEUREANNUALISE0.6" ... etc .... ;"MENSUELSANNUALISE0.5"}

C'est à l'intérieur de cette matrice que la fonction EQUIV() va chercher la position de la chaîne recherchée.

Le dernier argument de la fonction EQUIV() peut-être 1, -1, ou 0, ici j'ai mis 0 pour indiquer que je voulais une correspondance exacte.

Dans cet exemple, la fonction EQUIV() renvoie 6, c'est à dire que la chaîne recherchée se trouve en 6ème position dans la matrice (chaîne mise en bleu ci-dessus, qui est bien la 6ème)

La fonction INDEX() du début va donc chercher dans le tableau E$87:E$104, la 6ème position, et renvoyer 39h

Voilà frusciantefan, j'espère que ces explications pourront t'aider.

Bonne soirée à toi,

mth
 
Dernière édition:

frusciantefan

XLDnaute Junior
Re : Somme prod et texte

Bonsoir frusciantefan,

En remplissant ton tableau avec cet exemple ...



Voici ce que fait la formule en E2 en la décortiquant:

Deux fonctions sont utilisées ici, INDEX() et EQUIV()

INDEX() renvoie la valeur d’un élément d’un tableau sélectionné à partir des index de numéros de ligne et de colonne.

Syntaxe: INDEX(matrice;no_lig;no_col)
Appliquée à ton fichier: matrice est la plage de cellules E$87:E$104
no_lig va être calculé grâce à la fonction EQUIV()
no_col est égal à 1, car on ne lit ici qu'une colonne à la fois, pas besoin de calculer ce numéro de colonne.

Concernant la fonction EQUIV(), extrait de l'aide Excel:


Syntaxe: EQUIV(valeur_cherchée, matrice_recherche, [type])

Appliquée à ton fichier:

valeur_cherchée: $A2&$B2&$C2&$D2
Ici je cherche la chaîne de caractères qui est la concaténation des cellules A à C de la ligne2, par exemple je vais cherche dans le tableau la chaîne "CADREFORFAIT HEUREANNUALISE0.8"

matrice_recherche: $A$87:$A$104&$B$87:$B$104&$C$87:$C$104&$D$87:$D$104

Un peu la même chose que la ligne ci-dessus mais concernant l'ensemble du tableau de A87 à D104. Cette partie de la formule renvoie une matrice composée de la concaténation des 4 colonnes ligne à ligne du tableau, soit:
{"CADREFORFAIT HEUREHEBDOMADAIRE0.9";"CADREFORFAIT HEUREHEBDOMADAIRE0.8";"CADREFORFAIT HEUREHEBDOMADAIRE0.6";"CADREFORFAIT HEUREHEBDOMADAIRE0.5";"CADREFORFAIT HEUREANNUALISE0.9";"CADREFORFAIT HEUREANNUALISE0.8";"CADREFORFAIT HEUREANNUALISE0.6" ... etc .... ;"MENSUELSANNUALISE0.5"}

C'est à l'intérieur de cette matrice que la fonction EQUIV() va chercher la position de la chaîne recherchée.

Le dernier argument de la fonction EQUIV() peut-être 1, -1, ou 0, ici j'ai mis 0 pour indiquer que je voulais une correspondance exacte.

Dans cet exemple, la fonction EQUIV() renvoie 6, c'est à dire que la chaîne recherchée se trouve en 6ème position dans la matrice (chaîne mise en bleu ci-dessus, qui est bien la 6ème)

La fonction INDEX() du début va donc chercher dans le tableau E$87:E$104, la 6ème position, et renvoyer 39h

Voilà frusciantefan, j'espère que ces explications pourront t'aider.

Bonne soirée à toi,

mth


Ah, autre question, je viens de bidouiller un peu la base et je me rend compte que ça ne fonctionne pas.

Dans mon exemple resolu par mth, la recherche est faite par rapport aux cellules A2, B2, C2... qui accueille des listes déroulantes.

Des que je renvoie la cellule à un autre onglet, avec exactement les mêmes sources, la formule ne fonctionne plus...

Si A2 = Onglet1!C4 par exemple, cela ne fonctionne pas bien que le résultat texte soit exactement le même que s'il vient d'une liste déroulante...

Une idée pour m'aider a résoudre ce souci?

Merci à vous!
 

mth

XLDnaute Barbatruc
Re : Somme prod et texte

Bonjour frusciantefan,

Je ne comprends pas bien ce que tu as modifié, donc pas d'idée pour t'aider.
En joignant un nouvel extrait de fichier ça sera sans doute plus clair pour nous.

Bonne journée,

mth
 

Statistiques des forums

Discussions
299 845
Messages
1 979 507
Membres
206 754
dernier inscrit
Jacqueline ANDRAULT