Calculs et fonctions

Références relatives et absolues

On appelle référence le positionnement d’une cellule (A1, B5, etc.). Il existe trois types de références :

  • La référence relative ;

  • La référence absolue ;

  • la référence mixte.

La référence relative désigne une adresse de cellule calculée par rapport à la cellule contenant la formule.

Ex. : Si vous entrez dans la cellule D4 la formule suivante : =C1*3, la référence (relative) C1 représente une cellule située une colonne avant et 3 lignes avant la cellule contenant la formule. Si vous copiez cette cellule en F8, la formule est modifiée automatiquement et devient =E5*3, car la cellule E5 est située une colonne avant et 3 lignes plus haut que F8.

excel-fct-3La référence absolue désigne une adresse de cellule fixe quelle que soit la cellule qui contient la formule. Une référence absolue s’écrit avec des symboles $ (ex : $B$4).

Ex. : calculons le prix TTC de plusieurs articles. La formule est donc PVTTC=PVHT*(1+TVA). La TVA est donc constante, mais la référence du prix HT doit rester variable.

En entrant en C3 la formule =B3*(1+$B$1), nous « bloquons » la cellule B1. En utilisant la poignée de recopie vers le bas, la formule changera comme ceci : =B4*(1+$B$1) pour la cellule C4, etc.

Les références relatives sont bien modifiées, mais pas la référence absolue !

Les références mixtes combinent les deux autres références. Vous pouvez ainsi bloquer une partie de la référence d’une cellule. Ainsi, si vous entrez $A4 dans une formule, vous bloquez la colonne. Lors de la recopie de la formule, seule la ligne changera. De même, en entrant A$4, vous bloquez ainsi la ligne, mais la colonne reste variable.

Pour définir une référence mixte ou absolue, vous devez entrer le symbole $ lors de la saisie de la formule. Retenez ceci : l’insertion du symbole $ bloque la référence qui suit.

Si vous préférez écrire la formule avec la souris, en sélectionnant les cellules, vous pouvez bloquer la cellule sélectionnée en appuyant sur la touche F4. Une pression sur cette touche ajoute automatiquement les symboles $ devant la lettre de colonne et le numéro de la ligne. Appuyez encore sur F4  pour bloquer uniquement la ligne, et une dernière fois sur F4  pour bloquer uniquement la colonne.

Pour transformer une formule existante :

  • Double-cliquez sur la cellule contenant la formule ;

  • Sélectionnez la référence à transformer ;

  • Appuyez sur F4 autant de fois que nécessaire.

  • Validez en appuyant sur .

Exercice : références mixtes

Refaites le tableau suivant. Les cellules sur fond gris seront calculées.excel-fct-4

Reproduisez le tableau et saisissez les champs non calculés.

Appliquez la mise en forme monétaire sur les champs calculés et placez les bordures.

Vous devez calculer les intérêts perçus sur une durée déterminée. La formule est :

Capitalactuel=Capitalplacé×(1+taux)durée

Le remplissage du tableau se fera via la poignée de recopie, vers la droite puis vers le bas. Il faut donc bloquer certaines cellules.

En C7, retranscrivez la formule théorique ci-dessus. =D2*(1+C6)^B7. Utilisez la poignée de recopie pour copier la cellule vers la droite, puis sélectionnez la plage C7:K7 et utilisez la poignée de recopie vers le bas.

Le tableau obtenu ne ressemble pas du tout à ce que nous voulons obtenir ! Il faut bloquer certaines cellules.

Double-cliquez sur C7, puis sélectionnez la première référence (le capital placé). Ce capital placé est constant. Appuyez sur F4 pour bloquer la cellule ($D$2).

La seconde référence est celle du taux d’intérêt. Ce tableau présente plusieurs exemples de taux. La référence de la cellule doit donc être fixe pour une copie vers le bas (un taux par colonne), mais doit pouvoir être variable pour la copie vers la droite. Appuyez deux fois sur F4 pour bloquer la ligne de la cellule (C$6).

La troisième référence est la durée. Chaque ligne du tableau correspond à une durée. La référence de la cellule doit donc être fixe pour une copie vers la droite, et variable pour une copie vers le bas. Appuyez trois fois F4 sur pour bloquer la colonne de la cellule ($B6).

Utilisez la poignée de recopie vers la droite. Sélectionnez la plage C7:K7, et utilisez la poignée de recopie vers le bas.

Résumé

Dans cet exercice, nous avons vu :

  • Adressage absolu ($D$2) ;

  • Adressage mixte (C$6 et $B6).

Vous pouvez également utiliser des cellules se trouvant sur d’autres feuilles du classeur. Dans une formule, sélectionnez simplement la feuille contenant les données, puis la (les) cellule (s) nécessaire (s). La notation d’une cellule se situant sur une autre feuille est du type Feuil1!A1, avec le nom de la feuille suivie d’un point d’exclamation (!).

Ex. : une feuille 1, nommée données, possède une série de nombres. Si vous souhaitez en faire la somme sur la feuille 2, vous devez :

  • Sélectionner la cellule où sera affichée la réponse (A1), et tapez = ;

  • Appelez la fonction SOMME (voir plus loin) ou tapez SOMME( ;

  • Sélectionnez la plage sur la feuille de données (A1:D1) ;

  • Validez la sélection. La formule s’écrira =SOMME(données!A1:C1).

Print Friendly, PDF & Email