Al momento sei offline in attesa che la connessione Internet venga ristabilita

Funzioni statistiche di Excel: DISTRIB.BINOM

Il supporto per Office 2003 è terminato

Il supporto Microsoft per Office 2003 è terminato l'8 aprile 2014. Questa modifica ha interessato gli aggiornamenti software e le opzioni di sicurezza. Ulteriori informazioni su come continuare a essere protetti.

Sommario
In questo articolo viene descritta la funzione DISTRIB.BINOM di Microsoft Office Excel 2003 e delle versioni successive di Excel, ne viene illustrato l'utilizzo e vengono confrontati i risultati della funzione in Excel 2003 e nelle versioni successive di Excel con i risultati nelle versioni precedenti di Excel.

Informazioni per Microsoft Excel 2004 per Mac

Le funzioni statistiche di Excel 2004 per Mac sono state aggiornate con gli stessi algoritmi utilizzati per l'aggiornamento delle funzioni statistiche di Microsoft Office Excel 2003 e delle versioni successive di Excel. Le informazioni di questo articolo relative al funzionamento o alla modifica di una funzione di Excel 2003 e delle versioni successive di Excel riguardano anche Excel 2004 per Mac.
Informazioni
Quando cumulativo = VERO, la funzione DISTRIB.BINOM(x, n, p, cumulativo) restituisce la probabilità di x o meno successi in n prove Bernoulli indipendenti. Ciascuna di tali prove ha una probabilità associata p di successo (e la probabilità 1-p di insuccesso). Quando cumulativo = FALSO, DISTRIB.BINOM restituisce la probabilità di esattamente x successi.

Sintassi

BINOMDIST(x, n, p, cumulative)

Parametri

  • x è un numero intero non negativo
  • n è un numero intero positivo
  • 0 < p < 1
  • Cumulativo è una variabile logica che assume i valori di VERO o FALSO

Esempio di utilizzo

Si facciano le seguenti presupposizioni:
  • Nel baseball è chiamato "battitore da 0,300" un battitore che ha una probabilità di 0,300 (30%) di colpire la palla con successo ogni volta che si trova alla battuta, ovvero a ogni prova.
  • Le volte successive che il battitore si trova alla battuta sono le prove di Bernoulli indipendenti.
È possibile utilizzare la tabella che segue per calcolare la probabilità che tale battitore riesca a colpire con successo la palla esattamente 0, 1, 2, ..., o 10 volte in 10 prove e la probabilità che riesca a colpire la palla con successo 0, 1 o meno, 2 o meno, ..., 9 o meno, o 10 o meno volte in 10 prove.

Se il battitore colpisce la palla con successo 50 volte durante le prime 200 prove, con una media di 0,250 (25%), dovrà colpirla 100 volte durante le successive 300 prove se vuole arrivare a 150 colpi andati a buon fine e a una media di 0,300 (30%) su 500 prove. È possibile utilizzare la tabella che segue per analizzare le probabilità che il battitore colpisca la palla con successo un numero sufficiente di volte per mantenere la propria media. I commentatori di baseball fanno spesso riferimento alla "legge delle medie" quando affermano che i tifosi non devono preoccuparsi delle prestazioni di un battitore che abbia colpito la palla con successo solo 50 volte nel corso delle prime 200 prove, in quanto "prima della fine della stagione la sua media raggiungerà comunque quota 0,300". Se le prove fossero realmente indipendenti e il battitore avesse realmente una probabilità di successo di 0,300 a ogni prova, questo ragionamento non sarebbe corretto in quanto i risultati delle prime 200 prove non influiscono sul numero di successi o fallimenti ottenuti nel corso delle ultime 300 prove.

Per illustrare l'utilizzo della funzione DISTRIB.BINOM, creare un foglio di lavoro di Excel vuoto, copiare la tabella che segue, selezionare la cella A1 nel foglio di lavoro vuoto e incollare le voci in modo che nella tabella che segue vengano inserite nelle celle A1:C22 del foglio di lavoro.
numero di prove10
probabilità di successo0.3
successi, xP(x successi esatti)P(x o meno successi)
0=DISTRIB.BINOM(A4,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A4,$B$1,$B$2,VERO)
1=DISTRIB.BINOM(A5,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A5,$B$1,$B$2,VERO)
2=DISTRIB.BINOM(A6,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A6,$B$1,$B$2,VERO)
3=DISTRIB.BINOM(A7,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A7,$B$1,$B$2,VERO)
4=DISTRIB.BINOM(A8,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A8,$B$1,$B$2,VERO)
5=DISTRIB.BINOM(A9,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A9,$B$1,$B$2,VERO)
6=DISTRIB.BINOM(A10,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A10,$B$1,$B$2,VERO)
7=DISTRIB.BINOM(A11,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A11,$B$1,$B$2,VERO)
8=DISTRIB.BINOM(A12,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A12,$B$1,$B$2,VERO)
9=DISTRIB.BINOM(A13,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A13,$B$1,$B$2,VERO)
10=DISTRIB.BINOM(A14,$B$1,$B$2,FALSO)=DISTRIB.BINOM(A14,$B$1,$B$2,VERO)
300 prove, probabilità di successo 0,3:
successi, xP(x successi esatti)P(x o meno successi)
89=DISTRIB.BINOM(A18,300,0,3,FALSO)=DISTRIB.BINOM(A18,300,0,3,VERO)
90=DISTRIB.BINOM(A19,300,0,3,FALSO)=DISTRIB.BINOM(A19,300,0,3,VERO)
99=DISTRIB.BINOM(A20,300,0,3,FALSO)=DISTRIB.BINOM(A20,300,0,3,VERO)
100=DISTRIB.BINOM(A21,300,0,3,FALSO)=DISTRIB.BINOM(A21,300,0,3,VERO)
101=DISTRIB.BINOM(A22,300,0,3,FALSO)=DISTRIB.BINOM(A22,300,0,3,VERO)
Nota Una volta incollata questa tabella nel nuovo foglio di lavoro di Excel, scegliere il pulsante Opzioni Incolla e fare clic su Applica formattazione di destinazione. Con l'intervallo incollato ancora selezionato, utilizzare una delle procedure seguenti, a seconda della versione di Excel in uso:
  • In Microsoft Office Excel 2007, fare clic sulla scheda Home, scegliere Formato nel gruppo Celle, quindi fare clic su Adatta larghezza colonne.
  • In Excel 2003 e nelle versioni precedenti di Excel scegliere Colonna dal menu Formato, quindi fare clic su Adatta.
Si consiglia di formattare le celle B4:C22 in maniera coerente per facilitarne la lettura, ad esempio formattare i valori numerici con cinque posizioni decimali.

Le celle B4:B14 mostrano le probabilità di x successi esatti in 10 prove. Il numero più probabile di successi è 3. Le probabilità di 0, 6, 7, 8, 9 o 10 successi sono ciascuna inferiore allo 0,05 e assommano a 0,076 circa. Pertanto la probabilità di 1, 2, 3, 4 o 5 successi è di circa 1 – 0,076 = 0,924. Le celle C4:C14 mostrano le probabilità di x o meno successi in 10 prove. È possibile verificare che le voci presenti in ciascuna riga della colonna C corrispondono alla somma di tutte le voci presenti nella colonna B fino alla riga in questione compresa.

Le celle B18:B20 mostrano che il numero più probabile di successi in 300 prove è 90. La probabilità di x successi esatti aumenta con x che raggiunge 90, e quindi decresce con x che va oltre 90. La probabilità di 90 o meno successi è di poco oltre il 50%, come mostrato dalla cella C20. La probabilità di 99 o meno successi è di circa 0,884. Pertanto, c'è solo una probabilità dell'11,6% (0,116 = 1 – 0,884) che il battitore ottenga 100 o più successi.

Risultati nelle versioni precedenti di Excel

Knusel (vedere la nota 1) ha documentato istanze in cui la funzione DISTRIB.BINOM non restituisce un valore numerico bensì #NUM! a causa di un overflow numerico. Quando tuttavia DISTRIB.BINOM restituisce risposte numeriche, queste sono corrette. DISTRIB.BINOM restituisce #NUM! solo quando il numero di prove è maggiore o uguale a 1030. Non vi sono problemi di calcolo se n < 1030. In pratica, tali valori elevati di n sono improbabili. Con un numero così elevato di prove indipendenti, è preferibile approssimarsi alla distribuzione binomia per mezzo di una distribuzione normale (se n*p e n*(1-p) sono sufficientemente elevati, ad esempio ciascuno è maggiore di 30) o altrimenti per mezzo di una distribuzione Poisson.

Nota 1 Knusel, L. "On the Accuracy of Statistical Distributions in Microsoft Excel 97", Computational Statistics and Data Analysis (1998), 26: 375-377.

Per il caso non cumulativo, DISTRIB.BINOM(x, n, p, FALSO) utilizza la formula seguente:
COMBIN(n,x)*(p^x)*((1-p)^(n-x))
COMBINAZIONE è una funzione di Excel che restituisce il numero di combinazioni di x elementi in un gruppo di n elementi. COMBINAZIONE(n,x) viene talvolta scritto come nCx e detta "coefficiente combinatorio" o semplicemente "n scelta x". Se si effettua un esperimento con la funzione COMBINAZIONE digitando =COMBINAZIONE(1029,515) in una cella e =COMBINAZIONE(1030,515) in un'altra cella, la prima cella restituisce un numero astronomico, 1.4298E+308, mentre la seconda cella restituisce #NUM! perché il numero è perfino maggiore. L'overflow di COMBINAZIONE causa un overflow di DISTRIB.BINOM nelle versioni precedenti di Excel.

COMBINAZIONE non è stata modificata in Excel 2003 e nelle versioni successive di Excel.

Risultati in Excel 2003 e nelle versioni successive di Excel

Poiché Microsoft è riuscita a individuare quando un overflow causa la restituzione di #NUM! da parte della funzione DISTRIB.BINOM ed è noto che DISTRIB.BINOM funziona bene quando l'overflow non si verifica, è stato implementato un algoritmo condizionale in Excel 2003 e nelle versioni successive di Excel.

L'algoritmo utilizza il codice della funzione DISTRIB.BINOM delle versioni precedenti di Excel (la formula di calcolo menzionata in precedenza in questo articolo) quando n < 1030. Quando n >= 1030, Excel 2003 e le versioni successive di Excel utilizzano l'algoritmo alternativo descritto più avanti nel presente articolo.

In genere la funzione COMBINAZIONE causa un overflow perché è astronomica, ma p^x e (1-p)^(n-x) sono ciascuno infinitesimali. Se fosse possibile moltiplicarli insieme, il prodotto sarebbe una probabilità realistica compresa tra 0 e 1. Ma dato che i numeri aritmetici finiti esistenti non possono essere moltiplicati insieme, un algoritmo alternativo evita la valutazione di COMBINAZIONE.

L'approccio adottato da Microsoft calcola una somma non scalato di tutte le probabilità di x successi esatti utilizzati in seguito a fini scalari. Calcola inoltre un valore non scalato della probabilità che si desidera venga restituita da DISTRIB.BINOM. Infine, utilizza il fattore di scalatura per restituire un valore DISTRIB.BINOM corretto.

L'algoritmo sfrutta il fatto che il rapporto dei termini successivi di COMBINAZIONE(n,k)*(p^k)*((1-p)^(n-k)) ha una forma semplice. L'algoritmo procede come descritto nello pseudocodice riportato nei passaggi che seguono.

Passaggio 0: (Inizializzazione). Inizializzazione della ProbabilitàTotaleNonScalata e delle proprietà RisultatoNonScalato a 0. Inizializzazione della costante PraticamenteZero a un numero estremamente ridotto, ad esempio 10^(-12).

Passaggio 1: Individuazione di n*p e arrotondamento al numero intero più vicino, m. Il numero più probabile di successi in n prove è m o m+1. COMBINAZIONE(n,k)*(p^k)*((1-p)^(n-k)) decresce mentre k si riduce da m a m-1 a m-2 e così via. Inoltre, COMBINAZIONE(n,k)*(p^k)*((1-p)^(n-k)) decresce mentre k aumenta da m+1 a m+2 a m+3 e così via.
TotalUnscaledProbability = TotalUnscaledProbability + 1;If (m == x) then UnscaledResult = UnscaledResult + 1;If (cumulative && m < x) then UnscaledResult = UnscaledResult + 1;
Passaggio 2: Calcolo delle probabilità non scalate per k > m:
PreviousValue = 1;Done = FALSE;k = m + 1;While (not Done && k <= n)  {	CurrentValue = PreviousValue * (n – k + 1) * p / (k * (1 – p));	TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue;	If (k == x) then UnscaledResult = UnscaledResult + CurrentValue;	If (cumulative && k < x) then UnscaledResult = UnscaledResult + 		CurrentValue;	If (CurrentValue <= EssentiallyZero) then Done = TRUE;	PreviousValue = CurrentValue;	k = k+1;  }end While;
Passaggio 3: Calcolo delle probabilità non scalate per k < m:
PreviousValue = 1;Done = FALSE;k = m - 1;While (not Done && k >= 0)  {	CurrentValue = PreviousValue * k+1 * (1-p) / ((n – k) * p);	TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue;	If (k == x) then UnscaledResult = UnscaledResult + CurrentValue;	If (cumulative && k < x) then UnscaledResult = UnscaledResult + 		CurrentValue;	If (CurrentValue <= EssentiallyZero) then Done = TRUE;	PreviousValue = CurrentValue;	k = k-1;  }end While;
Passaggio 4: Combinazione dei risultati non scalati:
Return UnscaledResult/TotalUnscaledProbability;
Sebbene questo metodo venga utilizzato solo per n >= 1030, è possibile utilizzare le aggiunte riportate di seguito nel foglio di Excel per facilitare l'esecuzione manuale di questo algoritmo affinché calcoli DISTRIB.BINOM(3, 10, 0,3, VERO) (nell'esempio del baseball, la probabilità di 3 o meno colpi in 10 prove per un battitore da 0,300).

Per illustrare tutto ciò, copiare la tabella seguente, selezionare la cella D4 nel foglio di lavoro di Excel creato precedentemente, quindi incollare le voci in modo che vengano compilate le celle D1:E15 del foglio di lavoro.
=D5*(1-$B$2)*(A4+1)/($B$2*($B$1-A4))=D4/$D$15
=D6*(1-$B$2)*(A5+1)/($B$2*($B$1-A5))=D5/$D$15
1=D6/$D$15
=D6*$B$2*($B$1-A7+1)/((1-$B$2)*A7)=D7/$D$15
=D7*$B$2*($B$1-A8+1)/((1-$B$2)*A8)=D8/$D$15
=D8*$B$2*($B$1-A9+1)/((1-$B$2)*A9)=D9/$D$15
=D9*$B$2*($B$1-A10+1)/((1-$B$2)*A10)=D10/$D$15
=D10*$B$2*($B$1-A11+1)/((1-$B$2)*A11)=D11/$D$15
=D11*$B$2*($B$1-A12+1)/((1-$B$2)*A12)=D12/$D$15
=D12*$B$2*($B$1-A13+1)/((1-$B$2)*A13)=D13/$D$15
=D13*$B$2*($B$1-A14+1)/((1-$B$2)*A14)=D14/$D$15
=SOMMA(D4:D14)
Nella colonna D sono riportate le probabilità non scalate. Il valore 1 nella cella D6 è il risultato del Passaggio 1 dell'algoritmo. In Excel 2003 e nelle versioni successive di Excel, le voci nelle celle D7, D8, ..., D14 (in questo ordine) vengono calcolate al Passaggio 2. Le voci nelle celle D5 e D4 (in questo ordine) vengono calcolate al Passaggio 3. La somma di tutte le probabilità non scalate è riportata nella cella D15.

Per calcolare la probabilità di 3 o meno successi, digitare la formula che segue in qualsiasi cella vuota:
= SOMMA(D4:D7)/D15
Nell'esempio precedente, PraticamenteZero non interrompe il Passaggio 2 o 3. Se tuttavia si desidera calcolare DISTRIB.BINOM(550, 2000, 0.3, VERO), PraticamenteZero potrebbe interrompere il Passaggio 2 o 3. Una variabile casuale binomia con n = 2000 e p = 0,3 ha una distribuzione che viene approssimata per mezzo della distribuzione normale con una media di 600 e la deviazione standard RADQ(2000*0,3*(1 – 0,3)) = RADQ(420) = 20,5. Pertanto 805 è di 10 deviazioni standard superiore rispetto alla media e 395 è di 10 deviazioni standard inferiore rispetto alla media. A seconda dell'impostazione di PraticamenteZero, PraticamenteZero potrebbe interrompere il Passaggio 2 prima che raggiunga 805 e il Passaggio 3 prima che raggiunga 395.

Conclusioni

Imprecisioni nelle versioni di Excel precedenti a Excel 2003 si verificano solo quando il numero di prove è maggiore o uguale a 1030. In tali casi DISTRIB.BINOM restituisce #NUM! nelle versioni precedenti di Excel a causa di un overflow di un termine in una sequenza di termini moltiplicati tra loro. Per correggere questo comportamento, nei casi in cui potrebbe verificarsi tale overflow in Excel 2003 e nelle versioni successive di Excel viene utilizzata la procedura alternativa descritta in precedenza.

Anche le funzioni CRIT.BINOM, DISTRIB.IPERGEOM, DISTRIB.BINOM.NEG e POISSON presentano un comportamento simile nelle versioni precedenti di Excel. Anche queste funzioni restituiscono risultati numerici corretti o #NUM! o #DIV/0!, e anche in questi casi i problemi sono causati da un overflow o da un underflow.

È facile determinare quando e come tali problemi si verificano. In Excel 2003 e nelle versioni successive di Excel viene utilizzato un algoritmo alternativo simile a quello di DISTRIB.BINOM affinché vengano restituite risposte corrette nei casi in cui nelle versioni precedenti di Excel viene restituito #NUM!.
Proprietà

ID articolo: 827459 - Ultima revisione: 02/21/2007 09:54:42 - Revisione: 4.0

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2004 for Mac

  • kbexpertisebeginner kbinfo KB827459
Feedback