Come impostare un intervallo di valori in Excel

Quando si utilizza Microsoft Excel, molto spesso l'utente non sa in anticipo quante informazioni ci saranno di conseguenza nella tabella. Pertanto, non capiamo in tutte le situazioni quale intervallo dovrebbe essere coperto. Dopotutto, un insieme di celle è un concetto variabile. Per sbarazzarsi di questo problema, è necessario rendere automatica la generazione dell'intervallo in modo che sia basata esclusivamente sulla quantità di dati che è stata inserita dall'utente.

Modifica automatica degli intervalli di celle in Excel

Il vantaggio degli intervalli automatici in Excel è che semplificano notevolmente l'utilizzo delle formule. Inoltre, consentono di semplificare notevolmente l'analisi di dati complessi che contengono un gran numero di formule, che includono molte funzioni. Puoi dare un nome a questo intervallo, quindi verrà aggiornato automaticamente a seconda dei dati che contiene.

Come apportare modifiche automatiche all'intervallo in Excel

Supponiamo che tu sia un investitore che ha bisogno di investire in qualche oggetto. Di conseguenza, vogliamo ottenere informazioni su quanto puoi guadagnare in totale per tutto il tempo in cui i soldi funzioneranno per questo progetto. Tuttavia, per ottenere queste informazioni, dobbiamo monitorare regolarmente quanto profitto totale ci porta questo oggetto. Fai lo stesso rapporto di questo screenshot.

A prima vista, la soluzione è ovvia: basta sommare l'intera colonna. Se compaiono voci in esso, l'importo verrà aggiornato in modo indipendente. Ma questo metodo ha molti svantaggi:

1. Se il problema viene risolto in questo modo, non sarà possibile utilizzare le celle incluse nella colonna B per altri scopi.
2. Tale tabella consumerà molta RAM, il che renderà impossibile l'utilizzo del documento su computer deboli.

Quindi, è necessario risolvere questo problema attraverso nomi dinamici. Per crearli, è necessario eseguire la seguente sequenza di azioni:

1. Vai alla scheda "Formule", che si trova nel menu principale. Ci sarà una sezione "Nomi definiti", dove c'è un pulsante "Assegna un nome", su cui dobbiamo fare clic.
2. Successivamente, apparirà una finestra di dialogo in cui è necessario compilare i campi come mostrato nello screenshot. È importante notare che dobbiamo applicare la funzione = SPOSTAMENTO insieme alla funzione CONTROLLOper creare un intervallo di aggiornamento automatico. 
3. Dopodiché dobbiamo usare la funzione SUM, che utilizza la nostra gamma dinamica come argomento. 

Dopo aver completato questi passaggi, possiamo vedere come viene aggiornata la copertura delle celle appartenenti all'intervallo "reddito" man mano che aggiungiamo nuovi elementi lì.

Funzione OFFSET in Excel

Diamo un'occhiata alle funzioni che abbiamo registrato in precedenza nel campo "intervallo". Usando la funzione SMALTIMENTO possiamo determinare la quantità di intervallo dato quante celle nella colonna B sono riempite. Gli argomenti della funzione sono i seguenti:

1. Inizia cella. Con questo argomento, l'utente può indicare quale cella nell'intervallo verrà considerata in alto a sinistra. Riporterà in basso ea destra.
2. Intervallo sfalsato per righe. Usando questo intervallo, impostiamo il numero di celle di cui dovrebbe verificarsi un offset dalla cella in alto a sinistra dell'intervallo. Puoi usare non solo valori positivi, ma zero e meno. In questo caso, lo spostamento potrebbe non verificarsi affatto o verrà eseguito nella direzione opposta.
3. Intervallo sfalsato per colonne. Questo parametro è simile al precedente, consente solo di impostare il grado di spostamento orizzontale dell'intervallo. Qui puoi anche utilizzare sia valori zero che negativi.
4. La quantità di intervallo in altezza. In effetti, il titolo di questo argomento ci chiarisce cosa significa. Questo è il numero di celle di cui l'intervallo dovrebbe aumentare.
5. Il valore dell'intervallo in larghezza. L'argomento è simile al precedente, riguarda solo le colonne.

Non è necessario specificare gli ultimi due argomenti se non è necessario. In questo caso, il valore dell'intervallo sarà una sola cella. Ad esempio, se si specifica la formula =SCARTO(A1;0;0), questa formula farà riferimento alla stessa cella di quella nel primo argomento. Se l'offset verticale è impostato su 2 unità, in questo caso la cella farà riferimento alla cella A3. Ora descriviamo in dettaglio cosa significa la funzione CONTROLLO.

Funzione COUNT in Excel

Utilizzando la funzione CONTROLLO determiniamo quante celle nella colonna B abbiamo riempito in totale. Cioè, usando due funzioni, determiniamo quante celle nell'intervallo sono riempite e, in base alle informazioni ricevute, determiniamo la dimensione dell'intervallo. Pertanto, la formula finale sarà la seguente: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Diamo un'occhiata a come comprendere correttamente il principio di questa formula. Il primo argomento indica dove inizia la nostra gamma dinamica. Nel nostro caso, questa è la cella B2. Ulteriori parametri hanno coordinate zero. Ciò suggerisce che non abbiamo bisogno di offset relativi alla cella in alto a sinistra. Tutto ciò che stiamo compilando è la dimensione verticale dell'intervallo, di cui abbiamo utilizzato la funzione CONTROLLO, che determina il numero di celle che contengono alcuni dati. Il quarto parametro che abbiamo inserito è l'unità. Quindi mostriamo che la larghezza totale dell'intervallo dovrebbe essere una colonna.

Quindi, usando la funzione CONTROLLO l'utente può utilizzare la memoria nel modo più efficiente possibile caricando solo quelle celle che contengono alcuni valori. Di conseguenza, non ci saranno ulteriori errori nel lavoro associati alle scarse prestazioni del computer su cui funzionerà il foglio di calcolo.

Di conseguenza, per determinare la dimensione dell'intervallo in base al numero di colonne, è necessario eseguire una sequenza di azioni simile, solo in questo caso è necessario specificare l'unità nel terzo parametro e la formula nel quarto CONTROLLO.

Vediamo che con l'aiuto delle formule di Excel, non solo puoi automatizzare i calcoli matematici. Questa è solo una goccia nell'oceano, ma in realtà ti consentono di automatizzare quasi tutte le operazioni che ti vengono in mente.

Grafici dinamici in Excel

Quindi, nell'ultimo passaggio, siamo stati in grado di creare un intervallo dinamico, la cui dimensione dipende completamente dal numero di celle riempite che contiene. Ora, sulla base di questi dati, puoi creare grafici dinamici che cambieranno automaticamente non appena l'utente apporta modifiche o aggiunge una colonna o una riga aggiuntiva. La sequenza di azioni in questo caso è la seguente:

1. Selezioniamo il nostro range, dopodiché inseriamo un grafico del tipo “Istogramma con raggruppamento”. Puoi trovare questo elemento nella sezione "Inserisci" nella sezione "Grafici - Istogramma".
2. Facciamo un clic sinistro del mouse su una colonna casuale dell'istogramma, dopodiché la funzione =SERIES() verrà mostrata nella riga della funzione. Nello screenshot puoi vedere la formula dettagliata. 
3. Successivamente, è necessario apportare alcune modifiche alla formula. Devi sostituire l'intervallo dopo "Sheet1!" al nome dell'intervallo. Ciò risulterà nella seguente funzione: =RIGA(Foglio1!$B$1;;Foglio1!reddito;1)
4. Ora resta da aggiungere un nuovo record al report per verificare se il grafico viene aggiornato automaticamente o meno.

Diamo un'occhiata al nostro diagramma.

Riassumiamo come abbiamo fatto. Nel passaggio precedente, abbiamo creato un intervallo dinamico, la cui dimensione dipende da quanti elementi contiene. Per fare ciò, abbiamo utilizzato una combinazione di funzioni CONTROLLO и SMALTIMENTO. Abbiamo nominato questo intervallo e quindi abbiamo utilizzato il riferimento a questo nome come intervallo del nostro istogramma. Quale intervallo specifico scegliere come origine dati nella prima fase non è così importante. La cosa principale è sostituirlo con il nome dell'intervallo in un secondo momento. In questo modo puoi risparmiare molta RAM.

Intervalli denominati e loro usi

Parliamo ora in modo più dettagliato di come creare correttamente intervalli denominati e utilizzarli per eseguire le attività impostate per l'utente di Excel.

Per impostazione predefinita, utilizziamo normali indirizzi di cella per risparmiare tempo. Ciò è utile quando è necessario scrivere un intervallo una o più volte. Se deve essere utilizzato continuamente o deve essere adattivo, è necessario utilizzare intervalli denominati. Rendono molto più semplice la creazione di formule e non sarà così difficile per l'utente analizzare formule complesse che includono un gran numero di funzioni. Descriviamo alcuni dei passaggi coinvolti nella creazione di intervalli dinamici.

Tutto inizia con la denominazione della cella. Per fare ciò, selezionalo semplicemente e poi scrivi il nome di cui abbiamo bisogno nel campo del suo nome. È importante che sia facile da ricordare. Ci sono alcune restrizioni da considerare quando si nomina:

1. La lunghezza massima è di 255 caratteri. Questo è abbastanza per assegnare un nome che il tuo cuore desidera.
2. Il nome non deve contenere spazi. Pertanto, se contiene più parole, è possibile separarle utilizzando il carattere di sottolineatura.

Se in seguito su altri fogli di questo file è necessario visualizzare questo valore o applicarlo per eseguire ulteriori calcoli, non è necessario passare al primo foglio. Puoi semplicemente annotare il nome di questa cella dell'intervallo.

Il passaggio successivo consiste nel creare un intervallo denominato. La procedura è sostanzialmente la stessa. Per prima cosa devi selezionare un intervallo, quindi specificarne il nome. Successivamente, questo nome può essere utilizzato in tutte le altre operazioni sui dati in Excel. Ad esempio, gli intervalli denominati vengono spesso utilizzati per definire la somma dei valori.

Inoltre, è possibile creare un intervallo denominato utilizzando la scheda Formule utilizzando lo strumento Imposta nome. Dopo averlo selezionato, apparirà una finestra in cui dobbiamo scegliere un nome per il nostro intervallo, oltre a specificare l'area in cui si estenderà manualmente. Puoi anche specificare dove opererà questo intervallo: all'interno di un singolo foglio o in tutto il libro.

Se è già stato creato un intervallo di nomi, per utilizzarlo è disponibile un servizio speciale chiamato gestore dei nomi. Consente non solo di modificare o aggiungere nuovi nomi, ma anche di eliminarli se non sono più necessari.

Va tenuto presente che quando si utilizzano intervalli denominati nelle formule, dopo averli eliminati, le formule non verranno automaticamente sovrascritte con i valori corretti. Pertanto, possono verificarsi errori. Pertanto, prima di eliminare un intervallo denominato, è necessario assicurarsi che non sia utilizzato in nessuna delle formule.

Un altro modo per creare un intervallo denominato è ottenerlo da una tabella. Per fare ciò, esiste uno strumento speciale chiamato "Crea dalla selezione". Come abbiamo capito, per usarlo, devi prima selezionare l'intervallo che modificheremo, quindi impostare il luogo in cui abbiamo le intestazioni. Di conseguenza, sulla base di questi dati, Excel elaborerà automaticamente tutti i dati e i titoli verranno assegnati automaticamente.

Se il titolo contiene più parole, Excel le separerà automaticamente con un trattino basso.

Pertanto, abbiamo capito come creare intervalli denominati dinamici e come consentono di automatizzare il lavoro con grandi quantità di dati. Come puoi vedere, è sufficiente utilizzare diverse funzioni e gli strumenti del programma integrati nella funzionalità. Non c'è niente di complicato, anche se può sembrare così a un principiante a prima vista.