22. Arrondi des données Excel

Lorsque les données sont compilées pour un rapport ou une présentation PowerPoint, les sommes arrondies posent souvent un problème dans Excel. Il est fréquemment souhaitable, mais difficile à obtenir, que les totaux arrondis correspondent exactement aux addenda arrondis. Prenons par exemple le tableau suivant :

En arrondissant les valeurs à des nombres entiers à l'aide de la fonction Format de cellule d'Excel, nous obtenons le tableau suivant. Les totaux représentant un « mauvais calcul » sont affichés en gras :

De même, lorsque les fonctions d'arrondi standard d'Excel sont utilisées, les totaux des valeurs arrondies sont calculés correctement, mais les erreurs d'arrondi s'accumulent et les résultats sont souvent très éloignés des totaux réels des valeurs d'origine. Le tableau suivant présente le résultat de la fonction =ROUND(x,0) avec l'exemple ci-dessus. Les totaux éloignés de 1 ou plus de la valeur originale sont affichés en gras :

La fonction think-cell round permet d'obtenir des totaux arrondis de manière cohérente en procédant à un minimum d'« adaptations » : alors que la plupart des valeurs sont arrondies au nombre entier supérieur, quelques valeurs sont arrondies à l'inverse, ce qui permet de conserver des calculs corrects même en accumulant les erreurs d'arrondi. Étant donné qu'il existe plusieurs possibilités d'obtenir des totaux arrondis correctement en modifiant des valeurs, le logiciel choisit la solution qui nécessite de modifier le moins de valeurs possibles et l'écart moindre par rapport aux valeurs précises. Par exemple, il est préférable d'arrondir 10,5 à 10 plutôt que d'arrondir 3,7 à 3. Le tableau suivant présente une solution optimale pour l'exemple ci-dessus, avec les valeurs « adaptées » en gras :

Pour obtenir ce résultat avec vos propres calculs, il suffit de sélectionner la plage de cellules Excel concernée. Ensuite, cliquez sur le bouton de l'onglet Formules et, si nécessaire, ajustez la précision de l'arrondi à l'aide de la liste déroulante de la barre d'outils.

22.1

Utilisation de think-cell round

22.2

Limites de think-cell round

22.3

Résolution des problèmes de formules TCROUND

22.1 Utilisation de think-cell round

La fonctionnalité think-cell round s'intègre sans problème à Microsoft Excel et propose un ensemble de fonctions similaires aux options d'arrondi standard d'Excel. Vous pouvez facilement appliquer ces fonctions à vos propres données en utilisant le groupe de rubans think-cell round dans l’onglet Formules.

22.1.1 Paramètres d’arrondi

Comme les fonctions Excel, les fonctions d'arrondi think-cell tiennent compte de deux paramètres :

x

La valeur à arrondir. Il peut s'agir d'une constante, d'une formule ou d'une référence à une autre cellule.

n

La précision d'arrondi. La signification de ce paramètre dépend de la fonction que vous utilisez. Les paramètres des fonctions think-cell sont les mêmes que pour les fonctions Excel équivalentes. Vous en trouverez des exemples dans le tableau ci-dessous.

La fonctionnalité think-cell round peut non seulement arrondir des valeurs à des nombres entiers, mais également à n’importe quel multiple. Par exemple, si vous souhaitez représenter vos données selon un schéma 5-10-15-..., il vous suffit d'arrondir à des multiples de cinq. A l'aide de la zone déroulante dans la barre d'outils think-cell round, saisissez ou sélectionnez la précision d'arrondi souhaitée. think-cell round choisit alors la fonction et les paramètres appropriés. Le tableau suivant offre quelques exemples d’arrondis de valeurs x obtenus avec la barre d’outils et leur paramètre n spécifique.

x = n =	100	50	2	1	0,01
1,018	0	0	2	1	1,02
17	0	0	18	17	17,00
54,6	100	50	54	55	54,60
1234,1234	1200	1250	1234	1234	1234,12
8776,54321	8800	8800	8776	8777	8776,54

Si les valeurs ne s'affichent pas comme prévu, vérifiez que la mise en forme de cellule d'Excel est bien définie sur Général et que les colonnes sont assez larges pour afficher toutes les décimales.

Bouton	Formule	Description
	TCROUND(x, n)	Laissez think-cell round décider auquel des deux multiples les plus proches arrondir afin de minimiser les erreurs d'arrondi.
	TCROUNDUP(x, n)	Force l’arrondi de x vers la valeur supérieure.
	TCROUNDDOWN(x, n)	Force l’arrondi de x vers la valeur inférieure.
	TCROUNDNEAR(x, n)	Force l’arrondi de x au multiple le plus proche selon la précision désirée.
		Supprimer toutes les fonctions think-cell round des cellules sélectionnées.
		Sélectionner ou saisir le multiple d'arrondi désiré.
		Met en surbrillance toutes les cellules que think-cell a choisi d'arrondir au plus éloigné des deux multiples les plus proches au lieu d'au plus proche.
		La molette qui tourne indique que think-cell round est occupé.

Pour obtenir des résultats optimaux avec un écart le plus restreint possible par rapport aux valeurs sous-jacentes, il est recommandé d'utiliser TCROUND de préférence. Utilisez les fonctions les plus restrictives telles que TCROUNDDOWN, TCROUNDUP ou TCROUNDNEAR uniquement si nécessaire.

Attention: Il est recommandé de ne jamais utiliser de fonctions non déterministes telles que RAND() au sein d'une formule TCROUND. Si les fonctions renvoient une valeur différente chaque fois qu'elles sont évaluées, think-cell round fera des erreurs lors du calcul des valeurs.

22.1.2 Disposition du calcul

La disposition en rectangle de l'exemple ci-dessus est uniquement indicative. Vous pouvez utiliser les fonctions TCROUND pour configurer l'affichage des sommes arbitraires réparties sur votre feuille Excel. Les références 3D d'Excel à d'autres feuilles et les liens vers d'autres fichiers fonctionnent également.

22.1.3 Placement des fonctions TCROUND

Étant donné que les fonctions TCROUND sont destinées à contrôler le résultat d'une cellule, il doit s'agir de la fonction la plus éloignée :

Incorrect:	=TCROUND(A1, 1)+TCROUND(SUM(B1:E1), 1)
Correct:	=TCROUND(A1+SUM(B1:E1), 1)
Incorrect:	=3*TCROUNDDOWN(A1, 1)
Correct:	=TCROUNDDOWN(3*A1, 1)

Si vous saisissez des valeurs similaires aux exemples incorrects, think-cell round affichera la valeur d'erreur Excel #VALUE!.

22.2 Limites de think-cell round

think-cell round trouve toujours une solution pour les sommes arbitraires avec des sous-totaux et des totaux. think-cell round fournit également des solutions acceptables pour certains autres calculs impliquant des fonctions de multiplication et numériques. Cependant, pour des raisons mathématiques, l'existence d'une solution arrondie de manière cohérente ne peut être garantie à partir du moment où des opérateurs différents de +, - et SUM sont utilisés.

22.2.1 Multiplication avec une constante

Dans de nombreux cas, think-cell round offre de bons résultats en présence de la multiplication d'une constante, autrement dit lorsque pas plus d'un coefficient est issu du résultat d'une autre fonction TCROUND. Prenons l'exemple suivant :

Le calcul précis de la cellule C1 est 3×1,3+1,4=5,3. Ce résultat peut être obtenu en arrondissant à la hausse la valeur 1,4, à 2.

Cependant, think-cell round peut seulement « adapter » les nombres en les arrondissant à la hausse ou à la baisse. Les déviations supplémentaires par rapport aux valeurs d'origine ne sont pas prises en charge. Aussi, pour certaines combinaisons de valeurs d'entrée, aucune solution d'arrondi cohérente ne peut être trouvée. Dans ce cas, la fonction TCROUND affiche la valeur d'erreur Excel #NUM!. L'exemple suivant présente un problème insoluble :

Le calcul précis de la cellule C1 est 6×1,3+1,4=9,2. Les cellules arrondies A1 et B1 donneraient 6×1+2=8 ou 6×2+1=13. Le résultat réel 9,2 n'a pu être arrondi à 8 ou 13 et le résultat de think-cell round ressemble à cela :

Remarque: La fonction Excel AVERAGE est interprétée par think-cell round comme la combinaison de la somme et de la multiplication d'une constante. En fait, une somme où le même addendum apparaît plus d'une fois est mathématiquement équivalente à la multiplication d'une constante, et l'existence d'une solution n'est pas garantie.

22.2.2 Multiplication générale et autres fonctions

Tant que les fonctions TCROUND sont utilisées pour toutes les cellules pertinentes et que les résultats intermédiaires sont seulement reliés par +, -, SUM et AVERAGE, les addenda et les totaux (intermédiaires) sont intégrés à un problème d'arrondi unique. En pareil cas, think-cell round trouvera une solution offrant un résultat cohérent pour toutes les cellules concernées, à condition qu'une telle solution existe.

Étant donné que TCROUND est une fonction Excel normale, elle peut être combinée avec des fonctions et des opérateurs arbitraires. Mais lorsque vous utilisez des fonctions différentes de celles mentionnées ci-dessus pour relier les résultats des instructions TCROUND, think-cell round ne peut intégrer ces composants à un problème interconnecté. En lieu et place, les composants de la formule seront gérés en tant que problèmes distincts qui seront résolus de manière indépendante. Les résultats pourront alors être utilisés en tant qu'entrée pour d'autres formules.

Dans de nombreux cas, le résultat de think-cell round restera raisonnablement précis. Cependant, il existe certains cas où l'utilisation d'opérateurs différents de +, -, SUM et AVERAGE offre des résultats arrondis fort éloignés du résultat du calcul non arrondi. Prenons l'exemple suivant :

Dans ce cas, le calcul précis de la cellule C1 serait 8.7×1.7=14.79. Étant donné que les cellules A1 et B1 sont reliées par une multiplication, think-cell round ne peut intégrer les formules de ces cellules à un problème commun. Après constat que la cellule A1 est valide en tant que données d'entrée, la cellule B1 est évaluée indépendamment et le résultat est repris sous forme de constante dans le reste du problème. Puisqu'il n'existe aucune autre contrainte, la valeur 1,7 de la cellule B1 est arrondie au nombre entier le plus proche, à savoir 2.

À ce stade, le calcul « précis » de la cellule C1 est 8.7×2=17.4. C'est là le problème que think-cell round essaie de résoudre. Il existe une solution cohérente qui consiste à arrondir 17,4 à 18. Le résultat prend alors la forme suivante :

Veuillez noter que la valeur arrondie de la cellule C1, à savoir 18, est bien différente de la valeur d’origine de 14,79.

22.3 Résolution des problèmes de formules TCROUND

Vous pouvez rencontrer deux valeurs d'erreur en utilisant think-cell round : #VALUE! et #NUM!.

22.3.1 #VALUE!

L'erreur #VALUE! signale des problèmes syntaxiques, tels que des formules incorrectement saisies ou de mauvais paramètres. Par ailleurs, veillez à utiliser les bons séparateurs : Par exemple, alors que dans la version internationale d'Excel la formule ressemble à cela : =TCROUND(1.7, 0), elle doit être écrite de la manière suivante dans la version allemande d'Excel =TCROUND(1,7; 0).

Une autre erreur spécifique à think-cell round est le placement de l'appel de fonction TCROUND : vous ne pouvez pas utiliser une fonction TCROUND au sein d'une autre formule. Assurez-vous que TCROUND est la fonction la plus éloignée de la formule de la cellule (voir Placement des fonctions TCROUND).

22.3.2 #NUM!

L'erreur #NUM! résulte de problèmes numériques. Lorsque le résultat d'une fonction TCROUND est #NUM!, le problème tel qu'il est énoncé par l'ensemble de formules est mathématiquement insoluble (voir Limites de think-cell round).

Tant que les formules inclues dans les fonctions TCROUND contiennent seulement +, - et SUM, et que toutes les autres instructions TCROUND partagent la même précision (second paramètre), il est certain qu'une solution existe et sera trouvée par think-cell round. Toutefois, dans les cas suivants, l'existence d'une solution d'arrondi systématique n'est pas garantie :

• Les formules impliquent des autres opérations telles la multiplication ou des fonctions numériques. Par ailleurs, une somme où le même addendum apparaît plusieurs fois est mathématiquement équivalente à une multiplication.
• Vous utilisez des précisions différentes dans le second paramètre de la fonction TCROUND.
• Vous utilisez fréquemment les fonctions spécifiques TCROUNDDOWN, TCROUNDUP et TCROUNDNEAR.

Vous pouvez essayer de reformuler le problème pour obtenir une solution cohérente. Essayez ce qui suit :

• Utilisez une plus grande précision pour certaines instruction TCROUND (ou pour toutes).
• N'utilisez pas TCROUND avec des multiplications ou des fonctions numériques autres que +, - et SUM.
• Utilisez une précision similaire (second paramètre) pour toutes les affirmations TCROUND
• Utilisez si possible TCROUND à la place des fonctions TCROUNDDOWN, TCROUNDUP et TCROUNDNEAR, qui sont plus spécifiques.

Vous n’êtes pas encore utilisateur(trice) ?