Lascia parlare… Excel!

[ Commenti

Automatici

]

1

LASCIA PARLARE… EXCEL! Uno dei sogni nel cassetto di ogni analista è il commento automatico ai valori numerici contenuti in una tabella o un foglio di lavoro, cioè la creazione di una tabella parlante. Fra le possibilità di utilizzo di Excel vi è anche l’analisi e il confronto dei dati e la scelta di commenti testuali da riportare in una zona prestabilita: in questo modo un Report diventa leggibile anche da persone che, per mestiere, non sono abituate a leggere fitte tabelle di numeri. Queste persone saranno felici di non dover incrociare colonne e righe per capire se le vendite vanno bene o meno e si accontenteranno di un semplice “le vendite crescono a un ritmo molto sostenuto” o “le vendite stanno entrando in un momento di crisi”. Ma la loro felicità è, purtroppo, proporzionale alla complessità del foglio che dovremo costruire.

In questo articolo spiegheremo come costruire un foglio di calcolo che offra commenti testuali automatici sulla base dei dati inseriti in un certo intervallo di celle.

Per fare bene questo tipo di operazioni occorre – oltre ad avere una solida base di Excel - conoscere le logiche delle seguenti funzioni:           

SE(), E(), O() CONCATENA() TESTO() CODICE.CARATT() CERCA.VERT() MEDIA() GRANDE() e PICCOLO() DEV.ST() CORRELAZIONE() TENDENZA() MEDIA()

1

SOMMARIO Fu n zi o n i d i a na l is i s ta t is t ic a

2

La Me d i a

2

Le f u n zi o n i M IN( ) e M AX ()

2

La D e v i a zi o ne St a nd ar d

3

La c or r e l a zi o n e

4

La T en d e n za

5

Q u adr i am o il c er c h i o

6

Funzioni di analisi statistica Quelle che seguono sono funzioni che permettono l’analisi critica dei dati. Prima di entrare nel vivo delle funzioni, ricordiamo che esse diventano realmente affidabili quando la base di dati (serie) è sufficientemente ampia. Inoltre, è sempre raccomandabile verificare se le analisi matematiche trovano conforto nelle dinamiche reali: un risultato va sempre ponderato prima di venderlo come assoluto.

La Media Si tratta di una funzione di grande semplicità, ma la cui valenza euristica è senz’altro importante. La sintassi prevede un unico argomento, cioè la serie di dati sulla quale calcolare la media: =MEDIA(num1, num2, num3…)

La funzione è del tutto identica come logica di funzionamento a SOMMA(), pertanto valgono le stesse regole nella selezione dei dati. Da un punto di vista teorico, tuttavia, è importante mettere a fuoco i dati sui quali si vuole calcolare la media. In un mercato in forte crescita è utile, per esempio, calcolare la media sui mesi più recenti, mentre in mercati stabili è meglio estendere la stima a periodi più lunghi.

Le funzioni MIN() e MAX() Si tratta di due funzioni molto semplici e utili nell’analisi dei dati e servono per determinare il valore più alto e più basso di una serie di dati. Anche in questo caso queste funzioni richiedono un unico argomento. =MIN(num1, num2, num3…) =MAX(num1, num2, num3…)

2

Basta inserire il riferimento alla serie di dati sulla quale calcolare il minimo e il massimo come argomento ed Excel restituirà i risultati attesi.

La Deviazione Standard La Deviazione Standard o Scarto Quadratico Medio, simbolizzata con la lettera greca σ (sigma), restituisce la distanza media dei dati dalla media degli stessi. Per comprendere meglio il significato di questa frase, facciamo un esempio. Consideriamo le seguenti serie: Serie 1: 99, 100, 101 Serie 2: 0, 100, 200

La media di entrambe le serie è sempre 100, ma è ovvio che la Serie 2 è molto più dispersa rispetto alla media. La deviazione standard ci dice qual è il livello di variabilità della serie: la serie 1 è molto lineare, mentre la 2 è più altalenante. La formula della Deviazione Standard DEV.ST() calcola le differenze rispetto alla media e le eleva al quadrato (così i valori negativi spariscono e nel sommare le differenze non otteniamo 0). La somma di queste differenze al quadrato diviso il numero di elementi della serie meno uno ci restituisce un altro indicatore interessante che si chiama Varianza. La radice quadrata della varianza è – finalmente – lo scarto quadratico medio. Tutto questo viene sintetizzato in una funzione estremamente semplice che ha gli stessi argomenti della funzione MEDIA(): =DEV.ST( num1, num2, num3…)

Le serie precedenti avevano entrambe media = 100, ma il calcolo della deviazione standard rivela la loro diversità: la serie 1 ha σ = 1, mentre la serie 2 ha un σ = 100. In questo esempio, è evidente che la serie 1 è più stabile della 2 poiché la media delle due serie è la stessa, ma in altri casi questa valutazione non è così semplice. Consideriamo le seguenti serie: Serie 1: 124, 507, 945  media = 525, σ = 411 Serie 2: 4.791, 9.875, 16.427  media = 10.364, σ = 5.833

Qual è la serie con minore variabilità? In questo esempio, se prendessimo in considerazione solo σ non giungeremmo alla corretta valutazione. Occorre, infatti, rapportare il primo con la media: Serie 1: σ / media = 411 / 525 = 78,2% Serie 2: σ / media = 5.833 / 16.427 = 56,28% La seconda serie è più “stabile” o più “lineare” della seconda.

3

La correlazione È un calcolo che mette a confronto due serie per evidenziare se esse hanno uno stesso andamento o se, viceversa, non esiste alcuna relazione tra esse. Questo tipo di analisi è molto utile per capire se, per esempio, al variare di una variabile un’altra aumenta o diminuisce. Oppure, può essere utile per valutare se esiste una certa stagionalità in una certa grandezza (vendite, costi o altro) nei diversi periodi dell’anno. Il calcolo statistico che sta alla base di questa analisi è la Covarianza ed è un calcolo piuttosto complesso, sul quale possiamo tranquillamente sorvolare, visto che Excel ci offre la soluzione senza troppa fatica. Anche in questo caso, vediamo un esempio per comprendere meglio il concetto. Osserviamo le seguenti serie di dati: Serie 1: 2, 4, 6, 8, 10, 12, 14, 16 Serie 2: 16, 14, 12, 10, 8, 6, 4, 2

Tra queste due serie esiste una forte relazione, anche se inversa: al crescere dei valori della prima serie, decrescono i valori della seconda. La correlazione tra i dati è perfetta, ma negativa quindi il coefficiente di correlazione che otterremo è -1. Vediamo un altro esempio: Serie 1: 1, 2, 3, 4, 5, 6, 7, 8 Serie 2: 2, 4, 6, 8, 10, 12, 14, 16

In questo caso la correlazione è altrettanto perfetta, ma è anche diretta: la prima serie è la metà della seconda.

In generale, quando esiste una correlazione il valore del coefficiente tende a essere vicino a 1. Quando non esiste relazione il valore della funzione restituirà valori vicino a 0. Un coefficiente di correlazione pari a 0,5 indica che non vi è una debolissima correlazione. Sotto questo valore, si può affermare che la correlazione non esiste. Se la correlazione è negativa, ma esiste, otterremo valori vicino a -1.

La funzione Excel per calcolare la correlazione è CORRELAZIONE() e ha due argomenti: CORRELAZIONE(matrice1; matrice2)

4

I due argomenti sono semplicemente due riferimenti a due intervalli di dati.

La Tendenza Il Trend o Tendenza è un calcolo che, data una serie storica di dati, permette di stimare quale sarà il dato tra 1, 2, n periodi successivi all’ultimo periodo noto. In altre parole, siamo a fine anno, conosciamo quale è stato il prezzo di un’azione negli ultimi 10 mesi e vogliamo stimare quale sarà il valore sulla base delle a gennaio: questa funzione assolve a questo compito. L’algoritmo di calcolo che sta alla base è il metodo dei minimi quadrati, cioè il calcolo di una retta che interpola i punti rappresentati dalla serie storica di coppie x e y in un sistema cartesiano. La funzione TENDENZA() di Excel serve per restituire le x successive ai periodi dati. Per chi legge queste righe e ha ricordi di algebra che si perdono nelle pieghe del tempo, ricordiamo che parleremo della famosa equazione y = mx + b, dove y rappresenta il punto da stimare, m è la pendenza della retta e b il punto di incrocio tra la retta e l’asse delle ordinate (intercetta). La funzione tendenza non fa altro che calcolare il valore di m e di b e calcola il valore di y al variare di x. Nella quasi totalità delle applicazioni di questo calcolo, x rappresenta il tempo. Per i motivi ben noti a chi conosce l’uso strategico degli strumenti grafici, esso viene rappresentato come asse delle ascisse (in orizzontale). La sintassi della funzione prevede 4 argomenti, di cui uno facoltativo: TENDENZA ( y_nota; x_nota; nuova_x; cost )

 

 

y_nota rappresenta i valori osservati, per esempio il prezzo delle azioni di un certo periodo x_nota è la serie temporale relativa ai valori osservati, per esempio il numero dei mesi. È una serie di numeri che può essere rappresentata da date (anch’esse numeri seriali) o semplici numeri che rappresentano un anno o un mese. Attenzione! Se inseriamo due date anziché due numeri potremmo non avere lo stesso risultato. In altre parole se, per rappresentare il mesi di febbraio, marzo e aprile inseriamo 2, 3, 4 oppure 28/02/05, 31/03/05, 30/04/05 il risultato sarà simile ma non uguale perché nel primo caso la distanza è 1, mentre nei secondi la distanza varia tra 31 e 30. nuova_x è il periodo per il quale vogliamo stimare il valore della y cost è un valore logico che assume VERO o FALSO a seconda se desideriamo o meno che il valore b della funzione intercetti l’asse delle ordinate al punto 0. È una condizione non molto frequente e di solito conviene lasciarla al valore di default che è VERO Il numero di valori di y_nota e x_nota devono essere lo stesso: non possiamo avere 10 prezzi e 9 valori temporali.

5

Vediamo un esempio. Immaginiamo di avere il numero di unità vendute fino a settembre e di voler stimare il loro valore per i mesi rimanenti fino alla fine dell’anno. La serie di dati è la seguente: Periodo

Valore

1

1.000

2

1.300

3

1.324

4

1.200

5

1.341

6

1.275

7

1.450

8

1.345

9

1.570

Se disponiamo questi valori nell’intervallo A1:B1, e nella cella A12 scriviamo “10”, cioè il numero relativo al periodo successivo all’ultimo della serie, la formula da inserire in B12 sarà: =TENDENZA( $B$2:$B$10; $A$2:$A$10; A12)

La formula restituirà il valore stimato usando il metodo dei minimi quadrati.

Quadriamo il cerchio In questo paragrafo vedremo alcuni esempi sull’uso delle funzioni precedentemente spiegate per far sì che Excel esprima un giudizio testuale. Questi esempi sono integrati in un modello liberamente scaricabile, nel quale è possibile modificare i dati per vedere cosa dirà “l’oracolo”.

Inseriamo i dati sui quali eseguire le analisi nel modello. Per congruenza rispetto ai valori e che analizzeremo, nel range A1:C13 inseriamo i seguenti dati: Periodo

Serie1

Serie2

1

100

80

2

120

120

3

130

130

4

140

150

5

110

180

6

115

140

7

150

140

8

125

131

9

145

190

10

160

170

11

171

290

12

140

100

6

Osservando i valori della Serie 2, appare chiaro che il primo valore e l’undicesimo sono molto più alti o molto più bassi rispetto alla serie stessa e sono senz’altro dei temi da approfondire. Se la serie rappresentasse delle vendite, potrebbero essere degli effetti di stock o delle diminuzioni della domanda: in ogni caso, dei motivi di approfondimento. Come fare per far sì che Excel evidenzi automaticamente un valore troppo alto o troppo basso?

Per prima cosa, dobbiamo calcolare un valore di stima che rappresenti da solo l’intera serie. Lo stimatore per eccellenza, con i suoi pregi e i suoi difetti, è la media. Mettendo a rapporto ogni valore della serie con la media, scopriamo valori eccessivamente alti o bassi. Assumiamo che giudichiamo eccessivamente alto un rapporto maggiore o uguale a 1,5 ed eccessivamente basso un rapporto minore o uguale a 0,5. Per individuare sia i valori alti che i valori bassi basta considerare il valore assoluto del rapporto meno uno:

Stima = ass [(valore n / media) – 1]

In questo modo, potremo considerare come “anomali” tutti i valori maggiori o uguali a 0,5. Vediamo come approcciare il problema con Excel proponendo un approfondimento sul valore anomalo più evidente.

Calcoliamo la media delle due serie in B14:C14 utilizzando la formula MEDIA(). Ora eseguiamo un confronto con SE() verificando quali celle delle due serie hanno dei rapporti superiori a 0,5. In E2 scriviamo: =SE(ASS(B2/B$14-1)>=0,5;ASS(B2/B$14-1);0)

La formula verifica se il valore assoluto della cella B2 divisa per la media (B14) meno uno, riporti un valore superiore a 0,5: se la condizione è vera viene riportato il rapporto, mentre, in caso contrario, la formula riporta uno 0. Copiamo questa formula nel range E2:F13. Nel range E14:F14 calcoliamo il valore massimo della serie appena calcolata attraverso la funzione MAX(). Se il valore massimo sarà 0, significherà che non vi è alcun valore “anomalo”. Per questo utilizziamo una funzione SE() che riporterà un “no” nel caso non vi siano valori anomali. In E14 inseriamo la seguente formula: =SE(MAX(E2:E13)=0;"no";MAX(E2:E13))

7

Copiamo la formula in F14. Nel range G2:G13, riportiamo il numero che rappresenta il periodo: questo sarà utile per derivare l’eventuale valore anomalo con la funzione CERCA.VERT.

A questo punto possiamo creare una formula che ci dica qual è il periodo anomalo per entrambe le serie e, per farlo, utilizziamo la funzione CERCA.VERT, preceduta da un SE() che riporterà la frase testuale di analisi. Nella cella E15 scriviamo: =SE(E14="no";"";CERCA.VERT(E14;$E$2:$G$13;3;FALSO))

Nella cella F15, invece: =SE(F14="no";"";CERCA.VERT(F14;$F$2:$G$13;2;FALSO))

Avremo ottenuto il numero dell’eventuale periodo con un picco anomalo. Ora proviamo a comporre una frase che dica “Il x° periodo evidenzia un andamento anomalo”.

Il primo grande problema deriva dal fatto che potremo usare l’articolo il in tutti i casi tranne quando ci troviamo di fronte all’undicesimo periodo. In questo caso, infatti l’articolo diventa l’. Rileviamo questa complessità con un SE() che tenga conto anche del caso in cui non vi siano picchi anomali. In A17 scriveremo: =SE(E15="";"La Serie 1 non presenta picchi anomali";SE(E15=11;"L'";"Il ")&E15&"° periodo della Serie 1 evidenzia un picco anomalo")

8

In A18, invece: =SE(F15="";"La Serie 2 non presenta picchi anomali";SE(F15=11;"L'";"Il ")&F15&"° periodo della Serie 2 evidenzia un picco anomalo") In A18 e A17 ora abbiamo due commenti relativi alle due serie. Proviamo ora a stabilire il livello di correlazione tra le due serie. Per farlo, utilizzeremo la funzione CERCA.VERT() con il quarto argomento su VERO, cioè con sfruttando l’approssimazione. A titolo esemplificativo, a fronte di un certo valore di correlazione, il commento che riporteremo sarà “Le due serie presentano”: Corr. -1 -0,9

Commento un andamento diametralmente opposto un andamento opposto

-0,55

una relazione molto debole

-0,1

un andamento non correlato

0,1

una relazione molto debole

0,55 0,9 0,95

un andamento simile un andamento molto simile una forte relazione

Per esempio, se la correlazione ha un valore pari a 0,8 il commento sarà “Le due serie hanno un andamento simile”, poiché l’approssimazione di CERCA.VERT() riporta il valore immediatamente precedente a quello cercato, cioè 0,55. Iniziamo a calcolare la correlazione nella cella F16: =CORRELAZIONE(B2:B13;C2:C13) La cella restituisce un valore pari a 0,659. Ora riportiamo il valore testuale attraverso la funzione CERCA.VERT(). Per questo riportiamo la tabella sopra illustrata nel range J1:K9 e, in A19, scriviamo: ="Le due serie presentano "&CERCA.VERT(F16;$J$2:$K$9;2;VERO)

Come ultimo esempio proviamo a mettere insieme i tre testi prodotti inserendo dei ritorni di carrello (carattere ASCII = 10). Per farlo utilizzeremo la funzione CODICE.CARATT(). Prima di procedere all’inserimento, dobbiamo creare una fusione tra celle per favorire una maggiore visibilità. Selezioniamo le celle A20:K20 e premiamo Ctrl + 1, scegliamo la scheda Allineamento e spuntiamo Testo a capo, Unione Celle e Allineamento Testo Orizzontale a Sinistra. Ora, in A20 scriviamo: =A17&". "&CODICE.CARATT(10)&A18&". "&CODICE.CARATT(10)&A19

9

10