EXCEL INTERMEDIO PARA LA GESTIÓN EFICIENTE

MÓDULO I: MODELAMIENTO DE TABLAS OFICIALES Y FORMULACIÓN MULTICRITERIO

SESIÓN 1: de rangos estáticos a tablas inteligentes y auditoría de fórmulas

• El poder de las Tablas Oficiales de Excel (Ctrl + T): Limitaciones del rango tradicional vs. ventajas del objeto Tabla. Estructura de nombres de tablas, referencias estructuradas automáticas ([@Campo]), columnas calculadas y fila de totales dinámica.

• Administración de Nombres de Rango: Creación y gestión de nombres para celdas y matrices desde el Administrador de Nombres. Aplicación de nombres en fórmulas para evitar referencias crípticas y facilitar la lectura en equipo.

• Herramientas de Auditoría y Diagnóstico de Fórmulas: Rastrear celdas precedentes y dependientes, evaluación paso a paso de fórmulas complejas con "Evaluar fórmula" y manejo estructurado de errores (#N/D, #¡VALOR!, #¡DIV/0!, #¿NOMBRE?).

Ejercicios Prácticos de la Sesión :

• Ejercicio Práctico 1.1 – "Modernización de Planillas Maestras": Convertir un listado plano de ventas y logística de 800 filas en una Tabla Oficial estructurada con nombres semánticos, columnas autocalculadas y filas de totales dinámicas.

• Ejercicio Práctico 1.2 – "Auditoría Forense de un Presupuesto Operativo": Detectar y corregir fórmulas rotas y referencias circulares en un libro contable desalineado utilizando las herramientas de rastreo y evaluación de fórmulas.

SESIÓN 2: Lógica Condicional Multicriterio y Funciones Lógicas Anidadas

• Lógica Booleana en Excel: Operadores lógicos y combinación de la función SI con operadores Y() y O(). Evaluación de escenarios de cumplimiento simultáneo vs. alternativo.

• Función SI Anidada y Función SI.CONJUNTO: Estructuración de múltiples rangos de evaluación (ej. escalas de comisiones, tramos de cobranza o categorización de clientes por nivel de riesgo).

• Manejo Seguro de Excepciones: Integración preventiva de SI.ERROR y SI.ND sobre fórmulas lógicas compuestas para resguardar la estética y consistencia de los reportes.

Ejercicios Prácticos de la Sesión :

• Ejercicio Práctico 2.1 – "Matriz de Comisiones y Escalas de Rendimiento": Construir un modelo de liquidación comercial que evalúe cumplimiento de metas y antigüedad usando SI anidado y SI.CONJUNTO para determinar el porcentaje aplicable.

• Ejercicio Práctico 2.2 – "Semáforo Lógico de Priorización Operativa": Evaluar pedidos de bodega asignando prioridad "Alta", "Media" o "Baja" según saldo disponible, tipo de cliente y urgencia con SI(Y(...)) y SI(O(...)).

MÓDULO II: ESTADÍSTICA CONDICIONAL Y CRUCE ROBUSTO DE INFORMACIÓN

SESIÓN 3: Agregación Condicional Multicriterio y Análisis Estadístico Intermedio

• Agregación con Múltiples Filtros: Comparativa entre funciones de un solo criterio (SUMAR.SI, CONTAR.SI) y funciones multicriterio: SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO.

• Uso de Comodines y Criterios con Fechas: Aplicación de comodines (*, ?) para coincidencias parciales y construcción dinámica de criterios con comparadores lógicos y concatenación (">=" & Fecha).

• Estadísticas de Posición y Dispersión: Uso de K.ESIMO.MAYOR, K.ESIMO.MENOR, MEDIANA y MODA.UNO para análisis de distribución y detección de valores atípicos en operaciones comerciales o de RRHH.

Ejercicios Prácticos de la Sesión :

• Ejercicio Práctico 3.1 – "Panel de Control de Costos por Centro de Costo y Periodo": Totalizar gastos y contar órdenes de compra simultáneamente por sucursal, mes y estado mediante SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO.

• Ejercicio Práctico 3.2 – "Ranking y Detección de Valores Extremos": Analizar los tiempos de respuesta del área de soporte técnico extrayendo el top 5 de demoras (K.ESIMO.MAYOR) y la mediana del tiempo de atención.

SESIÓN 4: Búsquedas Modernas, Cruces Bidireccionales y Matrices Dinámicas

• Evolución y Limitaciones de BUSCARV: Por qué BUSCARV falla con columnas hacia la izquierda o inserción de columnas. Coincidencia exacta vs. aproximada (para tramos tributarios y comisiones).

• El Salto a BUSCARX: Sintaxis completa de BUSCARX: búsqueda hacia cualquier dirección, valor si no se encuentra integrado, modos de coincidencia y búsqueda de último a primero.

• El Dúo Clásico de Alta Precisión: Combinación de INDICE + COINCIDIR para búsquedas matriciales bidireccionales (cruce simultáneo de fila y columna).

• Introducción a Funciones de Matriz Dinámica: Visualización y filtrado en tiempo real con FILTRAR, ORDENAR y UNICOS.

Ejercicios Prácticos de la Sesión :

• Ejercicio Práctico 4.1 – "Cruces Masivos con BUSCARX y Respaldo de Seguridad": Cruzar una nómina de colaboradores con sucursales y cargos hacia la izquierda, controlando errores sin romper la estructura de la base.

• Ejercicio Práctico 4.2 – "Tarifario Matricial Bidireccional": Determinar el costo de flete según zona de destino (filas) y peso de la carga (columnas) mediante la combinación INDICE + COINCIDIR.

MÓDULO III: GESTIÓN INTERMEDIA DE DATOS, VINCULACIÓN Y TABLAS DINÁMICAS

SESIÓN 5: Validación Dependiente, Consolidación y Vínculos entre Archivos

• Validación de Datos con Listas Desplegables Dependientes: Creación de listas en cascada (ej. seleccionar "Región" y que la lista de "Comunas" se filtre automáticamente) utilizando nombres de rango y la función INDIRECTO.

• Consolidación de Datos: Herramienta Consolidar de Excel para unificar datos de múltiples sucursales o períodos sin necesidad de copiar y pegar manualmente.

• Gestión y Actualización de Vínculos Externos: Vinculación entre diferentes libros de Excel, actualización de rutas, ruptura de vínculos y precauciones para no perder la integridad de las planillas en red.

Ejercicios Prácticos de la Sesión :

• Ejercicio Práctico 5.1 – "Formulario de Registro con Listas en Cascada": Diseñar una interfaz de ingreso donde al elegir una Gerencia se desplieguen únicamente los Cargos pertenecientes a esa división mediante INDIRECTO.

• Ejercicio Práctico 5.2 – "Consolidación Trimestral Multisede": Unificar los balances mensuales provenientes de tres libros independientes (Enero, Febrero, Marzo) en una planilla máster consolidada y actualizada.

SESIÓN 6: Tablas Dinámicas – Estructuración, Agrupación y Cálculos Personalizados

• Fundamentos de las Tablas Dinámicas: Reglas de oro para la base de origen (encabezados únicos, sin celdas combinadas, registros limpios). Configuración del área de trabajo (Filas, Columnas, Valores y Filtros).

• Personalización del Análisis: Modificar operaciones (Suma, Recuento, Promedio, Máx/Mín), dar formato numérico correcto dentro de la tabla y opciones de diseño de informe (formato tabular, subtotales).

• Agrupación y Cálculos Especiales: Agrupación automática de fechas por Año, Trimestre y Mes; agrupación numérica por tramos; y configuración de valores para mostrar % del total general o % de la fila.

• Campos y Elementos Calculados: Crear fórmulas internas en la Tabla Dinámica sin alterar la base de datos original.

Ejercicios Prácticos de la Sesión :

• Ejercicio Práctico 6.1 – "Análisis Multidimensional de Facturación": Resumir una base de 3.000 ventas agrupando por año y mes, calculando el ticket promedio y la participación porcentual de cada línea de producto.

• Ejercicio Práctico 6.2 – "Cálculo de Margen de Utilidad Interno": Incorporar un Campo Calculado en una tabla dinámica para obtener el margen porcentual ((Venta - Costo) / Venta) directamente en el reporte.

MÓDULO IV: REPORTABILIDAD DINÁMICA, DASHBOARDS OPERATIVOS Y EVALUACIÓN

SESIÓN 7: Gráficos Dinámicos, Segmentación y Construcción de Dashboards Operativos

• Gráficos Dinámicos Interactivos: Diferencias entre un gráfico estándar y uno dinámico. Selección adecuada del tipo de gráfico según el dato (columnas para ranking, líneas para tendencias temporales).

• Segmentación de Datos y Escala de Tiempo: Inserción de filtros visuales y conexión de una sola segmentación a múltiples tablas dinámicas en simultáneo.

• Principios de Diseño de Dashboards Operativos: Maquetación limpia, uso de paletas de color sobrias, supresión de líneas de cuadrícula y disposición de KPIs principales para presentaciones ejecutivas.

Ejercicios Prácticos de la Sesión :

• Ejercicio Práctico 7.1 – "Sincronización de Tablas y Filtros Visuales": Conectar tres tablas dinámicas independientes a un único set de segmentadores (Mes, Región y Vendedor) para filtrado cruzado instantáneo.

• Ejercicio Práctico 7.2 – "Construcción de Dashboard Operativo": Montar una pestaña de reportería ejecutiva con 3 KPIs numéricos, un gráfico de tendencia temporal, un gráfico de participación y segmentadores interactivos.

SESIÓN 8: Integración General, Taller Práctico Evaluado y Puente al Nivel Avanzado

• Checklist de Optimización de Modelos Intermedios: Buenas prácticas de rendimiento, validación de integridad de datos y preparación de archivos para automatizaciones mayores.

• Desarrollo del Trabajo Final Integrador en Clase: Resolución guiada de un caso real de negocio que articula todos los contenidos del curso con soporte directo del relator.

• Rúbrica de Evaluación y Retroalimentación: Revisión contra estándares de precisión de fórmulas, diseño dinámico y entrega de resultados.

• Puente al Nivel Avanzado: Demostración introductoria de los siguientes pasos: conexión de datos externos con Power Query y modelamiento relacional previo a macros.

Ejercicios Prácticos de la Sesión :

Proyecto Evaluado – "Sistema de Reportería y Dashboard de Gestión Integral":

• A partir de una base de datos operativa desordenada de una empresa de servicios/ventas, el participante deberá:

• Transformar y normalizar los datos en una Tabla Oficial con nombres estructurados.

• Implementar columnas de control con lógica multicriterio (SI.CONJUNTO / SI + Y) y cruce de datos maestros mediante BUSCARX o INDICE + COINCIDIR.

• Generar una tabla resumen con SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO.

• Diseñar un reporte con Tablas Dinámicas agrupadas y campos calculados.

• Montar un Dashboard operativo interactivo con segmentadores y gráficos dinámicos listo para entrega a jefatura.