XL 2016 Rechercher la valeur la plus à droite dans une plage en fonction d'un seuil

[niiiiiiiiiico]

Bonjour

Je cherche à prendre la valeur la plus à droite dans ma ligne L13:AC13 mais en excluant les valeurs <=100

Cette formule permet de trouver la valeur la plus à droite mais sans le critère qui exclue les valeurs <=100
=RECHERCHEH(9^9;L13:AC13;1)

Comment faire un rechercheh avec un si du coup ?

Merci

 

[chris]

Bonjour

Une recherche approximative (avec 1 en dernier argument) nécessite des données classées ce qui d'après ta question n'est pas le cas

 

[ALS35]

Bonjour à tous,
Essaie :

=RECHERCHEH(9^9;SI(L13:AC13<=100;"";L13:AC13);1)

Formule matricielle, à valider pat Ctrl+Maj+Entrée
Cordialement

 

[job75]

Bonjour à tous,

Ou aussi avec validation matricielle de la formule :

=RECHERCHEH(9^9;SI(L13:AC13>100;L13:AC13);1)

A+

 

[JHA]

Bonjour à tous,

Une autre approche, formule matricielle

=INDEX($C$2:$O$2;MAX(SI($C$2:$O$2>100;COLONNE($C$2:$O$2)-2)))

JHA

 

[job75]

@JHA il vaut mieux utiliser GRANDE.VALEUR(xxx;1) au lieu de MAX(xxx).

Car s'il n'y a pas de valeurs > 100 ta formule renvoie la valeur en C2.

 

[patricktoulon]

bonjour à tous

 

[job75]

Allons Patrick, il faut adapter la formule matricielle de JHA en enlevant le "-2".

 

[patricktoulon]

re
Bonjour @job75
des explications sur les parties de formule seraient sympa

 

[JHA]

Bonjour à tous,

=INDEX($C$2:$O$2;MAX(SI($C$2:$O$2>100;COLONNE($C$2:$O$2)-2)))

Le début de plage à contrôler débute à la colonne "C" donc la 3ème colonne, on soustrait deux (-2) pour donner une plage de 1 à x afin de donner le résultat exact avec la fonction index().

COLONNE($C$2:$O$2)
{3.4.5.6.7.8.9.10.11.12.13.14.15}

COLONNE($C$2:$O$2)-2
{1.2.3.4.5.6.7.8.9.10.11.12.13}

même chose avec grande.valeur

JHA

 

[patricktoulon]

re
ha ben oui !!
merci JHA
j'avais zapé index en effet

 

[ALS35]

Bonjour à tous,
@patricktoulon
Si tu souhaites te faire un catalogue de formules, quelques autres possibilités (hors gestion d'erreur) :

Pour s'affranchir de la position en colonne :

=INDEX(2:2;GRANDE.VALEUR(SI(C2:O2>100;COLONNE(C2:O2));1))

Pour s'affranchir de la position en colonne et de la validation matricielle :

=INDEX(2:2;AGREGAT(14;6;COLONNE(C2:O2)/(C2:O2>100);1))
qui permettra d'avoir également la première valeur (voire la nième) avec :
=INDEX(2:2;AGREGAT(15;6;COLONNE(C2:O2)/(C2:O2>100);1))

Pour ceux qui ont Excel 365, une formule simplissime :

=PRENDRE(FILTRE(C2:O2;C2:O2>100;"");;-1)
ou
=LET(p;C2:O2;PRENDRE(FILTRE(p;p>100;"");;-1))
et pour la première valeur :
=PRENDRE(FILTRE(C2:O2;C2:O2>100;"");;1)

voili voilou,
Cordialement

 

[patricktoulon]

re bonjour @ALS35
merci pour les exemple
=INDEX(A1:F1 ; 'ok pigé
AGREGAT(argument 14 =grandevaleur ok ; argument option = 6 quoi ;
COLONNE(A1:F1)/(A1:F1>100);1)) <--c'est quoi cette formule explique

 

[ALS35]

Re,
Pas certain que tu aies tout pigé !
1)
=INDEX(A1:F1 ;
Si tu mets A1:F1 ça marche sans correction de colonne, si tu déplaces ta plage en B1:G1 par exemple il faut corriger le numéro de colonne de -1 comme ceci :
=INDEX(B1:G1;GRANDE.VALEUR((B1:G1>100)*COLONNE(B1:G1);1)-1)
Si tu mets toute la ligne 1, tu n'a pas à faire cette correction quelle que soit la position de ta chaine :
=INDEX(1:1;GRANDE.VALEUR((B1:G1>100)*COLONNE(B1:G1);1))
2)
La fonction AGREGAT
Le 14 pour GRANDE.VALEUR avec le 1 à la fin qui va avec ;1)) ,
le 6 pour ne pas tenir compte des erreurs générées dans la formule
la formule COLONNE(A1:F1)/(A1:F1>100), le (A1:F1>100) retourne Vrai ou Faux, converti en 1 ou 0 dans les calculs, si on divise le numéro de colonne par 1 on a donc le numéro de colonne, si on divise par 0 on a une erreur DIV/0 qui est ignorée vu qu'on a mis le paramètre précédent à 6, on obtient ainsi le plus grand numéro de colonne des cellules d ayant une valeur >100, et dont on récupère la valeur avec INDEX(1:1
Cordialement

 

[patricktoulon]

a ben voilà
la c'est clair
je me posais la question du 6 l'aide n'est pas implémenté dans excel 2013 par contre le 14 j'ai la liste

ok pour index tu a raison

je comprends mieux maintenant car j'avais essayé chaque segment de la formule a part et le dv0 me rendait perplexe
je comprends mieux il est zappé par l'option 6

merci @ALS35
good job!