Comment ajouter rapidement $ dans les formules Excel ?

Dans Excel, les références de cellules dans les formules sont relatives par défaut, comme "A1". Cela signifie que lorsque vous copiez ou faites glisser une formule vers un autre emplacement, les références se mettent à jour automatiquement pour refléter leurs nouvelles positions. Par exemple, si vous entrez la formule =A1+B1 dans la cellule C1, puis que vous la copiez dans la cellule H12, la formule s'ajustera automatiquement en =F12+G12. Ce comportement est utile dans de nombreuses situations mais peut provoquer des erreurs lorsque vous devez toujours faire référence à des cellules spécifiques, indépendamment de l'emplacement de la formule. Pour éviter cet ajustement automatique, vous devez changer les références de cellules en références absolues en ajoutant le symbole dollar ($), comme $A$1. Les références absolues restent constantes lorsqu'elles sont copiées ou déplacées, ce qui les rend essentielles pour les calculs utilisant des valeurs ou des plages fixes.

Ce guide présente plusieurs méthodes pratiques pour ajouter rapidement des symboles $ aux formules Excel, facilitant ainsi le contrôle de la façon dont vos formules font référence aux cellules. Le choix d'une méthode appropriée dépendra de la structure de votre feuille de calcul, du nombre de formules à convertir, de votre version d'Excel et de vos préférences de flux de travail personnel. Voici un résumé des méthodes couvertes dans ce guide :

Ajouter $ à la formule avec des raccourcis clavier

Ajouter $ à la formule avec Kutools pour Excel

Utiliser une macro VBA pour convertir en masse les références de cellules en absolu

Ajouter $ à la formule avec des raccourcis clavier

Vous pouvez facilement basculer entre différents types de références de cellules – relatives, absolues et mixtes – en utilisant des raccourcis clavier. Pour modifier une référence de cellule, sélectionnez simplement la cellule cible, cliquez dans la barre de formule, et placez le curseur à l'intérieur ou à côté de la référence de cellule que vous souhaitez modifier. Ensuite, appuyez sur la touche F4 pour parcourir les types de références disponibles. Chaque pression sur F4 met à jour la référence dans cet ordre : relative (A1), absolue ($A$1), mixte (colonne fixe) (A$1), et mixte (ligne fixe) ($A1).

Cette méthode convient pour mettre à jour rapidement des références individuelles dans une formule. Cependant, elle nécessite de sélectionner manuellement chaque référence de cellule, ce qui peut être chronophage si vous avez de nombreuses formules ou des formules complexes contenant plusieurs références de cellules. Assurez-vous également de modifier la formule directement dans la barre de formule ou en mode édition de cellule. Si le raccourci clavier ne fonctionne pas, confirmez que la touche Verrouillage Fonction (Fn) de votre clavier est correctement configurée, car certains claviers peuvent nécessiter d'utiliser Fn + F4.

Ajouter $ à la formule avec Kutools pour Excel

Le raccourci F4 ne permet de traiter qu'une seule référence de cellule à la fois, ce qui le rend inefficace lorsque vous devez mettre à jour plusieurs cellules ou toute une plage de formules. Si vous gérez plusieurs formules ou devez standardiser les types de références dans une feuille de calcul, Kutools pour Excel propose une solution efficace grâce à son utilitaire Convertir les références.

Après avoir installé gratuitement Kutools pour Excel, vous pouvez suivre ces étapes :

1. Sélectionnez toutes les cellules de formule auxquelles vous souhaitez ajouter des symboles $ (rendre les références absolues). Puis allez dans Kutools > Plus > Convertir les références. Voir capture d'écran :

2. Dans la boîte de dialogue qui apparaît, choisissez le type de référence dont vous avez besoin. Pour rendre toutes les références de cellules absolues, sélectionnez l’option “En Absolue”.

3. Cliquez OK, et toutes les références de cellules de formule sélectionnées seront mises à jour comme spécifié.
Si vos formules font référence à des données provenant de feuilles externes ou de classeurs, vérifiez les modifications pour vous assurer que les données liées sont toujours correctes, car certaines situations peuvent nécessiter de conserver certaines références relatives. Kutools vous permet également de facilement annuler les modifications et de convertir les références en types relatifs ou mixtes selon les besoins.

Cette approche est bénéfique pour traiter de grandes quantités de formules ou lorsque vous avez besoin d'opérations en lot. Kutools offre également une fonctionnalité d'annulation, facilitant la correction des actions en cas d'erreurs. 

Utiliser une macro VBA pour convertir en bloc les références de cellules en absolu

Si vous avez fréquemment besoin de convertir toutes les références de cellules dans des formules en références absolues ($A$1 format) sur une plage ou dans toute la feuille de calcul, utiliser des macros VBA est une solution efficace et flexible. Cette méthode est particulièrement utile pour les utilisateurs avancés d'Excel ou lorsqu'il s'agit de grands ensembles de données, car elle automatise le processus de conversion et peut gérer des structures de formules complexes en une seule opération. Si vos formules font référence à des feuilles ou des classeurs externes, assurez-vous que les références absolues sont appropriées pour votre scénario – parfois, des références mixtes sont préférables pour maintenir la flexibilité des formules.

1. Sur le ruban Excel, accédez à Outils Développeur > Visual Basic. Dans la fenêtre Microsoft Visual Basic pour Applications, cliquez sur Insertion > Module, puis collez la macro suivante dans le nouveau module :

Sub ConvertToAbsoluteReferences()
    Dim rng As Range
    Dim cell As Range
    Dim formulaStr As String
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the range to convert formulas to absolute references", xTitleId, rng.Address, Type:=8)
    
    Application.ScreenUpdating = False
    
    For Each cell In rng
        If cell.HasFormula Then
            formulaStr = cell.Formula
            For i = 1 To 10 ' Protects from infinite looping
                formulaStr = Replace(formulaStr, "([A-Za-z]+)([0-9]+)", "$$1$$2") ' Basic pattern replacement
            Next i
            cell.Formula = Application.ConvertFormula(formulaStr, xlA1, xlA1, xlAbsolute)
        End If
    Next
    
    Application.ScreenUpdating = True
End Sub

2. Et ensuite, cliquez sur Exécuter pour exécuter le code. Une boîte de dialogue apparaîtra vous demandant de sélectionner la plage que vous souhaitez traiter. Après confirmation, toutes les références de cellules valides dans les formules de la plage seront automatiquement converties en style de référence absolue (avec des symboles $).

Si vous rencontrez des erreurs, vérifiez que votre plage sélectionnée contient des formules. Cette macro convertit les références dans des formules Excel standard ; les formules matricielles spéciales ou les fonctions non standard peuvent ne pas être traitées comme prévu, donc passez en revue les cellules affectées après exécution. Sauvegardez toujours une copie de sauvegarde de votre classeur avant d'exécuter des macros, surtout sur des données importantes ou sensibles.

L'utilisation de VBA permet un traitement complet par lot, ce qui est adapté aux utilisateurs avancés, au personnel informatique ou à ceux travaillant dans des environnements où les installations sont limitées. Toutefois, l'exécution des macros peut être désactivée sur certains systèmes en raison des paramètres de sécurité, auquel cas il faudra activer les macros dans les paramètres de confiance d'Excel.

Résumé et suggestion : Lorsque vous décidez comment convertir les références de cellules en absolu dans Excel, tenez compte du nombre de formules que vous devez modifier et de votre familiarité avec les fonctionnalités d'Excel. Pour les formules individuelles ou les modifications ad hoc, utiliser des raccourcis clavier est rapide et pratique. Pour les opérations en lot, Kutools pour Excel et les macros VBA permettent d'économiser beaucoup de travail manuel. Vérifiez toujours les résultats après la conversion, surtout dans des feuilles de calcul complexes ou lorsque vous utilisez des outils automatisés. En cas d'erreurs ou de résultats inattendus, restaurez depuis votre sauvegarde ou utilisez la fonction Annuler si possible.