Pour utiliser efficacement un chiffrier, 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 chiffrier.
L'un des grands avantages d'un chiffrier 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 . 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 . 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.
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.
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 vers la droite.
Le contenu de la cellule va se recopier dans les cellules sélectionnées. Vous
pouvez aussi copier le contenu d'une celle dans n'importe quelle direction
(gauche, droite, haut et bas). 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
chiffres 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èrent 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.
Quiz: Si on recopie la formule +A1+A2 de la cellule A3 dans la cellule Z20, à
quoi ressemblera la formule? La réponse est à la fin de cette page.
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 chiffres 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êt 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 longue
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$1
La ligne est gelée mais pas la colonne.
$A$1
La ligne et la colonne sont gelées.
Exemple:
Écrivez les
chiffres 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
Il y a aussi 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 noir. 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
B3
100
+$A1+B2
C3
100
+$A1+C2
D3
100
+$A1+D2
E3
100
+$A1+E2
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.
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
recopier. Il consiste à changer les valeurs en dollars américains en dollars
canadiens.
Écrivez les
chiffres et les formules dans les cellules appropriées.
Pour faciliter la compréhension, on présume que 1 dollar américain équivaut à
1,4 dollar canadien, d'où la raison pourquoi il y a 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 . 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 . 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:
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 chiffres
ou de 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.
Cela serait vraiment dommage d'avoir un modèle inutile à cause que vous avez
oublié de changer le contenu d'une ou deux cellules!
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 en tout temps 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 y 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
chiffre 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 au
début de la page.
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.
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? ( Rép.: modifier une cellule )
Mais il faut faire attention de la manière qu'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.
Quel taux de change! Vérifions le contenu des cellules A4 à
A8.
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.
S.V.P. Prenez beaucoup de pratique avec
cette technique. Elle est capitale pour utiliser efficacement un classeur. Créez
vos propres exercices.
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
chiffres 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
. 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 chiffre qui est le résultat d'une
formule.
Formats
Colle juste les options de présentations telles 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.
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 chiffres 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 truc 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 dans 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 chiffres 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 changez 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 chiffres 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.
Abonnez-vous à l'infolettre
Abonnez-vous à l'infolettre du site pour recevoir le manuel " 150 fonctions Excel expliquées ". Vous recevrez aussi les prochaines infolettres sur les nouveautés du site et des formations détaillées que vous ne trouverez nulle part ailleurs.
Du menu Édition, sélectionnez
Copier.
.
.
