Calcolo dell'intervallo di confidenza in Excel

Excel viene utilizzato per eseguire diverse attività statistiche, una delle quali è il calcolo dell'intervallo di confidenza, che viene utilizzato come sostituto più appropriato per una stima puntuale con una piccola dimensione del campione.

Vogliamo subito notare che la stessa procedura per calcolare l'intervallo di confidenza è piuttosto complicata, tuttavia in Excel ci sono una serie di strumenti progettati per facilitare questo compito. Diamo un'occhiata a loro.

Calcolo dell'intervallo di confidenza

È necessario un intervallo di confidenza per fornire una stima dell'intervallo ad alcuni dati statici. Lo scopo principale di questa operazione è rimuovere le incertezze della stima puntuale.

Esistono due metodi per eseguire questa attività in Microsoft Excel:

• Operatore NORMA DI FIDUCIA – utilizzato nei casi in cui la dispersione è nota;
• Operatore STUDENTE DI FIDUCIAquando la varianza è sconosciuta.

Di seguito analizzeremo passo dopo passo entrambi i metodi nella pratica.

Metodo 1: istruzione TRUST.NORM

Questa funzione è stata introdotta per la prima volta nell'arsenale del programma nell'edizione Excel 2010 (prima di questa versione era sostituita dall'operatore "DI FIDUCIA"). L'operatore è incluso nella categoria “statistica”.

Formula di funzione NORMA DI FIDUCIA assomiglia a quello:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

Come possiamo vedere, la funzione ha tre argomenti:

• "Alfa" è un indicatore del livello di significatività, che viene preso come base per il calcolo. Il livello di confidenza è calcolato come segue:
  • 1-"Альфа". Questa espressione è applicabile se il valore "Alfa" presentato come un coefficiente. Per esempio, 1-0,7=, dove 0,7=70%/100%.
  • (100-"Альфа")/100. Questa espressione verrà applicata se consideriamo il livello di confidenza con il valore "Alfa" in percentuale. Per esempio, (100-70) / 100 = 0,3.
• "Deviazione standard" – rispettivamente, la deviazione standard del campione di dati analizzato.
• "Taglia" è la dimensione del campione di dati.

Nota: Per questa funzione, la presenza di tutti e tre gli argomenti è un prerequisito.

Operatore “DI FIDUCIA", utilizzato nelle versioni precedenti del programma, contiene gli stessi argomenti ed esegue le stesse funzioni.

Formula di funzione DI FIDUCIA come segue:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

Non ci sono differenze nella formula stessa, solo il nome dell'operatore è diverso. In Excel 2010 e versioni successive, questo operatore è nella categoria Compatibilità. Nelle versioni precedenti del programma, si trova nella sezione delle funzioni statiche.

Il limite dell'intervallo di confidenza è determinato dalla seguente formula:

X+(-)ДОВЕРИТ.НОРМ

where Х è il valore medio nell'intervallo specificato.

Vediamo ora come applicare nella pratica queste formule. Quindi, abbiamo una tabella con vari dati da 10 misurazioni effettuate. In questo caso, la deviazione standard del set di dati è 8.

Il nostro compito è ottenere il valore dell'intervallo di confidenza con un livello di confidenza del 95%.

1. Prima di tutto, seleziona una cella per visualizzare il risultato. Quindi clicchiamo sul pulsante “Inserisci funzione” (a sinistra della barra della formula).
2. Si apre la finestra della procedura guidata. Facendo clic sulla categoria di funzioni corrente, espandere l'elenco e fare clic sulla riga al suo interno “statistico”.
3. Nell'elenco proposto, fare clic sull'operatore “NORMA DI FIDUCIA”, Quindi premere OK.
4. Vedremo una finestra con le impostazioni degli argomenti della funzione, riempiendo la quale premiamo il pulsante OK.
   • nel campo "Alfa" indicare il livello di significatività. Il nostro compito presuppone un livello di confidenza del 95%. Sostituendo questo valore nella formula di calcolo, che abbiamo considerato sopra, otteniamo l'espressione: (100-95)/100. Lo scriviamo nel campo dell'argomento (oppure puoi scrivere subito il risultato del calcolo pari a 0,05).
   • nel campo “std_off” a seconda delle nostre condizioni, scriviamo il numero 8.
   • nel campo “Size” specificare il numero di elementi da esaminare. Nel nostro caso sono state effettuate 10 misurazioni, quindi scriviamo il numero 10.
5. Per evitare di dover riconfigurare la funzione quando i dati cambiano, è possibile automatizzarla. Per questo utilizziamo la funzione “DAI UN'OCCHIATA". Metti il ​​puntatore nell'area di input delle informazioni sull'argomento "Taglia", quindi fai clic sull'icona del triangolo sul lato sinistro della barra della formula e fai clic sull'elemento "Più funzioni...".
6. Di conseguenza, si aprirà un'altra finestra della procedura guidata. Scegliendo una categoria “statistico”, clicca sulla funzione “DAI UN'OCCHIATA", allora OK.
7. Lo schermo visualizzerà un'altra finestra con le impostazioni degli argomenti della funzione, che viene utilizzata per determinare il numero di celle in un determinato intervallo che contengono dati numerici.

   Formula di funzione CONTROLLO si scrive così: =СЧЁТ(Значение1;Значение2;...).

   Il numero di argomenti disponibili per questa funzione può essere fino a 255. Qui puoi scrivere numeri specifici, indirizzi di celle o intervalli di celle. Useremo l'ultima opzione. Per fare ciò, clicca sull'area di inserimento delle informazioni per il primo argomento, quindi, tenendo premuto il tasto sinistro del mouse, seleziona tutte le celle di una delle colonne della nostra tabella (senza contare l'intestazione), quindi premi il pulsante OK.

8. Come risultato delle azioni intraprese, il risultato dei calcoli per l'operatore verrà visualizzato nella cella selezionata NORMA DI FIDUCIA. Nel nostro problema, il suo valore si è rivelato uguale a 4,9583603.
9. Ma questo non è ancora il risultato finale del nostro compito. Successivamente, è necessario calcolare il valore medio su un determinato intervallo. Per fare ciò, è necessario utilizzare la funzione “CUORE"A che esegue il compito di calcolare la media su un determinato intervallo di dati.

   La formula dell'operatore è scritta in questo modo: =СРЗНАЧ(число1;число2;...).

   Seleziona la cella in cui intendiamo inserire la funzione e premi il pulsante “Inserisci funzione”.

10. Nella categoria “statistico” scegli un operatore noioso "CUORE" e fare clic su OK.
11. Negli argomenti della funzione nel valore dell'argomento "Numero" specificare l'intervallo, che include tutte le celle con i valori di tutte le misurazioni. Quindi clicchiamo VA BENE.
12. A seguito delle azioni intraprese, il valore medio verrà calcolato automaticamente e visualizzato nella cella con la funzione appena inserita.
13. Ora dobbiamo calcolare i limiti CI (intervallo di confidenza). Iniziamo calcolando il valore del bordo destro. Selezioniamo la cella dove vogliamo visualizzare il risultato, ed eseguiamo l'addizione dei risultati ottenuti utilizzando gli operatori “CUORE" e "NORME DI FIDUCIA”. Nel nostro caso, la formula si presenta così: A14+A16. Dopo averlo digitato, premere entrare.
14. Di conseguenza, verrà eseguito il calcolo e il risultato verrà immediatamente visualizzato nella cella con la formula.
15. Quindi, in modo simile, eseguiamo il calcolo per ottenere il valore del bordo sinistro del CI. Solo in questo caso il valore del risultato “NORME DI FIDUCIA” non è necessario aggiungere, ma sottrarre dal risultato ottenuto utilizzando l'operatore “CUORE". Nel nostro caso, la formula si presenta così: =A16-A14.
16. Dopo aver premuto Invio, otterremo il risultato nella cella data con la formula.

Nota: Nei paragrafi precedenti, abbiamo cercato di descrivere nel modo più dettagliato possibile tutti i passaggi e ciascuna funzione utilizzata. Tuttavia, tutte le formule prescritte possono essere scritte insieme, come parte di una grande:

• Per determinare il bordo destro dell'elemento della configurazione, la formula generale sarà simile alla seguente:

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• Allo stesso modo, per il bordo sinistro, solo invece di un più, devi inserire un meno:

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

Metodo 2: operatore TRUST.STUDENT

Ora, acquisiamo familiarità con il secondo operatore per la determinazione dell'intervallo di confidenza − STUDENTE DI FIDUCIA. Questa funzione è stata introdotta nel programma in tempi relativamente recenti, a partire dalla versione di Excel 2010, e ha lo scopo di determinare l'IC del dataset selezionato utilizzando la distribuzione di Student, con una varianza sconosciuta.

Formula di funzione STUDENTE DI FIDUCIA come segue:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

Analizziamo l'applicazione di questo operatore sull'esempio della stessa tabella. Solo ora non conosciamo la deviazione standard in base alle condizioni del problema.

1. Innanzitutto, seleziona la cella in cui prevediamo di visualizzare il risultato. Quindi fare clic sull'icona “Inserisci funzione” (a sinistra della barra della formula).
2. Si aprirà la già nota finestra della procedura guidata. Scegli una categoria “statistico”, quindi dall'elenco di funzioni proposto, fare clic sull'operatore “STUDENTE DI FIDUCIA”, poi - OK.
3. Nella finestra successiva, dobbiamo impostare gli argomenti della funzione:
   • Nel "Alfa" come nel primo metodo, specificare il valore 0,05 (o “100-95)/100”).
   • Passiamo all'argomento. “std_off”. Poiché in base alle condizioni del problema, il suo valore ci è sconosciuto, dobbiamo fare gli opportuni calcoli, in cui l'operatore "DEV.ST.B”. Fare clic sul pulsante Aggiungi funzione e quindi sull'elemento "Più funzioni...".
   • Nella finestra successiva della procedura guidata delle funzioni, selezionare l'operatore "DEV.ST.B” nella categoria “statistico” e fare clic su OK.
   • Entriamo nella finestra delle impostazioni degli argomenti della funzione, la cui formula è simile a questa: =СТАНДОТКЛОН.В(число1;число2;...). Come primo argomento, specifichiamo un intervallo che include tutte le celle nella colonna "Valore" (senza contare l'intestazione).
   • Ora devi tornare alla finestra con gli argomenti della funzione “FIDUCIA.STUDENTE”. Per fare ciò, fare clic sull'omonima scritta nel campo di immissione della formula.
   • Passiamo ora all'ultimo argomento “Size”. Come nel primo metodo, qui puoi semplicemente specificare un intervallo di celle o inserire l'operatore "DAI UN'OCCHIATA". Scegliamo l'ultima opzione.
   • Dopo aver compilato tutti gli argomenti, fare clic sul pulsante OK.
4. La cella selezionata visualizzerà il valore dell'intervallo di confidenza in base ai parametri specificati.
5. Successivamente, dobbiamo calcolare i valori dei confini CI. E per questo è necessario ottenere il valore medio per l'intervallo selezionato. Per fare ciò, applichiamo nuovamente la funzione “CUORE". L'algoritmo delle azioni è simile a quello descritto nel primo metodo.
6. Dopo aver ricevuto il valore “CUORE", puoi iniziare a calcolare i limiti della CI. Le formule stesse non sono diverse da quelle usate con “NORME DI FIDUCIA”:
   • Bordo destro CI=MEDIA+FIDUCIA DELLO STUDENTE
   • CI legato a sinistra=FIDUCIA MEDIA-STUDENTE

Conclusione

L'arsenale di strumenti di Excel è incredibilmente ampio e, insieme alle funzioni comuni, il programma offre un'ampia varietà di funzioni speciali che renderanno molto più semplice il lavoro con i dati. Forse i passaggi sopra descritti possono sembrare complicati per alcuni utenti a prima vista. Ma dopo uno studio dettagliato del problema e della sequenza delle azioni, tutto diventerà molto più semplice.