Votre première formule : opérateurs, priorités de calcul et recopie

Écrire une formule, c’est confier un calcul à Excel pour ne plus jamais le refaire. C’est aussi le moment où l’on prend de bonnes ou de mauvaises habitudes. Aujourd’hui, vous allez écrire vos premières formules sur une vraie facture fournisseur, comprendre dans quel ordre Excel calcule, et recopier une formule sur toute une colonne en une seconde.

Ce que vous saurez faire à la fin de l’épisode

• écrire une formule qui calcule à partir de cellules et non de chiffres tapés ;
• utiliser les six opérateurs arithmétiques (+ − * / ^ %) et prévoir le résultat d’une expression complexe ;
• recopier une formule par glisser ou par double-clic ;
• calculer HT, TVA et TTC d’une facture et en vérifier les totaux ;
• identifier les erreurs de saisie les plus courantes.

Anatomie d’une formule

Une formule est une cellule dont le contenu commence par =. Ce signe dit à Excel : « ce qui suit est un calcul, pas un texte ». Ensuite on combine :

• des constantes (20%, 1000) ;
• des adresses de cellules (B2, C2) : Excel va chercher leur valeur à chaque recalcul ;
• des opérateurs (+, *…) ;
• et plus tard des fonctions (SOMME, SI…).

Reprenons la facture de fournitures d’Atlas Négoce. La première ligne est une commande de 12 ramettes à 185,00 MAD hors taxes. On veut le total hors taxes de la ligne.

Figure 1 : en D2, la formule =B2*C2 multiplie la quantité (repère 2) par le prix unitaire (repère 3). Le résultat, 2 220,00, s’affiche dans la cellule (repère 1).

Pour la saisir : sélectionnez D2, tapez =, cliquez sur B2 (Excel écrit l’adresse à votre place), tapez *, cliquez sur C2, appuyez sur Entrée. Cliquer plutôt que taper évite les fautes de frappe d’adresse. Après validation, la cellule affiche le résultat et la barre de formule conserve la formule : c’est là qu’on la relit.

La différence avec une calculatrice est décisive : si vous changez la quantité ou le prix, le total se met à jour sans qu’on touche à la formule. C’est ce qu’on appelle le recalcul automatique. (Si vos résultats ne bougent plus, vérifiez dans Formules › Options de calcul que le mode est bien Automatique ; la touche F9 force un recalcul en mode manuel.)

Les opérateurs

Il existe aussi les opérateurs de comparaison (=, <>, <, >, <=, >=) qui renvoient VRAI ou FAUX et que nous utiliserons avec la fonction SI à l’épisode 13, et l’opérateur de concaténation & (épisode 16).

L’ordre des opérations

Excel applique les règles de priorité de l’arithmétique, dans cet ordre :

1. ce qui est entre parenthèses, de la plus intérieure à la plus extérieure ;
2. le pourcentage % ;
3. la puissance ^ ;
4. la multiplication et la division, de gauche à droite ;
5. l’addition et la soustraction, de gauche à droite.

Figure 2 : sept expressions, avec le résultat réellement calculé.

Lisez la figure ligne par ligne :

• =10+5*2 donne 20 et non 30, car la multiplication passe avant l’addition (10 + 10).
• =(10+5)*2 donne 30 : les parenthèses forcent l’addition en premier.
• =100-100*20% donne 80 : une remise de 20 % sur 100.
• =1000*(1+20%) donne 1 200, la méthode la plus courte pour passer d’un montant hors taxes au montant toutes taxes comprises à 20 %.
• =10+20/2 donne 20 : la division passe avant l’addition (10 + 10). Pour la moyenne de 10 et 20, il faut =(10+20)/2, soit 15. C’est l’erreur la plus fréquente sur une moyenne écrite à la main.

Un piège plus subtil : Excel applique le signe moins avant la puissance, ce qui surprend les mathématiciens. =-2^2 donne 4 (et non −4). Si vous voulez −4, écrivez =-(2^2). Dans le doute, ajoutez des parenthèses : elles ne coûtent rien et rendent la formule lisible pour le collègue qui la reprendra.

Recopier une formule

Écrire la même formule vingt fois serait absurde. Excel la recopie pour vous, et surtout adapte les adresses à chaque ligne : on parle de références relatives.

1. Sélectionnez D2. Un petit carré apparaît en bas à droite du cadre : c’est la poignée de recopie.
2. Faites-la glisser vers le bas jusqu’en D5, ou double-cliquez dessus : Excel recopie jusqu’à la fin du tableau voisin.
3. Cliquez sur D3 et regardez la barre de formule : =B3*C3. Excel a décalé les adresses d’une ligne.

On peut aussi copier la cellule (Ctrl + C) et la coller sur une plage (Ctrl + V) : même effet. Sur la facture, nous procédons de la même façon pour la TVA (=D2*20%) et le total TTC (=D2+E2).

Figure 3 : la ligne 6 contient les totaux, calculés avec la somme automatique. Contrôle : 5 467,00 × 20 % = 1 093,40, et 5 467,00 + 1 093,40 = 6 560,40.

Vérifiez les résultats avec une autre méthode : c’est le réflexe qui distingue le comptable du simple utilisateur. Les quatre lignes donnent 2 220 + 2 560 + 435 + 252 = 5 467,00 HT ; la TVA à 20 % vaut 5 467 × 0,20 = 1 093,40 ; le TTC est donc 6 560,40. Les trois totaux de la ligne 6 sont obtenus avec =SOMME(D2:D5), =SOMME(E2:E5) et =SOMME(F2:F5) : la fonction SOMME sera détaillée à l’épisode 5, mais le raccourci Alt + = la saisit pour vous.

Les erreurs courantes et leur lecture

Une erreur n’est pas une catastrophe : c’est Excel qui vous dit pourquoi il ne peut pas calculer. Le pire, ce sont les calculs faux sans erreur, comme les 0 que nous verrons demain.

Les mauvaises habitudes à ne pas prendre

• Taper des chiffres au lieu de cliquer sur les cellules. =2220*20% donne le bon résultat aujourd’hui, mais ne suivra plus quand la quantité changera.
• Cacher le taux dans la formule. Ici, =D2*20% écrit le taux de TVA en dur. C’est acceptable pour s’entraîner, mais si le taux change (ou si un article est à 10 %), il faut rouvrir chaque formule. À l’épisode 4, nous saisirons le taux dans une cellule de paramètre.
• Mélanger valeurs affichées et valeurs réelles. Si vous retapez à la main le résultat que vous voyez, vous remplacez la formule par une constante : la cellule ne se mettra plus à jour.
• Oublier de fermer une parenthèse. Excel signale l’erreur à la validation et colorie les paires de parenthèses pendant la saisie : regardez les couleurs.

À vous de jouer

Ouvrez le classeur d’exercice, onglet Facture (les cellules D2:F6 sont à compléter).

1. En D2, saisissez =B2*C2 en cliquant sur les cellules, puis recopiez jusqu’en D5 par double-clic sur la poignée.
2. En E2, saisissez =D2*20%, recopiez. En F2, saisissez =D2+E2, recopiez.
3. En ligne 6, calculez les trois totaux avec Alt + =.
4. Contrôle : multipliez le total HT par 1,2 sur une calculatrice et comparez avec le total TTC.
5. Onglet Priorités : avant de regarder la colonne C, notez sur papier le résultat que vous attendez pour chacune des sept lignes, puis comparez.

Correction. Total HT : 5 467,00 ; TVA : 1 093,40 ; TTC : 6 560,40 ; et 5 467 × 1,2 = 6 560,40, le contrôle est bon. Sur l’onglet Priorités, les résultats sont 20, 30, 80, 1 200, 8, 20 et 15. Si vous avez trouvé 30 pour la première ligne, vous avez calculé de gauche à droite sans appliquer la priorité de la multiplication.

À retenir

• Une formule commence par = et calcule à partir d’adresses de cellules : elle se met à jour seule.
• Ordre de calcul : parenthèses, puis %, ^, * /, enfin + -. Dans le doute, mettez des parenthèses.
• La poignée de recopie étend une formule en décalant les références.
• Un résultat se vérifie toujours par une seconde méthode.
• Ne pas cacher un taux dans une formule : c’est le sujet de l’épisode 4, qui explique comment figer une cellule avec le signe dollar.