Como crear Una Tabla De Datos Dinámica En Excel

Las tablas dinámicas en Excel son tablas que te dan la posibilidad de determinar fácilmente los campos que se mostrarán en las columnas, en las filas o como valores de la tabla, de tal forma que sea fácil para tí hacer modificaciones, a tales datos, cuando así lo desees.

En este tutorial, te mostraremos cómo utilizar cuadros combinados y la función indirecta para Crear una tabla de datos dinámica en Excel, que se puede usar para visualizar mejor los datos y usarse como una solución para una herramienta de informes dirigida al cliente o al gerente.

Crear una tabla de datos dinámica en Excel

Este tutorial utilizará datos ficticios para mostrar un panel de indicadores clave de rendimiento básico con datos del 15 de abril al 17 de marzo. La función indirecta se utilizará específicamente para hacer referencia dinámica a todas nuestras hojas de respaldo.

Crear una experiencia de usuario completa y coherente en un panel basado en Excel puede ser realmente difícil si no sabes por dónde empezar. A menudo, la forma más fácil de abordar la situación será utilizar tablas dinámicas y gráficos dinámicos, que vienen con herramientas útiles como rebanadoras para filtrar dinámicamente los datos con sólo tocar un botón.

Sin embargo, hay algunas cosas que molestan en cuanto a los Pivotes (complemento de Excel que se emplea para realizar análisis de datos y para realizar modelos de datos avanzados), principalmente porque requieren que los datos estén en un diseño particular; que pueden ser restrictivos en el tipo y diseño de la salida que producen (aunque brindan un buen nivel de opciones); y cuando se actualizan o cambian datos, dentro de una hoja de cálculo, los datos dinámicos no se actualizan a menos que se indique explícitamente (lo que puede crear una experiencia discordante al manejar y presentar conjuntos de datos más grandes).

Entonces, ¿Cómo harías para crear una tabla de datos dinámicos y un tablero sin usar tablas dinámicas? Hay una cantidad aparentemente enorme de formas de realizar esta tarea, pero la forma que encontramos más versátil y  cómoda es haciendo uso del cuadro combinado y diferentes funciones de Excel como INDIRECTO (cubierto en este tutorial), OFFSET , SUMIFS e INDICE.

Paso 1: Primero, necesitarás crear una tabla de respaldo estructurada que sigan un diseño coherente y que contenga los datos que deseas que aparezcan en tu tabla dinámica. Esto es esencial para permitir que la función "Indirecto" genere consistentemente las matrices correctas para tus datos.

La parte superior izquierda de la hoja incluye una descripción rápida de dónde obtuvimos los datos y qué tan actualizados están nuestros datos. Incluir esto es la mejor práctica general para mantener las hojas de respaldo de los datos claras y entendibles que otras personas comprendan el historial de los datos y para que puedas garantizar una mejor calidad de los datos. Las tres últimas hojas de este libro de trabajo deberán seguir el mismo formato, incluida la misma lista de organizaciones y fechas.

Paso 2: Ahora crea la hoja de salida que contendrá la tabla de datos dinámicos. En este ejemplo, hemos copiado la misma estructura utilizada en las últimas hojas, con las mismas organizaciones y todas las fechas. Sería posible elegir fechas y organizaciones para esta hoja de salida, ya que la fórmula utilizada podrá tratar cualquier combinación de las 2 variables.

Paso 3: Ahora puedes crear el cuadro combinado, las listas de medidas y nombres de hoja que alimentan el cuadro combinado. Para crear un cuadro combinado, ve a la pestaña “Programador de la cinta” Si la pestaña de desarrollador de la cinta no está visible para ti, dirígete a: Archivo -> Opciones -> Personalizar cinta luego, en la lista del lado derecho de esta pestaña, marca la casilla de selección para la pestaña de "Programador".

Paso 4: En la pestaña de "Programador", selecciona” Insertar” y en "Controles de formulario" selecciona la opción de “Cuadro combinado”. A continuación, deberías poder dibujar un cuadro combinado en la hoja de cálculo.

Paso 5: Ahora crea 2 listas: una de nombres de medidas y otra correspondiente a cada una de las hojas traseras.

Tal vez te interese: Cómo Hacer Un Mapa En Excel Desde Cero

Paso 6: Ahora necesitas vincular el cuadro combinado que creaste anteriormente a la lista de medidas. Haz clic derecho en el cuadro combinado y selecciona "Formato de control". Para el "Rango de entrada", selecciona la lista de medidas; en este ejemplo es A16: A18. Para la opción "Vincular con la celda", selecciona una celda vacía cercana; en este ejemplo, la celda elegida fue G16.

Paso 7: Presiona "Aceptar”, el cuadro combinado ahora debería permitirte seleccionar las medidas en la lista de medidas, con la celda vinculada mostrando el número correspondiente a la posición de la medida en la lista. En la celda al lado de la celda vinculada ahora debes usar una función "Indice" para determinar el nombre de la lista creada al lado de los nombres de las medidas.

Nota: INDICE mira una matriz y calcula la posición de una celda en función de un número de fila y columna determinado. Como la celda vinculada da la posición de la medida en la lista de medidas (actuando así como el número de fila), también devolverá la hoja posterior correspondiente si la usamos en una función INDICE.

Paso 8: Ahora construye la función "Indirecto"; en este ejemplo, se hará dentro de un rango con nombre, pero también es posible hacerlo dentro de la fórmula de una celda. El uso de rangos con nombre simplemente limpia la fórmula final y facilita el análisis para otros usuarios.

Para crear un nuevo rango con nombre, ve a la pestaña "Fórmulas" de la cinta y selecciona la opción "Definir nombre". Asigna al rango nombrando un nombre apropiado como "SelectedData" e inserta la función "Indirecto".

En este ejemplo, la fórmula será:

=INDIRECTO ("'" &' Hoja de salida '! $ H $ 16 & "'! $ C $ 7: $ Z $ 16")

El propósito de la función "Indirecto" es permitir que Excel interprete el texto como una matriz o celda. Desglosar esta fórmula nos da lo siguiente:

= INDIRECTO ("TEXTO A INTERPRETAR")

= INDIRECTO ("‘ "& CELDA CON NOMBRE DE HOJA &" ‘! ARREGLO DE LA TABLA DE DATOS") esto es interpretado por Excel como:

= 'NOMBRE DE LA HOJA'! FORMACIÓN DE LA TABLA DE DATOS

Nota: La matriz de la tabla de datos no incluye los encabezados de las columnas ni los encabezados de las filas, es decir, las fechas o los nombres de las organizaciones; comienza con los datos reales.

Paso 9: Ahora debes construir la fórmula utilizada en la tabla de datos dinámicos. Usando una función "Indice", junto con el rango con nombre "SelectedData" y la función "Coincidir".

La fórmula en la parte superior izquierda de nuestra tabla de datos dinámicos es:

= INDICE (SelectedData, COINCIDIR ($ B4, $ B $ 4: $ B $ 13,0), COINCIDIR (C $ 3, $ C $ 3: $ Z $ 3,0))

Desglosar esto en cada parte nos da:

= INDICE (SelectedData, este es el enlace a nuestro rango con nombre, por lo que nuestro rango con nombre es la matriz que estamos "indexando").

COINCIDIR ($ B4, $ B $ 4: $ B $ 13,0), aquí es donde calculamos el número de fila a buscar. B4 es el nombre de nuestra primera organización, "Proveedor A" (su columna se "congela" mediante el uso de "$" antes de B), y la matriz B4:B13 es la lista completa o los nombres de las organizaciones. El 0 muestra que estás buscando una coincidencia exacta. A continuación, "Coincidir" devuelve el número de fila de "Proveedor A" en la lista de nombres; 1 en este caso.

Como se mencionó en el punto anterior, la matriz (la lista de fechas) en esta función puede ser la lista en la hoja de salida ya que es la misma que la lista en las hojas posteriores de datos.

Esta fórmula se puede copiar y pegar en toda la tabla de datos. Esto debería resultar en una tabla que pueda actualizarse dinámicamente.

Paso 10: Ahora que tienes una tabla de datos que se actualiza dinámicamente, puedes agregar el formato condicional de actualización dinámica. Junto a las listas de medidas y hojas, puedes incluir un límite en el que el verde se vuelve rojo y viceversa.

En el ejemplo los establecimos en "Tiempo promedio empleado", "Rendimiento total" y "Número de personal", respectivamente. Junto a estas listas, hemos incluido una celda que contiene el límite de la medida seleccionada. Esto se hace de la misma manera que la celda del nombre de la hoja, usando la función "Indice". Para este ejemplo, la fórmula es:

= ÍNDICE ($ C $ 16: $ C $ 18, $ J $ 1)

Esto mira la matriz de límites (C16: C18) y devuelve el correspondiente a la medida elegida.

Paso 11: Ahora puedes resaltar la tabla de datos, ve a la pestaña "Inicio" de la cinta y selecciona "Formato condicional", luego "Resaltar reglas de celdas" y "Mayor que ...".

Paso 12: Selecciona la celda que contiene el límite de la medida seleccionada; D16 en este caso. Elige la opción "Relleno rojo claro con texto rojo oscuro" y presiona “Aceptar”. Haz lo mismo para "Menos de ...", seleccionando la misma celda y esta vez seleccionando "Relleno verde claro con texto verde oscuro". ¡Ordenado!

Paso 13: Ahora, al cambiar las medidas, es posible que te hayas dado cuenta de que no tienen sentido para la medida de "Rendimiento total" y que la medida "Número de personal" muestra incorrectamente el formato condicional.

Para combatir esto, necesitaras cambiar las reglas a una declaración “SI” en lugar de una simple regla mayor o menor que. Para cambiar las reglas, dirígete a la sección "Administrar reglas" del menú "Formato condicional". Para el formato rojo, selecciona "Editar regla" y cambia la regla a "Usar una fórmula para determinar qué celdas formatear".

Esto verifica la hoja posterior de datos seleccionada, y si es la hoja posterior para los datos de rendimiento total: resalta las celdas que son menores que D16 (La celda que contiene el límite de la medida seleccionada) en rojo; y si no lo es, resalte las celdas que tengan más de D16 en rojo. Esto significa que la regla ahora muestra correctamente las organizaciones con bajo rendimiento en rojo, en lugar de verde.

Esto verifica la hoja posterior de datos seleccionada, y si es la hoja posterior para los datos de rendimiento total: resalta las celdas que son mayores o iguales a D16 (La celda que contiene el límite de la medida seleccionada) en verde; y si no lo es, resalte las celdas que sean menores o iguales a D16 en rojo.

Esto significa que la regla ahora muestra correctamente aquellas organizaciones con alto rendimiento en rojo, en lugar de verde. Al usar la desigualdad "mayor / menor o igual a", ahora también has incluido el número del límite dentro del formato, que es otro error que debe corregirse.

Paso 14: El último error que debe corregirse es que "Número de personal" seguirá mostrando el formato condicional a pesar de no tener un límite. Para corregir esto, agrega una nueva regla con "Usar una fórmula para determinar qué celdas formatear", y agrega la fórmula que excluirá esas medidas sin límite; en este ejemplo, hemos usado “-“para indicar que no hay límite en la lista de fronteras. La fórmula utilizada es entonces la siguiente:

= $ D $ 16 = "-"

Paso 15: Deja el formato como "Sin formato configurado" y presiona “Aceptar”. Luego, marca la casilla de verificación "Detener si es cierto" en la ventana de opciones "Administrar reglas" para esta regla.

La hoja ahora está completa desde el punto de vista de la funcionalidad.

Quizás también te interese: Cómo Hacer Un Formulario En Excel

Tips extras

Hemos realizado algunos cambios de formato generales para intentar que la tabla sea más clara y fácil de interpretar.

 Aumenta el tamaño de la fila de las filas de la tabla a 20.

Elimina las líneas de cuadrícula en la hoja yendo a "Ver" en la cinta y desmarcando la casilla "Líneas de cuadrícula".

También agrega un título: "Seleccionar medidas", encima del cuadro combinado para ayudar a mejorar la experiencia del usuario.

Utiliza la configuración "Congelar paneles" en la pestaña "Ver" en la celda C2 para bloquear los nombres de las organizaciones y el cuadro combinado en la vista de la hoja de cálculo.

Una cosa a tener en cuenta con este método es una debilidad de la función INDIRECTO. INDIRECTO se conoce como una función volátil, esto significa que cada vez que se realiza algún cambio en el libro de trabajo, se volverá a calcular cualquier fórmula con una función INDIRECTO. Otras funciones que comparten esta propiedad son las funciones como AL AZAR y AL AZAR CON.

Esto significa que las tablas de datos increíblemente grandes que usan INDIRECTO pueden causar una ralentización masiva al realizar cambios en el libro de trabajo. Una forma de evitar la función INDIRECTO sería formatear las hojas posteriores de datos de una manera alternativa, con una hoja posterior de datos maestros en lugar de varias.

Entonces, después de seguir este método, deberías poder crear una tabla de datos dinámica perfecta para los paneles de indicadores clave de rendimiento. Este método ofrece más flexibilidad que las tablas dinámicas, y el uso de cuadros combinados nos permite vincular varias partes de una hoja de cálculo a una sola celda dinámica para crear una experiencia de usuario más cohesiva.