Formula generica
=$A$1:INDEX($A:$A,lastrow)
Sommario
Un modo per creare un intervallo denominato dinamico in Excel consiste nell'usare la funzione INDICE. Nell'esempio mostrato, l'intervallo denominato "dati" è definito dalla seguente formula:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
che si risolve nell'intervallo $ A $ 2: $ A $ 10.
Nota: questa formula ha lo scopo di definire un intervallo denominato che può essere utilizzato in altre formule.
Spiegazione
Questa pagina mostra un esempio di un intervallo denominato dinamico creato con la funzione INDICE insieme alla funzione CONTA.VALORI. Gli intervalli denominati dinamici si espandono e si contraggono automaticamente quando i dati vengono aggiunti o rimossi. Sono un'alternativa all'utilizzo di una tabella Excel, che viene ridimensionata anche quando i dati vengono aggiunti o rimossi.
La funzione INDICE restituisce il valore in una determinata posizione in un intervallo o in una matrice. È possibile utilizzare INDICE per recuperare singoli valori o intere righe e colonne in un intervallo. Ciò che rende INDEX particolarmente utile per gli intervalli denominati dinamici è che in realtà restituisce un riferimento. Ciò significa che puoi utilizzare INDICE per costruire un riferimento misto come $ A $ 1: A100.
Nell'esempio mostrato, l'intervallo denominato "dati" è definito dalla seguente formula:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
che si risolve nell'intervallo $ A $ 2: $ A $ 10.
Come funzionano queste formule
Nota innanzitutto che questa formula è composta da due parti che si trovano su entrambi i lati dell'operatore di intervallo (:). A sinistra, abbiamo il riferimento iniziale per l'intervallo, codificato come:
$A$2
Sulla destra c'è il riferimento finale per l'intervallo, creato con INDICE in questo modo:
INDEX($A:$A,COUNTA($A:$A))
Qui, inseriamo INDICE tutta la colonna A per l'array, quindi utilizziamo la funzione CONTA.VALORI per calcolare l '"ultima riga" nell'intervallo. COUNTA funziona bene qui perché ci sono 10 valori nella colonna A, inclusa una riga di intestazione. COUNTA quindi restituisce 10, che va direttamente in INDICE come numero di riga. INDICE restituisce quindi un riferimento a $ A $ 10, l'ultima riga utilizzata nell'intervallo:
INDEX($A:$A,10) // resolves to $A$10
Quindi, il risultato finale della formula è questo intervallo:
$A$2:$A$10
Una gamma bidimensionale
L'esempio sopra funziona per un intervallo unidimensionale. Per creare un intervallo dinamico bidimensionale in cui anche il numero di colonne è dinamico, puoi utilizzare lo stesso approccio, espanso in questo modo:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Come prima, COUNTA viene utilizzato per calcolare "lastrow", e usiamo nuovamente COUNTA per ottenere "lastcolumn". Questi vengono forniti all'indice rispettivamente come row_num e column_num.
Tuttavia, per la matrice, forniamo il foglio di lavoro completo, immesso come tutte le 1048576 righe, che consente a INDICE di restituire un riferimento in uno spazio 2D.
Nota: Excel 2003 supporta solo 65535 righe.
Determinazione dell'ultima riga
Esistono diversi modi per determinare l'ultima riga (ultima posizione relativa) in un set di dati, a seconda della struttura e del contenuto dei dati nel foglio di lavoro:
- Ultima riga in dati misti con spazi vuoti
- Ultima riga in dati misti senza spazi
- Ultima riga nei dati di testo
- Ultima riga in dati numerici
