La statistica con Excel

Ragazzi (e lettori interessati, viste le chiavi di ricerca...),
propongo qui la prima di una serie di esercitazioni sull'uso di Excel per la statistica.

Prendiamo come esempio un campione costituito dai voti (giudizi) ottenuti dagli alunni di una classe in una verifica di Inglese.
Compilazione della tabella dei dati.
L'insegnante ha svolto una verifica, ottenendo i risultati che vedi in figura (immettili anche tu in un foglio di lavoro, poi segui passo a passo tutte le fasi del lavoro, completando via via)

Digita, come in figura, le intestazioni di colonna; in cella A1: Alunno (n° progressivo o se vuoi, i nomi) e in B1: Voto verifica, quindi i dati. Salva il foglio con il nome Statistica.
Frequenza assoluta e Frequenza relativa
Siamo curiosi di verificare quanti alunni hanno avuto Buono, quanti Suff, Distinto ecc ?
Questa informazione si chiama frequenza.
La frequenza è il numero di unità statistiche (elementi dell'insieme oggetto di indagine statistica, insieme chiamato popolazione statistica), che presentano il carattere secondo una data modalità. Vale a dire il numero di casi osservati per una data modalità, della grandezza considerata, nel nostro caso, il voto della verifica.
La frequenza così intesa , si chiama frequenza assoluta.
Per calcolare la frequenza dobbiamo dapprima immettere in una colonna le classi dei dati, cioè l'elenco dei valori possibili, NS (non suff), S (suff), B (buono), D (distinto), O (ottimo).
Aggiungi le classi dei dati come in figura, in colonna C

Per determinare la frequenza assoluta ci serviamo della funzione CONTA.SE() (una delle funzioni che in Excel troviamo nella categoria Statistiche), che in questo blog abbiamo già incontrato .
In cella D1 immetti l'intestazione: Frequenza assoluta
In cella D2 immetti la formula:

Stiamo istruendo il programma: calcola nell'intervallo B2:B21, il numero di celle che contengono il dato contenuto in cella C2.
Utilizziamo per l'intervallo dei dati i riferimenti assoluti, per riferirci al medesimo intervallo (B2:B21), variando il criterio, in cella C2, riferimento relativo.
Dopo aver immesso la formula, selezionata la cella D2, posiziona il puntatore del mouse sul quadratino di riempimento e trascinalo fino alla cella D6 (stai copiando la formula lungo la colonna). L'indirizzo C2, verrà man mano modificato in C3, C4 ecc. Ottieni i valori come in figura:

Ora sai dunque che 2 alunni hanno preso il voto NS, 7 alunni la sufficienza, S, ecc.

Nelle indagini statistiche è utile tuttavia determinare la frequenza relativa.
La frequenza relativa è il rapporto tra la frequenza assoluta degli elementi che presentano una determinata modalità del carattere di interesse e il totale degli elementi da analizzare.
Nel nostro esempio, volendo confrontare i voti relativi a 2 classi con diverso numero di alunni, non potremmo dire "quale classe è più o meno brava" basandoci solo sulle frequenze assolute.
Dobbiamo considerare cioè il numero dei Buono o dei Distinto, sul totale del numero di voti, che corrisponde al numero di alunni.
Il semplice rapporto tra una frequenza e il totale delle unità statistiche considerate ci dà le frequenze relative unitarie.
Possiamo anche esprimerle in percentuale (frequenze relative percentuali).
Per il calcolo della frequenza relativa si può determinare (quando necessario, nel nostro esempio abbiamo un numero limitato di elementi da analizzare e in più è conosciuto!), la dimensione del campione mediante le funzioni CONTA.NUMERI() o CONTA.VALORI().
CONTA.NUMERI() calcola il numero di celle totali che non siano vuote e che contengano valori numerici.
La sintassi è semplice:

CONTA.NUMERI(val1;val2;...)
Val1; val2; ... sono da 1 a 30 argomenti che possono contenere dati di diverso tipo, di cui vengono contati soltanto i numeri.

Nel nostro esempio non abbiamo valori numerici, i voti sono espressi mediante giudizi sintetici, che esprimono un carattere qualitativo perciò utilizziamo la funzione CONTA.VALORI().
Questa funzione calcola il numero di celle totali che non siano vuote e di valori presenti nell'elenco degli argomenti.
E' idonea al calcolo della dimensione dei campioni che contengano variabili qualitative.
Sintassi

CONTA.VALORI(val1;val2;...)

Val1; val2; ... sono da 1 a 30 argomenti che rappresentano i valori che si desidera contare.

Aggiungiamo dunque ancora una colonna alla nostra tabella, con intestazione: Frequenza relativa.
In cella E2 immetti la formula:

Istruiamo il programma: calcola il quoziente (il rapporto) tra la frequenza assoluta e il numero totale di unità statistiche, i valori presenti nell'intervallo B2: B21.
Anche qui usiamo i riferimenti assoluti (intervallo dati) e relativi.
Copia la formula lungo la colonna E fino a E6.
Applica alla cella il formato: percentuale [Menu Formato, Celle... Scheda Numero, scegli Categoria: percentuale]
Ottieni i dati come in figura:

Nella cella D7 immetti la formula:
=SOMMA(D2:D6)
da trascinare orizzontalmente in E7.
Al prossimo post vedremo come rappresentare graficamente la tabella di frequenza.

[Excel]Funzioni condizionate: SOMMA.SE()

Dopo CONTA.SE(), conosciamo ora un'altra funzione condizionata.
Sappiamo che in Excel si può usare la funzione SOMMA() per sommare il contenuto di un intervallo di celle.
Esiste una funzione, denominata SOMMA.SE, che esegue delle somme parziali, somma cioè solo i termini che soddisfano una condizione (Criteri).
Vediamone la sintassi
SOMMA.SE()
somma le celle specificate secondo un criterio assegnato.

Sintassi
SOMMA.SE(intervallo;criteri;int_somma)

Intervallo è l'intervallo di celle al quale si applicano le condizioni, l'intervallo da "analizzare".
Criteri sono i criteri, la condizione in base alla quale il programma sceglie quali celle verranno sommate.
Criteri può essere espresso sotto forma di valore numerico, riferimento di cella, relazione o testo, ad esempio, come 32, A1, "32", ">32", "mele".
Le celle da sommare sono quelle di Int_somma.

Osservazione
Le celle in int_somma vengono sommate solo se le celle corrispondenti in intervallo soddisfano i criteri.

Proviamo ora ad applicare la funzione SOMMA.SE() alla tabella ottenuta applicando la funzione CONTA.SE() nel problema illustrato nel post.

Supponiamo di voler sommare i componenti delle ultime due squadre.
Posizioniamoci in cella I2 e immettiamo la formula:
=SOMMA.SE(E2:E4;">A";F2:F4)
E2:E4 è l'intervallo da analizzare;
se il contenuto delle celle soddisfa la condizione, il criterio: "essere maggiore di A"
allora vengono sommate le celle corrispondenti dell'intervallo F2:F4 (Int_somma)
Il risultato in cella I2 è 13.

Nota a proposito di ">A", confronto fra caratteri.
i caratteri a disposizione sulla tastiera, sono così ordinati dal minore al maggiore:
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, (spazio), !, ", #, $, %, &, (, ), *, ",", ., /, :, ?, @, [, \, ], ^, _, +, <, =, >, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z (è indifferente che le lettere siano scritte in maiuscolo o in minuscolo)
Due parole o due testi alfanumerici vengono ordinati in base al confronto eseguito a partire dal primo carattere diverso. Es: Sandra è minore di Sandro e AB12 è maggiore di AB1oo (non posso qui usare i simboli maggiore e minore: sono interpretati come tag del HTML).

Ancora un'osservazione per ciò che riguarda la funzione SOMMA.SE
Se l'argomento Int_somma è omesso, verranno sommate le celle in Intervallo.
Per esempio se in un intervallo contenente dei valori numerici, volessimo sommare solo i valori maggiori di un determinato valore:

Il terzo argomento della funzione è omesso. Sono sommati solo i valori maggiori di 6 dell'intervallo A2:A15.

Nel file da scaricare ho integrato con la funzione SOMMA.SE(), il problema già visto con CONTA.SE(). Contiene un secondo foglio di lavoro con altri esempi.
FILE AGGIORNATO: contiene esempi con 2 criteri

[Excel]Funzioni condizionate: CONTA.SE()

Diversi nostri lettori mostrano interesse per le funzioni di Excel e per i primi passi con l'applicativo.
In questi giorni di vacanza dalla scuola posso dedicarmi alla presentazione di qualche interessante funzione. Naturalmente, per lettori e pargoli (alunni)! :-)

Parliamo dunque di

funzioni condizionate.

Tali funzioni operano e forniscono un risultato solo dopo aver verificato che gli argomenti soddisfano a determinate condizioni richieste dall'utente.
Una di queste funzioni è CONTA.SE()
Un problema:
In un campeggio estivo per ragazzi da 9 a 16 anni si sono costituite 3 squadre, denominate A, B e C.
Della squadra A fanno parte i ragazzi da 9 a 11 anni,
della B quelli da 12 a 14 anni e
della C i ragazzi da 15 a 16 anni.
Dalla tabella in Excel che associa ogni ragazzo a una squadra vogliamo ottenere il numero di componenti di ciascuna squadra. In seguito vedremo anche come ottenere la somma dei componenti di due o tre squadre a scelta.
In un foglio di lavoro prepariamo una tabella:

Risolviamo il problema di contare quanti sono i componenti di ciascuna squadra:
In cella E1 digitiamo Squadra e nelle celle sottostanti, uno per cella, il nome delle squadre: A, B, C.
In F1 digitiamo Componenti
Per ottenere in F2 il numero di componenti la squadra A, si dovrebbe contare il numero di A presenti nella colonna Squadra della nostra tabella; a questo pensa Excel con la funzione CONTA.SE()
Vediamo la sintassi della funzione.
CONTA.SE() conta il numero di celle in un intervallo che soddisfano i criteri specificati.

Sintassi
CONTA.SE(intervallo;criteri)

Intervallo è l'intervallo di celle dove bisogna cercare gli elementi da contare.
Criteri sono i criteri, la condizione espressa sotto forma di valore numerico, riferimento di cella, relazione o testo, in base alla quale il programma sceglie quali elementi dell'intervallo contare, cioè quali celle verranno contate.
Ad esempio, criteri può essere espresso come 32, A1, "32", ">32", "mele".

Tornando al nostro problema, in cella F2 immettiamo dunque la formula:
=CONTA.SE($C$2:$C$21;"A")
Il nostro intervallo è $C$2:$C$21 (riferimenti assoluti, per riferirci al medesimo intervallo, variando il criterio)
Il criterio, la condizione, è la lettera A, che indica la squadra (osserva: A è un testo, quindi va messo tra virgolette).
Per il calcolo dei componenti le squadre B e C, dobbiamo ripetere in F3 e F4 la stessa formula cambiando solo l'argomento del criterio.
Il risultato ottenuto sarà questo:

Ma... non sappiamo noi della "praticità" di Excel? :-)
Per calcolare il numero di componenti delle tre squadre abbiamo dovuto cambiare l'argomento criterio nella formula!
Più rapidamente (ma è così che va utilizzato Excel!) saremmo giunti allo stesso risultato con la formula immessa in questo modo:
=CONTA.SE($C$2:$C$21;E2)
l'argomento criterio è il contenuto della cella E2, cioè la lettera A (E2, rif. relativo, il criterio cambia copiando la formula lungo la colonna).
Si copia semplicemente dunque la formula nelle due celle sottostanti, F3 e F4 e il gioco è fatto!
in cella F3 E2 diventerà E3, e in F4, E4.

Ma non è finita!
Si è detto che l'argomento criteri può essere anche una relazione. Possiamo perciò utilizzare gli operatori di confronto.
La funzione CONTA.SE() ci permette allora qualche altra indagine (ricordo che CONTA.SE() è una delle funzioni appartenenti alla categoria Statistiche): conoscere per es. quanti sono i ragazzi del campeggio di età maggiore di 11 anni.
In una cella immettiamo la formula:
=CONTA.SE($B$2:$B$21;">11")
L'intervallo è quello della colonna B della tabella, contenente le età, il criterio maggiore di 11, va messo tra virgolette.
Il risultato ottenuto è 13.
Ancora un esempio:
vogliamo conoscere il numero di ragazzi di età maggiore o uguale a 14.
La formula in questo caso è:
=CONTA.SE($B$2:$B$21;">=14")
Otteniamo il risultato 9.
Si può scaricare il file d'esempio CONTA.SE()

Voglio segnalare infine che il problema del calcolo dei componenti le tre squadre si potrebbe risolvere anche evitando l'utilizzo della colonna C della tabella. NON con la funzione CONTA.SE() però! Si dovrebbe utilizzare il calcolo matriciale che noi conosciamo un po'. Potremmo esemplificare in uno dei prossimi post!:-)
AGGIORNAMENTO
In riferimento alla nota qui sopra: non volendo ricorrere alle formule matriciali per calcolare il numero dei componenti delle squadre considerando esclusivamente le fasce d'età, si può utilizzare un "doppio" CONTA.SE. [Le funzioni CONTA.SE e SOMMA.SE non consentono di specificare criteri multipli come ad esempio un intervallo di valori compreso tra un valore minimo e un valore massimo]
La soluzione, insieme ad altri esempi di SOMMA.SE e CONTA.SE con 2 criteri, si può vedere sul file aggiornato Conta.Se()_Somma.se()_2.xls
A disposizione per eventuali chiarimenti.