Donnée à l'intersection d'une colonne (variable) et d'une ligne

Bonjour,

Je cherche - désespérément - la formule qui me permettra d'obtenir le paramètre C qui correspond à un taux d'intérêt T (quelle que soit sa valeur) pour une durée D donnée.

Pour ce faire, j'ai construit un tableau (voir fichier joint) dans lequel, pour une valeur de T donnée (parmi celles retenues dans le tableau), figurent en colonnes les différentes valeurs de D et C.

Comme le montre l'exemple joint, si la valeur de T est fixée à l'avance (ici: 2%), je trouve la valeur de C (ici : 0,933) pour une valeur de D donnée (ici : 6,4) en combinant les fonctions INDEX & EQUIV.

Là où cela se complique, c'est quand on ne connait pas à l'avance les valeurs de T & de D (qui résultent d'un calcul amont).

Il faudrait que je puisse utiliser une formule qui, à chaque occurrence,

1/ détermine la colonne correspondant à la valeur T obtenue par le calcul amont;

2/ trouve la ligne sur laquelle est positionnée la valeur de D (elle aussi obtenue par un calcul amont);

3/ récupère la valeur de C se trouvant dans la colonne à droite de celle de D et sur la même ligne que celle de la valeur de D.

Dit autrement, en se référant à l'exemple, quelles que soient les valeurs qui seront indiquées en cellules F24 & F25, la valeur en cellule F26 doit être la bonne.

Voilà, voilà . . .

Si en cette période de confinement, un(e) expert(e) d'XL trouve dans ce "problème à résoudre" de quoi occuper ses méninges pour trouver la solution, je l'en remercie par avance.

Louis34

Bonjour Louis34,

Si je vous ai bien compris, essayez la formule ci-dessous

=INDIRECT(ADRESSE(EQUIV($F$25;INDIRECT(CONCATENER(ADRESSE(4;EQUIV($F$24;$A$2:$J$2;0)-1);":";ADRESSE(22;EQUIV($F$24;$A$2:$J$2;0)-1));1);-1)+3;EQUIV($F$24;$A$2:$J$2;0));1)

Bonjour njhub,

Tout d'abord merci pour votre solution qui fonctionne parfaitement dans le fichier d'exemple que j'avais joint (. . . et pour sa transmission ultra rapide)

Maintenant, je me heurte à une autre difficulté qui est celle de la transposition de la formule dans le fichier réel dans lequel elle doit être utilisée.

Ma connaissance d'XL ne me permettant pas de comprendre les rôles des fonctions INDIRECT & ADRESSE, je suis incapable de connaitre la cause (et de la supprimer) du #N/A qui apparaît quand je recopie la formule (après avoir enlevé les "$" pour l'adapter à la position réelle du tableau dans l'onglet avec lequel je dois travailler) -> voir l'onglet "Durées résiduelles" du fichier joint.

Mais la place définitive de la formule ne sera pas dans cet onglet (dans lequel je l'ai, dans un premier temps, recopiée pour m'assurer qu'elle fonctionnait bien quelles que soient les valeurs de T figurant dans la ligne 224).

Elle devra se positionner dans la cellule P5 de l'onglet "Fichier réel" (et être copiée des milliers de fois dans cette colonne) pour au final être utilisée pour obtenir la valeur en colonne R (en faisant R = P x Q).

Si vous pouvez m'aider à nouveau pour savoir ce qui cloche, j'en serai ravi.

Merci d'avance.

Louis34

Bonjour Louis34,

Si je vous ai bien compris, essayez la formule ci-dessous en P5 de l'onglet "Fichier réel"

=SI(L5=0;"";INDIRECT(ADRESSE(EQUIV($Q5;INDIRECT(CONCATENER(ADRESSE(226;EQUIV($N5;$'Durées résiduelles'.$A$224:$AL$224;0)-1;4;1;"Durées résiduelles");":";ADRESSE(244;EQUIV($N5;$'Durées résiduelles'.$A$224:$AL$224;0)-1;4;1;"Durées résiduelles"));1);-1)+225;EQUIV($N5;$'Durées résiduelles'.$A$224:$AL$224;0);4;1;"Durées résiduelles");1))

Bonsoir njhub,

J'ai porté la formule en P5 mais ça bloque.

Je joins en annexe la zone surlignée en gris que l'assistant XL indique comme incorrecte.

Le mieux serait peut être de me retourner le fichier Exemple XL COMPLET avec la formule - fonctionnant correctement - en P5.

Remarque : Dans le fichier réel dans laquelle elle doit être utilisée, le nom de l'onglet "Durées résiduelles" est écrit avec un "R majuscule". J'ai donc procédé à la correction de sa typographie dans la formule qui apparaît dans le fichier joint.

Bonne soirée

Bonsoir Louis34,

Quand vous déciderez vous à faire parvenir le fichier dans lequel cette formule doit être appliquée?

C'est lassant d'essayer de deviner, vous donnez des cas concrets qui ne le sont pas, puisqu'à chaque fois vous invoquez un autre fichier, c'est lassant... Il aurait mieux valu ne rien demander que de m'empêcher de vous aider

Confirmez que c'est bien Q5 (D=6.3) qu'il faut trouver en "'Durées résiduelles'!I226:I244" (T=2.0%) pour afficher C=0.933

Je vous renvoie votre deuxième fichier, avec la formule fonctionnelle

Bonsoir njhub,

Cette fois-ci ça fonctionne parfaitement. Grand merci !

Désolé pour mes "tâtonnements" avec le fichier Exemple dus, d'une part, à mon inexpérience du forum et, d'autre part, à la taille du fichier à exploiter (+ de 30 000 lignes) qui m'a dissuadé de le joindre en l'état.

Maintenant que tout est OK, j'ai une dernière demande : connaîtriez-vous un tuto ou une doc qui me permettrait de comprendre les rôles des fonctions ADRESSE & INDIRECT ?

Bravo pour votre efficacité et encore merci.