datos: excel, consultas en access y tablas dinamicas

Como dije en mas de una oportunidad, Excel no es conveniente para el almacenamiento de grandes cantidades de datos. Quizás hoy la versión 2007 nos ofrezca mas de un millón de filas, pero luego, para trabajar con tantos registros, deberemos contar con una supercomputadora. Mi solución: esa perfecta armonía que integran Excel y Access. Tengo la tabla "Ventas" en Access, con cientos de miles de registros (mentira, tengo unos pocos para este ejemplo... pero valga lo mismo); dicha tabla está conformada por los siguientes campos:

fecha, vendedor e importe

[+/-] Ver el resto / Ocultar

Si nos vamos a las consultas de Access, nos resultará muy sencillo diseñar una e ir mostrando, por ejemplo, los datos segun fechas, vendedores, importes, etc, etc:

incorporando una fecha en la línea criterios, me será factible visualizar todas las ventas realizadas en ese período, como se muestra a continuación:

Digamos que hasta aquí la "llevamos fácil". Mas allá que poca gente utiliza Access (y menos gente lo hace con el verdadero poder que esta aplicación tiene), ¿que pasa si ahora solicito que esa misma consulta me arroje totales por fecha y vendedor?. Desde ya que las cosas se complicarían mucho y deberíamos contar con buenos conocimientos para armar un trabajo digno. Entonces es aquí donde surge la idea de combinar las cosas con Excel: Access para almacenar y Excel para analizar y graficar.

Recordemos que desde Excel podemos importar una tabla o consulta de Access; en este caso trabajaremos con la segunda. Primeramente armaremos la consulta en Access de acuerdo a nuestras necesidades y la guardamos. Cerramos todo y abrimos Excel.

Vamos al menú "Datos / Obtener datos externos / Nueva consulta de base de datos"

Y seguimos los pasos que indican las siguientes imágenes:

 elegimos MS Access

buscamos en nuestro disco duro el archivo .mdb que utilizaremos

hacemos click sobre el item "consulta_ventas" e incorporamos todos sus campos en la ventana de la derecha,

presionando sobre el boton  >

en este punto Excel nos permite incorporar criterios de filtrado. Pero dejamos todo como está. (practiquen luego con estas opciones)

a modo de ejemplo les muestro como también se puden traer los datos ordenados:

le pedimos que retorne los datos a Excel

ahora cuidado, por que haremos click sobre "Crear un informe de tabla dinámica"

solicitamos que incorpore la tabla en una hoja nueva y luego presionamos el botón "Diseño...", para acomodar los campos de la Tabla Dinámica:

"Diseño..." nos traerá a la pantalla el siguiente fomulario: noten que existe una tabla y a la derecha tres "botones", que se corresponden con los campos presentes en nuestra consulta de Access: fecha, vendedor y monto:

ahora, arrastrando con el mouse, acomodamos los campos de la siguiente forma: en las filas colocaremos "fecha" y "vendedor" y en el cuadro Datos el campo "monto":

podemos hacer doble click sobre "suma de monto" para personalizar el nombre que tendrá en la tabla dinámica y con el botón "número" accederemos a darle un formato en particular (moneda, general, etc)

Notarán también que figura el cuadro "Resumir por:" en este caso utilizaremos suma, pero observen la gran cantidad de opciones disponibles: cuenta, promedio, maximo, minimo, etc, etc. Presionamos Aceptar.

y aquí tenemos en Excel nuestra consulta de Access, llevada directamente a una Tabla Dinámica. Podrán ver como es factible visualizar totales por vendedor y fecha:

Esta consulta en Access nos habría dado nuestros buenos dolores de cabeza; hasta aquí aprendimos como volcar los datos y con ellos generar una Tabla Dinámica. Ahora bien, esta última es todo un tema aparte, ya que es en verdad una herramienta de resumen y analisis muy poderosa.

Por ejemplo, desde la barra de herramientas respectiva, podemos darle un autoformato en cuestión de segundos:

quedandonos el siguiente resultado, que es producto de uno de los tantos formatos predefinidos existentes:

Vemos como sobre cada campo existe un filtro. Presionando sobre cualquiera de ellos, activaremos/desactivaremos la visualización de los mismos, obteniendo vistas "parciales": aqui oculto al vendedor "damian":

Obteniendo este resultado, en el cual me muestra todas las ventas, exceptuando las del vendedor destildado:

Como procedí con el campo "vendedor", podemos hacerlo (simultáneamente si fuese necesario) con el resto de los campos, mostrando u ocultando informacion de la consulta original, que sigue con su fuente de origen en Access.
Con un par de clicks: primero boton derecho del mouse sobre cualquier celda de la tabla dinámica, del menú emergente seleccionamos "asistente...":

y reacomodamos los campos:

para obtener ahora el total por vendedor y fecha (anteriormente era por fecha y vendedor), lo que nos arrojará un nuevo informe:

Dicen que a las entradas de los blogs no hay que hacerlas demasiado extensas... pero con esta voy muerto. El tema es que aquí hay mucho para tratar, pero por lo pronto vimos como traer la consulta desde Access a Excel, generar una Tabla Dinámica y configurar algunos de sus parámetros. No tengan miedo a "tocar" un poco estos diseños, ya que no deja de ser una buena forma de aprender.

Una mas: en la barra de herramientas de la tabla dinámica hacemos click sobre el botón que tiene el ícono de un gráfico, para generar...... si, adivinaron, un gráfico dinámico a partir de la Tabla, la cual tiene su fuente de datos en Access... jeje, todo un mundo de análisis en pocos segundos:

Y como es lógico, aparecen los excelentes resultados:

simplemente arrastrando con el mouse los campos dentro del gráfico, cambiándolos de posición, obtenemos resultados realmente brillantes, como el que se muestra a continuacion:

A medida que se actualicen los datos en Access, estos se verán reflejados en Excel, una extraordinaria ventaja. Observen que en la barra de herramientas hay un signo " ! ", con el cual actualizamos manualmente los datos. Mas no se podría pedir.

Quedan abiertas las consultas al respecto. No olviden practicar un poco sobre los ejemplos, aqui les dejo los archivos por si quieren algo pre-armado: Access_Excel_TablasDinamicas 

.