Comment récupérer la dernière valeur ajoutée dans une colonne Excel : guide complet
Dans Excel, il est fréquent de devoir extraire la dernière valeur saisie dans une colonne, que ce soit pour afficher la dernière entrée, un total final ou un horodatage récent. Automatiser cette tâche grâce à des formules permet d'améliorer la productivité, d'éviter les erreurs liées au traitement manuel et de faciliter l'analyse dynamique de données qui évoluent continuellement.
Pourquoi savoir récupérer la dernière valeur d'une colonne est essentiel
Cette compétence est incontournable pour :
- Automatiser les rapports avec des données continuellement mises à jour.
- Éviter de faire défiler manuellement des centaines ou milliers de lignes.
- Garantir la précision des données extraites, même lorsque la source est modifiée.
- Gagner du temps lors de la préparation de tableaux de bord ou d’analyses.
Prérequis pour maîtriser les formules de récupération
- Connaissance de base des références de cellules dans Excel.
- Maitrise des fonctions courantes : NBVAL, INDEX, EQUIV (MATCH), RECHERCHE.
Formule classique pour saisir la dernière valeur : INDEX-NBVAL
La méthode la plus simple consiste à combiner INDEX et NBVAL :
- Choisissez une cellule où afficher la dernière valeur.
- Saisissez la formule suivante :
=INDEX(A:A;NBVAL(A:A))
où A:A est la colonne contenant les données. - Appuyez sur Entrée. La formule compte le nombre de cellules non vides grâce à NBVAL puis extrait la valeur correspondante avec INDEX.
- Vérifiez que la valeur affichée correspond bien à la dernière donnée active en parcourant la colonne.
Cette formule inclut cependant les en-têtes si présents. Pour éviter cela, il suffit d'ajuster la plage : =INDEX(A2:A1000;NBVAL(A2:A1000)).
Méthodes alternatives
Utiliser la fonction RECHERCHE pour plus de polyvalence
La fonction RECHERCHE est très efficace pour trouver la dernière valeur non vide, qu’elle soit numérique ou textuelle. La formule est :
Lire aussi: TVA et Excel
=RECHERCHE(2;1/(A:A<>"");A:A)
Cette formule fonctionne car elle recherche la valeur 2, qui n’existe pas, dans un vecteur binaire où 1/(A:A<>"") produit #DIV/0! pour les cellules vides et 1 pour les cellules non vides. Le résultat est donc la dernière valeur non vide.
INDEX et EQUIV pour un contrôle précis
Une autre approche mage de contrôle est :
=INDEX(A:A;EQUIV(2;1/(A:A<>"");0))
Elle nécessite dans les anciennes versions d’Excel la validation par Ctrl+Maj+Entrée pour fonctionner comme formule matricielle.
Lire aussi: Excel et la TVA : Mode d'emploi
Approche manuelle rapide
Pressez Ctrl+Flèche bas dans une colonne pour atteindre directement la dernière cellule remplie. Cette méthode est rapide pour vérifications ponctuelles mais n’est pas dynamique ni adaptable aux calculs automatiques.
Astuces pour optimiser vos formules
- Utilisez des références absolues comme $A:$A lorsque vous copiez les formules pour éviter tout décalage.
- Combinez avec SIERREUR pour gérer les colonnes vides, par exemple : =SIERREUR(INDEX(A:A;NBVAL(A:A));"Pas de données").
- Testez d'abord vos formules sur de petits jeux de données avant de les appliquer à des bases volumineuses.
Approches avancées et combinées
- Imbriquez NBVAL avec DÉCALER pour traiter des plages discontinues où des lignes sont sautées.
- Utilisez INDIRECT pour des plages dynamiques couvrant plusieurs colonnes simultanément.
- Combinez avec AUJOURD'HUI() ou MAINTENANT() pour horodater automatiquement la récupération de la dernière valeur, ce qui améliore la traçabilité.
Questions fréquentes
Peut-on récupérer la dernière valeur dans une colonne avec des cellules vides entre les données ?
Oui, les formules avec RECHERCHE sont adaptées car elles ignorent les lacunes et recherchent la dernière cellule non vide, même si des cellules vides sont présentes au-dessus.
Quelle formule est la plus rapide pour un grand volume de données ?
RECHERCHE est généralement plus rapide que INDEX-EQUIV grâce à son utilisation optimisée.
Pour maximiser la rapidité, préférez des plages définies (ex : A1:A100000) plutôt que des références à des colonnes entières (A:A).
Comment adapter la formule à plusieurs colonnes ?
Changez la plage dans la formule selon la colonne :
Lire aussi: Comment Calculer TVA 20%
- Pour la colonne B : =INDEX(B:B;NBVAL(B:B))
- Pour la colonne C : =INDEX(C:C;NBVAL(C:C))
Vous pouvez aussi concevoir des formules dynamiques combinant les références de colonnes pour extraire plusieurs dernières valeurs simultanément.
Et si ma colonne contient un en-tête ?
Le comptage NBVAL inclut les en-têtes, ce qui peut fausser le résultat. Pour exclure l’en-tête, démarrez la plage à la deuxième ligne :
=INDEX(A2:A1000;NBVAL(A2:A1000))
Peut-on récupérer la dernière valeur selon un critère précis ?
Oui. Utilisez des formules matricielles intégrant MAXSI avec INDEX et EQUIV. Par exemple :
=INDEX(A:A;EQUIV(1;(B:B="Oui");0))
trouve la dernière ligne où la colonne B contient "Oui".
Focus sur la fonction RECHERCHEV pour trouver la dernière valeur
Bien que la fonction RECHERCHEV soit traditionnellement utilisée pour rechercher une valeur précise vers le bas dans une colonne, on peut la détourner pour localiser la dernière valeur. En effet, si la clé de recherche donnée est un nombre très élevé (par exemple, un nombre que la plage ne contient pas), la fonction RECHERCHEV retourne par défaut la dernière valeur trouvée dans la colonne.
Par exemple, dans la plage A2:Z100, où la colonne A2:A100 contient les dates, et si on cherche une clé très grande dans la colonne 2, RECHERCHEV renverra la dernière donnée présente dans cette colonne. De cette manière, il est possible d’exploiter RECHERCHEV ou RECHERCHEH pour récupérer la dernière valeur d’une colonne ou d’une ligne respectivement.
Fonctionnement technique simplifié des vecteurs de recherche dans RECHERCHE
La fonction RECHERCHE utilise un vecteur de recherche pouvant contenir des erreurs #DIV/0! et des valeurs 1 et 0 issus de ESTVIDE et NON. 1/(A:A<>"") inverse donc les valeurs booléennes pour créer un vecteur binaire exploitable, où 1 signifie présence de donnée.
La recherche se fait alors sur la valeur 2, inexistante, qui fait retourner la dernière valeur correspondante.
Excel : RECHERCHEX à 2 critères, oubliez RECHERCHEV !
| Méthode | Formule type | Avantages | Limites |
|---|---|---|---|
| INDEX + NBVAL | =INDEX(A:A;NBVAL(A:A)) | Simple, rapide | Inclut souvent l’en-tête, ne gère pas plages discontinues |
| RECHERCHE | =RECHERCHE(2;1/(A:A<>"");A:A) | Gère les textes et nombres, plus robuste avec cellules vides | Formule matricielle, complexité relative |
| INDEX + EQUIV | =INDEX(A:A;EQUIV(2;1/(A:A<>"");0)) | Contrôle précis, adaptable | Nécessite Ctrl+Maj+Entrée sur versions anciennes |
| RECHERCHEV détournée | =RECHERCHEV(1E+99;A2:B100;2;VRAI) | Peut exploiter RECHERCHEV simplement | Limité aux colonnes strictes, moins intuitif |
balises:
