Comment obtenir systématiquement la valeur de la cellule située juste au-dessus lors de l’insertion ou de la suppression d’une ligne dans Excel ?
Dans Excel, faire référence à la valeur de la cellule située juste au-dessus est une exigence courante pour créer des cumuls progressifs, effectuer des comparaisons mois après mois ou simplement garantir la cohérence des données à mesure que votre feuille évolue. Généralement, vous pouvez référencer la cellule au-dessus à l’aide d’une formule simple comme =D5, mais cette solution présente des limites : si vous insérez ou supprimez des lignes, votre formule risque de ne plus pointer vers la nouvelle cellule « au-dessus » comme prévu. Comme illustré ci-dessous, l’insertion d’une nouvelle ligne au-dessus peut rompre la continuité ou conserver la référence vers la cellule d’origine au lieu de s’ajuster automatiquement à la nouvelle position.
Pour résoudre ce problème et garantir que votre formule récupère toujours la valeur de la cellule située juste au-dessus — même après l’insertion ou la suppression de lignes — plusieurs approches s’offrent à vous. Chacune présente des compromis différents, selon la complexité de votre feuille, votre besoin d’une mise à jour automatique ou manuelle, ainsi que votre niveau de familiarité avec VBA ou les macros.
Table des matières :
- Obtenez toujours la valeur de la cellule située au-dessus lors de l’insertion ou de la suppression de lignes avec une formule
- Mettez à jour automatiquement la valeur d’une cellule à partir de la cellule située juste au-dessus, à l’aide d’une macro VBA pilotée par événement (toujours dynamique)
Obtenez toujours la valeur de la cellule située au-dessus lors de l’insertion ou de la suppression de lignes avec une formule
Pour résoudre ce problème facilement, sans recourir aux macros ni à une configuration complexe, utilisez une formule qui fait référence dynamiquement à la cellule située juste au-dessus — quelle que soit la modification apportée à vos lignes. Cette formule combine les fonctions INDIRECT et ADRESSE d’Excel pour que la référence « suive » toujours la cellule au-dessus, même si des lignes sont insérées ou supprimées. Idéale pour les feuilles dont la structure change fréquemment, notamment lorsque vous ajoutez régulièrement de nouvelles données en haut ou au milieu d’une liste.
Saisissez la formule suivante directement dans la cellule où vous souhaitez toujours récupérer la valeur située au-dessus (par exemple en B6 si vous voulez faire référence à B5) :
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))Après avoir saisi la formule, appuyez sur Entrée. La cellule active affichera instantanément la valeur de la cellule située juste au-dessus, comme illustré ci-dessous :
Désormais, dès que vous insérez une nouvelle ligne n’importe où au-dessus de la cellule contenant la formule, celle-ci se met automatiquement à jour pour afficher la valeur de la nouvelle cellule située juste au-dessus. Vos formules restent ainsi toujours parfaitement synchronisées, même en cas d’insertions ou de suppressions de lignes. Consultez la capture d’écran suivante à titre de référence :
Explication des paramètres et conseils :
- Cette formule récupère automatiquement la valeur de la cellule située juste au-dessus de celle qui la contient : ainsi, si elle est placée en B6, elle affichera toujours le contenu de B5, même après l’insertion ou la suppression de lignes au-dessus.
- Si vous utilisez cette formule dans la première ligne de vos données (par exemple en A1), elle risque de tenter de récupérer des données depuis une ligne inexistante et de renvoyer une erreur
#REF!. Pour éviter ce problème, ajoutez une gestion d’erreur — par exemple avec=IF(ROW()=1,"",INDIRECT(ADDRESS(ROW()-1,COLUMN())))— afin d’afficher une cellule vide en première ligne. - Gardez à l’esprit que la fonction INDIRECT est volatile ; son utilisation excessive dans des feuilles de calcul très volumineuses peut ralentir les calculs.
- Cette formule est idéale lorsque vous souhaitez maintenir un lien strict avec la position de la ligne, quelle que soit l’évolution de la structure de votre feuille.
Dépannage et suggestions récapitulatives :
Si votre formule ne se met pas à jour comme prévu après l’insertion ou la suppression de lignes, vérifiez qu’elle est bien saisie dans la cellule prévue. Assurez-vous également de ne pas utiliser de références absolues (comme $A$1), qui restent figées. Si vous obtenez des erreurs #REF! dans la première ligne, envisagez d’utiliser une formule conditionnelle comme indiqué précédemment. Pour une automatisation avancée, ou si vous avez besoin que la valeur soit copiée (et non simplement référencée), découvrez ci-dessous la solution basée sur une macro VBA pilotée par événement, qui propose une approche dynamique fondée sur du code.
Mettez à jour automatiquement la valeur d’une cellule à partir de la cellule située juste au-dessus, à l’aide d’une macro VBA pilotée par événement (toujours dynamique)
Si vous avez besoin d’une solution où la valeur d’une cellule se met automatiquement à jour pour correspondre à celle de la cellule située au-dessus, quelle que soit l’insertion ou la suppression de lignes — et que vous souhaitez gérer ce comportement sans copier de formules — une macro VBA pilotée par événement s’avère efficace. Cette méthode convient particulièrement aux utilisateurs à l’aise avec l’activation des macros et permet une mise à jour dynamique : dès que vous sélectionnez une cellule ou une plage spécifique, ou que vous modifiez une valeur, la macro définit instantanément la cellule cible pour qu’elle corresponde toujours à la valeur située au-dessus, assurant ainsi une saisie cohérente quelle que soit l’évolution de votre feuille. Cela est particulièrement utile dans les modèles où vous souhaitez appliquer un modèle fixe ou une valeur par défaut dans chaque nouvelle ligne.
Voici comment configurer cette solution à l’aide de l’événement Worksheet_Change :
1. Cliquez avec le bouton droit sur l’onglet de la feuille de calcul où vous souhaitez activer cette fonctionnalité, puis sélectionnez Afficher le code. L’éditeur Microsoft Visual Basic pour Applications s’ouvrira directement sur le module de la feuille concernée.
2.Copiez-collez le code VBA suivant dans la fenêtre du module de feuille :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
On Error Resume Next
' Set the range you want to monitor (for example, B2:B100)
Set WatchRange = Intersect(Target, Me.Range("B2:B100"))
If Not WatchRange Is Nothing Then
Application.EnableEvents = False
Dim cell As Range
For Each cell In WatchRange
' Avoid the first row, or adjust as needed
If cell.Row > 1 Then
cell.Value = Me.Cells(cell.Row - 1, cell.Column).Value
End If
Next cell
Application.EnableEvents = True
End If
End SubRemarques sur les paramètres : Remplacez "B2:B100" dans Me.Range("B2:B100")par la plage réelle sur laquelle vous souhaitez appliquer ce comportement (vous pouvez utiliser une colonne entière, par exemple)"B:B", mais limiter la plage améliore les performances et évite les remplacements involontaires).
3. Fermez l’éditeur VBA. Désormais, chaque fois qu’une cellule est modifiée, insérée ou que la feuille est mise à jour au sein de votre plage limitée, Excel mettra automatiquement à jour ces cellules pour qu’elles reflètent la valeur située juste au-dessus. Par exemple, si vous insérez une ligne à la ligne 5, toutes les cellules surveillées de la colonne B à partir de ce point récupéreront automatiquement la valeur de la cellule directement au-dessus de leur nouvelle position.
- Attention : ce code remplacera toute saisie manuelle des valeurs dans la plage surveillée. Utilisez-le avec précaution si certaines cellules contiennent des formules ou si vous souhaitez conserver vos saisies initiales.
- Si vous souhaitez continuer à utiliser cette macro VBA dans le classeur à l’avenir, enregistrez-le au format Classeur prenant en charge les macros (.xlsm).
- Le code piloté par événement ne fonctionne que dans la feuille de calcul dont le module contient la macro (et non sur toutes les feuilles, sauf si ce code est ajouté dans chaque module de feuille).
- Si vous souhaitez que la mise à jour s’effectue dès la sélection d’une cellule, plutôt qu’après la modification d’une valeur, vous pouvez utiliser l’événement Worksheet_SelectionChange avec une logique similaire.
Dépannage et suggestions récapitulatives :
Si le script VBA ne semble pas fonctionner après copie, assurez-vous que les macros sont activées dans votre classeur et que vous avez collé le code dans le bon module de feuille (et non dans un module standard). Si vous rencontrez des erreurs ou si Excel se fige, vérifiez que Application.EnableEvents est défini sur False avant vos modifications automatiques de cellules, puis rétabli sur True après, afin d’éviter les boucles récursives. Pour d’autres comportements avancés ou un contrôle plus fin, envisagez un script personnalisé adapté à la structure de vos données.
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