recherche derniere valeur

marmotte18

XLDnaute Impliqué

• 27 Juillet 2008

• #16

Re : recherche derniere valeur

Bonjour mécano41,

La formule de Raja aurait du s'écrire :

Code:

=RECHERCHE(9^9;Feuil2!B6:B15000)

pour être plus cohérente avec la question posée.

En plus, il est indispensable de n'avoir que des nombres dans la plage analysée (pas de texte mais il peut y avoir des cellules vides) et cette fonction est faite pour des cellules triées. Ce qui n'est le cas à priori !

9^9 veut dire 9 puissance 9, c'est à dire 387420489.

Il recherche ce nombre dans la plage B6:B15000. Encore faut-il être sûr qu'il n'existe pas ! Il part du principe qu'il y a peu de chance qu'il y figure.

Si la fonction RECHERCHE ne peut pas trouver ce nombre, elle utilise la plus grande valeur inférieure ou égale à celui-ci. Ce qui, entre parenthèse, correspond forcément à la dernière valeur recherchée dans le cas où l'on a un plage triée.

Il se trouve, qu'apparemment il ramène toujours la dernière valeur même lorsque la plage analysée n'est pas triée.

C'est une astuce non prévue par la fonction elle même et cela semble fonctionner sous toutes les réserves déjà formulées.

 

Vote positif 0 Vote négatif

M

mécano41

XLDnaute Accro

• 27 Juillet 2008

• #17

Re : recherche derniere valeur

marmotte18 à dit:

...il se trouve, qu'apparemment il ramène toujours la dernière valeur même lorsque la plage analysée n'est pas triée.

C'est une astuce non prévue par la fonction elle même et cela semble fonctionner sous toutes les réserves déjà formulées.

Cliquez pour agrandir...

j'en était arrivé à la même conclusion...mais c'est justement sur ces deux mots que je bute également...😀...

Cordialement

 

Vote positif 0 Vote négatif

marmotte18

XLDnaute Impliqué

• 27 Juillet 2008

• #18

Re : recherche derniere valeur

Bonjour,

mécano41 à dit:

Pour Marmotte18,

Je n'avais pas regardé ta macro...mais cela devrait suffire :

Code:

Sub DernièreCellule()
      Worksheets("Feuil1").Range("i9").Value = Worksheets("Feuil2").Range("B15000").End(xlUp).Value
End Sub

Cordialement

Cliquez pour agrandir...

Ce code ne fonctionne pas avec le problème posé car à supposer que la plage B6:B15000 soit vide et qu'en B3 il y ait la valeur 1215, il va me ramener 1215. Ce qui n'est pas normal puisque cette valeur se trouve hors de la plage à analyser.

 

Vote positif 0 Vote négatif

M

mécano41

XLDnaute Accro

• 27 Juillet 2008

• #19

Re : recherche derniere valeur

Dans ce cas, en rajoutant un petit test, ça doit aller :

Code:

Sub DernièreCellule()
Worksheets("Feuil1").Range("i9").Value = ""
With Worksheets("Feuil2")
    If .Range("B6").End(xlDown).Row <= .Range("B15000").End(xlUp).Row Then Worksheets("Feuil1").Range("i9").Value = .Range("B15000").End(xlUp).Value
End With
End Sub

Vérifie s'il n'y a pas un autre cas particulier...

Cordialement

 

Vote positif 0 Vote négatif

marmotte18

XLDnaute Impliqué

• 27 Juillet 2008

• #20

Re : recherche derniere valeur

Réponse à mécano41,

Ta réponse fonctionne presque dans tous les cas, sauf 1. Quand tu saisis une valeur sur la 15000 ème position, il ne la détecte pas.

=> il faut donc remplacer 15000 en 15001 à 2 endroits dans ta formule.

 

Vote positif 0 Vote négatif

Tibo

XLDnaute Barbatruc

• 27 Juillet 2008

• #21

Re : recherche derniere valeur

Hello,

Une solution avec une formule matricielle :

Code:

=INDEX(A1:A65535;MAX((A1:A65535<>"")*LIGNE(A1:A65535)))

à valider par CTRL + MAJ + ENTREE

Fonctionne que les cellules contiennent des nombres ou du texte.

Petit détail : si on met 65536 (à la place de 65535), Excel reconnait qu'il s'agit d'une colonne complète et transforme le A1:A65536 en A:A
Or, les formules matricielles ne font pas bon ménage avec les références à des colonnes entières.

@+

 

Vote positif 0 Vote négatif

R

Raja

XLDnaute Accro

• 27 Juillet 2008

• #22

Re : recherche derniere valeur

Bonjour,

Merci Maître marmotte !!! pour toutes ces leçons. Un conseil Calme-toi, c'est dimanche et il faut beau.

 

Vote positif 0 Vote négatif

marmotte18

XLDnaute Impliqué

• 27 Juillet 2008

• #23

Re : recherche derniere valeur

Réponse à Tibo,

Intéressant ta formule, j'ai d'ailleurs du mal à la comprendre. Il faut croire que je fais un blocage sur les formules matricielles.

Toujours est-il, pour qu'elle donne vraiment le résultat escompté dans le cadre du problème, il faudrait :

Code:

{=INDEX(Feuil2!B6:B15000;MAX((Feuil2!B6:B15000<>"")*(LIGNE(Feuil2!B6:B15000)-5)))}

Ce qui n'empêche pas que je ne la comprends pas ! Elle marche cependant !

Pourrais-tu, s'il te plaît m'en expliquer le principe ?

Merci par avance

 

Dernière édition: 27 Juillet 2008

Vote positif 0 Vote négatif

marmotte18

XLDnaute Impliqué

• 27 Juillet 2008

• #24

Re : recherche derniere valeur

Réponse à Raja,

C'est gentil de me prendre pour un maître que je ne suis pas. Bon nombre de personnes ici sont 10 000 fois plus calées que moi.

Mon problème, c'est que je ne prends pas les solutions comme bonnes, sans les avoir vérifiées et si possible comprises.

Donc si je pense voir une faille dans l'explication ou si je ne comprends pas, je le dis. C'est bien la fonction du forum, non ?

Désolé si je t'incommode, ce n'est absolument pas mon intention.

Cordialement et sans rancune 🙂

 

Dernière édition: 27 Juillet 2008

Vote positif 0 Vote négatif

A

abcd

XLDnaute Barbatruc

• 27 Juillet 2008

• #25

Re : recherche derniere valeur

Bonjour,

Petite remarque pour marmotte 18
La colonne ne doit pas être triée.
Si elle était triée, il suffirait d'employer la fonction MAX.
Il suffit qu'aucune valeur soit supérieure à 9^9 pour que la denière valeur numérique soit renvoyée.
9^9 peut être remplacé par une autre valeur plus grande si nécessaire, par exemple 99^9 ou 9^99

Pour du texte c'est la même formule en remplaçant 9^9 par "zz".
Il est très rare qu'un texte débute par zz.

Pourtant, à mon humble avis, la formule de Tibo reste la meilleure, bien que matricielle.

abcd

 

Vote positif 0 Vote négatif

marmotte18

XLDnaute Impliqué

• 27 Juillet 2008

• #26

Re : recherche derniere valeur

Réponse pour abcd,

Merci pour les précisions apportées. Je constate effectivement que cela marche quand la colonne n'est pas triée. Bravo ! C'est pourquoi, j'ai qualifié cela d'astuce, on pourrait même dire d'expérience si tu préfères.

Mais il est vrai que dans l'aide de la fonction RECHERCHE, il est dit :

Important Les valeurs de l'argument vecteur_recherche (qui est le 2ème argument de la fonction) doivent être placées en ordre croissant : ...,-2, -1, 0, 1, 2, ..., A-Z, FAUX, VRAI. Sinon, la fonction RECHERCHE peut donner une valeur incorrecte. Les majuscules les et minuscules sont traitées indifféremment.

Cliquez pour agrandir...

Par ailleurs, il est intéressant de savoir que l'on peut mettre "ZZ" si on est en présence de texte.

Que met-on si l'on a un mélange de nombres et de texte ?

 

Vote positif 0 Vote négatif

A

abcd

XLDnaute Barbatruc

• 27 Juillet 2008

• #27

Re : recherche derniere valeur

Re,

Si tu as des valeurs numériques et alphabétiques il te reste la formule de Tibo !

Pour le faire avec la fonction RECHERCHE j'ai vu que Monique a proposé une formule en son temps.
Si tu ne la trouves pas, je dois l'avoir dans mes archives, mais ce sera pour demain.

abcd

 

Vote positif 0 Vote négatif

Tibo

XLDnaute Barbatruc

• 28 Juillet 2008

• #28

Re : recherche derniere valeur

Bonjour,

Une solution basée sur 9^9 et zz non matricielle :

Code:

=INDEX(A:A;MAX(EQUIV(9^9;A:A);EQUIV("zz";A:A)))

@+

 

Vote positif 0 Vote négatif

marmotte18

XLDnaute Impliqué

• 28 Juillet 2008

• #29

Re : recherche derniere valeur

Bonsoir,

Réponse à Tibo :

Tibo à dit:

Bonjour,

Une solution basée sur 9^9 et zz non matricielle :

Code:

=INDEX(A:A;MAX(EQUIV(9^9;A:A);EQUIV("zz";A:A)))

@+

Cliquez pour agrandir...

Pour que cette formule corresponde au problème posé, il faudrait mettre, à mon avis :

Code:

=INDEX(Feuil2!B6:B15000;MAX(EQUIV(9^9;Feuil2!B6:B15000);EQUIV("zz";Feuil2!B6:B15000)))

Par ailleurs :

• S'il n'y a que des nombres, cela ne fonctionne pas
• S'il n'y a du texte, cela ne fonctionne pas

=> il doit y avoir plus d'une valeur dans la plage Feuil2!B6:B15000 et il faut impérativement un mélange de nombres et de texte.

Sous ces conditions, j'ai l'impression que cela fonctionne bien !

 

Dernière édition: 29 Juillet 2008

Vote positif 0 Vote négatif

Tibo

XLDnaute Barbatruc

• 28 Juillet 2008

• #30

Re : recherche derniere valeur

re,

exact en cas d'absence de données ou de l'un ou l'autre typer de données, d'où cette nouvelle proposition :

Code:

=INDEX(A:A;MAX(SI(ESTNA(EQUIV(9^9;A:A));0;EQUIV(9^9;A:A));
SI(ESTNA(EQUIV("zz";A:A));0;EQUIV("zz";A:A))))

retourne 0 quand la colonne est vide

A adapter à la colonne B

Cela dit, la formule matricielle me paraît un peu (beaucoup) plus pratique (et logique).


@+

 

Vote positif 0 Vote négatif