Esta función nos permite extraer datos de una tabla dinámica. La importancia de esta herramienta reside en el hecho que las tablas dinámicas son el método más eficiente de Excel para totalizar y analizar datos de una base de datos.
Vamos a mostrar un ejemplo basándonos en los datos del ejemplo de las notas anteriores.
El cuaderno con el ejemplo se puede descargar aquí.
Después de totalizar los datos en esta tabla dinámica
donde hemos agrupado los datos por zonas, años y trimestres.
El tablero de comandos que construiremos nos permitirá extraer datos dinámicamente para realizar comparaciones por años, trimestres y zonas
En este video pueden apreciar el funcionamiento del dashboard
El cuaderno contiene cuatro hojas
Reporte: contiene el tablero de comandos
td: donde reside la tabla dinámica que totaliza nuestros datos; esta hoja es el “motor” del modelo
BD: la base de datos
definiciones: esta hoja contiene los valores de dos rangos nominados (nombres) que usamos en las listas desplegables del dashboard.
Nuestro tablero de comandos se divide en dos zonas: la zona de los parámetros
donde establecemos los parámetros de los datos a comparar, y la zona de los resultados
Las columnas A y B están ocultas. Estas columnas nos sirven para manejar rangos auxiliares, cuyo objetivo mostraremos más adelante.
El rango F4:H6 es donde ponemos los parámetros del análisis, eligiéndolos de listas desplegables cuyas definiciones pueden verse en la hoja “definiciones”.
Antes de poner la fórmula en la celda F10 nos aseguramos que el generador de la función IMPORTARDATOS DINAMICOS esté activo.
En Excel 2007 seleccionamos una celda de la tabla dinámica y en la cinta de Opciones de las herramientas de tablas dinámicas abrimos la pestaña Tabla Dinámica-Opciones
En Excel Clásico (97-2003) encontramos esta opción en la barra de herramientas de las tablas dinámicas
Una vez que esta opción está activa creamos una referencia a la celda correspondiente en la tabla dinámica
Excel crea automáticamente la función con los argumentos relevantes
=IMPORTARDATOSDINAMICOS("Clientes ";td!$A$3;"Fecha";1;"Zona";"Este";"Años";2007)
Analicemos esta fórmula:
el “1” que aparece después de “Fecha” es la referencia al Trimestre 1 (el primer elemento del campo Fecha). Este elemento debe ser dinámico, es decir, cambiar de acuerdo a la elección del usuario.
“Este” define que zona hemos elegido. También este elemento debe cambiar dinámicamente.
“2007” es el otro elemento que deberá cambiar de acuerdo a la elección del usuario.
Estos elementos serán reemplazados de esta manera:
=IMPORTARDATOSDINAMICOS("Ventas ";td!$A$3;"Fecha";$A$5;"Zona";F6;"Años";F4)
$A$5 reemplaza el índice del elemento del campo fecha.
La celda A5 contiene la fórmula
=COINCIDIR(F5;Trimestre;0)
cuyo valor sirve como argumento en las funciones IMPORTARDATOS DINAMICOS con las que extraemos los datos
Lo mismo con B5 que contiene la fórmula =COINCIDIR(H5;Trimestre;0)
Los argumentos “Año” y “Zona” podemos pasarlos directamente de las celdas F4 y F6 donde el usuario hace su elección.
Hacemos lo mismo con el resto de las celdas en las filas 10 y 11. Con esto nuestro modelo es absolutamente dinámico.
Las celdas F12, H12 y J12 contienen simples fórmulas para calculas el cambio porcentualmente.
Finalmente agregamos los gráficos con técnicas como las que he mostrado en la nota anterior.
El cuaderno se puede descargar aquí.
Technorati Tags: MS Excel
Estimados Amigos,
ResponderBorrarEs realmente agradable encontrar espacios como este, en los que podemos compartir el conocimiento... Jorge, te felicito, y espero que en este lugar puedan ayudarme con las dudas que tenga en Excel. Así mismo aportaré con mi granito de arena a esta noble iniciativa, siempre y cuando eso esté permitido.
Gracias Amigos.
Hola estoy interesado por el cuadro de mando, existe algún otro medio de pago, que no sean con tarjetas de crédito.?
ResponderBorrarHola, no. El único método es con PayPal.
ResponderBorrar