Cours Excel référence absolue et relatives

Découverte et pratique du tableur. Module de base

1.     Les calculs

&         Le Concept

Généralement, on recourt à un tableur pour réaliser des calculs. Les formules indiquent quels éléments doivent être calculés.  Les fonctions permettent de prédéfinir des formules adaptées à des calculs spécifiques.

•  Considérons des extraits de feuilles de calcul présentés ci-après (voir figures 1, 2 et 3)

Figure 1

•  En C7, la formule =C1*C2 demande au programme d’effectuer le produits des valeurs numériques contenues dans les cellules dont les adresses sont C1 et C2.

•  En E4, la formule effectue la somme des valeurs contenues dans les cellules E1 et E2 puis divise le résultat obtenu par la constante 2 (voir figure 2)

•  En F4, la formule effectue la division de la valeur contenue dans la cellule F2  puis additionne le résultat au contenu de la cellule F1 (voir figure 3)

@     La pratique

pour insérer une formule, — permettant d’effectuer un calcul —

7        

n on amène le pointeur dans la cellule qui contiendra la formule (à l’aide des touches du pavé curseur par exemple). On vérifie que le pointeur se trouve bien dans la cellule souhaitée. L’adresse apparaît dans la barre des formules (en haut à gauche). Par exemple B7.

n on tape =

n on écrit les adresses, les opérateurs, les constantes selon la syntaxe qui convient (voir ci-avant, radiographie d’une formule)

n on valide la formule en appuyant sur la touche Enter  n le pointeur se déplace alors sur la cellule suivante (ici B8)

8 

n on amène le pointeur dans la cellule qui contiendra la formule (à l’aide de la souris, qui pilote un curseur en forme de croix suisse). On vérifie que le pointeur se trouve bien dans la cellule souhaitée. L’adresse apparaît dans la barre des formules (en haut à gauche). Par exemple B7.

n on recourt au clavier pour insérer le signe =

n à l’aide de la souris, on clique sur la cellule dont l’adresse doit apparaître à droite du signe n l’adresse apparaît dans la barre des formule et dans la cellule, à droite du signe = n la cellule est alors entourée d’une corde clignotante

n on recourt au clavier pour insérer l’opérateur arithmétique, par exemple +

n à l’aide de la souris, on clique sur la cellule dont l’adresse doit apparaître à droite de l’opérateur arithmétique n de la même manière, l’adresse apparaît dans la barre des formule et dans la cellule, à droite de l’opérateur arithmétique

n la cellule ainsi sélectionnée est, elle  aussi, entourée d’une corde clignotante

n quand toutes les adresses et tous les opérateurs arithmétiques sont portés dans la cellule, on clique enfin sur la case de validation de la barre des formules

2.     Les fonctions

&         Le Concept

Pour éviter l’encodage des nombreuses adresses nécessaires à l’élaboration de formules (voir figure 1, B7), on recourt à des fonctions. Il s’agit simplement de formules préétablies, regroupées en catégories : fonctions Finances, Date & Heure, Math  & Trigono, Statistiques, Logique

•  si nous reprenons notre exemple (voir figure 1, B7), au lieu d’encoder  

•  on insérera simplement la fonction =SOMME(), ce qui en l’occurrence donnera

(voir figure 5)

@ La pratique

Pour la fonction =SOMME()

Pour une autre fonction — par exemple la fonction =MOYENNE() —

7        

n on place le pointeur dans la cellule qui recueillera la formule (voir figure 5, en A6) n on encode la formule en respectant bien sa syntaxe  =moyenne(

— notez les minuscules, et l’absence d’espace entre = et les caractères suivants —

n on sélectionne la plage contenant les cellules dont il faut calculer la moyenne des valeurs ; on observe la corde qui entoure progressivement cette plage, au fur et à mesure que l’on déplace la

croix suisse pour opérer la sélection. Au terme de celle-ci, on obtient l’expression suivante

=moyenne(A1:A5

n on ferme la parenthèse ) eton valide la formule en appuyant sur la touche Enter  n on observe que le calcul s’effectue immédiatement, et que la formule devient :

=MOYENNE(A1:A5)

n il est possible d’insérer une fonction à l’aide de l’Assistant Fonction (à l’aide de la souris —  voir ci après — mais, dans le cas d’une fonction simple dont on connaît la syntaxe, on peut l’introduire directement via le clavier. 

8 

n on place le pointeur dans la cellule qui recueillera la fonction (voir figure 5, en A6) n on clique sur l’outil Assistant Fonction(voir figure 6, en A6) de la barre d’outils n on choisit le type de fonction, puis on suit les indications qui apparaissent dans les boîtes de dialogue successives (pour définir la plage, pour valider etc.)

3.     Les adresses relatives et absolues

3.1.    Les adresses relatives. Notion de copie relative

Problème : comment effectuer rapidement la somme de plusieurs colonnes de nombres, autrement dit comment recopier une formule placée en B7 de C7 à E7 par exemple  ?

            — c'est-à-dire demander d’effectuer instantanément les sommes dans ces cellules —

&         Le Concept

•  Considérons une feuille de calcul (voir figure 6) contenant déjà diverses valeurs en B1 à B6, en C1 à C6, D1 à D6, E1 à E6. La fonction =SOMME(B1:B6) a permis l’affichage en B7 de la somme des valeurs affichées dans la plage B1 à B6.

•  La fonction =SOMME() peut être copiée instantanément dans les cellules C7, D7, E7.(voir

Figure 7

•  Ce type de copie s’appelle « copie relative »

•  la fonction =SOMME(B1:B6) doit être comprise comme l’exigence suivante :

« additionne les valeurs contenues dans toutes les cellules qui se trouvent au-dessus de la cellule contenant ladite fonction, en partant de celle qui se trouve 6 cellules audessus, et en terminant à celle qui se trouve une cellule au-dessus »

•  quand on recopie cette fonction vers la droite (en C7), on recopie en fait l’exigence « additionne les valeurs contenues dans toutes les cellules qui se trouvent au-dessus de la cellule contenant ladite fonction, en partant de celle qui se trouve 6 cellules au-

dessus, et en terminant à celle qui se trouve une cellule au-dessus » et le système écrit alors dans la cellule C7 : =SOMME(C1:C6) et ainsi de suite .

•  ce type de copie porte un nom : il s’agit de la copierelative. La copie relative est basée sur le fait que les adresses des cellules A1, C4, D28, AB1958, IV19384 .sont des adressesrelatives.

•  ce type de copie s’effectuera le plus souvent vers le bas ou vers la gauche.

@ La pratique

8 

n on place le pointeur dans la cellule B7qui contient la fonction dont on veut effectuer la recopie relative de C7 à E7 (voir figure 8, en B7)

n on déplace la souris jusqu’à ce que le pointeur coïncide avec l’angle inférieur droit de ladite cellule B7. Il prend alors la forme d’une fine croix et s’appelle alors « poignée de recopie » (voir figure 8)

 (Attention : cette croix est différente de la croix suisse)

n on « drague » (en anglais, on dit « to drag ») la poignée de recopie (ici vers la droite) de manière à traverser les cellules qui contiendront la fonction recopiée, puis on relâche le bouton de gauche de la souris : les fonctions sont recopiées.

3.2.     Les adresses absolues. Notion de copie absolue

Problème : comment effectuer rapidement un calcul du pourcentage ?

&Le Concept

• Considérons une feuille de calcul (voir figure 9) contenant déjà diverses valeurs de B2 à G10.

@ La pratique

8 et 7

n on place le pointeur dans la cellule D11qui contiendra la formule dont on veut effectuer la recopie « absolue » de D11 en E11, puis F11 et enfin G11(voir figure 9)

n on tape le signe =, ce qui indique au système que l’on souhaite insérer un calcul, un formule ou une fonction dans cette cellule

n on amène le pointeur dans la cellule D10. Pour que cette adresse constitue le numérateur de la division, on clique dans la cellule D10 à l’aide du bouton gauche de la souris. On constate alors que la corde clignotante entoure la cellule D10.

(voir figure 10).  On valide par

ENTER.

puis…

n au clavier on entre le signe  / (la touche « divisé » du pavé numérique)

n on amène le pointeur dans la cellule C10. Pour que cette adresse constitue le dénominateur de la division, on clique donc dans cette cellule C10 à l’aide du bouton gauche de la souris. On constate alors que la corde clignotante entoure la cellule C10. On valide par ENTER(voir figure 11)

Figure11

n on place alors le curseur dans la barre des formules, à droite du dénominateur de la fraction. Il s’agit de faire apparaître à cet endroit le signe $ Pour ce faire, on appuie sur la touche de fonction F4 Le contenu de la cellule propose la formule intégrant l’adresse C10, désormais absolue. C10 est devenu alors $C$10(voir figure 12)

Figure12

n on place le pointeur dans la cellule D11qui contient la formule dont on veut effectuer la recopie absolue de E11 à G11(comme en  figure 8, en B6)

n on déplace la souris jusqu’à ce que le pointeur coïncide avec l’angle inférieur droit de ladite cellule D11. Il prend alors la forme d’une fine croix et s’appelle alors « poignée de recopie » (voir figure 8) (Attention : cette croix est différente de la croix suisse)

n on « drague » la poignée de recopie (ici vers la droite) de manière à traverser les cellules qui contiendront la formule recopiée, puis on relâche le bouton de gauche de la souris : les formules sont recopiées.

n le calcul est effectué immédiatement, et on observe les formules copiées dans chaque cellule : (voir figure 13)

Figure13