Référence relative et absolue des cellules et formatage

Dans cette leçon, nous aborderons les références de cellule, comment copier ou déplacer une formule et formater les cellules. Pour commencer, clarifions ce que nous entendons par références de cellules, qui sous-tendent une grande partie de la puissance et de la polyvalence des formules et des fonctions. Une compréhension concrète du fonctionnement des références de cellules vous permettra de tirer le meilleur parti de vos feuilles de calcul Excel.!

NAVIGATION SCOLAIRE

1. Pourquoi avez-vous besoin de formules et de fonctions?
2. Définir et créer une formule
3. Référence relative et absolue des cellules et formatage
4. Fonctions utiles à connaître
5. Recherches, graphiques, statistiques et tableaux croisés dynamiques

Remarque: nous allons simplement supposer que vous savez déjà qu'une cellule est l'un des carrés de la feuille de calcul, organisés en colonnes et en lignes référencées par des lettres et des chiffres horizontaux et verticaux..

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

Une «référence de cellule» désigne la cellule à laquelle une autre cellule fait référence. Par exemple, si vous êtes dans la cellule A1, vous avez = A2. Alors A1 se réfère à A2.

Passons en revue ce que nous avons dit dans la leçon 2 à propos des lignes et des colonnes afin d'explorer davantage les références de cellules..

Les cellules de la feuille de calcul sont référencées par des lignes et des colonnes. Les colonnes sont verticales et étiquetées avec des lettres. Les rangées sont horizontales et étiquetées avec des nombres.

La première cellule de la feuille de calcul est A1, ce qui signifie que la colonne A, la ligne 1, B3 fait référence à la cellule située dans la deuxième colonne, la troisième ligne, etc..

À des fins d’apprentissage sur les références de cellules, nous les écrirons parfois sous forme de ligne, colonne. Il ne s’agit pas d’une notation valide dans la feuille de calcul; elle vise simplement à rendre les choses plus claires..

Types de références de cellules

Il existe trois types de références de cellules.

Absolute - Cela signifie que la référence de cellule reste la même si vous copiez ou déplacez la cellule dans une autre cellule. Ceci est fait en ancrant la ligne et la colonne, de sorte qu'il ne change pas lors de la copie ou du déplacement.

Relatif - Le référencement relatif signifie que l'adresse de la cellule change lorsque vous la copiez ou la déplacez. c'est-à-dire que la référence de la cellule est relative à son emplacement.

Mixte - Cela signifie que vous pouvez choisir d'ancrer la ligne ou la colonne lorsque vous copiez ou déplacez la cellule, de sorte que l'une des modifications et l'autre pas. Par exemple, vous pouvez ancrer la référence de ligne, puis déplacer une cellule vers le bas sur deux lignes et sur quatre colonnes et la référence de ligne reste la même. Nous allons expliquer cela plus loin.

Références relatives

Reportons-nous à cet exemple précédent - supposons que dans la cellule A1, nous avons une formule qui dit simplement = A2. Cela signifie que Excel génère dans la cellule A1 ce qui est entré dans la cellule A2. Dans la cellule A2, nous avons saisi «A2». Excel affiche donc la valeur «A2» dans la cellule A1..

Supposons maintenant que nous ayons besoin de faire de la place dans notre feuille de calcul pour plus de données. Nous devons ajouter des colonnes au-dessus et des lignes à gauche, nous devons donc déplacer la cellule vers le bas et à droite pour faire de la place..

Lorsque vous déplacez la cellule vers la droite, le numéro de colonne augmente. Lorsque vous le déplacez vers le bas, le numéro de la ligne augmente. La cellule sur laquelle elle pointe, la référence de cellule, change également. Ceci est illustré ci-dessous:

Continuant avec notre exemple, et en regardant le graphique ci-dessous, si vous copiez le contenu de la cellule A1 deux à droite et 4 en bas, vous l’avez déplacé dans la cellule C5..

Nous avons copié la cellule deux colonnes à droite et quatre en bas. Cela signifie que nous avons changé la cellule qui fait référence à deux dans et quatre en bas. A1 = A2 est maintenant C5 = C6. Au lieu de faire référence à A2, maintenant la cellule C5 fait référence à la cellule C6.

La valeur affichée est 0 car la cellule C6 est vide. Dans la cellule C6, nous tapons «Je suis C6» et maintenant C5 affiche «Je suis C6».

Exemple: formule de texte

Essayons un autre exemple. Vous souvenez-vous de la leçon 2 où nous avons dû scinder un nom complet en prénom et nom? Que se passe-t-il quand on copie cette formule?

Écrivez la formule = RIGHT (A3, LEN (A3) - FIND (“,”, A3) - 1) ou copiez le texte dans la cellule C3. Ne copiez pas la cellule réelle, mais seulement le texte, copiez le texte, sinon la mise à jour sera mise à jour.

Vous pouvez modifier le contenu d'une cellule en haut d'une feuille de calcul dans la zone à côté de l'indication "fx". Cette zone est plus longue qu'une cellule de largeur, ce qui facilite son édition..

Maintenant nous avons:

Rien de compliqué, nous venons d'écrire une nouvelle formule dans la cellule C3. Maintenant, copiez C3 dans les cellules C2 et C4. Observez les résultats ci-dessous:

Maintenant nous avons les prénoms d'Alexander Hamilton et Thomas Jefferson.

Utilisez le curseur pour mettre en surbrillance les cellules C2, C3 et C4. Pointez le curseur sur la cellule B2 et collez le contenu. Regardez ce qui s'est passé - nous obtenons une erreur: "#REF." Pourquoi est-ce?

Lorsque nous avons copié les cellules de la colonne C à la colonne B, la colonne de référence de gauche a été mise à jour = RIGHT (A2, LEN (A2) - FIND (“,”, A2) - 1).

Il a changé chaque référence à A2 dans la colonne à gauche de A, mais il n'y a pas de colonne à gauche de la colonne A. Donc, l'ordinateur ne sait pas ce que vous voulez dire.

La nouvelle formule dans B2 par exemple, est = RIGHT (#REF!, LEN (#REF!) - FIND («,», # REF!) - 1) et le résultat est #REF:

Copier une formule dans une plage de cellules

La copie de cellules est très pratique car vous pouvez écrire une formule et la copier dans une grande zone et la référence est mise à jour. Cela évite d'avoir à éditer chaque cellule pour s'assurer qu'elle pointe au bon endroit.

Par «portée», nous entendons plus d'une cellule. Par exemple, (C1: C10) désigne toutes les cellules de la cellule C1 à la cellule C10. C'est donc une colonne de cellules. Un autre exemple (A1: AZ1) est la ligne du haut de la colonne A à la colonne AZ..

Si une plage comprend cinq colonnes et dix lignes, indiquez-la en écrivant la cellule en haut à gauche et la cellule en bas à droite, par exemple A1: E10. Il s’agit d’une zone carrée qui traverse des lignes et des colonnes et non pas seulement une partie d’une colonne ou une partie d’une ligne..

Voici un exemple illustrant comment copier une cellule vers plusieurs emplacements. Supposons que nous voulions afficher nos dépenses prévues pour le mois dans un tableur afin que nous puissions établir un budget. Nous faisons un tableur comme ceci:

Copiez maintenant la formule de la cellule C3 (= B3 + C2) dans le reste de la colonne pour obtenir un solde permanent pour notre budget. Excel met à jour la référence de cellule lorsque vous la copiez. Le résultat est présenté ci-dessous:

Comme vous pouvez le constater, chaque nouvelle cellule se met à jour relatif au nouvel emplacement, la cellule C4 met donc à jour sa formule pour = B4 + C3:

La cellule C5 se met à jour en = B5 + C4, etc.:

Références absolues

Une référence absolue ne change pas lorsque vous déplacez ou copiez une cellule. Nous utilisons le signe $ pour faire une référence absolue - pour nous en rappeler, imaginez un signe dollar comme une ancre..

Par exemple, entrez la formule = $ A $ 1 dans une cellule. Le $ devant la colonne A signifie que vous ne modifiez pas la colonne, le $ devant la rangée 1 signifie que vous ne modifiez pas la colonne lorsque vous copiez ou déplacez la cellule dans une autre cellule..

Comme vous pouvez le voir dans l'exemple ci-dessous, dans la cellule B1, nous avons une référence relative = A1.Quand nous copions B1 dans les quatre cellules situées en dessous, la référence relative = A1 devient la cellule à gauche, de sorte que B2 devient A2, B3 deviennent A3, etc. Ces cellules n'ont évidemment aucune valeur entrée, donc la sortie est zéro.

Toutefois, si nous utilisons = $ A1 $ 1, comme dans C1 et que nous le copions dans les quatre cellules situées en dessous, la référence est absolue, elle ne change donc jamais et le résultat est toujours égal à la valeur de la cellule A1..

Supposons que vous gardiez une trace de vos intérêts, comme dans l'exemple ci-dessous. La formule dans C4 = B4 * B1 est le «taux d’intérêt» * «solde» = «intérêts par an».

Vous avez maintenant modifié votre budget et économisé 2 000 $ supplémentaires pour l’achat d’un fonds commun de placement. Supposons que ce soit un fonds à taux fixe et qu'il paie le même taux d'intérêt. Entrez le nouveau compte et le solde dans la feuille de calcul, puis copiez la formule = B4 * B1 de la cellule C4 vers la cellule C5..

Le nouveau budget ressemble à ceci:

Le nouveau fonds commun de placement rapporte 0 $ d’intérêts par an, ce qui ne peut pas être vrai puisque le taux d’intérêt est clairement de 5%.

Excel met en évidence les cellules auxquelles une formule fait référence. Vous pouvez voir ci-dessus que la référence au taux d'intérêt (B1) est déplacée vers la cellule vide B2. Nous aurions dû faire référence à B1 absolue en écrivant $ B $ 1 en utilisant le signe dollars pour ancrer la référence de ligne et de colonne.

Réécrivez le premier calcul en C4 pour lire = B4 * $ B $ 1 comme indiqué ci-dessous:

Copiez ensuite cette formule de C4 à C5. La feuille de calcul ressemble maintenant à ceci:

Puisque nous avons copié la cellule de formule un vers le bas, c’est-à-dire que nous avons augmenté le rang d’une ligne, la nouvelle formule est = B5 * $ B $ 1. Le taux d’intérêt des fonds communs de placement est maintenant calculé correctement, car le taux d’intérêt est ancré dans la cellule B1..

C'est un bon exemple de cas où vous pourriez utiliser un «nom» pour faire référence à une cellule. Un nom est une référence absolue. Par exemple, pour attribuer le nom «taux d'intérêt» à la cellule B1, cliquez avec le bouton droit de la souris sur la cellule, puis sélectionnez «définir un nom».

Les noms peuvent faire référence à une cellule ou à une plage et vous pouvez utiliser un nom dans une formule, par exemple = rate_rate * 8 équivaut à écrire = $ B $ 1 * 8.

Références mixtes

Les références mixtes sont quand non plus la ligne ou la colonne est ancrée.

Par exemple, supposons que vous êtes un agriculteur qui établit un budget. Vous possédez également un magasin d'alimentation et vendez des semences. Vous allez planter du maïs, du soja et de la luzerne. La feuille de calcul ci-dessous indique le coût par acre. Le «coût par acre» = «prix par livre» * «livres de semences par acre» - c'est ce qu'il vous en coûtera pour planter un acre..

Entrez le coût par acre de = B2 $ * C2 dans la cellule D2. Vous dites que vous souhaitez ancrer la colonne de prix par livre. Copiez ensuite cette formule dans les autres lignes de la même colonne:

Maintenant, vous voulez connaître la valeur de votre inventaire de semences. Vous devez connaître le prix par livre et le nombre de livres en inventaire pour connaître la valeur de l'inventaire..

Nous ajoutons deux colonnes: "livre de graine en inventaire" puis "valeur de l'inventaire". Maintenant, copiez la cellule D2 à F4 et notez que la référence de ligne dans la première partie de la formule d'origine ($ B2) est mise à jour pour la ligne 4 mais la colonne reste fixe car le $ l'ancre à «B».

Il s'agit d'une référence mixte car la colonne est absolue et la ligne est relative..

Références circulaires

Une référence circulaire est quand une formule se réfère à elle-même.

Par exemple, vous ne pouvez pas écrire c3 = c3 + 1. Ce type de calcul s'appelle «itération», ce qui signifie qu'il se répète. Excel ne prend pas en charge les itérations car il calcule tout une seule fois.

Si vous essayez cela en tapant SOMME (B1: B5) dans la cellule B5:

Un écran d'avertissement apparaît:

Excel vous indique uniquement que vous avez une référence circulaire au bas de l'écran, de sorte que vous ne le remarquerez peut-être pas. Si vous avez une référence circulaire, fermez une feuille de calcul et ouvrez-la à nouveau, Excel vous indiquera dans une fenêtre contextuelle que vous avez une référence circulaire..

Si vous avez une référence circulaire, chaque fois que vous ouvrez la feuille de calcul, Excel vous indiquera dans cette fenêtre contextuelle que vous avez une référence circulaire..

Références à d'autres feuilles de travail

Un «classeur» est un ensemble de «feuilles de calcul». Cela signifie simplement que vous pouvez avoir plusieurs feuilles de calcul (feuilles de calcul) dans le même fichier Excel (classeur). Comme vous pouvez le voir dans l'exemple ci-dessous, notre exemple de classeur contient de nombreuses feuilles de calcul (en rouge)..

Les feuilles de calcul par défaut sont nommées Sheet1, Sheet2 et ainsi de suite. Vous créez un nouveau en cliquant sur le "+" en bas de l'écran Excel.

Vous pouvez modifier le nom de la feuille de calcul en quelque chose d’utile, comme «prêt» ou «budget», en cliquant avec le bouton droit de la souris sur l’onglet de la feuille de calcul affiché au bas de l’écran du programme Excel, en sélectionnant renommer et en tapant un nouveau nom.

Ou vous pouvez simplement double-cliquer sur l'onglet et le renommer.

La syntaxe pour une référence de feuille de calcul est = feuille de calcul! Cellule. Vous pouvez utiliser ce type de référence lorsque la même valeur est utilisée dans deux feuilles de calcul, par exemple:

• La date d'aujourd'hui
• Taux de conversion des devises de Dollars en Euros
• Tout ce qui concerne toutes les feuilles de calcul du classeur

Vous trouverez ci-dessous un exemple de feuille de calcul «intérêt» faisant référence à la feuille de calcul «prêt», cellule B1..

Si nous regardons la feuille de calcul «prêt», nous pouvons voir la référence au montant du prêt:

À suivre…

Nous espérons que vous maîtriserez parfaitement les références de cellules, notamment relatives, absolues et mixtes. Il y a certainement beaucoup.

C’est tout pour la leçon d’aujourd’hui. Dans la leçon 4, nous aborderons quelques fonctions utiles que vous voudrez peut-être connaître pour une utilisation quotidienne dans Excel..