In questo esempio è mostrato come sia possibile rendere dinamica una somma di valori all'interno di una tabella: l'intervallo dei dati sommati varia al variare di alcuni parametri impostati.
Un esempio pratico potrebbe essere quello in cui abbiamo una tabella con lo storico dei fatturati dell'anno e vogliamo fare la somma solo degli ultimi 3 mesi, o solo per alcune colonne, ecc.
 Problema Il problema che vogliamo risolvere quindi è: avendo una tabella di dati simile a quella di esempio qui a lato, vogliamo rendere una somma, un subtotale, ecc. dinamica, ovvero dipendente da parametri. |
 Immaginando di voler ottenere la somma con un filtro di un periodo per le righe e di una zona per le colonne, come evidenziato nella tabella con la formattazione condizionale, possiamo scrivere due tipi di formule: una di matrice e una utilizzando le funzioni SCARTO e CONFRONTA. |
Soluzione con calcoli di matrice
La soluzione secondo me preferibile in casi come questi (ma la cosa è molto soggettiva) è utilizzare una
formula di matrice: è molto più facile da scrivere e risulta molto più leggibile successivamente.
Con questo metodo e con le celle visibili nelle immagini precedenti la formula sarà:
{=SOMMA(SE(B3:B14>=H4;SE(B3:B14<=H5;SE(C2:E2=H6;C3:E14;0))))}
 Le formule di matrice, come questa, possono essere poco comprensibili: non è sempre intuitivo capire come funzionano.
L'immagine a lato mostra il risultato del nostro file di analisi sulla formula qui sopra, sono stati evidenziati i valori (sono delle tabelle in realtà) dei calcoli intermedi. Come potete vedere il calcolo parte dalla tabella completa (il riquadro più a destra), la prima selezione viene fatta dal SE più a destra nella formula e "riduce" i dati alla sola colonna riferita alla zona2. Il secondo SE filtra i soli dati minori o uguali al mese di agosto, e infine l'ultimo ai soli dati maggiori o uguali al mese di giugno.
Il risultato è una tabella contenente i soli dati che ci interessano su cui applicare semplicemente la funzione SOMMA. Una cosa da notare è che per Excel il valore Falso (indicato sulle tabelle con F) è rappresentato dallo zero e quindi ininfluente nella somma. |
Soluzione con SCARTO+CONFRONTA
Una soluzione alternativa può essere quella di utilizzare la funzione CONFRONTA, come nel caso della ricerca con
un valore o con
due valori per determinare le righe e colonne da sommare, in questo modo:
=SOMMA(SCARTO(B2;CONFRONTA(H4;B3:B14;0);CONFRONTA(H6;C2:E2;0);CONFRONTA(H5;B3:B14;0)-CONFRONTA(H4;B3:B14;0)+1;1))
 Qui a destra potete vedere l'analisi della formula che utilizza SCARTO+CONFRONTA.
Come si può vedere dalla struttura le funzioni CONFRONTA vengono utilizzate per avere gli indici delle righe (dalla riga 6 per 3 righe) e delle colonne (la 2°colonna) da sommare.
La funzione SCARTO poi ci permette di avere il riferimento ad un intervallo di celle con questi parametri:
- RIF: Cella o intervallo da cui inizia lo spostamento
- RIGHE: Numero di righe verso il basso (positivo) o verso l'altro (negativo) di cui spostarsi
- COLONNE: Numero di colonne verso destra (positivo) o verso sinistra (negativo) di cui spostarsi
- ALTEZZA: Altezza in righe del riferimento restituito
- LARGH: Larghezza in colonne del riferimento restituito
|
L'esempio completo é scaricabile da questo link:
Es396.xlsx
Gli esempi contenuti nel sito sono per uso personale, non é consentito l'uso professionale, commerciale o la riproduzione senza autorizzazione.
