Tout le monde sait que vous pouvez entrer une formule dans Excel. (Que serait une feuille de calcul sans formules, après tout?) Si vous utilisez des références d’adresse dans une formule, ces références sont automatiquement mises à jour si vous insérez ou supprimez des cellules, des lignes ou des colonnes et ces modifications affectent la référence d’adresse d’une manière ou d’une autre. Prenons, par exemple, la formule simple suivante:

=IF(A7=B7,"YES","NO")

Si vous insérez une cellule au-dessus de B7, la formule est automatiquement ajustée par Excel pour qu’elle apparaisse comme ceci:

=IF(A7=B8,"YES","NO")

Et si vous ne voulez pas qu’Excel ajuste la formule, cependant? Vous pouvez essayer d’ajouter des signes dollar à l’adresse, mais cela n’affecte que les adresses dans les formules qui sont copiées ultérieurement; cela n’affecte pas la formule elle-même si vous insérez ou supprimez des cellules qui affectent la formule.

La meilleure façon de rendre les références de formule non ajustables? consiste à modifier la formule elle-même pour utiliser différentes fonctions de feuille de calcul. Par exemple, vous pouvez utiliser cette formule dans la cellule C7:

=IF(INDIRECT("A"&ROW(C7))=INDIRECT("B"&ROW(C7)),"YES","NO")

Cette formule permet de construire une adresse en fonction de la cellule dans laquelle la formule apparaît. La fonction ROW renvoie le numéro de ligne de la cellule (C7 dans ce cas, la valeur 7 est donc renvoyée), puis la fonction INDIRECT est utilisée pour référencer l’adresse construite, telle que A7 et B7. Si vous insérez (ou supprimez) des cellules au-dessus de A7 ou B7, la référence dans la cellule C7 n’est pas perturbée, car elle construit simplement allègrement une nouvelle adresse.

Une autre approche consiste à utiliser la fonction OFFSET pour construire un type de référence similaire:

=IF(OFFSET($A$1,ROW()-1,0)=OFFSET($B$1,ROW()-1,0),"YES","NO")

Cette formule regarde simplement où il se trouve (dans la colonne C) et compare les valeurs dans les cellules qui sont à sa gauche. Cette formule n’est pas dérangée de la même manière si vous insérez ou supprimez des cellules dans la colonne A ou B.

Une approche finale (et peut-être la plus astucieuse) consiste à utiliser des formules nommées. Il s’agit d’une fonctionnalité des capacités de dénomination d’Excel qui est rarement utilisée par la plupart des gens. Suivez ces étapes:

  1. Sélectionnez la cellule C2.

  2. Choisissez Nom dans le menu Insertion, puis choisissez Définir dans le sous-menu.

Excel affiche la boîte de dialogue Définir le nom. (Voir la figure 1.)

  1. Dans la zone Noms dans le classeur, entrez le nom CompareMe. (Vous pouvez utiliser un nom différent, si vous le souhaitez.)

  2. Effacez tout ce qui se trouve dans la zone Se réfère à, en le remplaçant par la formule suivante:

  3. Cliquez sur OK.

À ce stade, vous avez créé votre formule nommée. Vous pouvez maintenant l’utiliser dans n’importe quelle cellule de la colonne C de cette manière:

=CompareMe

Il compare tout ce qui se trouve dans les deux cellules à sa gauche, tout comme votre formule originale a été conçue pour le faire. Mieux encore, la formule n’est pas automatiquement ajustée lorsque vous insérez ou supprimez des cellules.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2876) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Non-Adjusting_References_in_Formulas [Non-Adjusting References in Formulas].