Condividi:        

formula incoerente con macro

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

formula incoerente con macro

Postdi miko » 26/09/19 06:04

Buongiorno,
casualmente, testando la funzione media, ho riscontrato un diverso risultato tra la formula e la macro;
in una generica colonna ho inserito questi quattro numeri, anche in celle non consecutive, 1, 3, 6, 8, casuali
in una cella vuota ho inserito la formula

Codice: Seleziona tutto
=ARROTONDA(MEDIA.SE(A10:A50; ">"&0);0)


mentre nel codice ho scritto

Codice: Seleziona tutto
Worksheets("Foglio1").Range("C10").Value = Round(Application.WorksheetFunction. _
                                            AverageIf(Worksheets("Foglio1") _
                                            .Range("A10:A50"), ">" & 0), 0)


stando alla descrizione delle 2 funzioni, arrotonda e media.se, il risultato esatto è 5
infatti la funzione restituisce 5, mentre il codice mi dà 4.
ho pensato ad un errore ed ho cambiato celle, foglio, formato celle, .... ma il risultato è sempre lo stesso.
la cosa strana è che aggiungendo altri valori, ho sostituendo i primi 4 valori con altri, formula e codice danno lo stesso risultato.
per numeri la cui media è ad esempio 3,5 ottengo 4, da formula e macro
non capisco perchè proprio con quei 4 valori, 1,3,6,8, si ha differenza nel risultato.
date le infinite possibilità di variare i valori allora se c'è un errore si potrà ripetere.
forse mi sfugge qualcosa?
ciao grazie
windows 10 - office 2013
miko
Utente Senior
 
Post: 520
Iscritto il: 29/12/09 10:44

Sponsor
 

Re: formula incoerente con macro

Postdi Marius44 » 26/09/19 06:34

Ciao
Iniziamo dicendo che la media NON arrotondata delle quattro cifre indicate è 4,5
Pertanto il problema, a mio avviso, sta nella funzione arrotonda e non in media.se. Infatti se in entrambe le formule (Funzione e VBA) sostituisci lo 0 con 1 vedrai che danno lo stesso risultato.
Forse sarebbe meglio utilizzare le formule più avanzate di arrotonda, sia come funzioni sia come VBA.

Ciao,
Mario
Marius44
Utente Senior
 
Post: 655
Iscritto il: 07/09/15 22:00

Re: formula incoerente con macro

Postdi wallace&gromit » 26/09/19 07:42

Penso che esistano due metodi accettati per arrotondare all'intero più vicini la media esatta di x.5:
quello che penso sia più accreditato nel mondo della statistica è approssimare al numero pari (ed è adottato dalla macro), mentre l'alternativa è approssimare verso l'alto come fa la formula (senz'altro preferito dagli studenti!).
Se lavori con numeri decimali il rischio di trovarsi con casi dubbi penso sia molto basso.
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: formula incoerente con macro

Postdi Marius44 » 26/09/19 09:22

Salve a tutti
Allego un file in cui ho ipotizzato le diverse possibilità di arrotondamento e di media sia con Formule sia con VBA

Penso chiarisca molte cose.
Ciao,
Mario

File a questo link https://www.dropbox.com/s/qv1s8ikr5aslvvj/Tipi%20arrotondamento.xlsm?dl=0
Marius44
Utente Senior
 
Post: 655
Iscritto il: 07/09/15 22:00

Re: formula incoerente con macro

Postdi zsadist » 26/09/19 09:36

Uhmmmm..
beh, considerate la mia solo come una partecipazione, ma non sono ne un matematico, ne una persona capace in excel.

in effetti, le due opzioni indicate danno risultati diversi o, se si tiene conto anche del suggerimento di Marius44, 3, di cui due con lo stesso risultato.

io, dato che sono mononeurone, se dovessi creare una macro per calcolare la media indicata, se parto dal presupposto che il risultato dato sia coerente con quanto mi serve, avrei creato la macro in modo da ricrearmi la stessa formula:

ovvero, se la formula era questa
Codice: Seleziona tutto
=ARROTONDA(MEDIA.SE(A10:A50; ">"&0);0)


la macro l'avrei fatta semplicemente così

Codice: Seleziona tutto
Range("C10") = "=ROUND(AVERAGEIF(A10:A50, "">""&0),0)"


so che non serve a nulla il mio intervento, tranquilli :D

P.S.Ho scritto prima dell'intervento di Marius44 dove ha postato il file :)
Adattati! L'incapacità di cambiare direzione porta alla sconfitta
zsadist
Utente Junior
 
Post: 96
Iscritto il: 04/04/19 13:48

Re: formula incoerente con macro

Postdi Anthony47 » 26/09/19 13:30

Dico la mia, a proposito della discrepanza tra il risultato via formula e via vba.

La differenza sta nell' Arrotonda; Mario ci ha ben fatto vedere che si fa presto a dire "Arrotonda", poi i metodi matematici sono vari, ognuno col suo particolare comportamento.
Per me la risposta all'arcano sta' in quanto scrive Microsoft nella documentazione della funzione vba Round, in una "Nota" in evidenza:
Microsoft Round function documentation ha scritto: This VBA function returns something commonly referred to as bankers rounding. So be careful before using this function. For more predictable results, use Worksheet Round functions in Excel VBA

Vedi https://docs.microsoft.com/it-it/office ... d-function

Insomma la funzione vba Round, pur avendo lo stesso nome e la stessa sintassi della funzione Excel Arrotonda restituisce "l'arrotondamento del bancario" e Microsoft suggerisce, se si vogliono risultati piu' simili alle attese, di usare WorksheetFunction.Round

Ciao

PS: "l'arrotondamento del bancario" immagino sia quello che favorisce la banca e sfavorisce il cliente; ma magari Mario ha una definizione diversa :D
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: formula incoerente con macro

Postdi zsadist » 26/09/19 13:58

Anthony47 ha scritto:
PS: "l'arrotondamento del bancario" immagino sia quello che favorisce la banca e sfavorisce il cliente; ma magari Mario ha una definizione diversa :D


:lol:
Adattati! L'incapacità di cambiare direzione porta alla sconfitta
zsadist
Utente Junior
 
Post: 96
Iscritto il: 04/04/19 13:48

Re: formula incoerente con macro

Postdi wallace&gromit » 26/09/19 15:24

Anthony ha scritto:"l'arrotondamento del bancario" immagino sia quello che favorisce la banca e sfavorisce il cliente

In questo caso la macro dovrebbe prevedere la domanda "compri o vendi?" :lol:
In realtà è quella che dicevo io, cioè arrotondare sempre al numero pari, in questo modo statisticamente si dovrebbero pareggiare le situazioni di arrotondamento per difetto e per eccesso.
È comunque comune anche l'arrotondamento "dello studente" come l'ho ribattezzato io (usato dalla formula del foglio di lavoro), che arrotonda sempre per eccesso a partire da 0.5.
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: formula incoerente con macro

Postdi miko » 26/11/19 21:35

Buonasera,
non ho avuto tempo libero per dedicarmi al problema indicatovi,
ma avevo premura di salutarvi e ringraziarvi.
solo ora ho potuto rivedere la questione e mi sono limitato velocemente
ad un breve test seguendo quanto indicato da Marius
...se in entrambe le formule (Funzione e VBA) sostituisci lo 0 con 1 vedrai che danno lo stesso risultato.

e quanto suggerito da Anthony
...e Microsoft suggerisce, se si vogliono risultati piu' simili alle attese, di usare WorksheetFunction.Round

in entrambi i casi, formula e macro forniscono lo stesso risultato.
noto però che se nella cella il risultato è 3 nella barra delle formule leggo ad esempio 2,8
oppure se in cella si trova 5 nella barra leggo 5,3
ho visto anche il file, molto esaustivo, realizzato da Marius; ma non ho potuto approfondire.
molto utile, mi ha fatto conoscere altre funzioni.
non immaginavo tutte queste diverse possibilità, ero limitato al solo e semplice arrotondamento per difetto ed eccesso.
grazie a tutti per i vostri contributi
windows 10 - office 2013
miko
Utente Senior
 
Post: 520
Iscritto il: 29/12/09 10:44

Re: formula incoerente con macro

Postdi Anthony47 » 27/11/19 19:22

noto però che se nella cella il risultato è 3 nella barra delle formule leggo ad esempio 2,8
oppure se in cella si trova 5 nella barra leggo 5,3
Quello che vedi nella formula e' il risultato della combinazione "valore della cella + formattazione scelta"; nella barra della formula vedrai il valore.

Nei casi citati evidentemente la formattazione e' "numeri interi con 0 decimali", e in questo caso Excel "arrotonda" il valore al numero intero piu' vicino, secondo il criterio che da 0.5 in avanti si arrotonda la visualizzazione al num superiore, prima di 0.5 si arrotonda la visualizzazione all'intero inferiore

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: formula incoerente con macro

Postdi miko » 06/12/19 06:09

Salve,
hai ragione, le celle sono con
...formattazione e' "numeri interi con 0 decimali"...

grazie, ciao
windows 10 - office 2013
miko
Utente Senior
 
Post: 520
Iscritto il: 29/12/09 10:44


Torna a Applicazioni Office Windows


Topic correlati a "formula incoerente con macro":


Chi c’è in linea

Visitano il forum: Nessuno e 64 ospiti

cron