Erreur SOMMEPROD pour afficher une valeur qui répond à 2 conditions
C'est la 1ère fois que je poste ici depuis bien longtemps. Je me remets à Excel dans le cadre de mon nouveau travail dans la formation continue.
Je rencontre un os avec le fonction SOMMEPROD dans le fichier joint j'espère que vous pourrez m'aider. En vous remerciant par avance.
Voici ce que je cherche à obtenir comme résultat :
Afficher dans la cellule B2 de la feuille TRAITEMENT la valeur COULEUR de la feuille EXTRACTION qui répond à la double condition suivante :
Condition 1 : dans la feuille EXTRACTION, ne prendre en compte que les cellules dont la valeur ID =valeur ID de la feuille TRAITEMENT
Condition 2 : dans la feuille EXTRACTION, ne prendre en compte que les cellules avec le mot TITRE
Cette formule me renvoie #Valeur!
=SOMMEPROD((Extraction!$A$2:$A$2500=Traitement!A2)*(Extraction!$B$2:$B$2500=Traitement!$B$1)*(Extraction!$C$2:$C$2500))
- Messages
- 3'581
- Excel
- 2013, 2019, 365
- Inscrit
- 11/04/2020
- Emploi
- Formateur bureautique, dvpt fichiers
Bonjour,
SOMMEPROD va t'additionner des valeurs qui vont répondre à une (ou plusieurs) conditions. Là en l'occurrence, tu lui demandes d'additionner des N, des orange, des vert ... Je ne vois aucune chance pour que ça fonctionne.
Il n'y a qu'une seule donnée dans tout ton tableau qui répond à tes 2 conditions ?
Tu es vraiment sur la version 2003 ?
- Messages
- 3'581
- Excel
- 2013, 2019, 365
- Inscrit
- 11/04/2020
- Emploi
- Formateur bureautique, dvpt fichiers
Parce que bon, dans l'absolu, un simple tableau croisé dynamique ferait bien l'affaire, non ?
Bonjour à tous !
Une proposition :
=INDEX(Extraction!$C$1:$C$2500;MAX(SI((Extraction!$A$2:$A$2500=Traitement!A2)*(Extraction!$B$2:$B$2500=Traitement!$B$1)>0;LIGNE(Extraction!$C$2:$C$2500);1);))Si les conditions ne sont pas remplies, la formule retourne "COULEUR" (ie titre de la colonne !)
- Messages
- 3'581
- Excel
- 2013, 2019, 365
- Inscrit
- 11/04/2020
- Emploi
- Formateur bureautique, dvpt fichiers
Re,
Alors si on en est à s'amuser avec des formules, on a aussi ça ;)
=SIERREUR(INDEX(Extraction!$C$1:$C$2500;EQUIV(Traitement!$B$1;DECALER(Extraction!$B$1;EQUIV(A2;Extraction!$A$2:$A$2500;0);;NB.SI(Extraction!$A$2:$A$2500;A2));0));"pas de titre")Bonsoir à tous !
Un multi-post.... UN !
- Messages
- 3'581
- Excel
- 2013, 2019, 365
- Inscrit
- 11/04/2020
- Emploi
- Formateur bureautique, dvpt fichiers
Hello.
Bon bah s'il a eu la réponse ailleurs, on le reverra dans 5 ans 😁
Bonjour,
Merci pour vos réponse.
Pour éviter les malentendus, j'ai bien clôturé la question qui est restée sans réponse sur un autre site.
J'ai passé un bout de temps à essayer de comprendre la solution avec INDEX et EQUIV. Serait-il possible de m'expliquer le fonctionnement de cette formule pour m'aider à en saisir la syntaxe (le SI ERREUR ne me pose pas de problème).
=SIERREUR(INDEX(Extraction!$C$1:$C$2500;EQUIV(Traitement!$B$1;DECALER(Extraction!$B$1;EQUIV(A2;Extraction!$A$2:$A$2500;0);;NB.SI(Extraction!$A$2:$A$2500;A2));0));"pas de titre")
En la décomposant emboitement par emboitement, je bloque déjà sur la partie : DECALER(Extraction!$B$1;EQUIV(A2;Extraction!$A$2:$A$2500;0);;NB.SI(Extraction!$A$2:$A$2500;A2))
qui sert à déterminer la plage de recherche de la fonction EQUIV dans EQUIV(Traitement!$B$1;DECALER(Extraction!$B$1;EQUIV(A2;Extraction!$A$2:$A$2500;0);;NB.SI(Extraction!$A$2:$A$2500;A2));0)
Voici ce que j'ai pu comprendre de la solution proposée par JFL en la décomposant. J'espère que je ne me suis pas trompé dans mon interprétation. Je suis encore obligé de passer par le langage naturel pour ne pas me perdre en route.
Avec cette fonction INDEX, il s'agit de renvoyer la valeur de la cellule en fonction des coordonnées suivantes :
- la plage de la colonne : Extraction!$C$1:$C$2500
- le N° de ligne : MAX(SI((Extraction!$A$2:$A$2500=Traitement!A2)*(Extraction!$B$2:$B$2500=Traitement!$B$1)>0;LIGNE(Extraction!$C$2:$C$2500);1);)
ZOOM SUR LE CALCUL DU N° DE LIGNE
Le n° de ligne est calculé en fonction de la valeur logique VRAI/ FAUX renvoyée par le TEST suivant : (Extraction!$A$2:$A$2500=Traitement!A2)*(Extraction!$B$2:$B$2500=Traitement!$B$1)>0
En langage naturel, on peut traduire ce calcul par : si le TEST est vrai alors renvoyer la valeur de référence de la ligne la plus élevée où le TEST est vrai, dans le cas contraire renvoyer la référence de la première ligne.
Le TEST est lui même le produit de deux autres tests :
test 1 : Extraction!$A$2:$A$2500=Traitement!A2 renvoi la valeur 1 toutes les fois où le test est VRAI et 0, c'est à dire FAUX, pour les autres fois
test 2 : Extraction!$B$2:$B$2500=Traitement!$B$1 renvoi la valeur 1 toute les fois où le test est VRA et 0, c'est à dire FAUX, pour les autres fois
Si le produit des 2 tests est > 0 cela implique que les deux tests ont renvoyé la valeur 1.