Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Re: Arrotondare a cento in istogrammi

354 views
Skip to first unread message

fernando cinquegrani

unread,
Sep 21, 2006, 4:22:40 PM9/21/06
to
[Ggenny] scrive in
http://www.google.it/groups?threadm=F9A6A9F1-F90C-4411...@microsoft.com
> Ciao a tutti,
>
> ho cercato sul newsgroup ma non sono riuscita a trovare un thread che
> mi potesse aiutare, ho un problema con gli arrotondamenti, mi spiego
> se ho due numeri del tipo 12,5 e 87,5 e sono i numeri che appaiono in
> un istogramma a pila, nel grafico ( i cui valori devono essere
> approssimati all'unità) appaiono come 88 e 13 e quindi la somma
> anzichè darmi 100% mi dà 101%. Come posso fare perchè il totale sia
> sempre 100 anzichè 101?

non è chiaro. utilizza un istogramma in pila 100%: il terzo
della serie dei modelli. oppure considera 88 e 100-88.
.f

Ggenny

unread,
Sep 21, 2006, 4:55:02 PM9/21/06
to
Ciao Fernando,
in pratica il problema è che se io ho 12,5 e poi 87,5 la cui somma fa cento,
se chiedo ad excel di arrotondare all'unità mi tira fuori 13 e 88.
In effetti utilizzo il terzo tipo degli istrogrammi a pila e non sempre ho
due numeri a volte sono anche sei diversi segmenti dello stesso istogramma.
ciao e grazie

"fernando cinquegrani" ha scritto:

fernando cinquegrani

unread,
Sep 21, 2006, 7:53:38 PM9/21/06
to
[Ggenny] scrive in
http://www.google.it/groups?threadm=5501AEAA-1038-4D4F...@microsoft.com
> Ciao Fernando,
> in pratica il problema che se io ho 12,5 e poi 87,5 la cui somma fa
> cento, se chiedo ad excel di arrotondare all'unit?tira fuori 13 e
> 88.
 
se ho ben capito il problema è un problema di calcolo non di rappresentazione grafica.
vediamo meglio: hai una serie numerica in colonna A (io ho posto dei valori casuali)
in colonna B ne calcoliamo le percentuali sul totale
=A8/SOMMA($A$8:$A$19)
non ci piace; noi vogliamo un arrotondamento a 2 cifre decimali
(ossia un valore intero nella moltiplicazione o nella rappresentazione percentuale)
in colonna C arrotondiamo a 2 cifre decimali
=ARROTONDA(A8/SOMMA($A$8:$A$19);2)
ma il totale di colonna può a questo punto essere inferiore o superiore a 1 (100), in questo caso 1.01 (101),
 
  A B C D E F
7 valori % %;2 a b c
8 0.5036 0.07 0.07 0.07 0.07 0.07
9 0.8660 0.11 0.11 0.11 0.11 0.11
10 0.7654 0.10 0.10 0.10 0.10 0.10
11 0.3795 0.05 0.05 0.05 0.05 0.05
12 0.5452 0.07 0.07 0.07 0.07 0.07
13 0.6879 0.09 0.09 0.09 0.09 0.09
14 0.2162 0.03 0.03 0.03 0.03 0.03
15 0.9650 0.13 0.13 0.13 0.13 0.13
16 0.0855 0.01 0.01 0.01 0.01 0.01
17 0.9743 0.13 0.13 0.13 0.12 0.12
18 0.9549 0.12 0.12 0.12 0.12 0.12
19 0.7308 0.10 0.10 0.09 0.10 0.10
20 1.00 1.01 1.00 1.00 1.00
 
il modo più semplice per evitare residui è quello che ti consigliavo prima (a):
modifica la modalità di calcolo dell'ultima percentuale, ossia calcola con
=ARROTONDA(A8/SOMMA($A$8:$A$19);2)
le percentuali dalla prima alla penultima, e con
=1-SOMMA(D8:D18)
l'ultima percentuale (0.09 invece di 0.10).
 
un modo più complesso, che è quello maggiormente utilizzato negli annuari statistici,
consiste nell'attribuire i residui al valore più elevato (b).
la formula, matriciale (da inserire quindi con Ctrl+Maiusc+Invio), utilizza la
formula
=ARROTONDA(A8/SOMMA($A$8:$A$19);2)
per i valori diversi dal massimo
e attribuisce al valore massimo, nell'esempio 0.9743, i resti a 1 (100).
 
la formula è complessa, ma almeno ci risparmia dal dover guardare i singoli dati per individuare il massimo.
se poi diamo un nome alla serie (c), con Inserisci :: Nome :: Definisci...
(io l'ho chiamata serie2) possiamo facilmente trasportarla nel foglio di lavoro là dove ne abbiamo bisogno.
 

  A B C D E F
7 valori % %;2 a b c
8 0.5036 =A8/SOMMA($A$8:$A$19) =ARROTONDA(A8/SOMMA($A$8:$A$19);2) =ARROTONDA(A8/SOMMA($A$8:$A$19);2) =SE(A8=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A8/SOMMA($A$8:$A$19);2);ARROTONDA(A8/SOMMA($A$8:$A$19);2)) =SE(A8=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A8/SOMMA(serie2);2);ARROTONDA(A8/SOMMA(serie2);2))
9 0.8660 =A9/SOMMA($A$8:$A$19) =ARROTONDA(A9/SOMMA($A$8:$A$19);2) =ARROTONDA(A9/SOMMA($A$8:$A$19);2) =SE(A9=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A9/SOMMA($A$8:$A$19);2);ARROTONDA(A9/SOMMA($A$8:$A$19);2)) =SE(A9=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A9/SOMMA(serie2);2);ARROTONDA(A9/SOMMA(serie2);2))
10 0.7654 =A10/SOMMA($A$8:$A$19) =ARROTONDA(A10/SOMMA($A$8:$A$19);2) =ARROTONDA(A10/SOMMA($A$8:$A$19);2) =SE(A10=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A10/SOMMA($A$8:$A$19);2);ARROTONDA(A10/SOMMA($A$8:$A$19);2)) =SE(A10=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A10/SOMMA(serie2);2);ARROTONDA(A10/SOMMA(serie2);2))
11 0.3795 =A11/SOMMA($A$8:$A$19) =ARROTONDA(A11/SOMMA($A$8:$A$19);2) =ARROTONDA(A11/SOMMA($A$8:$A$19);2) =SE(A11=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A11/SOMMA($A$8:$A$19);2);ARROTONDA(A11/SOMMA($A$8:$A$19);2)) =SE(A11=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A11/SOMMA(serie2);2);ARROTONDA(A11/SOMMA(serie2);2))
12 0.5452 =A12/SOMMA($A$8:$A$19) =ARROTONDA(A12/SOMMA($A$8:$A$19);2) =ARROTONDA(A12/SOMMA($A$8:$A$19);2) =SE(A12=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A12/SOMMA($A$8:$A$19);2);ARROTONDA(A12/SOMMA($A$8:$A$19);2)) =SE(A12=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A12/SOMMA(serie2);2);ARROTONDA(A12/SOMMA(serie2);2))
13 0.6879 =A13/SOMMA($A$8:$A$19) =ARROTONDA(A13/SOMMA($A$8:$A$19);2) =ARROTONDA(A13/SOMMA($A$8:$A$19);2) =SE(A13=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A13/SOMMA($A$8:$A$19);2);ARROTONDA(A13/SOMMA($A$8:$A$19);2)) =SE(A13=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A13/SOMMA(serie2);2);ARROTONDA(A13/SOMMA(serie2);2))
14 0.2162 =A14/SOMMA($A$8:$A$19) =ARROTONDA(A14/SOMMA($A$8:$A$19);2) =ARROTONDA(A14/SOMMA($A$8:$A$19);2) =SE(A14=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A14/SOMMA($A$8:$A$19);2);ARROTONDA(A14/SOMMA($A$8:$A$19);2)) =SE(A14=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A14/SOMMA(serie2);2);ARROTONDA(A14/SOMMA(serie2);2))
15 0.9650 =A15/SOMMA($A$8:$A$19) =ARROTONDA(A15/SOMMA($A$8:$A$19);2) =ARROTONDA(A15/SOMMA($A$8:$A$19);2) =SE(A15=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A15/SOMMA($A$8:$A$19);2);ARROTONDA(A15/SOMMA($A$8:$A$19);2)) =SE(A15=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A15/SOMMA(serie2);2);ARROTONDA(A15/SOMMA(serie2);2))
16 0.0855 =A16/SOMMA($A$8:$A$19) =ARROTONDA(A16/SOMMA($A$8:$A$19);2) =ARROTONDA(A16/SOMMA($A$8:$A$19);2) =SE(A16=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A16/SOMMA($A$8:$A$19);2);ARROTONDA(A16/SOMMA($A$8:$A$19);2)) =SE(A16=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A16/SOMMA(serie2);2);ARROTONDA(A16/SOMMA(serie2);2))
17 0.9743 =A17/SOMMA($A$8:$A$19) =ARROTONDA(A17/SOMMA($A$8:$A$19);2) =ARROTONDA(A17/SOMMA($A$8:$A$19);2) =SE(A17=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A17/SOMMA($A$8:$A$19);2);ARROTONDA(A17/SOMMA($A$8:$A$19);2)) =SE(A17=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A17/SOMMA(serie2);2);ARROTONDA(A17/SOMMA(serie2);2))
18 0.9549 =A18/SOMMA($A$8:$A$19) =ARROTONDA(A18/SOMMA($A$8:$A$19);2) =ARROTONDA(A18/SOMMA($A$8:$A$19);2) =SE(A18=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A18/SOMMA($A$8:$A$19);2);ARROTONDA(A18/SOMMA($A$8:$A$19);2)) =SE(A18=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A18/SOMMA(serie2);2);ARROTONDA(A18/SOMMA(serie2);2))
19 0.7308 =A19/SOMMA($A$8:$A$19) =ARROTONDA(A19/SOMMA($A$8:$A$19);2) =1-SOMMA(D8:D18) =SE(A19=MAX($A$8:$A$19);1-SOMMA(ARROTONDA(($A$8:$A$19)/SOMMA($A$8:$A$19);2))+ARROTONDA(A19/SOMMA($A$8:$A$19);2);ARROTONDA(A19/SOMMA($A$8:$A$19);2)) =SE(A19=MAX(serie2);1-SOMMA(ARROTONDA((serie2)/SOMMA(serie2);2))+ARROTONDA(A19/SOMMA(serie2);2);ARROTONDA(A19/SOMMA(serie2);2))
20 =SOMMA(B8:B19) =SOMMA(C8:C19) =SOMMA(D8:D19) =SOMMA(E8:E19) =SOMMA(F8:F19)
 
                                                                                    .f
fernando cinquegrani
 
p.s.: utilizzare Formato :: Testo normale per rispondere a questo post
 

fernando cinquegrani

unread,
Sep 22, 2006, 2:04:21 AM9/22/06
to
f.cinq...@prodomosua.it"fernando cinquegrani"
<f.cinq...@xroxxmxxxa.it> ha scritto nel messaggio
news:%235E8Tld...@TK2MSFTNGP02.phx.gbl...

> la formula è complessa, ma almeno ci risparmia dal dover guardare i
> singoli dati per individuare il massimo.
> se poi diamo un nome alla serie (c), con Inserisci :: Nome ::
> Definisci...
> (io l'ho chiamata serie2) possiamo facilmente trasportarla nel foglio
> di lavoro là dove ne abbiamo bisogno.

naturalmente la formula
=SE(A8=MAX(rng);1-SOMMA(ARROTONDA((rng)/SOMMA(rng);2))+ARROTONDA(A8/SOMMA(rng);2);ARROTONDA(A8/SOMMA(rng);2))
Ctrl+Maiusc+Invio
diventa
=SE(A8=MAX(rng);1-SOMMA(ARROTONDA((rng)/SOMMA(rng);3))+ARROTONDA(A8/SOMMA(rng);3);ARROTONDA(A8/SOMMA(rng);3))
se si vuole un arrotondamento a 3 decimali, come solitamente si usa
fare.
e comunque, variando il numero dei decimali, la somma totale non cambia.
.f


fernando cinquegrani

unread,
Sep 24, 2006, 2:51:05 AM9/24/06
to
[fernando cinquegrani] scrive in
http://www.google.it/groups?threadm=eGBHW0g3...@TK2MSFTNGP04.phx.gbl

> naturalmente la formula
> =SE(A8=MAX(rng);1-SOMMA(ARROTONDA((rng)/SOMMA(rng);2))+ARROTONDA(A8/SOMMA(rng);2);ARROTONDA(A8/SOMMA(rng);2))
> Ctrl+Maiusc+Invio
> diventa
> =SE(A8=MAX(rng);1-SOMMA(ARROTONDA((rng)/SOMMA(rng);3))+ARROTONDA(A8/SOMMA(rng);3);ARROTONDA(A8/SOMMA(rng);3))
> se si vuole un arrotondamento a 3 decimali, come solitamente si usa
> fare.

certo che nel caso di valori ripetuti la formula si complica
e invece di utilizzare una formula del tipo
=SE(E(A8=MAX(rng);CONFRONTA(A8;rng;0)-1=INDICE(RIF.RIGA()-RIF.RIGA(rng);1));1-SOMMA(ARROTONDA((rng)/SOMMA(rng);2))+ARROTONDA(A8/SOMMA(rng);2);ARROTONDA(A8/SOMMA(rng);2))
che funzionerebbe solo per valori in colonna
č conveniente il ricorso a una funzione utente

Option Explicit
Option Base 1
Public Function matPct(A As Variant, dec As Long) As Variant
Dim T() As Variant, complete As Boolean
Dim i As Long, j As Long
Dim sn As Double, ds As Double
Dim nr As Long, nc As Long
sn = Application.WorksheetFunction.Sum(A)
If TypeOf A Is Range Then
nr = A.Rows.Count
nc = A.Columns.Count
Else
nr = UBound(A, 1)
nc = UBound(A, 2)
End If
ReDim T(nr, nc)
For i = 1 To nr
For j = 1 To nc
T(i, j) = Round(A(i, j) / sn, dec)
ds = ds + T(i, j)
Next j
Next i
If sn <> 1 Then
sn = Application.WorksheetFunction.Max(A)
For i = 1 To nr
For j = 1 To nc
If A(i, j) = sn And Not complete Then
T(i, j) = T(i, j) + 1 - ds
complete = True
End If
Next j
Next i
End If
matPct = T
End Function

che ha il vantaggio di essere indifferente alle dimensioni e alla
disposizione dell'intervallo.
va usata cosě
se in A1:C3 ho
5 2 3
4 5 6
7 8 9

se in E1:G3 inserisco
=ARROTONDA(A1/SOMMA($A$1:$C$3);2)
ottengo una somma pari al 98%

se in I1:K3 inserisco
=matPct(A1:C3;2)
[č una formula matriciale: selezionare l'intero
intervallo I1:K3, quindi, dopo aver inserito la formula,
inviare con Ctrl+Maiusc+Invio]
ottengo una somma pari al 100%.

i due argomenti della funzione sono rispettivamente
A1:C3 [intervallo] č il range di cui calcolare le percentuali
2 [decimali] č il numero di decimali di arrotondamento

il secondo argomento č opzionale, se assente viene
posto a 2.
in I1:K3 potevamo scrivere quindi piů semplicemente
=matPct(a1:c3)
che č una bella semplificazione rispetto a prima.

grazie della domanda, Ggenny,
mi serviva proprio questa funzione :-)
.f
fernando cinquegrani
Microsoft MVP
http://www.prodomosua.eu

fernando cinquegrani

unread,
Sep 24, 2006, 10:07:43 AM9/24/06
to
[fernando cinquegrani] scrive in
http://www.google.it/groups?threadm=eJOlWX63...@TK2MSFTNGP02.phx.gbl

> Option Explicit
> Option Base 1
> Public Function matPct(A As Variant, dec As Long) As Variant

> il secondo argomento č opzionale, se assente viene


> posto a 2.
> in I1:K3 potevamo scrivere quindi piů semplicemente
> =matPct(a1:c3)
> che č una bella semplificazione rispetto a prima.

naturalmente, questa affermazione č vera se scrivo
Public Function matPct(A As Variant, Optional dec As Long = 2) As
Variant
:-)
.f


gge...@hotmail.com

unread,
Sep 24, 2006, 12:38:27 PM9/24/06
to

fernando cinquegrani ha scritto:

> [fernando cinquegrani] scrive in
> http://www.google.it/groups?threadm=eJOlWX63...@TK2MSFTNGP02.phx.gbl
> > Option Explicit
> > Option Base 1
> > Public Function matPct(A As Variant, dec As Long) As Variant
>

> > il secondo argomento è opzionale, se assente viene
> > posto a 2.
> > in I1:K3 potevamo scrivere quindi più semplicemente
> > =matPct(a1:c3)
> > che è una bella semplificazione rispetto a prima.
>
> naturalmente, questa affermazione è vera se scrivo


> Public Function matPct(A As Variant, Optional dec As Long = 2) As
> Variant
> :-)


Ciao Fernando,
prometto che scriverò con formato normale appena riuscirò a
installare qualsiasi cosa che mi permetta di accedere al ng senza ie.

Scusami per questa volta.

Premetto che leggendo le tue risposte mi sono sentita tanto come le
tipe che partecipano a quella specie di reality fatto di belle ( e
oche?) e maschietti con qi 160.

Posso darti un paio di numeri reali che mi creano problemi?

eccoli:

0,198146803791087 0,575525307521678 0,226327888687235

Se faccio la somma usando excel mi viene esattamente 1

Se creo un istogramma a pila a 100 con valori approssimati a meno
dell'unità mi viene
20% 58% 23% che "visivamente" dà come risultato 101

Ho provato a usare la funzione MatPct l'ho in pratica copiata e
incollata in un modulo di excel, e poi in un range di 3 celle ho
copiato la tua formula che è diventata
=matPct(D23:F23;2).

Naturalmente i 3 numeri erano nelle celle d23:f23.

Ma i numeri che mi appaiono nel grafico sono 20%; 58% ;23% la cui somma
fa 101.

Grazie di tutto.

Ggenny

fernando cinquegrani

unread,
Sep 24, 2006, 12:56:07 PM9/24/06
to
[gge...@hotmail.com] scrive in
http://www.google.it/groups?threadm=1159115907.6...@m73g2000cwd.googlegroups.com

> prometto che scriverò con formato normale appena riuscirò a
> installare qualsiasi cosa che mi permetta di accedere al ng senza ie.

stai scrivendo in formato normale :-)

> Premetto che leggendo le tue risposte mi sono sentita tanto come le
> tipe che partecipano a quella specie di reality fatto di belle ( e
> oche?) e maschietti con qi 160.

io non ho qi 160, ma credo anche che tu non sia un'oca

> Se creo un istogramma a pila a 100 con valori approssimati a meno
> dell'unità mi viene
> 20% 58% 23% che "visivamente" dà come risultato 101

immagino sbagli qualcosa nell'inserimento.
questo è il file delle mie prove
http://www.prodomosua.eu/zips/percentuali.xls

la prima serie riporta i tuoi valori.
.f


Ggenny

unread,
Sep 24, 2006, 1:23:02 PM9/24/06
to
Ciao Fernando,
ho riprovato con il mio foglio e il secondo numero appare sempre come 58%,
mentre con il tuo foglio lo stesso numero appare come 57%.
Avrò fatto qualche errore. Per adesso copio il tuo foglio e faccio i miei
grafici sul tuo foglio excel, appena ho un attimo di tempo controllo per
quale motivo il mio foglio continua a darmi 58%, se il motivo è degno di nota
lo posto, se ho solo dimenticato di copiare qualcosa eviterò di farlo.
Grazie di nuovo di tutto.

Ggenny

P.S.
Secondo me ti sottovaluti :P


"fernando cinquegrani" ha scritto:

0 new messages