Formula Excel: intervallo denominato dinamico con OFFSET -

Formula generica

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Sommario

Un modo per creare un intervallo denominato dinamico con una formula consiste nell'utilizzare la funzione OFFSET insieme alla funzione COUNTA. Gli intervalli dinamici sono noti anche come intervalli in espansione: si espandono e si contraggono automaticamente per accogliere dati nuovi o eliminati.

Nota: OFFSET è una funzione volatile, il che significa che ricalcola ad ogni modifica a un foglio di lavoro. Con una macchina moderna e un set di dati più piccolo, questo non dovrebbe causare problemi, ma potresti vedere prestazioni più lente su set di dati di grandi dimensioni. In tal caso, prendi in considerazione la creazione di un intervallo denominato dinamico con la funzione INDICE.

Nell'esempio mostrato, la formula utilizzata per l'intervallo dinamico è:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Spiegazione

Questa formula utilizza la funzione OFFSET per generare un intervallo che si espande e si contrae regolando l'altezza e la larghezza in base a un conteggio di celle non vuote.

Il primo argomento in OFFSET rappresenta la prima cella nei dati (l'origine), che in questo caso è la cella B5. I due argomenti successivi sono offset per righe e colonne e vengono forniti come zero.

Gli ultimi due argomenti rappresentano l'altezza e la larghezza. Altezza e larghezza vengono generate al volo utilizzando COUNTA, che rende dinamico il riferimento risultante.

Per l'altezza, utilizziamo la funzione COUNTA per contare i valori non vuoti nell'intervallo B5: B100. Ciò non presuppone alcun valore vuoto nei dati e nessun valore oltre B100. COUNTA restituisce 6.

Per la larghezza, usiamo la funzione COUNTA per contare i valori non vuoti nell'intervallo B5: Z5. Ciò presuppone nessuna cella di intestazione e nessuna intestazione oltre Z5. COUNTA restituisce 6.

A questo punto, la formula è simile a questa:

=OFFSET(B5,0,0,6,6)

Con queste informazioni, OFFSET restituisce un riferimento a B5: G10, che corrisponde a un intervallo di 6 righe di altezza per 6 colonne trasversali.

Nota: gli intervalli utilizzati per l'altezza e la larghezza devono essere regolati in modo che corrispondano al layout del foglio di lavoro.

Variazione con riferimenti colonna / riga completi

Puoi anche utilizzare riferimenti di colonna e riga completi per altezza e larghezza in questo modo:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Notare che l'altezza viene regolata con -2 per tenere conto dei valori di intestazione e titolo nelle celle B4 e B2. Il vantaggio di questo approccio è la semplicità delle gamme all'interno di COUNTA. Lo svantaggio deriva dalle colonne e righe intere di dimensioni enormi: è necessario prestare attenzione per evitare valori errati al di fuori dell'intervallo, poiché possono facilmente eliminare il conteggio.

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: