Curso Monográfico de Excel para Profesionales y Directivos

1. Manejo de datos extraídos de ERP Y CRM

Con la ayuda de las funciones de texto se podrán manipular de forma fácil y rápida los datos descargados de otros programas para su posterior manipulación en Excel

• Comando Texto en columnas
  • Separar textos
  • Cambiar formatos de fechas
  • Formato decimal y de millares
• Funciones de texto
  • Concatenar y Operador &
  • Funciones principales: ESPACIOS, IZQUIERDA, DERECHA, LARGO, ENCONTRAR, EXTRAER, …
  • Funciones recientes: TEXTOANTES, TEXTODESPUES, DIVIDIRTEXTO

2. La utilidad de la validación de datos

Su empleo permite a los analistas evitar la introducción de datos incorrectos, asegurando la integridad de la información de nuestras bases de datos

• Aspectos iniciales: Configuración, Mensajes de entrada y Mensajes de error
• Definición de criterios de validación
  • Criterios habituales
  • Criterios personalizados
• Creación de listas dependientes

3. El uso del formato condicional para establecer alertas y mensajes

Herramienta útil para identificar de forma visual las situaciones que se desee controlar y que supone un complemento para el análisis de datos

• Aplicación de formatos condicionales
• Formatos condicionales básicos
  • Formatos de barra de datos
  • Formatos de Escala de color
  • Formatos de Iconos
• Formatos condicionales avanzados

4. Comparación de datos reales y presupuestados y realización de informes

Estudio de funciones que permiten realizar comparaciones entre datos reales y presupuestados y las correspondientes desviaciones, presentando informes de los mismos a partir de datos comerciales o contables y elaboración de informes de seguimiento

• BUSCARX y BUSCARV. Funcionamiento y uso en la realización de informes. Cuando usar una u otra
• La función DESREF, para la realización de informes dinámicos
• Las funciones de Base de Datos, filtros avanzados, FILTRAR y APILARV, que nos permiten el uso de grandes volúmenes de datos
• Comparación entre dos tablas de datos
• La consolidación de datos
• Elaboración de informes de seguimiento: comparación de incurrido vs. presupuesto
• Empleo de hipervínculos
• Elaboración de gráficos

5. El tratamiento masivo de datos en Excel

Las tablas permiten crear rangos dinámicos que se actualizan automáticamente al añadir datos, actualizándose también todas las funciones que estén vinculadas. Es la base para el uso avanzado de Excel. El empleo de tablas dinámicas facilita el tratamiento de grandes volúmenes de información

• Las tablas en Excel
• Tablas dinámicas y gráficos dinámicos:
  • Introducción a las tablas dinámicas
  • El origen de datos en las tablas dinámicas
  • Cambio del origen de datos en tablas dinámicas
  • Campos calculados en tablas dinámicas. Limitaciones
  • Realización de cuadros de mando con tablas dinámicas. Gráficos dinámicos y segmentación de datos. El formato en las tablas dinámicas
• El modelo de datos relacional. Power Pivot y Power BI. El contexto. Funcionamiento avanzado de filtros
  • Tablas dinámicas con datos de varias tablas
  • Las relaciones entre dos tablas
  • Informes con datos en distintos archivos. El modelo tabular de tablas relacionadas
  • Relaciones entre varias tablas. Relaciones de estrella
  • El contexto de filtro en tablas dinámicas relacionadas

6. Preparación de datos para su tratamiento posterior en Excel

El objetivo es normalizar los datos para facilitar su análisis posterior con la herramienta Power Query (Obtener y transformar)

• Conexión con el modelo de datos
• Comprensión de Power Query, modelo de datos e informe
• Conexión con ficheros de texto, csv, Access…
• Conexión con una carpeta. Importación de todos los archivos de una carpeta
• Preparación automática de ficheros. Normalización de tablas, eliminación de redundancias
• Combinación de tablas de distintos orígenes
• Anexar ficheros, Agrupar por, Dinamizar y anular dinamización
• Conexión e integración de tablas de origen web
• Reutilización de consultas
• Integración de informes mensuales
• Conexión con varios libros

7. La presentación gráfica de los datos

Análisis de los distintos gráficos de Excel, organizando los datos y eligiendo el tipo de gráfico más adecuado (entre los existentes en Excel) en función del mensaje que se desea mostrar, así como su integración en los informes, y la realización de cuadros de mando

• La organización y preparación de los datos para realizar gráficos
• Los componentes de un gráfico
• Los minigráficos y gráficos dinámicos
• Gráficos dinámicos
• Los distintos gráficos para comparación o mostrar evolución de los datos
• Controles de formularios para la realización de gráficos interactivos

8. Creación de escenarios y análisis de sensibilidad

Estudio de las herramientas en Excel que le ayudarán a simplificar y mecanizar la actividad de simulación, análisis de sensibilidad y optimización

• Como generar diferentes escenarios en función de la evolución de las diferentes hipótesis del negocio
• Utilización de gráficos para el análisis
• Búsqueda de valores límites y punto de break-even
• Aplicación de Buscar Objetivo
• Simulaciones con la herramienta Tabla de datos
  • Tablas de una variable
  • Tablas de dos variables
• Decisiones óptimas: Solver

9. Creación de un cuadro de mando

Los informes generados por la hoja Excel permiten visualizar el grado de cumplimiento de los indicadores definidos de una manera sencilla, intuitiva y eficaz, orientado al público hacia el que va dirigido

• Transferir la información mediante funciones de búsqueda: BUSCARV, BUSCARX
• Análisis de la información mediante tablas dinámicas
• Elección de elementos con ayuda de desplegables
• Creación de gráficos ligados a los resultados

10. Creación de resúmenes de forma rápida y eficaz y elaboración de informes estadísticos

Estudio de las funciones y herramientas que permiten manipular grandes volúmenes de datos para facilitar la realización de cualquier tipo de análisis. Excel también dispone de funciones y herramientas para el análisis estadístico de forma fácil y rigurosa que facilitan este tipo de tareas, generando en algunos casos gráficos además de tablas de resultados

• Ordenación y filtrado de datos: Filtros avanzados
• Creación de subtotales
• Resúmenes cronológicos
• Funciones lógicas: SI, SI.CONJUNTO
• Funciones matemáticas: SUMAR.SI, SUMAR.SI.CONJUNTO, SUMAPRODUCTO
• Funciones estadísticas básicas: PROMEDIO, PROMEDIO.SI, CONTAR, CONTAR.SI, CONTAR.SI.CONJUNTO
• Funciones estadísticas avanzadas: FRECUENCIA, HISTOGRAMA
• Predicción de valores