Microsoft 365 Problème de calcul

[kikou017]

Bonjour,

Bonne année à tous
Je viens vers vous car en aidant ma fille sur un devoir de math de 3ème, je tombe sur un problème que je ne sais pas résoudre....(et ce n'est pas un problème de math).
L'exercice est on ne peut plus simple, il faut créer un tableau avec 4 chiffres consécutifs dans 4 colonnes (A->D). En colonne E, il faut faire la soustraction du produit des colonnes 2&3 avec le produit des colonnes 1&4 (pour ligne 1 , c'est égal à (A2*A3)-(A1*A4).)

Il faut démontrer que le résultat est égal à 2 sur toutes les lignes.
Il est ensuite demandé de le faire avec un nombre très grand (ordre du milliard) et là, bizarrement, le résultat est égal à 0 ??
En cherchant pourquoi, il apparaît qu'à partir de 47 453 131, le résultat devient "0".
Mieux, j'ai refait le calcul en le décomposant (une fois en calcul automatique et 2 fois en rentrant manuellement les chiffres) et pour la même opération, j'obtiens 3 résultats différents ??
Je vous joins le fichier car je suis à court d'idée pour expliquer ça. (les calculs sont en colonnes G à I, sur les premières colonnes, j'ai tiré mes formules pour voir à partir d'où cela ne fonctionne plus)
Merci pour votre aide

PS : Je venais d'expliquer à ma fille l'intérêt de mettre en équation pour résoudre tout type de problème, j'ai l'air malin avec mon équation qui ne marche pas !!

 

[mapomme]

Bonsoir,

A partir d'une certaine taille de nombre, Excel passe en représentation (mantisse + puissance de la base).
En interne , c'est la base deux, en externe on peut parler en base 10.
Le passage de la représentation d'un grand entier en notation (mantisse / puissance de la base) se fait forcément avec perte de précision (c'est à partir d'environ 15 chiffres significatifs qu'on change de représentation pour un entier)

Dans une cellule, saisissez le nombre :9 999 999 999 999 999 (sans les espaces)

• vous constatez qu'Excel affiche 1 E+16 qui est différent du nombre saisi
• vous pouvez constater aussi que dans la barre de formule, le nombre saisi s'est transformé en 9 999 999 999 999 990 (sans les espaces)

Quand vous multipliez deux grands nombres, le résultat est encore plus grand et il y a perte de chiffres significatifs. Dans votre cas, la perte de chiffres significatifs des deux produits B*C et A*D aboutit à deux nombres identiques. La différence est nulle.

Cet exercice a sans doute été fait pour permettre aux élèves de voir que qu'il ne faut pas se fier aveuglement à un tableur sans savoir comment il fonctionne en interne. Il faut toujours rester critique quand au résultat d'un programme ou logiciel. Imaginons que vous réalisiez la conception d'un viaduc. C'est le genre d'erreur qui peut vous conduire à la catastrophe (et en prison).

Il existe d'autres cas de figure aboutissant à des résultats étranges.

Rien ne vaut dans ce cas le calcul mathématique :

(n+1)*(n+2)-n*(n+3) =

n^2+3n+2-n^2-3n =

2 (après avoir retiré les opposés dont la somme s'annule)

Et ceci est donc vrai quelque soit le nombre (entier, positif, réel et même complexe)

Il existe des programme qui permettent de faire des calculs de très grand nombres.

Edit : bonjour @Dranreb , @sylvanu

 

[Dranreb]

Bonsoir et bonne année.
Pour la démonstration j'ai trouvé ça, en appelant 'a' le 1er nombre :

(a+1) * (a+2) - a*(a+3)

= a*(a+2) + (a+2) - a*a - a*3

= a² + a*2 + a + 2 - a² - a*3

= a*2 + a + 2 - a*3

= a*3 + 2 - a*3

= 2

Pour les calculs en Excel c'est cuit: le plus grand nombre entier impair conservé avec exactitude est 9007199254740991 (2 ^ 53 - 1). Alors avec des produits de nombres à partir de 94906265 ça devient chaud !
En VBA ça devrait pouvoir s'écrire en utilisant la fonction CDec.

 

[sylvanu]

Bonsoir Kikou,
Je pense que vous atteignez la limite d'XL en précision.
La mantisse ne peut avoir que 15 chiffres au maximum.
Mais 48312345677*48312345680 dépasse ces 15 chiffres significatifs donc XL arrondit à
2 334 082 744 958 880 000 000, et évidemment idem pour 48312345678*48312345679 donc la différence vaut 0.
Le nombre maximal qui donne à N(N+1) sur 15 bits significatif est 47 453 132 ce qui donne
2 251 799 784 062 550.

Numeric precision in Microsoft Excel - Wikipedia

fr.qaz.wiki

 

[mapomme]

Re,

Autre curiosité :

• saisir dans une cellule =1*(0,5-0,4-0,1)
• le résultat affiché dans la cellule est -2,77556E-17

-2,77556E-17 certes est très petit mais pas nul.

 

[kikou017]

Bonsoir à tous,
Merci pour vos réponses précises.
Je me doutais que c'était lié au nombre de chiffres. Par contre, ça n'explique pas pourquoi quand je fais 3 fois le même calcul, j'obtiens 3 résultats différents (10 ; 6 et 0)
Nous avions fait le calcul arithmétique qui conduisait au résultat de 2 quels que soient les chiffres utilisés, c'est d'ailleurs ce qui m'a surpris quand j'ai voulu montrer que ça marchait avec n'importe quel chiffre et que ce n'était pas le cas avec Excel. D'ailleurs, avec une calculatrice collège, même problème, j'obtiens un résultat de 0. C'est tout de même un peu inquiétant de se dire qu'un tableur ou une calculatrice donnent des résultats faux pour des opérations simples au demeurant...
Bonne soirée,

 

[mapomme]

C'est tout de même un peu inquiétant de se dire qu'un tableur ou une calculatrice donnent des résultats faux pour des opérations simples au demeurant...

Non ce n'est pas inquiétant. C'est comme cela que ça se passe.
Voyez l'exemple du message #5 (et ça ne concerne pas les grand nombres).
Vous ne pouvez pas représenter n'importe quel nombre avec un nombre fini de registre et une mémoire de taille finie que ce soit en 32 ou 64 bit. Il faut trouver des compromis et en être conscient.

De même des nombres avec virgule peuvent être "fini" en base 10 et avoir une représentation infinie en base 2 (qui reste la base utilisée en informatique classique).

Comment montrer qu'un programme ne peut pas comporter d'erreur ?
On ne le peut pas si ce n'est tester toutes les situations de départ possibles ce qui est dans la grande majorité des cas est irréaliste et impossible.

 

[kikou017]

Non ce n'est pas inquiétant. C'est comme cela que ça se passe.
Voyez l'exemple du message #5 (et ça ne concerne pas les grand nombres).
Vous ne pouvez pas représenter n'importe quel nombre avec un nombre fini de registre et une mémoire de taille finie que ce soit en 32 ou 64 bit. Il faut trouver des compromis et en être conscient.

De même des nombres avec virgule peuvent être "fini" en base 10 et avoir une représentation infinie en base 2 (qui reste la base utilisée en informatique classique).

Certes, l'exemple du message 5 est assez édifiant.
Ce que je trouve inquiétant est que lorsque l'on fait un calcul sur une machine quelle qu'elle soit, on ne se dit pas de prime abord que le résultat peut être faux. Ca fait réfléchir.
C'est comme si on faisait 1+1 et que le résultat n'était pas 2.
Mais c'est bon à savoir

 

[mapomme]

Mais c'est bon à savoir

Je ne sais pas si cela peut vous rassurer mais même en étant au courant, je me fais encore piégé.
Par exemple, en VBA, je compare certains calculs à zéro alors que je devrais comparer la valeur absolue des résultats non pas à zéro mais si inférieur ou non à 1 E-10 (par exemple) pour conclure à la nullité ou non des résultats.
Ce n'est pas intuitif.

 

[mapomme]

Par contre, ça n'explique pas pourquoi quand je fais 3 fois le même calcul, j'obtiens 3 résultats différents (10 ; 6 et 0)

C'est aussi un phénomène connu. Je ne me rappelle plus de l'explication .

 

[mapomme]

Re,

Souvenir de vieux schnoque:
Sans doute un des rares à avoir utiliser la règle de calcul. Et avec cet instrument, il fallait faire d'abord un calcul approximatif d'ordre de grandeur du résultat pour déterminer le résultat. La règle de calcul donnait quelques chiffres significatifs sans préciser la puissance de 10. C’était fastidieux mais intéressant aussi et faisait toucher du doigt les notions de chiffres significatifs, d'ordre de grandeur et d'erreur de calcul. Mais on ne pouvait s'en prendre qu'à nous même en cas de mauvais résultat de calcul et pas rejeter l'erreur sur un tableur externe.

 

[soan]

Bonjour mapomme, le fil,

c'est pas gentil de me traiter de vieux schnoque, LOLLLLLLLLL ! moi aussi,
j'avais dû utiliser une de ces règles à calcul antédiluviennes, avec une petite
barrette coulissante au milieu !

c'est marrant, tu m'rappelles que des fois, j'devais faire des interpolations avec des
tables de logarithmes, et aussi avec des tables d'intérêts simples ou composés,
selon le taux d'intérêt annuel (y'avait aussi des tables d'annuités).

quelques années avant, j'avais eu mon tout premier PC ; c'était alors de la très bonne
qualité : il ne tombait jamais en panne, ni bugs, ni virus, ni mises à jour, pas même
une prise de courant !

Spoiler: cliquer ici

soan