Accueil

Formation Excel d’après http ://www.lecompagnon.info/excel/index.html (liens vérifiés bons sinon supprimés et rajouts de quelques mentions)

6. Copier et position relative et absolue

Introduction

Copier

Sélectionnez la cellule ou le bloc de cellules à copier.

Position relative et absolue

Exercice du taux de change

Collage spécial

 

Introduction

Pour utiliser efficacement un tableur, il faut créer des formules pour calculer automatiquement tout changement que vous pourriez apporter à votre modèle. Puisque plusieurs de ces formules se ressemblent, il est plus efficace de les copier que d'avoir à les réécrire chaque fois que vous en avez besoin. Cette page démontre comment procéder et aussi regarde le point très important des positions relatives et absolues. Il est capital de maîtriser ces notions pour utiliser correctement un tableur.

Copier

L'un des grands avantages d'un tableur est d'être capable de recopier une formule ou du texte aussi souvent que nécessaire pour créer un modèle. Il est bien plus efficace de recopier une formule que de la réécrire chaque fois qu'elle est nécessaire. Excel vous offre plusieurs façons de copier et de coller. Vous pouvez combiner ces différentes façons selon vos besoins.

Sélectionnez la cellule ou le bloc de cellules à copier.

Pour copier :

*Du menu Édition, sélectionnez Copier.

OU

*Appuyez sur le bouton copier.

OU

*Appuyez sur les touches Ctrl et C.

OU

*Appuyez sur le bouton droit de la souris.

*Du menu contextuel, sélectionnez l'option Copier.

 

*Déplacez le curseur sur la cellule ou faites un bloc avec les cellules dans lesquelles vous voulez recopier la formule.

 

Pour coller

*Du menu Édition, sélectionnez Coller.

OU

*Appuyez le bouton coller.

OU

*Appuyez sur les touches Ctrl et V.

OU

*Appuyez sur le bouton droit de la souris.

*Du menu contextuel, sélectionnez l'option Coller.

Vous pouvez aussi vous utiliser le menu contextuel. Son avantage est que vous n'avez pas à déplacer continuellement la souris vers les barres d'outils pour ensuite revenir à la feuille de calcul.

Remarque : si vous avez copié un bloc de cellules, sélectionnez la cellule ciblant la première cellule en haut à gauche du nouveau bloc qui va être collé. Copier un bloc dans un autre bloc dont les dimensions ne sont pas multiples de celui à copier sera refusé par Excel.

Une dernière technique pour copier est d'utiliser la poignée de recopie située au coin inférieur droit de la cellule active ou du bloc de cellules sélectionnées.

Cellule active

Il suffit de :

*Placez le pointeur sur la cellule à copier.

*Placez le pointeur sur le petit carré noir au coin inférieur droit de la cellule.

*En gardant un doigt sur le bouton gauche de la souris, déplacez le pointeur dans la direction désirée.

Le contenu de la cellule va se recopier dans les cellules sélectionnées. Vous pouvez aussi copier un bloc de cellules en même temps. Le résultat va varier selon que le contenu des cellules est des nombres ou des formules. Il y a un exercice de cette option plus loin sur cette page. Elle peut aussi être utilisée pour continuer une série de données.

Position absolue et relative : IMPORTANT

Copier les formules est l'un des éléments qui accélère beaucoup la création de modèles. Il y a cependant un piège auquel il faut faire très attention. Prenons par exemple la formule suivante +A1+A2 qui est située dans la cellule A3. Si on recopie cette formule dans les cellules B3 et C3 le résultat serait le suivant : B3 :+B1+B2, C3 :+C1+C2. Pourquoi Excel n'a-t-il pas recopié exactement la formule +A1+A2 ?

Tout est dans la manière qu'Excel interprète la formule. Ce n'est pas l'adresse de la cellule qui est importante mais sa position par rapport la cellule où se trouve la formule. Si le curseur est dans la cellule A3, Excel va interpréter la formule +A1+A2 de cette manière suivante : la cellule A1 est deux cellules au-dessus de la position actuelle du curseur et A2 la cellule juste au dessus. Donc, quand Excel va recopier la formule il va faire l'addition des deux cellules juste au-dessus de l'endroit où est écrite la formule. C'est pour cette raison que les formules recopiées en B3 et C3 le sont ainsi. On appelle ce fonctionnement des positions relatives. Relatif à l'emplacement de la formule, Excel va toujours chercher les données nécessaires au même endroit.

 

Mais que faire quand on a besoin d'une variable dans la formule ? Une variable est une donnée qui peut changer en tout temps. Par exemple, si on veut savoir ce qui arrive aux profits si le taux de croissance change entre 5% et 50%. Ou si le nombre d'employés passe de 5 à 25 ? Ou si le taux d'intérêt pour l'hypothèque qui est à renouveler change ?

Un modèle est composé de trois éléments : les constantes, les variables et les formules. Les constantes font partie des hypothèses de base du classeur tel que les nombres de départ du modèle. Pour savoir combien de profits la compagnie va réaliser, elle doit savoir premièrement quelles sont les sommes des revenus et des charges.

Les variables sont les données sur lesquelles on veut tester le modèle en posant la question "qu'arrivera-t-il si ..." Qu'arrivera-t-il si les taux d'intérêts augmentent ? Qu'arrivera-t-il si tel client ne me paie pas pour mes services rendus ? Qu'arrivera-t-il si on a tel taux de croissance ?

Ne jamais écrire une variable dans une formule. Sinon, vous serez obligé de changer la formule à chaque fois que la variable change. C'est pour cette raison qu'il faut écrire une variable dans une seule cellule. Si la variable est écrite dans plusieurs cellules, il y a de très grandes chances d'oublier de changer l'une de ces cellules au bon moment. Ceci invaliderait le modèle au complet. Ceci rend aussi difficile de s'assurer que le modèle est correct puisqu'il faudrait vérifier toutes les cellules où la variable a été recopiée. Ceci rendrait le processus de création de modèle, surtout sa validation, très long et très pénible. Donc, il faut éviter au maximum de recopier une variable à plusieurs endroits dans un classeur. La meilleure manière est donc d'écrire une variable dans une seule cellule.

Ceci rend le travail de création de formules un peu plus difficile. Que faire alors quand on veut recopier une formule? En recopiant la formule, la position relative change elle aussi. Quand on recopie une formule dans une cellule de gauche ou de droite, la position pour la colonne change. Quand on recopie une formule dans une autre au-dessus ou en dessous, la position de la ligne change.

On peut utiliser le signe "$" pour "figer" ou "geler" une colonne ou une ligne dans une formule. Quand la formule sera recopiée, les parties gelées ne changeront pas.

A1

Ni la colonne, ni la ligne sont gelées. Elles changeront avec la position de la formule.

$A1

La colonne est gelée mais pas la ligne. A ne changera pas mais 1 pourra changer.

A$1

La ligne est gelée mais pas la colonne. A pourra changer mais 1 ne changera pas.

$A$1

La ligne et la colonne sont gelées. On fera toujours référence au contenu de la seule cellule A1.

Exemple :

a1:100 a2:200 a3: =$a1+a2

*Écrivez les nombres et les formules suivantes dans les cellules appropriées.

Recopiez le contenu de la cellule A3 dans les cellules B3 à E3. Il y a plusieurs manières pour copier la formule.

*Placez le curseur dans la cellule A3.

*Du menu Édition, sélectionnez l'option copier.

*Faites un bloc avec les cellules B3 jusqu'à E3.

*Du menu Édition, sélectionnez l'option Coller

OU en utilisant la poignée de recopie.

*Placez le curseur dans la cellule A3.

*Placez le curseur sur le carré au coin inférieur droit de la cellule. La forme du curseur doit se changer pour devenir un signe "+" de couleur noire.

*Gardez un doigt sur le bouton gauche de la souris et déplacez le pointeur jusqu'à la cellule E3.

Le résultat des cellules B3 jusqu'à E3 est le suivant.

Cellule Résultat Formule contenu de cellule

A3

300

+$A1+A2

A2 =200

B3

100

+$A1+B2

B2 =0 puisque vide

C3

100

+$A1+C2

C2 =0 puisque vide

D3

100

+$A1+D2

D2 =0 puisque vide

E3

100

+$A1+E2

E2 =0 puisque vide

En "gelant" la colonne A dans la formule, celle-ci va toujours chercher la valeur dont elle a besoin dans la première colonne. Ceci est nécessaire quand vous voulez chercher le contenu d'une cellule qui contient une variable. Une formule n'ayant que des positions relatives ne vous aurait pas permis de toujours chercher le contenu de la cellule A1 à moins de changer manuellement toutes les formules. En gelant la colonne ou la ligne appropriée dans la formule, vous ne serez pas obligé de modifier la formule après l'avoir copiée dans d'autres cellules.

En copiant la cellule A3 par exemple en A7, B7 et C7

Cellule Résultat Formule contenu de cellule

A7

100

+$A5+A6

A6 =0 puisque vide

B7

100

+$A5+B6

B6 =0 puisque vide

C7

100

+$A5+C6

C6 =0 puisque vide

$A1 est devenu $A5 puisqu'il n'y a pas $ avant le 1 !

 

Exercice du taux de change

Cet exercice est pour vous démontrer l'énorme avantage d'utiliser les positions relatives et absolues dans des formules dont vous savez que vous allez avoir à recopier. Il consiste à changer les valeurs en dollars américains en dollars canadiens.

*Écrivez les nombres et les formules dans les cellules appropriées.

Ciffres et formules à mettre A!:5 B1:10 C1:15 D1:20 A2:1,4 A3:=a1*A2

Pour faciliter la compréhension, on présume que 1 dollar américain équivaut à 1,4 dollars canadiens, d'où la valeur 1,4 dans la cellule A2. La cellule A3 contient la formule qui va nous aider à trouver la valeur pour la première colonne. Le résultat de cette cellule devrait être de 7 (5 * 1,4 = 7). Vous pourriez récrire la formule pour les cellules suivantes, de B3 à D3, mais il est encore plus efficace de recopier la formule que vous avez préparée de la cellule A3 aux autres.

*Placez le pointeur sur la cellule A3.

*Du menu Édition, sélectionnez l'option Copier.

OU

*Appuyez sur le bouton bouton copier.

OU

*Appuyez sur les touches CTRL et C.

Le cadre de la cellule A3 va clignoter. Ceci est normal. C'est pour vous rappeler que c'est la cellule que vous venez de copier.

*Faites un bloc avec les cellules B3 à D3.

*Du menu Édition, sélectionnez l'option Coller.

OU

*Appuyez sur le bouton bouton coller.

OU

*Appuyez sur les touches CTRL et V.

Et voilà ! La formule est maintenant recopiée dans les autres cellules que vous venez de spécifier. Cependant, le résultat n'est pas ce que vous attendiez. Il n'y a que des zéros au lieu de valeurs ! Allons voir pourquoi.

*Placez le pointeur sur la cellule B3.

Vous pouvez voir à partir de la barre de formules le contenu suivant :

Contenu de la barre formule: =B1*B2

Vous remarquerez que la cellule B1 contient une valeur (10) tandis que la cellule B2 est vide. C'est pour cette raison qu'Excel affiche la valeur 0 ( 10 * 0 = 0 ).

Pour régler ce petit problème, vous pourriez recopier le taux de change de la cellule A2 dans les cellules B2 à D2. En fait, ce n'est pas une solution pratique pour plusieurs raisons. Premièrement, cela occuperait plusieurs cellules du classeur pour rien. On pourrait mettre d'autres nombres ou formules à la place. Deuxièmement, les chances d'oublier de changer le contenu d'une seule cellule lors de modifications sont beaucoup trop élevées.

Il est nettement préférable de mettre les valeurs de variables et de constantes dans des cellules à part et d'ajuster les formules. Il ne faut jamais mettre la valeur d'une constante, ou d'une variable, dans une formule. Encore une fois, comme mentionné au paragraphe précédent, si vous oubliez de changer la valeur dans une seule cellule, votre modèle au complet peut être invalide.

En fait les constantes et les variables ont un élément en commun : elles peuvent changer. Une variable peut changer pour les besoins de votre modèle : taux d'occupation, prix d'un produit, prix du matériel etc. Une constante peut aussi changer à moyen ou à long terme : taxes, taux d'imposition, tarifs ... Puisqu'elles peuvent changer, il est beaucoup plus facile pour les gérer d'avoir la valeur dans une seule cellule à laquelle on peut faire référence dans la formule. Mais, pour maîtriser cette situation, il faut maîtriser les formules que l'on veut recopier avec les positions relatives et absolues.

Donc, pour revenir à l'exercice en cours, il faut ajuster la formule pour qu'elle puisse toujours retrouver le taux de change dans la bonne cellule; A2 dans notre cas.

Que faut-il savoir avant d'ajuster la formule ?

Y a-t-il une partie de la formule que vous voulez fixer ? Il est possible que la réponse soit non dans plusieurs cas. Pour l'exercice, il faut "fixer" la partie A2 de la formule.

Il faut aussi savoir si vous aller copier la formule horizontalement, verticalement ou les deux ? Pour l'exercice, la formule sera recopiée horizontalement aux cellules B3 à D3. Puisque c'est horizontalement, il faudra "geler" ou "fixer" la lettre de la colonne et non le nombre de la ligne où est située la cellule contenant la formule.

Il est maintenant temps de modifier la formule de la cellule A3 pour l'adapter.

*Placez le pointeur dans la cellule A3.

Il faut maintenant modifier le contenu de la cellule.

*Appuyez sur la touche F2.

OU

*Faites un double-clic sur la cellule A3.

OU

*Placez ensuite le curseur sur la barre de formule.

*Changez le contenu de la formule à ceci : =A1*$A2

La formule a été changée mais la valeur reste la même : 7 ( 5 * 1,4 ).

*Recopiez la formule A3 aux cellules B3 à D3.

Si vous avez besoin d'un rappel, référez-vous aux instructions pour copier.

Chiffrier avec les bons résultats B3: 14, C3: 21, D3: 28

*Vérifiez les formules des cellules B3 à D3.

B3 : =B1*$A2

C3 : =C1*$A2

D3 : =D1*$A2

Vous avez maintenant le bon résultat parce que vous avez "gelé" la colonne A de la cellule A2. Pour vous montrer à quel point cela est pratique :

*Changez la valeur de la cellule A2 de 1,4 à 1,25.

Nouveaux résultats du chiffrier modifié: B3: 12,5, C3: 18,75 D3: 25

Tout le classeur s'est modifié en changeant une seule valeur. Que préférez-vous maintenant; changer le contenu d'une cellule ou changer plusieurs cellules ? Lequel est le plus efficient ?

Mais il faut faire attention à la manière dont on utilise les positions relatives et absolues.

*Placez le pointeur dans la cellule A3.

*Copier le contenu de la cellule.

*Coller le contenu des cellules aux cellules A4 à A8.

Chiffrier avec les nouvelles cellules: A4: 7,8125, A5: 48,828125, A6: 381,4697266, A7: 18626,45149, A8: 7105427,358

Quel taux de change ! Vérifions le contenu des cellules A4 A8.

A4 : = A2*$A3

A5 : =A3*$A4

A6 : =A4*$A5

A7 : =A5*$A6

A8 : =A6*$A7

Vous voyez le problème ! On a recopié la formule à la verticale et non à l'horizontale. Bien que la colonne soit gelée, la ligne ne l'est pas. C'est pour cette raison que les valeurs grimpent si rapidement. Faites attention lorsque vous appliquez les notions de positions relatives et absolues. Assurez-vous de geler la bonne colonne ou ligne.

Collage spécial

Les options pour copier et coller sont très avantageuses pour copier les formules. Mais il est aussi possible de copier des formats de présentation ou d'accomplir des opérations mathématiques avec l'option collage spécial. Les prochains exercices démontrent quelques-unes des possibilités de cette option.

*Entrez les nombres et la formule dans les cellules appropriées.

*Sélectionnez les cellules A1 à A3.

*Du menu Edition, sélectionnez l'option Copier.

OU

*Appuyez sur le bouton Copier.

OU

*Appuyez sur le bouton droit de la souris.

*Du menu contextuel, sélectionnez l'option Copier.

*Placez le pointeur dans la cellule B1.

*Du menu Edition, sélectionnez l'option Collage spécial.

Le collage spécial offre plusieurs options. Voici la description de la première série.

Option

Description

Tout

Fonctionne exactement comme l'option Coller.

Formules

Colle seulement la formule sans les options de présentation.

Valeurs

Colle seulement le nombre qui est le résultat d'une formule.

Formats

Colle juste les options de présentations telle que la couleur, la taille et le type de police de caractère, la couleur du fond de cellule et la bordure de la cellule.

Commentaires

Colle seulement le commentaire copié d'une autre cellule.

Validation

Colle seulement les bornes placées dans la validation.

Tout sauf bordure

Copie tout sauf la bordure choisie.

*De la section Coller, sélectionnez l'option Valeurs.

*Appuyez sur le bouton OK.

Les nombres ont été recopiés mais pas la formule. Vérifiez le contenu de la cellule B3. C'est le résultat de la formule qui s'y trouve et non la formule. On peut aussi appliquer des opérations mathématiques en utilisant le collage spécial. C'est ce que le prochain exercice va vous démontrer.

*Sélectionnez les cellules A1 à A3.

*Du menu Edition, sélectionnez l'option Copier.

*Placez le pointeur dans la cellule C1.

*Du menu Edition, sélectionnez l'option Coller.

*Placez le pointeur dans la cellule C1.

*Du menu Edition, sélectionnez l'option Collage spécial.

*De la section opération, sélectionnez l'option Addition.

*Appuyez sur le bouton OK.

Les valeurs de la première colonne ont été additionnées aux cellules de la troisième colonne. Mais qu'est-il arrivé à la formule de la cellule C3 ? Excel a ajouté le contenu de la formule de la première colonne à la formule qui existait déjà. Donc, la nouvelle formule pour cette cellule est =(C1+C2)+(C1+C2) . Excel a doublé la formule. Donc, il faut faire attention lorsqu'on utilise cette option avec des formules.

Le collage spécial a encore un tour dans son sac. Il s'agit de transposer les valeurs. Cela consiste à "tourner" un bloc de cellules. Ce qui était avant sur une ligne sera maintenant sur une colonne. Voici un exemple.

*Sélectionnez les cellules A1 à A3.

*Du menu Edition, sélectionnez l'option Copier.

*Placez le pointeur dans la cellule A5.

*Du menu Edition, sélectionnez l'option Collage spécial.

*Sélectionnez l'option Transposé.

*Appuyez sur le bouton OK.

Le contenu des cellules A1 à A3 est maintenant dans les cellules A5 à C5. Le collage spécial a "tourné" le bloc de cellules. Cela peut être pratique dans certains cas.

Il reste un dernier tour au collage spécial. C'est celui de faire des liens vers d'autres cellules. Ceci est très pratique pour les formules et pour les grands modèles.

*Sélectionnez les cellules A1 à A3.

*Du menu Edition, sélectionnez l'option Copier.

*Sélectionnez la seconde feuille de travail (Feuil2) en cliquant sur l'onglet au bas de l'écran ou en utilisant les touches Ctrl et PgDn (Page Down).

*Placez le pointeur dans la cellule A1.

*Du menu Edition, sélectionnez l'option Collage spécial.

*Appuyez sur le bouton Coller avec liaison.

Les nombres apparaissent comme dans la première colonne de la première feuille de calcul. Mais ce sont des formules qui sont dans les cellules. Les valeurs vont changer lorsque vous allez changer le contenu des cellules de la première feuille de calcul. Retournez à la première page et changer les valeurs des cellules A1 à A3. Puis retournez à la seconde feuille de calcul pour constater le changement.

Il est aussi possible d'aller chercher des nombres provenant d'autres classeurs. Vous avez peut être déjà vu un exemple sur la page des opérations de base. Mais il y a aussi une autre façon de lier des classeurs en utilisant le collage spécial.

 

mis à jour le 30.12.2010