Comment rechercher et renvoyer la valeur située à l’avant-dernière position d’une ligne ou d’une colonne donnée dans Excel ?
Lorsque vous travaillez avec de grandes feuilles Excel, il est souvent essentiel non seulement d’identifier la dernière valeur d’une ligne ou d’une colonne, mais surtout la valeur située en avant-dernière position. Par exemple, comme le montre la capture d’écran ci-dessous, dans la plage A1:E16, vous pourriez avoir besoin d’extraire l’avant-dernière valeur de la 6ᵉ ligne ou de la colonne B. Ce type de besoin revient fréquemment lors du suivi des modifications récentes, de l’analyse de séries chronologiques ou encore de l’examen des entrées les plus récentes — sans être la toute dernière — d’un jeu de données. Contrairement à la recherche simple de la dernière valeur, repérer l’avant-dernière peut s’avérer moins évident, notamment en présence de cellules vides ou lorsque les données sont régulièrement mises à jour. Cet article vous propose des solutions claires et détaillées, étape par étape, pour accomplir cette tâche efficacement et éviter les erreurs courantes.
- Rechercher et renvoyer la valeur en avant-dernière position dans une ligne ou une colonne donnée à l’aide de formules
- Code VBA – Utiliser une macro pour trouver la valeur en avant-dernière position dans une ligne ou une colonne spécifiée
- Autres méthodes intégrées d’Excel – Filtrer toutes les cellules vides et identifier manuellement la valeur en avant-dernière position
Rechercher et renvoyer la valeur en avant-dernière position dans une ligne ou une colonne donnée à l’aide de formules
Comme le montre la capture d’écran ci-dessus, pour rechercher et renvoyer la valeur située en avant-dernière position — que ce soit dans la 6ᵉ ligne ou dans la colonne B au sein de la plage A1:E16 — les formules Excel offrent une solution à la fois dynamique et efficace. Elles s’avèrent particulièrement utiles lorsque vos données sont régulièrement mises à jour ou que la position de l’avant-dernière valeur est susceptible de changer à la suite d’ajouts ou de suppressions. Contrairement aux méthodes manuelles, les formules assurent une mise à jour automatique de votre résultat en fonction de la dernière entrée disponible.
Rechercher et renvoyer la valeur en avant-dernière position dans la colonne B
1. Sélectionnez une cellule vide où vous souhaitez afficher la valeur en avant-dernière position. Saisissez la formule matricielle suivante dans la barre de formule, puis appuyez sur Ctrl + Maj + Entrée (pour les versions antérieures d’Excel) pour la valider en tant que formule matricielle. Sous Excel 365 ou Excel 2021, il suffit d’appuyer sur Entrée, car les formules matricielles sont désormais gérées automatiquement.
=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))
Remarque : dans cette formule, B:B fait référence à la colonne B. Si vous avez besoin de la valeur en avant-dernière position dans une autre colonne, remplacez simplement B:Bpar la référence de colonne souhaitée (par exemple, modifiez-la en)C:C pour la colonne C). Cette méthode fonctionne pour des plages contenant ou non des cellules vides, mais si des valeurs masquées ou exclues par un filtre sont présentes, veillez à tenir compte des écarts possibles. Vérifiez toujours vos plages de données si le résultat attendu n’apparaît pas.
Rechercher et renvoyer la valeur en avant-dernière position dans la ligne 6
Sélectionnez une cellule vide pour afficher le résultat correspondant à la 6ᵉ ligne, puis saisissez la formule suivante dans la barre de formule et appuyez sur Entrée pour la valider.
=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)
Remarque : dans la formule ci-dessus, $A$6 est la première cellule de la ligne 6, et 6:6désigne la ligne entière 6. Ajustez ces références si vous ciblez d’autres lignes. La formule compte dynamiquement le nombre de cellules non vides dans la ligne 6, puis effectue un décalage à partir de la cellule de départ pour localiser l’avant-dernière cellule remplie. Si votre ligne contient des formules renvoyant des chaînes vides ()""), NBVAL peut néanmoins les comptabiliser comme non vides, ce qui pourrait fausser le résultat. Pour les plages combinant constantes et formules, vérifiez attentivement les résultats afin d’éviter toute erreur.
Conseils pour l’utilisation des formules :
- Avec de grands jeux de données, l’utilisation de références complètes de colonne (par exemple,)
B:B) peut ralentir les performances. Dans la mesure du possible, restreignez la plage à la zone strictement nécessaire (par exemple,B1:B100). - Si vos données contiennent des lignes masquées ou filtrées, les formules prennent néanmoins en compte les cellules masquées ou exclues par le filtre. Pour les données filtrées, privilégiez l’utilisation de fonctions SOUS.TOTAL dédiées ou de colonnes auxiliaires.
- Si toutes les valeurs d’une ligne ou d’une colonne cible sont vides, ces formules peuvent générer une erreur ou un résultat inattendu ; veillez à ce que vos données contiennent au moins deux valeurs non vides.
Code VBA – Utiliser une macro pour trouver la valeur en avant-dernière position dans une ligne ou une colonne spécifiée
Pour les utilisateurs qui doivent fréquemment récupérer la valeur située en avant-dernière position dans des plages variables ou volumineuses, automatiser cette tâche à l’aide d’une macro VBA permet de gagner un temps considérable, notamment avec des tableaux dynamiques ou complexes. Les macros s’avèrent particulièrement utiles lorsque la taille de votre jeu de données fluctue et que vous souhaitez éviter de modifier manuellement vos formules, ou encore lorsque vous devez extraire des valeurs issues de positions différentes selon une même logique. Ci-dessous, une solution en code VBA vous invite à indiquer la ligne ou la colonne cible, puis renvoie automatiquement la valeur en avant-dernière position — sans comptage ni ajustement manuel.
1. Accédez aux Outils de développement dans le ruban Excel, cliquez sur Visual Basic pour ouvrir l’éditeur VBA. Dans l’éditeur, cliquez sur Insertion > Module. Collez ensuite le code VBA suivant dans le module nouvellement créé :
Sub GetSecondToLastValue()
Dim rng As Range
Dim arr As Variant
Dim values As Collection
Dim i As Long
Dim secondLast As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
arr = rng.Value
Set values = New Collection
If rng.Rows.Count = 1 Then
For i = 1 To rng.Columns.Count
If arr(1, i) <> "" Then
values.Add arr(1, i)
End If
Next i
ElseIf rng.Columns.Count = 1 Then
For i = 1 To rng.Rows.Count
If arr(i, 1) <> "" Then
values.Add arr(i, 1)
End If
Next i
Else
MsgBox "Please select a single row or single column range.", vbExclamation
Exit Sub
End If
If values.Count < 2 Then
MsgBox "There are less than two non-blank values in the selected range.", vbInformation
Exit Sub
End If
secondLast = values(values.Count - 1)
MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub2. Après avoir saisi le code, retournez dans Excel et exécutez la macro en cliquant sur le bouton 
Exécuter, ou en appuyant sur Alt + F8 puis en sélectionnant GetSecondToLastValue dans la liste. Une invite s’affichera pour vous demander de choisir une plage correspondant soit à une seule ligne, soit à une seule colonne (par exemple B1:B16 pour une colonne ou A6:E6 pour une ligne). Dès validation de votre sélection, la macro affichera la valeur située en avant-dernière position dans une boîte de dialogue.
Remarques :
- Cette macro compte uniquement les cellules non vides, en ignorant automatiquement les cellules vides de la ligne ou de la colonne sélectionnée.
- Si votre sélection contient moins de deux valeurs non vides, un message d’avertissement s’affiche et aucune valeur n’est renvoyée.
- La macro est conçue pour des sélections sur une seule ligne ou une seule colonne. Si vous sélectionnez plusieurs lignes et colonnes en même temps, elle vous invitera à ajuster votre sélection.
- Pour encore plus d’automatisation, vous pouvez modifier le code afin qu’il copie directement le résultat dans une cellule précise de votre feuille de calcul, sans passer par une boîte de dialogue.
Cette solution VBA s’avère particulièrement avantageuse pour les utilisateurs de tableaux dynamiques ou de jeux de données fréquemment mis à jour, car elle élimine les erreurs manuelles et automatise les tâches répétitives.
Autres méthodes intégrées d’Excel – Filtrer toutes les cellules vides et identifier manuellement la valeur en avant-dernière position
Bien que les formules et les macros offrent des méthodes automatisées pour identifier la valeur en avant-dernière position, une approche visuelle rapide s’avère parfois préférable — notamment avec des données irrégulières ou non contiguës, ou lorsque vous n’avez besoin de vérifier cette valeur qu’occasionnellement. Les outils de filtrage intégrés d’Excel vous permettent alors de masquer temporairement les cellules vides ou non pertinentes, facilitant ainsi l’identification visuelle de la valeur recherchée, sans modifier ni écrire la moindre formule.
Méthode par filtrage :
- Sélectionnez la plage de données correspondant à votre colonne ou ligne : pour les colonnes, choisissez les cellules B1:B16 ; pour les lignes, sélectionnez A6:E6 dans votre feuille de calcul.
- Dans le ruban Excel, cliquez sur Données > Filtrer pour activer les flèches de filtre. Dans l’en-tête de la colonne ou à côté de votre sélection, cliquez sur la liste déroulante du filtre.
- Décochez (Vides) pour masquer les cellules vides. La liste affichée ne contient désormais que les valeurs non vides.
- Pour une colonne, faites défiler jusqu’en bas de la liste filtrée et repérez la valeur située en avant-dernière position. Pour une ligne (après filtrage, si possible), comptez à partir de la droite afin d’identifier la deuxième valeur non vide depuis la fin.
Remarques et conseils :
- Le filtrage est la méthode la plus rapide pour les listes de petite ou moyenne taille, ou lorsque vous souhaitez confirmer visuellement la position des valeurs.
- Si votre jeu de données est très volumineux, le filtrage peut nuire aux performances et n’est ni adapté à l’automatisation ni recommandé pour une utilisation répétée.
- Lorsque des filtres sont appliqués, veillez à toujours tenir compte du bon contexte (les listes filtrées peuvent ignorer les lignes ou colonnes masquées).
- Pour supprimer les filtres, cliquez sur Effacer dans l’onglet Données.
Cette méthode se prête moins bien à une analyse dynamique continue ou à de grands jeux de données, mais elle permet une vérification manuelle transparente de l’avant-dernière valeur non vide, ce qui peut s’avérer utile pour un dépannage rapide ou une validation ponctuelle.
Articles associés :
- Comment trouver la position du premier ou du dernier chiffre dans une chaîne de texte sous Excel ?
- Comment trouver le premier ou le dernier vendredi de chaque mois dans Excel ?
- Comment utiliser RECHERCHEV pour trouver la première, la deuxième ou la nième occurrence correspondante dans Excel ?
- Comment trouver la valeur ayant la fréquence la plus élevée dans une plage sous Excel ?
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels