Si index equiv

Bonjour,

Je souhaite votre aide pour la recherche d'une valeur dans un tableau.

A partir d'une liste déroulante BCEF ou chaque choix (disposés de A1à I4) renvoie à une colonne de valeur, et pour une valeur référence choisie (en B26 et B27), je souhaite obtenir en résultat, la valeur se trouvant dans la colonne J correspondante. En exemple : pour B_PVC3 (correspondant à la colonne A) et pour une valeur référence de 15.5, j'obtiens 1.5 en résultat. En revanche si la valeur référence n'appartient pas à la colonne, la valeur récupérée est celle immédiatement supérieur (j'utilise equiv -1, y a t'il une autre fonction pour faire ça?) et renvoie la valeur correspondante de la colonne J.

J'ai essayé cette formule pour la colonne A qui marche : =SI(A26=A1;INDEX(J12:J23;EQUIV(B26;A12:A23;-1))).

J'ai essayé cette même formule pour la colonne B qui a 3 entrées et qui marche aussi : =SI(OU(B1=A27;B2=A27);INDEX(J8:J23;EQUIV(B27;B8:B23;-1))).

Mais quand j'essaie de combiner les deux, ça marche pour le premier tronc de la formule mais renvoie FAUX pour le deuxième, pourquoi? =SI(A1=A28;INDEX(J12:J23;EQUIV(B28;A12:A23;-1);SI(OU(B1=A28;B2=A28);INDEX(J8:J23;EQUIV(B28;B8:B23;-1)))))

D'autre part, sachant que la fonction SI ne peut être utilisé que 7 fois alors que mon tableau possède 9 colonnes, comment puis-je faire pour obtenir un résultat via une seule liste déroulante de la totalité de mon tableau? Si vous avez des suggestions je suis preneur. Merci d'avance.

97bcef.xlsx (12.64 Ko)

bonjour

avec des formules bien plus simples et une touche de vba pour la securité

115nojamandre.xlsm (18.62 Ko)

cordialement

Bonjour,

Hélas Tulipe_4 votre proposition est inexploitable car fausse pour deux raisons :

- Premièrement lors de la vérification de votre solution : si je prends B_PVC3 et que je choisis dans la liste déroulante, la valeur 36, ça me renvoie 12.5 en réponse au lieu de 6!!

Idem pour 207 qui renvoie 145 au lieu de 95.

J'ai également essayé B_PVC2 et j'ai relevé les mêmes problèmes : 299 renvoie 270 au lieu de 150 ; 119 renvoie 60 au lieu de 35; 24 renvoie 4 au lieu de 2.5....

-Deuxièmement, le fait de mettre une liste déroulante pour les valeurs ne convient pas puisque je peux être amené à saisir une valeur différente, et dans ce cas, je dois prendre la valeur supérieur de la colonne et obtenir la valeur correspondante dans la colonne J. Par exemple, en B_PVC3 je tape 40 qui n'existe pas dans la colonne A, donc c'est la valeur immédiatement supérieur qui doit prise en compte à savoir 50 et me renvoyer la valeur correspondante soit 10 (d'où mon utilisation de equiv -1). Hors si je tape une valeur différente de la liste déroulante, j'obtiens 0.

Donc retour au point de départ et je réitère mes questions :

  • Pourquoi lorsque j'utilise deux fonctions si avec index et equiv, cela ne marche pas? Alors que la formule est validé par excel?
  • y a t'il une autre solution qui fait la même chose que equiv -1?
  • sachant que j'ai 9 colonnes et que seul 7 si peuvent être utilisés quelle fonction puis-je utilisé à la place pour avoir tout mon tableau?
  • est-ce qu'EXCEL peut résoudre ce problème ou est-ce que je lui demande un truc impossible?

Merci pour vos propositions, si ce problème vous inspire; Perso je viens de me mettre aux forumules sur excel et je ne trouve pas du tout cela intuitif. Le coup de la formule si qui ne marche pas alors qu'elle est sensée être bien écrite est décourageant.

Hello

=SI(A1=A28;INDEX(J12:J23;EQUIV(B28;A12:A23;-1));SI(OU(B1=A28;B2=A28);INDEX(J8:J23;EQUIV(B28;B8:B23;-1))))

Mais honnêtement tu t'aventures dans une formule très complexe qui, si elle contient une erreur, sera impossible à "réparer". Je pense qu'il doit y avoir bien plus simple ..

Il te manquai une parenthèse après le 1er EQUIV pour le fermer et attaquer ton 2ème SI imbriqué.

Je laisse les pro s'en charger.

Cordialement,

bonjour

c'est de ma faute ,mais j'ai corrigé le sommeprod et mis une option pour que tu puisse saisir dans la cell de choix (jaune )

desormais c'est la cell en bleu clair qui pilote

je crois qu'il faut garder la liste "personnalisée" car cela evite de saisir n'importe quoi pour un type choisi sinon tu tapes une valeur et je te trouve celle qui est immediatement superieure >>>>et sa correspondance ;du coup ça servira a rien de choisir un type ;je suppose que ce n'est pas le but du jeu

95nojamandre2.xlsm (19.25 Ko)

cordialement

@ Ergotamine

Oui merci beaucoup, ça marche, le problème venait bien d'une virgule manquante après equiv... bien joué! Méchant Excel qui m'a validé cette formule sans me dire d'où venait le problème. Du coup grâce à vous je me remet en selle!

@ tulipe_4

Malheureusement (je suis désolé) ça ne marche toujours pas. Voici les erreurs que j'ai rencontrées :

- avec F_PVC3 :

36 renvoie 6 au lieu de 4

299 renvoie 150 au lieu de 120

-avec F_PR3 :

24 renvoie 2.5 au lieu de 1.5

80 renvoie 16 au lieu de 10

138 renvoie 35 au lieu de 25

207 renvoie 95 au lieu de 50

- avec E_PR2 :

36 donne 6 au lieu de 2.5

63 donne 10 au lieu de 6

+ une curiosité : j'ai mis les 3 cellules sur E_PR2 et j'ai saisi 56 en valeur: j'ai obtenu ceci : 10 , 0 , 0. Donc j'ai décelé une différence de formule entre la première et les deux autres cellules.

- avec E_PVC2 :

119 donne 35 au lieu de 25

Merci tout de même pour m'avoir consacré du temps.

Hello,

Une piste en bidouillant des formules trouvées sur le net couplées à mes maigres connaissances en excel

Cordialement,

94copie-de-bcef.xlsx (12.71 Ko)

bonjour

mon sommeprod n'etait pas adapté ..........

revenons a des choses simples

99nojamandre3.xlsm (19.76 Ko)

cordialement

Mes félicitations à vous deux, Ergotamine et tulipe_4 pour être parvenus à solutionner mon problème.

Il me reste à les décortiquer et en extraire les fonctions qui me sont inconnues pour comprendre et être en mesure éventuellement de le refaire si besoin.

En plus de cela les différentes formules utilisées dans les différentes cellules s'impactent mutuellement...ouais j'aurais jamais été capable de trouver un truc pareil! Dire que j'étais fier de ma solution, même si j'avais été obligé de faire deux cellules.

Je constate tulipe_4 que vous aimez particulièrement "sommeprod", et y en a même dans la liste déroulante (=DECALER($A$5:$A$23;;SOMMEPROD(MAX((A$1:J$4=A26)*(COLONNE(A:J))))-1;)). A première vue, la solution d'Ergotamine semble plus accessible pour mon niveau de débutant.

Bref, merci infiniment, un gros travail d'analyse m'attend pour explorer les fonctions d'Excel que j'ignore au travers de vos deux solutions.

Je vous tire à tous les deux mon chapeau!

re

c'est le seul moyen que je connaisse pour indiquer un n° de colonne surtout si il depend de plusieurs valeurs dans une matrice

equiv ne marche que pour une ligne (ou colonne)

cordialement

Tu peux m'expliquer comment fonctionne la formule ci-dessous si ce n'est pas trop long

MAX((A$1:J$4=A26)*(COLONNE($A:$J))

Merci !

bonjour

tu noteras que l'on croise 2 matrices

une qui renvoie des vrai ou faux soit 1 ou 0

l'autre qui contient des valeurs ( les n° de colonne )

si on les multiplie entre eux : les 1.... 0 ;0;0 et les n°; on va avoir une matrice virtuelle composée de plein de 0 et de une ou plusieurs fois le n° de colonne

MAX extrait le n° et comme il y avait (par exemple) 2 valeurs dans la colonne 3 cela fait deux 3 ;on en a besoin que d'1; c'est pour echapper les 0

la formule telle que tu la presentes marche aussi mais il faut la valider en matriciel ;avec sommeprod pas besoin donc on peux l'employer dans validation ou pour une MFC

cordialement

Ok je comprend mieux, donc MAX nous permet de renvoyer qu'une seule valeur .. Et ben j'ai encore du boulot ^^

Mais de toute façon FAUX*n'importe quel nombre est censé renvoyer 0 non ?

re

oui 20 fois 0= 0

cordialement

><" merci pour l'info

Alors quelle est l'utilité du Max ? Car si je comprend bien la formule comme toutes les valeurs sont différentes dans la matrice A1:J4, une seule cellule renverra 1 pour (A$1:J$4=A26).

Désolé pour le spam jsuis chiant et têtu ..

re

la matrice vvirtuelle etant composée de 0000000000100000000; il faut expliquer a excel qu'on n'en veux qu'une ;de preference celle qui es superieure a 0

d'une maniere generale quand le resultat a obtenir est noyé dans une matrice ;meme si c'est un mot ; il faut indiquer ou? ou pourquoi c'est celui la et pas un autre ;d'ou l'utilistion de MAX ;MIN ; PETITE VALEUR ; GRANDE VALEUR et meme une condition (SI)

cordialement

D'accord merci beaucoup !

Rechercher des sujets similaires à "index equiv"