Guida agli strumenti di analisi

Questa è una guida introduttiva agli Strumenti di analisi, uno dei componenti aggiuntivi classici preinstallati in Excel, che offre un set di strumenti per ampliare le capacità del foglio di calcolo nella risoluzione di problemi statistici.

Cos’è l’analisi dei dati?

L’analisi dati è l’elaborazione di una base di dati con lo scopo di studiarne le caratteristiche, evidenziare ed estrarne valori e informazioni significative, creare modelli su cui basare interpretazioni, predizioni o decisioni. Per realizzare un’analisi dei dati si possono applicare diverse tecniche per i diversi ambiti e scopi.

Per esempio, la “business intelligence” mira all’analisi dei dati in ambito aziendale e si basa principalmente sull’aggregazione dei dati, il “data mining” mira invece all’estrazione di un’informazione a partire da grandi quantità di dati in ambito scientifico e industriale per scopi descrittici e predittivi e si basa primariamente su estrazione ed analisi sistematica dei dati.

L’analisi statistica dei dati può essere descrittiva, esplorativa, di conferma e predittiva. La più nota è l’analisi descrittiva che si occupa della rilevazione, classificazione, sintesi e rappresentazione dei dati ottenuti dallo studio di una popolazione o di un campione di essa. Quella esplorativa si occupa della rilevazione delle diversità, anomalie o caratteristiche anomale presenti nei dati, mentre l’analisi di conferma si occupa specificatamente di confermare delle ipotesi esistenti. Infine l’analisi predittiva si concentra sull’applicazione di modelli statistici con lo scopo di formulare predizioni.

Quali sono gli strumenti di Excel per l’analisi statistica?

Il primo strumento per l’analisi dei dati sono le tabelle pivot.

Il primo strumento per l’analisi dei dati sono le tabelle pivot, uno strumento potente e flessibile con cui è possibile realizzare analisi preliminari e descrittive. Le tabelle pivot permettono di eseguire calcoli semplici (sommare, contare, media, minimo e massimo, deviazione standard, varianza e relative percentuali) su grandi basi dati, che possono filtrare, ordinare, raggruppare secondo le categorie dei diversi campi. Sono strumenti efficienti con cui realizzare analisi soddisfacenti nella maggior parte degli ambiti aziendali e professionali.

Per approfondire l’utilizzo delle tabelle pivot ti rimando alla “Guida introduttiva alle tabelle pivot”, al tutorial sulle tabelle pivot. e ai principali post dedicati alle pivot: “Come creare le tabelle pivot”, “Come giostrare con le pivot” e “Usa le pivot come un professionista” .

Il secondo strumento di Excel sono le funzioni statistiche.

Le funzioni statistiche sono strumenti potenti e rodati con cui possiamo elaborare i dati in modo preciso e specifico. In ambito statistico propongono un’ampia serie di strumenti con cui calcolare i parametri statistici più semplici come media, mediana, deviazione standard, varianza, frequenza, rango, percentile, correlazione, eccetera).

Queste sono alcune delle più significative:

1. MEDIA()                    calcola la Media di un insieme di dati
2. MODA()                    calcola la Moda di un insieme di dati
3. MEDIANA()              calcola la Mediana di un insieme di dati
4. DEV.ST()                    calcola la Deviazione standard dei dati
5. VAR()                         calcola la Varianza dei dati
6. QUARTILE()               calcola il Quartile Q dei dati
7. e da questo è possibile calcolare la di Distanza interquartile:  QUARTILE(DATI,3)-QUARTILE(DATI,1)
8. MIN()                         calcola il valore Minimo
9. MAX()                        calcola il valore Massimo
10. e da questi è possibile calcolare il Range: MAX()-MIN()
11. FREQUENZA()           calcola la Frequenza assoluta
12. RANGO.UG()             calcola il rango dei dati
13. CORRELAZIONE()     calcola il coefficiente di correlazione tra 2 variabili
14. TESTT()                       la probabilità che due campioni appartengano alla stessa popolazione

Per approfondire le funzioni con cui fare analisi statistica ti invito a leggere questi post sulle funzioni: qui, qui e qui; ti invito anche a consultare le pagine dedicate del sito microsoft e a consultare le fonti riportate nelle pagina “Risorse utili per imparare Excel”.

Il terzo strumento sono gli Strumenti di analisi.

Gli Strumenti di analisi sono un componente aggiuntivo storico di Excel, a nostra disposizione anche nelle ultime versioni, che offre un set di strumenti con cui eseguire calcoli e analisi statistica. Lo puoi trovare nella scheda “Dati” della barra multifunzione, gruppo “Analisi”, comando “Analisi dati”.

Cliccando sul comando “Analisi dati” si aprirà la finestra degli “Strumenti di analisi”, un semplice elenco da cui selezionare quello che ci serve e lanciarlo cliccando sul pulsante “Ok”. Si aprirà quindi la finestra relativa da cui potremo applicare lo strumenti ai dai che vogliamo analizzare.

Gli strumenti di analisi sono un componente aggiuntivo preinstallato, ma se non lo trovi è possibile che tu lo debba attivare attraverso la finestra dei componenti aggiuntivi che puoi richiamare attraverso il comando “Componenti aggiuntivi di Excel” che in excel 2016 trovi nella scheda “Sviluppo”, oppure usando la finestra “Opzioni”, scheda “Componenti aggiuntivi”, richiamabile dalla scheda “File”. Una volta attivati li avrai sempre a disposizione nella barra principale come descritto sopra.

Nelle vecchie versioni di Excel può succedere talvolta di non trovare il componente nella lista, sarà quindi necessario cercarlo cliccando sul “Sfoglia” nella finestra “Componenti aggiuntivi” e, se non installato, sarà necessario seguire le istruzioni per l’installazione.

Per maggiori approfondimenti su cosa sono e come installare i componenti aggiuntivi, ti invito a leggere questa guida ai componenti aggiuntivi e a guardare il tutorial relativo.

Strumenti di analisi

Cliccando sul comando “Analisi dati” della scheda “Dati” della barra multifunzione si apre la finestra degli “Strumenti di analisi”, un semplice elenco da cui selezionare quello che ci serve e lanciarlo cliccando sul pulsante “Ok”. Di conseguenza si aprirà la finestra di controllo relativa da cui potremo applicare lo strumento scelto ai dati che vogliamo analizzare indicando i parametri richiesti.

Il pacchetto è come un coltello svizzero, composto da una lunga serie di strumenti da utilizzare per i propri scopi:

1. Analisi varianza a un fattore
2. Analisi varianza a due fattori con replica
3. Analisi varianza a due fattori senza replica
4. Correlazione
5. Covarianza
6. Statistica descrittiva
7. Smorzamento esponenziale
8. Test F a 2 campioni per varianze
9. Analisi di Fourier
10. Istogramma
11. Media mobile
12. Generazione di un numero casuale
13. Rango e percentile
14. Regressione
15. Campionamento
16. Test t: 2 campioni accoppiati per medie
17. Test t: 2 campioni assumendo uguale varianza
18. Test t: 2 campioni assumendo varianze diverse
19. Test z: 2 campioni per medie

Se conosci la statistica avrai già intuito l’impiego di ogni strumento dal suo nome. Selezionando ognuno di essi si aprirà la relativa finestra operativa con cui è possibile applicarli indicando i parametri richiesti.

Qui di seguito trovi una breve presentazione degli strumenti a disposizione:

Media mobile

La media mobile fornisce informazioni sulla tendenza, cioè come varierà nel tempo un parametro, ed è calcolata come media dei valori del parametro per un preciso numero di periodi precedenti. Lo strumento richiede l’intervallo dei dati, il numero di periodi e dove posizionare l’output.

È uno strumento previsionale semplice utilizzato per fare previsioni sul breve periodo per esempio sulle vendite, i magazzini e i consumi. La sua sensibilità dipende dal numero di periodi considerati, più sono e meno sensibile è alle variazioni e viceversa, per questo è necessario individuare il numero ideale, per tentativi o per confronto.

E’ facilmente replicabile con la funzione Media() impostata su un intervallo mobile e personalmente preferisco la funzione allo strumento.

Statistica descrittiva

Strumento comodo e potente, lo indirizzi sull’intervallo dei dati del parametro da analizzare e lo strumento calcola e produce un minireport con: Media, Errore standard, Mediana, Moda, Deviazione standard, Varianza, Curtosi, Asimmetria, Intervallo, Minimo, Massimo, Somma, Conteggio.

Quello che ti serve per farti un’idea della distribuzione dei valori del parametro in esame. Pratico, veloce ed efficace. Lo consiglio, provalo. Puoi ottenere lo stesso con funzioni e formule, ma con più tempo e sforzi.

Generatore di numeri casuale

Genera numeri causali a piacere e indipendenti estratti da una delle distribuzioni possibili. Richiede la quantità di numeri da generare, la distribuzione a cui riferirsi, eventuali caratterizzazioni e dove posizionare l’output.

È comodo e veloce e sappiamo entrambi quanto, in certe situazioni, è utile avere una serie di numeri casuali su cui lavorare.

Campionamento

Lo strumento crea un campione estraendo un certo numero di valori dalla popolazione del parametro in esame. Si imposta l’intervallo della popolazione, si scegli il metodo, se casuale o periodico, e dove posizionare l’output.

Utile in presenza di una popolazione troppo grande per essere elaborata o rappresentata in un grafico. Un metodo di campionamento pratico e veloce che elimina l’influenza della mano umana.

Regressione

Lo strumento esegue l’analisi della regressione lineare con il metodo dei minimi quadrati, o più semplicemente adatta una retta a una serie di punti in un grafico xy. Serve a valutare il comportamento di una variabile indipendente su una variabile dipendente, con lo scopo di riuscire a realizzare una stima del valore atteso. Per esempio possiamo usarla per valutare e prevedere la vita di funzionamento di uno strumento idraulico in relazione ai materiali, le dimensioni e altri fattori.

Si impostano i parametri richiesti: valori di X, valori di Y, eventuale livello di confidenza e dove posizionare l’output. Il report di output è ampio ed esaustivo, arriva perfino al grafico delle probabilità.

Istogramma

Lo strumento Istogramma consente di calcolare le frequenze individuali e cumulative relative a un campo, i cui valori sono raccolti in un intervallo di celle, allo scopo di ottenere la relativa distribuzione.

Per esempio è possibile determinare la distribuzione dell’età della popolazione di una città, dove il valore è l’età e il numero dei valori è dato dal numero di abitanti. La distribuzione dell’età può essere calcolata per ogni singolo valore dell’età (es: da 1 a 120 anni, 1, 2, 3, ecc) oppure per intervalli definiti (o classi, per esempio: da 1 a 5 anni, da 6 a 10 anni, eccetera).

I valori e le classi devono essere inseriti in due intervalli di celle che dovranno essere indicati nella finestra dello strumento, impostando poi le opzioni di output. Per esempio, riprendendo l’esempio precedente nelle celle B2:B13654 ci saranno i valori dell’età degli abitanti, mentre nelle celle F2:F127 ci saranno le età da valutare, cioè i singoli anni da 0 a 125 (in alternativa potrebbe indicare solo gli anni pari, come 0, 2, 4, 6, ecc). Consiglio sempre di impostare l’opzione grafico e la percentuale cumulativa.

Smorzamento esponenziale

Lo smorzamento esponenziale consente di fare una previsione di un valore, basandosi sulla previsione per il periodo precedente e la correzione dell’errore della previsione precedente. A differenza della media mobile che fa previsioni valutando un certo numero di valori precedenti, lo smorzamento esponenziale si appoggia all’intera serie storica.

La previsione è regolata da una costante di smorzamento (a) che definisce la relazione tra le previsioni e gli errori delle previsioni precedenti, o meglio definisce la correzione da applicare alla previsione basandosi sugli errori delle previsioni precedente.

Questa costante è decisa da noi, varia da 0 a 1 e di solito è regolata tra 0,2 e 0,3, cioè il 20 e il 30% della previsione e rappresenta la reattività dello strumento alle variazioni. Consiglio di partire da 0,3 e di valutare con l’esperienza quale valore della costante sia il più adatto al parametro sotto osservazione.

Analisi di Fourier

Lo strumento Analisi di Fourier permette di eseguire un’analisi armonica su dati periodici per risolvere problemi di sistemi lineari. In poche parole la trasformata di Fourier consente di scomporre un’onda qualsiasi, anche complessa, nelle sue onde componenti, che sommate tra loro creano il segnale di partenza, e permette di calcolare ampiezza, fase e frequenza di queste componenti. Per la trasformazione dei dati lo strumento utilizza l’algoritmo l’FFT (Fast Fourier Transform), che riesce a ridurre la complessità della trasformata di Fourier, o meglio del relativo calcolo computazionale.

Questo strumento può essere utilizzato in diversi modi, per esempio per individuare un criterio per compiere un campionamento, oppure per iniziare o migliorare un’indagine statistica sulle possibili cause di fenomeni rilevati. Lo strumento di Excel supporta anche le trasformazioni inverse, cioè partendo dai dati delle componenti restituisce i dati originali.

Rango e percentile

Lo strumento Rango e percentile genera una tabella contenente il rango ordinale e percentuale di ogni valore di un set di dati con cui è possibile analizzare la posizione relativa dei valori in un set di dati. In parole più semplici calcola la posizione del valore nell’elenco. Questo strumento usa le funzioni di Excel RANGO.UG() e INC.PERCENT.RANGO(): la prima restituisce il rango di un valore in un elenco di numeri, ovvero la sua grandezza relativa rispetto agli altri valori nell’elenco, la seconda funzione restituisce il rango come percentuale del set di dati.

Varianza

Gli strumenti di analisi offrono diversi tipi di analisi della varianza. In statistica la varianza di una variabile è una funzione (es: Var(x) ) che fornisce la misura della variabilità dei valori della variabile, o meglio la misura di quanto si discostino dalla media aritmetica (quadraticamente). La scelta dello strumento dipende dal numero di fattori e di campioni di cui si dispone relativi alle popolazioni che si vuole verificare.

1. Varianza a un fattore

Lo strumento esegue una semplice analisi della varianza dei dati di due o più campioni. In sintesi verifica l’ipotesi secondo cui ogni campione fa parte della stessa distribuzione di probabilità rispetto all’ipotesi alternativa secondo cui le distribuzioni di probabilità sottostanti non sono uguali per tutti i campioni.

Se i campioni sono solo due è possibile usare la funzione TEST.T() di Excel che appunto restituisce la probabilità che due campioni possono essere derivati dalla stessa popolazione.

2. Varianza a 2 fattori con replica

Utile per calcolare la varianza dello stesso valore, ma per due fattori correlati. Per esempio, l’analisi della varianza dell’altezza della popolazione adulta di una città in relazione all’alimentazione e al paese d’origine. Lo strumento verifica la varianza dei valori per singolo fattore e per coppie di fattori.

3. Varianza a 2 fattori senza replica

Analogo al precedente, ma limitato a un solo valore per coppia di fattori.

Correlazione

La correlazione in statistica è la relazione tra due variabili, cioè la tendenza di una variabile a cambiare in funzione di un’altra. Se le due variabili crescono insieme la correlazione è positiva, se viceversa una cresce e l’altra diminuisce la correlazione è negativa.

In excel è possibile misurare la correlazione usando le funzioni CORRELAZIONE() e PEARSON(). Queste calcolano il coefficiente di correlazione tra due variabili quando per ogni soggetto vengono rilevate le misurazioni di ogni variabile, altrimenti il soggetto verrà ignorato nell’analisi.

Lo strumento Correlazione permette di misurare la correlazione tra più di due variabili di misura per ogni soggetto N. Lo strumento genera una matrice di correlazione che riporta il coefficiente di correlazione. Il coefficiente di correlazione misura la varianza di due variabili ed è indipendente dalle unità di misura in cui vengono espresse le variabili. Il valore di qualsiasi coefficiente di correlazione deve essere compreso tra -1 e +1. Se non esiste correlazione tra i valori delle due variabili il coefficiente è prossimo a zero.

Covarianza

In statistica la covarianza di due variabili è un valore che fornisce la misura della loro dipendenza, cioè di quanto le due varino assieme. Il coefficiente di correlazione e la covarianza misurano entrambi l’estensione della varianza di due variabili che “variano insieme”, con la differenza che i coefficienti di correlazione vengono “adattati” in modo da essere compresi tra -1 e +1, mentre le covarianze non vengono adattate.

Anche la Covarianza produce una matrice che riporta appunto la covarianza tra ogni coppia di variabili di misura. Questo valore in Excel è calcolabile anche attraverso la funzione COVARIANZA.P(), ma tra solo due variabili (N=2). In modo analogo alla correlazione, la covarianza indica quale tipo di dipendenza sussiste tra le variabili (positiva, negativa o nessuna).

Test F

Il test F per il confronto di due varianze è un test parametrico basato sulla distribuzione F di Fisher-Snedecor, volto a verificare l’ipotesi che due popolazioni con distribuzioni normali abbiano la stessa varianza.

In sostanza lo strumento confronta le varianze di due popolazioni, per esempio la vita media delle popolazioni di due città diverse. Lo strumento calcola il valore f e quando è prossimo a 1 allora le varianze delle popolazioni sono uguali.

Test Z

In statistica il test Z verifica se il valore medio di una distribuzione si discosta significativamente da un valore di riferimento. Lo strumento “Test Z 2 campioni per medie” verifica l’ipotesi secondo cui non esiste differenza tra due medie di popolazione eseguendo un test z a due campioni con varianze note. Se le varianze non sono note, è più opportuno usare la funzione di Excel TEST.Z() che esegue lo stesso calcolo.

Lo strumento può anche essere usato per valutare le differenze tra le medie delle due popolazioni in rapporto a un valore di riferimento. Per esempio per determinare le differenze di prestazioni tra due automobili.

Test T

In statistica il test t è un test parametrico con lo scopo di verificare se il valore medio di una distribuzione si discosta significativamente da un certo valore di riferimento. A differenza del Test z non richiede di conoscere la varianza.

Gli strumenti a disposizione eseguono il test su due campioni di due popolazioni diverse per verificare l’uguaglianza delle medie, partendo da presupposti diversi:

1. che le varianze della popolazione siano uguali,
2. che le varianze della popolazione non siano uguali,
3. che i due campioni rappresentino osservazioni degli stessi soggetti.

1. Test t su 2 campioni accoppiati per medie

Usato tipicamente per verificare la corrispondenza delle medie di due rilevazioni prese sullo stesso campione in tempi diversi, esempio prima e dopo un evento o un esperimento. Tra i risultati generati troviamo la varianza complessiva, una misura cumulativa della distribuzione dei dati rispetto alla media. Questo test non presuppone che le varianze di entrambe le popolazioni siano uguali.

2. Test t su 2 campioni assumendo uguale varianza

Questo strumento esegue un test t di Student a due campioni che presuppone che i due set di dati derivino da distribuzioni con le stesse varianze. Si può usare per determinare la probabilità che i due campioni derivino da distribuzioni con medie di popolazione uguali.

3. Test t su 2 campioni assumendo varianze diverse

Questo esegue un test t presupponendo che i due set di dati derivino da distribuzioni con varianze diverse. Anche questo strumento può essere usato per determinare la probabilità che i due campioni derivino da distribuzioni con medie di popolazione uguali.  Attenzione è possibile che i risultati dello strumento e della funzione di Excel TEST.T() possano essere diversi nel caso di varianze uguali, in quanto usano approcci diversi nella definizione dei gradi di libertà.

Conclusione?

No, la guida non è ancora conclusa. Questa è solo la presentazione degli strumenti di analisi presenti in Excel, ma la guida non è ancora completa. Nella seconda parte di questa guida vedremo come si utilizzano gli strumenti presentati e viste le richieste pervenute integreremo anche una breve introduzione alla statistica e vedremo anche come si raccolgono, gestiscono e analizzano i dati.

Spero con questa piccola guida di averti dimostrato l’utilità di questi strumenti per l’analisi statistica. No, Excel non è il miglior strumento con cui fare analisi statistica, sono altri gli strumenti professionali per fare analisi statistica, ma se le tue esigenze sono più limitate e semplici, come hai visto Excel offre notevoli possibilità per eseguire calcoli statistici di base e non solo. Per questo ti invito a studiare e usare questi strumenti.

Se questa guida ti è piaciuta o ti è stata utile, condividila con gli amici sui social.
Puoi farlo attraverso i pulsanti qui di seguito.
Grazie!

Per comodità tua e mia puoi scaricare il PDF della guida tramite Gumroad

Devi solo cliccare sul pulsante sottostante e seguire le istruzioni

Scarica la Guida

NB: non devi pagare, non è necessario lasciare la tua mail

Iscriviti a ExcelProfessionale per scoprire tutto quello che Excel può fare per te

Excel può fare per te più di quello che immagini.
Se vuoi scoprire e apprendere cosa puoi fare realmente con Excel, iscriviti a Excel Professionale usando la maschera seguente.

Riceverai la newsletter bimestrale, accederai ai contenuti gratuiti condivisi (fogli dimostrativi, macro, eccetera), potrai iscriverti gratuitamente ai video corsi introduttivi e ai webinar formativi, riceverai offerte dedicate ai corsi avanzati e molto altro.

PS: Qui puoi trovare le altre guide di Excel Professionale