Si index equiv Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
n
nojamandre
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 19 juillet 2017
Version d'Excel : 2013

Message par nojamandre » 19 juillet 2017, 21:45

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.
BCEF.xlsx
(12.64 Kio) Téléchargé 19 fois
t
tulipe_4
Passionné d'Excel
Passionné d'Excel
Messages : 8'407
Appréciations reçues : 209
Inscrit le : 1 janvier 2011
Version d'Excel : 2000 2007

Message par tulipe_4 » 19 juillet 2017, 22:41

bonjour
avec des formules bien plus simples et une touche de vba pour la securité
nojamandre.xlsm
(18.62 Kio) Téléchargé 24 fois
cordialement
c'est en cherchant qu'on trouve
n
nojamandre
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 19 juillet 2017
Version d'Excel : 2013

Message par nojamandre » 20 juillet 2017, 19:52

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.
E
Ergotamine
Membre fidèle
Membre fidèle
Messages : 361
Appréciations reçues : 18
Inscrit le : 26 juin 2016
Version d'Excel : 365

Message par Ergotamine » 20 juillet 2017, 20:17

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,
t
tulipe_4
Passionné d'Excel
Passionné d'Excel
Messages : 8'407
Appréciations reçues : 209
Inscrit le : 1 janvier 2011
Version d'Excel : 2000 2007

Message par tulipe_4 » 20 juillet 2017, 20:53

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
nojamandre2.xlsm
(19.25 Kio) Téléchargé 16 fois
cordialement
c'est en cherchant qu'on trouve
n
nojamandre
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 19 juillet 2017
Version d'Excel : 2013

Message par nojamandre » 20 juillet 2017, 23:19

@ 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.
E
Ergotamine
Membre fidèle
Membre fidèle
Messages : 361
Appréciations reçues : 18
Inscrit le : 26 juin 2016
Version d'Excel : 365

Message par Ergotamine » 21 juillet 2017, 09:24

Hello,

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

Cordialement,
Copie de BCEF.xlsx
(12.71 Kio) Téléchargé 17 fois
t
tulipe_4
Passionné d'Excel
Passionné d'Excel
Messages : 8'407
Appréciations reçues : 209
Inscrit le : 1 janvier 2011
Version d'Excel : 2000 2007

Message par tulipe_4 » 21 juillet 2017, 11:43

bonjour
mon sommeprod n'etait pas adapté .......... :mrgreen:
revenons a des choses simples
nojamandre3.xlsm
(19.76 Kio) Téléchargé 21 fois
cordialement
c'est en cherchant qu'on trouve
n
nojamandre
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 19 juillet 2017
Version d'Excel : 2013

Message par nojamandre » 21 juillet 2017, 17:27

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!
t
tulipe_4
Passionné d'Excel
Passionné d'Excel
Messages : 8'407
Appréciations reçues : 209
Inscrit le : 1 janvier 2011
Version d'Excel : 2000 2007

Message par tulipe_4 » 21 juillet 2017, 17:42

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
c'est en cherchant qu'on trouve
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message
  • index et equiv
    par TouToune » 7 août 2015, 17:22 » dans Excel - VBA
    3 Réponses
    155 Vues
    Dernier message par TouToune
    9 août 2015, 19:50
  • Index/equiv ? #N/A
    par pika83 » 30 novembre 2019, 14:16 » dans Excel - VBA
    9 Réponses
    84 Vues
    Dernier message par xorsankukai
    30 novembre 2019, 17:09
  • Index equiv
    par JP54 » 29 novembre 2019, 15:55 » dans Excel - VBA
    8 Réponses
    89 Vues
    Dernier message par JP54
    30 novembre 2019, 16:25
  • index et equiv
    par ph91480 » 15 octobre 2014, 19:39 » dans Excel - VBA
    4 Réponses
    229 Vues
    Dernier message par ph91480
    20 octobre 2014, 14:24
  • Index, equiv
    par Username123 » 25 novembre 2019, 15:51 » dans Excel - VBA
    5 Réponses
    46 Vues
    Dernier message par Username123
    25 novembre 2019, 16:27
  • INDEX-EQUIV
    par lucie1206 » 25 octobre 2016, 14:41 » dans Excel - VBA
    5 Réponses
    222 Vues
    Dernier message par Steelson
    26 octobre 2016, 16:48