Funzioni statistiche di Excel: DISTRIB.BINOM

Traduzione articoli Traduzione articoli
Identificativo articolo: 827459 - Visualizza i prodotti a cui si riferisce l?articolo.
Espandi tutto | Chiudi tutto

In questa pagina

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.
Riduci questa tabellaEspandi questa tabella
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.
Riduci questa tabellaEspandi questa tabella
=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Ó

Identificativo articolo: 827459 - Ultima modifica: mercoledý 21 febbraio 2007 - Revisione: 4.0
Le informazioni in questo articolo si applicano a
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
Chiavi:á
kbexpertisebeginner kbinfo KB827459
LE INFORMAZIONI CONTENUTE NELLA MICROSOFT KNOWLEDGE BASE SONO FORNITE SENZA GARANZIA DI ALCUN TIPO, IMPLICITA OD ESPLICITA, COMPRESA QUELLA RIGUARDO ALLA COMMERCIALIZZAZIONE E/O COMPATIBILITA' IN IMPIEGHI PARTICOLARI. L'UTENTE SI ASSUME L'INTERA RESPONSABILITA' PER L'UTILIZZO DI QUESTE INFORMAZIONI. IN NESSUN CASO MICROSOFT CORPORATION E I SUOI FORNITORI SI RENDONO RESPONSABILI PER DANNI DIRETTI, INDIRETTI O ACCIDENTALI CHE POSSANO PROVOCARE PERDITA DI DENARO O DI DATI, ANCHE SE MICROSOFT O I SUOI FORNITORI FOSSERO STATI AVVISATI. IL DOCUMENTO PUO' ESSERE COPIATO E DISTRIBUITO ALLE SEGUENTI CONDIZIONI: 1) IL TESTO DEVE ESSERE COPIATO INTEGRALMENTE E TUTTE LE PAGINE DEVONO ESSERE INCLUSE. 2) I PROGRAMMI SE PRESENTI, DEVONO ESSERE COPIATI SENZA MODIFICHE, 3) IL DOCUMENTO DEVE ESSERE DISTRIBUITO INTERAMENTE IN OGNI SUA PARTE. 4) IL DOCUMENTO NON PUO' ESSERE DISTRIBUITO A SCOPO DI LUCRO.

Invia suggerimenti

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com