Comment figer des cellules pour ne plus faire d’erreurs dans vos formules

Imaginez

Vous avez créé un tableau pour calculer le pourcentage des produits vendus par votre entreprise.

Vous savez que pour calculer un pourcentage vous devez diviser la quantité vendue par le total des produits.

Pour cela, vous avez déjà calculé le pourcentage du premier produit.

Mais lorsque vous recopiez la formule dans les lignes suivantes ou lorsque vous copiez et collez la formule, vous obtenez des messages d’erreurs de types : #DIV/0!.

#DIV/0!, signifie que vous ne pouvez pas diviser un nombre par zéro.

Le problème, c’est que quand vous recopiez la formule vers le bas, Excel déplace les cellules d’autant de lignes que vous descendez.

Comment allez-vous procéder ?

C’est là qu’interviennent les « RÉFÉRENCES DE CELLULES ».

Qu’est-ce que les « références de cellules » ?

Par essence, toutes les cellules de votre feuille de calcul sont libres. Les formules que vous y appliquez peuvent être déplacées dans toutes les directions (horizontal et/ou vertical).

Or, dans votre calcul de pourcentages, toutes les formules se basent sur le total des quantités vendues. Votre cellule total des quantités vendues doit donc rester fixe, quel que soit le déplacement que vous opérez.

Selon le calcul à effectuer, vous utiliserez des références relatives, absolues ou mixtes, qui font référence à un emplacement mobile ou fixe.

Les références relatives

Les références relatives sont les références de base d’une cellule (A1, B2, C7, etc.). Lorsque vous copiez/coller ou déplacez des « références relatives », les références contenues dans la formule sont modifiées pour correspondre à celles des cellules occupant la même position relative par rapport à la formule.

Les colonnes et les lignes sont totalement mobiles.

Les références absolues

Si une référence doit rester identique dans la copie d’une formule, utilisez une « référence absolue » en insérant le symbole $ devant chaque partie de la référence : la lettre pour la/les colonne(s) et le chiffre pour la/les ligne(s) ($C$7).

Pour insérer le symbole $, sélectionnez la cellule visée et appuyez sur la touche F4 du clavier

Si vous copiez la formule dans un autre emplacement, celle-ci fera toujours référence à la cellule d’origine.

Les références absolues s’appliquent lorsque dans un calcul vous devez faire référence à une même cellule ou lorsque vous placez à l’extérieur de votre tableau, des valeurs constantes comme des : taux, tarifs, coefficients, durées, etc.

Les références mixtes

Les « références mixtes » sont formées d’une ligne absolue et d’une colonne relative ou d’une colonne absolue et d’une ligne relative. C’est-à-dire que le $ est placé soit devant l’indication de colonne ($C7), soit devant l’indication de ligne (C$7).

En fonction de l’emplacement du $, seule la colonne ou seule la ligne reste fixe.

Utiliser une « référence absolue » dans une formule

Sélectionnez la cellule ou la plage de données dans laquelle vous voulez obtenir le résultat

Tapez la formule, commencez par = (égal) ou sélectionnez une formule automatique

Cliquez F2 ou double-cliquez sur la cellule pour afficher les références de la formule

Placez-vous au niveau de la référence de cellule que vous souhaitez figer

Cliquez F4 pour mettre la référence de cellule en absolu ($ devant la lettre de colonne et $ devant le numéro de ligne)

Cliquez Entrée pour valider la formule

Les deux $ indiquent que la formule ne peut pas être déplacée. Elle est totalement figée.

Utiliser une « référence mixte » dans une formule

Sélectionnez la cellule ou la plage de données dans laquelle vous voulez obtenir le résultat

Tapez la formule, commencez par = (égal) ou sélectionnez une formule automatique

Cliquez F2 ou double-cliquez sur la cellule pour afficher les références de la formule

Placez-vous au niveau de la référence de cellule que vous souhaitez figer

Cliquez deux ou trois fois sur F4 pour mettre la référence de cellule en mixte ($ devant la lettre de colonne ou $ devant le numéro de ligne)

Cliquez Entrée pour valider la formule

Le $ placé devant l'indice de colonne interdit tout déplacement horizontal.

Le $ placé devant l'indice de ligne interdit tout déplacement vertical.