mosprepaPréparez votre certification
Microsoft Office Specialist

MS Excel 2013/2016

Références et priorité des opérateurs

La notion de référence dans Excel est tellement importante qu'il faut bien la maitriser pour ne pas obtenir des résultats érronés. Un autre point qui génère des erreurs de calcul est l'ordre dans lequel Excel effectue les opérations.

Dans le but de cerner ces deux notions, on va traiter les points suivants :

  1. Définition de l'ordre de priorité des opérateurs
  2. Référencement des plages de cellules dans des formules
  3. Utilisation de références (relative, mixte, absolue)

Pour mettre en pratique les compétences traitées dans ce cours, je vous invite à télécharger le fichier de travail ici. Le classeur contient une seule feuille de calcul : Facture, dans laquelle on va essayer d'effectuer les différents calculs.

Références et ordre des opérations

Définition de l'ordre de priorité des opérateurs

Dans une cellule Excel on peut saisir du texte, des nombres, des dates et des formules de calcul.

À retenir :

Dans Excel, une formule commence toujours par le symbole égal (=). Attention, AUCUN caractère avant le symbole égal (=), même pas un espace, sinon la formule est considérée comme du texte. Après validation, la formule ci-dessous donne, tout naturellement, 7 comme résultat.

Références et ordre des opérations

Types d'opérateurs

Les opérateurs indiquent le type d'opération que l'on désir effectuer sur les termes de la formule. Ils sont groupés en plusieurs catégories.

Les opérateurs arithmétiques

Ces opérateurs servent à effectuer des opérations arithmétiques telles que l'addition, la soustraction, la multiplication, la division, la puissance et le pourcentage.

Symbole Signification Exemple Résultat
+ Addition. Effectuer la somme de deux termes =6+9 15
- Soustraction. Soustrait le terme à droite de celui de gauche, si ce dernier est absent, il est remplacé par 0 (zéro) =15-6
=-2 (=0-2)
9
-2
* Multiplier deux termes =15*6 90
/ Diviser le terme de gauche par celui de droite, ce dernier ne doit pas être nul =30/5 6
^ Puissance. Élever un nombre à une puissance =2^9 512
% Divise un nombre par 100 (calcul un pourcentage) =5% 0.05
Les opérateurs de comparaison

Les opérateurs de comparaison, comme leur nom l'indique, permettent de comparer deux termes et de retourner un résultat qui est soit True (Vrai) soit False (Faux).

Symbole Signification Exemple Résultat
= Vérifie l'égalité de deux termes =6=9 False (Faux)
< Vérifie si le terme de gauche est inférieur strictement à celui de droite =6<9 True (Vrai)
> Vérifie si le terme de gauche est supérieur strictement à celui de droite =6>9 False (Faux)
<= Vérifie si le terme de gauche est inférieur ou égal à celui de droite =6<=9 True (Vrai)
>= Vérifie si le terme de gauche est supérieur ou égal à celui de droite =6>=9 False (Faux)
< > Vérifie si le terme de gauche est différent de celui de droite =6< >9 True (Vrai)
L'opérateur de concaténation de texte

Cet opérateur met une chaine de caractères à la suite d'une autre, on dit qu'il fait une Concaténation.

Symbole Signification Exemple Résultat
& Met le texte de gauche à la suite de celui de droite. Penser à mettre les textes entre guillemets et à ajouter un espace vide pour séparer les deux textes. ="Ms"&"Excel"
="Ms"&" "&"Excel"
MsExcel
Ms Excel

Ordre de priorité par défaut des opérateurs

Une formule peut contenir plusieurs opérateurs, exemple =10+15/5-6+12-4*2^3.

Faites sur papier ce calcul ; si vous trouvez -13, comme Excel, c'est que vous avez bien respecté la priorité des opérateurs, sinon c'est que vous avez une erreur quelque part. En fait, Excel, comme tous les calculateurs, respecte l'ordre de priorité des opérateurs et effectue cette opération comme le montrent les étapes suivantes :

  1. Dans un premier temps, il évalue l'expression 2 à la puissance 3. 2^3=8
  2. Dans la deuxième étape, Excel effectue la division de 15 par 5, 15/5=3
  3. puis il multiplie 4 par 8, ce qui donne 4*8=32
  4. Au final, il ne reste plus que des opérations d'addition et de soustraction qu'il exécute dans l'ordre de gauche à droite. 10+3-6+12-32=-13

On remarque que cette formule est composée de trois types d'opérateurs qui sont exécutés dans cet ordre :

  1. La puissance
  2. La multiplication et la division
  3. L'addition et la soustraction

La puissance a donc une priorité forte, la multiplication et la division ont une priorité moyenne et l'addition avec la soustraction une priorité faible.

Le tableau suivant montre les priorités des différents opérateurs.

Ordre Description Opérateurs
1 Négation (Exemple -30) -
2 Pourcentage %
3 Puissance ^
4 Multiplication et Division * et /
5 Addition et Soustraction + et -
6 Concaténation &
7 Comparaison =,<,>,<=,>=,< >

Modifier l'ordre de priorité des opérateurs

Ayant une priorité plus forte que celle des autres opérateurs, les parenthèses sont utilisées pour modifier l'ordre par défaut. Ainsi, en utilisant les parenthèses à différents endroits dans l'expression de tout à l'heure =10+15/5-6+12-4*2^3, on change l'ordre d'exécution des opérateurs et par conséquent on obtient un résultat de calcul différent à chaque fois, comme le montre la liste suivante :

  • =10+15/5-6+12-4*2^3 donne -13 (sans parenthèses)
  • =(10+15)/5-6+12-4*2^3 donne -21
  • =10+15/(5-6)+12-4*2^3 donne -25
  • =10+15/5-6+(12-4)*2^3 donne 71
  • =(10+15/5-6+12-4*2)^3 donne 1331
  • =10+15/5-6+12-(4*2)^3 donne -493

Avec les parenthèses on donne la priorité à un opérateur ou un groupe d'opérateurs, quel que soit son degré de priorité.

Travail à faire :

Sur papier, évaluer les expressions suivantes :

  1. =2*3+9/3+12/4-4+3*5 =23
  2. =12*(24-6+8)*3^2/4+10-3+4/2=711
  3. =15/3+45-46*3+12/2+6-12+64=-24
  4. =65*2-54+65*(25-24)+12-74=79
  5. =15/3+45-46*3+12/2+6-12+64>65*2-54+65*(25-24)+12-74=Faux

Référencement des plages de cellules dans des formules

Appliquons ce que nous venons de voir sur la feuille Facture pour calculer le Total et le Total TTC sachant que :

  • Total=Quantité * Prix unitaire HT
  • et
  • Total TTC = Total + Total * TVA

Pour le premier article qui se trouve dans la ligne 6, Quantité = 2 et le Prix unitaire HT = 2800, le Taux de TVA étant 20%. On a donc toutes les données nécessaires et on peut tenter un premier essai en saisissant dans la cellule E6 l'expression =2 * 2800 pour calculer le total et dans la cellule F6 saisir l'expression =5600 + 5600 * 20% comme le montre la capture ci-dessous.

Références et ordre des opérations

En affichant les résultats, apparemment tout va bien puisque nous obtenons 5600 pour le Total et 6720 pour le Total TTC, des résultats justes.

Références et ordre des opérations

Mais un problème surgit dès que nous apportons un changement au niveau de la Quantité (la cellule B6) ou bien au niveau du Prix unitaire HT (la cellule D6) ou encore au niveau du Taux de TVA (la cellule F2) puisque les résultats ne suivent pas le changement alors qu'ils devaient le faire.

Références et ordre des opérations

On remarque ici que suite à la modification de la Quantité qui est devenue 5 au lieu de 2, l'expression de calcul n'a pas suivi le changement, elle est restée figée à = 2 * 2800 au moment où elle devait être = 5 * 2800 du coup le résultat est erroné 5600 au lieu de 14000.

Pour remédier à ce problème, Excel permet d'utiliser le référencement c'est à dire les adresses des cellules dans les expressions et les formules de calcul au lieu d'utiliser des données en dur qui ne changent pas. Rappelez-vous, chaque cellule est référencée par une adresse formée de la lettre de sa colonne suivie du numéro de sa ligne, exemple B6. Pour le premier article, la Quantité se trouve dans la cellule B6 et le Prix unitaire HT se trouve dans la cellule D6. Dans la cellule E6, au lieu d'écrire = 2 * 2800, on va saisir = B6 * D6. Cette nouvelle écriture veut dire : le contenu de la cellule E6 est égal au contenu de la cellule B6 multiplié par le contenu de la cellule D6.

Références et ordre des opérations

En utilisant les références des cellules dans les formules de calcul, les expressions suivent les changements des opérandes et les résultats s'adaptent aux nouvelles données.

Travail à faire

Sur la feuille Facture du fichier téléchargé, saisir les expressions convenables dans les cellules E6 et F6 en utilisant les références des cellules en sachant que :

Total TTC = Total + Total * TVA

Utilisation de références (relative, mixte, absolue)

Un autre avantage de l'utilisation des références des cellules dans les formules est de pouvoir recopier la formule vers les cellules voisines en cliquant-glissant, horizontalement ou verticalement, le petit carré situé en bas à droite de la cellule, vers les autres cellules.

Références et ordre des opérations

Référence relative

En recopiant la formule d'une cellule à une autre, celle-ci change de référence : = B6 * D6 pour la cellule E6, = B7 * D7 pour la cellule E7, etc. En fait, Excel comprend l'expression = B6 * D6 qui se trouve dans la cellule E6 de la manière suivante :

Multiplier le contenu de la troisième cellule à gauche par le contenu de la première cellule à gauche.

Pour la cellule E6 cette instruction fait référence aux cellules B6 et D6. Pour la cellule E7 elle fait référence aux cellules B7 et D7. Pour E8 ça fait référence aux cellules B8 et D8 et ainsi de suite. On dit qu'on a une Référence relative parce qu'elle change selon le contexte.

Références et ordre des opérations

Une référence relative est une référence qui varie en fonction de l'emplacement, les colonnes et les lignes peuvent changer toutes les deux.

Référence mixte

Occupons-nous maintenant du Total TTC.

Total TTC = Total + Total * TVA

Pour le premier article qui se trouve dans la ligne 6, le Total est dans la cellule E6 et le Taux de TVA dans la cellule F2. On va donc écrire dans la cellule F6 l'expression suivante : = E6 + E6 * F2 ce qui donne pour résultat 16800.

Références et ordre des opérations

Pour recopier la formule de calcul vers les cellules d'en bas, on va procéder de la même façon que tout à l'heure, c'est à dire cliquer sur le petit carré situé en bas à droite de la cellule F6 puis de le faire glisser vers les autres cellules.

Références et ordre des opérations

Et là, surprise, plein d'erreurs ; des résultats erronés, des messages d'erreur. Bref, rien ne marche !

Essayons de comprendre ce qui s'est passé.

D'après l'écriture = E6 + E6 * F2 de la cellule F6 l'instruction est : le contenu de E6 multiplié par le contenu de F2 auquel on rajoute le contenu de E6 ce qui donne 14000 + 14000 * 0.2 = 16800 (Résultat correct).

Pour la cellule F7, = E7 + E7 * F3 l'instruction est : le contenu de E7 multiplié par le contenu de F3 auquel on rajoute le contenu de E7, or la cellule F3 est vide, ce qui donne 21000 + 21000 * 0 = 21000 (Résultat erroné).

La même remarque pour la cellule F8, = E8 + E8 * F4 l'instruction est : le contenu de E8 multiplié par le contenu de F4 auquel on rajoute le contenu de E8, or la cellule F4 est vide, ce qui donne 2700 + 2700 * 0 = 2700 (Résultat erroné).

L'expression de la cellule F9 (= E9 + E9 * F5) ne donne pas un résultat erroné mais elle génère l'erreur #VALEUR! provoquée par le contenu de la cellule F5 qui est un texte : 1795 + 1795 * "Total TTC" (on ne peut pas multiplier un nombre par un texte).

Pour la cellule F10 (= E10 + E10 * F6), l'instruction est : le contenu de E10 multiplié par le contenu de F6 auquel on rajoute le contenu de E10, or la cellule F6 contient la valeur 16800, ce qui donne 5700 + 5700 * 16800 = 95765700 (Résultat erroné).

Références et ordre des opérations

On s'aperçoit que le problème est causé par la référence à la TVA qui change à chaque fois (F2, F3, F4, F5, F6), alors qu'elle doit pointer toujours sur la même cellule F2. On remarque aussi que la colonne F ne pose pas de problème car elle est toujours la même, ce qui n'est pas le cas pour la ligne ; c'est donc celle-ci qu'on doit fixer.

Pour fixer une ligne ou bien une colonne, il suffit de la précéder du symbole dollar $ dans la formule. On donc, avant de faire la recopie, écrire la formule dans la cellule F6 en insérant un symbole dollar $ avant le 2 qui indique la ligne à fixer.

Références et ordre des opérations

On n'a pas fixé la cellule E6 pour le Total parce que il faut bien que ça change ; on n'a pas fixé la colonne F aussi parce qu'on va recopier sur la même colonne (ce n'est pas la peine). En faisant cette manipulation et en recopiant la formule vers le bas, tout rentre dans l'ordre.

Références et ordre des opérations

Pour la cellule E6 on n'a fixé ni la colonne ni la ligne, c'est une référence relative, alors que pour la cellule F2 on a fixé uniquement la ligne et pas la colonne c'est une référence mixte.

Une référence mixte est une référence dans laquelle on fixe soit la colonne soit la ligne. Exemple : $F2, F$2

Pour insérer le symbole dollar $ dans la formule, plusieurs méthodes sont possibles :

  1. Chercher la touche correspondante sur le clavier, elle doit être près de la grosse touche Entrée.
  2. Utiliser le raccourci ALT + 036
  3. Appuyer sur la touche de fonction F4

Travail à faire

Sur une nouvelle feuille du classeur, utiliser la référence mixte dans la cellule B2 à recopier vers les autres cellules pour créer la table de multiplication comme le montre la capture.

Références et ordre des opérations

Référence absolue

Le référencement absolu consiste à fixer à la fois les lignes et les colonnes, de cette façon la cellule ou la plage de cellules référencée est figée lorsqu'on recopie la formule horizontalement ou verticalement vers d'autres cellules. Pour appliquer une référence absolue il suffit d'ajouter, dans la formule, un symbole dollar $ devant la lettre de la colonne à fixer, et un autre symbole $ devant le numéro de la ligne à fixer. Exemple : $F$2

Références et ordre des opérations

Un moyen élégant et propre pour utiliser le référencement absolu est de nommer la cellule ou la plage de cellules à fixer. Le fait de nommer les cellules permet d'obtenir des formules beaucoup plus parlantes puisqu'on peut utiliser ces noms dans les expressions de calcul. La capture qui suit montre la formule précédente après avoir nommé la cellule F2 en TVA.

Références et ordre des opérations

Travail à faire

Sur la feuille Facture, effectuer les tâches suivantes :

  1. Définir TVA comme nom de la cellule F2
  2. Utiliser ce nom dans la formule de la cellule F6
  3. Recopier la formule vers les cellules d'en bas

Téléchargement

Télécharger ce document au format PDF

MOS Prepa