automatizacion de reportes con excel y access
TRANSCRIPT
INTEGRACIÓN DE EXCEL Y ACCESS PARA LA AUTOMATIZACIÓN DE REPORTESPablo Magallanes Fajardo
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