Références relatives dans les plages nommées (Microsoft Excel)

Chris a mis en place une feuille de calcul dans laquelle il utilise des plages nommées (lignes) dans ses formules. Il a nommé la ligne entière des ventes comme «Ventes», puis utilise le nom Ventes dans diverses formules. Par exemple, dans n’importe quelle colonne donnée, il peut dire = Sales, et la valeur de la ligne Sales, pour cette colonne, est renvoyée par la formule. Chris se demandait comment utiliser la même technique de formule pour faire référence à des cellules dans différentes colonnes.

Cela peut être fait de différentes manières. Tout d’abord, vous pouvez utiliser la fonction INDEX pour faire référence aux cellules. La manière rigoureuse de faire référence à la valeur des ventes dans la même colonne est la suivante:

Cela fonctionne si la plage nommée Sales fait vraiment référence à la ligne entière de la feuille de calcul. Si ce n’est pas le cas (par exemple, Sales peut faire référence aux cellules C10: K10), la formule suivante fait référence à la valeur de Sales dans la même colonne dans laquelle la formule apparaît:

Si vous souhaitez faire référence à une colonne différente, ajustez simplement la valeur ajoutée à la désignation de colonne dans la fonction INDEX. Par exemple, si vous vouliez déterminer la différence entre les ventes de la colonne actuelle et les ventes de la colonne précédente, vous utiliseriez ce qui suit:

La version « abrégée » de cette formule serait la suivante:

Il existe d’autres fonctions que vous pouvez utiliser en plus de INDEX (comme OFFSET), mais la technique est toujours la même – vous devez trouver un moyen de faire référence à un décalage de la colonne actuelle.

Cependant, il existe un moyen plus simple d’obtenir les données souhaitées. Disons que votre gamme Sales a également une ligne d’en-tête à ce sujet, similaire à ce qui est montré dans ce qui suit: (Voir la figure 1.)

Figure 1. Exemple de données de vente.

La ligne d’en-tête répertorie les années de la plage et les valeurs sous les en-têtes sont celles qui composent réellement la plage de ventes. Pour vous assurer que cette technique fonctionnera, procédez comme suit:

Une fois ce changement de configuration effectué, vous pouvez utiliser les formules suivantes:

En fait, vous demandez à Excel de travailler avec des unions de cellules. Dans ce cas, Sales ‘2009’ renvoie la cellule à l’intersection de la plage Sales et de la colonne ‘2009’. Une union similaire est renvoyée pour la partie de la formule à droite du signe moins. Le résultat est la soustraction des deux valeurs souhaitées.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (3202) 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-Relative_References_within_Named_Ranges [Références relatives dans les plages nommées].