Un'introduzione ai range dinamici
La funzione CERCA.VERT viene spesso utilizzata per trovare informazioni archiviate all'interno di tabelle in Excel. Quindi, ad esempio, se abbiamo un elenco di nomi ed età di persone:
E poi possiamo in una cella vicina usare la funzione CERCA.VERT per determinare l'età di Paul:
Finora questo è uno standard abbastanza. Ma cosa succede se dobbiamo aggiungere altri nomi alla lista? L'ovvio pensiero sarebbe quello di modificare l'intervallo nel VLOOKUP. Tuttavia, in un modello davvero complesso, potrebbero esserci diversi riferimenti al VLOOKUP. Ciò significa che dovremmo cambiare ogni riferimento, supponendo che sapessimo dove si trovavano.
Tuttavia, Excel fornisce un modo alternativo, chiamato intervallo DINAMICO. Questo è un intervallo che si espande e si aggiorna automaticamente. Questo è perfetto se le tue liste sono in continua espansione (ad esempio dati di vendita mese su mese).
Per impostare un intervallo dinamico dobbiamo avere un nome di intervallo, quindi chiameremo il nostro AGE_DATA. L'approccio per l'impostazione degli intervalli dinamici è diverso tra Excel 2007 e le versioni precedenti di Excel:
In Excel 2007, fare clic su "Definisci nome" sotto le formule:
Nelle versioni precedenti di Excel fare clic su "Inserisci" e poi su Nomi".
Nella finestra pop-up, inserisci il nome della nostra gamma dinamica, che è "AGE DATA":
Nella casella "Riferito a" dobbiamo inserire l'intervallo dei nostri dati. Questo sarà un risultato utilizzato da una funzione OFFSET. Questo ha 5 argomenti:
=OFFSET(Riferimento, Righe, Col., Altezza, Larghezza)
- Il riferimento è l'indirizzo dell'angolo IN ALTO A SINISTRA della nostra gamma - in questo caso la cella B5
- The Rows è il numero di righe dall'alto a sinistra che vogliamo che sia l'intervallo - che in questo caso sarà 0
- Il Cols è il numero di righe dall'ALTO A SINISTRA che vogliamo che sia l'intervallo - che in questo caso sarà 0
- L'altezza della gamma - vedi sotto per questo
- La Larghezza dell'intervallo - questo è 2 ha abbiamo DUE colonne nel nostro intervallo (il nome delle persone e la loro età)
Ora l'altezza dell'intervallo dovrà variare a seconda del numero di voci nella nostra tabella (che attualmente è 7).
Ovviamente vogliamo un modo per contare le righe nella nostra tabella che si aggiorni automaticamente, quindi un modo per farlo è usare la funzione CONTA.VALORI. Questo conta solo il numero di celle non vuote in un intervallo. Poiché i nostri nomi sono nella colonna B, il numero di voci nei nostri dati è COUNTA(B:B).
Nota che se dovessi inserirlo in una cella, otterresti il valore 8, poiché include i nomi dell'intestazione. Tuttavia, è irrilevante.
Quindi nella casella "Riferito a" mettiamo:
=OFFSET($B$5,0,0,conta(B:B),2)
E fai clic sul pulsante OK. La nostra gamma dinamica è ora creata.
Ora torna alle formule CERCA.VERT e sostituisci l'intervallo $B:4:$C11 con il nome del nostro nuovo intervallo dinamico AGE_DATA quindi abbiamo:
Finora non è cambiato nulla. Tuttavia, se aggiungiamo qualche altro nome alla nostra tabella:
E nella cella in cui avevamo Paul, sostituiscilo con un nuovo nome come Pedro (che non era nell'elenco originale):
E vediamo che Excel ha restituito automaticamente l'età di Pedro, anche se non abbiamo modificato le formule CERCA.VERT. Invece l'ambito della gamma dinamica è aumentato per includere i nomi extra.
Gli intervalli dinamici sono molto utili quando abbiamo volumi di dati crescenti, specialmente quando sono richieste le tabelle VLOOKUP e PIVOT.
