L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce tutoriel, nous allons plonger dans un sujet que beaucoup d’utilisateurs d’Excel rencontrent sans vraiment s’en rendre compte : les erreurs d’arrondi.

Si vous avez déjà été en pleine réunion et que quelqu’un vous a interrompu pour dire que vos chiffres ne s’additionnent pas correctement, vous savez à quel point cela peut être frustrant.

Il arrive souvent que de simples erreurs d’arrondi soient à l’origine de ces différences.

Je vais donc vous montrer comment éviter ces problèmes à l’aide de la fonction ARRONDI d’Excel, et vous donner toutes les astuces pour que vos chiffres soient irréprochables lors de vos prochaines présentations.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

1. Présentation

Avant de résoudre un problème, il est essentiel de le comprendre. Les erreurs d’arrondi surviennent souvent lorsque nous manipulons des chiffres avec plusieurs décimales, et qu’Excel affiche des résultats tronqués à l’écran. Cependant, dans le calcul interne, Excel continue de travailler avec la valeur précise, ce qui peut causer des écarts dans les résultats.

Pour illustrer cela, prenons un exemple simple : imaginons que nous gérions un petit café et que nous suivions les ventes de différentes boissons.

Voici les données que nous allons utiliser :

Excel formation - 0021-Erreurs arrondi - 01

Ces totaux sont précis, mais trop détaillés pour une présentation.

Si nous additionnons ces chiffres avec la formule =SOMME(D2:D5), Excel affichera 650,845 €. Mais imaginez que dans une présentation, vous n'affichiez que deux décimales.

Si nous arrondissons visuellement à deux décimales (ce que vous ferez probablement pour simplifier la lecture), nous obtiendrons un total de 650,85 €.

Excel formation - 0021-Erreurs arrondi - 02

Voilà le problème : il y a un écart minime, mais il peut semer le doute dans l’esprit de votre auditoire.

 

2. Utiliser la fonction ARRONDI pour éviter les erreurs

La fonction ARRONDI est la solution à ces problèmes.

Elle permet de fixer un nombre spécifique de décimales pour les résultats de vos calculs, et d'assurer que les chiffres affichés sont les mêmes que ceux qu'Excel utilise dans ses calculs internes.

Cela permet de garantir que vos sommes totales seront toujours cohérentes et ne prêteront pas à confusion.

 

   2.1. Appliquer la fonction ARRONDI

 

La syntaxe de la fonction ARRONDI est simple :

=ARRONDI(nombre; nombre_de_décimales).

Le premier argument correspond à la valeur que nous voulons arrondir (par exemple, la cellule contenant un total), et le second argument détermine le nombre de décimales que nous souhaitons conserver.

Nous allons appliquer cette fonction à notre tableau.

Pour arrondir le total des ventes de café à deux décimales, nous allons utiliser la formule suivante dans la cellule D9 :

 =ARRONDI(B9*C9;2) 

Cette formule nous donnera 180,85 € au lieu de 180,851 €.

Faisons de même pour les autres lignes et étendant la formule vers le bas :

Excel formation - 0021-Erreurs arrondi - 03

Après avoir appliqué la fonction ARRONDI à toutes les lignes du tableau, si regardons le résultat de la somme des cellules correspondantes, nous obtenons un total propre de 650,84 €, qui correspond parfaitement à ce que nous voyons à l'écran.

Nous avons donc éliminé toute confusion possible liée à l’affichage des décimales et nous sommes assurés que les résultats sont exacts.

 

   2.2. Astuces supplémentaires pour maîtriser les arrondis

 

Si nous avons besoin d’arrondir à l’unité la plus proche (c’est-à-dire sans décimales), nous pouvons utiliser cette fonction ARRONDI(), en spécifiant que le nombre décimales doit être nul.

Cela permet de simplifier les chiffres pour une présentation plus claire, en se débarrassant des décimales tout en conservant un niveau de précision acceptable pour la plupart des rapports ou présentations.

Ainsi la formule « =ARRONDI(120,567; 0) », nous renverra 121, car nous demandons à Excel d’arrondir à zéro décimale, c’est-à-dire à l’unité la plus proche.

Il existe également une alternative pour se débarrasser des décimales qui est la fonction ENT().

Cette fonction ne fait pas exactement la même chose qu’ARRONDIE(). Alors qu’ARRONDI() arrondit au nombre entier le plus proche (en fonction de la valeur après la virgule, soit en arrondissant vers le haut si la valeur est égale ou supérieure à 0,5), la fonction ENT() se contente de tronquer la valeur à l'entier inférieur le plus proche, sans tenir compte des décimales.

Autrement dit, ENT() renverra toujours l'entier inférieur.

Par exemple, si nous utilisons la formule « =ENT(120,567) », le résultat sera 120.

La différence ici est qu’ARRONDIE() tiendra compte des décimales pour ajuster la valeur (0,567 est supérieur à 0,5, donc Excel arrondi vers le haut), tandis que ENT() ignore les décimales et retourne directement l'entier inférieur, quelle que soit la valeur après la virgule.

Enfin, si vous avez besoin de toujours arrondir vers le haut, quelle que soit la valeur des décimales, vous pouvez utiliser la fonction ARRONDI.SUP(). Cette fonction arrondit systématiquement au nombre entier supérieur, même si la valeur après la virgule est inférieure à 0,5. Par exemple, « =ARRONDI.SUP(120,1) » renverra 121, tandis que « =ARRONDI.SUP(120,567) » donnera également 121.

Maintenant, si nous souhaitons arrondir à un chiffre précis AVANT la virgule, c’est-à-dire que nous voulons arrondir un nombre à un multiple d'une puissance de dix (par exemple, arrondir à la dizaine, à la centaine, ou au millier), nous pouvons utiliser des valeurs négatives pour le second argument de la fonction ARRONDI().

Cela nous permet d’arrondir à un certain nombre de positions à gauche du séparateur décimal.

Ainsi, si nous utilisons la formule « =ARRONDI(12345; -2) », Excel arrondira cette valeur à la centaine la plus proche. Le résultat sera 12300. La logique est la suivante : en utilisant un argument négatif (-2), nous demandons à Excel d’arrondir en tenant compte des deux chiffres avant la virgule. Cela signifie que le nombre 45 (les deux derniers chiffres) est arrondi à 00 car il est inférieur à 50, et les chiffres restants restent inchangés.

 

3. Ajouter une ligne d'ajustement pour éviter les écarts d'arrondi dans des rapports complexes

Dans des cas plus complexes, notamment lors de la gestion de grands ensembles de données ou de rapports financiers détaillés, il est fréquent que des différences minimes s’accumulent en raison des arrondis. Ces différences peuvent être gênantes lorsqu’il s’agit de présenter un rapport avec un total exact.

Pour résoudre ce problème, nous allons ajouter une ligne « Ajustement d’arrondi » qui permettra de compenser ces petits écarts.

Nous devons d'abord calculer l'écart entre la somme des valeurs avant arrondi et la somme des valeurs après arrondi.

Pour ce faire, nous allons utiliser la formule suivante :

 =SOMME(Valeurs réelles)-SOMME(Valeurs arrondies) 

Cela nous permettra de connaître la différence que nous devons compenser.

Dans notre exemple, les valeurs réelles (avant arrondi) ne sont pas directement visibles dans notre tableau, car nous avons arrondi les résultats dans une autre colonne.

Pour obtenir la somme des valeurs réelles, nous allons utiliser la fonction SOMMEPROD(), qui permet de multiplier les valeurs de deux plages de cellules et de faire la somme de ces produits.

Cette méthode est particulièrement utile lorsque nous avons des quantités et des prix unitaires, car SOMMEPROD() multiplie chaque valeur de la colonne des quantités avec la colonne des prix, nous permettant ainsi de calculer les montants précis sans avoir besoin de créer une colonne intermédiaire pour chaque produit.

Dans notre cas, nous avons les colonnes B9:B14 pour les quantités vendues, C9:C14 pour les prix unitaires, et D9:D14 pour les totaux arrondis. Voici comment nous pouvons calculer l’écart d’arrondi :

 =SOMMEPROD(B9:B17; C9:C17) - SOMME(D9:D17) 

La partie SOMMEPROD(B9:B17; C9:C17) calcule la somme des valeurs réelles en multipliant chaque quantité vendue par son prix unitaire.

La partie SOMME(D9:D17) calcule la somme des valeurs arrondies (après l’application de la fonction ARRONDI()).

Excel formation - 0021-Erreurs arrondi - 04

Ici, étant donné qu’il y a un peu plus de lignes, l’écart est de -0,0284, soit près de 3 centimes

Ainsi, notre tableau est parfaitement équilibré. Même si la différence est infime, ce type d’ajustement permet d’éviter tout doute et garantit que nos résultats sont parfaitement cohérents.

N'oubliez pas que dans Excel, les chiffres peuvent être votre meilleur allié... ou votre pire ennemi. Utiliser la fonction ARRONDI vous permet de toujours avoir des résultats cohérents et fiables, quel que soit le contexte.

 



Articles qui pourraient vous intéresser

L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)
Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?
Comment créer un tableau de bord commercial intelligent avec Excel ?
Comment créer un graphique dessiné à la main avec Excel ?
Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment gérer ses comptes sur Excel avec le « Suivi du budget familial »
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans Excel ?
Comment créer un graphique dynamique en quelques minutes sur Excel sans VBA ni TCD ?
Comment créer des feux de signalisation tricolores dynamiques dans Excel sans VBA ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.