Contenido
¿Tiene tablas con datos en Excel que se pueden cambiar de tamaño, es decir, el número de filas (columnas) puede aumentar o disminuir en el transcurso del trabajo? Si los tamaños de la tabla "flotan", deberá monitorear constantemente este momento y corregirlo:
- enlaces en fórmulas de informe que hacen referencia a nuestra tabla
- rangos iniciales de tablas dinámicas que se construyen de acuerdo con nuestra tabla
- rangos iniciales de gráficos construidos de acuerdo con nuestra tabla
- rangos para menús desplegables que usan nuestra tabla como fuente de datos
Todo esto en total no dejará que te aburras 😉
Será mucho más conveniente y correcto crear un rango dinámico de "goma", que se ajustará automáticamente en tamaño al número real de filas y columnas de datos. Para implementar esto, hay varias formas.
Método 1. Mesa inteligente
Resalte su rango de celdas y seleccione de la pestaña Inicio – Formato como tabla (Inicio – Formato como tabla):
Si no necesita el diseño de rayas que se agrega a la tabla como efecto secundario, puede desactivarlo en la pestaña que aparece Constructor (Diseño). Cada tabla creada de esta manera recibe un nombre que puede ser reemplazado por uno más conveniente en el mismo lugar de la pestaña. Constructor (Diseño) en el campo Nombre de la tabla (Nombre de la tabla).
Ahora podemos usar enlaces dinámicos a nuestra "tabla inteligente":
- Tabla 1 – enlace a toda la tabla excepto a la fila del encabezado (A2:D5)
- Tabla1 [#All] – enlace a toda la tabla (A1:D5)
- Mesa1[Pedro] – referencia a una columna de rango sin el primer encabezado de celda (C2:C5)
- Tabla1[#Encabezados] – enlace al “encabezado” con los nombres de las columnas (A1:D1)
Tales referencias funcionan muy bien en fórmulas, por ejemplo:
= SUMA (Mesa1[Moscú]) – cálculo de la suma para la columna “Moscú”
or
=RPV(F5;Tabla 1;3;0) – busque en la tabla el mes de la celda F5 y emita la suma de San Petersburgo para él (¿qué es BUSCARV?)
Dichos enlaces se pueden usar con éxito al crear tablas dinámicas seleccionando en la pestaña Insertar - Tabla dinámica (Insertar - Tabla dinámica) e ingresando el nombre de la tabla inteligente como fuente de datos:
Si selecciona un fragmento de dicha tabla (por ejemplo, las dos primeras columnas) y crea un diagrama de cualquier tipo, al agregar nuevas líneas, se agregarán automáticamente al diagrama.
Al crear listas desplegables, no se pueden usar enlaces directos a elementos de tablas inteligentes, pero puede sortear fácilmente esta limitación usando un truco táctico: use la función INDIRECT (INDIRECTO), que convierte el texto en un enlace:
Aquellos. un enlace a una tabla inteligente en forma de cadena de texto (¡entre comillas!) se convierte en un enlace completo, y la lista desplegable normalmente lo percibe.
Método 2: rango dinámico con nombre
Si por alguna razón no desea convertir sus datos en una tabla inteligente, entonces puede usar un método un poco más complicado, pero mucho más sutil y versátil: cree un rango dinámico con nombre en Excel que se refiera a nuestra tabla. Luego, como en el caso de una tabla inteligente, puede usar libremente el nombre del rango creado en cualquier fórmula, informe, gráfico, etc. Comencemos con un ejemplo simple:
Tarea: cree un rango dinámico con nombre que haga referencia a una lista de ciudades y se estire y reduzca automáticamente al agregar nuevas ciudades o eliminarlas.
Necesitaremos dos funciones integradas de Excel disponibles en cualquier versión: POICPOZ (JUEGO) para determinar la última celda del rango, y ÍNDICE (ÍNDICE) para crear un enlace dinámico.
Encontrar la última celda usando MATCH
COINCIDIR (valor_buscado, rango, tipo_coincidencia) – una función que busca un valor dado en un rango (fila o columna) y devuelve el número ordinal de la celda donde se encontró. Por ejemplo, la fórmula COINCIDIR(“Marzo”;A1:A5;0) devolverá el número 4 como resultado, porque la palabra “Marzo” se encuentra en la cuarta celda de la columna A1:A5. El último argumento de la función Match_Type = 0 significa que estamos buscando una coincidencia exacta. Si no se especifica este argumento, la función cambiará al modo de búsqueda del valor más pequeño más cercano; esto es exactamente lo que se puede usar con éxito para encontrar la última celda ocupada en nuestra matriz.
La esencia del truco es simple. MATCH busca celdas en el rango de arriba a abajo y, en teoría, debería detenerse cuando encuentra el valor más pequeño más cercano al dado. Si especifica un valor que es obviamente mayor que cualquiera disponible en la tabla como el valor deseado, COINCIDIR llegará al final de la tabla, no encontrará nada y dará el número de secuencia de la última celda llena. ¡Y lo necesitamos!
Si solo hay números en nuestra matriz, entonces podemos especificar un número como el valor deseado, que obviamente es mayor que cualquiera de los de la tabla:
Como garantía, puede utilizar el número 9E + 307 (9 por 10 elevado a 307, es decir, 9 con 307 ceros), el número máximo con el que Excel puede trabajar en principio.
Si hay valores de texto en nuestra columna, entonces, como el equivalente del número más grande posible, puede insertar la construcción REPETIR ("i", 255) - una cadena de texto que consta de 255 letras "i" - la última letra de el alfabeto. Dado que Excel en realidad compara los códigos de caracteres al buscar, cualquier texto en nuestra tabla será técnicamente "más pequeño" que una línea tan larga "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":
Generar un enlace usando ÍNDICE
Ahora que conocemos la posición del último elemento no vacío en la tabla, queda por formar un enlace a toda nuestra gama. Para esto usamos la función:
ÍNDICE(rango; núm_fila; núm_columna)
Da el contenido de la celda del rango por número de fila y columna, es decir, por ejemplo, la función =ÍNDICE(A1:D5;3;4) en nuestra tabla con ciudades y meses del método anterior dará 1240 – el contenido de la 3ra fila y la 4ta columna, es decir, las celdas D3. Si solo hay una columna, se puede omitir su número, es decir, la fórmula ÍNDICE (A2: A6; 3) dará "Samara" en la última captura de pantalla.
Y hay un matiz no del todo obvio: si el ÍNDICE no se ingresa simplemente en la celda después del signo =, como de costumbre, sino que se usa como la parte final de la referencia al rango después de los dos puntos, entonces ya no da el contenido de la celda, sino su dirección! Por lo tanto, una fórmula como $A$2:ÍNDICE($A$2:$A$100;3) dará una referencia al rango A2:A4 en la salida.
Y aquí es donde entra la función COINCIDIR, que insertamos dentro del ÍNDICE para determinar dinámicamente el final de la lista:
=$A$2:ÍNDICE($A$2:$A$100; PARTIR(REP(“I”;255);A2:A100))
Crear un rango con nombre
Queda por empacar todo en un solo todo. abrir una pestaña fórmula (Fórmulas) Y haga clic en Administrador de nombre (Gerente de nombre). En la ventana que se abre, haga clic en el botón Crear (nuevo), ingrese nuestro nombre de rango y fórmula en el campo Gama de Colores (Referencia):
Queda por hacer clic en OK y el rango listo se puede usar en cualquier fórmula, lista desplegable o gráfico.
- Uso de la función BUSCARV para vincular tablas y valores de búsqueda
- Cómo crear una lista desplegable de autocompletado
- Cómo crear una tabla dinámica para analizar una gran cantidad de datos