Pourquoi vous devriez éviter les valeurs de codage dur dans les formules Microsoft Excel

Lors de la création de formules Microsoft Excel, vous pouvez générer des calculs en utilisant des valeurs spécifiques – également connues sous le nom de codage dur – ou référençant d'autres cellules dans le classeur. Dans ce guide, je vais expliquer pourquoi les valeurs de codage dur peuvent causer des problèmes et comment l'utilisation des références cellulaires est souvent la meilleure option.

Problèmes causés par des valeurs de codage dur dans les formules Excel

Disons que vous élaborez le coût des produits dans votre magasin lorsqu'une taxe de 20% est ajoutée. Pour ce faire, dans la colonne C, vous avez multiplié les valeurs de la colonne B de 1,2, comme on le voit dans la barre de formule en haut de la fenêtre Excel:

=B2*1.2

Cependant, imaginez maintenant que l'année suivante, la taxe sur tous les articles est réduite à 15%. En conséquence, vous devrez retourner dans les cellules et modifier toutes les formules de la colonne C afin que les valeurs de la colonne B soient multipliées par 1,15.

Oui, vous pouvez modifier la formule dans la cellule C2 et le dupliquer automatiquement dans les cellules restantes en double-cliquant ou en cliquant et en faisant glisser la poignée de remplissage dans le coin inférieur droit de la cellule. Cependant, cette approche prend plus de temps que nécessaire et pourrait facilement entraîner des erreurs de copie ou de mise à jour.

De plus, les valeurs de codage dur dans les formules Excel rend les erreurs de repérage plus difficiles, car vous devez sélectionner une cellule pour voir la composition d'un calcul dans la barre de formule. Alternativement, vous pouvez appuyer sur Ctrl + `(accent grave) pour afficher toutes les formules dans la feuille de calcul, mais – encore une fois, c'est une étape inutile à franchir et pourrait ajouter de la confusion.

Enfin, si vous partagez votre classeur Excel avec les autres, les valeurs codées durs dans les formules manquent de contexte qui explique comment vous êtes arrivé à ce chiffre.

Pourquoi référencer les cellules variables est une meilleure option

Pour surmonter les inconvénients des valeurs de codage dur dans les formules Microsoft Excel, vous pouvez utiliser des références cellulaires.

En utilisant l'exemple ci-dessus, plutôt que de multiplier chaque valeur dans la colonne B de 1,2 dans votre formule, vous pouvez référencer une cellule qui contient la variable – dans ce cas, la cellule E2 – au lieu:

=B2*$E$2

Il est important de noter que la référence à la cellule E2 est absolue, représentée par les symboles dollar ($) avant la colonne et la ligne. Si vous ne faites pas cela, lorsque vous dupliquez la formule aux cellules restantes de la colonne C, la référence de la cellule ajustera par rapport à la position de la cellule active. Par exemple, la formule de la cellule C3 référencerait la cellule E3, la formule dans la cellule C4 référencerait la cellule E4, etc.

Lors de la fabrication de votre formule, appuyez sur F4 après avoir tapé la référence cellulaire pour la transformer d'une référence relative en une référence absolue.

Maintenant, lorsque la taxe sur les éléments passe à 15%, vous pouvez simplement modifier la valeur de la cellule E2 à 1,15, et toutes les formules qui font référence que cette cellule mettra à jour instantanément.

De plus, puisque la cellule contenant la variable est toujours exposée, il est facile de revoir pour la précision. De plus, parce que la cellule E1 contient le mot «taxe», toute personne accès à la feuille de calcul pour la première fois peut voir le contexte complet de la façon dont les valeurs totales de la colonne C sont calculées.

Avertissement: il y a des cas où des valeurs de codage dur dans les formules peuvent être plus appropriées. Par exemple, vous pouvez coder dur un total hebdomadaire à diviser par sept pour générer un total quotidien, car le nombre de jours d'une semaine ne change jamais. De plus, les formules avec des références de cellule utilisent un peu plus de mémoire que celles avec des valeurs codées dures, donc si vous le faites souvent, vous pouvez voir une baisse – quoique négligeable – dans les performances de votre classeur.

L'utilisation de références de cellules dans des formules au lieu de valeurs codées durs est particulièrement utile si les paramètres de calcul sont susceptibles de changer à l'avenir. Dans cet exemple, la fonction IFS est utilisée dans la colonne C pour déterminer si chaque élève a réussi ou a échoué à l'examen de fin de semestre, selon qu'il a atteint la variable de marque de passe dans la cellule E2:

=IFS((@Score)<$E$2,"FAIL",(@Score)>=$E$2,"PASS")

Pour le semestre suivant, vous pouvez simplement dupliquer la feuille et modifier la variable de marque de passe, le tout sans avoir à vous soucier d'ajuster la formule pour générer le résultat correct pour ces données.

Conseil de pro: nommez la variable

Jusqu'à présent, j'ai parlé de référence à des cellules dans des formules Excel au lieu de valeurs du codage dur. Une façon de sécuriser encore vos formules est de donner à la cellule qui contient une variable un nom, ce qui est particulièrement utile si vous prévoyez de l'utiliser dans votre classeur.

Supposons que vous ayez plusieurs feuilles de travail dans un classeur, chacune contenant divers produits de différents magasins, et vous devez afficher le coût total de chaque article, y compris la taxe.

Vous remarquerez dans la capture d'écran ci-dessus qu'il y a aussi une feuille de calcul appelée « Tax ». Cette feuille contient le taux d'imposition actuel et la valeur dont les coûts doivent être multipliés pour calculer le coût global.

Plutôt que d'avoir à se souvenir de l'emplacement de la variable dans votre classeur chaque fois que vous souhaitez calculer le coût global d'un produit, vous pouvez donner un nom à cette cellule. Pour ce faire, sélectionnez la cellule pertinente et dans la zone de nom dans le coin supérieur gauche de votre écran, saisissez une étiquette de cellule mémorable. Dans mon cas, je suis parti avec « taxe ». Ensuite, appuyez sur Entrée pour terminer le processus de dénomination.

Maintenant, peu importe où vous êtes dans votre classeur, vous pouvez facilement référencer cette variable dans votre formule:

=B2*TAX

Chaque fois que le taux d'imposition change, vous n'avez à l'ajuster qu'une seule fois dans votre feuille de travail fiscale pour mettre à jour tous vos coûts. De plus, puisque les références aux gammes nommées sont de nature absolue, vous n'avez pas à vous rappeler d'ajouter des signes en dollars à vos formules!

---

La référence des cellules au lieu des valeurs de codage dur dans les formules n'est pas le seul moyen de gagner du temps dans Microsoft Excel. Par exemple, vous pouvez ajouter vos commandes les plus utilisées à la barre d'outils à accès rapide, ou apprendre certains des raccourcis les plus utiles d'Excel pour vous empêcher de sauter entre votre clavier et votre souris.