Contenuti
Avete tabelle con dati in Excel che possono essere ridimensionati, ovvero il numero di righe (colonne) può aumentare o diminuire nel corso del lavoro? Se le dimensioni della tabella "fluttuano", dovrai monitorare costantemente questo momento e correggerlo:
- link nelle formule di report che fanno riferimento alla nostra tabella
- intervalli iniziali di tabelle pivot costruite in base alla nostra tabella
- gamme iniziali di grafici costruiti secondo la nostra tabella
- intervalli per i menu a discesa che utilizzano la nostra tabella come origine dati
Tutto questo in totale non vi farà annoiare 😉
Sarà molto più conveniente e corretto creare un intervallo dinamico di "gomma", che si adatterà automaticamente alle dimensioni del numero effettivo di righe e colonne di dati. Per implementarlo, ci sono diversi modi.
Metodo 1. Tavolo intelligente
Evidenzia il tuo intervallo di celle e seleziona dalla scheda Home – Formatta come tabella (Home – Formatta come tabella):
Se non hai bisogno del disegno a righe che viene aggiunto al tavolo come effetto collaterale, puoi disattivarlo nella scheda che appare Costruttore (progettazione). Ogni tabella così creata riceve un nome che può essere sostituito con uno più conveniente nella stessa posizione della scheda Costruttore (progettazione) nel campo Nome della tabella (Nome tabella).
Ora possiamo utilizzare i collegamenti dinamici alla nostra "tabella intelligente":
- Tabella 1 – collegamento all'intera tabella ad eccezione della riga di intestazione (A2:D5)
- Tabella1[#Tutti] – collegamento all'intera tabella (A1:D5)
- Tabella1[Pietro] – riferimento a una colonna di intervallo senza la prima intestazione di cella (C2:C5)
- Tabella1[#Intestazioni] – link alla “testata” con i nomi delle colonne (A1:D1)
Tali riferimenti funzionano alla grande nelle formule, ad esempio:
= SUM (Tabella1[Mosca]) – calcolo della somma per la colonna “Mosca”
or
=PRV(F5;Tabella 1;3;0) – cerca nella tabella il mese dalla cella F5 ed emetti la somma di San Pietroburgo (che cos'è VLOOKUP?)
Tali collegamenti possono essere utilizzati con successo durante la creazione di tabelle pivot selezionando nella scheda Inserisci – Tabella pivot (Inserisci – Tabella pivot) e inserendo il nome della smart table come origine dati:
Se selezioni un frammento di tale tabella (ad esempio, le prime due colonne) e crei un diagramma di qualsiasi tipo, quando aggiungi nuove righe, verranno automaticamente aggiunte al diagramma.
Quando si creano elenchi a discesa, non è possibile utilizzare collegamenti diretti agli elementi della tabella intelligente, ma è possibile aggirare facilmente questa limitazione utilizzando un trucco tattico: utilizzare la funzione INDIRETTI (INDIRETTO), che trasforma il testo in un collegamento:
Quelli. un collegamento a una tabella intelligente sotto forma di stringa di testo (tra virgolette!) Si trasforma in un collegamento completo e l'elenco a discesa normalmente lo percepisce.
Metodo 2: intervallo denominato dinamico
Se per qualche motivo non è desiderabile trasformare i tuoi dati in una tabella intelligente, puoi utilizzare un metodo leggermente più complicato, ma molto più sottile e versatile: creare un intervallo denominato dinamico in Excel che faccia riferimento alla nostra tabella. Quindi, come nel caso di una smart table, puoi utilizzare liberamente il nome dell'intervallo creato in qualsiasi formula, report, grafico, ecc. Iniziamo con un semplice esempio:
Task: crea un intervallo denominato dinamico che faccia riferimento a un elenco di città e si allunghi e rimpicciolisca automaticamente quando si aggiungono nuove città o le si elimina.
Avremo bisogno di due funzioni integrate di Excel disponibili in qualsiasi versione − POICPOZ (INCONTRO) per determinare l'ultima cella dell'intervallo, e INDICE (INDICE) per creare un collegamento dinamico.
Trovare l'ultima cella usando MATCH
MATCH(valore_ricerca, intervallo, tipo_corrispondenza) – una funzione che ricerca un dato valore in un intervallo (riga o colonna) e restituisce il numero ordinale della cella in cui è stato trovato. Ad esempio, la formula MATCH("March";A1:A5;0) restituirà il numero 4 come risultato, perché la parola "March" si trova nella quarta cella della colonna A1:A5. L'ultimo argomento della funzione Match_Type = 0 significa che stiamo cercando una corrispondenza esatta. Se questo argomento non è specificato, la funzione passerà alla modalità di ricerca per il valore più piccolo più vicino: questo è esattamente ciò che può essere utilizzato con successo per trovare l'ultima cella occupata nel nostro array.
L'essenza del trucco è semplice. CONFRONTA cerca le celle nell'intervallo dall'alto verso il basso e, in teoria, dovrebbe interrompersi quando trova il valore più piccolo più vicino a quello dato. Se specifichi un valore che è ovviamente maggiore di qualsiasi disponibile nella tabella come valore desiderato, MATCH raggiungerà la fine della tabella, non troverà nulla e fornirà il numero di sequenza dell'ultima cella riempita. E ne abbiamo bisogno!
Se ci sono solo numeri nel nostro array, allora possiamo specificare un numero come valore desiderato, che è ovviamente maggiore di quelli nella tabella:
A titolo di garanzia, puoi utilizzare il numero 9E + 307 (9 per 10 alla potenza di 307, ovvero 9 con 307 zeri), il numero massimo con cui Excel può funzionare in linea di principio.
Se ci sono valori di testo nella nostra colonna, allora come equivalente del numero più grande possibile, puoi inserire la costruzione REPEAT(“i”, 255) – una stringa di testo composta da 255 lettere “i” – l'ultima lettera di l'alfabeto. Poiché Excel confronta effettivamente i codici dei caratteri durante la ricerca, qualsiasi testo nella nostra tabella sarà tecnicamente "più piccolo" di una riga così lunga "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":
Genera un collegamento usando INDEX
Ora che conosciamo la posizione dell'ultimo elemento non vuoto nella tabella, resta da formare un collegamento a tutta la nostra gamma. Per questo utilizziamo la funzione:
INDEX(intervallo; num_riga; num_colonna)
Fornisce il contenuto della cella dall'intervallo per riga e numero di colonna, ad esempio, la funzione =INDEX(A1:D5;3;4) nella nostra tabella con città e mesi dal metodo precedente darà 1240 – il contenuto dalla 3a riga e 4a colonna, ovvero le celle D3. Se c'è solo una colonna, allora il suo numero può essere omesso, cioè la formula INDEX(A2:A6;3) darà “Samara” nell'ultimo screenshot.
E c'è una sfumatura non del tutto ovvia: se l'INDEX non è solo inserito nella cella dopo il segno =, come al solito, ma è usato come parte finale del riferimento all'intervallo dopo i due punti, allora non cede più il contenuto della cella, ma il suo indirizzo! Pertanto, una formula come $A$2:INDEX($A$2:$A$100;3) darà un riferimento all'intervallo A2:A4 nell'output.
Ed è qui che entra in gioco la funzione CONFRONTA, che inseriamo all'interno dell'INDICE per determinare dinamicamente la fine della lista:
=$A$2:INDICE($A$2:$A$100; CONFRONTA(REP(“I”;255);A2:A100))
Crea un intervallo denominato
Resta da impacchettare tutto in un unico insieme. Apri una scheda formula (formule) E clicca il Nome Manager (Nome Gestore). Nella finestra che si apre, fai clic sul pulsante Creare (nuovo), inserisci il nome dell'intervallo e la formula nel campo Escursione (Riferimento):
Resta da cliccare OK e l'intervallo pronto può essere utilizzato in qualsiasi formula, elenco a discesa o grafico.
- Utilizzo della funzione CERCA.VERT per collegare tabelle e cercare valori
- Come creare un elenco a discesa con compilazione automatica
- Come creare una tabella pivot per analizzare una grande quantità di dati