Somma in vba di formati diversi
osval...@gmail.com
Ho una ventina di colonne composte da 500 righe nelle quali si
alternano dati in diversi formati vi faccio un esempio di una colonna
A1
03/10/07
EURO 150
04/11/07
EURO 380
15/05/07
EURO 85
e cosi' via, ora, con la funzione sottostante faccio alla grande la
somma degli EURO
Public Function SommaEuro(Rng As Range) As Double
Dim cella As Range
Dim s As Double
s = 0
For Each cella In Rng
With cella
If IsNumeric(.Value) Then
s = s + (.Value)
End If
End With
Next
SommaEuro = s
End Function
infatti le date non influiscono sulla somma.
Ma supponiamo che debba sommare valori numerici ma con formato
diverso, per esempio
mm 140
EURO 150
mm 300
EURO 380
mm 58
EURO 85
dove tutti i formati mm sono formati personalizzati, e i formati EURO
cono formati contabilita',
come faccio a dire al vba fammi la somma dei differenti formati,
ovvero la somma personalizzata del formato che io voglio.
Grazie
Maurizio Borrelli
news:d4f97edb-3b82-41b5...@v4g2000hsf.googlegroups.com:
Ciao osvaldaccio.
Anche se e' cosa che concettualmente mi ripugna...
Option Explicit
Public Function GetNumberFormat(ByVal rng As Excel.Range) As Variant
Application.Volatile True
GetNumberFormat = rng.NumberFormat
End Function
Public Function SumIfFormat(ByVal rng As Excel.Range _
, ByVal format As Variant) As Double
Dim rngItem As Excel.Range
Dim dblSum As Double
Application.Volatile True
For Each rngItem In rng
With rngItem
If .NumberFormat = format Then
dblSum = dblSum + .Value
End If
End With
Next
SumIfFormat = dblSum
End Function
Da usare, nel caso dei tuoi dati di esempio qui sopra, cosi':
=SumIfFormat(A1:A6;GetNumberFormat(A2))
Attenzione pero' al ricalcolo se cambi formato!
--
(Facci sapere se e eventualmente come hai risolto. Grazie.)
Maurizio Borrelli [MVP]
http://www.riolab.org
osval...@gmail.com
wrote:
> "osvaldac...@gmail.com" wrote in message
>
> news:d4f97edb-3b82-41b5...@v4g2000hsf.googlegroups.com:
>
>
>
>
>
> > Ciao a tutti soprattutto ai maghi delvbaNorman Tiziano ecc..
> > alternano dati in diversiformativi faccio un esempiodiuna colonna
> > A1
> > 03/10/07
> > EURO 150
> > 04/11/07
> > EURO 380
> > 15/05/07
> > EURO 85
> > e cosi' via, ora, con la funzione sottostante faccio alla grande la
> > Public Function SommaEuro(Rng As Range) As Double
> > Dim cella As Range
> > Dim s As Double
> > s = 0
> > For Each cella In Rng
> > With cella
> > If IsNumeric(.Value) Then
> > s = s + (.Value)
> > End If
> > End With
> > Next
> > SommaEuro = s
> > End Function
> > infatti le date non influiscono sullasomma.
> > Ma supponiamo che debba sommare valori numerici ma con formato
> > diverso, per esempio
> > mm 140
> > EURO 150
> > mm 300
> > EURO 380
> > mm 58
> > EURO 85
> > dove tutti iformatimm sonoformatipersonalizzati, e iformatiEURO
> > come faccio a dire alvbafammi lasommadei differentiformati,
>
> Ciao osvaldaccio.
>
> Anche se e' cosa che concettualmente mi ripugna...
>
> Option Explicit
>
> Public Function GetNumberFormat(ByVal rng As Excel.Range) As Variant
> Application.Volatile True
> GetNumberFormat = rng.NumberFormat
> End Function
>
> Public Function SumIfFormat(ByVal rng As Excel.Range _
> , ByVal format As Variant) As Double
> Dim rngItem As Excel.Range
> Dim dblSum As Double
> Application.Volatile True
> For Each rngItem In rng
> With rngItem
> If .NumberFormat = format Then
> dblSum = dblSum + .Value
> End If
> End With
> Next
> SumIfFormat = dblSum
> End Function
>
>
> =SumIfFormat(A1:A6;GetNumberFormat(A2))
>
> Attenzione pero' al ricalcolo se cambi formato!
>
> --
> (Facci sapere se e eventualmente come hai risolto. Grazie.)
>
>
> - Mostra testo tra virgolette -
Caro Maurizio, funziona la somma per quanto riguarda il formato "mm"
perche' nelle celle c'e' un numero puro, per quanto riguarda le celle
formattate in contabilita' come EURO, essendoci in esse un numero frutto
di questa formula
=SE(VAL.ERRORE(SE(I14="";"";$I$9/1000*I14));"";SE(I14="";"";$I
$9/1000*I14))
il risultato e' #VALORE!, anche se nelle funzione ho attribuito la
cella del formato esatto,
probabilmente sono io che sono un cane, vuoi avere la pazienza di
dirmi dove sbaglio?
Grazie Osvalcaccio
Maurizio Borrelli
news:6f4d4b19-ef32-41de...@e10g2000prf.googlegroups.com:
> On 6 Gen, 12:16, "Maurizio Borrelli" wrote:
> > "osvaldac...@gmail.com" wrote in message
> > news:d4f97edb-3b82-41b5...@v4g2000hsf.googlegroups.com:
[...]
> > Anche se e' cosa che concettualmente mi ripugna...
> > Option Explicit
> > Public Function GetNumberFormat(ByVal rng As Excel.Range) As Variant
> > Application.Volatile True
> > GetNumberFormat = rng.NumberFormat
> > End Function
> > Public Function SumIfFormat(ByVal rng As Excel.Range _
> > , ByVal format As Variant) As Double
> > Dim rngItem As Excel.Range
> > Dim dblSum As Double
> > Application.Volatile True
> > For Each rngItem In rng
> > With rngItem
> > If .NumberFormat = format Then
> > dblSum = dblSum + .Value
> > End If
> > End With
> > Next
> > SumIfFormat = dblSum
> > End Function
> > Da usare, nel caso dei tuoi datidiesempio qui sopra, cosi':
> > =SumIfFormat(A1:A6;GetNumberFormat(A2))
> > Attenzione pero' al ricalcolo se cambi formato!
> funziona la somma per quanto riguarda il formato "mm"
> perche' nelle celle c'e' un numero puro, per quanto riguarda le celle
> formattate in contabilita' come EURO, essendoci in esse un numero frutto
> di questa formula
> =SE(VAL.ERRORE(SE(I14="";"";$I$9/1000*I14));"";SE(I14="";"";$I
> $9/1000*I14))
> il risultato e' #VALORE!, anche se nelle funzione ho attribuito la
> cella del formato esatto,
> probabilmente sono io che sono un cane, vuoi avere la pazienza di
> dirmi dove sbaglio?
Ciao Osvalcaccio.
Piano a darti del cane! Io ne ho due e potrebbero offendersi. ;-)
Fa' prima di tutto una prova con poche celle. Cancella il loro formato e
immetti nuovamente la formula. Funziona? Se si', seleziona le poche
celle di prima e assegna loro il formato "Contabilitą". Immetti
nuovamente la formula. Come va?
Maurizio Borrelli
news:#GVazMIU...@TK2MSFTNGP05.phx.gbl:
> "osvaldaccio..." wrote in message
> news:6f4d4b19-ef32-41de...@e10g2000prf.googlegroups.com:
> > On 6 Gen, 12:16, "Maurizio Borrelli" wrote:
> > > "osvaldac...@gmail.com" wrote in message
> > > news:d4f97edb-3b82-41b5...@v4g2000hsf.googlegroups.com:
> > funziona la somma per quanto riguarda il formato "mm"
> > perche' nelle celle c'e' un numero puro, per quanto riguarda le celle
> > formattate in contabilita' come EURO, essendoci in esse un numero
> frutto
> > di questa formula
> > =SE(VAL.ERRORE(SE(I14="";"";$I$9/1000*I14));"";SE(I14="";"";$I
> > $9/1000*I14))
> > il risultato e' #VALORE!, anche se nelle funzione ho attribuito la
> > cella del formato esatto,
> > probabilmente sono io che sono un cane, vuoi avere la pazienza di
> > dirmi dove sbaglio?
[...]
> Fa' prima di tutto una prova con poche celle. Cancella il loro formato e
> immetti nuovamente la formula. Funziona? Se si', seleziona le poche
> celle di prima e assegna loro il formato "Contabilità". Immetti
> nuovamente la formula. Come va?
Ciao Osvaldaccio.
Rileggendo, noto che ci hai mentito! ;-)
Non e' vero che tutte le celle da sommare contengono numeri. In base
alla formula che hai postato alcune potrebbero non avere un valore
numerico ma di testo, precisamente stringa nulla ("").
Devi quindi decidere se vuoi modificare la formula, sostituendola con
qualcosa del genere:
=SE(VAL.ERRORE(SE(I14="";0;$I$9/1000*I14));0;SE(I14="";0;$I
$9/1000*I14))
-oppure-
modificare la funzione perche' escluda i valori stringa nulla. Un modo:
Public Function SumIfFormat(ByVal rng As Excel.Range _
, ByVal format As Variant) As Double
Dim rngItem As Excel.Range
Dim dblSum As Double
Application.Volatile True
For Each rngItem In rng
With rngItem
If .NumberFormat = format Then
If Len(.Value) Then
dblSum = dblSum + .Value
End If
End If
End With
Next
SumIfFormat = dblSum
End Function
--
Pippo
"Maurizio Borrelli" <maurizio...@freepass.it> ha scritto nel messaggio
news:OaUsZYIU...@TK2MSFTNGP04.phx.gbl...
>> > funziona la somma per quanto riguarda il formato "mm"
>> > perche' nelle celle c'e' un numero puro, per quanto riguarda le celle
>> > formattate in contabilita' come EURO, essendoci in esse un numero
>> frutto
>> > di questa formula
>> > =SE(VAL.ERRORE(SE(I14="";"";$I$9/1000*I14));"";SE(I14="";"";$I
>> > $9/1000*I14))
>> > il risultato e' #VALORE!, anche se nelle funzione ho attribuito la
>> > cella del formato esatto,
>> > probabilmente sono io che sono un cane, vuoi avere la pazienza di
>> > dirmi dove sbaglio?
> [...]
>> Fa' prima di tutto una prova con poche celle. Cancella il loro formato e
>> immetti nuovamente la formula. Funziona? Se si', seleziona le poche
>> celle di prima e assegna loro il formato "Contabilità". Immetti
>> nuovamente la formula. Come va?
> Rileggendo, noto che ci hai mentito! ;-)
*Forse* poteva anche evitare di aprire un nuovo thread.. :-))
> Non e' vero che tutte le celle da sommare contengono numeri. In base alla
> formula che hai postato alcune potrebbero non avere un valore numerico ma
> di testo, precisamente stringa nulla ("").
> Devi quindi decidere se vuoi modificare la formula, sostituendola con
> qualcosa del genere:
> =SE(VAL.ERRORE(SE(I14="";0;$I$9/1000*I14));0;SE(I14="";0;$I
> $9/1000*I14))
[cut]
Nel thread http://tinyurl.com/3dbqpl (dove ha segnalato lo stesso tipo di
errore), ha spiegato di avere i dati disposti in modo alternato e, pertanto,
sono state proposte formule che eseguono la somma dei *soli* dati *numerici*
contenuti in celle con righe pari oppure con righe dispari.
Se la struttura dei dati e' ancora la suddetta, credo siano valide le
soluzioni suggerite in quel thread, senza ricorrere al Vba, eseguendo pero',
la modifica alla formula di Osvaldaccio, come hai indicato in questo thread.
Cosa ne pensi?
Puoi rispondere in quel thread! ;-)
PS:Non ho avuto ancora il tempo di studiarmi il codice del thread "Numero
progressivo in word" del ng "mpiow": appena possibile, inizio di nuovo a
*romperti* le scatole.... :-)))
Ciao e grazie milleeeeeeee
Pippo
Maurizio Borrelli
news:67B86020-0E62-4716...@microsoft.com:
> "Maurizio Borrelli" ha scritto nel messaggio
> news:OaUsZYIU...@TK2MSFTNGP04.phx.gbl...
Ciao Pippo.
[...]
> Nel thread http://tinyurl.com/3dbqpl
Spiacente, non l'ho ancora letto.
> (dove ha segnalato lo stesso tipo
> di errore), ha spiegato di avere i dati disposti in modo alternato e,
> pertanto, sono state proposte formule che eseguono la somma dei *soli* dati
> *numerici* contenuti in celle con righe pari oppure con righe dispari.
Andiamo bene!... All'orrore non c'e' mai fine. ;-)
> Se la struttura dei dati e' ancora la suddetta, credo siano valide le
> soluzioni suggerite in quel thread,
Lo leggero' ma ritengo improbabile che possa essere valida una soluzione
a un problema... invalido. :D
> una soluzione senza ricorrere al Vba, eseguendo pero',
> la modifica alla formula di Osvaldaccio, come hai indicato in questo
> thread.
> Cosa ne pensi?
Mi sono appena espresso. ;)
> Puoi rispondere in quel thread! ;-)
Appena lo leggero' cerchero' di farmi vivo IVI.
[...]
--
Pippo
"Maurizio Borrelli" <maurizio...@freepass.it> ha scritto nel messaggio
news:uD$hyOJUI...@TK2MSFTNGP02.phx.gbl...
> "Pippo" wrote in message
>> Nel thread http://tinyurl.com/3dbqpl
> Spiacente, non l'ho ancora letto.
>> (dove ha segnalato lo stesso tipo
>> di errore), ha spiegato di avere i dati disposti in modo alternato e,
>> pertanto, sono state proposte formule che eseguono la somma dei *soli*
>> dati
>> *numerici* contenuti in celle con righe pari oppure con righe dispari.
> Andiamo bene!... All'orrore non c'e' mai fine. ;-)
[ROTFL] :-)))
> Appena lo leggero' cerchero' di farmi vivo IVI.
OK!
Grazie milleeeeeee
Ciao
Pippo
Pippo
[cut]
'=================================================
Public Function SommaEuro(rng As Range) As Double
Dim cella As Range
Dim s As Double
s = 0
For Each cella In rng
With cella
If IsNumeric(.Value) Then
s = s + (.Value)
End If
End With
Next
SommaEuro = s
End Function
'================================================
Scusa Maurizio, ho notato che, *forse*, nel suddetto codice (scritto da me)
l'istruzione:
s = s + cella.value
all'aumentare del numero di celle da esaminare, sia la causa del
*rallentamento* notevole dei tempi di risposta della funzione stessa.
Usando le funzioni del foglio di lavoro tramite Vba
(Application.WorksheetFunction) sicuramente potrei ottenere una maggiore
velocita' di esecuzione, pero', nel caso in esame, non credo siano
applicabili...
Cosa ne pensi?
Esistono soluzioni alternative?
Ciao e grazie milleeeee
Pippo
osval...@gmail.com
> "Maurizio Borrelli" <maurizio.borre...@freepass.it> ha scritto nel messaggionews:%23GVazMI...@TK2MSFTNGP05.phx.gbl...
>
> [cut]
>
> '=================================================
>
> Public Function SommaEuro(rng As Range) As Double
>
> Dim cella As Range
>
> Dim s As Double
>
> s = 0
>
> For Each cella In rng
>
> With cella
>
> If IsNumeric(.Value) Then
>
> s = s + (.Value)
>
> End If
>
> End With
>
> Next
>
> SommaEuro = s
>
> End Function
>
> '================================================
>
> Scusa Maurizio, ho notato che, *forse*, nel suddetto codice (scritto da me)
> l'istruzione:
>
> s = s + cella.value
>
> *rallentamento* notevole dei tempidirisposta della funzione stessa.
>
> Usando le funzioni del fogliodilavoro tramiteVba
> (Application.WorksheetFunction) sicuramente potrei ottenere una maggiore
> applicabili...
>
> Cosa ne pensi?
>
> Esistono soluzioni alternative?
>
> Ciao e grazie milleeeee
> Pippo
Ciao maurizio, naturalmente era scontato che tu avessi ragione,
chiedendo scusa ai cani io lo sono stato, e' bastato sostituire ;""
con ;0 nella formula da me mal impostata, che tutto e' andato a posto,
come nel famoso film Alien Scontro Finale "tutto deve filare
automaticamente".
Mah ! sai, sbatti il muso di qua' sbatti il muso di la', sto
imparando, naturalmente ammesso che voi maestri non perdiate la
pazienza.
Solo una precisazione quando si hanno 40 colonne e 500 righe, mettere
dati diversi in colonne diverse e' un po' macchinoso per la
visualizzazione, e con l'orrore qualche volta bisogna convivere,anche
perche' le tabelle pivot sono terreno inesplorato, non perdo la
speranza di imparare molto da voi tutti, vi rinnovo gli auguri per il
2008 vi ringrazio e ringrazio anche Pippo per il tempo che mi avete
dedicato.
Maurizio Borrelli
news:CC3A4DC0-553F-4309...@microsoft.com:
> "Maurizio Borrelli" ha scritto nel messaggio
> news:%23GVazMI...@TK2MSFTNGP05.phx.gbl...
[...]
Ciao Pippo.
Secondo me l'unica soluzione alternativa valida e sensata e' strutturare
i dati in modo che non si abbia bisogno di questi obbrobri di calcoli
basati su formattazioni, colori, o altre diavolerie a-informatiche. So
che c'e' chi non e' d'accordo, ma... pazienza.
Come ti dicevo in altro 3D, all'aumentare del numero delle volte che
viene eseguito qualcosa, che sia una somma o qualsiasi altra cosa, il
tempo di esecuzione fatalmente aumenta. In proporzione al numero delle
volte che quel qualcosa viene eseguito. Quindi togli pure quel
"*forse*".
Tu dici che "sicuramente", (stavolta senza asterischi, perche'? ;)
applicando una funzione intrinseca di Excel si potrebbe ottenere lo
stesso risultato in meno tempo. Puo' darsi... ma pare proprio non ce ne
siano adatte alla bisogna. Per fortuna, secondo me.
Inoltre: diffidare della funzione IsNumeric, che prende per numerici
anche dati che dal punto di vista dell'utente non lo sono. Per esempio:
10E2
10D2
ovvero tutte quelle stringhe, generalmente usate come "codici", che
possono essere interpretate come numeri in notazione scientifica. Per
non parlare delle immissioni errate, tipo:
12E4
al posto di:
1234
che invece che valutato come immissione errata, non numerica cioe',
viene accettato come se l'utente avesse digitato:
120000
Pippo
"Maurizio Borrelli" <maurizio...@freepass.it> ha scritto nel messaggio
news:%23CDFO$jVIHA...@TK2MSFTNGP04.phx.gbl...
> Secondo me l'unica soluzione alternativa valida e sensata e' strutturare i
> dati in modo che non si abbia bisogno di questi obbrobri di calcoli basati
> su formattazioni, colori, o altre diavolerie a-informatiche. So che c'e'
> chi non e' d'accordo, ma... pazienza.
Io sono d'accordo! ;-)
> Come ti dicevo in altro 3D, all'aumentare del numero delle volte che viene
> eseguito qualcosa, che sia una somma o qualsiasi altra cosa, il tempo di
> esecuzione fatalmente aumenta. In proporzione al numero delle volte che
> quel qualcosa viene eseguito. Quindi togli pure quel "*forse*".
> Tu dici che "sicuramente", (stavolta senza asterischi, perche'? ;)
Ho dato per scontato che fosse cosi'..(vedi link successivo)...;-)
> applicando una funzione intrinseca di Excel si potrebbe ottenere lo stesso
> risultato in meno tempo. Puo' darsi... ma pare proprio non ce ne siano
> adatte alla bisogna.
In realta', ricordavo di aver gia' letto qualcosa relativamente alla
velocita' delle funzioni (richiamabili da Vba) excel , ma non riuscivo a
trovare il link, ora sono riuscito a trovarlo:
http://groups.google.it/group/microsoft.public.it.office.excel/msg/efba14849365ecbc
>Per fortuna, secondo me.
:-))
> Inoltre: diffidare della funzione IsNumeric, che prende per numerici anche
> dati che dal punto di vista dell'utente non lo sono. Per esempio:
>
> 10E2
> 10D2
>
> ovvero tutte quelle stringhe, generalmente usate come "codici", che
> possono essere interpretate come numeri in notazione scientifica. Per non
> parlare delle immissioni errate, tipo:
>
> 12E4
>
> al posto di:
>
> 1234
>
> che invece che valutato come immissione errata, non numerica cioe', viene
> accettato come se l'utente avesse digitato:
>
> 120000
*Interessante*, quindi, nel caso in esame, quale alternativa ci potrebbe
essere all'uso della funzione "IsNumeric()"?
Grazie Maurizio per l'infinita disponibilita' e competenza!
Ciao
Pippo
Maurizio Borrelli
news:3371B3F0-3047-4831...@microsoft.com:
> "Maurizio Borrelli" ha scritto nel messaggio
> news:%23CDFO$jVIHA...@TK2MSFTNGP04.phx.gbl...
> > "Pippo" wrote in message
> > news:CC3A4DC0-553F-4309...@microsoft.com:
> >> "Maurizio Borrelli" ha scritto nel messaggio
> >> news:%23GVazMI...@TK2MSFTNGP05.phx.gbl...
> *Interessante*, quindi, nel caso in esame, quale alternativa ci potrebbe
> essere all'uso della funzione "IsNumeric()"?
Ciao Pippo.
Insisti con *questo* caso in esame di cui non mi importa ne' punto ne'
poco?!
Parliamo in generale, va'... ;-)
In generale... ogni programmatore (VB) s'arrangia come meglio crede.
Siamo nel campo minato della validazione dell'input dell'utente quindi
le scelte dipendono dal rigore col quale si vuol fare detta valutazione.
Se scavi appena un po' in rete troverai centinaia di routine di
validazione dell'input numerico. Dovresti trovarne anche nei post di
questo ng. Magari cercando proprio "IsNumeric".
Considera la "banale":
?"1234"=format$(clng("1234"),"0")
Vero
Cioe' ci si accerta che il testo, convertito in numero (Long, in questo
caso), sia uguale al testo immesso.
Mentre:
?"12E4"=Format$(Clng("12E4"),"0")
Falso
Pur essendo:
?isnumeric("12E4")
Vero
C'e' poi la questione delle virgole, dell'eventuale segno, ecc...