SOMME, MOYENNE, MIN, MAX, NB et l’arrondi comptable

Sans fonctions, Excel ne serait qu’une calculatrice un peu encombrante. Les fonctions sont des formules prêtes à l’emploi : on leur passe des cellules, elles renvoient un résultat. Cet épisode couvre les cinq ou six fonctions que vous utiliserez chaque jour, puis traite une question que se posent tous les comptables un jour ou l’autre : pourquoi le total affiché ne tombe-t-il pas sur la somme des lignes affichées ?

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

• écrire une fonction avec ses arguments, à la main ou avec l’assistant ;
• totaliser, moyenner, trouver un maximum, un minimum et compter ;
• distinguer NB, NBVAL et NB.VIDE ;
• arrondir un montant au centime et expliquer un écart de centimes ;
• utiliser la barre d’état comme calculatrice de contrôle.

Anatomie d’une fonction

Une fonction s’écrit NOM(argument1;argument2;…). Le nom est suivi d’une parenthèse ouverte, collée au nom, les arguments sont séparés par des points-virgules (avec la configuration française) et la parenthèse se referme. Les arguments peuvent être des nombres, des cellules, des plages (B2:B7) ou d’autres fonctions.

Deux aides à la saisie : en tapant =SOM, Excel propose la liste des fonctions qui commencent ainsi (Tab valide la proposition) ; et le bouton fx à gauche de la barre de formule ouvre l’assistant, qui décrit chaque argument. Si vous utilisez Excel en anglais, les équivalents sont donnés entre parenthèses ci-dessous.

Le cas : les ventes du premier semestre

Atlas Négoce suit son chiffre d’affaires hors taxes mois par mois. Le gérant demande le total du semestre, la moyenne mensuelle, le meilleur et le plus faible mois.

Figure 1 : les six indicateurs de la colonne E sont calculés à partir de la plage B2:B7.

• Total (repère 1) : =SOMME(B2:B7) donne 2 503 700,00. On additionne 412 500 + 389 200 + 455 800 + 401 000 + 478 300 + 366 900.
• Moyenne (repère 2) : =MOYENNE(B2:B7) donne 417 283,33, soit 2 503 700 ÷ 6.
• Meilleur mois : =MAX(B2:B7) donne 478 300,00 (mai) ; plus faible : =MIN(B2:B7) donne 366 900,00 (juin).
• Comptages (repère 3) : =NB(B2:B7) et =NBVAL(A2:A7) donnent tous deux 6 : six mois sont renseignés.

La somme automatique

Sélectionnez la cellule sous la colonne à additionner et appuyez sur Alt + = : Excel écrit =SOMME( et propose la plage de nombres située au-dessus. Vérifiez que le pourtour animé correspond bien à ce que vous voulez totaliser, puis validez. Le même geste fonctionne en ligne, et sur une sélection de plusieurs colonnes il remplit tous les totaux d’un coup.

Sans formule : la barre d’état

Sélectionnez B2:B7 et regardez en bas à droite de la fenêtre : Moyenne : 417 283,33 — Nb : 6 — Somme : 2 503 700,00. C’est le meilleur contrôle rapide d’un total. Un clic droit sur la barre d’état permet d’ajouter le minimum, le maximum ou le nombre de valeurs numériques.

Les pièges de ces fonctions

• Plage incomplète. Si vous ajoutez un mois en ligne 8, =SOMME(B2:B7) ne le prend pas en compte. Insérer une ligne à l’intérieur de la plage l’étend automatiquement ; ajouter à la suite ne le fait pas. (Les tableaux Excel, épisode 8, règlent ce problème.)
• Zéro contre vide. MOYENNE ignore les cellules vides mais compte les zéros. Un mois pas encore saisi doit rester vide.
• Lignes masquées ou filtrées. SOMME additionne aussi les lignes masquées. Pour ne totaliser que ce qui est visible après un filtre, on utilise SOUS.TOTAL (épisode 9).
• Textes parmi les nombres. SOMME et NB les ignorent sans signal : comparez toujours NB et NBVAL sur une colonne que vous croyez numérique (épisode 2).
• Moyenne de moyennes. La moyenne des taux de marge de plusieurs produits n’est pas le taux de marge global : il faut pondérer (épisode 24).

L’arrondi comptable

Figure 2 : trois produits vendus au poids. Colonne D : montants bruts ; colonne E : montants arrondis.

Atlas Négoce vend des produits au poids : 2 kg à 5,002 MAD le kilo, soit 10,004 MAD par ligne. Le format monétaire à deux décimales affiche 10,00 sur chaque ligne. Pourtant, le total de la colonne D (repère 1) affiche 30,01 : Excel a additionné les valeurs réelles, 10,004 × 3 = 30,012, puis affiché le résultat à deux décimales. Un lecteur qui additionne les montants affichés trouve 30,00 et croit à une erreur.

Quand ce montant est facturé, comptabilisé ou rapproché avec un autre document, il doit être un montant au centime, pas une valeur à trois décimales qu’on masque. La solution est la fonction ARRONDI(nombre;décimales) :

=ARRONDI(B2*C2;2)

Elle arrondit la valeur calculée au centime. Chaque ligne vaut désormais exactement 10,00, et le total de la colonne E (repère 2) vaut 30,00, soit la somme des montants affichés.

Figure 3 : résultats calculés sur le nombre 1 234,567. Le dernier cas montre que −2,5 est arrondi à −3 : Excel arrondit « loin de zéro » quand la décimale est 5.

Deux précisions utiles :

• Excel arrondit la moitié « loin de zéro » : 2,5 devient 3 et −2,5 devient −3. Ce n’est pas l’arrondi « au pair » de certains logiciels : si vous rapprochez avec un autre outil, un écart d’un centime sur un montant se terminant par 5 peut venir de là.
• ENT n’arrondit pas, elle tronque vers le bas : ENT(-2,5) donne −3, pas −2.

Pourquoi arrondir aussi pour comparer ?

Excel calcule avec environ 15 chiffres significatifs, en binaire. Certains résultats, qui devraient être égaux, diffèrent de quelques milliardièmes. Une formule de contrôle comme =D10=E10 peut ainsi répondre FAUX alors que les deux montants s’affichent identiques. Écrire =ARRONDI(D10-E10;2)=0 règle le problème : on compare des centimes, pas des fractions de centimes. Nous généraliserons ce réflexe dans l’épisode 33 sur les contrôles comptables.

La TVA par ligne ou sur le total

L’arrondi crée un autre écart classique. Une facture compte trois lignes à 33,33, 33,33 et 33,34 MAD hors taxes (total 100,00). La TVA à 20 % arrondie ligne par ligne donne 6,67 + 6,67 + 6,67, soit 20,01 ; calculée sur le total (100,00 × 20 %), elle donne 20,00. Un centime d’écart, sans erreur de calcul. L’important est de choisir une méthode, de l’appliquer partout (facturation comme comptabilité) et de la documenter ; nous la retrouverons à l’épisode 35 sur la TVA.

Enfin, une mise en garde : l’option Excel Définir la précision au format affiché (Fichier › Options › Options avancées) remplace définitivement les valeurs par celles affichées, dans tout le classeur. Elle paraît pratique, mais elle est irréversible et touche tout, y compris les feuilles où l’on avait besoin des décimales. Préférez ARRONDI, appliquée à bon escient.

À vous de jouer

Dans le classeur d’exercice :

1. Onglet Ventes : complétez E2:E7 avec les formules SOMME, MOYENNE, MAX, MIN, NB et NBVAL. Contrôlez le total avec la barre d’état en sélectionnant B2:B7.
2. Ajoutez en ligne 8 le mois de juillet avec 430 000. Que devient le total ? Étendez la plage de la formule pour l’inclure.
3. Onglet Arrondi : remplissez E2:E4 avec =ARRONDI(B2*C2;2) et le total E5. Comparez avec D5.
4. Onglet Fonctions d’arrondi : remplacez le nombre par 2 549,50 et prévoyez les résultats avant de les lire.

Correction. Total 2 503 700,00 ; moyenne 417 283,33 ; maximum 478 300,00 ; minimum 366 900,00 ; NB et NBVAL valent 6. Avec juillet, la plage B2:B7 ne suffit plus : il faut B2:B8 et le total devient 2 933 700,00. Sur l’onglet Arrondi, D5 affiche 30,01 et E5 affiche 30,00. Pour 2 549,50 : ARRONDI(…;2) = 2 549,50 ; ARRONDI(…;0) = 2 550 ; ARRONDI(…;−2) = 2 500 ; ARRONDI.SUP = 2 550 ; ARRONDI.INF = 2 549 ; ENT = 2 549.

À retenir

• Une fonction : NOM(arguments séparés par des points-virgules). Les sept de base : SOMME, MOYENNE, MAX, MIN, NB, NBVAL, NB.VIDE.
• Alt + = pour la somme automatique ; la barre d’état pour un contrôle immédiat.
• Une plage fixe n’inclut pas les lignes ajoutées après coup.
• Le format n’arrondit pas : ARRONDI(x;2) fixe la valeur au centime et supprime les écarts de total.
• Pour comparer deux montants, comparez des valeurs arrondies.

Au prochain épisode : les raccourcis clavier et les gestes qui font gagner une heure par jour, avant de passer à la mise en forme professionnelle.