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.
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 matricielleLa 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.
rédigé par Hervé Thiriez |
11 commentaire(s)
11 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
Anonyme, sur 10:19 AM
Existe-t-il un moyen de renvoyer à l'avant dernière cellule non vide?
By
Unknown, sur 4:07 PM
Pourquoi rechercher 2,1 et non pas 1,1 ou autre chose
By
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
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
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
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
Hervé Thiriez, sur 9:49 AM
Pour du texte : =RECHERCHE("zzz";A1:G1)
By
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
kayar, sur 6:04 PM
Bonjour,
Comment articuler cela avec une recherchev?
Je voudrais recuperer la derniere cellule non vide de la ligne sur laquelle apparait le critere de recherche?
By
Breda Patrick, sur 2:16 PM
Super. Génial. Un GRAND merci !
By
Bernard Lamailloux, sur 5:43 PM
Enregistrer un commentaire
<< Accueil