Le signe du dollar ($) dans les références cellulaires dans les formules Excel confond souvent les utilisateurs, mais son principe est simple. Le signe du dollar n'a qu'une seule fonction dans les références de cellules Excel: il indique à Excel s'il faut modifier la référence lors de la copie d'une formule vers une autre cellule. Ce tutoriel expliquera cette fonctionnalité en détail.

L'importance de la référence des cellules Excel ne peut pas être surestimée. Comprenez la différence entre les citations absolues, relatives et mixtes, et vous avez ma?trisé la moitié de la puissance des formules et des fonctions Excel.

Vous avez peut-être vu le panneau Dollar ($) en formule Excel et que vous voulez savoir de quoi il s'agit. En fait, vous pouvez vous référer à la même cellule de quatre manières différentes, comme A1, $ a 1 $, A1 $ et 1 $.

Le signe du dollar dans une référence de cellule Excel n'affecte qu'une seule chose - il indique comment Excel gère les références lors du déplacement ou de la copie d'une formule vers une autre cellule. En bref, l'utilisation du symbole $ avant la ligne et les coordonnées de la colonne crée une référence cellulaire absolue qui ne change pas. Sans utiliser le symbole $, la référence est relative et elle changera.

Si vous écrivez une formule pour une seule cellule, vous pouvez utiliser n'importe quel type de référence et la formule est correcte de toute fa?on. Cependant, si vous prévoyez de copier la formule dans une autre cellule, il est crucial de choisir le bon type de référence de cellule. Si vous vous sentez chanceux, vous pouvez essayer de lancer une médaille :) Si vous voulez le prendre au sérieux, prenez quelques minutes pour apprendre les tenants et aboutissants des références cellulaires absolues et relatives dans Excel et quand utiliser laquelle.

• Références cellulaires relatives
• Référence cellulaire absolue
• Utilisez des références cellulaires relatives et absolues dans une formule
• Références de cellules mixtes dans Excel
• Références en colonne entière et entières entières
• Basculer entre différents types de référence (touche F4)

Qu'est-ce qu'une référence de cellules Excel?

En termes simples, la référence cellulaire dans Excel est l'adresse cellulaire. Il indique à Microsoft Excel où trouver la valeur à utiliser dans la formule.

Par exemple, si vous entrez une formule simple = A1 dans la cellule C1, Excel extrait la valeur de la cellule A1 à C1:

Comme mentionné précédemment, tant que vous rédigez une formule pour une seule cellule , vous êtes libre d'utiliser n'importe quel type de référence, que vous utilisiez le signe du dollar ($) et le résultat est le même:

Cependant, si vous souhaitez déplacer ou copier des formules dans une feuille de calcul, le choix du type de référence correct est essentiel pour que la formule soit copiée correctement sur d'autres cellules. Les sections suivantes expliquent chaque type de référence de cellule en détail et fournissent des exemples de formules.

Avis. En plus du style de référence A1 (où la colonne est définie par des lettres et que la ligne est définie par les nombres), il existe également un style de référence R1C1 , où les lignes et les colonnes sont identifiées par les nombres (R1C1 représente la ligne 1, colonne 1).

étant donné que A1 est le style de référence par défaut dans Excel et est utilisé la plupart du temps, ce tutoriel ne traite que des références de type A1. Si quelqu'un utilise actuellement le style R1C1, vous pouvez le désactiver en cliquant sur Fichier > Options > Formules et décocher la case à cocher du style de référence R1C1 .

Excel Relative Cell Reference (pas de symbole $)

Une référence relative dans Excel est une adresse cellulaire sans le symbole $ dans les coordonnées de ligne et de colonne, telles que A1 .

Lorsqu'une formule contenant une référence à une cellule relative est copiée dans une autre cellule, la référence change en fonction de la position relative de la ligne et de la colonne. Par défaut, toutes les références dans Excel sont relatives. L'exemple suivant montre comment fonctionnent les références relatives.

Supposons que vous ayez la formule suivante dans la cellule B1:

=A1*10

Si vous copiez cette formule dans une autre ligne dans la même colonne, comme la cellule B2, la formule sera ajustée à la ligne 2 (A2 * 10), car Excel suppose que vous souhaitez multiplier la valeur de chaque ligne dans la colonne A par 10.

Si vous copiez une formule contenant une référence de cellule relative à une autre colonne dans la même ligne, Excel modifie la référence de la colonne en conséquence:

Si vous copiez ou déplacez une formule Excel contenant une référence de cellule relative à une autre ligne et à une autre colonne , les références de colonne et de ligne sont modifiées:

Comme vous pouvez le voir, l'utilisation de références de cellules relatives dans des formules Excel est un moyen très pratique d'effectuer les mêmes calculs tout au long de la feuille de calcul. Pour mieux illustrer cela, discutons un vrai exemple.

Utilisation de références relatives dans un exemple de formule Excel

Supposons que vous ayez une colonne de prix USD (colonne B) dans votre feuille de calcul que vous souhaitez convertir en Euro. Le taux d'échange en dollar-euro est connu (0,93 au moment de la rédaction), et la formule à la ligne 2 est simple, c'est-à-dire =B2*0.93 . Notez que nous utilisons des références de cellules relatives Excel sans signes en dollars.

Appuyez sur Entrée pour calculer la formule et les résultats seront immédiatement affichés dans la cellule.

indice. Par défaut, toutes les références cellulaires dans Excel sont des références relatives. Ainsi, lors de l'écriture d'une formule, vous pouvez ajouter des références relatives en cliquant sur la cellule correspondante sur la feuille de calcul au lieu de taper manuellement la référence de la cellule.

Pour copier la formule vers le bas , survolez la poignée de remplissage (le petit carré dans le coin inférieur droit de la cellule sélectionnée). Lorsque vous faites cela, le curseur se transforme en une fine croix noire que vous pouvez tenir et faire glisser sur la cellule que vous souhaitez automatiquement.

C'est ?a! La formule a été copiée dans d'autres cellules et sa référence relative a été ajustée correctement pour chaque cellule. Pour vous assurer que les valeurs de chaque cellule sont calculées correctement, sélectionnez n'importe quelle cellule et affichez la formule dans la barre de formule. Dans cet exemple, je sélectionne la cellule C4 et je vois que la référence cellulaire dans la formule est relative à la ligne 4, ce à quoi elle devrait ressembler:

Excel Absolute Cell Reference (avec $ signe)

Une référence absolue dans Excel est une adresse cellulaire avec un signe en dollars ($) dans les coordonnées de ligne ou de colonne, telles que 1 $ $ .

Le signe du dollar épingle la référence à la cellule donnée afin qu'elle reste la même, peu importe où la formule se déplace. En d'autres termes, l'utilisation de références de cellules $ dans les cellules vous permet de copier des formules Excel sans modifier la référence.

Par exemple, s'il y en a 10 dans la cellule A1 et que vous utilisez une référence cellulaire absolue ( $ a 1 $ ), la formule =$A$1 5 reviendra toujours 15 quelles que soient les autres cellules dont la formule est copiée. D'un autre c?té, si vous écrivez la même formule en utilisant une référence de cellule relative ( A1 ), puis le copiez vers d'autres cellules de la colonne, une valeur différente sera calculée pour chaque ligne. La figure suivante montre la différence:

Avis. Bien que nous ayons dit que les références absolues dans Excel ne changent jamais, en réalité, lorsque vous ajoutez ou supprimez des lignes et / ou des colonnes dans votre feuille de calcul, cela change, ce qui modifie la position de la cellule référencée. Dans l'exemple ci-dessus, si nous insérons une nouvelle ligne en haut de la feuille de calcul, Excel est assez intelligent pour ajuster la formule pour refléter le changement:

Dans les feuilles de travail réelles, il est très rare d'utiliser uniquement des références absolues dans des formules Excel. Cependant, il existe de nombreuses taches qui nécessitent des références absolues et relatives, comme le montre l'exemple suivant.

Avis. Les références cellulaires absolues ne doivent pas être confondues avec des valeurs absolues, qui ne sont pas prises en compte dans la taille du nombre de son symbole.

Utilisez des références cellulaires relatives et absolues dans une formule

Vous pouvez souvent avoir besoin d'une formule où certaines références cellulaires sont ajustées aux colonnes et aux lignes pour lesquelles la formule est copiée, tandis que d'autres restent fixes sur une cellule spécifique. En d'autres termes, vous devez utiliser des références cellulaires relatives et absolues dans une formule.

Exemple 1. Références de cellules relatives et absolues utilisées pour calculer les nombres

Dans nos précédents exemples de prix américains et euros, vous ne voulez probablement pas coder le taux de change dans la formule. Au lieu de cela, vous pouvez saisir ce numéro dans une cellule, comme C1, et utiliser le signe du dollar ($) pour fixer cette référence de cellule dans la formule, comme indiqué dans la capture d'écran ci-dessous:

Dans cette formule (B4 * $ C 1 $), il existe deux types de référence cellulaires:

• B4 - références de cellules relatives , qui seront ajustées pour chaque ligne, et
• $ C 1 $ - Référence cellulaire absolue , qui ne changera pas partout où la formule est copiée.

L'avantage de cette approche est que vos utilisateurs peuvent calculer le prix de l'euro en fonction des taux de change variables sans modifier la formule. Une fois que le taux de conversion change, il vous suffit de mettre à jour la valeur dans la cellule C1.

Exemple 2. Références de cellules relatives et absolues utilisées pour calculer les dates

Une autre utilisation courante de l'utilisation de références cellulaires absolues et relatives dans une formule consiste à calculer les dates dans Excel en fonction de la date d'aujourd'hui.

Supposons que vous ayez une colonne de dates de livraison dans la colonne B et que vous entrez la date actuelle en C1 à l'aide de la fonction aujourd'hui (). Si vous souhaitez conna?tre l'heure d'expédition pour chaque article, vous pouvez le calculer en utilisant la formule suivante: =B4-$C$1

De même, nous utilisons deux types de référence dans la formule:

• Des références relatives sont utilisées pour les cellules contenant la première date de livraison (B4), car vous voulez que cette référence cellulaire varie en fonction de la ligne dans laquelle la formule est située.
• Une référence absolue est utilisée pour les cellules contenant la date d'aujourd'hui ($ C 1 $), car vous voulez que cette référence cellulaire reste la même.

En résumé, chaque fois que vous souhaitez créer une référence de cellule statique Excel qui fait toujours référence à la même cellule, assurez-vous d'inclure le signe du dollar ($) dans la formule pour créer une référence absolue dans Excel.

Excel références à cellules mixtes

Une référence de cellules mixtes dans Excel fait référence à une référence avec des lettres de colonne fixes ou des numéros de ligne. Par exemple, A1 $ A1 et A 1 $ sont des références mitigées. Mais que signifie chacun? C'est très simple.

Vous vous souvenez que les références absolues Excel contiennent des panneaux de 2 dollars ($) que les colonnes et les lignes de verrouillage. Dans une référence cellulaire mixte, une seule coordonnée est fixe (absolue) et l'autre (relative) variera en fonction de la position relative de la ligne ou de la colonne:

• Colonnes absolues et lignes relatives , telles que A1 $. Lorsqu'une formule contenant ce type de référence est copiée dans une autre cellule, le symbole $ avant la lettre de colonne verrouille la référence à la colonne spécifiée afin qu'elle ne change jamais. Les références de ligne relatives (aucun signe en dollars) varieront selon les lignes copiées dans la formule.
• Colonnes relatives et lignes absolues , comme 1 $. Dans ce type de référence, la référence de ligne ne change pas et la référence de la colonne ne change pas.

Vous trouverez ci-dessous des exemples en utilisant ces deux types de référence de cellules mixtes, en espérant que cela rendra les choses plus faciles à comprendre.

Utilisation de références mixtes dans Excel - Formule Exemple

Dans cet exemple, nous utiliserons à nouveau notre table de conversion de devises. Mais cette fois, nous ne nous limiterons pas à la conversion USD-Euro. Ce que nous allons faire, c'est convertir le prix du dollar américain en plusieurs autres devises, tous utilisant une seule formule !

Tout d'abord, entrons le taux de conversion dans une certaine ligne, par exemple, la ligne 2, comme indiqué dans la capture d'écran ci-dessous. Vous pouvez ensuite écrire une formule pour la cellule supérieure gauche (C5 dans cet exemple) pour calculer le prix de l'euro:

=$B5*C$2

où B5 $ est le prix du dollar américain dans la même rangée et C 2 $ est le taux de conversion du dollar-euro américain.

Copiez maintenant la formule dans d'autres cellules de la colonne C et remplissez automatiquement les autres colonnes avec la même formule en faisant glisser la poignée de remplissage. En conséquence, vous aurez 3 colonnes de prix différentes qui sont correctement calculées en fonction du taux de change correspondant dans la ligne 2 dans la même colonne. Pour vérifier cela, sélectionnez n'importe quelle cellule dans le tableau et affichez la formule dans la barre de formule.

Par exemple, sélectionnons la cellule D7 (dans la colonne Pound). Ce que nous voyons ici, c'est la formule =$B7*D$2 , qui prend le prix du dollar américain en B7 et le multiplie par la valeur en D2, c'est-à-dire le taux de conversion Dollar-GBP, qui est exactement ce que nous voulons :)

Voyons maintenant comment Excel sait exactement quel prix prendre et quel taux de change le multiplier par. Comme vous l'avez peut-être deviné, c'est là que fonctionne la référence de cellules mixtes ($ b5 * c 2 $).

• $ B5 - colonnes absolues et lignes relatives . Ici, vous ne ajoutez que le signe du dollar ($) avant les lettres de colonne pour épingler la référence à la colonne A, donc Excel utilise toujours le prix du dollar d'origine pour tous les convertis. La référence de la ligne (pas de signe $) n'est pas verrouillée car vous souhaitez calculer le prix de chaque ligne séparément.
• C 2 $ - Colonnes relatives et lignes absolues . étant donné que tous les taux de change sont sur la ligne 2, vous verrouillez la référence de ligne en ajoutant un signe en dollars ($) avant le numéro de ligne. Maintenant, quelle que soit la ligne à laquelle vous copiez la formule, Excel recherche toujours le taux de change dans la ligne 2. Et puisque la référence de la colonne est relative (pas de signe $), il ajustera la colonne à laquelle la formule est copiée.

Comment référencer une colonne entière ou une ligne entière dans Excel

Lorsque vous utilisez une feuille de calcul Excel avec un nombre variable de lignes, vous pouvez référencer toutes les cellules dans une colonne spécifique. Pour référencer une colonne entière, tapez simplement la lettre de colonne deux fois et ajoutez un c?lon entre les deux, comme a: a .

Référence de la colonne entière

Comme les références de cellules, les références en colonne entières peuvent être absolues et relatives, par exemple:

• Références de colonnes absolues , telles que $ a: $ a
• Références de colonne relative , comme un: un

Encore une fois, vous le verrouillez dans une colonne spécifique en utilisant le signe du dollar ($) dans votre référence de colonne absolue afin que la référence entière de la colonne ne change pas lorsque vous copiez la formule dans une autre cellule.

Lorsque vous copiez ou déplacez une formule vers une autre colonne, la référence de la colonne relative change, et lorsque vous copiez la formule dans une autre cellule dans la même colonne, elle reste la même.

Référence de ligne entière

Pour référencer la ligne entière, vous pouvez utiliser la même méthode, sauf que vous tapez le numéro de ligne au lieu des lettres de colonne:

• Référence de ligne absolue , comme 1 $: 1 $
• Références de ligne relative , par exemple 1: 1

En théorie, vous pouvez également créer des références de colonnes entières mixtes ou des références mixtes entières entières , telles que $ a: a ou 1: 1. Je dis "théorie" parce que je ne peux penser à aucune application pratique d'une telle citation, bien que l'exemple 4 prouve que les formules avec une telle citation fonctionnent exactement comme prévu.

Exemple 1. Référence de la colonne entière Excel (absolue et relative)

Supposons que vous ayez quelques chiffres dans la colonne B et que vous souhaitez découvrir leur somme et leur moyenne. Le problème est que de nouvelles lignes sont ajoutées au tableau chaque semaine, donc l'écriture de la formule Sum () ou moyenne () pour les cellules à portée fixe n'est donc pas une manière viable. Au lieu de cela, vous pouvez référencer la colonne entière B:

=SUM($B:$B) - Utilisez le signe du dollar ($) pour créer une référence de colonne entière absolue , verrouillant la formule à la colonne B.

=SUM(B:B) - écrivez une formule sans le symbole $ pour créer une référence de colonne entière relative , qui changera lorsque vous copiez la formule dans une autre colonne.

indice. Lorsque vous écrivez une formule, cliquez sur la lettre de colonne pour ajouter la référence entière de la colonne à la formule. Comme les références de cellules, Excel inserte les références relatives par défaut (pas de symbole $):

De même, nous écrivons une formule pour calculer le prix moyen de toute la colonne B:

=AVERAGE(B:B)

Dans cet exemple, nous utilisons une référence de colonne entière relative, donc lorsque nous le copie dans d'autres colonnes, notre formule obtient le bon ajustement:

Avis. Lorsque vous utilisez des références de colonne entières dans des formules Excel, ne saisissez jamais des formules dans la même colonne. Par exemple, il semble que la formule entrant = sum (b: b) dans une cellule vide entrez la formule = sum (b: b) dans la cellule Bottommost de la colonne B pour afficher la somme à la fin de la même colonne. Ne fais pas ?a! Cela créera une référence dite circulaire et la formule reviendra 0.

Exemple 2. Référence Excel entière (absolue et relative)

Si les données d'une table Excel sont organisées par des lignes plut?t que par des colonnes, vous pouvez vous référer à la ligne entière dans la formule. Par exemple, c'est ainsi que nous calculons le prix moyen de la ligne 2:

=AVERAGE($2:$2) - La référence absolue de ligne entière est verrouillée sur une ligne spécifique en utilisant le signe du dollar ($).

=AVERAGE(2:2) - Lors de la copie de la formule à une autre ligne, la référence par rapport à la ligne entière changera.

Dans cet exemple, nous avons besoin d'une référence par rapport à toute la ligne, car nous avons 3 rangées de données, et nous voulons calculer la valeur moyenne de chaque ligne en copie la même formule:

Exemple 3. Comment référencer la colonne entière (à l'exclusion des premières lignes)

Il s'agit d'une question très chaude car les premières lignes d'une feuille de calcul contiennent généralement des termes d'introduction ou des informations explicatives que vous ne souhaitez pas inclure dans vos calculs. Malheureusement, Excel n'autorise pas de références comme B5: B, il contiendra toutes les lignes de la colonne B à partir de la ligne 5. Si vous essayez d'ajouter une telle référence, votre formule renverra probablement une erreur #Name.

Au lieu de cela, vous pouvez spécifier la ligne maximale afin que votre référence contienne toutes les lignes possibles dans la colonne donnée. Dans Excel 2016, 2013, 2010 et 2007, les valeurs maximales sont de 1 048 576 lignes et 16 384 colonnes. Les versions antérieures d'Excel avaient une ligne maximale de 65 536 et un maximum de colonne de 256.

Par conséquent, pour trouver la valeur moyenne de chaque colonne de prix dans le tableau suivant (colonnes B à D), entrez la formule suivante dans la cellule F2 et copiez-la dans les cellules G2 et H2:

=AVERAGE(B5:B1048576)

Si vous utilisez la fonction SUM, vous pouvez également soustraire les lignes que vous souhaitez exclure:

=SUM(B:B)-SUM(B1:B4)

Exemple 4. Utilisation de références de colonnes entières mixtes dans Excel

Comme je l'ai mentionné dans les paragraphes précédents, vous pouvez également créer des colonnes entières mixtes ou des références entières dans Excel:

• Références à colonnes mixtes, telles que $ a: a
• Références de lignes mixtes, telles que 1 $: 1

Voyons maintenant ce qui se passe lorsque vous copiez la formule contenant de telles références à d'autres cellules. Supposons que vous entrez la formule =SUM($B:B) dans une certaine cellule, F2 dans cet exemple. Lorsque vous copiez la formule sur la cellule de main droite adjacente (G2), elle se transforme en =SUM($B:C) parce que le premier B est fixé avec le symbole $, tandis que le second ne le fait pas. En conséquence, la formule résume tous les nombres dans les colonnes B et C. Je ne sais pas si cela a une valeur réelle, mais vous vous demandez peut-être comment cela fonctionne:

avertir! N'utilisez pas trop de références en colonne / ligne complète dans une feuille de calcul, car elles peuvent ralentir Excel.

Comment changer de références absolues, relatives et mixtes (clé F4)

Lorsque vous écrivez des formules Excel, vous pouvez bien s?r taper manuellement le symbole $ pour modifier la référence cellulaire relative à une référence absolue ou mixte. Alternativement, vous pouvez appuyer sur F4 pour accélérer. Pour utiliser les touches de raccourci F4, vous devez être en mode d'édition de formule:

1. Sélectionnez la cellule qui contient la formule.
2. Appuyez sur F2 ou double-cliquez sur la cellule pour entrer en mode d'édition.
3. Sélectionnez la référence cellulaire pour changer.
4. Appuyez sur F4 pour basculer entre les quatre types de référence de cellules.

Si vous sélectionnez une référence de cellule relative sans le signe $, tel que A1, appuyez à plusieurs reprises sur la touche F4 pour basculer entre une référence absolue contenant deux signes en dollars (tels que $ a 1 $), la ligne absolue A 1 $, la colonne absolue $ A1, puis revenez à la référence relative A1.

Avis. Si vous appuyez sur F4 sans sélectionner de références de cellule, la référence à gauche du curseur sera automatiquement sélectionnée et modifiée en un autre type de référence.

J'espère que vous comprenez maintenant pleinement ce que sont les références de cellules relatives et absolues, et que les formules excel avec le signe $ ne sont plus un mystère. Dans les prochains articles, nous continuerons à en savoir plus sur divers aspects des références de cellules Excel, telles que la référence d'une autre feuille de travail, des références 3D, des références structurées, des références circulaires, etc. Merci d'avoir lu pendant cette période et j'espère vous voir sur notre blog la semaine prochaine!

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!