contingut
Tens taules amb dades en Excel que es poden redimensionar, és a dir, el nombre de files (columnes) pot augmentar o disminuir en el transcurs del treball? Si les mides de la taula "floten", haureu de controlar constantment aquest moment i corregir-lo:
- enllaços a les fórmules d'informe que fan referència a la nostra taula
- rangs inicials de taules dinàmiques que es construeixen segons la nostra taula
- rangs inicials de gràfics construïts segons la nostra taula
- intervals de desplegables que utilitzen la nostra taula com a font de dades
Tot això en total no us deixarà avorrir 😉
Serà molt més convenient i correcte crear un rang de "cautxú" dinàmic, que s'ajustarà automàticament en mida al nombre real de files i columnes de dades. Per implementar-ho, hi ha diverses maneres.
Mètode 1. Taula intel·ligent
Ressalteu el vostre rang de cel·les i seleccioneu-lo a la pestanya Inici - Format com a taula (Inici - Format com a taula):
Si no necessiteu el disseny de ratlles que s'afegeix a la taula com a efecte secundari, podeu desactivar-lo a la pestanya que apareix Constructor (Disseny). Cada taula creada d'aquesta manera rep un nom que es pot substituir per un de més convenient al mateix lloc de la pestanya Constructor (Disseny) en el camp Nom de la taula (Nom de la taula).
Ara podem utilitzar enllaços dinàmics a la nostra "taula intel·ligent":
- Taula 1 – enllaç a tota la taula excepte a la fila de capçalera (A2:D5)
- Taula 1[#Tot] – enllaç a tota la taula (A1:D5)
- Taula 1[Pere] – referència a una columna d'interval sense la primera capçalera de cel·la (C2:C5)
- Taula 1[#Headers] – enllaç a la "capçalera" amb els noms de les columnes (A1:D1)
Aquestes referències funcionen molt bé en fórmules, per exemple:
= SUM (Taula 1[Moscou]) - càlcul de la suma per a la columna "Moscou"
or
=VPR(F5;Taula 1;3;0) – cerqueu a la taula el mes de la cel·la F5 i emeteu la suma de Sant Petersburg (què és BUSCAR V?)
Aquests enllaços es poden utilitzar correctament quan es creen taules dinàmiques seleccionant a la pestanya Insereix - Taula dinàmica (Insereix - Taula dinàmica) i introduint el nom de la taula intel·ligent com a font de dades:
Si seleccioneu un fragment d'aquesta taula (per exemple, les dues primeres columnes) i creeu un diagrama de qualsevol tipus, en afegir línies noves, s'afegiran automàticament al diagrama.
Quan creeu llistes desplegables, no es poden utilitzar enllaços directes a elements de la taula intel·ligent, però podeu evitar aquesta limitació fàcilment mitjançant un truc tàctic: feu servir la funció INDIRECTE (INDIRECTE), que converteix el text en un enllaç:
Aquells. un enllaç a una taula intel·ligent en forma de cadena de text (entre cometes!) es converteix en un enllaç complet, i la llista desplegable normalment ho percep.
Mètode 2: rang amb nom dinàmic
Si convertir les vostres dades en una taula intel·ligent no és desitjable per algun motiu, podeu utilitzar un mètode una mica més complicat, però molt més subtil i versàtil: creeu un rang de nom dinàmic a Excel que faci referència a la nostra taula. Aleshores, com en el cas d'una taula intel·ligent, podeu utilitzar lliurement el nom de l'interval creat en qualsevol fórmula, informe, gràfic, etc. Comencem amb un exemple senzill:
Tasca: creeu un rang de nom dinàmic que faci referència a una llista de ciutats i s'estiri i es redueix automàticament en mida quan s'afegeixen ciutats noves o les suprimeixen.
Necessitarem dues funcions d'Excel integrades disponibles en qualsevol versió − POICPOZ (PARTIDA) per determinar l'última cel·la de l'interval, i ÍNDEX (ÍNDEX) per crear un enllaç dinàmic.
Trobar l'última cel·la amb MATCH
MATCH(valor_cerca, interval, tipus_coincidència) – una funció que cerca un valor determinat en un rang (fila o columna) i retorna el número ordinal de la cel·la on s'ha trobat. Per exemple, la fórmula MATCH(“Març”;A1:A5;0) retornarà el número 4 com a resultat, perquè la paraula “Març” es troba a la quarta cel·la de la columna A1:A5. L'últim argument de la funció Match_Type = 0 significa que estem buscant una coincidència exacta. Si no s'especifica aquest argument, la funció canviarà al mode de cerca pel valor més petit més proper; això és exactament el que es pot utilitzar amb èxit per trobar l'última cel·la ocupada a la nostra matriu.
L'essència del truc és senzilla. MATCH cerca cel·les en el rang de dalt a baix i, en teoria, hauria d'aturar-se quan trobi el valor més petit més proper al donat. Si especifiqueu un valor que òbviament és més gran que qualsevol disponible a la taula com a valor desitjat, aleshores MATCH arribarà al final de la taula, no trobarà res i donarà el número de seqüència de l'última cel·la emplenada. I ho necessitem!
Si només hi ha números a la nostra matriu, podem especificar un nombre com a valor desitjat, que és òbviament més gran que qualsevol dels de la taula:
Com a garantia, podeu utilitzar el número 9E + 307 (9 vegades 10 a la potència de 307, és a dir, 9 amb 307 zeros), el nombre màxim amb el qual pot treballar Excel en principi.
Si hi ha valors de text a la nostra columna, com a equivalent al nombre més gran possible, podeu inserir la construcció REPEAT ("i", 255) - una cadena de text que consta de 255 lletres "i" - l'última lletra de l'alfabet. Com que Excel compara els codis de caràcters en cercar, qualsevol text de la nostra taula serà tècnicament "més petit" que una línia tan llarga "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":
Genereu un enllaç mitjançant INDEX
Ara que sabem la posició de l'últim element no buit de la taula, queda per formar un enllaç a tot el nostre rang. Per a això fem servir la funció:
INDEX(interval; nombre_fila; nombre_columna)
Ofereix el contingut de la cel·la de l'interval per nombre de fila i columna, és a dir, per exemple, la funció =INDEX(A1:D5;3;4) a la nostra taula amb ciutats i mesos del mètode anterior donarà 1240: el contingut de la 3a fila i 4a columna, és a dir, les cel·les D3. Si només hi ha una columna, es pot ometre el seu número, és a dir, la fórmula INDEX(A2:A6;3) donarà "Samara" a l'última captura de pantalla.
I hi ha un matís no del tot obvi: si l'INDEX no només s'introdueix a la cel·la després del signe =, com és habitual, sinó que s'utilitza com a part final de la referència a l'interval després dels dos punts, ja no s'ofereix. el contingut de la cel·la, però la seva adreça! Així, una fórmula com $A$2:INDEX($A$2:$A$100;3) donarà una referència a l'interval A2:A4 a la sortida.
I aquí és on entra la funció MATCH, que inserim dins de l'INDEX per determinar dinàmicament el final de la llista:
=$A$2:INDEX($A$2:$A$100; COINCIDENT(REP(“I”;255);A2:A100))
Creeu un rang amb nom
Queda per empaquetar-ho tot en un sol tot. Obre una pestanya fórmula (Fórmules) I feu clic a Gestor de noms (Nom Gestor). A la finestra que s'obre, feu clic al botó Create (nou), introduïu el nom i la fórmula del nostre rang al camp Abast (Referència):
Queda per clicar OK i el rang preparat es pot utilitzar en qualsevol fórmula, llistes desplegables o gràfics.
- Utilitzant la funció BUSCARV per enllaçar taules i valors de cerca
- Com crear una llista desplegable d'emplenament automàtic
- Com crear una taula dinàmica per analitzar una gran quantitat de dades