Discussion :

[Just-Soft]

Bonjour

je sais que ça parait assez facile à 1ière vue mais voilà ce que je dois réaliser :

Etape Bg Bd Appréciation
1 0 1 Mauvais
1 1 2 Satisfaisant
1 2 4 Bon
1 4 7 Excellent
2 1 2 Mauvais
2 2 4 Satisfaisant
2 4 7 Bon
2 7 11 Excellent
3 2 4 Mauvais
3 4 7 Satisfaisant
3 7 11 Bon
3 11 14 Excellent
4 4 7 Mauvais
4 7 11 Satisfaisant
4 11 14 Bon
4 14 20 Excellent

j'ai cette liste, constituée de 4 colonnes; étape, borne gauche, borne droite et appréciation. j'obtiens un résultat d'une autre feuille de calcul que j'arrange ainsi :

Résultat 3.8
Etape 2
Appréciation ?

je dois obtenir l'appréciation correspondante au résultat et à l'étape. je m'explique, puisque l'étape est 2 je dois restreindre mon champs d'application à la sous matrice suivante

2 1 2 Mauvais
2 2 4 Satisfaisant
2 4 7 Bon
2 7 11 Excellent

puis je dois faire un test pour savoir que le résultat est compris entre 2 et 4 (borne gauche et borne droite), pour enfin avoir la bonne appréciation qui est dans ce cas "Satisfaisant".



Merci à vous tous.

[Pierre Fauconnier]

Bonjour

Avec tes données en A1 (intitulés), tu peux utiliser

=INDEX(D2:D17;SOMMEPROD((A2:A17=2)*(B2:B17<=3.8)*(C2:C17>=3.8)*LIGNE(A2:A17))-1)

pour autant qu'il n'y ait qu'une ligne validant tes conditions. S'il y a plusieurs lignes, il faut envisager d'autres solutions, comme par exemple le filtre, automatique ou élaboré, le TCD, ...

[Just-Soft]

Bonjour

Avec tes données en A1 (intitulés), tu peux utiliser

pour autant qu'il n'y ait qu'une ligne validant tes conditions. S'il y a plusieurs lignes, il faut envisager d'autres solutions, comme par exemple le filtre, automatique ou élaboré, le TCD, ...

Re

très très bien et merci encore, impeccable. mon résultat ne tient que sur une ligne. je dois abuser de ta gentillesse grand Monsieur et te demander une petite explication si possible. j'ai, en effet, regardé l'aide mais j'avoue ne pas comprendre ce que fait exactement sommeprod dans ce cas là.

[Pierre Fauconnier]

INDEX(Plage, Position) permet de récupérer la valeur qui se trouve à la position renseignée dans la plage. Pour la plage D2:D17, tu cherches la valeur se trouvant en 6ième position (D2 est en première position, D3 en deuxième, etc).

Pour trouver la position, on utilise SOMMEPROD, qui permet de renvoyer une donnée selon certains critères. A l'intérieur de SOMMEPROD, tu observes des parenthèses qui contiennent des (in)égalités, des assertions, que Excel vérifie pour renvoyer les valeurs VRAI ou FAUX.

Ces valeurs VRAI et FAUX prennent respectivement les valeurs numériques 1 et 0 lorsqu'on les utilise dans une opération arithmétique. LIGNE() renvoie la ligne de la cellule qui utilise la fonction. On peut donc réaliser le produit des différentes valeurs trouvées (1 est neutre pour la multiplication et 0 est absorbant), de manière à récupérer 0 sauf pour la ligne qui correspond aux critères. Dans ton cas, on trouve 7, alors qu'on attend 6, et donc on retire 1.

SOMMEPROD (pour SOMME des PRODuits) est au départ une formule permettant des sommes de produits de matrice, mais elle est ici utilisée de manière détournée, en permettant de se passer des colonnes de construction. C'est pourquoi SOMMEPROD() utilise les plages, ou plutôt les vecteurs A2:A17, B2:B17, C2:C17 et D2:D17 qui possèdent le même nombre de lignes.

[Just-Soft]

waw, très propre l'explication. merciiiiiiiiiiiiiiiiii , y a même un schéma ! je commence à voir plus clair. Bonne journée à toi Monsieur Excell

[Just-Soft]

Re-bonjour

je me suis aperçus que dans la formule suivante

=INDEX('Paramètres et Listes'!D3:D18;SOMMEPROD(('Paramètres et Listes'!A3:A18=B46)*('Paramètres et Listes'!B3:B18<=B45)*('Paramètres et Listes'!C3:C18>=B45)*LIGNE('Paramètres et Listes'!A3:A18))-1)

la fonction LIGNE ne rend pas la bonne ligne si ma liste commence pas en première ligne ?

je m'explique, quand je mets ma liste au début de la feuille, j'ai cette formule :

=INDEX('Paramètres et Listes'!D2:D17;SOMMEPROD(('Paramètres et Listes'!A2:A17=B46)*('Paramètres et Listes'!B2:B17<=B45)*('Paramètres et Listes'!C2:C17>=B45)*LIGNE('Paramètres et Listes'!A2:A17))-1)

dans ce cas mon résultat est bon mais si je veux insérer des lignes au dessus de ma liste de sélection mais là j'ai des résultats inattendus voire même un erreur de type "#ref".

je dois absolument ajouter des lignes au début de ma liste, alors comment forcer la fonction LIGNE à me rendre la ligne correspondante à mon choix ?

merci par avance.

[Pierre Fauconnier]

Tu utilises LIGNE() en paramètre de la fonction INDEX, pour renvoyer la valeur de la cellule qui se trouve en énième position.

Pour rappel, INDEX(Vecteur;Position) renvoie la valeur de la cellule se trouvant à la position renseignée dans le vecteur. Si ton vecteur commence en ligne 2, INDEX(Vecteur;1) renvoie la première valeur du vecteur, soit celle qui se trouve sur la ligne 2. Tu dois donc adapter x dans Ligne()-x en fonction de la configuration de ton classeur.

[Just-Soft]

Re

merci à toi, ok je viens de faire et ça marche à merveille mais je me demande si on ne peut pas automatiser ça par une autre fonction que ligne ?

[Pierre Fauconnier]

Si. Avec une autre approche, tu peux automatiser sans devoir utiliser Ligne().

Avec EQUIV(), tu peux rechercher la position de 1 dans la matrice constituée par le produit des résultats des conditions. Voilà la formule à valider en matricielle:

=INDEX(D2:D17;EQUIV(1;(A2:A17=2)*(B2:B17<=3.8)*(C2:C17>=3.8);0))

[Just-Soft]

Désolé mais ça ne marche pas ! ça me donne "#valeur!", j'ai tout essayé pour y remédier mais j'y arrive pas.

[Pierre Fauconnier]

Montre la formule que tu utilises

[GerardCalc]

Bonjour,

L'erreur #VALEUR! est symptomatique d'une validation de formule matricielle par entrée.

Voilà la formule à valider en matricielle:

C'est à dire avec la combinaison de touches ctrl+maj+entrée.

A+

[Just-Soft]

Montre la formule que tu utilises

avec plaisir

voilà Monsieur

=INDEX('Paramètres et Listes'!D318;EQUIV(1;('Paramètres et Listes'!A3:A18=B46)*('Paramètres et Listes'!C3:C18<=B45)*('Paramètres et Listes'!C3:C18>=B45);0))

faut dire que j'ai aussi essayé ça seul

=EQUIV(1;('Paramètres et Listes'!A3:A18=B46)*('Paramètres et Listes'!C3:C18<=B45)*('Paramètres et Listes'!C3:C18>=B45);0)

et toujours même erreur !!!
donc c'est equiv qui n'est pas bien évalué ou y'a erreur dans la formule malgré que pour moi je vois que tout est dans l'ordre !

[Just-Soft]

Bonjour,

L'erreur #VALEUR! est symptomatique d'une validation de formule matricielle par entrée.

C'est à dire avec la combinaison de touches ctrl+maj+entrée.

A+

hein ......
et en langage clair, que dois-je faire ?

[GerardCalc]

Après saisie de la formule, au lieu de valider par entrée, tu valides l'opération par entrée mais avec les touches ctrl et maj enfoncée.

Si ta formule est déjà saisie et validée, double-clic dans la cellule ou F2 et tu valides comme indiqué plus haut.

La formule s'affiche alors entre {} comme ceci :

{=EQUIV(1;('Paramètres et Listes'!A3:A18=B46)*('Paramètres et Listes'!C3:C18<=B45)*('Paramètres et Listes'!C3:C18>=B45);0)}

Attention, tu ne dois pas saisir les accolades manuellement.

A+

[Just-Soft]

Après saisie de la formule, au lieu de valider par entrée, tu valides l'opération par entrée mais avec les touches ctrl et maj enfoncée.

Si ta formule est déjà saisie et validée, double-clic dans la cellule ou F2 et tu valides comme indiqué plus haut.

La formule s'affiche alors entre {} comme ceci :

{=EQUIV(1;('Paramètres et Listes'!A3:A18=B46)*('Paramètres et Listes'!C3:C18<=B45)*('Paramètres et Listes'!C3:C18>=B45);0)}

Attention, tu ne dois pas saisir les accolades manuellement.

A+

merci à toi, je viens de faire, et j'ai de belle {} autour de mon expression mais au lieu du "#valeur!", il me renvois ça "#N/A". je viens de voir dans l'aide et y'a 7 causes à ça !!!

je me penche sur le problème mais si quelqu'un a une idée sur la résolution sans passer en revue les 7 possibilités je suis preneur

merci à vous

[GerardCalc]

Comme tu le dis, il peut y avoir plusieurs causes.



Les 2 principales sont :

• Aucune ligne ne répond au critères.
• S'agissant d'une formule matricielle, les matrices doivent avoir le même nombre de lignes.

Si la formule est celle que tu as posté, c'est aucune des 2.

Pour t'en dire plus il faudrait voir le fichier...

Edit :

=EQUIV(1;(A3:A18=B46)*(C3:C18<=B45)*(C3:C18>=B45);0)

Tu fais 2 fois référence à la même colonne (C), est-ce normal ?

[Just-Soft]

re

alors voilà, j'ai fais une évaluation pas à pas et j'ai eu ce message "erreur due à une valeur non disponible" j'avoue ne pas avoir bien saisis la chose.
je note que le dernier écran de l'évaluation de la fonction EQUIV m'a donné ce résultat

EQUIV(1;{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;};0)

il parait clair que EQUIV n'a pas trouvé de correspondance puisque tout ce qui est entre {} est 0 !!

[GerardCalc]

C'est bien le premier cas.


J'ai édité mon message, je n'avais pas vu que tu avais posté en même temps.

Edit :

=EQUIV(1;(A3:A18=B46)*(C3:C18<=B45)*(C3:C18>=B45);0)

Tu fais 2 fois référence à la même colonne (C), est-ce normal ?

[Just-Soft]

Comme tu le dis, il peut y avoir plusieurs causes.


Les 2 principales sont :

• Aucune ligne ne répond au critères.
• S'agissant d'une formule matricielle, les matrices doivent avoir le même nombre de lignes.

Si la formule est celle que tu as posté, c'est aucune des 2.

Pour t'en dire plus il faudrait voir le fichier...

sans faute Monsieur
la formule en question se trouve dans la feuille 3 cellule B49.

merci par avance.