riepilogo-funzioni

Prof. Folgieri aa 2008/09

Riepilogo delle principali funzioniExcel utilizzate

Prof. Raffaella Folgieri

Email [email protected]


Come usare questa guida Le funzioni presenti in queste slide sono quelle viste a

lezione e utili sia per la risuluzione degli esercizi, sia ai fini della certificazione IT4PS

In alto a destra riportato (riquadro colorato) il riferimento della lezione in cui la funzione stata trattata per la prima volta)

Inizialmente vengono introdotti alcuni argomenti di utilitgenerale e di base per poter utilizzare al meglio tutte le funzioni avanzate di Escel viste durante il corso.

E riportato sia il nome della funzione in Italiano sia quello in Inglese, cos che ciascuno possa scegliere, a seconda della versione di Excel che utilizza.


Formattazione celle

Formattazione del testo e dei singoli caratteri(dal menu Formato):

Orientamento a 45Allineamento orizzontale al centro

Protezione: bloccata o nascosta (per le formule)

Controllo testo: testo a capo

Argomento generale


Formattazione celleFormattazione condizionale (Conditional Formatting) (dal menu Formato):

Argomento generale


Vengono aggiornate automaticamente quando le celle cui fanno riferimento vengono modificate

Le formule

Le formule iniziano con loperatore = Possono contenere:

1.Costanti(numeri o stringhe) 2.Riferimenti(indirizzi di celle, es. A1) 3.Funzioni(operazioni predefinite)

Argomento generale


I riferimenti relativi

Si adeguano in relazione alle celle di partenza quando si copia o si sposta una formula

Argomento generale


I riferimenti assoluti Non comportano nessun adeguamento durante la copia della formula

Rif. assoluto: =$A$1

Rif. misto: =$A4

Rif. misto: =A$7

Copia della formula =A$7 nelle celle adiacenti

Argomento generale


Le funzioni Sono caratterizzate da un nome,un elenco di argomenti (arg1;arg2;), restituiscono un valorenella cella ospitante

Argomento generale


Inserimento di una funzione

Manualmente: ogni formula preceduta dal simbolo = e vengono utilizzati o i nomi di funzione (se noti) oppure i simboli matematici usuali (*,+,/,-). Vengono utilizzate le parentesi per indicare lordine di svolgimento dei calcoli.

In modo guidato: dal menu Inserisci/formula, trovando la formula desiderata e seguendo le indicazioni (help) di Excel per inserire gli argomenti necessari.

Argomento generale


Completamento automatico Se la stessa formula deve essere

riportata lungo tutta una colonna (o una riga):

Una volta inserita la formula, se selezionate la casella, a lato, in basso a destra, compare un puntino (maniglia)

Click e, tenendo premuto il pulsante sinistro del mouse (modalit drag and drop), trascinare la formula sulle caselle desiderate.

Excel riporter la formula aggiornando (vedere riferimenti relativi ed assoluti) tutti i riferimento.

Argomento generale


Esempi Argomento generale


Esempi Simbolo Tipo +-*

/ % ^

3+2, 6-1, 4*5, 8/2 20%, 10^2, 10*10 aritmetici

= > < => B1, A1B1 confronto

salva & gente salvagente

& concatenazione

B5:C15 riferim. (intervallo)

: A1;B15;C27;D2 ; riferim. (unione)

Operatori Argomento generale


Calcoli in forma di matrice La cella C16 contiene la formula in forma di matrice la quale trovale celle nell'intervallo C5:C14 contenenti il testo Europa, quindi calcola la media delle celle corrispondenti nell'intervallo D5:D14.Una Formula in forma di matriceesegue pi calcoli su uno o piinsiemi di valori e restituisce un unico risultato o pi risultati. Le formule in forma di matricevengono immesse prementoCTRL+MAIUSC+INVIO.

Argomento generale


Riferimento a fogli e cartelle E un modo per semplificare modelli complessi in pi cartelle di lavoro tra loro dipendenti

Per collegare formule in altri fogli di lavoro vengono usate due sintassi: con parantesi [] (aperto) o senza (chiuso)

Argomento generale


Inserimento di un Grafico(menu Inserisci)

Argomento generale


Intervallo dei dati Per selezionare le celle che contengono i dati a partire dai quali si intede realizzare il grafico.

Nel nostro caso le serie sono i valori rilevati per i vari inquinanti.

Quindi i dati relativi alle serie sono qulli compaiono nelle colonne.

Argomento generale


Per dare il nome alle serie

Si seleziona la cella che contiene il nome dellinquinante a cui la serie corrisponde.

Argomento generale


Titoli del grafico

Argomento generale


Posizionare la legenda Argomento generale


Posizionare il grafico

Il grafico pu essere posizionato nello stesso foglio che contiene i dati o in un foglio a parte.

Argomento generale


Dimensionare e posizionare Argomento generale


Dimensionare e posizionare Spostare: premere il tasto sinistro quando il cursore sullarea del grafico e, tenedolo premuto, trascinare il grafico. Dimensionare: selezionare il grafico; posizionare il cursore su uno dei quadratini che compaiono sulla cornice; compare una freccia che indica il verso del dimensionamento; premere il tasto sinistro e, tenedolo premuto, spostarlo nella direzione in cui si vuole modificare la dimensione.

Argomento generale


Modificare un grafico Posizionare il cursore sul grafico.

Premere il tasto destrodel mouse.

Si apre un mencontestuale, che fornisce i comandi per modificare le varie proprietdi un grafico.

Argomento generale


Aggiornamento in tempo reale

Le formule e i grafici vengono aggiornati in tempo reale.

Se vengono modificati i valori rilevati dalle centraline, vengono aggiornati media, allarmi, e i grafici.

Argomento generale


Come creare un modello a partireda un foglio Excel Se vogliamo conservare un foglio in cui abbiamoposto particolari funzioni per ulteriori usi futuri, ma, ad eempio, considerando dati diversi, possiamocreare un MODELLO. I passi da compiere sono i seguenti Selezionare la voce SALVA CON NOME (SAVE AS) dal menu FILEScegliere (nella tendina TIPO DI FILE) la voce MODELLO.

Argomento generale


Presentazione dei dati Per presentare i dati (contenuti nel foglio) in un modo pi chiaro o per evidenziarne la struttura, selezionare la voe FORMATTAZIONE AUTOMATICA dal menu FORMATO.E possibile scegliere tra vari formati predefiniti messi a disposizione da Excel.

Argomento generale


Analisi e valutazione delle formule Se si desidera analizzare le formule contenute in un foglio Excel,

possibile scegliere Verifica Formule dal menu Strumenti, e selezionare lopzione MOSTRA BARRA DELLE FORMULE:

Per vedere dove sono le formule, selezionare STRUMENTI/OPZIONI e VISUALIZZA-FORMULE

Ora, se si seleziona una cella contenente una formula, attraverso il bottone INDIVIDUA PRECEDENTI, si ottiene la visualizzazione di tutte le celle coinvolte nel calcolo.

Argomento generale


Analisi e valutazione delle formule Allo stesso modo, possibile individuare le celle dipendenti e

chiedere ad Excel di evidenziare le celle contenenti i dati non validi o mostrare una finestra in cui seguire la valutazione passo-passo dei risultati della formula.

Argomento generale


Import di dati E possibile importare in Excel dati provenienti da altre

fonti (ad es, database trovati in internet, fogli di testo. Per fare ci, sono possibili due strade:

1. Aprire il file dei dati dal menu FILE/Open, ma, nonostante Excel possa aprire molti tipi di file, non sempre questa operazione possibile (tipo di file non riconosciuto) o conveniente (non vengono conservate le formattazioni o il tipo di valore contenuto nel campo)

2. Sceglere lopzione IMPORTA DATI ESTERNI/IMPORTA DATI dal menu DATI. Comparir la procedura guidata che ci permetter di indicare il separatore tra i record (spesso il carattere di tabulazione, lo spazio, la virgola o il punto e virgola), fino allinserimento dei dati nel foglio di lavoro.

Argomento generale


TABELLA MONOVARIATA Struttura di una tabella monovariata (che dipende da un solo parametro):

Argomento generale


TABELLA BIVARIATA Struttura di una tabella bivariata (che dipende da due parametri, rif. tabella pivot):

Argomento generale


Sintassi della funzione SE (IF)

La funzione SE prende come argomenti: Una condizione

Il valore da restituire se la condizione soddisfatta

Il valore da restituire se la condizione non soddisfatta

Sintassi: =SE(condizione;valore_SE_si;valore_SE_no) Il valore restituito dalla funzione pu essere un numero oppure testo o un valore di errore (#VALORE!) Per inserire pi criteri, si utilizzano gli operatori logici AND, cherichiede che le due (o pi) condizioni siano soddisfatte (vere) contemporameneamente, oppure OR (almeno una delle due, o pi, condizioni deve essere vera)

LEZIONE 1


Finestra della formula

Nidificazione delle funzioni (max 7 livelli) (Il valore restituito da una funzione deve essere un parametro valido per laltra)

LEZIONE 1


Sintassi della funzione TASSO (RATE)La funzione TASSO prende come argomenti: Periodi: numero totale dei periodi di pagamento in un'annualitPagam: pagamento effettuato in ciascun periodo. Non pu variare nel corso dell'annualit. In genere, pagam include il capitale e gli interessi, ma non altre imposte o spese. Se si omette pagam, sar necessario includere l'argomento val_futuro.Val_attuale: il valore attuale o la somma forfettaria che rappresenta il valore attuale di una serie di pagamenti futuri.Val_futuro: il valore futuro o il saldo in contanti che si desidera raggiungere dopo aver effettuato l'ultimo pagamento. Se val_futuro omesso, verr considerato uguale a 0. Il valore futuro di un prestito, ad esempio, sar uguale a 0.Tipo: corrisponde a 0 o a 1 e indica le scadenze dei pagamentiImpostare tipo a 0 (oppure omettere) se i pagamenti devono essere effettuati alla fine del periodo. Impostare tipo a 1 se devono essere effettuati allinizio del periodo.Ipotesi l'ipotesi del valore del tasso. Se ipotesi omesso, verr considerato uguale a10%. Se i risultati di TASSO non convergono, provare a utilizzare dei valori differenti per ipotesi. In genere, i risultati di TASSO convergono se ipotesi compreso tra 0 e 1.

LEZIONE 1


Finestra della formula LEZIONE 1


Sintassi della funzione ASS (ABS)Restituisce il valore assoluto di un valore

La funzione ASS prende come argomenti:

Numero: casella in cui indicare il numero o la cella di cui si vuole

conoscere il valore assoluto. Pu contenere anche unoperazione di

cui si desidera il risultato in valore assoluto.

LEZIONE 1


Sintassi della funzione ASS (ABS)Restituisce il valore assoluto di un valore


Numero: casella in cui indicare il numero o la cella di cui si vuole

conoscere il valore assoluto. Pu contenere anche unoperazione di

cui si desidera il risultato in valore assoluto.

LEZIONE 1


Sintassi della funzione MIN (MIN)Restituisce il valore minimo di un insieme di valori (ignorando I

valori logici o testuali)


Lintervallo di celle in cui si desidera trovare il valore minimo.

LEZIONE 1


Sintassi della funzione SOMMA (SUM)Restituisce la somma dei valori considerati

I valori si possono sommare direttamente ponendo nella cella di destinazione

del risultato il riferimento alle celle, sommate tra loro con il simbolo di somma

(+)

In alternativa si usi la funzione di SOMMA AUTOMATICA, selezionabile dal

menu dei bottoni (il sigma maiuscolo, ovvero il simbolo di sommatoria) oppure

la funzione SOMMA dal menu INSERISCI/FUNZIONE


Lintervallo di celle di cui si desidera ottenere la somma.

LEZIONE 2


La funzione sum if (somma se) La funzione SUMIF serve per sommare tra loro i dati solo se soddisfano una certa condizione Vediamo come si comporta nellesempio riportato qui sotto:

LEZIONE 2


SOLUZIONE Dovete scrivere la formula =SOMMA.SE(D5:D11;">100";D5:D11) (SUMIF se avete excel in Inglese)

La formula fornisce il risultato:

La funzione sum if (somma se) LEZIONE 2


TABELLE PIVOT (1) Sono strumenti flessibili per analizzare grandi quantit di dati Provate a immaginare di avere i dati riportati nella tabella a fianco, e di dover rispondere a domande del tipo: - Che quantit di A stata spedita a Febbraio? - Cosa ha ricevuto il cliente Rossi? - Che quantit di C ha ricevuto Bianchi - Che quantit in totale stata spedita di C? Bisogna organizzare i dati secondopi dimensioni, utilizzando laTABELLA PIVOT

LEZIONE 3


TABELLE PIVOT (2)

In Italiano menu DATi, voce RAPPORTO TABELLA PIVOT e GRAFICO PIVOT

LEZIONE 3


TABELLE PIVOT (3)

1. Se la fonte dei dati lo stesso foglio Excel, sufficiente cliccare su AVANTI

2. selezionate tutta larea dati (comprese le intestazioni delle colonne)

3. Ora occorre definire il layout (tramite il bottone LAYOUT

Seguite le indicazioni del wizard

LEZIONE 3


TABELLE PIVOT (4) LEZIONE 3


TABELLE PIVOT (5) Disponiamo le colonne come risulta qui sotto:

4. Date OK

5. poi FINE

LEZIONE 3


TABELLE PIVOT (7) Ecco il risultato nel nostro esempio:

LEZIONE 3


TABELLE PIVOT (10) FILTRO DI PAGINA:

LEZIONE 3


TABELLE PIVOT (tipo di dato rappresentato) LEZIONE 3


TABELLE PIVOT (altre viste) LEZIONE 3


Opera su una cella contentente caratteri, date o numeri. Permette diselezionare il numero di caratteri specificato a sinistra della cella.Nellesempio, la cellaoriginale contiene iltesto si pensa di noCon la funzione SINISTRAviene richiesta la selezionedei due caratteri pi a sinistradel testo, ottenendo SIcome risultato

La funzione SINISTRA (LEFT) LEZIONE 3


Opera su una cella contentente caratteri, date o numeri. Permette diselezionare il numero di caratteri specificato a destra della cella.Nellesempio, la cellaoriginale contiene iltesto si pensa di noCon la funzione DESTRAviene richiesta la selezionedei due caratteri pi a destradel testo, ottenendo NOcome risultato

La funzione DESTRA (RIGHT) LEZIONE 3


Restituisce lanno relativamente ad una data

La funzione ANNO (YEAR) LEZIONE 3


VLOOKUP (CERCA.VERT)

Supponiamo di voler trovare il nome della Facolt cui corrisponde il codice 3

Supponiamo di avere una tabella organizzata verticalmente e di voler prendere i valori da tale tabella in funzione di un parametro scritto in una cella. Compilate una tabella come nellesempio qui sotto:

LEZIONE 3


VLOOKUP (CERCA.VERT) SOLUZIONE:

LEZIONE 3


HLOOKUP (CERCA.ORIZZ) Chiaramente si avr una sintassi analoga se consideriamo il cerca orizzontale Provate a cercare, con gli stessi dati, messi orizzontalmente (copia, poi incolla speciale-trasponi), sempre la Facolt cui corrisponde il codice 3:

LEZIONE 3


Come calcolare frequenza relativa e frequenza assoluta Costruiamo una tabella delle frequenze assolute e relative. Nella prima colonna riportiamo le sette categorie associate alla varibile in esame. Per calcolare le freq. Ass. utilizziamo la funzione CONTA.SE(intervallo;criteri) dove intervallo=celle in cui sono stati riportati i dati, criterio=uguaglianza con categoria considerata. Per la frequenza relativa occorre dividere le freq. Assolute per il numero totale di osservazioni (somma di tutte le frequenze assolute). Verifica: la somma di tutte le frequenze relative deve essere uguale a 1

LEZIONE 4


Costruzione della tabella delle frequenze Modo veloce per costruire la tabelle di distribuzione delle frequenze:- Nella prima colonna indicare gli estremi destri delle classi scelte -Nella seconda colonna calcolare il numero di osservazioni che cadononella classe corrispondente, utilizzando il comando vettoriale frequenza(dati, classi)-Nella terza colonna calcolare le frequenze relative, dividendo le frequenzeassolute per la numerosit del campione- Nella quarta colonna si ripostano le frequenze cumulate, Ovviamentenella prima casella si riporta il valore della frequenza relativa della prima classe e nelle caselle successive si somma al valore della cellaimmediatamente sopra la frequenza relativa della classe corrispondente- Nella quinta colonna si riportano le densit di probabilit di ogni classe, ottenute dividendo la frequenza relativa per lampiezza della classe- Nella sesta colonna si scrivono esplicitamente le classi in cui sono statiripartiti i valori per poterle poi utilizzare come etichette da riportare nelgrafico

LEZIONE 4


Tabella delle frequenze e istogramma

Generalmente per mostrare I risultati di una tabella difrequenze, si preferisce il grafico a istogramma, che permettedi evidenziare eventuali simmetrie.

LEZIONE 4


Dare nomi alle celle E possibile assegnare un nome (consigliabilmente evocativo) a un insieme di celle. Per esempio:

E sufficiente posizionarsi nella casella casella nome(in figura) e assegnare il nome desiderato.

LEZIONE 4


Variabili qualitative (1) Si vuole condurre lanalisi dei dati riportati in tabella, che rappresentano il risultato di 60 osservazioni di una variabile che indica il tipo di danno alla salute presentato da 60 soggetti impiegati in unazienda agricola e per i quali si sospetta lesposizione a un pestivida. I possibili valori assunti dalla variabile sono etichettati con le lettere:J>=joint swelling; F=fatigue; B=back pain; M=muscle wakness; C=coughinh; N=nose running/irritation; O=other

LEZIONE 5


Indici di posizione: il RANGE Conduciamo lanalisi dei dati in tabella, che

rappresentano la concentrazione di vitamina D in un campione di 26 soggetti (dati da Bland M., 1995).

Calcoliamo i principali indici di posizione e di dispersione, sfruttando le funzioni statistiche di Excel. Supponendo di avere i valori xi delle nostre osservazioni, ad es. nelle celle A4; A29, otteniamo:

Ricordiamo che range =differenza tra valore massimo e valore minimo

LEZIONE 5


Funzione MAX Rende possibile determinare il valore massimo in un intervallo di celle:

LEZIONE 5


Funzione CONTA.SE (COUNT.IF) Conta il numero di celle, in un intervallo, rispondenti al criterio indicato:

LEZIONE 5


Funzione MODA (MODE) Restituisce il valore pi ricorrente in un intervallo dati:

LEZIONE 5


Funzione MEDIANA (MEDIAN) Restituisce la mediana dellintervallo considerato:

LEZIONE 5


Funzione MEDIA (AVERAGE) Restituisce la media aritmetica dei valori considerati:

LEZIONE 5


Altri tipi di medieEs. Media.geometrica:

LEZIONE 5


Funzione VAR (VAR) Restituisce la varianza dei valori considerati:

LEZIONE 5


Altri tipi di funzioni per ilcalcolo della varianzaVAR.VALORI: Stima la varianza sulla base di un campione. Oltre ai numeri, nel calcolo vengono inclusi anche i valori di testo e logici, quali VERO e FALSO.SintassiVAR.VALORI(val1;val2;...)

Val1,val2,... sono da 1 a 30 argomenti di valori corrispondenti a un campione della popolazione.

LEZIONE 5

VAR.POP: Restituisce la varianza sulla base dell'intera popolazione.SintassiVAR.POP(num1;num2;...)

Num1;num2;... sono da 1 a 30 argomenti corrispondenti a una popolazione.

VAR.POP.VALORI: Restituisce la varianza sulla base dell'intera popolazione. Oltre ai numeri, nel calcolo vengono inclusi anche i valori di testo e logici, quali VERO e FALSO.SintassiVAR.POP.VALORI(val1;val2;...)

Val1;val2;... sono da 1 a 30 argomenti di valori corrispondenti a una popolazione.


Funzione Quartile Restituisce il quartile secondo lindice indicato (rif. Figura):

LEZIONE 5


Per determinare il range interquatileQ3-Q1

LEZIONE 5


ROUND (ARROTONDA) Ipotizziamo di voler arrotondare i valori contenuti in alcune celle. Provate con il valore riportato nellesempio, arrotondando a 0,1,2 cifre (con la funzione arrotonda, arrotonda per eccessoe arrotonda per difetto):

LEZIONE 5


ROUND (SCHEMA) LEZIONE 5


Arrotondare alla decina o al centinaio Se si desidera arrotondare i dati alla decina o al centinaio:

=ROUND (D3/100;0)*100 =ARROTONDA (D3/199;0)*100

Cio si procede cos: Si divide il numero per 100 Poi si arrotonda allintero pi vicino In tal modo si ottiene il numero di centinaia del numero iniziale Moltiplicando ancora per 100 si ottiene il numero iniziale, arrotondato alla centinaia

LEZIONE 5


Test chi-quadrato spiegazione pratica

Illustreremo due tipi di test statistici che usano la distribuzione chi-quadrato:

1. Il test della bont di adattamento, che determina se i dati osservati di un campione provengono da una particolare distribuzione della popolazione

2. Il test della tabella di contingenza, che esamina lindipendenza fra due misure categoriche e pu essere utilizzato anche per verificare luguaglianza delle proporzioni da due o pi popolazioni

LEZIONE 5


Test chi-quadrato per la normalit (dati) Unazienda che produce batterie elettriche, per determinare il periodi di garanzia, ha bisogno di sapere se le durate delle batterie hanno una distribuzione normale. Sono state registrate le durate di 200 batterie e i dati sono stati raggruppati in una distribuzione di frequenze, (celle A6:C13). I valori calcolati della media e della deviazione standard sono, rispettivamente, 164 e 10 ore. Copiate i dati in figura (comprese le intestazioni (in bold) che ci serviranno per il chi-quadrato).

Nelle formule utilizzeremo i nomi, perci selezionate e celle A3:C4, aprite il menu Inserisci e scegliete le opzioni Nome>Crea>Riga Superiore. Fate click su OK. Le ipotesi nulla e alternativa da verificare sono: H0: la popolazione delle durate delle batterie ha una distribuzione normale HA: la popolazione delle durate delle batterie non ha una distribuzione normale

LEZIONE 5


Test chi-quadrato per la normalit (calcoli - 1) Ora per determinare le probabilit normali, digitate la formula per il primo intervallo chiuso (D9), copiatela nelle altre celle che contengono le probabilit normali (D8:D12) e modificate per il primo e ultimo intervallo aperto (D8 e D12) Le formule sono: =DISTRIB.NORM(B8;Media;Dev_Standard;VERO)-0

=DISTRIB.NORM(B9;Media;Dev_Standard;VERO)-DISTRIB.NORM(A9;Media;Dev_Standard;VERO)



=1-DISTRIB.NORM(A12;Media;Dev_Standard;VERO)

Le formule servono a calcolare la probabilit cumulativa normale della coda sinistra del limite superiore meno la probabilit cumulativa normale della coda sinistra del limite inferiore. Per il primo intervallo il limite inferiore meno infinito e la probabilit cumulativa della coda sinistra zero. Per lultimo intervallo, il limite superiore dellintervallo pi infinito e la probabilit cumulativa della coda sinistra uno.

LEZIONE 5


Test chi-quadrato per la normalit (calcoli - 2) Create ora le formule per le frequenze attese, che sono pari alla probabilit normale dellintervallo per la dimensione del campione. Scrivete =D8*nnella cella E8 e copiate la formula nelle celle E9:E12 Create le formule per i singoli termini della statistica chi-quadrato: Termine chi-quadrato = (Fr_Effettiva Fr_Attesa)^2/Fr_Attesa, cio=(C8-E8)^2/E8 nella cella F8 e copiate questa formula nelle celle F9:F12 Create la formula per sommare i termini di chi-quadrato. Scrivete la formula =SOMMA(F8:F12) nella cella F13. Scrivete ora letichetta chi_quadrato nella cella F14, sotto la cella che contiene il valore della statistica chi-quadrato. Utilizzate questa etichetta per assegnare il nome alla cella F13 (selezionate le celle F13:F14 aprite il menu Inserisci e scegliete le opzioni Nome>Crea>Riga inferiore. Click su OK.

LEZIONE 5


Test chi-quadrato per la normalit (gradi di libert)

I gradi di libert (GL) per un test della bont di adattamento dipendono dal numero di categorie o intervalli (nel nostro esempio sono 5) e dal numero di parametri della distribuzione ipotizzata per i dati del campione (due: la media e la deviazione standard): GL=Numero di intervalli Numero di parametri ipotizzati 1 Nel nostro caso si ha GL=2

NOTA: prendiamo il caso in cui lazienda avesse in mente una distribuzione specifica e parametri specifici prima di analizzare i dati del campione. Per esempio, per determinare se le durate delle batterie hanno una distribuzione normale con una media di 160 ore e una deviazione standard di 8 ore, i dati del campione non devono essere utilizzati per calcolare le frequenze attese, quindi GL=5-0-1=4. Poich il valore di GL dipende dalla situazione specifica, lutente che deve digitare il suo valore nel foglio di lavoro.

LEZIONE 5


Test chi-quadrato per la normalit (risultati statistica) Inserite le etichette che vedete qui a fianco nelle celle A15:A20.

Per utilizzare i nomi nelle successive formule, create i nomi per le celle che contengono GL, Alfae Val_Critico, cio selezionate le celle A15:B15 tenendo premuto il tasto control anche le celle A18:B19 e poi aprite il menu Inseriscie scegliete le opzioni Nome>Crea>Colonna sinistra. Click su OK.

Ora scrivete le formule per il p-value, cio =DISTRIB.CHI(chi_quadrato;GL) Per il valore critico =INV.CHI(Alfa;GL) Per il risultato della decisione scrivete: =SE(chi_quadrato>Val_critico;Rifiuta H0;Accetta H0)

Diamo ad Alfa il valore 0,01 Vedremo comparire, accanto alla casella decisione, il risultato del test

Il valore assunto dal p-value indica che molto probabile ottenere questi dati del campione da una distribuzione normale. Lipotesi nulla di una distribuzione normale non pu essere rifiutata.

LEZIONE 5


Test chi-quadrato: tabella di contingenza Unazienda vende quattro tipi di bevande nel Nord America. Per verificare se lo

stesso approccio di marketing adottato negli USA pu essere applicato anche in Canada e Messico, lazienda ha fatto analizare la relazione fra preferenze e nazionalit dei consumatori. Lanalisi di marketing ha classificato la popolazione dei consumatori di bevande in funzione delle seguenti preferenze: bevanda normale, senza caffeina e zucchero, senza caffeina, senza zucchero. Una seconda classificazione riguarda la nazionalit dei consumatori: americani, canadesi e messicani. Lanalista ha intervistato un campione casula di 250 consumatori delle tre nazionalit. Li ha raggruppati in base ai due criteri di classificazione e ha ottenuto le frequenze riportate nella figura seguente (NON copiatele in Excel).

Poich lanalista verifica lassociazione fra preferenze e nazionalit dei consumatori, lipotesi nulla e lipotesi alternativa sono le seguenti: H0: le due classificazioni sono statisticamente indipendenti HA: le due classificazioni sono dipendenti

LEZIONE 5


Test chi-quadrato tabella di contingenza (Frequenze Effettive) Costruiamo ora la tabella delle Frequenze Effettive (osservate) Scrivete i titolo della tabella (Frequenze effettive), le etichette delle categorie di bevande (Normale, ecc...), le etichette delle categorie di consumatori (Americani,ecc..) e le etichette Totale colonna e Totale riga, come si vede in figura, copiando i calori delle frequenze osservate nel corpo della tabella (celle B5:E7) Per ottenere i totali delle righe, inserite la formula =SOMMA(B5:E5)nella cella F5 e copiate questa formula nelle altre celle (F6:F7 dei totali delle righe Per ottenere i totali delle colonne, inserite la formula =SOMMA(B5:B7) nella cella B8 e copiate questa formula nella altre celle (C8:F8) dei totali delle colonne.

LEZIONE 5


Test chi-quadrato tabella di contingenza (Frequenze Previste) Per calcolare il p-value della distribuzione chi-quadrato, scrivete letichetta p-valuenella cella A17 Selezionate la cella B17 e fate click sul pulsante incolla funzione oppure selezionate Inserisci>Funzione Nella finestra di dialogo selezionate la categoria delle funzioni statistiche e scegliete la funzione TEST.CHInel riquadro Nome Funzione. Click su OK. Mettete i parametri che vedete in figura.

Secondo lapproccio di reporting il p-value trovato (0,00000013) dice che se lipotesi nulla vera e le due classificazioni sono statisticamente indipendenti la probabilit di ottenere le frequenze osservate in un campione casuale di 250 0,00000013. Poich i valori osservati sono cos improbabili sotto lipotesi

nulla, molti rifiuterebbero lipotesi nulla e concluderebbero che le due classificazioni sono dipendenti. Ci vuol dire che i consumatori di diversa nazionalit preferiscono tipi di bevande differenti.

LEZIONE 5


Test chi-quadrato tabella di contingenza (Test chi-quadrato) Costruite una tabella che contiene i singoli termini di chi-quadrato. Poi specificate un livello di significativit (Alfa), utilizzate la funzione INC.CHI di Excel per determinare il valore critico di chi-quadrato per la verifica, confrontate la statistica calcolata con il valore critico e, infine, decidete se accettare o rifiutare lipotesi. I risultati finali sono in figura. Vediamo, nella slide successiva, come ottenerli velocemente.

LEZIONE 5


Test chi-quadrato tabella di contingenza (Test chi-quadrato) Selezionate una parte della tabella delle frequenze effettive (A3:E7), escludendo i totali delle righe e delle colonne e copiate il contenuto Poi incollate in A19 In A19 scrivete Termini di chi-quadratocome titolo della nuova tabella Per ottenere i singoli termini di chi-quadrato usate la formula: Termine chi-quadrato = (Fr_Effettiva Fr_Prevista)^2/Fr_Previstae cioselezionate la cella B21 e scrivete =(B5-B12)^2/B12 Copiate la formula nelle celle B22:E23 Per completare il foglio di lavoro, scrivete le etichette e le formule che vedete in figura.

Per la funzione del valore critico, usate =INV.CHI(probabilit;gradi di libert)in cui probabilit la coda destra, che nel nostro caso il livello di significativit. Il grado di libert per una tabella di contingenza dato da Gradi di libert =(numero di righe 1) * (numero di colonne 1)nel nostro caso 6

LEZIONE 5


Test chi-quadrato tabella di contingenza (commenti)

Secondo lapproccio decisionale, il valore calcolato di chi-quadrato (42,75) misura le differenze fra le frequenze effettive e le frequenze previste sotto lipotesi nulla.

E maggiore del valore critico di chi-quadrato (16,81) che corrisponde alla regione di rifiuto dell1% nella coda destra.

Di conseguenza, c sufficiente evidenza per rifiutare lipotesi nulla al livello dell1% (test dalla coda destra) e concludere che i consumatori di diversa nazionalit preferiscono tipi di bevande differenti.

LEZIONE 5

riepilogo-funzioni

Documents

Transcript of riepilogo-funzioni