Oggi vogliamo cominciare a parlare di intervalli dinamici. Un intervallo dinamico è un intervallo particolare le cui dimensioni cambiano in funzione del contenuto del foglio di lavoro.

 

Immaginiamo di avere il foglio della figura che segue

Immaginiamo di voler avere sempre il conto aggiornato di quante persone sono elencate nel foglio

Potrei scrivere la formula

=CONTA.VALORI(A2:A8)

Potrei contare anche tutti i valori contenuti nella colonna A e poi sottrarre 1 (CONTA.VALORI(A:A)-1), cioè la cella di intestazione), ma mi occorre introdurre oggi il concetto di intervallo dinamico che, nei prossimi post, useremo per ottenere funzionalità interessanti.

La funzione CONTA.VALORI, conta le celle piene di un determinato intervallo.

Ma se io aggiungo un nome, il nuovo nome ricade al di fuori dell’intervallo in cui conto i valori. Quindi per mantenere aggiornato il conteggio dovrei correggere la formula oppure ricorrere ad un intervallo dinamico.

Per creare un intervallo dinamico, ricorro alla funzione  SCARTO(). SCARTO () fa parte delle funzioni di Ricerca e riferimento e restituisce il riferimento a un intervallo che viene costruito con uno spostamento rispetto a una cella o a un intervallo di celle di un numero specificato di righe e di colonne. Si deve indicare una cella di partenza, di quanto ci si deve spostare da questa cella e le dimensioni dell'intervallo che si vuole costruire. Per far questo, la funzione richiede 5 argomenti (i primi tre sono obbligatori):

• Rif è il riferimento da cui si desidera che inizi lo spostamento. Questo valore deve essere un riferimento di cella. Nel nostro caso partiamo dalla cella A2
• Righe è il numero di righe di cui ci si vuole spostare verso l'alto o verso il basso. Se il valore specificato è positivo lo spostamento è vero il basso. Con un valore negativo, lo spostamento è verso l'alto. Nel nostro caso lo spostamento è di 0 righe, quindi l'intervallo che otterremo partirà dalla riga 1.
• Colonne è il numero di colonne di cui ci si vuole spostare verso l'alto o verso il basso. Se il valore specificato è positivo lo spostamento è verso destra. Con un valore negativo, lo spostamento è verso sinistra. Nel nostro caso lo spostamento è di 0 colonne, quindi l'intervallo che otterremo partirà dalla colonna A.
• Altezza indica l'altezza dell'intervallo che si vuole ottenere espressa in numero di righe. Noi dobbiamo definire questo valore in modo che comprenda sempre tutte le celle piene della colonna A. Per poterle contare ricorriamo alla funzione  CONTA.VALORI. CONTA.VALORI(A:A)-1 restituisce il numero di celle piene della colonna A esclusa la cella di intestazione. Questo è il parametro chiave che ci premette di ottenere un intervallo dinamico.
• Largh indica la larghezza dell'intervallo che si vuole ottenere espressa in numero di colonne. Nel nostro caso, l'intervallo è restituito sarà largo 1 colonna (la colonna A), ma se lavorassimo con più colonne potremmo usare la funzione CONTA.VALORI per contare le colonne piene, magari considerando la riga 1 che contiene le intestazioni di colonna . La funzione da usare sarebbe CONTA.VALORI(1:1). In questo caso non è necessario sottrarre 1 al risultato perché non esiste nessuna intestazione di riga .

Detto questo, possiamo definire l’intervallo dinamico che comprende tutte le celle piene della colonna A, con la formula che segue

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

Se usiamo questa formula come argomento della funzione CONTA.VALORI proposta all’inizio per contare le celle piene della colonna A, otteniamo che il conteggio risulta sempre aggiornato anche quando aggiungiamo nuovi nomi nella colonna A.

La formula diventa

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

L'intervallo definito con la formula che abbiamo appena spiegato, dunque, si allungherà o accorcerà in funzione del contenuto della colonna A.

Individuare la colonna più lunga

Fin qui tutto bene, ma cosa succede se le colonne in cui devo contare sono più d’una e soprattutto non hanno un numero uguale di valori?

Se la colonna A è quella che ha più valori non importa, ma se la situazione fosse quella della figura che segue?

La formula di prima, contando le celle piene di A, taglierebbe fuori dal conteggio il nome Silvia che non ricadrebbe nell’intervallo dinamico.

Non è nemmeno una soluzione contare le celle piene di B, infatti noi non possiamo essere certi che la colonna B sia sempre la più lunga. Come fare allora? Dobbiamo fare in modo di considerare sempre la colonna più lunga nella creazione dell’intervallo dinamico. Per questo dobbiamo correggere l’argomento Altezza della funzione SCARTO usando una funzione MAX annidata recupera il valore più alto fra quelli ottenuti contendo il numero delle celle piene di A e il numero delle celle piene di B (e di eventuali altre colonne).

In questo modo

=SCARTO(A2;0;0;MAX(CONTA.VALORI(A:A);CONTA.VALORI(B:B))-1;2)

La sintassi completa della funzione MAX da usare come argomento Altezza della funzione SCARTO è la seguente

=MAX(CONTA.VALORI(A:A);CONTA.VALORI(B:B))

Al risultato della funzione MAX va sottratto 1 per eliminare l’intestazione di colonna dal conteggio.

La funzione CONTA.VALORI scritta all’inizio diventerebbe dunque

=CONTA.VALORI(SCARTO(A2;0;0;MAX(CONTA.VALORI(A:A);CONTA.VALORI(B:B))-1;2))

Un intervallo dinamico denominato

Invece di usare come argomento di una funzione la funzione SCARTO che definisce l’intervallo dinamico, può essere più comodo assegnare un nome all’intervallo dinamico e poi usare quel nome come argomento della funzione.

In Excel 2003 scegliete Inserisci>Nome>Definisci, mentre in Excel 2007/2010 occorre portarsi alla scheda Formule e, nel gruppo Nomi definiti, premere il pulsante Definisci.

In entrambi i casi vedrete la finestra  Nuovo Nome

Nella casella Nome, digitate il nome che volete assegnare all'intervallo. Noi abbiamo scelto IntervalloDinamico. Nella casella Riferito a, scrivete la formula che genera l’intervallo dinamico (=SCARTO($A$2;0;0;MAX(CONTA.VALORI($A:$A);CONTA.VALORI($B:$B))-1;2)) ricordandovi di fissare in valore assoluto tutti i riferimenti di cella.

Premete Ok. L’intervallo dinamico non compare fra i nomi della Casella del nome, ma può essere usato nelle formule, ad esempio così

=CONTA.VALORI(IntervalloDinamico)