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:

=INDEX(Sales,1,COLUMN())

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:

=INDEX(Sales,1,COLUMN()-COLUMN(Sales)+1)

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:

=INDEX(Sales,1,COLUMN()-COLUMN(Sales)+1) - INDEX(Sales,1,COLUMN()-COLUMN(Sales))

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

=Sales - INDEX(Sales,1,COLUMN()-COLUMN(Sales))

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 au-dessus, similaire à ce qui est montré dans ce qui suit: (Voir la figure 1.)

image

Figure 1. Exemple de données pour une feuille de calcul.

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:

  1. Affichez la boîte de dialogue Options Excel. (Dans Excel 2007, cliquez sur le bouton Office, puis sur Options Excel. Dans Excel 2010 et Excel 2013, affichez l’onglet Fichier du ruban, puis cliquez sur Options.)

  2. Cliquez sur le lien Formules sur le côté gauche de la boîte de dialogue. (Voir la figure 2.)

  3. Assurez-vous que la case à cocher Utiliser les noms de table dans les formules est activée.

  4. Cliquez sur OK.

Une fois ce changement de configuration effectué, vous pouvez utiliser ce qui suit comme formule:

=Sales '2013' – Sales '2008'

En fait, vous demandez à Excel de travailler avec des unions de cellules. Dans ce cas, Sales ‘2013’ renvoie la cellule à l’intersection de la plage Sales et de la colonne ‘2013’. 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 (9177) s’applique à Microsoft Excel 2007, 2010 et 2013. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Relative_References_within_Named_Ranges [Références relatives dans les plages nommées].