Casse tête :rechercher une valeur via 3 critères
Bonjour,
J'ai beau me tracasser la tête avec les fonctions basiques que j'utilise (index, equiv, rechercheh, recherchev, si, et, ou, etc.), je suis face à une difficulté qui me fait demander votre aide.
A partir de 3 critères je recherche à obtenir la valeur correspondante, mais il y a des contraintes :
- deux tableaux pouvant présenter des cellules vides
- critère 1 est tel que si la valeur donnée n'est pas listé, prendre la valeur immédiatement supérieure
- critère 2 est exactement donné
- critère 3 est tel que si la valeur n'est pas listé, prendre la valeur immédiatement inférieure.
Je ne sais absolument pas par où commencer, si donc vous avez une piste de travail (un nom de fonction par exemple...) je suis preneur.
Cordialement.
Bonjour,
=INDEX(B10:H13;EQUIV(A24;A10:A13;-1);EQUIV(C24;DECALER(B3:G3;EQUIV(B24;A4:A6;0););1))NB: Les valeurs de chaque ligne de critère 3 doivent être en ordre croissant.
Les valeurs de critère 1 doivent être en ordre décroissant.
Cordialement.
Salut Maréchal !
J'étais là-dessus pour ma part :
=INDEX(B10:H13;EQUIV(A24;A10:A13;-1);EQUIV(C24;DECALER(A4;EQUIV(B24;A4:A6;0);1;1;6)))La tienne est plus simple, bien joué.
Bonjour,
@MFerrand
Votre formule fonctionne très bien surtout si les tableaux sont collés.
=INDEX(B10:H13;EQUIV(A24;A10:A13;-1) ça je comprends : dans l'index cherche la valeur qui appartient à la plage B10:H13, à partir du critère 1 (A24) que tu trouveras dans la plage A10:A13 en valeur exacte ou immédiatement supérieur...ça ok
EQUIV(B24;A4:A6;0 renvoi la valeur exacte du critère 2 que tu trouveras dans la plage A4:A6 ça aussi ok
Auriez_vous l'amabilité de bien vouloir me traduire en bon français ce que signifie cette partie de la fonction avec décaler?
EQUIV(C24;DECALER(B3:G3;EQUIV(B24;A4:A6;0););1))
Pourquoi avoir choisi B3:G3? Qu'est-ce qui est décaler? Je sais que votre solution marche et vous en remercie mais j'aimerais bien comprendre la logique utiliser pour traiter ce problème.
@oyobrans
Merci pour votre contribution. Un bémol toutefois, si ça fonctionne pour certaines valeurs, pour d'autres situés plus loin renvoie à la cellule situé à côté gauche de la bonne.
Je prends un exemple : crit1: 30 ; crit2:240 ; crit3 : 12 (12 que je rajoute éventuellement en E 4. Votre formule renvoie 28.3 au lieu de 28.8.
Mais bon clairement, la fonction décaler semble être la clef.
En tout cas merci à vous deux.
Cordialement
Bonsoir, Salut Oyobrans !
Le résultat de la recherche se trouve dans la table B10:H13. On utilisera donc la fonction INDEX pour cette recherche, en déduisant les index de ligne et de colonne des critères fournis.
L'index de ligne dépend du critère 1 à rechercher en valeur proche, immédiatement supérieure au critère, dans la plage A10:A13 (dont les valeurs doivent donc être classées en ordre décroissant), recherche réalisable avec EQUIV, dotée du paramètre -1.
L'index de colonne dépend lui des critères 2 et 3. Le critère 3 fournira l'index de colonne à partir d'une recherche effectuée sur une ligne de la plage B4:G6, et c'est le critère 2 qui permettra de déterminer sur quelle ligne doit être recherché le critère 3, à partir d'une correspondance exacte de ce critère, cherché en A4:A6.
La recherche de critère 2 avec EQUIV (paramètre 0) fournira une valeur de 1 à 3 (plage de 3 valeurs) à mettre en correspondance avec une ligne de 1 à 3 de la plage A4:A6, soit la valeur renvoyée par EQUIV est une valeur de décalage de la ligne 0 (soit la ligne située immédiatement au-dessus de B4:G6, donc B3:G3 !) Cette ligne existant on peut la décaler pour trouver la ligne sur laquelle on devra chercher critère 3 :
DECALER(B3:G3;EQUIV(critère2;A4:A6;0);)
Evidemment, si B4:G6 s'était trouvée placée en B1:G3, on n'aurait pu décaler la ligne précédente, inexistante, et on aurait alors réduit le renvoi d'EQUIV de 1 pour définir un décalage de 0 à 2 à appliquer à la ligne B1:G1.
(Nb- le fait que B3:G3 soit fusionnée et contienne du texte est sans incidence, puisqu'elle ne sert que de repère origine du décalage, on sait à coup sûr qu'on ne cherchera rien sur cette ligne...)
Dans cette ligne décalée, on va donc pouvoir chercher critère 3 en valeur proche immédiatement inférieure (qui implique que les valeur de ces lignes soient classées en ordre croissant) :
EQUIV(critère3;LigneDécaléedeB4:G4;1)
fournira donc l'index de colonne finalisant notre recherche.
Cordialement.
Je corrige ma formule, il faut partir de A3 et non de A4 :
=INDEX(B10:H13;EQUIV(A24;A10:A13;-1);EQUIV(C24;DECALER(A3;EQUIV(B24;A4:A6;0);1;1;6);1))Voulu donner la réplique à MFerrand aussi vite qu'il avait répondu, je m'incline...
Génial, merci pour vos explications sans lesquelles DECALER resterait une énigme. Je constate que j'ai encore beaucoup de chemin avant d'atteindre la ceinture jaune d'excel, mais vos réponses encouragent à persévérer.
Merci encore.
Cordialement.
Ta question pourrait être une excellent exercice d'utilisation de EQUIV !