Références

Les références (à des cellules, des plages de cellules, des fichiers) permettent d'utiliser, dans une formule de calcul, le contenu d'autres cellules. Une bonne maîtrise des différentes formes de références peut faire gagner beaucoup de temps dans la conception de feuilles de calcul complexes.
L'utilisation de plages nommées facilite ainsi singulièrement la lecture des formules : par exemple, on comprend facilement que =Rexpl 1998/CA 1998 puisse renvoyer la marge d'exploitation de l'année 1998.
 
Références absolues, relatives, plages de cellules
Plages nommées
Référence à une autre feuille, à un fichier externe ; récupération de fichier
Lignes et colonnes nommées automatiquement, étiquettes

 

 

 

 
 
 
 

Références absolues, relatives, plages de cellules

B2 est une référence relative, $B$2 une référence absolue


Dans le tableau ci-dessus, la formule =2*$B$2 écrite en E2 fait référence à la cellule B2 de manière absolue
Quand on recopie la cellule E2 vers le bas (en E3, E4, E5), elle fait toujours référence à B2.
La formule =2*B2 écrite en C2 fait par contre référence à B2 de manière relative. Elle fait référence à "la cellule située deux colonnes avant".
Quand on recopie la cellule C2 vers le bas (en C3, C4, C5), la référence est donc décalée et la formule fait référence à B3, B4, B5.
Quand on sélectionne la cellule référencée à la souris (plutôt que d'inscrire B2 ou $B$2), on obtient une référence relative (B2).
Pour transformer, dans une formule, une référence relative en référence absolue (ou inversement), positionner le curseur sur la référence et taper la touche F4.
On peut également utiliser des références mixtes comme =B$2.
On peut dans un calcul faire référence à une plage de cellules :
=SOMME(B2:C5) fait la somme des valeurs contenues dans la plage (rectangulaire) B2:C5.
=MOYENNE(B:B) fait la moyenne de toutes les valeurs inscrites dans la colonne B (cellules vides ignorées)
(B:B représente la colonne B, 3:3 représente la ligne 3).
Pour faire référence à la réunion de deux plages, il suffit de les séparer par un point-virgule :


Pour faire référence à l'intersection de deux plages, il suffit de les séparer par un espace :
A14:B18 B17:D17 correspond à l'intersection des deux plages, c'est à dire à B17
Si on écrit (en C21) =A14:B18 B17:D17, on obtient la valeur de la cellule B17.


 
Dans certains cas, Excel peut interpréter des formules ambiguës :
Si en D30 on écrit =B27:D27 , Excel ne retiendra de la plage B27:D27 que la cellule qui est dans la même colonne (D) que D30, et donc D27. Cette écriture simplifiée ne marche que si la plage est une plage linéaire (rectangle à une ligne ou une colonne).


Au lieu de =B27:D27, on aurait pu écrire =27:27 (ligne entière)
 

Plages nommées
Pour simplifier la présentation des calculs, on peut nommer des plages de cellules.
Il suffira alors de remplacer $B$2 et B2:C5 par leur nom dans les formules.
Si la plage B2:C5 est nommée "tableau", on peut écrire =SOMME(tableau).
Pour nommer une plage, il suffit de la sélectionner et de faire Insertion / nom / définir et d'inscrire le nom souhaité.

 
Plus simple encore, sélectionner la plage et inscrire directement le nom dans la "zone noms" à gauche de la barre de formules et taper Entrée.
 


 
 


Si les colonnes sont nommées année95, année96, année97…
Et les lignes CA, REX, RNet
Il suffit d'écrire =REX année96 pour obtenir le résultat net de l'année 96 (à l'intersection des plages REX et année96).
Les plages sont nommées en absolu. En recopiant =année95 vers la droite, on conserve année95
Pour écrire la référence en relatif, on peut utiliser C:C au lieu de année95.
Les plages nommées sont généralement valides pour l'ensemble du fichier.
Il est possible de restreindre la portée d’un nom à une seule feuille avec Insertion / nom / définir / Feuil2!toto (on peut ainsi définir un toto par page du classeur).
Si on nomme une plage Zone_d_impression c'est cette zone qui sera imprimée. On peut aussi la définir par Fichier / Zone d'impression / Définir. On définit une zone d'impression par page.
 

Référence à une autre feuille, à un fichier externe ; récupération de fichier
On peut facilement faire référence à une cellule ou une plage contenue dans une autre feuille par =Feuil2!C33 (utiliser la souris pour simplifier la saisie).
Les plages nommées sont valides pour l'ensemble du fichier. On peut donc utiliser le nom d'une plage dans toutes les feuilles.
On peut faire référence à une plage "en 3D" : Feuil1:Feuil3!B3:C5 représente l'union des plages B3:B5 des trois feuilles.
On peut donc faire des calculs sur plusieurs feuilles :
=SOMME(Feuil1:Feuil20!B15)
=MOYENNE(Feuil1:Feuil20!B15)
(Utile par exemple pour consolider les comptes de plusieurs sociétés. La fonction Données / consolider est toutefois plus performante).
De même =[rien.xls]Feuil1!$A$3 fait référence à la cellule A3 de la feuille Feuil1 du classeur rien.xls (classeur ouvert).
Particularité intéressante, si on ferme le classeur contenant la plage utilisée comme référence, la formule devient :
='C:\MES DOCUMENTS\[rien.xls]Feuil1'!$A$3
La cellule fait référence à un classeur externe FERME. Le chemin d'accès complet est indiqué.
Une liaison a été créée entre les deux fichiers. On peut la mettre à jour SANS ouvrir le fichier cible par Edition / Liaisons / mise à jour.
Attention : dans le cas de classeurs liés, le classeur contenant la liaison ne se mettra à jour qu'une fois ouvert.
Si par exemple une ligne est ajoutée dans le classeur source alors que le classeur contenant la liaison est fermé, la référence ne pointera plus vers la bonne cellule.
Il est donc vivement recommandé d'utiliser des plages nommées pour les liaisons, par exemple
='C:\MES DOCUMENTS\[rien.xls]Feuil1'!nom_ref

au lieu de ='C:\MES DOCUMENTS\[rien.xls]Feuil1'!$A$3.

Cette remarque vaut pour des liaisons Word pointant vers un fichier Excel (document Word se mettant à jour en fonction des modifications du fichier Excel).

On peut aussi utiliser des plages servant de zones de transfert (nommées) quand on a de multiples liaisons entre deux fichiers (liaison sous forme matricielle). Cette technique est inutile entre feuilles d’un même classeur puisque les noms de plage sont valides pour l’ensemble du classeur.  

Cette syntaxe de liaison externe permet de lire le contenu des cellules d'un classeur FERME (cf. également le paragraphe sur les requêtes externes).
Dans le cas d'un classeur corrompu, refusant de s'ouvrir, on peut donc récupérer les valeurs des cellules (pas les formats ni les formules) en écrivant dans une feuille vierge en A1 :
='C:\MES DOCUMENTS\[fichier_corrompu.xls]Feuil1'!A1, et en recopiant cette formule sur une large plage de la feuille (si on ignore le nom des feuilles à récupérer, Excel en donne la liste).
On peut parfois ouvrir le fichier sous forme de texte sous NotePad ou Word.
Il est toutefois plus efficace d'utiliser un utilitaire de récupération de fichier comme celui de Microsoft
(cf. récup.microsoft. et http://www.microsoft.com/france/support/data/Visioxl8.exe, ou cleaner.xla à télécharger sur http://www.microsoft.com/france/support/data/recover.exe , ou version démo de OfficeRecovery à télécharger sur http://www.officerecovery.com/excel/)
(cliquer pour voir d'autres techniques)
 
 
 

Lignes et colonnes nommées automatiquement, étiquettes
Quand on utilise, dans une formule, un nom ne faisant pas partie de la liste des plages nommées, Excel essaie de deviner à quoi il correspond, et recherche une cellule contenant ce nom.
Dans l'exemple précédent, =RCAI 1996 renvoie directement la valeur du résultat courant 1996, sans qu'il soit nécessaire de nommer les plages !


Attention toutefois,
Remarque : l'utilisation d'un nombre comme nom de ligne ou colonne est généralement interdit. Les dates (1998 dans notre exemple) font heureusement exception.
Pour lever les ambiguïtés, on peut utilement utiliser l'outil "étiquettes" :
Sélectionner tous les en-têtes de colonnes (lignes) devant être utilisés dans des formules, puis Insertion / Noms / Etiquettes …



>>>