Ovviamente stiamo scherzando perché questo piatto di spaghetti all’amatriciana nulla ha a che vedere con Excel. Mentre Amatrice resta un paesello vicino a Rieti dove pare abbiano inventato questo condimento sopraffino, una matrice in matematica altro non è che una griglia di righe e di colonne.
E poiché Excel vive proprio di righe e di colonne, sono parecchie le acrobazie che si possono fare con questo strumento informatico potentissimo.
Ma, come al solito, dobbiamo andare per gradi.
Come certamente ricorderete, il nostro studio sugli Indicatori Tecnici di Borsa si era concentrato sulla Regressione Lineare, ma se vi fosse rimasto qualche dubbio potete rispolverare i contributi n.18, 19 e 20.
In quella occasione, partendo dall’esempio di una biglia in movimento, siamo passati all’equazione di Gauss dei minimi quadrati e alla funzione corrispondente di Excel: =PREVISIONE.
Nota:
Attenzione a non confondere gli studi sulla Regressione Lineare (=PREVISIONE) con quelli della più recente Deviazione Standard (=DEV.ST.POP).
In estrema sintesi, la funzione PREVISIONE permette di trovare quella retta teorica che meglio si presta a interpolare le coppie di punti delle nostre osservazione. Poi, procedendo per estrapolazione <che è l’esatto contrario dell’interpolazione> si possono trovare i ‘punti futuri sul grafico>.
Per rinfrescare la memoria, rivediamo la regressione della dispensa 18 perché ciò che conta è che sia chiaro ciò di cui stiamo parlando:
Una volta ottenuta questa retta, se prendo un righello e lo posiziono in punto qualsiasi dell’asse X (es: 6,5) posso subito vedere a colpo d’occhio quale sarà il corrispondente Y.
Naturalmente in questo modo si va poco lontani perché il metodo ‘a colpo d’occhio’ è fin troppo empirico, ma la sostanza è che questa retta ha una sua precisa equazione e la funzione PREVISIONE la conosce talmente bene da essere in grado di trovare l’Y corrispondente con precisione diabolica.
Oggi è ancora presto per analizzare l’equazione di questa retta di regressione perché temo che sia molto facile andare nel pallone, per cui preferisco fare ancora un altro esempio fuori dalla Borsa: parleremo di tutt’altro, cioè dell’inquinamento di un lago.
L’equazione è solo rimandata, ma è bene studiarla perché è fondamentale per capire l’ingranaggeria Excel di queste sette funzioni fondamentali:
- PREVISIONE
- TENDENZA
- PENDENZA
- RQ
- ERR.STD.YX
- REGR.LIN
- PEARSON
Quando avremo compreso e interiorizzato le connessioni delle 7 funzioni, potremo ritenerci soddisfatti e molto più sicuri nelle interpretazioni di Borsa che faremo.
Quello che voglio fare oggi è un esempio che ci servirà a descrivere la funzione TENDENZA che, passatemi il termine, è una “parente stretta” di quella PREVISIONE che già conosciamo.
Via, si parte!
Il Ministero dell’Ambiente ha rilevato che il lago “Schifezza” nel comune di “Topolinia” è inquinato al punto di aver bisogno di un impianto di depurazione per poi iniziare un programma di ripopolamento ittico.
Una volta installato l’impianto, ogni mese un’equipe di esperti preleva un campione delle acque e registra l’Indice di inquinamento: un numero che, col tempo, dovrebbe calare.
Dopo un anno esatto si ottiene questa matrice:
Come potete vedere in sfondo giallo, se vogliamo sapere cosa ne sarà dell’inquinamento al mese 13° basta applicare la formula: PREVISIONE(x;y_nota;x_nota) in cui “x” è la cella C11 che contiene il valore 13, y_nota è il vettore B11:B22 (cioè le rilevazioni) e x_nota è la sequenza dei mesi conosciuti.
Fin qui niente di strano, tutto coincide col precedente esempio della biglia della dispensa 18.
Il fatto nuovo, invece, è che oltre a PREVISIONE esiste un’altra formula Excel a nome TENDENZA che, come vedremo, oltre a replicare i risultati di PREVISIONE, in realtà riesce a fare un po’ di più.
Vediamo al volo la coincidenza dei risultati applicando TENDENZA al problema del lago Schifezza.
Come volevasi dimostrare, i risultati di PREVISIONE e TENDENZA coincidono alla perfezione: al 13° mese il livello di inquinamento sarà sceso a 114,45.
Nota:
Attenzione allo scambio dei parametri nelle due funzioni. In PREVISIONE il valore da cercare (x) in cella C11 viene posto all’inizio di parentesi mentre in TENDENZA cade alla fine.
Fin qui, entrambe le funzioni restituiscono un unico valore nel senso che uno soltanto è stato il valore richiesto dalla formula, cioè il 13° mese. Il problema cambia quando al posto di unico risultato viene richiesta una matrice di risultati.
Nel caso specifico, non ci dispiacerebbe sapere cosa può accadere contemporaneamente al 13°, 14° e 15° mese della nostra esplorazione statistica.
Per far ciò possiamo utilizzare solo ed esclusivamente TENDENZA che, come dicevamo, è più performante della precedente PREVISIONE.
In conclusione, mentre PREVISIONE si ferma alla determinazione di un solo valore, TENDENZA può determinarne contemporaneamente più di uno.
Cominciamo a fissare le idee con la sintassi della funzione: TENDENZA(y_nota;x_nota;nuova_x;cost) in cui il terzo parametro <nuova_x> può essere una matrice anziché una voce singola.
Vediamo l’esempio:
Come potete vedere, TENDENZA non ha restituito UN SOLO VALORE bensì una MATRICE DI VALORI, cioè contemporaneamente 3 celle con questi risultati: 114,45 per il 13° mese, 103,02 per il 14° e 91,59 per il 15°.
E questo è il vero vantaggio di questa incredibile formula: risultati complessi e contemporanei.
Attenzione però perché per poter funzionare, questa funzione ha la necessità di essere inserita come “FORMULA MATRICE” perché se la eseguiamo normalmente come copia-incolla di formula <normale> non dà i risultati sperati.
Alt! A questo punto dobbiamo fare luce sulla questione, se no non si capisce!
Andiamo con le domande:
- Quando una FORMULA MATRICE ?
- Perché una FORMULA MATRICE ?
- Come si fa a fare una FORMULA MATRICE ?
- Come si riconosce una FORMULA MATRICE ?
E ora le risposte, partendo dalla definizione della letteratura:
“… Alcune funzioni dei fogli di lavoro restituiscono matrici di valori o richiedono una matrice di valori come argomento. Per calcolare più risultati con una formula in forma di matrice, occorre immettere la matrice in un intervallo di celle che abbia lo stesso numero di righe e colonne degli argomenti matrice …”.
Chiaro? Certo che no. Io non ho capito!
Partiamo da qui: “… Alcune funzioni dei fogli di lavoro restituiscono matrici di valori o richiedono una matrice di valori come argomento …”.
Ma ‘alcune quali’? Mi chiedo io?
Occorre ‘saperlo prima’ ma con un po’ di pratica si scopre che sono le funzioni nella cui sintassi della Guida in linea di Excel è inclusa la parola “matrice”.
Infatti se vado nella Guida alla voce TENDENZA scopro che: “… Restituisce i valori lungo una tendenza lineare. Utilizzando il metodo dei minimi quadrati, calcola una retta che coincide con le matrici y_nota e x_nota e restituisce i valori y lungo la retta per la matrice di nuova_x specificata …”.
Quindi la risposta alla domanda 1) è: quando lo dice la sintassi della Guida
Veniamo alla domanda 2): Perché una formula matrice?
La risposta è: 2) quando hai più dati da elaborare contemporaneamente
E ora la domanda 3). Come si crea una Formula Matrice?
Questi sono i passi:
- selezionare l’intervallo di celle per le quali vogliamo trovare i risultati (nel nostro caso occorrerà selezionare da D24 a D26)
- inserire la formula nella barra della formula =TENDENZA(B11:B22;A11:A22;A24:A26)
- anziché fare il classico INVIO, eseguire il comando contemporaneo CTRL+MAIUSCOLO+INVIO
Domanda 4: come si riconosce una Formula Matrice?
Semplice: la formula matrice è sempre preceduta e seguita da una parentesi graffa che si può vedere SOLO nella barra della formula:
{=TENDENZA(B11:B22;A11:A22;A24:A26)}
Nota:
Excel racchiude automaticamente la formula tra parentesi graffe{ }. Non è possibile immettere le parentesi manualmente. In questo caso, infatti, la formula non funziona perché Excel interpreta le parentesi graffe come testo e non è possibile eseguire calcoli sul testo. Assicurarsi quindi di premere CTRL+SHIFT+ENTER.
Bene!
Forse siamo tutti un po’ storditi da questa tediosa investigazione di Excel, ma credo ne sia valsa la pena, se non altro per aver chiarito uno degli scogli più duri delle funzioni statistiche di Excel. Anche la prossima puntata sarà complicata, ma … come sempre, cercheremo di analizzare i dettagli con calma e semplicità. Alla fine saremo sicuramente riusciti a chiudere il cerchio delle 7 funzioni principali come in questo schema:
Non mancate all’appuntamento su www.francescocaranti.net.
Francesco Caranti
