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