This One Keyboard Shortcut in Google Sheets Will Change How You Edit Formulas

Il existe trois types de références cellulaires dans Google Sheets: les références relatives changent en fonction de leur emplacement, les références absolues restent fixes, quel que soit leur lieu, et les références mixtes font les deux. Un raccourci de clavier simple – F4 – laisse vous basculer entre ceux-ci en un instant.

Pour suivre lorsque vous lisez ce guide, créez votre propre copie du manuel Google Sheets utilisé dans les captures d’écran. Après avoir cliqué sur le lien, vous serez invité à vous connecter à votre compte Google si vous ne l’avez pas déjà fait. Une fois que vous avez connecté, cliquez sur “Faire une copie” pour ajouter une copie du classeur que vous seul pouvez voir et modifier à votre Google Drive. Les onglets en bas de la fenêtre Google Sheets correspondent aux sections de cet article.

Création de références relatives dans Google Sheets

Les références cellulaires dans les formules dans Google Sheets sont relatives par défaut, ce qui signifie qu’elles s’adaptent en fonction de leur position dans la feuille de calcul. En conséquence, il n’est pas encore nécessaire d’utiliser F4, mais nous y reviendrons bientôt.

Dans cet exemple, taper:

=SUM(C2/B2)

Dans la cellule D2 et en appuyant sur Entrée divise le score de l’équipe A par le nombre de membres pour produire un score moyen des membres.

Parce que les références aux cellules C2 et B2 dans cette formule sont relatives – en d’autres termes, la formule fait référence aux cellules qui sont à une et deux endroits à gauche de la cellule active, respectivement – lorsque vous appliquez la formule aux cellules restantes dans la colonne en cliquant sur le même calcul de la poignée de remplissage dans le coin inférieur droit de la cellule D2 vers le bas, les références ajustent pour effectuer le calcul de chaque rangée respective.

Google Sheets peut même vous proposer de terminer ce processus de remplissage automatique pour vous.

Alternativement, pour accélérer encore plus ce processus, plutôt que de cliquer et de faire glisser la poignée de remplissage, double-cliquez-le. Cela oblige les feuilles Google à répéter la formule dans chaque cellule qui contient des données dans une cellule adjacente.

Copier la formule à partir de la barre de formule en haut de la fenêtre Google Sheets et la coller dans une autre cellule duplique la formule exactement telle qu’elle est. En d’autres termes, les références ne changeront pas par rapport à la position de la cellule de destination, vous devez donc travailler dans la feuille de calcul elle-même pour garantir que le référencement relatif fonctionne comme prévu.

En fait, peu importe où le contenu de la cellule copiée est dupliquée dans cette feuille de calcul ou une autre, elle divisera toujours la valeur dans la cellule qui est à gauche de la cellule active par la valeur dans la cellule qui est deux à gauche de la cellule active.

Enfin, pour ranger le formatage du nombre, sélectionnez toutes les cellules résultant et cliquez sur le bouton “Diminuez les décimales” sur le ruban jusqu’à ce que vous soyez satisfait de la façon dont vos données sont présentées.

Utilisation de F4 pour créer des références absolues dans Google Sheets

Comme leur nom l’indique, les références absolues dans Google Sheets restent fixes, même lorsqu’ils sont déplacés ou copiés à différents endroits dans la feuille de calcul.

Imaginez que vous avez été chargé d’élaborer le coût global de dix produits après l’ajout de taxe.

Pour ce faire, dans la cellule C2, type:

=SUM(B2+(B2*E2))

et appuyez sur Entrée.

Cependant, lorsque vous appliquez cette formule aux cellules restantes de la colonne C en double-cliquant sur la poignée de remplissage dans la cellule C2, elle ne fonctionne pas comme prévu. En effet, chaque fois que la formule se déplace vers le bas, faites donc toutes ses références cellulaires.

Par exemple, dans la cellule C3, plutôt que de référencer le taux d’imposition dans la cellule E2, la formule fait référence à la cellule E3, qui est vide.

=SUM(B3+(B3*E3))

Au lieu de cela, vous voulez seulement que la référence à la cellule de la colonne B s’ajuste en fonction de la ligne sur laquelle elle est, avec la référence à E2 restant fixe.

Pour ce faire, reproduisez d’abord à la cellule d’origine contenant la formule (dans ce cas, cellule C2), et appuyez sur F2 pour entrer le mode d’édition de cellule. Ensuite, utilisez vos flèches pour déplacer le curseur directement à côté ou dans la référence E2, et appuyez sur F4. Remarquez comment les signes en dollars sont ajoutés à la fois à la colonne et à la référence de ligne à la cellule E2.

Cela signifie que ce qui était auparavant une référence relative a maintenant été converti en une référence absolue, il restera donc toujours le même, peu importe où la formule est dupliquée dans la feuille de calcul.

La prochaine fois, plutôt que de rentrer dans la cellule, appuyez sur F4 immédiatement après avoir tapé une référence dans une formule pour la transformer en une référence absolue au fur et à mesure.

Maintenant, appuyez sur Entrée pour confirmer la formule édité, et cliquez et faites glisser la poignée de remplissage dans la cellule C2 vers le bas pour remplacer les formules incorrectes dans les cellules restantes de la colonne C par les bonnes.

Double-cliquez sur l’une des cellules résultantes pour vérifier que la formule fonctionne comme prévu avec la référence absolue à la cellule E2.

Utilisation de F4 pour créer des références mitigées dans Google Sheets

Le type de référence final dans Google Sheets est des références mixtes, qui contiennent à la fois une référence relative et une référence absolue en même temps. Ceux-ci sont utiles lorsque vous souhaitez réparer une référence de colonne, mais laissez la référence de la ligne adaptable, ou vice versa. Regardons un exemple de chacun.

Références mixtes: fixer la référence de la colonne uniquement

Dans cet exemple, disons que vous devez déterminer le salaire total de chaque employé sur trois ans avec des paiements de primes inclus.

Commencez par taper la première formule de la cellule F2 pour déterminer le paiement total à l’employé F en 2019 et en appuyant sur ENTER. Dans ce cas, c’est:

=B2+E2

Maintenant, avant d’aller de l’avant et de commencer à dupliquer cette formule, passons en revue chaque partie pour déterminer les pièces qui doivent être relatives et quelles pièces doivent être absolues:

• B: La référence à la colonne B doit être relative, de sorte que lorsque vous dupliquez la formule de la colonne F aux colonnes G et H, la formule calcule correctement le paiement total pour 2020 et 2021, respectivement.
• 2: La référence à la ligne 2 doit également être relative, car vous souhaitez que les références dans les formules s’adaptent pour les détails de chaque employé lorsque vous descendez les lignes.
• E: Chaque référence aux cellules de la colonne E ne doit pas changer, même lorsque vous dupliquez la formule de la colonne F aux colonnes G et H, donc cela doit être une référence absolue.
• 2: Comme pour la référence précédente à la ligne 2, cela doit être relatif afin qu’il s’applique aux chiffres de chaque employé.

Ainsi, en résumé, vous avez besoin de la référence à la colonne E dans chaque formule pour être absolue, avec toutes les autres références restantes relatives.

Pour ce faire, double-cliquez sur la cellule F2 pour entrer le mode d’édition cellulaire. Ensuite, placez le curseur à l’intérieur ou à côté de la référence à la cellule E2 et appuyez sur F4 (trois fois) jusqu’à ce que la référence à la colonne E soit précédée du symbole $.

=B2+$E2

Ensuite, appuyez sur Entrée pour commettre la formule, et cliquez et faites glisser la poignée de remplissage de la cellule F2 à la cellule H2 pour terminer les calculs de l’employé A.

Pour vérifier que la référence mixte a fonctionné comme prévu, double-cliquez sur les cellules G2 et vérifiez que les cellules précédentes correctes sont mises en évidence – dans ce cas, les cellules C2 et E2.

Maintenant, finalisez vos données en sélectionnant les cellules F2 à H2 et en double-cliquant sur la poignée de remplissage pour dupliquer cette formule dans les lignes restantes.

Encore une fois, double-cliquez sur n’importe quelle cellule pour confirmer que la formule qu’il contient fait référence aux cellules correctes.

Références mixtes: fixer la référence de ligne uniquement

Dans un dernier exemple, votre objectif est de déterminer combien d’argent chaque boutique devrait gagner en fonction du taux de croissance annuel attendu.

Comme toujours, commencez par taper la première formule et entraînez-vous à partir de ce point ce qui doit être relatif et ce qui doit être corrigé. Dans ce cas, dans la cellule C4, type:

=B4+C3

Pour ajouter le point de départ de 2020 pour l’atelier 1 à la croissance attendue de 2021.

Maintenant, passons en revue les références dans cette formule:

• B: Cette référence de colonne doit être relative, car vous voulez que le total soit cumulatif à mesure que vous vous déplacez à travers la rangée, ajoutant chaque total annuel à la croissance attendue de l’année suivante.
• 4: Cette référence de ligne doit également être relative, car vous voulez que la référence change selon la ligne dans laquelle elle se trouve.
• C: Comme pour la référence à la colonne B, la référence à la colonne C doit être relative, pour permettre une croissance cumulative.
• 3: La référence à la ligne 3 doit cependant être absolue, car cette ligne contient la croissance attendue de chaque magasin chaque année.

Donc, placez votre curseur dans ou à côté de la référence à la cellule C3 et appuyez sur F4 (deux fois) jusqu’à ce que le signe du dollar apparaisse avant la référence à la ligne 3.

=B4+C$3

Enfin, appliquez la formule aux cellules restantes de la plage à l’aide de la poignée de remplissage, et double-cliquez sur une cellule aléatoire pour vérifier que la référence mixte fonctionne comme prévu.

---

Si vous décidez si vous devez utiliser Google Sheets ou Microsoft Excel, il existe plusieurs différences clés entre les deux programmes que vous devriez considérer. Par exemple, Excel est connu pour offrir des outils d’analyse de données plus avancés, mais la collaboration en temps réel dans les feuilles fonctionne de manière plus transparente. Cependant, quel que soit celui que vous choisissez, sachant à quel point les références relatives, absolues et fixes fonctionnent sont essentielles pour générer des formules qui fonctionnent comme prévu.