Monsieur Excel
Pour tout savoir faire sur Excel !

21 septembre 2008

La dernière cellule non vide

Il est parfois nécessaire, dans un bloc, de récupérer la valeur de la dernière cellule non vide du bloc. Nous prenons ici le bloc à fond bleu ciel A1:J1 et nous voulons récupérer – dans les cellules à fond jaune – la valeur de la dernière cellule non vide de ce bloc.

Comme c’est fréquemment le cas en modélisation, il y a de multiples solutions possibles. Nous vous en montrons trois ici, chacune ayant un caractère particulier. Les formules de la colonne A sont reproduites en colonne B.

Une formule matricielle

La première solution est une formule matricielle, donc validée par [Ctrl]-[Maj]-[Entrée]. Elle récupère par la fonction Index() la valeur du vecteur dont l’indice est le numéro de la dernière colonne dans laquelle on a trouvé une cellule non vide.

Cette formule nous montre – une fois de plus – que l’on peut pratiquement résoudre tous les problèmes d’Excel à l’aide de formules matricielles.

Une formule originale

La seconde formule est doublement originale.

Tout d’abord car elle crée un vecteur grâce à la formule 1/(A1 :J1<>"") : quand on évalue cette formule, on obtient {1.#DIV/0!.1.1.#DIV/0!.1.1.#DIV/0!.1.#DIV/0!}, donc un vecteur contenant un 1 pour chaque position non vide et la valeur d’erreur #DIV/0! pour chaque position vide.

La seconde originalité vient de ce que le troisième argument est le vecteur lui-même alors qu’intuitivement on l’aurait imaginé en deuxième argument plutôt qu’en troisième.

La formule la plus simple

La troisième solution est la plus simple que j’aie pu imaginer mais elle exige que les cellules concernées ne reçoivent que des valeurs positives, ce qui est quand même fréquemment le cas dans des tableaux de données Excel.

Si l’on remplace le premier argument « -1 » par « -10^33 », cette formule fonctionnera aussi si le tableau contient des nombres quelconques supérieurs à -10^33.

9 Commentaire(s):

  • Merci pour ce bel exercice de style.
    Cela m'a permis d'avancer dans la résolution de mon problème : identifier le N° de la dernière colonne non vide d'une ligne d'entêtes de tableau.
    Votre formule =RECHERCHE(2,1;1/(1:1<>"");1:1) m'a permis d'identifier le contenu de la dernière cellule d'en-tête.

    En y rajoutant la fonction INDEX comme ceci : =EQUIV(RECHERCHE(2,1;1/(1:1<>"");1:1);1:1) on obtient le numéro de la première cellule non vide.

    Encore merci

    By Anonymous jean-pierre, sur 10:19 AM  

  • Existe-t-il un moyen de renvoyer à l'avant dernière cellule non vide?

    By Blogger Olivier, sur 4:07 PM  

  • Pourquoi rechercher 2,1 et non pas 1,1 ou autre chose

    By Blogger Macinfo, sur 2:51 PM  

  • Merci bcp pour cette formule qui m'a fait gagné bcp de temps et m'a permis d'éviter de passer par une macro.

    By Anonymous Anonyme, sur 2:36 AM  

  • Merci Infiniment , c'est vraiment génial ce que vous nous avez montré .

    Grâce a votre formule j'ai pu récupéré l'adresse de la dernière cellule non vide , voila comment je me suis pris:

    =ADRESSE(LIGNE((INDEX(D:D;EQUIV(-1;D:D;-1))));3;1)

    Merci encore et @+

    By Anonymous Anonyme, sur 6:02 AM  

  • PLUS SIMPLE :

    Formule Excel qui renvoie la valeur de la dernière cellule non vide (d’une ligne ou d’une colonne.) :

    =RECHERCHE(9^9;1:1)

    ou

    =RECHERCHE(9^9;A1:G1)

    By Anonymous Anonyme, sur 10:19 AM  

  • Pour le commentaire précédent...

    Votre formule ne fonctionne que si le dernier élément est une valeur.

    Mes solutions en A3 et A4 présentent l'avantage de fonctionner même quand ce dernier élément est un texte.

    By Blogger Hervé Thiriez, sur 9:49 AM  

  • Pour du texte : =RECHERCHE("zzz";A1:G1)

    By Blogger  Marc, sur 1:47 PM  

  • Super initiative ce site. Cela dit,je n'arrive pas à utiliser la première formule pour du texte et ce même en faisant ctrl+shift+enter..

    Pouvez vous m'aider ?

    By Anonymous kayar, sur 6:04 PM  

Enregistrer un commentaire

<< Accueil