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

Découvrez l'erreur courante qui plombe vos tableaux Excel et surtout comment l'éviter. Bonjour à tous, c'est Alex d'excelformation.fr et dans ce tutoriel, nous allons plonger dans un sujet que beaucoup d'utilisateurs d'Excel rencontrent sans vraiment s'en rendre compte. Il s'agit des 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 évidemment à quel point cela peut être frustrant. Il arrive en effet souvent que de simples erreurs d'arrondi soient à l'origine de ces différences et donc je vais vous montrer comment éviter ces problèmes à l'aide des fonctions arrondies et de ces déclinaisons. Nous allons également découvrir toutes les astuces afin de permettre à nos chiffres d'être irréprochables lors des prochaines présentations. Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner et surtout à activer la clochette de notification afin d'être sûr de ne manquer aucune astuce et de devenir rapidement un pro d'Excel. Comme d'habitude, si vous voulez être sûr de bien comprendre cette vidéo, vous pouvez télécharger le support de ce tutoriel ainsi que son corrigé en suivant le lien disponible dans la description de la vidéo, ce qui vous permettra de réaliser les opérations en même temps que moi pour bien comprendre tout ce que nous allons voir dans cette vidéo. Alors comme nous venons de le voir, nous allons découvrir quelle est l'erreur qui plombe vos tableaux Excel sans que vous ne vous en rendiez compte. Mais avant de résoudre un problème, il est essentiel de le comprendre et donc nous allons ici nous pencher sur les erreurs d'arrondi qui vont survenir lorsque nous manipulons des chiffres avec plusieurs décimales et qu'Excel affiche des résultats tronqués à l'écran. Cependant, dans son calcul interne, Excel va continuer de travailler avec la valeur précise de la valeur numérique, ce qui pourra causer des écarts dans les résultats. Pour illustrer cela, prenons un exemple simple. Ici, nous imaginons que nous devions gérer un petit café et que nous suivions les ventes des différentes boissons. Voici donc les données que nous allons utiliser qui sont affichées dans le tableau que nous avons actuellement à l'écran. Pour obtenir le total de chacune des boissons vendues, il suffit de multiplier les quantités vendues par le préunitaire et de valider en appuyant sur Entrée. Comme vous pouvez le voir, ici nous avons une grande précision qui permet d'avoir les valeurs réelles et donc si nous sélectionnons les 4 cellules que nous venons de calculer et que nous nous rendons en bas dans la barre d'état, nous voyons que le résultat total que cela représente est de 650,845 que ici Excel a arrondi dans la cellule qui calcule le total à 650,85. Ici, la différence est minime, elle est d'un demi-centime, mais bien entendu, plus nous allons manipuler de nombres avec une précision accrue et plus cette différence pourra être élevée. Nous verrons tout à l'heure un exemple sur lequel cet écart peut rapidement atteindre plusieurs centimes que nous pourrons extrapoler ensuite à plusieurs euros. Pour pouvoir éviter ce genre de problème, nous allons pouvoir utiliser la fonction Arrondi. Cette fonction permet en effet de fixer un nombre spécifique de décimales pour les résultats des calculs et d'assurer que les chiffres affichés soient les mêmes que ceux qu'Excel utilise dans ses calculs internes. Cela nous permettra donc de garantir que les sommes totales seront toujours cohérentes avec celles qui sont affichées et qu'il n'y aura donc aucune confusion qui pourra être permise. La syntaxe de la fonction Arrondi est très simple, il suffit d'aller chercher la fonction. En tapant égal arrondi, le premier paramètre va être la valeur que nous allons vouloir arrondir. Donc ici nous allons reprendre le calcul des quantités multipliées avec le prix unitaire et ensuite nous allons spécifier le nombre de chiffres que nous voulons derrière la virgule. Pour obtenir donc un chiffre en centimes, nous voulons deux chiffres derrière la virgule, donc nombre de chiffres est égal à 2. Nous fermons la parenthèse et nous validons en appuyant sur contrôle et entrée afin de valider la formule sur toutes les cellules actuellement sélectionnées. Et maintenant, lorsque nous sélectionnons ces cellules, nous voyons que le résultat affiché, 650,84, est bien le même que celui qui a été calculé par la fonction Somme. Cette opération nous a donc permis d'éliminer toute confusion possible liée à l'affichage des décimales et nous serons assurés donc que les résultats seront exacts. Si nous avons besoin d'arrondir à l'unité la plus proche, c'est-à-dire à l'euro et donc sans décimales, nous pouvons utiliser cette fonction Arrondi en spécifiant que le nombre de décimales doit être nul. Donc ici, dans notre formule, nous remplaçons le 2 par un 0 et comme tout à l'heure, nous validons en appuyant sur contrôle et entrée pour avoir uniquement des valeurs arrondies à l'euro. Il existe également une alternative à cette fonction Arrondi avec un nombre pour deux décimales de 0 qui est la fonction AntEnt qui va permettre de tronquer la valeur afin de ne conserver que la valeur entière qui correspond. Nous appelons égal entier, nous multiplions les quantités vendues par le préunitaire et ici, il y a un seul argument. Nous pouvons directement valider en appuyant sur contrôle et entrée sauf que comme vous pouvez le constater ici, ces deux fonctions peuvent retourner un résultat relativement différent étant donné que la fonction Arrondi tient compte uniquement des décimales pour ajuster la valeur c'est-à-dire que si la valeur est supérieure à 0.5, l'Arrondi va donc l'arrondir à la valeur supérieure alors que si elle est comprise entre 0 et 0.4, elle sera arrondie à la valeur inférieure tandis que AntEnt va ignorer les décimales en tronquant directement le nombre au niveau de la virgule afin de retourner directement l'entier inférieur, quelle que soit la valeur après la virgule. C'est pourquoi si nous avions 180.99, le résultat serait quand même 180. Et donc tant que nous en sommes à avoir les différentes variantes de la fonction Arrondi sachez qu'il est également possible de l'arrondir systématiquement vers le haut quelle que soit la valeur des décimales en utilisant pour cela la fonction Arrondi.sup toujours du même calcul, donc de nos quantités vendues multipliées par le préunitaire en choisissant ici encore d'avoir des valeurs arrondies à l'euro et donc avec un nombre de décimales égal à 0. Lorsque nous validons, tous les nombres seront donc arrondis à la valeur supérieure peu importe la valeur des nombres décimaux. Sachez également que si vous souhaitez arrondir un nombre à un chiffre précis avant la virgule c'est-à-dire que vous voulez arrondir un nombre en fonction d'un multiple d'une puissance de 10 par exemple à la dizaine, à la centaine ou au millier il faudra pour cela utiliser des valeurs négatives en tant que second argument de la fonction Arrondi. Donc ici nous allons rappeler Arrondi et ici pour avoir des valeurs arrondies à la dizaine d'euros nous allons spécifier la valeur "-1", et nous allons valider. Maintenant tous les nombres sont arrondis à la dizaine d'euros la plus proche. Si nous avions utilisé la valeur "-2", alors ces résultats seraient arrondis à la centaine "-3", au millier, etc. Dans les cas les 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 de ces arrondis et que ces différences puissent générer des écarts qui cette fois-ci seront constitutifs de plusieurs centimes d'euros voire même plusieurs euros en fonction de votre ensemble de données. Pour résoudre ce problème, nous allons pouvoir ajouter une ligne ajustement d'arrondi comme nous l'avons ici qui va permettre de compenser ces petits écarts. Pour cela nous allons calculer l'écart entre la somme des valeurs avant l'arrondi et la somme des valeurs après cet arrondi. Cela nous permettra donc de connaître la différence que nous devons compenser et donc dans notre exemple, les valeurs réelles avant arrondies ne sont pas visibles directement dans le tableau car nous avons arrondi les résultats dans une autre colonne. Pour obtenir donc la somme des valeurs réelles, nous allons utiliser la fonction SUMPROD qui permet de multiplier les valeurs de deux plages de cellules afin de retourner la somme de ces produits avec le nombre de décimales qui correspond. Nous appelons la fonction SUMPROD. Nous multiplions la colonne quantité avec la colonne préunitaire. Nous sélectionnons les coordonnées de ces deux plages de cellules et lorsque nous validons, nous voyons que ce résultat est égal à 1392,2716. Or, si nous sélectionnons toutes les cellules situées juste au-dessus, nous voyons que le résultat retourné dans la cellule est de 1392,30 c'est-à-dire qu'il y a pratiquement 3 centimes d'erreur sur l'ensemble du listing. Pour obtenir la valeur réelle de cet écart, nous allons maintenant soustraire cette valeur en utilisant, comme nous l'avons fait juste avant, la fonction SUM sur la plage des 9 à des 16. Lorsque nous validons, nous voyons que ce résultat est bien de 3 centimes. Nous pouvons ajouter un détail supplémentaire en ajoutant des décimales ce qui nous permet d'obtenir le montant réel ici dans la cellule D18 en tenant compte de cet écart. De cette manière, notre tableau est maintenant parfaitement équilibré et même si la valeur peut ici paraître infime, ce type d'ajustement permet d'éviter tout doute et de garantir que nos résultats sont parfaitement cohérents. En effet, n'oubliez pas que dans Excel, les chiffres peuvent être notre meilleur allié mais également notre pire ennemi et donc c'est pourquoi utiliser la fonction ARRONDI va permettre de toujours avoir des résultats cohérents et fiables, quel que soit le contexte. Comme d'habitude, vous pouvez télécharger le support de secours en suivant le lien que vous trouverez dans la description de la vidéo. Si la vidéo vous a plu, n'oubliez pas de la liker et de la partager à vos collègues et amis sur l'ensemble de vos réseaux sociaux. Vous pouvez également me laisser vos impressions dans l'espace des commentaires. Je lirai chacun d'entre eux et y répondrai avec plaisir. Evidemment, si ce n'est pas encore fait, abonnez-vous à la chaîne pour ne manquer aucune astuce. C'est également très important pour m'aider à gagner en visibilité afin de pouvoir développer la chaîne et continuer de vous proposer des tas de nouvelles vidéos. Merci d'avoir suivi cette vidéo et à très bientôt pour un prochain tuto !