Objetivos:
Dominar funciones avanzadas, macros y automatizaciones. Dotar al participante de las competencias necesarias para gestionar y preparar datos en Excel mediante Power Query, construir modelos de datos relacionales con Power Pivot y elaborar informes de análisis con tablas dinámicas, orientados a la toma de decisiones en el ámbito de la dirección financiera y el control de gestión.
- Dominar funciones avanzadas, macros y automatización.
- Extraer y preparar datos desde múltiples orígenes utilizando Power Query, asegurando su limpieza y estructuración correcta.
- Transformar y consolidar información resolviendo problemas habituales de archivos no analizables y unificando datos de varios formatos.
- Automatizar procesos de carga y actualización de datos para optimizar el tiempo y reducir errores manuales.
- Diseñar un modelo de datos relacional en Excel mediante Power Pivot, incorporando tablas de fechas y relaciones entre tablas.
- Crear indicadores y métricas clave mediante medidas DAX para el análisis de importes, ratios y comparaciones temporales.
- Analizar y presentar información de forma clara e interactiva utilizando tablas dinámicas, segmentaciones y opciones de diseño adecuadas.
Dirigido a:
- Directores financieros, controllers, responsables de administración y perfiles de gestión que trabajen con datos en Excel.
- Profesionales de finanzas, contabilidad o controlling que quieran ir más allá del uso tradicional de Excel.
- Usuarios con un nivel intermedio de Excel (tablas, fórmulas básicas, filtros), sin necesidad de conocimientos previos de Power Query o Power Pivot.
Programa:
Bloque A · Funciones Avanzadas de Búsqueda y Referencia. Evolución de las funciones de búsqueda en Excel: desde las herramientas clásicas hasta las funciones modernas de Microsoft 365, eligiendo en cada caso la herramienta más adecuada según la estructura de los datos y la complejidad de la consulta. SI, BUSCARV, CAMBIAR, BUSCARX frente a BUSCARV y BUSCARH, ÍNDICE y COINCIDIR
Bloque B · Funciones Estadísticas y Condicionales. Dominio de las funciones de cálculo condicional con criterios simples y múltiples, imprescindibles en el análisis de datos financieros, comerciales y de gestión, junto con el uso de SUBTOTALES para la presentación flexible de información.
B.1 – Funciones condicionales con criterios múltiples. SI y SUMAR.SI.CONJUNTO: acumulados por categoría, periodo, producto o combinación de criterios. SI y CONTAR.SI.CONJUNTO: recuento de registros bajo condiciones complejas. SI: cálculo de medias condicionadas aplicado al análisis de márgenes, precios y ratios. Casos prácticos orientados al análisis de ventas, control de costes y reporting financiero.
B.2 – SUBTOTALES, vistas y numeración automática. Función SUBTOTALES: modos de cálculo y comportamiento con filas ocultas y filtros activos. Diferencia entre SUBTOTALES y funciones convencionales al aplicar filtros. Vistas personalizadas: guardar y alternar entre configuraciones de visualización para distintos perfiles de usuario. Numeración automática en bases de datos: técnica con SUBTOTALES para mantener correlatividad al filtrar.
Bloque C · Introducción a las Macros y Automatización. La automatización mediante macros permite eliminar tareas repetitivas garantizando la consistencia de los resultados. Se trabaja con el grabador de macros y la edición básica del código VBA generado, sin requerir conocimientos previos de programación.
C.1 – Conceptos previos y entorno de trabajo. Qué es una macro y para qué sirve en un entorno de gestión financiera. Activación de la ficha Desarrollador. El Editor de Visual Basic: módulos y estructura básica. Tipos de macros y formatos de archivo compatibles (.xlsm). Seguridad y habilitación de macros.
C.2 – Grabación y edición de macros. El grabador de macros como puerta de entrada al código VBA. Referencias absolutas frente a referencias relativas: cuándo usar cada modo. Lectura e interpretación del código generado. Edición básica: simplificar acciones redundantes. Asignación de macros a botones y accesos directos de teclado.
C.3 – Práctica 1: Modificar configuraciones de Excel con macro. Grabación de una macro que configura automáticamente el entorno de trabajo al abrir el archivo: opciones de visualización, formatos de cabecera, zoom e inmovilización de paneles.
C.4 – Práctica 2: Automatizar la introducción de datos. Macro para registrar los datos de un formulario de entrada en la siguiente fila disponible de una base de datos, con limpieza automática del formulario y confirmación al usuario.
C.5 – Práctica 3: Automatizar el copiado entre hojas. Macro para transferir datos desde una hoja de origen a una hoja de informe, pegando solo valores y localizando dinámicamente la primera fila vacía disponible.
Bloque D · Power Query – Extracción y Preparación de Datos. Conexión y extracción de datos desde diferentes orígenes (libros de Excel, ficheros de texto, bases de datos, web, etc.). Uso del Editor de Consultas como entorno de trabajo para explorar y transformar datos. Limpieza de datos: eliminación de filas innecesarias, duplicados y registros erróneos. Transformación de datos: cambio de tipos, división y combinación de columnas, estandarización de formatos. Resolución de problemas habituales en archivos «no analizables» (celdas combinadas, cabeceras múltiples, filas de totales, etc.). Consolidación de información procedente de varios archivos y/o formatos en una única tabla estructurada. Procesamiento automático de todos los archivos de una carpeta. Automatización de consultas para que la actualización se realice con un solo clic.
Bloque E · Power Pivot – Modelo de Datos e Indicadores. Activación y uso del complemento Power Pivot en Excel. Carga de tablas preparadas en Power Query al modelo de datos. Diseño de un modelo relacional sencillo mediante relaciones entre tablas. Uso de una tabla de fechas para facilitar el análisis temporal (años, trimestres, meses, días). Diferencias entre columnas calculadas y medidas en Power Pivot. Creación de medidas básicas con DAX para obtener importes, recuentos, ratios y porcentajes. Definición de indicadores de comparación temporal (variaciones respecto a periodos anteriores). Preparación del modelo para su explotación con tablas dinámicas e informes.
Bloque F · Tablas Dinámicas – Análisis y Presentación. Creación de tablas dinámicas basadas en el modelo de datos de Excel. Estructura básica y configuración de campos de filas, columnas, filtros y valores. Agrupación de información (especialmente fechas) para análisis por periodos. Uso de segmentación de datos (slicers) y filtros para análisis interactivos. Opciones básicas de diseño y presentación para elaborar informes claros y comprensibles.
