Excel Consolidar Datos con Tabla Dinamica

Publicado: 3 de agosto de 2012 en Actualidad

Me consultan bastante sobre como realizar procedimientos en VBA (visual basic) para recabar información dispersa en distintas partes de un libro (por ejemplo, varios rangos u hojas), lo cual es una tarea que desvela a mas de uno "dedicado a los números". Esta labor se complica aún mas si los datos involucrados se encuentran, a su vez, distribuidos en varios libros de Excel. Si bien las macros vienen a solucionarnos innumerables problemas y amplían nuestra capacidad de trabajo a límites casi increíbles, también pueden complicarnos bastante, si no evaluamos previamente que resultado le exigiremos a nuestro proyecto.
Vamos al ejemplo mas sencillo: debemos agrupar información de distintas áreas una empresa, en cuanto a los gastos en los que incurrieron mes a mes. Tenemos a: sistemas, marketing y ventas (para simplificar), con el siguiente detalle:
consolidarcontd01-.JPG

[+/-] Ver el resto / Ocultar
La imagen de arriba nos muestra los gastos en los que incurrió el área de marketing el primer semestre del año. Ahora imaginemos que tenemos dos tablas mas, cuyos registros obedecen a sistemas y ventas.
Agrupar y totalizar (en definitiva, consolidar) esa información puede ser engorrosa mediante macros, inclusive haciendo uso de funciones, teniendo en contra (además) la ralentización que le supone a Excel el uso intensivo de fórmulas.

Agravemos un poco la situación. Ya dijimos que las fuentes de datos (tablas en este caso) están en distintas hojas y, probablemente, en distintos libros. ¿Como agruparé esa info? ¿Por área, por mes, por tipo de gasto? Cada opción que elijamos, y hasta combinemos, supondrá el diseño de mas y mas código VBA para solucionar nuestro problema de resumir todos esos datos dispersos. Y ni hablar si optamos por funciones.

Pero Excel posee una herramienta que pareciera especialmente diseñada para solucionar esto, con el menor esfuerzo posible: las Tablas Dinámicas. Aunque por lo general le damos otros usos, el de la consolidación de datos es, realmente, espectacular. A su vez y con muy poco trabajo, las podemos potenciar con macros, pero de eso hablaré en mi próxima entrada.

Primero un pequeño detalle: estoy (intentando) dejar atrás las versiones mas antiguas de Excel, reemplazando todo mi trabajo por lo mas nuevo: la versión 2010, que es insuperable desde todo punto de vista. Aquí "faltan o sobran" muchas cosas con respecto al "Excel viejo", por lo que deberemos adaptarnos a nuestra nueva interfaz. De entrada me costó encontrar el famoso Asistente para Tablas Dinámicas, así que les muestro como colocarlo encima de las "Ribbons", nueva terminología de nuestra querida y antigua Barra de Menús, completamente eliminada en Excel 2010:
consolidarcontd01-02.JPG
desplegamos la pequeña flecha sobre el borde superior de la ventana y luego damos click en "Mas comandos"

consolidarcontd01-03.JPG
en el formulario emergente, sobre la izquierda, clickeamos en "barra de herramientas de acceso rápido". Sobre la primer ventana seleccionamos "todos los comandos" y buscamos "Asistente para tablas y gráficos dinámicos"

consolidarcontd01-04.JPG
luego de seleccionarlo, presionamos "agregar" y pasará a la ventana derecha. Aceptamos y el Asistente nos quedará fijo, listo para ser utilizado.

Volvamos al trabajo. Para simplificar la tarea, distribuí una tabla por cada área en distintas hojas del mismo libro, nombrándolas:
consolidarcontd01-05.JPG
mas abajo explicaré por qué antepuse números a los nombres de página.

Si la información está distribuida en distintos libros, es conveniente abrirlos antes de utilizar el Asistente.

Bueno listo, basta de explicaciones complementarias y pasemos al ejemplo.
Mi lector necesita "juntar" toda esa información y generar un reporte que muestre cuanto erogó la empresa en los distintos rubros, por mes. Insertamos una nueva hoja y hacemos click sobre el Asistente de Tablas Dinámicas:
consolidarcontd01-06.JPG
indicamos que trabajaremos con varios rangos.

consolidarcontd01-07.JPG
dejamos "crear un solo campo de página". mas abajo hablaremos sobre esto.

consolidarcontd01-08.JPG
atención ahora: seleccionamos un rango y presionamos "agregar". luego vamos a la segunda hoja, seleccionamos el rango y volvemos a presionar "agregar". repetimos esta operación hasta concluir con todas las tablas (estén en este u otro libro)

consolidarcontd01-09.JPG
así debe quedarnos: noten que tengo dentro del cuadro principal las tres hojas, con sus respectivos rangos de datos. damos en "siguiente"

Si presionamos el botón "examinar" accederemos al formulario que nos permitirá buscar en otros libros. Pero este no es el caso, por lo menos en el presente ejemplo.

consolidarcontd01-10.JPG
dejamos todo tal cual y finalizamos.

Estudiemos un poco la tabla que Excel nos armó sobre la base de los sencillos pasos vistos previamente:

consolidarcontd01-11.JPG

1) En las columnas (campos) tenemos las fechas: de enero a diciembre.
2) En las filas (registros) figuran los distintos rubros que produjeron erogaciones.
3) Tanto los campos como las columnas poseen totales.

4) ¿Código VBA? Ninguno. Y todo salió perfecto.

Demos formato a la tabla, para una mejor presentación:
consolidarcontd01-12.JPG
cambiamos el formato de fecha, numérico y aplicamos uno de los tantos formatos de tabla predeterminados que trae la versión 2010.

Verán dos celdas: en una dice "fila" (rubros) y en otra "columna" (meses). También lo personalizaremos, para evitar confusiones y agilizar futuras tareas con la Tabla. Hacemos click derecho sobre cada campo (uno a la vez) y seleccionamos "configuración de campo". Allí cambiaremos su nombre:
consolidarcontd01-13.JPG
repetimos la acción con "columna"

Y ya vamos mejorando:
consolidarcontd01-14.JPG
pueden ver las tres celdas modificadas, coloreadas de naranja.

Concluidas algunas mejoras visuales, que nunca están de mas, vamos a esta poderosa herramienta. Estimo que ya están al tanto del manejo de una Tabla Dinámica y lo que es algo maravilloso: poder mover los campos de lugar, para lograr en milisegundos informes de primer nivel. Hacemos click sobre "Meses" y, sin soltarlo, lo "tiramos" dentro de los rubros, para obtener esto:
consolidarcontd01-15.JPG
ahora tenemos un pormenorizado detalle de los rubros, con sus gastos mes a mes y también totalizados.

Si invierto el orden de la "movida" anterior:

consolidarcontd01-16.JPG
el mismo informe, pero invertido: aquí vemos los totales mensuales, discriminados por rubros. haciendo click sobre cada " + " o " – " expanderemos o contraeremos el detalle.

Observen como hasta el momento generamos tres informes completísimos (a los cuales se les puede anexar un gráfico dinámico, sin esfuerzo) y que son fácilmente configurables y "cambiables".

Pero esto, obviamente, es recién el comienzo. Puedo mostrar información parcializada: noten que en cada campo hay un filtro. Haremos click, por ejemplo, sobre "Rubros", para activar las opciones de la consulta:
consolidarcontd01-17.JPG
le digo que solo muestre los gastos relativos a "impuestos, seguridad y útiles".

Y tal lo solicitado, Excel lo cumple al pié de la letra:

consolidarcontd01-18.JPG

El campo "Meses" nos depara una muy buena sorpresa. Imaginen la buena tarea que nos deparará, por ejemplo, agrupar los gastos por trimestres y meses, de utilizar macros o funciones. Aquí solo debemos dar click derecho sobre dicho campo y presionar "Agrupar":
consolidarcontd01-19.JPG
como el campo "Meses" posee fechas, Excel lo detecta solo y nos permite agrupar por todas las opciones que ven en la ventana de arriba.

Seleccionamos "trimestres" y "meses" (noten que hasta podemos hacerlo por años) y damos "aceptar", para obtener el siguiente informe:
consolidarcontd01-20.JPG
absolutamente detallado y perfecto, como solo Excel puede hacerlo: sin la mas mínima de las complicaciones agregamos un detalle trimestral a nuestro informe.

Probablemente no lo recuerden, pero estamos trabajando con datos de tres hojas distintas, las cuales podrían ser 20 o 40, distribuidas en distintos libros. Los invito a practicar con ello.

Vamos a la primer fila de la hoja, en la celda que denominamos "Area". Allí Excel se encargó de crear otro filtrado para nuestro informe: "Campo de página". Hacemos click sobre el autofiltro y vemos las opciones disponibles:
consolidarcontd01-21.JPG
por cada hoja (sistemas, marketing y ventas) nuestra TD creó un elemento. O sea que, de acuerdo a la selección que hagamos de dichos elementos, nuestro reporte podrá mostrar los gastos de una o dos áreas en particular. O bien de todas, como le veníamos haciendo.

La imagen de arriba es la causal de que a cada nombre de hoja le antepuse un número (01, 02, 03), para que luego queden bien ordenadas, ya que Excel (lamentablemente) nos muestra la etiqueta "Elemento" en vez del nombre de la hoja en sí. Al tener las áreas identificadas con un número específico, luego relaciono ese número con la posición de "Elemento" dentro de la matriz.

Estos "Campos de páginas" son espectaculares. Si hacemos click dentro de cualquier lugar de la tabla, sobre el margen derecho de la pantalla veremos el "panel de tareas" de la tabla dinámica, como muestro en esta imagen:
consolidarcontd01-22.JPG
aquí accedemos a las acciones mas comunes sobre la tabla.

Ahora, de la lista superior, arrastramos el campo "Meses" y lo soltamos sobre "Area", para obtener esto:
consolidarcontd01-23.JPG
si, un nuevo "campo de página". noten que "meses" desapareció de la tabla principal, pero ahora lo tenemos en otro lugar, listo para aplicar algún criterio de filtrado.

Como este:
consolidarcontd01-24.JPG
solo enero, febrero, marzo y abril. un lujo. a no olvidar que ya tenemos dos "campos de página", pudiendo filtrar por meses y por áreas

A estas alturas imaginarán que es factible estar todo el día armando consultas. Espero que el concepto les quede claro y analicen la incorporación de las Tablas Dinámicas en sus informes, a modo de "consolidación de datos". Esto es realmente brillante, logrando trabajos muy profesionales con poco esfuerzo.

Se puede aprender mucho con el solo hecho de "arrastar y soltar" campos "de un lado al otro". Los formatos y tipos de informes varían mucho y se logran resultados sorprendentes.
En mi próxima entrada (y con el mismo ejemplo) veremos como presentar este proyecto incorporando algunas macros, para limitar las acciones de los usuarios y generar solo ciertos tipos de reportes.

Para finalizar, lo mejor: en la misma hoja en donde tenemos nuestra actual tabla dinámica, llamamos de nuevo al asistente, para crear (a la derecha) otra tabla, y presentar dos resúmenes: por meses y por rubros, logrando de a poco un verdadero "tablero de control":

consolidarcontd01-25.JPG
para que consuma menos recursos de nuestro ordenador, le indicamos que la tabla a crear se nutrirá de una tabla dinámica existente.

consolidarcontd01-26.JPG
elegimos la tabla, ya que podrían existir varias dentro del libro

consolidarcontd01-27.JPG
como lugar de ubicación le damos a F3, para colocarla al lado de la tabla original.

Y configurando los campos, como ya vimos, brindamos dos informes con distintos criterios de agrupamiento, los cuales nos proveen de un nivel de acceso a la información únicos.
consolidarcontd01-28.JPG
jaja, "ambos dos", en pocos segundos y 100% personalizables.

¿Mas? Sí, por supuesto. Recién realizamos una tabla dinámica (la "azul") sobre la base de otra (la "roja"). De igual forma podríamos traer a Excel una consulta de Access y/o archivos de texto, también con Tablas Dinámicas… y luego fusionar todos esos registros (con fuentes en distintos formatos y ubicaciones) en otra TD, que resuma esa información.

Gente, esto es interminable, y la entrada se hizo demasiado larga. No duden en avisar ante cualquier consulta, no dejen de practicar con esta increíble herramienta de trabajo.
Les dejo el link al archivo.

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s