Chapitre5. Créez et recopiez des formules
Copier des cellules vous semble maintenant simple... Mais attention ! Il faudra être vigilant quand les cellules que vous allez recopier contiendront des formules.
Imaginons que vous soyez gérant d’un magasin, et que vous réalisiez un tableau de suivi des ventes pour un produit.
Dans ce tableau des ventes, vous avez indiqué les prénoms des vendeurs et la quantité d’articles vendus par chacun d’entre eux.
Comme vous avez saisi dans les cellules D4 à D9 le tarif de vente des articles, vous souhaitez calculer le chiffre d'affaires (CA, dans le tableau) de chaque vendeur, celui-ci étant égal à : Quantité x Tarif.
Vous allez donc créer la formule qui permettra d’obtenir ce résultat !
Mais c’est la première fois que je crée une formule de calcul ! 😱 Je ne sais pas comment faire !
Pas de panique ! Vous pouvez créer une formule simplement en suivant ces étapes :
1. Sélectionnez la cellule de destination, c'est-à-dire la cellule où vous voulez faire apparaître le résultat. Pour cet exemple, c’est la cellule E4.
2. Saisissez = . Pour rappel, une formule commence toujours par le signe =.
3. Cliquez sur la cellule C4.
Vous pouvez remarquer que la référence C4 apparaît dans la cellule de destination mais également dans la barre de formule.
4. Saisissez au clavier le symbole * (équivalent de la multiplication, sur Excel).
5. Sélectionnez la cellule D4.
6. Validez votre formule de calcul avec la touche Entrée de votre clavier.
Et voilà, vous venez de créer votre première formule ! 😃
Le résultat apparaît dans la cellule E4, et la formule =C4*D4 dans la barre de formule.
Vous souhaitez maintenant calculer les chiffres d’affaires des autres vendeurs, sans refaire toutes les manipulations. Vous allez donc recopier la cellule E4 jusqu’à la cellule E9.
Voyons ce que vous obtenez :
Vous pouvez remarquer qu'Excel a calculé les chiffres d’affaires pour chaque vendeur !
Voyons maintenant pourquoi cela a fonctionné !
Observez ce tableau qui n’affiche pas les résultats, mais les formules.
Au moment de la recopie incrémentée, la formule =C4*D4 s’est décalée d’une ligne vers le bas pour devenir =C5*D5… et ainsi de suite jusqu’à la dernière formule, =C9*D9.
Avec Excel, vous avez la possibilité de recopier les formules de calcul, ce qui représente un gain de temps appréciable et facilite la création de tableaux. Dans une formule recopiée, les références se décalent automatiquement. C’est ce qu’on appelle des “références relatives”.
Récapitulons en vidéo
Retrouvez les différentes étapes pour créer et recopier une formule simple dans la vidéo ci-dessous :
Pas de panique, cette manipulation est moins compliquée qu’il n’y paraît ! Reprenons l’exemple précédent. Étant donné que le suivi porte sur un produit, vous souhaitez améliorer votre tableau. Au lieu d’indiquer plusieurs fois le tarif dans la colonne D, vous décidez de calculer le chiffre d’affaires après avoir saisi le tarif dans la cellule D1.
Construisez maintenant la formule qui permettra de calculer le chiffre d’affaires :
- Sélectionnez la cellule de destination D4.
- Pour commencer la formule, saisissez = au clavier.
- Sélectionnez la cellule C4.
- Saisissez * au clavier.
- Sélectionnez la cellule D1.
- Validez votre formule par Entrée.
Après avoir sélectionné la cellule D4, vous pouvez vérifier le résultat ci-dessous et constater que la formule apparaît dans la barre de formule.
Vous allez maintenant recopier la cellule D4 jusqu’à la cellule D9, et voyons ce que vous obtenez.
Au lieu de calculer les chiffres d’affaires pour chaque vendeur, Excel a généré une série d’erreurs avec le terme #VALEUR! 😵
Ohhh non !! Pourquoi ça n’a pas marché ?
Observez ce tableau qui n’affiche pas les résultats, mais les formules.
Au moment de la recopie incrémentée, la formule =C4*D1 s’est décalée d’une ligne vers le bas pour devenir =C5*D2… et ainsi de suite jusqu’à la dernière formule =C9*D6. Comme les références sont relatives, elles se sont décalées au moment de la recopie incrémentée. C’est pour cette raison que le résultat en D5 est égal à 0.
En effet, la formule =C5*D2 s’adresse à la cellule D2 qui est vide. C’est également pour cette raison que le résultat en D6 est égal à #VALEUR! En effet, la formule =C6*D3 s’adresse à une cellule qui contient du texte. Or, votre objectif est d’utiliser la référence D1 pour l’ensemble des formules. Vous devez donc faire comprendre à Excel que cette référence relative ne doit pas se décaler au moment de la recopie.
Reprenez l’exemple précédent, vous allez à nouveau calculer le chiffre d’affaires des vendeurs.
- Effacez les résultats des cellules D4 à D9.
- Sélectionnez la cellule de destination D4.
- Pour commencer la formule, saisissez = au clavier.
- Sélectionnez la cellule C4.
- Saisissez * au clavier.
- Sélectionnez la cellule D1 qui est actuellement une référence relative.
- La cellule D1 étant sélectionnée, appuyez sur la touche F4 de votre clavier, vous devriez obtenir le résultat suivant : $D$1.
En fonction de la marque de votre PC, la touche F4 peut avoir différents usages : modification de l’affichage, réglage du contraste ou de la luminosité... Ainsi, si la touche F4 ne fonctionne pas, vous pouvez utiliser la touche fn (pour fonction). Elle se trouve généralement en bas à gauche de votre clavier. En maintenant la touche fn enfoncée puis F4, vous devriez obtenir le même résultat : soit $D$1 !
Vous pourriez également ajouter les dollars au clavier dans votre formule au lieu d’utiliser F4, ou fn et F4. Ceci dit, je ne vous le conseille pas, car cela pourrait être source d’erreur. En revanche, en utilisant F4, vous vous assurez qu’Excel positionnera correctement les dollars dans la référence.
8. Validez votre formule par Entrée.
Après avoir sélectionné la cellule D4, vous pouvez vérifier le résultat ci-dessous et vérifier la formule dans la barre de formule.
Vous allez maintenant recopier la cellule D4 jusqu’à la cellule D9, et voyons ce que vous obtenez.
Excel a bien calculé les chiffres d’affaires pour chaque vendeur !
Voyons maintenant pourquoi cela a fonctionné !
Observez ce tableau qui n’affiche pas les résultats, mais les formules.
Au moment de la recopie incrémentée, la cellule C4 s’est décalée d’une ligne vers le bas pour devenir C5… et ainsi de suite jusqu’à C9. Par contre, toutes les formules contiennent maintenant une cellule constante qui est reconnaissable grâce aux $.
Dans une formule recopiée, les références se décalent automatiquement, sauf les références constantes. On parle communément de “références absolues”. Si cette notion ne vous paraît pas encore très claire, pas de panique ! Le principe est de comprendre que souvent, si la recopie d’une formule de calcul ne donne pas les résultats souhaités, c'est en raison d'un problème de référence de cellule qui aurait dû être absolue.
Ayez donc le réflexe de vous demander si vous ne devriez pas modifier les références pour les mettre en absolues, et souvenez-vous que pour transformer une référence relative, comme par exemple D1, en référence absolue, il suffit d’appuyer sur F4 ou fn et F4 lorsque la cellule est sélectionnée pour faire apparaître des dollars et obtenir $D$1.
Retrouvez les différentes étapes pour créer et recopier des formules complexes dans la vidéo ci-dessous :
- Vous pouvez créer une formule de calcul simple pour ajouter, soustraire, multiplier ou diviser des valeurs dans vos tableaux.
- Une formule de calcul est facilement identifiable : elle commence toujours par le signe =.
- Dans une formule recopiée, les références relatives se décalent automatiquement.
- Dans une formule recopiée, les références absolues ne se décalent pas.
- Pour transformer une référence relative en référence absolue, vous pouvez utiliser la touche F4 du clavier, ou la combinaison fn et F4
Les commentaires ne sont pas activés sur ce cours.