Format de chiffre d'une cellule qui remonte

Salut tout le monde,

Question en lien avec mon petit jeu (https://forum.excel-pratique.com/viewtopic.php?f=3&t=116642, j'en profite pour faire un peu de pub ici pour avoir des avis en plus, il y a moins de monde dans la section Application)

Elle n'est pas optimisé mais j'utilise une fonction pour afficher les chiffres à l'écran avec un format "scientifique" en reprenant par exemple l'or que possède le joueur actuellement dans forme texte (sur l'onglet YYY et la forme s'appelle GOLD) qui est situé dans une cellule (cells(1,1) dans l'onglet XXX)

Par exemple pour mettre à jour je fais simplement un

Sheets(Y).Shapes("GOLD").TextFrame2.TextRange.Characters.Text = CHIFFRAGE(Sheets(XXX).cells(1,1).Value 

avec ma fonction de chiffrage qui pour les valeur de 0 à 999 l'affiche sans rien changer puis 1E3 pour 1000, 2.5E5 pour 250000, etc:

Function CHIFFRAGE(VALEUR As String) As String

If Len(VALEUR) > 3 Then
    RATIO = 10
    For i = 3 To Len(VALEUR)
        RATIO = RATIO * 10
    Next
    CHIFFRAGE = Left((VALEUR / RATIO), 3) & "E" & Len(VALEUR) - 1
Else
    CHIFFRAGE = VALEUR
End If

End Function

Je passe la valeur en STRING car après 1E10 excel formate automatiquement on dirait et justement VALEUR même en single vaut "1E11" littéralement donc son len(VALEUR) ... vaut 4! idem pour un len(10000) il vaut ... 4! béééééé

En passant en string je règle le soucis... enfin je croyais, par exemple avec une valeur d'or de 1110208383254750 excel affiche 1.11021E+15 dans la cellule et retourne un len(VALEUR) de ... 20

Alors que par exemple pour 100020005000 excel affiche1,0002E+11 dans la cellule et retourne un len (VALEUR) de 12 comme il faut !

Au final quand on compare des chiffres avec des opérateurs > ou < tout se passe bien j'ai l'impression donc je pourrais utiliser quelque chose du genre

Function CHIFFRAGE(VALEUR As String) As String
Dim CHIFFRE As Single

CHIFFRE = 1
Do Until CHIFFRE >= Cells(1, 1).Value
    CHIFFRE = CHIFFRE * 10
Loop

'etc
End Function

Je peux avoir exactement le nombre d’occurrence de ma boucle Do et donc avoir la puissance EXXX facilement ou juste faire un méga select case ah ah ah

Du moins je n'ai pas totalement testé mais à première cette solution fonctionnerait

Par contre je ne vois pas déjà pourquoi avec VALEUR en Single au dessus de 1E11 ou pour 10000 le len retourne 4, il faudrait utiliser des fonctions comme FORMAT ou du genre?

Et pourquoi len(100020005000) = 12 ce qui est normal mais len(1110208383254750) = 20 !

Cela avait l'air une fonction simpliste mais je galère, quelqu'un a déjà eu ce genre de soucis? ou c'est le Single plutôt qu'un Double ou du genre?

Ou ma macro qui est complètement foireuse?

Ci-joint un fichier avec les exemple et ci-dessous les résultats des tests

test img

Merci d'avance pour ceux qui regarderont ce souci ou m'expliqueront ce que je fais de mal ^^

ECG

11test-chiffrage.xlsm (17.41 Ko)

Bonjour,

à tester (Variant)

Function CHIFFRAGE_SINGLE(VALEUR As Variant) As String

Variant

Toute valeur numérique, avec la même plage de valeurs qu'une donnée de type Double

Bonjour,

Voici une fonction pour le format 'ingénieur' (puissances multiples de 3) :

Function FormatEng(Nombre As Variant, Optional nbDecimales As Long = 2) As String
    'source : http://www.generation-nt.com/reponses/nombre-format-ingenieur-entraide-3222151.html#17586851
    Dim Exposant As Long
    Dim Parts() As String
    Parts = Split(Format(Nombre, "0.0#############E+0"), "E")
    Exposant = 3 * Int(Parts(1) / 3)
    FormatEng = Format(Parts(0) * 10 ^ (Parts(1) - Exposant), "0." & String(nbDecimales, "0")) & "E" & Format(Exposant, "+0;-0")
End Function

eric

Bonjour,

Petite correction dans le code d'Eric car si on met l'argument Decimales à 0 on a une virgule qui traîne donc, j'ai juste corrigé avec IIf() IIf(nbDecimales > 0, ".", "") :

FormatEng = Format(Parts(0) * 10 ^ (Parts(1) - Exposant), "0" & IIf(nbDecimales > 0, ".", "") & String(nbDecimales, "0")) & "E" & Format(Exposant, "+0;-0")

Niquel Erric et Theze ça à l'air de marcher du tonnerre! exactement ce que je voulais faire merci vous gérez

Par contre pour le coup du LEN(X) qui remonte n'importe quoi je veux bien une explication si quelqu'un à une idée car la fonction len() est basique et s'il y a un piège à éviter ça peut être utile pour pas mal de monde je pense.

J'ai refais un test encore plus simple d'un simple MSGBOX du len de la cellule:

pb2

On voit bien que le len du 1.11E+15 doit faire 16 et pas 20, d’où cela provient?

ECG

Len te retourne la longueur du contenu de la cellule, indépendamment du format choisi.

Si tu veux la longueur de ce que tu vois utilise .Text et non .Value

Et ton C8 = 1.11020838325475E+15 ce qui fait bien 20 car. Tu as atteint la limite où il est obligé de le représenter en scientifique

eric

Je ne comprends pas tout désolé Eriic ^^

Ou apparait le "1.11020838325475E+15 " de la limite scientifique? mon excel affiche "1.11021E+15", un .Text me retourne bien 11 et si je comprends ton exemple vu que dans la cellule j'ai "1110208383254750" cela devrait me retourner 16 caractères un .valeur et pas 20 nan?

ou alors comment obtenir 16 qui est le nombre de caractère que la cellule contient? en formule directement dans excel un =NBCAR(A1) avec dans A1 donc mon "1110208383254750" cela vaut bien 16

Quand j'ai "100020005000" c'est cohérent:

- un .value qui me retourne 12

- un .text qui me retourne 10 et je vois bien "1.0002E+11" soit bien 10 caractère

Je suis perdu hmm

ECG

Tu mets un espion sur ton [C8].value et tu verras qu'en interne tu as bien 1.11020838325475E+15 soit 20 caractères

Tu mets 16 digits, la précision d'excel c'est 15. Après il est obligé de mettre en scientifique avec mantisse et exposant pour les grands nombres.

Tu as saisis 1110208383254750, saisis ailleurs 1110208383254759, tu verras que tu perds de la précision et que tu retrouves à nouveau 1110208383254750. Un digit de trop...

eric

ah la lumière arrive ! je vois ce que tu veux dire pour la précision! merci d'avoir pris le temps de m'expliquer

Par contre ce qui en découle me fait peur du coup (mes excuses par avance si cela va être dur de me suivre avec mes exemples):

Si je mets la valeur "100000000000000123" en A1 soit 18 digits et comme on l'a vu après 15 digits excel formate et n'a plus de précision du coup on voit bien "1.00E+17" ... on perd l'information du 123 si je comprends bien

Pour vérifier je test avec en A1 "100000000000000123" on voit bien 1.2E+15 et en A2 "100000000000000000" on voit bien 1.2E+15 la soustraction donne pas 123 mais 0; En gros la précision après les 15 digits par en fumée c'est ce que tu me disais; cela pose un énorme problème je trouve

J'ai poussé plus loin mon investigation j'ai mis en A1 "1200000000000120" on voit bien 1.2E+15 et je me suis amusé à soustraire 1 pour voir ce que cela donne.

Sur le papier, vu qu'avec15 digit excel ne garde rien en mémoire (sinon on aurait eu 123 en résultat de la soustraction au dessus) cela ne devrait rien faire donc

Et bin au bout de 5 fois le chiffre passe à "1200000000000110"! puis après toute les 10 fois qu'on retire 10 soit on passe à "1200000000000100" puis "1200000000000090" etc

En analysant ce résultat, on dirait donc qu'excel garde en mémoire une valeur sur les derniers digit même en affichant que des 0 ou sinon en retirant 1 sur le dernier digit cela ne devrait rien faire.... mais du coup s'il garde en mémoire quelque chose quand même dans le test juste avant on aurait du retrouver nos 123! ou un autre chiffres mais pas 0 !

Bon déjà pourquoi au bout de 5 fois on passe de 20 à 10 la première fois? je me dis que je me suis surement trompé en entrant le chiffre au début et que j'avais pas mis le "1200000000000120" donc j'ai refais un test mais si je mets "1200000000000129" ou "1200000000000124" excel transforme tout en "1200000000000120" et au bout de 5 fois pour n'importe quel chiffre que j'entre et qu'on retire 1 passe à "1200000000000110".

J'en conclue donc que c'est excel qui formate automatiquement le format (ici à "1200000000000124" je dirais vu qu'au bout de 5 fois qu'on retire 1 on voit le dernier digit passer de 2 à 1) mais cela reste très bizarre, bon après c'est bien tout les 10 fois qu'on retire 10 au chiffres (ce qui parait normal 10 fois -1 on a bien -10) ... mais cela reste bizarre de soustraire 5 pour perdre une dizaine pour 10 après

Et pour pousser le vice encore plus loin j'ai donc mis "1200000000000120" puis j'ai soustrait 5, cela passe à "1200000000000110" puis je soustrait 7 (on reste donc à "1200000000000110" mais si je retire encore 3 on va bien passer à "1200000000000100") et que je lui soustrait "1200000000000100"... je vois s'afficher 8

De la même façon si je met "12000000000000100" soit "1.2E+16", je peux retirer 10 000 fois 1 via une boucle, le résultat ne change jamais alors que si je retire 10 donc au bout de 5 fois je vois passer à "12000000000000000"

En conclusion Excel à une précision de 15 digits mais le 16 digits en prix en compte même si Excel affiche un 0

En pratique si j'ai une compétence à prix fixe qui vaut 1 or si le joueur accumule au minimum 1E+16 (ou tout autre ratio qui dépasse la limite d'excel de 15 digits) et bin il pourra l'activer à l'infini car excel n'arrive pas à soustraire ?

Et cela vaut même dans n'importe quel classeur excel ; par exemple si je veux calculer le nombre de frite servi sur les 50 dernières années dans mes 3000 magasins dans le monde (pour calculer la perte que j'ai eu par rapport au nombre de frite normalement fournies, en effet les employés ont tendance à ne pas respecter les consignes et mettre 12 frites à la place des 10 de prévus dans le process) et qui se compte de la façon suivante :

95044758752811247852745757555887596847 (9.5E+37 dans excel)

alors que sur le process avec 10 frites par commande au total j'aurais du en servir :

95044758752811247852740000000000000000 ((9.5E+37 dans excel aussi)

quand je soustrait les deux cela me donne 0 et pas 5757555887596847 (soit 5.75E37 frites) en sachant que cela me coute 0.05 centimes la frites industrielles ça faire une pair de fric que je perd ! et pas 0 comme dirait excel

Comment on fait pour utiliser des chiffres très grand? je veux dire il y a surement des physiciens qui calcule des distances énormes ou des autres, comment ils font avec excel?

excel n'est pas prévus pour ça ou j'utilise mal tout cela? l’intérêt d'utiliser un Double qui va juste E308 est utilisé juste pour garder en mémoire les 15 premiers digit donc?

ECG

En conclusion Excel à une précision de 15 digits mais le 16 digits en prix en compte même si Excel affiche un 0

Plages de valeurs d'un double :

-1.79769313486231E308 à -4,94065645841247E-324 pour les valeurs négatives ; 4,94065645841247E-324 à 1,79769313486232E308 pour les valeurs positives

Tu vois que c'est même plutôt 14,5 digits si tu arrives aux extrémités.

Même si tu penses voir une précision cachée au 16e digit tu ne peux pas considérer ça comme fiable.

Voir ton essai avec -1. Il faut tenir compte du facteur d'échelle dans la représentation des flottants. A partir d'une certaine grandeur de nombre, ton 1 est perdu. Tout comme ton 10 le sera lorsque tu aborderas des nombres tels que 1E+18

Un article sur la représentation en interne des décimaux flottants qui pourrait t'intéresser : https://fr.wikipedia.org/wiki/IEEE_754

En vba tu aussi le type Decimal avec une mantisse de 28 chiffres ce qui étend la précision, mais sans les très grands nombres puisqu'il n'y a plus d'exposant pour la puissance de 10

+/-79 228 162 514 264 337 593 543 950 335 pour les nombres sans décimales. La plage de valeurs des nombres à 28 décimales est +/-7,9228162514264337593543950335. Le plus petit nombre différent de zéro est 0,0000000000000000000000000001

Tu ne peux pas les typer directement, il faut faire un CDec(ta_valeur_ou_ta_variable)

Ici tu trouveras une série de fonctions personnalisées pour traiter les grands nombres : http://fordom.free.fr/

Comment on fait pour utiliser des chiffres très grand? je veux dire il y a surement des physiciens qui calcule des distances énormes ou des autres, comment ils font avec excel?

excel n'est pas prévus pour ça ou j'utilise mal tout cela? l’intérêt d'utiliser un Double qui va juste E308 est utilisé juste pour garder en mémoire les 15 premiers digit donc?

Ne mélange pas grands nombres et précision. 1E308 est un très grand nombre qui dépasse l'imagination, accessible à excel. Mais dans excel tu ne l'auras qu'avec une précision de 15 chiffres ce qui suffit largement le plus souvent.

Pour la distance soleil-proxima du centaure tu te moques d'avoir 15 chiffres, 2-3 chiffres significatifs sont suffisants.

Mais si tu as besoin d'une grande précision pour y envoyer une fusée et bien oui, tu ne feras pas tes calculs sur excel

"A quoi ça sert la frite si t'as pas les moules", Bashung

eric

Mince pour mon jeu c'est une mauvaise nouvelle tout ça ... enfin ça peut rester accepter qu'au delà de 15 digit on ne soit plus précis faut avouer car en réfléchissant bien sur les mécanismes du jeu y a moyen d'éviter des failles surement et que finalement de soustraire 3000 or sur les 3.2E+88 que l'on a en stock n'a aucun impact dans ce cas la.

J'avais une autre question de pure mathématique pour acheter en xMax une compétence du genre:

j'ai 5.E47 or en stock et la compétence est du type cout level N+1 = 1*Level + 20*Level; combien de level d'un coup on peut acheter si actuellement elle est au niveau 5

Je n'ai pas encore trouvé en terme de mathématique comment le faire mais cela passe par des divisions ou le reste j'imagine et cette histoire de 15 digits va surement y mettre la pagaille

Sympa le site que tu as mis en liens avec des fonctions particulières (comme AGN:P), je vais y regarder de plus près

En tout cas merci pour ta patience et ton expertise Eriic! tu es un chef

ECG

Je ne comprend pas bien cout level N+1 = 1*Level + 20*Level, tu as dû mal expliquer

Pourquoi pas = 21*level ?

eric

Pardon oui plutôt du genre

cout level = 1+ 5*(level-1) 

Je remets ici ce que j'ai commencé à faire, il y peut être des erreurs, s'il y a des matheux je viens bien une correction si mon raisonnement est complétement faux ^^

Donc le Level 1 coute 1 puis le Level 2 coute 6 etc ça augmente de 5 quoi par niveau

cumul exemple

du coup comme souvent dans ce genre de jeu il y a des boutons pour acheter en x1; en x10; en x100; voir x1000 et donc xMAX pour éviter de cliquer 3000 fois si on peut

donc pour passer du level 0 au lvl 10 d'un coup ça donne

 cout cumulé = (1+5*0)+(1+5*1) +...(1+5*9)
 soit cout cumulé = 10*1+ ((0+1+2+....+9)*5)
 Soit mathématiquement comme (1+2...+N)  = N(N+1)/2 on a la formule 
 Cout level N = N*1 + ((((N-1)((N-1)+1))/2)*5) =  N*1 + (((N-1)N)/2)*5)
 

C'est donc pour calculer du lvl 0 jusque 10 mais une fois qu'on aura acheté par exemple 5 level, le cout du bouton x10 ne vaudra pas le cout du level 15 ( level actuel +10 ) mais bien sur le cout cumulé du lvl 15- cout cumulé du lvl 5 (car on l'a déjà payé)

Ça donne donc

Cout du lvl 5 à 15 = cout cumulé du lvl 15 - cout cumulé de 5 =  540-55  = 485 (vf tableau)
Soit par formule simplement pour le bouton X10 :
Cout du bouton x10 (soit le cout du level actuel N +10) = cout cumulé du lvl N+10 - cout du level N  = [ (N+10)*1 + (((N+10-1)(N+10))/2)*5)]       - [ N*1 + (((N-1)N)/2)*5)]
(

Pour les boutons x100 et x1000 c'est un changement de variable +10=>+100 => +1000 dans les formules et voila

Par contre pour mettre en place un bouton xMAX... si le joueur à 420 or et que le skill est au niveau 0 on pourra donc monter au niveau 13 qui coute 403 en cumul, etc , etc

Il faut donc trouver le N de l'équation mais la je sèche

Après vu qu'au final ma formule fait juste un cout de +5 entre chaque niveau je suis peut être partis dans des calculs compliqué pour rien du tout !

Bon après je pourrais changer mes calculs de cout plus simplement du genre

cout de base = 10 puis chaque level cout 1.2 fois le montant du niveau précédent

Et avec des LN ou des exponentiels c'est encore autre chose à calculer oh la la la

ECG

Rechercher des sujets similaires à "format chiffre qui remonte"