LeCompagnon.info en reconstruction

Excel

Excel : Copier et position relative et absolue

Introduction
Copier
Position relative et absolue
Exercice du taux de change
Collage spécial

Introduction

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.

Introduction à Excel
Menu Excel
Fichier
Accueil
Insertion
Mise en page
Formules
Données
Révision
Affichage
Thèmes
Opérations de base
Présentation
Mise en page et Impression
Fonctions
Fonctions matricielles
Copier, coller
Référence relative et absolue
Graphiques
Formules 3D
Outils d'analyse
Commentaires
Liste de données
Macro commandes
Tableau croisé dynamique
Exercices : création de modèle
Graphique: Insérer un second axe
Graphique: créer un graphique demi-lune
Graphique: Créer une pyramide d'âge
Gestionnaire de scénarios
Mise en forme conditionnelle
Remplissage instantané et analyse rapide
Solveur
Somme automatique
Sous-total
Tableau croisé dynamique
Table de données (1 et 2 variables)
Valeur cible
Validation - Liste de choix
Quiz

Autres formations

Word 2003
Word 2007
Excel
VBA pour Excel
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière
Vidéos

Navigation
Précédent
Suivant
Page Principale
FAQ
Nouveautés
Plan du site
Références
Motivations
Manuels de formation
Fichiers de démonstration
Droits d'auteur
Text in English
Nous joindre
Par courriel
Abonnez-vous à
l'infolettre LCI




















Abonnez-vous à l'infolettre LeCompagnon.info














Copier

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 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.

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 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:

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

*É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.

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 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 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 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.

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? ( 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.

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.

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 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 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.
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 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.

Cliquez ici pour vous abonner à l'infolettre





Ce site est hébergé par 1&1.com