Comment toujours obtenir la valeur de la cellule au-dessus lors de l'insertion ou la suppression de lignes dans Excel ?
Dans Excel, faire référence à la valeur de la cellule directement au-dessus est une exigence courante pour créer des totaux cumulés, des comparaisons mois par mois, ou simplement maintenir la cohérence des données à mesure que votre feuille de calcul évolue. Normalement, vous pouvez référencer la cellule au-dessus en utilisant une formule simple comme =D5, mais cette solution présente certaines limitations : si vous insérez ou supprimez des lignes, votre formule peut ne pas continuer à référencer la nouvelle cellule « au-dessus » comme vous pourriez vous y attendre. Comme illustré ci-dessous, insérer une nouvelle ligne au-dessus peut briser la continuité ou garder la référence liée à la cellule d'origine au lieu de s'ajuster à la nouvelle position.
Pour résoudre ce problème et garantir que votre formule récupère toujours la valeur de la cellule immédiatement au-dessus, même après avoir inséré ou supprimé des lignes, il existe plusieurs approches que vous pouvez adopter. Chaque méthode présente différents compromis en fonction de la complexité de votre feuille de calcul, de savoir si vous souhaitez une mise à jour automatique ou une formule manuelle, et de votre confort avec l'utilisation de VBA/macros.
Table des matières :
- Toujours obtenir la valeur de la cellule au-dessus lors de l'insertion ou la suppression de lignes avec une formule
- Mettre à jour automatiquement la valeur d'une cellule à partir de la cellule au-dessus en utilisant une macro événementielle VBA (toujours dynamique)
Toujours obtenir la valeur de la cellule au-dessus lors de l'insertion ou la suppression de lignes avec une formule
Pour résoudre ce problème de manière simple sans nécessiter de macros ni de configuration complexe, vous pouvez utiliser une formule qui fait référence dynamiquement à la cellule au-dessus, peu importe comment vos lignes changent. La formule utilise les fonctions INDIRECT et ADRESSE d'Excel afin que la référence « suive » toujours la cellule au-dessus, même si les lignes sont déplacées en raison d'insertions ou de suppressions. Cela la rend particulièrement adaptée aux feuilles de calcul où vous modifiez fréquemment la structure des lignes, comme ajouter de nouvelles données en haut ou au milieu d'une liste.
Entrez la formule suivante directement dans la cellule où vous souhaitez toujours obtenir la valeur de la cellule au-dessus (par exemple, dans la cellule B6 si vous voulez référencer B5) :
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))Appuyez sur Entrée après avoir tapé la formule. La cellule actuelle affichera instantanément la valeur de la cellule immédiatement au-dessus, comme indiqué ci-dessous :
Maintenant, si vous insérez une nouvelle ligne n'importe où au-dessus de la cellule contenant la formule de référence, la formule se recalculera et affichera toujours la valeur de la nouvelle cellule au-dessus. Ainsi, vos formules seront toujours à jour, quelles que soient les insertions ou suppressions de lignes. Consultez la capture d'écran suivante pour référence :
Explication des paramètres et conseils :
- Cette formule récupérera la valeur de la cellule directement au-dessus de la cellule de formule actuelle — donc son utilisation dans B6 reflétera toujours B5, même si des lignes sont insérées ou supprimées au-dessus.
- Si vous utilisez cette formule dans la première ligne de vos données (comme A1), elle pourrait essayer de tirer des données d'une ligne inexistante et retourner une erreur
#REF !. Vous pouvez éviter cela en ajoutant une gestion d'erreurs, par exemple avec=SI(LIGNE()=1;"";INDIRECT(ADRESSE(LIGNE()-1;COLONNE())))pour afficher une cellule vide dans la première ligne. - Gardez à l'esprit qu'INDIRECT est une fonction volatile, donc dans des feuilles de calcul extrêmement volumineuses, un usage excessif peut ralentir les calculs.
- L'utilisation de cette formule fonctionne bien lorsque vous voulez préserver une relation stricte avec l'emplacement de la ligne, peu importe comment la structure de votre feuille change.
Dépannage et suggestions de résumé :
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 entrée dans la cellule prévue. Assurez-vous également de ne pas utiliser de références de cellule absolues (telles que $A$1), qui sont statiques. Si vous rencontrez #REF! des erreurs dans la première ligne, envisagez d'utiliser une formule conditionnelle comme mentionné précédemment. Pour une automatisation avancée, ou si vous avez besoin que la valeur soit copiée, et non seulement référencée, consultez la solution de macro événementielle VBA ci-dessous pour une approche dynamique basée sur le code.
Mettre à jour automatiquement la valeur d'une cellule à partir de la cellule au-dessus en utilisant une macro événementielle VBA (toujours dynamique)
Si vous avez besoin d'une solution où la valeur de la cellule est toujours automatiquement mise à jour pour correspondre à celle de la cellule au-dessus, quelle que soit l'insertion ou la suppression de lignes — et que vous souhaitez que ce comportement soit géré sans copier de formules —, l'utilisation d'une macro événementielle VBA est efficace. Cette méthode convient parfaitement aux utilisateurs à l'aise avec l'activation des macros et permet une mise à jour dynamique : chaque fois que vous sélectionnez une cellule ou une plage spécifique, ou lorsque vous modifiez une valeur, la macro peut immédiatement définir la cellule cible pour qu'elle corresponde toujours à la valeur au-dessus, assurant une saisie de données cohérente, peu importe comment votre feuille de calcul évolue. C'est particulièrement utile dans les modèles où vous souhaitez un modèle fixe ou une configuration de valeurs par défaut dans chaque nouvelle ligne.
Voici comment configurer cela en utilisant l'événement Worksheet_Change :
1. Faites un clic droit sur l'onglet de la feuille de calcul où vous souhaitez cette fonctionnalité, puis sélectionnez Afficher le code. L'éditeur Microsoft Visual Basic for Applications s'ouvrira sur le module de feuille de calcul approprié.
2. Copiez et collez le code VBA suivant dans la fenêtre du module de feuille de calcul :
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 SubNotes sur les paramètres : Remplacez "B2:B100" dans Me.Range("B2:B100") par la plage réelle où vous souhaitez ce comportement (vous pouvez la définir sur une colonne entière, par exemple "B:B", mais réduire la plage améliore les performances et évite les écrasements involontaires).
3. Fermez l'éditeur VBA. Maintenant, chaque fois qu'une cellule est modifiée, insérée ou que la feuille de calcul est mise à jour dans votre plage spécifiée, Excel mettra automatiquement à jour ces cellules pour refléter la valeur au-dessus. Par exemple, si vous insérez une ligne à la ligne 5, toutes les cellules surveillées dans la colonne B à partir de ce point prendront la valeur de la cellule directement au-dessus de leur nouvelle position.
- Soyez prudent : ce code écrasera les valeurs entrées manuellement dans la plage surveillée. Utilisez-le avec précaution si vous avez des cellules de formule ou si vous souhaitez conserver les entrées originales.
- Si vous souhaitez continuer à utiliser cette macro VBA dans le classeur à l'avenir, vous devez enregistrer le fichier en tant que classeur prenant en charge les macros (.xlsm).
- Le code d'événement ne fonctionnera que sur le module de feuille de calcul où la macro est collée (pas sur toutes les feuilles à moins que du code ne soit ajouté à chaque module de feuille).
- Si vous souhaitez que la mise à jour se produise lors de la sélection d'une cellule plutôt qu'à la modification d'une valeur, vous pouvez utiliser Worksheet_SelectionChange et une logique similaire.
Dépannage et suggestions de résumé :
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 obtenez des erreurs ou constatez que Excel se fige, vérifiez que Application.EnableEvents est défini sur False avant vos modifications de cellules automatiques et réinitialisé à True après pour éviter les boucles récursives. Pour d'autres comportements avancés ou un contrôle plus fin, envisagez un script personnalisé basé sur votre structure de données.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels