Automatizacion de Reportes con Excel y Access

INTEGRACIÓN DE EXCEL Y ACCESS PARA LA AUTOMATIZACIÓN DE REPORTESPablo Magallanes Fajardo

pablo.magallanes@hotmail.com

CONTENIDO Presentación Introducción a Access Creando tablas en Access Construcción de consultas en Access Elaboración de funciones en Access Construcción de consultas avanzadas en Access

Funciones en consultas, pivotar consultas, unión y exclusión de datos

Conexión de Access con Excel Distintos Formatos de presentación en Excel

Tablas, Tablas Dinámicas, Gráficos, Gráficos Dinámicos

Automatización de recargas de información con macros en Excel

PRESENTACIÓN¿Por qué es necesaria la integración?

ASUMO QUE USTEDES…

O Realizan reportes o informes repetitivos que requieren consolidar, integrar, agregar y calcular la misma data constantemente.

O Son “Analistas” que dedican la mayor parte del tiempo preparan informes o reportes y esto los limita para realizar otras actividades (como analizar dichos informes)

O Son estudiantes que tienen curiosidad por entender como utilizar Excel con Access.

O todas las anteriores.

TALLER:

Utilizaremos un caso de Estudio

Revisaremos la parte teórica y practica inicial (1h15)

Tendremos un receso (15 min) Practicaremos ejercicios de

manera individual (1h00) Compartiremos lo aprendido

con el resto(30m)

CASO: NEGOCIO DE VENTA DE CELULARES

Empresa con 3 años en el mercado Mas de 30 vendedores 10 Almacenes, principalmente en Quito y

Guayaquil Sistemas provee de una base diaria de las ventas,

además nos ha entregado los maestros de datos (Articulo, Vendedores y Clientes)

La empresa requiere tener información de lo siguiente: Ventas y Margen acumulado al fecha, comparativo vs

el año pasado Cumplimiento de presupuesto por Vendedor y Almacén Top Vendedores, Artículos y Almacenes por Ventas y

Rentabilidad

ACCESS VS EXCEL

Necesite una base de datos relacional (varias tablas) para almacenar los datos.

Conserve una gran cantidad de datos (miles de entradas)

Desee ejecutar consultas complejas

Necesite que muchos usuarios trabajen en la base de datos y desee opciones sólidas que expongan los datos que se vayan a actualizar.

Necesite una vista simple o no relacional de los datos

Desee ejecutar principalmente cálculos y comparaciones estadísticas de los datos, por ejemplo si desea mostrar un análisis de costos y beneficios en el presupuesto de la compañía.

Sepa que el conjunto de datos tiene un tamaño que permite administrarlo

Utilice cuando… Utilice cuando…

CONSIDERE

Con Access el manejo de la información en base de datos permite simplificar el trabajo con el paso del tiempo, al inicio puede ser tedioso. Si un reporte debe ser generado continuamente Access facilitará este trabajo.

Excel facilita la creación de contenido que puede ser fácilmente distribuido y agregado a distintos reportes o informes.

Utilizar Access para procesar los datos y Excel para la presentación provee lo mejor de ambos mundos,.

INTEGRACIÓN DE ACCESS Y EXCEL

Proceso con Access Unir, agregar datos a la base Consolidar información Calcular resultados

Presentación con Excel Tablas y gráficos dinámicos con resúmenes Reportes e informes con formato Automatización de la recarga de información

INTRODUCCIÓN A ACCESSConceptos Básicos

CONCEPTO BÁSICOS

Crear una base de datos desde cero Pegar datos de otro origen en una tabla de

Access Importar datos desde otro origen

Asistente de importación

CREAR UNA BASE DESDE CERO Inicie Access. En la página Introducción a Microsoft Office

Access, haga clic en Base de datos en blanco. En el panel Base de datos en blanco, escriba un

nombre de archivo en el cuadro Nombre de archivo.

Para cambiar la ubicación del archivo, haga clic en Examinar, situado junto al cuadro Nombre de archivo, busque la nueva ubicación y, a continuación, haga clic en Aceptar.

Haga clic en Crear. Access crea la base de datos y, a continuación, abre

una tabla vacía (denominada Tabla1) en la vista Hoja de datos.

PEGAR DATOS DE OTRO ORIGEN

Abra el Excel con la información del taller Copie el contenido de la hoja Vendedores En Access, en la vista de hoja de datos,

pegue el contenido copiado. Access asignará automáticamente el formato

según el contenido que se este copiando, adicionalmente colocará como nombre de campos al primer registro que encuentre (es decir los encabezados de la columnas que seleccionamos)

Guarde la tabla con el nombre de Vendedores.

IMPORTAR DATOS DESDE OTRO ORIGEN En la ficha Datos Externos, en el grupo Importar,

haga clic en el comando correspondiente al tipo de archivo que va a importar, en este caso escogemos Excel

En el cuadro de diálogo Obtener datos externos, haga clic en Examinar para ir al archivo de datos de origen, o bien, escriba la ruta de acceso completa al archivo de datos de origen en el cuadro Nombre de archivo.

Haga clic en la opción deseada bajo Especifique cómo y dónde desea almacenar los datos en la base de datos actual. Puede crear una nueva tabla usando los datos importados, anexando los datos a una tabla existente o creando una tabla vinculada que mantenga un vínculo al origen de datos.

Haga clic en Aceptar. Access inicia el Asistente para importación.

IMPORTAR DATOS DESDE OTRO ORIGEN (1/7)

IMPORTAR DATOS DESDE OTRO ORIGEN (2/7)

IMPORTAR DATOS DESDE OTRO ORIGEN (3/7)

IMPORTAR DATOS DESDE OTRO ORIGEN (4/7)

IMPORTAR DATOS DESDE OTRO ORIGEN (5/7)

IMPORTAR DATOS DESDE OTRO ORIGEN (6/7)

IMPORTAR DATOS DESDE OTRO ORIGEN (7/7)

CREANDO TABLASEdición del contenido

CREAR UNA TABLA EN MODO DISEÑO

CREAR UNA TABLA EN MODO DISEÑO

EDITAR TABLA

RELACIONAR CONTENIDOS

¿Por qué crear relaciones de tabla? Para trabajar con registros de más de una tabla,

a menudo se debe crear una consulta que combine las tablas. La consulta compara los valores del campo de clave principal de la primera tabla con el campo de clave externa de la segunda tabla. Por ejemplo, para devolver filas que enumeren todas los ventas de cada cliente, deberá crear una consulta que combine la tabla Clientes con la tabla Ventas

RELACIONAR CONTENIDO En el grupo Mostrar u ocultar de la ficha Herramientas de base de

datos, haga clic en Relaciones. Si aún no ha definido ninguna relación, aparecerá automáticamente el

cuadro de diálogo Mostrar tabla. Si no aparece, en la ficha Diseño, en el grupo Relaciones, haga clic en Mostrar tabla.

En el cuadro de diálogo Mostrar tabla se muestran todas las tablas y consultas de la base de datos. Para ver únicamente las tablas, haga clic en Tablas. Para ver únicamente las consultas, haga clic en Consultas. Para ver las tablas y las consultas, haga clic en Ambas.

Seleccione una o varias tablas o consultas y, a continuación, haga clic en Agregar. Cuando termine de agregar tablas y consultas a la ventana Relaciones, haga clic en Cerrar.

Arrastre un campo (normalmente el campo de clave principal) de una tabla al campo común (la clave externa) en la otra tabla. Para arrastrar varios campos, presione la tecla CTRL, haga clic en cada uno de los campos y, a continuación, arrástrelos.

Aparecerá el cuadro de diálogo Modificar relaciones. Compruebe que los nombres de campo mostrados son los campos comunes

de la relación. Si un nombre de campo es incorrecto, haga clic en él y seleccione un nuevo campo de la lista.

Haga clic en Crear.

RELACIONAR CONTENIDO

RELACIONAR CONTENIDO

CREANDO CONSULTASConsolidar el Contenido

CONSULTAS

Consulta para sumar las ventas de cada mes y año

Consulta para sumar las ventas por vendedor en cada mes y año

Consulta para sumar las ventas por almacén en cada mes y año

Consulta para sumar las ventas por marca en cada mes y año

Consulta para sumar las ventas por forma de pago en cada mes y año

CONSULTA DE SELECCIÓN

CONSULTA DE AGRUPACIÓN

CONSULTA DE AGRUPACIÓN

CONSULTA DE AGRUPACIÓN

CONSULTA DE AGRUPACIÓN

CONSULTA DE AGRUPACIÓN

CONSULTA DE AGRUPACIÓN

CONSULTA DE AGRUPACIÓN

CONSULTA DE AGRUPACIÓN

CREANDO FUNCIONESExpresiones Complejas

CREACIÓN DE UN MODULO

CREACIÓN DE UN MODULO

FUNCIONES PUBLICAS

CONSULTAS AVANZADASExpresiones Complejas

AGREGAR UNA FUNCIÓN A UNA CONSULTA

AGREGAR UNA FUNCIÓN A UNA CONSULTA

PIVOTAR UNA CONSULTA

PIVOTAR UNA CONSULTA

PIVOTAR UNA CONSULTA

PIVOTAR UNA CONSULTA

PIVOTAR UNA CONSULTA

PIVOTAR UNA CONSULTA

PIVOTAR UNA CONSULTA

CONSULTAS CON PARÁMETROS

VENTA ÚLTIMOS 60 DÍAS

CONEXIÓN CON EXCELExpresiones Complejas

AÑADIR LA CONEXIÓN DE DATOS

AÑADIR CONEXIÓN DE DATOS

AÑADIR CONEXIÓN DE DATOS

AÑADIR CONEXIÓN DE DATOS

AÑADIR CONEXIÓN DE DATOS Y AGREGAR

RESUMIR INFORMACIÓN

OTROS RECURSOS

http://office.microsoft.com/es-hn/support/formacion-FX010056500.aspx

http://chandoo.org/ http://www.visual-literacy.org/periodic_table/p

eriodic_table.html http://databases.about.com/od/tutorials/ss/ex

celtoaccess.htm