Liens dans Excel – absolus, relatifs et mixtes. Erreurs lors de l'utilisation de liens relatifs dans Excel

Pour faciliter les calculs à l'aide de formules en mode automatique, des références aux cellules sont utilisées. Selon le type d'écriture, ils sont divisés en trois types principaux:

1. Liens relatifs. Utilisé pour des calculs simples. Copier une formule implique de changer les coordonnées.
2. Liens absolus. Si vous devez produire des calculs plus complexes, cette option est appropriée. Pour la fixation, utilisez le symbole "$". Exemple : $A$1.
3. liens mixtes. Ce type d'adressage est utilisé dans les calculs lorsqu'il est nécessaire de fixer une colonne ou une ligne séparément. Exemple : $A1 ou A$1.

S'il est nécessaire de copier les données de la formule saisie, des références avec adressage absolu et mixte sont utilisées. L'article révélera avec des exemples comment les calculs sont effectués à l'aide de différents types de liens.

Référence de cellule relative dans Excel

Il s'agit d'un ensemble de caractères qui définissent l'emplacement d'une cellule. Les liens dans le programme sont automatiquement écrits avec un adressage relatif. Par exemple : A1, A2, B1, B2. Le déplacement vers une autre ligne ou colonne modifie les caractères de la formule. Par exemple, position de départ A1. Se déplacer horizontalement change la lettre en B1, C1, D1, etc. De la même manière, des changements se produisent lors du déplacement le long d'une ligne verticale, seulement dans ce cas le nombre change - A2, A3, A4, etc. S'il est nécessaire de dupliquer un calcul du même type dans une cellule adjacente, un calcul est effectué en utilisant une référence relative. Pour utiliser cette fonctionnalité, suivez quelques étapes :

1. Dès que les données sont saisies dans la cellule, déplacez le curseur et cliquez avec la souris. La mise en surbrillance avec un rectangle vert indique l'activation de la cellule et la préparation pour d'autres travaux.
2. En appuyant sur la combinaison de touches Ctrl + C, nous copions le contenu dans le presse-papiers.
3. Nous activons la cellule dans laquelle vous souhaitez transférer des données ou une formule précédemment écrite.
4. En appuyant sur la combinaison Ctrl + V, nous transférons les données enregistrées dans le presse-papiers du système.

Conseil d'Expert! Pour effectuer le même type de calculs dans le tableau, utilisez un life hack. Sélectionnez la cellule contenant la formule saisie précédemment. En plaçant le curseur sur un petit carré qui apparaît dans le coin inférieur droit et en maintenant le bouton gauche de la souris enfoncé, faites glisser vers la ligne du bas ou la colonne extrême, selon l'action effectuée. En relâchant le bouton de la souris, le calcul sera effectué automatiquement. Cet outil s'appelle le marqueur de remplissage automatique.

Exemple de lien relatif

Pour que ce soit plus clair, considérons un exemple de calcul utilisant une formule avec une référence relative. Supposons que le propriétaire d'un magasin de sport, après un an de travail, doive calculer le bénéfice des ventes.

L'ordre des actions :

1. L'exemple montre que les colonnes B et C ont été utilisées pour renseigner la quantité de biens vendus et son prix. En conséquence, pour écrire la formule et obtenir la réponse, sélectionnez la colonne D. La formule ressemble à ceci : = B2*C

Faites attention! Pour faciliter le processus d'écriture d'une formule, utilisez une petite astuce. Mettez le signe "=", cliquez sur la quantité de biens vendus, placez le signe "*" et cliquez sur le prix du produit. La formule après le signe égal sera écrite automatiquement.

2. Pour la réponse finale, appuyez sur "Entrée". Ensuite, vous devez calculer le montant total des bénéfices reçus des autres types de produits. Eh bien, si le nombre de lignes n'est pas important, toutes les manipulations peuvent être effectuées manuellement. Pour remplir un grand nombre de lignes en même temps dans Excel, il existe une fonction utile qui permet de transférer la formule dans d'autres cellules.
3. Déplacez le curseur sur le coin inférieur droit du rectangle avec la formule ou le résultat final. L'apparition d'une croix noire signale que le curseur peut être déplacé vers le bas. Ainsi, un calcul automatique du profit reçu pour chaque produit séparément est effectué.
4. En relâchant le bouton de la souris enfoncé, nous obtenons les résultats corrects dans toutes les lignes.

En cliquant sur la cellule D3, vous pouvez voir que les coordonnées de la cellule ont été automatiquement modifiées et ressemblent maintenant à ceci : =B3*C3. Il s'ensuit que les liens étaient relatifs.

Erreurs possibles lors de l'utilisation de liens relatifs

Sans aucun doute, cette fonction Excel simplifie grandement les calculs, mais dans certains cas, des difficultés peuvent survenir. Considérons un exemple simple de calcul du coefficient de profit de chaque bien :

1. Créez un tableau et remplissez : A – nom du produit ; B – quantité vendue ; C-coût ; D est le montant reçu. Supposons qu'il n'y ait que 11 articles dans l'assortiment. Par conséquent, en tenant compte de la description des colonnes, 12 lignes sont remplies et le montant total du bénéfice est D
2. Cliquez sur la cellule E2 et entrez =D2 /D13.
3. Après avoir appuyé sur le bouton "Entrée", le coefficient de la part relative des ventes du premier article apparaît.
4. Étirez la colonne vers le bas et attendez le résultat. Cependant, le système donne l'erreur « #DIV/0 ! »

La raison de l'erreur est l'utilisation d'une référence relative pour les calculs. À la suite de la copie de la formule, les coordonnées changent. Autrement dit, pour E3, la formule ressemblera à ceci =D3 /D13. Parce que la cellule D13 n'est pas remplie et a théoriquement une valeur nulle, le programme donnera une erreur avec l'information que la division par zéro est impossible.

Important! Pour corriger l'erreur, il est nécessaire d'écrire la formule de manière à ce que les coordonnées D13 soient fixes. L'adressage relatif n'a pas une telle fonctionnalité. Pour ce faire, il existe un autre type de liens – absolu. 

Comment faire un lien absolu dans Excel

Grâce à l'utilisation du symbole $, il est devenu possible de fixer les coordonnées des cellules. Comment cela fonctionne, nous examinerons plus loin. Étant donné que le programme utilise l'adressage relatif par défaut, par conséquent, pour le rendre absolu, vous devrez effectuer un certain nombre d'actions. Analysons la solution à l'erreur "Comment trouver le coefficient de la vente de plusieurs biens", en effectuant le calcul par adressage absolu:

1. Cliquez sur E2 et entrez les coordonnées du lien =D2 /D13. Comme le lien est relatif, un symbole doit être défini pour fixer les données.
2. Fixer les coordonnées de la cellule D Pour effectuer cette action, faites précéder la lettre indiquant la colonne et le numéro de ligne en plaçant un signe « $ ».

Conseil d'Expert! Pour faciliter la tâche de saisie, il suffit d'activer la cellule d'édition de la formule et d'appuyer plusieurs fois sur la touche F4. Jusqu'à ce que vous obteniez des valeurs satisfaisantes. La formule correcte est la suivante : =D2/$D$13.

3. Appuyez sur le bouton "Entrée". À la suite des actions effectuées, le résultat correct devrait apparaître.
4. Faites glisser le marqueur vers la ligne du bas pour copier la formule.

En raison de l'utilisation de l'adressage absolu dans les calculs, les résultats finaux dans les lignes restantes seront corrects.

Comment mettre un lien mixte dans Excel

Pour les calculs de formule, non seulement des références relatives et absolues sont utilisées, mais également des références mixtes. Leur particularité est de fixer l'une des coordonnées.

• Par exemple, pour modifier la position d'une ligne, vous devez écrire le signe $ devant la désignation de la lettre.
• Au contraire, si le signe dollar est écrit après la désignation de la lettre, les indicateurs de la ligne resteront inchangés.

Il en résulte que pour résoudre le problème précédent de détermination du coefficient de vente de marchandises à l'aide d'un adressage mixte, il est nécessaire de fixer le numéro de ligne. Autrement dit, le signe $ est placé après la lettre de la colonne, car ses coordonnées ne changent pas même dans une référence relative. Prenons un exemple :

1. Pour des calculs précis, entrez =D1/$D$3 et appuyez sur "Entrée". Le programme donne la réponse exacte.
2. Pour déplacer la formule vers les cellules suivantes dans la colonne et obtenir des résultats précis, faites glisser la poignée vers la cellule inférieure.
3. En conséquence, le programme donnera les calculs corrects.

Attention! Si vous définissez le signe $ devant la lettre, Excel affichera une erreur "#DIV/0 !", ce qui signifie que cette opération ne peut pas être effectuée.

Adressage "SuperAbsolu"

À la fin, regardons un autre exemple de lien absolu - l'adressage "superabsolu". Quelles sont ses caractéristiques et ses différences. Prenez le nombre approximatif 30 et entrez-le dans la cellule B2. C'est ce nombre qui sera le principal, il faut effectuer une série d'actions avec lui, par exemple, pour l'élever à une puissance.

1. Pour l'exécution correcte de toutes les actions, entrez la formule suivante dans la colonne C : =$B$2^$D2. Dans la colonne D, nous inscrivons la valeur des degrés.

2. Après avoir appuyé sur le bouton "Entrée" et activé la formule, nous étirons le marqueur vers le bas de la colonne.
3. Nous obtenons les bons résultats.

En fin de compte, toutes les actions effectuées se réfèrent à une cellule fixe B2, donc :

• Copier une formule de la cellule C3 vers la cellule E3, F3 ou H3 ne modifiera pas le résultat. Il restera inchangé - 900.
• Si vous devez insérer une nouvelle colonne, les coordonnées de la cellule avec la formule changeront, mais le résultat restera également inchangé.

C'est la particularité du lien « SuperAbsolu » : si vous devez vous déplacer, le résultat ne changera pas. Cependant, il existe des situations où des données sont insérées à partir de sources tierces. Ainsi, les colonnes sont décalées sur le côté et les données sont définies à l'ancienne dans la colonne B2. Que se passe-t-il dans ce cas? Lorsqu'elle est mélangée, la formule change en fonction de l'action effectuée, c'est-à-dire qu'elle ne pointera plus vers B2, mais vers C2. Mais puisque l'insertion a été faite en B2, le résultat final sera faux.

Référence! Pour pouvoir insérer des macros à partir de sources tierces, vous devez activer les paramètres du développeur (ils sont désactivés par défaut). Pour cela, rendez-vous dans Options, ouvrez les paramètres du ruban et cochez la case dans la colonne de droite en face de « Développeur ». Après cela, l'accès à de nombreuses fonctions qui étaient auparavant cachées aux yeux de l'utilisateur moyen s'ouvrira.

Cela soulève la question : est-il possible de modifier la formule de la cellule C2 afin que le nombre d'origine soit collecté à partir de la cellule B, malgré l'insertion de nouvelles colonnes de données ? Pour vous assurer que les modifications apportées au tableau n'affectent pas la détermination du total lors de l'installation de données provenant de sources tierces, vous devez effectuer les étapes suivantes :

1. Au lieu des coordonnées de la cellule B2, saisissez les indicateurs suivants : =INDIRECT("B2"). En conséquence, après avoir déplacé la composition de formulation, cela ressemblera à ceci : =INDIRECT("B2")^$E2.
2. Grâce à cette fonction, le lien pointe toujours vers le carré de coordonnées B2, que des colonnes soient ajoutées ou supprimées dans le tableau.

Il faut comprendre qu'une cellule qui ne contient aucune donnée affiche toujours la valeur « 0 ».

Conclusion

Grâce à l'utilisation de trois variétés des liens décrits, de nombreuses opportunités apparaissent qui facilitent le travail avec les calculs dans Excel. Par conséquent, avant de commencer à travailler avec des formules, lisez d'abord les liens et les règles d'installation.