Licence Creative Commons
Cette séance est mise à disposition selon les termes de la Licence Creative Commons Attribution - Pas d’Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International.
Auteur : Julien Ah-Pine.


3.1 Références relatives

Lorsque nous copions/collons une cellule contenant une formule dans une autre cellule, les références des cellules données en argument d’entrée de la formule saisie initialement peuvent ou pas changer dans la cellule de destination. Il s’agit de la distinction entre références relatives d’une part et références fixes d’autre part. Le but de cette section est de vous expliquer ces différents concepts et de vous indiquer leurs utilités dans l’automatisation de calculs à l’aide d’exemples.

Nous allons en effet, beaucoup procéder par l'exemple pour introduire les concepts importants des références relatives, fixes (ou absolues) et mixtes. La pratique est la meilleure façon de s'approprier ces notions. Nous comptons sur votre sérieux pour entreprendre les exercices et visionner les vidéos qui suivent afin que ce cours vous soit le plus profitable !


icone à vous de jouer À vous de jouer ! Copier/coller et recopie d'une formule

  1. Ouvrez un nouveau classeur Excel.

  2. Saisissez dans les cellules appropriées les textes, et formules afin de reproduire l'exemple ci-dessous :
    Capture d'écran montrant l'exemple attendu avec une cellule "Note", une "Sommes"A2: "1", B2: "2", A3:"3", B3:"4", C2: =A2+B2
    Où, plus précisément :
    • Nous avons saisi « Note » en A1, puis fusionner A1 avec B1.
    • Nous avons saisi « Sommes » en C1, puis fusionner C1 avec D1.

      • symbole play video Démonstration : Fusionner des cellules


    • Nous avons saisi les nombres 1, 2, 3, 4 respectivement en A2, B2, A3 et B3.
    • Nous avons saisi en C2 la formule : « =A2+B2 ».

  3. Clic droit sur C2 → Copier. Puis Clic droit sur C3 → Coller.
    Ou de façon équivalente : Clic sur C2 → CTRL + C puis Clic sur C3 → CTRL + V (« copier/coller de C2 dans C3 »). Vérifiez que vous obtenez le résultat suivant :

Capture d'écran montrant que dans C3 il y a "=A3+B3" affichant 7


icone remarque importante Remarques importantes

  • Les références de la formule de la cellule initiale C2 (« =A2+B2 ») ont changé lors de la copie et le collage dans la cellule finale C3 (« =A3+B3 »). Ces références sont dites relatives (c'est-à-dire dites relatives (vis-à-vis de la cellule initiale avant le copier/coller).

  • Ce changement de « =A2+B2 » en « =A3+B3 » est en fait donné par le déplacement (comme un pion sur un plateau de jeux quadrillé !) de la cellule initiale C2 vers la cellule finale C3 :

    • Pour aller de C2 à C3, la colonne ne change pas (C/C) mais la ligne est augmentée d'une unité (2/3)
    • Lors du copier/coller de C2 dans C3, les références relatives de la formule saisie dans C2 effectuent « ce même déplacement » lorsque l'on colle dans C3 : les références colonnes ne changent pas (A/A et B/B) mais les lignes augmentent d'une unité (2/3).
    • Le résultat affiché dans C3 est la valeur de l'addition de la valeur saisie en A3 et de la valeur saisie en B3.

icone à vous de jouer À vous de jouer ! Les formules

  1. Dans le classeur précédent faites : copier/coller de C2 dans D2. Cette fois-ci la colonne est augmentée d'une unité (C/D) mais la ligne ne bouge pas (2/2). Les références de la formule dans la cellule finale D2 vont suivre ce même déplacement. Quelle est alors la formule en D2 ?

Capture d'écran montrant la formule en D2: =B2+C2

  1. Clic sur D2 → Clic maintenu sur la poignée de recopie → Faites glissez la souris jusqu'en D3 → Relâchez le clic. Ceci revient à faire un copier/coller de D2 dans D3. Quelle est alors la formule en D3 ?
symbole play video Correction : Copier/coller et poignée de recopie

icone remarque importante Remarque importante

Dans le cas d'une formule, la poignée de recopie revient à copier/coller la formule d'une cellule vers les cellules voisines sur un axe horizontal ou vertical selon que l'on glisse la souris respectivement soit vers la droite ou la gauche, soit vers le haut ou le bas. Les références relatives sont modifiées en accord avec les déplacements de la cellule initiale vers la cellule finale comme expliqué précédemment dans le cas du copier/coller.


Copier/coller et recopie d'une fonction

Une fonction intervient dans une formule et effectue un traitement prédéfini par Excel. Les copier/coller et les recopie à l'aide de la poignée et l'impact sur les références relatives que nous avons vues précédemment dans le cas des formules sont similaires pour les fonctions.

icone à vous de jouer À vous de jouer ! Calcul de la moyenne

  1. Téléchargez le fichier Seance3Adresses et sauvegardez le dans un répertoire de votre choix (Documents par exemple) :

    Cliquer ici pour télécharger le fichier Seance3Adresses

  2. Ouvrez le fichier avec Excel.
    Remarque : Celui-ci correspond au résultat obtenu à la fin de la séance 2, excepté pour la colonne O.

  3. Calculez à nouveau dans la colonne O la moyenne de chaque étudiant, mais utilisez cette fois ci la poignée de recopie :

    • En O2, calculez pour Joseph sa moyenne en utilisant la fx MOYENNE.
    • Automatiser ce calcul de moyenne effectué pour Joseph pour les autres étudiants en utilisant la poignée de recopie : Clic sur 02 → Clic maintenu sur la poignée de recopie (petit carré en bas de la cellule active) → Faites glissez la souris vers le bas jusqu'en O6 → Relâchez le clic.

symbole play video Démonstration : Poignée de recopie

icone remarque importante Remarques importantes

  • Glissez la poignée de recopie de O2 vers O6 équivaut à copier/coller O2 dans les cellules de O3 à O6.
  • Lors de ces copier/coller, les références relatives de la formule initiale saisie en O2 changent suivant le déplacement de la cellule initiale vers la cellule finale.
Par exemple : à l'issue de la procédure de recopie, la formule en O6 correspond au copier/coller de O2 dans O6. La colonne ne change pas (O/O) mais les lignes sont augmentées de 4 unités (2/6). Ainsi, dans la formule de O6 les colonnes de la formule de O2 ne sont pas changées alors que les lignes augmentent de 4 unités.

symbole play video Démonstration : Références qui changent



3.2 Références fixes (ou absolues)

Vous l'aurez compris, par opposition aux références relatives qui changent lorsque l'on fait un copier/coller d'une cellule contenant une formule dans une autre, les références fixes (ou absolues) elles, ne vont pas changer !

  • Comment définir des références fixes ? Reprenons l'exemple précédent illustré ci-dessous. Lorsque l'on saisit une formule dans une cellule comme « =A2+B2 » dans C2, afin d'indiquer que les références A2 et B2 doivent être fixées on ajoute le symbole « $ » devant chaque identifiant de colonne et de ligne des références en jeu. Cela donne dans cet exemple : « =$A$2+$B$2 ».

Capture d'écran montrant le résultat attendu avec la formule =$A$2+$B$2

  • Copions C2 (CTRL+C) et collons dans D3 (CTRL+V) : la formule en D3 est la même qu'en C2, « =$A$2+$B$2 » !

Capture d'écran montrant la similarité des deux formules de D3 et C2

  • Lorsque les références d'une formule saisie dans une cellule initiale sont fixées, le copier/coller de cette dernière dans une cellule finale ne change pas les références initiales et ce, peu importe le déplacement de la cellule initiale vers la cellule finale.
  • L'utilisation de références fixes est centrale lorsque nous avons défini un calcul pour une ligne (ou une colonne) et que nous voulons l'automatiser à d'autres lignes (resp. colonnes) mais que ce calcul contient des « paramètres » qui ont également été saisis et qui doivent être les mêmes pour toutes les autres lignes (resp. colonnes).
  • Pour illustrer ce propos, nous allons prendre ci-après un exemple concret où nous souhaitons calculer la moyenne pondérée d'un étudiant sachant que chaque matière peut avoir un coefficient différent. Dans la vidéo qui suit nous allons utiliser la formule mathématique suivante :

Moyenne pondérée = (Note Hist * Coef Hist + ... + Note Ang * Coef Ang)/(Coef Hist + ... + Coef Ang)

icone à vous de jouer À vous de jouer ! Calcul d'une moyenne pondérée

  1. Reprenez le classeur Seance3Adresses ouvert précédemment. Insérez une nouvelle ligne au-dessus de la ligne 1.

  2. Saisissez en I1 « Coefficients des matières : » puis de J1 à N1 les valeurs respectives 4, 2 ,4, 2, 4, pour les matières Histoire, Sport, Français, Maths et Anglais.

  3. En O1, saisissez « Somme Coefficient » et dans P1 saisissez la formule utilisant la fx SOMME permettant de sommer les coefficients de toutes les matières.

  4. En P2 saisissez « Moyenne pondérée » et en P3 appliquer la formule mathématique de la moyenne pondérée dans le cas de Joseph. En pratique, il faut saisir la formule suivante (la ligne 3 correspond à Joseph) :

    (J3*J1+K3*K1+L3*L1+M3*M1+N3*N1)/P1

    Capture d'écran montrant la moyenne Pondérée

    En effet, le copier/coller de P3 dans P4 augment les lignes d'une unité supplémentaire or, J2 par exemple, n'est pas un nombre mais le texte « Histoire ». Dans la formule le terme J4*J2 revient à multiplier un nombre par un texte ce qui n'est pas le fonctionnement attendu de la multiplication *. On veut en fait fixer le coefficient de Histoire à la cellule J1 et non J2.

  5. Copier/coller P3 dans P4 (avec ou sans la poignée de recopie). Vous devez observer ceci (une erreur !) :

    6. Capture d'écran montrant que dans la cellule P4, il s'affiche "#VALEUR!"

  6. Ajouter alors le symbole « $ » à la formule dans P3 afin de fixer les coefficients de toutes les matières et également la somme des coefficients en P1. En effet, ces cellules ne doivent pas changer lors de la recopie de P3 vers les cellules en dessous. Vous devez obtenir les résultats suivants (observez notamment la barre de formules) :

Capture d'écran montrant ce qui les différentes moyennes pondérées de la colonne P (14,75/10,625/10,625/11/9,375)

symbole play video Démonstration : Calculer une moyenne pondérée avec des coefficients



3.3 Références mixtes

Précédemment, par défaut, nous avons fixé une cellule en fixant à la fois son identifiant colonne et son identifiant ligne.

  • On parle de références mixtes lorsque le code d'une cellule contient un identifiant fixe et un identifiant relatif comme par exemple « A$1 » où l'identifiant ligne est fixe ($1) et l'identifiant colonne est relatif (A -non précédé du symbole $-) ou encore $C5 où c'est l'identifiant colonne qui est fixé cette fois-ci et l'identifiant ligne est variable.
  • L'impact d'un copier/coller d'une cellule initiale vers une cellule finale sur le déplacement des identifiants des références d'une formule sera alors effectif uniquement sur ceux qui ne sont pas fixés.
symbole play video Démonstration : Références mixtes

3.4 Exercices

Nous allons mettre en pratique plusieurs concepts et traitements vus précédemment sur un nouveau jeu de données. Il s’agit de nombres de vues des épisodes de chaque saison de la série GOT. Ici, il s'agit d'automatiser des calculs de moyennes arithmétiques.

icone à vous de jouer À vous de jouer ! Audiences de la série GOT

  1. Téléchargez le fichier Seance3Got, sauvegardez le dans un répertoire de votre choix (Documents par exemple), puis ouvrez le avec Excel. Ce fichier donne l'audience en millions de vues de tous les épisodes de la série « Game Of Thrones » aux USA.

    2. Cliquer ici pour télécharger le fichier Seance3Got

  2. En K11, saisissez « Nb total de vues : » et en L11 saisissez la formule utilisant la fx SOMME permettant de calculer le nombre total de vues tout épisode et toute saison confondue.

Remarque

  • Le terme N/A indique une donnée non renseignée (Non Attribué).
  • Les cellules vides (J9 par exemple) et celles remplies de texte (I9 par exemple) sont ignorées dans la fonction SOMME et MOYENNE.
  1. En K12, saisissez « Moy nb de vues par épisode :» et en L12 saisissez la formule utilisant la fx MOYENNE permettant de calculer le nombre moyen de vues tout épisode et toute saison confondue.
  2. En L2, saisissez « Total nb de vues de chaque saison ». En L3, saisissez la formule utilisant la fx SOMME permettant de calculer le nombre total de vues de la saison 1. Automatiser ce calcul pour toutes les autres saisons.
  3. En M2, saisissez « Moy nb de vues par épisode de chaque saison ». En M3, saisissez la formule utilisant la fx MOYENNE permettant de calculer le nombre moyen de vues par épisode lors de la saison 1. Automatiser ce calcul pour toutes les autres saisons.
  4. Vérifiez que vous obtenez les résultats suivants et appliquez les mises en forme afin d'avoir un rendu similaire à ci-dessous.

Capture d'écran montrant le résultat attendu et la mise en forme ColonneL: Données vert gras ColonneM: Données jaune gras etc

symbole play video Correction : Fonction SOMME, MOYENNE et mise en page

Cette fois-ci, il s’agit de traiter des statistiques par pays relatives à la coupe du monde de foot depuis sa création jusqu’en 2018. Ici, vous aurez à utiliser des références fixes.

icone à vous de jouer À vous de jouer ! Statistiques des coupes du monde de foot (avant 2022)

  1. Téléchargez le fichier Seance3Foot, sauvegardez le dans un répertoire de votre choix (Documents par exemple), puis ouvrez le avec Excel. Ce fichier donne le nombre de 1ère, 2ème, 3ème et 4ème places des pays ayant gagné au moins une fois la coupe du monde de foot avant 2022.

    2. Cliquer ici pour télécharger le fichier Seance3Foot

  2. Insérez une nouvelle feuille intitulée « Pourc de victoire ». Copiez/collez dans cette nouvelle feuille la plage A1 :B9 de Feuil1.

  3. En A10, saisissez « Total : ». En B10 saisissez la formule donnant la somme des nombres B2:B9. À quoi correspond ce nombre total ?

  4. En C1, saisissez « Pourc de victoires ». En C2 saisissez la formule calculant le pourcentage de victoire du Brésil. Modifiez le format pour faire apparaître des pourcentages % : Clic sur C2 → Format de cellule → Clic sur Pourcentage dans Catégorie.

  5. Automatiser ce calcul de pourcentage pour les autres pays. Vérifiez ensuite en C10 que la somme des pourcentages fait bien 100%.

Capture d'écran montrant le résultat final attendu (automatisation du pourcentage)

symbole play video Correction : Les pourcentages




La conception de ce cours a bénéficié d'un financement de :

Logo gouvernement “France Relance”  Logo Union Européenne “Financé par” Logo « Ministère de l’enseignement supérieur et de la recherche »Logo « Hybrid it » Université de Lyon Logo Université de Lyon

Modifié le: mercredi 12 juillet 2023, 14:08