Préparez votre certification
Microsoft Office Specialist

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

Recevez ce cours au format PDF en envoyant un message ici

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.

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.

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.

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).

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.

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.

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é.

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.

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.

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.

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.

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.

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é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.

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.

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.

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é).

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.

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.

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

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

Recevez ce cours au format PDF en envoyant un message ici