Excel creare un intervallo dinamico

Vale la pena ribadire ed approfondire il concetto di intervallo dinamico in Excel.
Quando i file sono di volta in volta importati da fonti esterne, il problema non si pone.
Ma quando il file su cui lavorare è in progress o i dati prelevati da origini esterne vanno accodati a dati esistenti, allora il problema si pone… La prima area di intervento è relativa ad un database contenente dati che incrementano con regolarità.
Si immagini di avere un prospetto con tante formule e con un ordinamento ben definito, ad esempio un ordinamento cronologico, come quello illustrato parzialmente dalla figura sotto.
Se i nuovi dati vengono inseriti sotto all’ultima riga, nel rispetto dell’ordinamento cronologico, le formule e funzioni non si estendono in automatico dalle celle soprastanti.
La stessa cosa accade se i dati vengono digitati in righe inserite in mezzo a quelle esistenti; in questo caso, non solo le formule non si aggiornano, ma si perde anche l’ordinamento.



La soluzione a questo problema è semplice come bere un bicchiere d’acqua: selezionare tutto l’intervallo celle attualmente compilato e formattarlo come tabella dall’apposito comando presente sulla scheda HOME.
Prima si sceglie un set di colori predefinito tra quelli messi a disposizione poi si conferma se l’intervallo dati ha già una riga di intestazione o meno ed il gioco è fatto.
Se si inseriscono dati nella prima riga che si trova sotto all’ultima riga della tabella, appena si conferma il primo dato immesso, la nuova riga è inclusa nella tabella e tutte le formule e funzioni sono dinamicamente estesa.
Naturalmente la stessa cosa se i nuovi dati vengono immessi in una riga all’interno e non alla fine dell’elenco.

Se, invece, l’intenzione è avere un’origine dati dinamica per un menu a tendina da creare con il comando della Convalida Dati, allora le cose da fare sono 2:

- Formattare come tabella la colonna con le voci che sono alla base del menu a tendina o, se la colonna fa parte integrante di un elenco, formattare come tabella tutto l’elenco.
- Nella colonna di appoggio per il menu a tendina, selezionare le sole celle che contengono le voci di origine per il menu a tendina (non selezionare la cella con l’eventuale etichetta) e dare un nome a queste celle. Tale nome va usato nella Convalida dati. Siccome le celle denominate si trovano in un intervallo formattato come tabella, il nome è dinamico ed includerà in automatico anche eventuali altre voci inserite in fondo.

Un altro caso di intervallo dinamico è rappresentato dall’origine dati a partire da cui si deve creare una tabella pivot.
Spesso viene adottato l’escamotage di selezionare più celle rispetto a quelle attualmente piene.
Se, ad esempio, l’intervallo dati compilati fosse A1:G230 e si prevedesse l’aggiunta successiva di nuove righe, l’utente di norma adotterebbe due soluzioni:

- Selezionare tutte le colonne (A:G)
- Selezionare qualche cella in più, magari un numero ragionevole di celle che si è sicuri non potranno mai essere superate (ad esempio A1:G8500).

Entrambe le soluzioni evitano di dover cambiare l’origine dati ogni volta che sono aggiunte una o più righe in fondo all’elenco, ma non sono ottimali perché pongono dei problemi: per ogni categoria messa in riga o colonna appare la dicitura “Vuoto” che va esclusa e per i campi da sottoporre al calcolo viene dato il conteggio anziché la somma, a causa della presenza di celle vuote.

La prima soluzione ottimale è quella più volte accennata (formattare l’intero intervallo come tabella) ma se non si volesse o potesse adottare questa soluzione (che pure ha dei limiti) allora ci accorre in aiuto una funzione interessante, sulla quale varrà la pena soffermarsi anche in altre occasioni: la funzione SCARTO che appartiene alla categoria delle funzioni di RICERCA E RIFERIMENTO.
Nella funzione, si indica una cella di partenza e poi bisogna specificare due cose:

- Di quante righe e/o colonne si deve spostare il punto di partenza.
- La profondità ovvero quante righe e colonne devono essere incluse in base alla cella di partenza.

Nel nostro caso, ipotizzando un elenco come quello sopra indicato A1:G230, il punto di partenza deve rimanere immutato (ovvero A1, senza spostarsi di alcuna riga o colonna) ed includere tutte le celle piena della colonna A in altezza e della riga 1 in larghezza.
La funzione che conta tutte le celle piene è CONTA.VALORI, che al momento arriverebbe alla riga 230 e colonna G, ma includerà tutte le righe e colonne che verranno eventualmente aggiunte.
La funzione risultante è la seguente (tutti i riferimenti di cella sono stati bloccati e resi assoluti con il simbolo del dollaro $).

= SCARTO($A$1;0;0;CONTA.VALORI($A:$A);CONTA.VALORI($1:$1))

Una funzione non può essere alla base di una tabella pivot, che accetta come origini solo intervalli di celle, nomi di tabelle formattati come tali ed intervalli denominati dall’utente.
L’accortezza è assegnare un nome alla funzione. Come? Ad esempio dal comando DEFINISCI NOME che si trova nella scheda FORMULE (vedi figura più in basso).
Nella sezione Nome si assegna un nome, ad esempio Base_pivot (il nome non ammette spazi o caratteri speciali) mentre nella sezione Riferito a si digita la funzione o forse è più agevole digitarla in una cella qualsiasi del foglio Excel, copiarla ed incollarla in questa sezione.

Come si fa, poi, ad inserire una tabella pivot basata sul nome appena assegnato?
Quando si clicca su INSERISCI e poi TABELLA PIVOT, nella sezione Tabella/Intervallo si digita il nome assegnato alla funzione (in questo caso Base_Pivot) o si richiama il nome attraverso il tasto F3.
Ed ecco che la propria origine dati diventa dinamica per la tabella pivot.