[XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

[ameisen]

Bonjour
Pour la petite histoire, j'ai une base de données concernant des profilés métallique et j'aimerais que Excel me fournisse le profilé qui va bien en fonction du résultat de mes calculs)
Fin de la petite histoire

La feuille "Feuil1 est la feuille d'affichage des résultats)
La feuille "HE" est la feuille qui contient la base de donnée de recherche des résultats

Dans la feuille "Feuill1"
Donc en bleu je sélectionne le critère dans une liste déroulante (par exemple Iy)
D'après mes calculs il vaut "500"
Donc en vert cela doit me mettre l'unité qui va bien avec le critère que j'ai choisi (à savoir cm4) pour le Iy

Donc en fonction du critère et du calcul je cherche la fonction Excel qui va chercher dans la feuille de calcul "HE" la valeur dans la colonne "Iy" immédiatement supérieure à "500" et cela doit me mettre que "dans mon cas" c'est un IPE120A qui va bien
(en effet d'apres la feuille de calcul HE, le Iy d'un HE120 A vaut 606.2>500)
Enfin je sèche coté formule excel à utiliser pour tout cela... entre rechercheV index...

Merci pour votre aide

 

[hoerwind]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Bonjour,

Essaie cette formule :

=INDEX(HE!A3:A7;EQUIV(D3;DECALER(HE!A3;;EQUIV(B3;HE!A3:N3);4)))

 

[ameisen]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Ca fonctionne sauf que si je mets 300 à la place de 500 ca devrait m'afficher un IPE 100 et pas ca me laisse un IPEA140

 

[R@chid]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Salut @ tous,
Formule : =INDEX(Zone;EQUIV(D3;DECALER(Zone;;EQUIV(B3;Critere;0));-1))
voir PJ.
Amicalement

 

[ameisen]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Bonsoir
Ca fonctionne pas si je mets "500" ou "400" comme valeur
Une erreur de code ?

 

[R@chid]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Salut,
je peux te repondre si tu peux me dire que veux tu obtenir si on met 400 et si on met 500??
Amicalement

 

[ameisen]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Bien pour un IPE 100 A on a Iy qui vaut "349.2"
pour un IPE 120 A on a Iy qui vaut "606.2"
pour un IPE 140 A on a Iy qui vaut "1033.0"

Donc si je rentre valeur calculé = 400 on devrait avoir un IPE 120A (idem si = 500, puisque 400 ou 500>349.2 et comme 400 ou 500<606.2 ca marche avec IPE 120 A)
Par contre si je mets valeur calculé = 300 c'est un IPE A 100 (puisque Iy IPE100=349.2>300)
Par contre si je mets valeur calculé = 650 c'est un IPE A 140 que je devrais obtenir puisque 650>606,2
Etc
Enfin voila pour la logique du fonctionnement je cherche la valeur qui soit immédiatement supérieur à la valeur calculée et donc le profilé qui correspond à cette même valeur

 

[R@chid]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Salut @ tous,
en C4 :

=SIERREUR(INDEX(Zone;EQUIV(MIN(SI(DECALER(Zone;;EQUIV(VRAI;EXACT(B3;Critere);0))>=D3;DECALER(Zone;;EQUIV(VRAI;EXACT(B3;Critere);0))));DECALER(Zone;;EQUIV(VRAI;EXACT(B3;Critere);0));0));"")

en E3 :

=INDEX(DECALER(Critere;1;);EQUIV(VRAI;EXACT(B3;Critere);0))

Formules matricielles @ valider par Ctrl+Maj+Entree
les plages :
Critere ==> =HE!$B$3:$N$3
Zone ==> =HE!$A$5:$A$7
Combien de Likes, merite la formule??
Amicalement

 

[R@chid]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Salut,
j'ai oublie de poster le fichier.
Voir PJ, j'ai simplifier avec champ dynamique nomme Plage.
Amicalement

Edit : Ajout de fichier *.xlsx

 

[ameisen]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Merci encore ca marche super
Par contre j'ai un peu complexifié la base de donnée mais ca ne fonctionne pas je maitrise assez mal l'argumentation utilisée
Voir piece jointe

 

[R@chid]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Bonsoir,
ceux sont des formules matricielles qui se valident par la combinaison des touches : Ctrl+Maj+Entree.
Voir PJ, j'ai definis la plage Zone dynamique, tu peux ajouter des donnes comme tu veux.
Amicalement

 

[ameisen]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Oki ca marche super bien sauf que je comprends mal comment tout cela fonctionne et comment modifier la plage de donnée ca s'applique ou le CTRL+MAJ+ENTREE ?

 

[R@chid]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Bonsoir,

ca s'applique ou le CTRL+MAJ+ENTREE ?

Quand tu saisies la formule, a la place de valider par simple Entree on doit valider par Ctrl+Maj+Entree.
si tu valides par simple Entree, la formule renvoie la valeur d'erreur : #VALEUR, mais tu ne la voie pas, car on a gere les erreurs par la fonction SIERREUR(), cette derniere qui renvoie "" VIDE on cas d'erreur.
alors si tu as deja valide par simple Entree, tu peux mettre le curseur dans la cellule contenant la formule et tapes la touche F2, et revalides par Ctrl+Maj+Entree ==> maintiens les 2 touches Ctrl et Maj enfonces et tapes la touche Entree.

comment modifier la plage de donnée

J'ai pas bien compris, la question a plusieurs dimensions, on prend la largeur et la hauteur de ces dimensions :

le champ Critere : =HE!$B$3:$N$3
c'est simple et n'a pas besoin d'explication.

le champ Zone : =DECALER(HE!$A$4;;;NBVAL(HE!$A:$A)-1)
la fonction =DECALER(réf;lignes;colonnes;[hauteur];[largeur]) :
réf ==> A4
lignes ==> Omis
colonnes ==> Omis
ils sont omis parcequ'on veut que l'origine reste A4,
[hauteur] ==> NBVAL(HE!$A:$A)-1
c'est le nombre de valeurs dans la colonne A -1, le -1 c'est pour la valeurr contenue dans la cellule A2 ==> "designation".
[largeur] ==> Omis, parceque notre cham va avoir une seule colonne donc pas besoin de largeur.

le champ Plage : =DECALER(Zone;;EQUIV(VRAI;EXACT(Feuil1!$B$3;Critere);0))
réf ==> le champ Zone
lignes ==> Omis,
colonnes ==> EQUIV(VRAI;EXACT(Feuil1!$B$3;Critere);0)
j'ai utilise EQUIV(VRAI;EXACT(Feuil1!$B$3;Critere);0), formule matricielle parceque tu as par exemple les criteres "Iz" et "iz" en majuscule et miniscule, si ce n'est pas le cas on peut faire simple EQUIV(Feuil1!$B$3;Critere;0).
ces 2 dernieres fonrmules vont renvoyer un nombre entier et c'est l'equivalent de Feuil1!$B$3 dans le champ Critere, est ca va etre le nombre de colonnes de decalge du champ Zone.

comment tout cela fonctionne

Formule : =SIERREUR(INDEX(Zone;EQUIV(MIN(SI(Plage>=D3;Plage));Plage;0));"")
j'ai deja parle de la fonction SIERREUR(), mais pas de prob on repete :
=SIERRERU(valeur;valeur-si-erreur)
valeur ==> INDEX(Zone;EQUIV(MIN(SI(Plage>=D3;Plage));Plage;0))
valeur-si-erreur ==> "", "" veut dire vide, donc en cas d'erreur elle renvoie vide, on peut mettre ce qu'on veut par exemple "Attention il y une erreur", et comme ca on aura la phrase : Attention il y une erreur, en cas d'erreur.
on passe a ==> INDEX(Zone;EQUIV(MIN(SI(Plage>=D3;Plage));Plage;0))
la fonction INDEX(matrice;numero de ligne;numero de colonne)
matrice ==> Zone, champ de resultats
numero de ligne ==> EQUIV(MIN(SI(Plage>=D3;Plage));Plage;0)
numero de colonne ==> Omis pareceque notre champ de resultats se compose d'une seule colonne, donc on n'a pas besoin.
on passe au numero de ligne,
EQUIV(MIN(SI(Plage>=D3;Plage));Plage;0), cette formule renvoie un nombre comme j'ai deja dis.
MIN(SI(Plage>=D3;Plage)) ==> va chercher dans le champ Plage le valeur directement superieur ou egale a la valeur en D3, si elle trouve la valeur de D3 dans le champ Plage, elle va renvoyer cette valeur, sinon elle va renvoyer la plus petite valeur directement superieur a la valeur en D3, et apres il va nous revoyer le rang de cette derniere valeur dans le champ Plage.

et voila j'espere bien etre clair sur mes explication, c'etait plus difficile que de faire la formule.
Amicalement

 

[mapomme]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Bonjour ameisen et Rachid,

Un essai avec une formule ordinaire (pas matricielle) hé bien non! J'avais mal examiné les données (merci Rachid) => voir ici

Le tableau comporte deux valeurs qui sont inférieures à celles du-dessus!

 

[R@chid]

Re : [XL 2010] Trouver la valeur supérieur dans la matrice et renvoyer le résultat

Salut @ tous,
Bonjour mapomme :
j'utilise souvent la formule =PETITE.VALEUR(Plage;NB.SI(Plage;"<"&D3)+1) a la place de la formule matricielle =MIN(SI(Plage>=D3;Plage)), mais je ne sais pas vraiment comment n'est elle pas venu sur ma tete hier? ICI
pour : DECALER(HE!A4:A23;0;EQUIV(B3;Crit;0) il faut la faire en matricielle parcequ'il y a les "Iy", "iy", "Iz" et le "iz" dans la plage de criteres ce qui va nous obliger de passer par EXACT().
Amicalement