Contidos
Tes táboas con datos en Excel que se poden redimensionar, é dicir, o número de filas (columnas) pode aumentar ou diminuír no transcurso do traballo? Se os tamaños da táboa "flotan", entón terás que supervisar constantemente este momento e corrixilo:
- ligazóns en fórmulas de informes que fan referencia á nosa táboa
- intervalos iniciais de táboas dinámicas que se constrúen segundo a nosa táboa
- intervalos iniciais de gráficos construídos segundo a nosa táboa
- intervalos de menú despregable que usan a nosa táboa como fonte de datos
Todo isto en total non te deixará aburrir 😉
Será moito máis cómodo e correcto crear un rango dinámico de "goma", que axustará automaticamente o tamaño ao número real de filas e columnas de datos. Para implementar isto, hai varias formas.
Método 1. Mesa intelixente
Resalte o seu intervalo de celas e seleccione na pestana Inicio – Formato como táboa (Inicio – Formato como táboa):
Se non precisa o deseño de raias que se engade á táboa como efecto secundario, pode desactivalo na pestana que aparece Construtor (Deseño). Cada táboa creada deste xeito recibe un nome que pode ser substituído por outro máis cómodo no mesmo lugar da pestana Construtor (Deseño) no campo Nome da táboa (Nome da táboa).
Agora podemos usar ligazóns dinámicas á nosa "táboa intelixente":
- Táboa 1 – ligazón a toda a táboa excepto á fila de cabeceira (A2:D5)
- Táboa 1[#All] – ligazón a toda a táboa (A1:D5)
- Táboa 1[Pedro] – referencia a unha columna de rango sen a primeira cabeceira de cela (C2:C5)
- Táboa 1[#Headers] - ligazón á "cabeceira" cos nomes das columnas (A1:D1)
Tales referencias funcionan moi ben nas fórmulas, por exemplo:
= SUMATáboa 1[Moscova]) – cálculo da suma para a columna "Moscova"
or
=VPR(F5;Táboa 1;3;0) – busque na táboa o mes da cela F5 e emita a suma de San Petersburgo (que é BUSCAR V?)
Tales ligazóns pódense usar con éxito ao crear táboas dinámicas seleccionando na pestana Inserir - Táboa dinámica (Inserir - Táboa dinámica) e introducindo o nome da táboa intelixente como fonte de datos:
Se selecciona un fragmento desta táboa (por exemplo, as dúas primeiras columnas) e crea un diagrama de calquera tipo, ao engadir novas liñas, engadiranse automaticamente ao diagrama.
Ao crear listas despregábeis, non se poden usar ligazóns directas a elementos da táboa intelixente, pero pode evitar esta limitación facilmente mediante un truco táctico: use a función INDIRECTO (INDIRECTO), que converte o texto nunha ligazón:
Eses. unha ligazón a unha táboa intelixente en forma de cadea de texto (entre comiñas!) transfórmase nunha ligazón completa, e a lista despregable normalmente percíbeo.
Método 2: intervalo denominado dinámico
Se por algún motivo non é desexable converter os teus datos nunha táboa intelixente, podes usar un método un pouco máis complicado, pero moito máis sutil e versátil: crea un rango de nomes dinámico en Excel que se refire á nosa táboa. Despois, como no caso dunha táboa intelixente, pode usar libremente o nome do intervalo creado en calquera fórmula, informe, gráfico, etc. Comecemos cun exemplo sinxelo:
Tarefa: crea un intervalo de nomes dinámico que faría referencia a unha lista de cidades e estira e reduce o tamaño automaticamente ao engadir novas cidades ou eliminalas.
Necesitaremos dúas funcións de Excel integradas dispoñibles en calquera versión − POICPOZ (Coincidir) para determinar a última cela do intervalo, e Index (ÍNDICE) para crear un enlace dinámico.
Busca a última cela usando MATCH
COINCIDIR(valor_buscar, intervalo, tipo_coincidir) – unha función que busca un valor determinado nun rango (fila ou columna) e devolve o número ordinal da cela onde se atopou. Por exemplo, a fórmula MATCH("Marzo";A1:A5;0) devolverá o número 4 como resultado, porque a palabra "Marzo" está situada na cuarta cela da columna A1:A5. O último argumento da función Match_Type = 0 significa que estamos a buscar unha coincidencia exacta. Se non se especifica este argumento, a función cambiará ao modo de busca para o valor máis pequeno máis próximo; isto é exactamente o que se pode usar con éxito para atopar a última cela ocupada na nosa matriz.
A esencia do truco é sinxela. MATCH busca celas no intervalo de arriba a abaixo e, en teoría, debería deterse cando atope o valor máis pequeno máis próximo ao dado. Se especificas un valor que é obviamente maior que calquera dispoñible na táboa como o valor desexado, entón MATCH chegará ao final da táboa, non atopará nada e dará o número de secuencia da última cela cuberta. E o necesitamos!
Se só hai números na nosa matriz, entón podemos especificar un número como o valor desexado, que é obviamente maior que calquera dos da táboa:
Como garantía, pode usar o número 9E + 307 (9 veces 10 á potencia de 307, é dicir, 9 con 307 ceros), o número máximo co que Excel pode traballar en principio.
Se hai valores de texto na nosa columna, entón como equivalente ao maior número posible, pode inserir a construción REPEAT ("i", 255) - unha cadea de texto que consta de 255 letras "i" - a última letra de o alfabeto. Dado que Excel compara os códigos de caracteres ao buscar, calquera texto da nosa táboa técnicamente será "menor" que unha liña tan longa "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":
Xera unha ligazón usando INDEX
Agora que coñecemos a posición do último elemento non baleiro na táboa, queda por formar unha ligazón a todo o noso rango. Para iso usamos a función:
INDEX(intervalo; núm_fila; núm_columna)
Dá o contido da cela do rango por número de fila e columna, é dicir, por exemplo, a función =INDEX(A1:D5;3;4) na nosa táboa con cidades e meses do método anterior dará 1240: o contido da 3a fila e 4a columna, é dicir, as celas D3. Se só hai unha columna, pódese omitir o seu número, é dicir, a fórmula INDEX(A2:A6;3) dará "Samara" na última captura de pantalla.
E hai un matiz non totalmente obvio: se o ÍNDICE non se introduce só na cela despois do signo =, como é habitual, senón que se usa como parte final da referencia ao intervalo despois dos dous puntos, entón xa non se mostra. o contido da cela, pero o seu enderezo! Así, unha fórmula como $A$2:INDEX($A$2:$A$100;3) dará unha referencia ao intervalo A2:A4 na saída.
E aquí é onde entra a función MATCH, que inserimos dentro do INDEX para determinar dinámicamente o final da lista:
=$A$2:INDEX($A$2:$A$100; COINCIDIR(REP(“I”;255);A2:A100))
Crea un intervalo con nome
Queda por agrupalo todo nun único todo. Abre unha pestana fórmula (Fórmulas) E faga clic no botón Xestor de nomes (Xestor de nomes). Na xanela que se abre, fai clic no botón crear (novo), introduza o nome e a fórmula do noso intervalo no campo Alcance (Referencia):
Queda por premer OK e o rango listo pódese usar en calquera fórmula, lista despregábel ou gráfico.
- Usando a función BUSCARV para vincular táboas e valores de busca
- Como crear unha lista despregable de enchemento automático
- Como crear unha táboa dinámica para analizar unha gran cantidade de datos