Comment figer une cellule dans une formule Excel

Souvent, les utilisateurs sont confrontés à la nécessité d'épingler une cellule dans une formule. Par exemple, cela se produit dans les situations où vous souhaitez copier une formule, mais de sorte que le lien ne se déplace pas vers le haut et vers le bas du même nombre de cellules qu'il a été copié à partir de son emplacement d'origine.

Dans ce cas, vous pouvez corriger la référence de cellule dans Excel. Et cela peut se faire de plusieurs manières à la fois. Voyons de plus près comment atteindre cet objectif.

Qu'est-ce qu'un lien Excel

La feuille est composée de cellules. Chacun d'eux contient des informations spécifiques. D'autres cellules peuvent l'utiliser dans les calculs. Mais comment savent-ils d'où obtenir les données ? Cela les aide à créer des liens.

Chaque lien désigne une cellule avec une lettre et un chiffre. Une lettre représente une colonne et un nombre représente une ligne. 

Il existe trois types de liens : absolu, relatif et mixte. Le second est défini par défaut. Une référence absolue est une référence qui a une adresse fixe à la fois pour une colonne et une colonne. Par conséquent, mixte est celui où une colonne ou une ligne séparée est fixée.

La 1 méthode

Pour enregistrer les adresses de colonne et de ligne, procédez comme suit :

1. Cliquez sur la cellule contenant la formule.
2. Cliquez sur la barre de formule de la cellule dont nous avons besoin.
3. Appuyez sur F4.

En conséquence, la référence de cellule deviendra absolue. Il peut être reconnu par le signe dollar caractéristique. Par exemple, si vous cliquez sur la cellule B2 puis sur F4, le lien ressemblera à ceci : $B$2.

Que signifie le signe dollar devant la partie de l'adresse par cellule ?

1. S'il est placé devant une lettre, il indique que la référence de la colonne reste la même, quel que soit l'endroit où la formule a été déplacée.
2. Si le signe dollar est devant le nombre, cela indique que la chaîne est épinglée. 

La 2 méthode

Cette méthode est presque la même que la précédente, seulement vous devez appuyer deux fois sur F4. par exemple, si nous avions la cellule B2, elle deviendra ensuite B$2. En termes simples, nous avons ainsi réussi à fixer la ligne. Dans ce cas, la lettre de la colonne changera.

C'est très pratique, par exemple, dans les tableaux où vous devez afficher le contenu de la deuxième cellule à partir du haut dans la cellule du bas. Au lieu de faire une telle formule plusieurs fois, il suffit de fixer la ligne et de laisser la colonne changer.

La 3 méthode

C'est complètement la même chose que la méthode précédente, seulement vous devez appuyer trois fois sur la touche F4. Alors seule la référence à la colonne sera absolue, et la ligne restera fixe.

La 4 méthode

Supposons que nous ayons une référence absolue à une cellule, mais ici il fallait la rendre relative. Pour ce faire, appuyez sur la touche F4 tant de fois qu'il n'y a pas de signe $ dans le lien. Ensuite, il deviendra relatif et lorsque vous déplacerez ou copierez la formule, l'adresse de la colonne et l'adresse de la ligne changeront.

Épingler des cellules pour une large plage

On voit que les procédés ci-dessus ne présentent aucune difficulté à mettre en oeuvre. Mais les tâches sont spécifiques. Et, par exemple, que faire si nous avons plusieurs dizaines de formules à la fois, dont les liens doivent être transformés en liens absolus. 

Malheureusement, les méthodes Excel standard n'atteindront pas cet objectif. Pour ce faire, vous devez utiliser un addon spécial appelé VBA-Excel. Il contient de nombreuses fonctionnalités supplémentaires qui vous permettent d'effectuer des tâches courantes avec Excel beaucoup plus rapidement.

Il comprend plus d'une centaine de fonctions définies par l'utilisateur et 25 macros différentes, et il est également mis à jour régulièrement. Il vous permet d'améliorer le travail avec presque tous les aspects:

1. Cellules.
2. Macro.
3. Fonctions de différents types.
4. Liens et tableaux.

En particulier, ce complément vous permet de corriger les liens dans un grand nombre de formules à la fois. Pour ce faire, vous devez effectuer les étapes suivantes :

1. Sélectionnez une plage.
2. Ouvrez l'onglet VBA-Excel qui apparaîtra après l'installation. 
3. Ouvrez le menu "Fonctions", où se trouve l'option "Verrouiller les formules".
   

   

4. Ensuite, une boîte de dialogue apparaîtra dans laquelle vous devrez spécifier le paramètre requis. Cet addon vous permet d'épingler une colonne et une colonne séparément, ensemble, et également de supprimer un épinglage déjà existant avec un package. Une fois le paramètre requis sélectionné à l'aide du bouton radio correspondant, vous devez confirmer vos actions en cliquant sur "OK".

Exemple

Prenons un exemple pour que ce soit plus clair. Supposons que nous disposions d'informations décrivant le coût des marchandises, leur quantité totale et le chiffre d'affaires. Et nous sommes confrontés à la tâche de faire en sorte que le tableau, basé sur la quantité et le coût, détermine automatiquement combien d'argent nous avons réussi à gagner sans déduire les pertes.

Dans notre exemple, pour cela, vous devez entrer la formule =B2*C2. C'est assez simple, comme vous pouvez le voir. Il est très facile d'utiliser son exemple pour décrire comment vous pouvez fixer l'adresse d'une cellule ou sa colonne ou ligne individuelle. 

Bien sûr, dans cet exemple, vous pouvez essayer de faire glisser la formule vers le bas à l'aide du marqueur de remplissage automatique, mais dans ce cas, les cellules seront automatiquement modifiées. Ainsi, dans la cellule D3, il y aura une autre formule, où les nombres seront remplacés, respectivement, par 3. De plus, selon le schéma - D4 - la formule prendra la forme = B4 * C4, D5 - de la même manière, mais avec le numéro 5 et ainsi de suite.

Si c'est nécessaire (dans la plupart des cas, il s'avère), alors il n'y a pas de problèmes. Mais si vous devez corriger la formule dans une cellule afin qu'elle ne change pas lors du glissement, cela sera un peu plus difficile. 

Supposons que nous ayons besoin de déterminer le revenu en dollars. Mettons-le dans la cellule B7. Soyons un peu nostalgiques et indiquons le coût de 35 roubles par dollar. En conséquence, pour déterminer les recettes en dollars, il est nécessaire de diviser le montant en roubles par le taux de change du dollar.

Voici à quoi cela ressemble dans notre exemple.

Si, comme dans la version précédente, nous essayons de prescrire une formule, nous échouerons. De même, la formule changera pour celle qui convient. Dans notre exemple, ce sera comme ça : =E3*B8. D'ici on peut voir. que la première partie de la formule est devenue E3, et nous nous sommes fixé cette tâche, mais nous n'avons pas besoin de changer la deuxième partie de la formule en B8. Par conséquent, nous devons transformer la référence en référence absolue. Vous pouvez le faire sans appuyer sur la touche F4, juste en mettant un signe dollar.

Après avoir transformé la référence à la deuxième cellule en une référence absolue, elle est devenue protégée contre les modifications. Vous pouvez maintenant le faire glisser en toute sécurité à l'aide de la poignée de remplissage automatique. Toutes les données fixes resteront les mêmes, quelle que soit la position de la formule, et les données non validées changeront de manière flexible. Dans toutes les cellules, les revenus en roubles décrits dans cette ligne seront divisés par le même taux de change du dollar.

La formule elle-même ressemblera à ceci :

=D2/$B$7

Attention! Nous avons indiqué deux signes dollar. De cette façon, nous montrons au programme que la colonne et la ligne doivent être corrigées.

Références de cellule dans les macros

Une macro est une sous-routine qui vous permet d'automatiser des actions. Contrairement à la fonctionnalité standard d'Excel, une macro vous permet de définir immédiatement une cellule spécifique et d'effectuer certaines actions en quelques lignes de code seulement. Utile pour le traitement par lots des informations, par exemple, s'il n'y a aucun moyen d'installer des modules complémentaires (par exemple, un ordinateur d'entreprise est utilisé, pas un ordinateur personnel).

Vous devez d'abord comprendre que le concept clé d'une macro est des objets qui peuvent contenir d'autres objets. L'objet Workbooks est responsable du livre électronique (c'est-à-dire du document). Il inclut l'objet Sheets, qui est une collection de toutes les feuilles d'un document ouvert. 

Par conséquent, les cellules sont un objet Cells. Il contient toutes les cellules d'une feuille particulière.

Chaque objet est qualifié avec des arguments entre parenthèses. Dans le cas des cellules, elles sont référencées dans cet ordre. Le numéro de ligne apparaît en premier, suivi du numéro ou de la lettre de la colonne (les deux formats sont acceptables).

Par exemple, une ligne de code contenant une référence à la cellule C5 ressemblera à ceci :

Classeurs ("Book2.xlsm").Feuilles ("Liste2").Cellules(5, 3)

Classeurs("Book2.xlsm").Feuilles("Liste2").Cellules(5, "C")

Vous pouvez également accéder à une cellule à l'aide d'un objet Bien rangé. En général, il est destiné à donner une référence à une plage (dont les éléments, soit dit en passant, peuvent aussi être absolus ou relatifs), mais vous pouvez simplement donner un nom de cellule, dans le même format que dans un document Excel.

Dans ce cas, la ligne ressemblera à ceci.

Classeurs ("Book2.xlsm").Feuilles ("Liste2").Plage ("C5")

Il peut sembler que cette option est plus pratique, mais l'avantage des deux premières options est que vous pouvez utiliser des variables entre parenthèses et donner un lien qui n'est plus absolu, mais quelque chose comme un relatif, qui dépendra des résultats de les calculs.

Ainsi, les macros peuvent être utilisées efficacement dans les programmes. En fait, toutes les références aux cellules ou aux plages ici seront absolues et peuvent donc également être corrigées avec elles. Certes, ce n'est pas si pratique. L'utilisation de macros peut être utile lors de l'écriture de programmes complexes avec un grand nombre d'étapes dans l'algorithme. En général, la manière standard d'utiliser des références absolues ou relatives est beaucoup plus pratique. 

Conclusions

Nous avons compris ce qu'est une référence de cellule, comment ça marche, à quoi ça sert. Nous avons compris la différence entre les références absolues et relatives et compris ce qui doit être fait pour transformer un type en un autre (en termes simples, corriger l'adresse ou la détacher). Nous avons compris comment vous pouvez le faire immédiatement avec un grand nombre de valeurs. Vous avez maintenant la possibilité d'utiliser cette fonctionnalité dans les bonnes situations.