Excel dare un formato variabile al numero dei decimali

[fantina]

Mi interessa vedere tutti e cinque i decimali solo se non sono zero.
Questi numeri sono il risultato di formule semplici (ad esempio il costo di un cartone con dentro 50 pezzi= prezzo unitario) ma in taluni casi il prezzo di partenza è talmente basso che mi serve vedere il risultato per esteso, nello stesso tempo voglio evitare di visualizzare la sfilza di zeri che possono uscire impostando lo stesso formato su tutto. C'è modo? Grazie

[Anthony47]

Oggi sei stata di turno?

Immagino che vuoi lavorare sulla formattazione, senza alterare il valore nella cella; lo puoi fare ad esempio con una macro di Worksheet_Change come questa:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub   '<<< Check area
StrDec = "0"
Decs = 4 - Int(Log(Target.Value +0.1) / Log(10)): If Decs < 0 Then Decs = 0
If Decs > 0 Then StrDec = StrDec & "." & String(Decs, "0")
Target.NumberFormat = StrDec
End Sub

Questa lavora sulla colonna A; se devi farlo su un range diverso modifica l' istruzione marcata <<<.

Ciao, fai sapere.

[fantina]

Ciao Anthony, niente turni, ci si porta un po' avanti con il lavoro
Ho messo il codice (cambiando solo la colonna A in E) ma mi esce un errore (runtime 13 ???) e si evidenzia la stringa
Decs = 4 - Int(Log(Target.Value + 0.1) / Log(10))
il risultato non viene
Però la notifica dell'errore esce quando taglio una riga per spostarla in un'altra posizione...

[Anthony47]

Se intendi che funziona eccetto quando selezioni piu' di una cella, allora modifica in questa:

Codice: Seleziona tutto

If Intersect(Target, Range("A:A")) Is Nothing Or Selection.Count > 1 Then Exit Sub

Ciao.

[fantina]

Sistemato tutto Anthony! Ti informo che avevo anche omesso un piccolo particolare e cioè di cambiare il formato delle celle in questione da Numero a Generale...

Grazie!!

[fantina]

Anthony... scusa un'altra piccola cosa: siccome su una delle colonne incluse nel range inserisco dati manualmente (numeri) ad ogni inserimento la cella mi si modifica automaticamente in formato numero, quindi ogni tanto devo andare a ridare alla colonna il formato generale e tutto si sistema, nulla di grave ma magari c'è un modo... grazie mille

[Anthony47]

La macro applica una formattazione Numerica con numero di decimali variabile tutte le volte che la cella viene modificata, quindi non capisco la tua affermazione.
Anzi, rileggendo il primo post e realizzando che la cella da formattare contiene una formula, mi chiedo perche' la soluzione che ti ho proposto "funzionicchi"; nel senso che la macro non lavora se il cambiamento e' prodotto da una formula ma solo quando la modifichi da tastiera. Insomma dovrebbe funzionare solo nel caso di cui ti lamenti e non funzionare in tutti gli altri...
Mi vengono in mente due soluzioni alternative: o modifichiamo la formula in modo da arrotondare direttamente il risultato alla precisione voluta (NB: arrotondare, non formattare!), o inseriamo le istruzioni topiche della macro in un ciclo For each Cella in Range("E1:E100") / Next Cella, sempre attivate dal Worksheet_Change.

Ma prima di proporre attendo tuoi commenti a quanto detto prima.

ciao.

[fantina]

A parte fare la piccola variazione che mi hai mandato dopo, del codice ho modificato solo A:A in E:I
Nel mio foglio solo nelle colonne E e F inserisco dati manualmente (prezzo e sconto) G, H e I riportano nell'ordine una formula aritmetica per il prezzo scontato, un cerca verticale per un costo di trasporto in un altro file e infine una formula aritmetica per il prezzo definitivo.
Inizialmente si comportava rendendomi svariati zeri, quindi apparentemente all'opposto: pensando di avere omesso qualcosa ho cambiato il formato in Generale e ora non mi rende più gli zeri ma addirittura mi rende fino a sei decimali. Andando avanti a scrivere a mano in E e in F le celle digitate ripresentano lo stesso problema degli zeri, riformattando in Generale e si mettono a posto.
Tutto ciò accade in Excel 2000.... cosa ho combinato?

[fantina]

errata corrige:
"Andando avanti a scrivere a mano in E e in F, le celle digitate ripresentano lo stesso problema degli zeri, riformattando in Generale si mettono a posto"
scusa

[fantina]

No, l'arrotondamento dei prezzi unitari mi potrebbe portare a differenze di prezzo nella confezione, alcuni cartoni contengono moltissime unità. Grazie

[Anthony47]

In generale, non dare per scontato che una descrizione verbale basta; ad esempio se dici "Inizialmente si comportava rendendomi svariati zeri" e poi "ora non mi rende più gli zeri ma addirittura mi rende fino a sei decimali" io non capisco cosa sono quegli "zeri" (quanti e dove?) e in quali casi ti rende 6 decimali (la macro e' pensata per dare max 5 cifre significative, quindi max 5 decimali) e che cosa ti rende normalmente. Se invece scrivi (ad esempio) digito "questo numero" in XXyy e mi visualizza "questo valore" sarebbe piu' a prova di interpretazione.

Comunque, ripeto che la macro attuale e' pensata per rendere 5 cifre significative nella cella in cui digiti un valore (se all' interno del range validato con la prima istruzione (If Intersect ...); es digiti 22 e ti visualizza 22,000; digiti 123,45678901 e ti visualizza 123,46; solo nella cella oggetto di input manuale.
CONFERMAMI almeno che questo lo fa, anche se e' chiaro che non e' quello che vuoi ottenere.

Adesso cerchiamo di concordare quello che vuoi ottenere; e' corretto dire che ogni volta che introduci o modifichi un valore in col E o F, vuoi avere in col I della stessa riga l' impostazione di N cifre significative? Es, in col I viene calcolato 1,23456789 ma viene visualizzato 1,2346; oppure calcolato 1234,5678 e viene visualizzato 1234,6 (N=5 cifre significative).

Ciao.

[fantina]

Comunque, ripeto che la macro attuale e' pensata per rendere 5 cifre significative nella cella in cui digiti un valore (se all' interno del range validato con la prima istruzione (If Intersect ...); es digiti 22 e ti visualizza 22,000; digiti 123,45678901 e ti visualizza 123,46; solo nella cella oggetto di input manuale.
CONFERMAMI almeno che questo lo fa, anche se e' chiaro che non e' quello che vuoi ottenere.

Lo fa, te lo confermo

Adesso cerchiamo di concordare quello che vuoi ottenere; e' corretto dire che ogni volta che introduci o modifichi un valore in col E o F, vuoi avere in col I della stessa riga l' impostazione di N cifre significative? Es, in col I viene calcolato 1,23456789 ma viene visualizzato 1,2346; oppure calcolato 1234,5678 e viene visualizzato 1234,6 (N=5 cifre significative).

No, quello che vorrei è: digitando 0,5 oppure 12.1 ottenere 0,5 o 12,1 e non 0,5000 o 12,100, mentre digitando 0,54432 vorrei 0,54432
Inoltre vorrei che questo si avverasse anche in caso di risultato di formula.
Scusa, avevo scritto per un'ora per spiegarmi meglio e mi è cascata la linea....

[Anthony47]

avevo scritto per un'ora per spiegarmi meglio e mi è cascata la linea

E' successo anche a me prima, oggi siamo sfortunati...

quello che vorrei è: digitando 0,5 oppure 12.1 ottenere 0,5 o 12,1 e non 0,5000 o 12,100, mentre digitando 0,54432 vorrei 0,54432
Inoltre vorrei che questo si avverasse anche in caso di risultato di formula

Scusa, ma per fare questo basta formattare "Generale" senza nessuna macro: scrivo 0,25 e vedo 0,25; scrivo cavolo e vedo cavolo; scrivo 123,45678 e vedo 123,45678
A te invece, formattando Generale che cosa succede, usando sia i numeri che hai scritto tu poco fa che quelli che ho scritto io?
Ciao.

[fantina]

eccomi: con Formato Generale impostato ho scritto 0,25 e vedo 0,25; ho scritto 123,45678 e vedo 123,45678; ho scritto 0,5 e ho visto 0,5; per non lasciare nulla di intentato ho scritto cavolo e ho visto cavolo (.... scherzo...)
Ora facciamo le due ipotesi: io compro pelati rossi e pelati verdi in casse da 24 lattine quindi imposto una cella per il prezzo a cartone che inserisco manualmente, la pezzatura in una cella apposita e una cella con la formula che faccia la divisione per ottenere il prezzo a lattina; i pelati rossi costano 4.85 a cartone e i pelati verdi costano 4.80.
Con Formato Generale il prezzo unitario dei pelati rossi contiene nove decimali che non mi servono a nulla perchè a me ne bastano quattro. Allora decido di lavorare con la tua macro (che peraltro trovo molto utile) ma quando arrivo ai pelati verdi nel risultato della formula mi ritrovo 0,2000 e anche 4,8000 nel prezzo a cartone: ancora troppi decimali che non mi servono a nulla.
Certo potrei usare come dici l'arrotondamento ma è sicuro che mi renda i quattro decimali che voglio vedere? e il prezzo 0,2000 me lo fa diventare 0,20?

Scusa se ti faccio perdere tempo a filosofeggiare sui "formati intelligenti" ma sono anni che mi sto accecando con questi fogli....

[Anthony47]

Da quello che ci siamo detti, non ho dedotto una specifica regola per la gestione dei decimali; non ho nemmeno capito che uso ne fai della macro che ti ho dato (essendo una Worksheet_Change lavora sulla cella modificata a mano, non su formula); l'hai adattata con l' uso di Target.Offset(x,y).NumberFormat ?
Vado avanti con una mia idea: rappresentare un valore con un min numero di decimali (impostabile) a cui si somma un tot di decimali in grado di rappresentare almeno un decimale al di sotto della prima cifra significativa. Lo fa questa macro:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or _
     Selection.Count > 1 Or Target.Value <= 0 Then Exit Sub
Decs = 1            '<<< Numero min di decimali
StrDec = "0"
If Log(Target.Value) < 0 Then Decs = Decs - (Decs = 0) * 1 - Int(Log(Target.Value) / Log(10))
If Decs > 0 Then StrDec = StrDec & "." & String(Decs, "0")
Target.NumberFormat = StrDec
End Sub

Questa lavora sulla cella modificata di col A; modificare nella macro quel range("A:A") per altri intervalli.

Da quello che ho capito tu modifichi le colonne E ed F, e devi lavorare sul valore contenuto in col I; quindi potresti usare questa versione:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E, F:F, I:I")) Is Nothing Or _
     Selection.Count > 1 Then Exit Sub
TCol = "I"
CRow = Target.Row
If Not IsNumeric(Cells(CRow, TCol).Value) Then Exit Sub
If Cells(CRow, TCol).Value <= 0 Then Exit Sub
MDecs = 1            '<<< Numero min di decimali
MxDecs = 3           '<<< Numero Max di decimali
StrDec = "0"
Decs = Len(Cells(CRow, TCol).Value) - Len(Int(Cells(CRow, TCol).Value))
If Decs > MxDecs Then Decs = MxDecs
If Decs < MDecs Then Decs = MDecs
If Decs > 0 Then StrDec = StrDec & "." & String(Decs, "0")
Cells(CRow, TCol).NumberFormat = StrDec
End Sub

Ha un comportamento simile, ma in piu' consente di definire il numero min e max di decimali; all' interno di questo range la macro identifica poi il valore piu' appropriato; usa un' altra tecnica per elaborare il valore della cella, ma la sostanza non cambia.

Vedi se con una di queste versioni i tuoi occhi trovano sollievo.

Ciao.