I dati di Excel cambiano frequentemente, quindi è utile creare un intervallo definito dinamico che si espande e si contrae automaticamente in base alle dimensioni dell’intervallo di dati. Vediamo come.
Utilizzando un intervallo definito dinamico, non sarà necessario modificare manualmente gli intervalli di formule, grafici e tabelle pivot quando i dati cambiano. Questo avverrà automaticamente.
Due formule vengono utilizzate per creare intervalli dinamici: OFFSET e INDICE. Questo articolo si concentrerà sull’utilizzo della funzione INDICE in quanto è un approccio più efficiente. OFFSET è una funzione volatile e può rallentare fogli di calcolo di grandi dimensioni.
Sommario:
Crea un intervallo definito dinamico in Excel
Per il nostro primo esempio, abbiamo l’elenco di dati a colonna singola visto di seguito.
Abbiamo bisogno che sia dinamico in modo che se vengono aggiunti o rimossi più paesi, l’intervallo si aggiorna automaticamente.
Per questo esempio, vogliamo evitare la cella di intestazione. Come tale, vogliamo l’intervallo $ A $ 2: $ A $ 6, ma dinamico. A tale scopo, fai clic su Formule> Definisci nome.
Digita “paesi” nella casella “Nome”, quindi inserisci la formula seguente nella casella “Si riferisce a”.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Digitare questa equazione in una cella del foglio di calcolo e quindi copiarla nella casella Nuovo nome a volte è più semplice e veloce.
Come funziona?
La prima parte della formula specifica la cella iniziale dell’intervallo (A2 nel nostro caso) e poi segue l’operatore di intervallo (:).
=$A$2:
L’utilizzo dell’operatore di intervallo impone alla funzione INDICE di restituire un intervallo anziché il valore di una cella. La funzione INDICE viene quindi utilizzata con la funzione CONTA.VALORI. COUNTA conta il numero di celle non vuote nella colonna A (sei nel nostro caso).
INDEX($A:$A,COUNTA($A:$A))
Questa formula chiede alla funzione INDICE di restituire l’intervallo dell’ultima cella non vuota nella colonna A ($ A $ 6).
Il risultato finale è $ A $ 2: $ A $ 6 e, a causa della funzione CONTA.VALORI, è dinamico, poiché troverà l’ultima riga. È ora possibile utilizzare questo nome definito “paesi” all’interno di una regola di convalida dei dati, formula, grafico o ovunque sia necessario fare riferimento ai nomi di tutti i paesi.
Crea un intervallo definito dinamico a due vie
Il primo esempio era solo dinamico in altezza. Tuttavia, con una leggera modifica e un’altra funzione CONTA.VALORI, puoi creare un intervallo dinamico sia per altezza che per larghezza.
In questo esempio, utilizzeremo i dati mostrati di seguito.
Questa volta creeremo un intervallo definito dinamico, che include le intestazioni. Fare clic su Formule> Definisci nome.
Digita “” vendite “nella casella” Nome “e inserisci la formula seguente nella casella” Si riferisce a “.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Questa formula utilizza $ A $ 1 come cella iniziale. La funzione INDICE utilizza quindi un intervallo dell’intero foglio di lavoro ($ 1: $ 1048576) per cercare e tornare.
Una delle funzioni CONTA.VALORI viene utilizzata per contare le righe non vuote e un’altra viene utilizzata per le colonne non vuote rendendole dinamiche in entrambe le direzioni. Sebbene questa formula partisse da A1, avresti potuto specificare qualsiasi cella iniziale.
È ora possibile utilizzare questo nome definito (vendite) in una formula o come serie di dati del grafico per renderli dinamici.