Placer ses dollars astucieusement

Alors non, nous ne parlerons pas de placement financier dans cet article !
Mais nous faisons suite à l'analyse des adresses où nous avons découvert les différentes composantes permettant de retrouver n'importe quelle référence.
Il y a un caractère que nous n'avons pas encore expliqué et il a son importance : le symbole $.


 

De quoi on parle ?

Pour comprendre ce dont nous parlons, prenons un exemple. Avec le tableau ci-dessous, nous rentrons la formule simple =A1 dans la cellule E1. Après validation, nous obtenons bien 15.

Exemple formule Excel avec reference relative

Si nous copions la cellule E1 et la collons en F3, nous obtenons la valeur 17 et la formule est devenue =B3.

Exemple copie formule reference relative

Ceci est dû à la relativité de la référence... Pas de panique, on ne parle pas de théorie de physique ici ! C'est bien plus simple !

Excel identifie une référence relative par rapport à la cellule de départ : la formule initiale fait référence 5 colonnes plus à gauche (E1 vers A1), Excel le reproduit dans la deuxième formule (F3 vers B3).
Autre façon de voir les choses, lorsque nous décalons notre formule 2 lignes plus bas, 1 colonne plus à droite, la référence se décale de la même façon et passe donc de A1 à B3.

 

Comment figer une référence ?

Si on veut que cette référence soit figée, il faut la rendre absolue. Une référence absolue est alors identifiée par une adresse précise et non par rapport à la cellule où elle est entrée. Elle est marquée dans une formule par le symbole $.

Par exemple, si nous recommençons la même opération que ci-dessus mais avec la formule initiale =$A$1 en cellule E1. A la recopie en F3, la formule reste =$A$1.

Exemple copie formule reference absolue

Vous le voyez, le signe $ est présent deux fois, avant la colonne et avant la ligne. Ainsi, les deux se retrouvent figées.
Il est également possible d'avoir des références mixtes, avec une seule composante absolue, l'autre relative.

Toujours avec une copie de E1 vers F3, voilà les résultats avec deux références mixtes
La colonne est absolue, elle ne bouge pas. La ligne est relative, elle est décalée.

Reference colonne absolue ligne relative

La colonne est relative, elle est décalée. La ligne est absolue et ne bouge donc pas.

Reference colonne relative ligne absolue

En synthèse, voilà les 4 types de références que l'on peut rencontrer dans une formule.

Tableau types references Excel
 

A quoi ça sert ?

Pour la cellule où nous entrons votre formule, il n'y a pas de différence, la valeur ramenée sera la même. Mais nous l'avons vu dans nos exemples, l'intérêt se situe lorsque nous voulons recopier cette formule vers une autre cellule ou plage.

Au sein de la formule d'origine, certaines références doivent parfois rester constantes. Marquer ces références comme absolues permet de les figer et de recopier les formules sans avoir à les modifier.

Dans le tableau ci-dessous, on peut compléter la partie CA en multipliant les quantités par le prix unitaire et avec une seule formule dupliquée.
Le PU HT étant invariable, dans cette formule, on fige la colonne en laissant la ligne relative. Les quantités sont relatives, elles doivent être différentes dans chaque case.

Exemple remplissage tableau formule unique

En réglant judicieusement nos références dans la cellule de départ, on gagne un temps précieux à la recopie !

 

Comment jongler avec ces différents types de références ?

Reprendre une formule pour y rajouter les signes $ n'est pas très pratique. Heureusement, Excel a prévu une autre méthode pour figer les références.
Lorsque nous saisissons une formule, après avoir cliqué sur la cellule voulue, nous pouvons appuyer sur la touche F4 pour changer le mode de référence : relative, absolue, mixte... Chaque appui sur la touche F4 passera au mode suivant selon la boucle présentée ci-dessous.

Cycle types references Excel

Lorsque nous ajoutons une cellule du même classeur à une formule, même feuille de calcul ou non, Excel l'ajoute en tant que référence relative. Si nous sélectionnons une cellule dans un autre classeur, Excel l'ajoute en tant que référence absolue.
Mais le changement de type se fait de la même façon, avec la touche F4 et selon la même boucle. Le point de départ est simplement différent.

 

Et avec les plages de cellules, ça marche aussi ?

Ça marche aussi et c'est même plus subtil. On peut utiliser deux types différents pour la première et la dernière cellule.
En utilisant deux références absolues, on obtient une plage entièrement fixe. A l'inverse avec deux références relatives, la plage est complètement mobile.
En mixant une référence absolue pour la première cellule et une relative pour la seconde, on obtient une plage de taille variable.

Reference mixte plage cellules

Dans le premier tableau ci-dessus à gauche, la première et la dernière cellule sont les mêmes. Cela ne pose pas de problème à Excel qui prend en compte une plage d'une seule cellule.

Avec ce complément sur les différentes notions de références, nous sommes parés pour nous attaquer aux formules les plus complexes d'Excel !
Si certains points ne sont pas clairs, posez vos questions en commentaires.

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Pour marque-pages : Permaliens.

Laisser un commentaire