I tuoi dati Excel cambiano spesso, quindi è utile creare un intervallo definito dinamico che si espande e si contrae automaticamente alla dimensione del tuo intervallo di dati. Vediamo come.
Quando si utilizza una gamma dinamica definita, non dovrai modificare manualmente gli intervalli delle tue formule, pivot grafici e tabelle quando i dati cambiano. Questo accadrà automaticamente.
Vengono utilizzate due formule per creare intervalli dinamici: OFFSET e INDICE. Questo post si concentrerà sull'uso della funzione INDICE, poiché è un approccio più efficiente. OFFSET è una funzione volatile e può rallentare fogli di calcolo di grandi dimensioni.
Crea un intervallo dinamico definito in Excel
Per il nostro primo esempio, abbiamo l'elenco dei dati a colonna singola visto di seguito.
Abbiamo bisogno che questo sia dinamico, quindi se vengono aggiunti o rimossi più paesi, la gamma si aggiorna automaticamente.
Per questo esempio, vogliamo evitare la cella di intestazione. Come tale, vogliamo la gamma $ UN $ 2: $ UN $ 6, ma dinamico. Fallo facendo clic su Formule> Imposta nome.
Scriba “Paesi” nella cornice “Nome” e quindi immettere la formula sottostante nella casella “Si riferisce a”.
=$A$2:INDICE($UN:$UN,CONTA($UN:$UN))
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 segui l'operatore di gamma (:).
=$A$2:
L'uso dell'operatore intervallo forza la funzione INDICE a restituire un intervallo anziché il valore di una cella. La funzione INDEX viene quindi utilizzata con la funzione COUNT. COUNT conta il numero di celle che non sono vuote nella colonna A (sei nel nostro caso).
INDICE($UN:$UN,CONTA($UN:$UN))
Questa formula chiede alla funzione INDICE di restituire l'intervallo dell'ultima cella non vuota nella colonna A ($ UN $ 6).
Il risultato finale è $ UN $ 2: $ UN $ 6, e grazie alla funzione COUNT, è dinamico, poiché troverà l'ultima riga. Ora puoi utilizzare questo nome definito da "paesi" all'interno di una regola di convalida dei dati, formula, grafico o dove dobbiamo fare riferimento ai nomi di tutti i paesi.
Crea una gamma dinamica bidirezionale definita
Il primo esempio era solo dinamico in altezza. Nonostante questo, con una leggera modifica e un'altra funzione COUNT, puoi creare un intervallo dinamico sia in altezza che in larghezza.
In questo esempio, useremo i dati mostrati di seguito.
Questa volta, creeremo una gamma dinamica definita, che include le intestazioni. Fare clic su Formule> Imposta nome.
Scriba “saldi” nella cornice “Nome” e inserisci la formula sottostante nella casella “Si riferisce a”.
=$A$1:INDICE($1:$1048576,CONTA($UN:$UN),CONTA($1:$1))
Questa formula usa $ UN $ 1 come cella iniziale. Successivamente, La funzione INDICE utilizza un intervallo dell'intero foglio di lavoro ($ 1: $ 1048576) per cercare e tornare.
Una delle funzioni CONTA.VALORI viene utilizzata per contare le righe che non sono vuote, e un altro viene utilizzato per le colonne che non sono vuote, che lo rende dinamico in entrambe le direzioni. Anche se questa formula è partita da A1, avrebbe potuto specificare qualsiasi cella iniziale.
Ora puoi usare questo nome definito (saldi) in una formula o come serie di dati del grafico per renderlo dinamico.
