cuntenutu
Avete tavule cù dati in Excel chì ponu esse ridimensionati, vale à dì u numeru di fila (columne) pò aumentà o diminuite in u cursu di u travagliu? Se i taglii di a tavola "float", allora duverete monitorà constantemente stu mumentu è currettu:
- ligami in formule di rapportu chì riferenu à a nostra tavula
- intervalli iniziali di tavule pivot chì sò custruiti secondu a nostra tavula
- intervalli iniziali di charts custruiti secondu a nostra tavula
- intervalli per dropdowns chì utilizanu a nostra tavula cum'è fonte di dati
Tuttu chistu in tuttu ùn vi permetterà di annoiarsi 😉
Serà assai più còmuda è currettu per creà una gamma dinamica di "gomma", chì automaticamente aghjustà in grandezza à u numeru attuale di fila è colonne di dati. Per implementà questu, ci sò parechje manere.
Metudu 1. Smart table
Evidenziate a vostra gamma di cellule è selezziunate da a tabulazione Home - Format as Table (Home - Format as Table):
Se ùn avete micca bisognu di u disignu strisce chì hè aghjuntu à a tavula cum'è un effettu secundariu, pudete disattivà in a tabulazione chì appare. Custruttore (Design). Ogni tavula creata in questu modu riceve un nome chì pò esse rimpiazzatu cù un più còmuda in u stessu locu nantu à a tabulazione Custruttore (Design) in u campu Nome di a tavola (Nome di a tavula).
Avà pudemu usà ligami dinamichi à a nostra "tavola intelligente":
- Table 1 - ligame à tutta a tavola eccettu per a fila di l'intestazione (A2: D5)
- Tabella 1[#Tutti] - ligame à tutta a tavola (A1: D5)
- Tabella 1 [Petru] - riferenza à una colonna di intervallu senza a prima cella-header (C2: C5)
- Tabella 1[#Headers] - ligame à l'"header" cù i nomi di e colonne (A1: D1)
Tali riferimenti funzionanu bè in formule, per esempiu:
= SUM (Tabella 1 [Mosca]) - calculu di a somma per a colonna "Mosca"
or
=VPR(F5;Table 1;3;0) - cercate in a tavula per u mese da a cell F5 è emette a somma di San Petruburgu per questu (chì hè VLOOKUP?)
Tali ligami ponu esse aduprati cù successu quandu creanu tabelle pivot, selezziunate nantu à a tabulazione Insert - Pivot Table (Insert - Pivot Table) è inserendu u nome di a tavola intelligente cum'è a fonte di dati:
Se selezziunate un frammentu di una tale tavola (per esempiu, i primi dui culonni) è creanu un diagramma di ogni tipu, da quandu aghjunghjenu novi linee, seranu automaticamente aghjuntu à u diagramma.
Quandu creanu listi drop-down, i ligami diretti à l'elementi di tavulinu intelligenti ùn ponu micca esse aduprati, ma pudete facilmente aggirari sta limitazione cù un truccu tatticu - utilizate a funzione INDIRETTA (INDIRETTA), chì trasforma u testu in un ligame:
Quelli. un ligame à una tavula intelligente in a forma di una stringa di testu (in virgulette!) Si trasforma in un ligame cumpletu, è a lista di u drop-down normalment percive.
Metudu 2: gamma dinamica chjamata
Se trasfurmà i vostri dati in una tavula intelligente hè indesevule per qualchì mutivu, pudete aduprà un metudu pocu più complicatu, ma assai più sottile è versatile - crea un intervallu dinamicu chjamatu in Excel chì si riferisce à a nostra tavula. Allora, cum'è in u casu di una tavola intelligente, pudete aduprà liberamente u nome di a gamma creata in qualsiasi formule, rapporti, charts, etc. Cuminciamu cù un esempiu simplice:
compitu: fate un intervallu dinamicu chjamatu chì si riferisce à una lista di cità è automaticamente si stende è si riduce in dimensione quandu aghjunghjenu e cità novi o sguassate.
Avemu bisognu di duie funzioni Excel integrate dispunibili in ogni versione - POICPOZ (MATCH) per determinà l'ultima cellula di a gamma, è INDEX (INDICE) per creà un ligame dinamicu.
Truvà l'ultima cellula cù MATCH
MATCH(lookup_value, range, match_type) - una funzione chì cerca un valore determinatu in un intervallu (fila o colonna) è torna u numeru ordinale di a cellula induve hè stata truvata. Per esempiu, a formula MATCH ("March"; A1: A5; 0) torna u numeru 4 in u risultatu, perchè a parolla "March" si trova in a quarta cellula in a colonna A1: A5. L'ultimu argumentu di funzione Match_Type = 0 significa chì circhemu una partita esatta. Se questu argumentu ùn hè micca specificatu, a funzione cambierà à u modu di ricerca per u valore più chjucu più vicinu - questu hè esattamente ciò chì pò esse usatu bè per truvà l'ultima cellula occupata in u nostru array.
L'essenza di u truccu hè simplice. MATCH cerca e cellule in u intervallu da u cima à u fondu è, in teoria, deve fermà quandu trova u più chjucu valore più vicinu à quellu datu. Se specificate un valore chì hè ovviamente più grande di qualsiasi dispunibule in a tavula cum'è u valore desideratu, allora MATCH ghjunghjerà à a fine di a tavula, ùn truvà nunda è dà u numeru di sequenza di l'ultima cellula piena. È avemu bisognu!
Se ci sò solu numeri in u nostru array, allora pudemu specificà un numeru cum'è u valore desideratu, chì hè ovviamente più grande di quellu di quelli in a tavula:
Per una guaranzia, pudete aduprà u numeru 9E + 307 (9 volte 10 à u putere di 307, vale à dì 9 cù 307 zeros) - u numeru massimu chì Excel pò travaglià in principiu.
Se ci sò valori di testu in a nostra colonna, allora cum'è l'equivalente di u più grande numeru pussibule, pudete inserisce a custruzzione REPEAT ("i", 255) - una stringa di testu custituita da 255 lettere "i" - l'ultima lettera di l'alfabetu. Siccomu Excel compara in realtà i codici di caratteri durante a ricerca, ogni testu in a nostra tavula serà tecnicamente "più chjucu" cà una linea cusì longa "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":
Generate un ligame cù INDEX
Avà chì sapemu a pusizione di l'ultimu elementu micca viotu in a tavula, ferma per furmà un ligame à tutta a nostra gamma. Per questu avemu aduprà a funzione:
INDEX(range; row_num; column_num)
Dà u cuntenutu di a cellula da a gamma per u numeru di fila è colonna, vale à dì, per esempiu, a funzione = INDEX (A1: D5; 3; 4) in a nostra tavula cù cità è mesi da u metudu precedente darà 1240 - u cuntenutu da a 3a fila è a 4a colonna, vale à dì e cellule D3. Se ci hè una sola colonna, allora u so numeru pò esse omessi, vale à dì a formula INDEX (A2: A6;3) darà "Samara" in l'ultima screenshot.
È ci hè una sfumatura micca cumplettamente evidenti: se l'INDICE ùn hè micca solu intrudutu in a cellula dopu à u signu =, cum'è di solitu, ma hè utilizatu cum'è a parte finale di a riferenza à a gamma dopu à u colon, allora ùn dà più. u cuntenutu di a cellula, ma u so indirizzu! Cusì, una formula cum'è $A$2:INDEX($A$2:$A$100;3) darà una riferimentu à a gamma A2:A4 à l'output.
È questu hè induve a funzione MATCH entra, chì inserimu in l'INDICE per determinà dinamicamente a fine di a lista:
=$A$2:INDICE($A$2:$A$100; MATCH(REP(“I”;255);A2:A100))
Crea un intervallu chjamatu
Resta à imballà tuttu in un unicu tutale. Aprite una tabulazione fòrmula (formule) È cliccate nantu à u Manager di Nome (Nome Manager). In a finestra chì si apre, cliccate nantu à u buttone creà (novu), entre in u nostru nome di gamma è formula in u campu Range (Riferimentu):
Resta da cliccà OK è a gamma pronta pò esse usata in qualsiasi formule, liste drop-down o charts.
- Utilizà a funzione VLOOKUP per ligà e tabelle è i valori di ricerca
- Cume creà una lista di menu di auto-populazione
- Cumu creà una tabella pivot per analizà una grande quantità di dati