Curso Microsoft Excel avanzado

TEMARIO.

Modulo 1. Office 2019. Actualizaciones.

• ¿ Cual es la diferencia entre office 365 y office 2019 ?

  • Archivos descargables: Dar un paseo / Introducción a formulas

• Como insertar un archivo SVG. Novedades para Excel, Word y Power Point,

• Datos geográficos y de cotizaciones.

• Notas y comentarios encadenados.

• Nueva barra de búsqueda.

• Control de cambios.

• Introducción a obtener y transformar en Excel.

• Foro sobre Power BI Desktop.

• Tutoriales.

• Pagina actualizaciones de power BI

*********************************************

Modulo 2.

• Introducción a Power Query.

• Parte 1.

• Parte 2.

• Parte 3.

• Parte 4.

• Parte 5.

• Parte 6.

• Parte 7.

Modulo 3..

• Funciones de bases de datos.

Modulo 4. Formulas y funciones de matriz.

• Formulas de matriz.

• Funciones matriciales.

• ¿Que son los derrames?

• Operador implícito @.

• Fórmulas de matriz dinámica frente a fórmulas de matriz de CSE heredadas

• La función TRANSPONER para invertir filas por columnas.

• Ejemplo con la Función TRANSPONER.

• Función FRECUENCIA.

• Función ÚNICOS. Conteo de valores duplicados.

• K.ESIMO.MENOR como formula de matriz.

• Función matricial ORDENAR.

• Función matricial ORDENARPOR.

• Función matricial FILTRAR.

• Función matricial SECUENCIA.

• Función MATRIZALEAT.

• La nueva funcion LET.

• La funcion MMULT.

Modulo 5. Ejemplos con funciones matriciales.

• La función ÚNICOS. Conteo de valores duplicados.

• Extraer valores únicos de una matriz.

• Cuadro de meses y años creados de forma matricial, con gráficos y mini graficos.

• Gráfico dinámico a partir de matriz derramada.

• SUMARSI, INDICE, TRANSPONER con matrices derramadas.

• FRECUENCIA e INDICE.

• Ejemplo de matrices con derrame.

• Crear una tabla dinamica con matrices derramadas.

• Calculo de totales por pais y año junto con grafico dinamico.

• Calculo de ventas maximas y minimas por años.

• Como crear una formula personalizada con matrices dinamicas.

Modulo 6. 

• Análisis de datos.

• Solver.

******************************************************

Modulo 7. Power Pivot para Excel 2016 (valido para Excel 2019).

•  ¿Qué es Power Pivot?

• Iniciar el complemento de Power Pivot.

• Crear modelo de datos en excel.

• Crear un modelo eficiente en cuanto a memoria.

• Crear cambios en el modelo de datos.

• Primer ejercicio en Power Pivot.

• Conjunto de campos predeterminado.

• Ocultar tablas, y, columnas.

Modulo 8. Principales iconos en Power Pivot.

• Agregar tablas al modelo de datos.

• Iconos del menú de Power Pivot.

• Administrar modelo de datos en Excel 2016.

• Principales menús del modelo de datos.

• Crear relaciones entre tablas.

• Crear tabla dinámica a partir del modelo de datos.

Modulo 9. Importar datos a  Power Pivot.

• Fuentes de datos para construir el modelo.

• Obtener datos desde Access.

• Obtener datos desde un archivo .CSV o .TXT.

• Obtener datos desde archivos de Excel.

• Obtener datos de otras fuentes.

• Ejemplo: Crear modelo de datos en Excel.

Modulo 10. Tablas y gráficos dinámicos en Power Pivot para Excel 2016.

• Tablas y gráficos dinámicos a partir del modelo de Power Pivot.

• Acciones avanzadas en tablas dinámicas.

• Posibilidades de crear varios gráficos dinámicos.

• Segmentación de tablas y gráficos dinámicos.

• Dashboard o panel de control con Power Pivot.

Modulo 11. Tratar los datos en Power Pivot en Excel 2016.

• Dar formato a los datos del modelo.

• Diferentes opciones con filtros existentes.

• Realizar acciones en columnas.

Modulo 12. Cálculos y formulas DAX.

• Operaciones básicas con autosuma.

• Operaciones estadísticas con autosuma.

• Introducción a formulas DAX.

• Cuando usar columnas calculadas o campos calculados (medidas).

• Formulas heredadas de Excel.

• Formulas usadas mas frecuentemente en Excel 2016.

• Formulas intermedias en DAX para Excel 2016.

• Otras funciones DAX en Excel 2016.

Modulo 13. Calcular KPIs en Power Pivot para Excel 2016.

• Que son los KPIs.

• Una mirada a la herramienta KPI.

• Crear KPIs.

Modulo 14. Compartir y guardar tus modelos.

• Guardar en diferentes ubicaciones tus modelos de datos.

• Compartir con colaboradores tus modelos de datos.

• Proteger libro Excel con modelo de datos.

Modulo 15. Un vistazo a Power View. 

• Introducción e instalación de Power View.

• Principales acciones en Power View.

• Crear tu primer panel en Power View.

Modulo 16. Un vistazo a otros complementos Power de Excel.

•  Power Map.

• Power Query.

********************************************************

Modulo 17. Macros.

• Ficha Desarrollador y entorno de Visual Basic.

• Mi primera macro con msgbox.

• Formas distintas de ejecutar una macro.

• Como guardar un libro con macros.

• Proteger archivos con macros.

• Como escribir datos en una celda.

• Como procesar un rango de datos vertical.

• Como procesar un rango de datos horizontal.

• Ejemplo simple con la sentencia IF…THEN.

• Ejemplo avanzado con la sentencia IF…THEN.

• La sentencia INPUTBOX.

Modulo 18.

• Grabación de macros: ejemplo simple.

• Optimizando el código de la grabadora.

• Grabadora de macros: ejemplo complejo.

• Cuando conviene usar la grabadora, y, cuando no.

Modulo 19.

• Que es un control de formulario y como buscar un valor con la función ÍNDICE.

• Generando el reporte a partir de controles.

Modulo 20.

• Preparación del archivo previo a la macro.

• Escribiendo el FOR – NEXT y el IF – THEN para copiar los datos.

• Afinando la macro y definiendo el contador.

• Dando formato con la grabadora y borrando los datos.

Modulo 21.

• Definición de variables con INPUT BOX y explicación de contador de fila.

• Copiado de filas con el primer FOR – NEXT.

• Copiado de columnas con FOR – NEXT ANIDADO.

• Agregado de la sentencia IF – THEN para excluir las filas vacías.

• Verificación de la macro o DEBUGGING paso a paso.

Modulo 22.

• Introducción a formularios e informes.

• Desarrollo de un formulario paso a paso, dividido en tres partes.

• Controlando el correlativo.

• Controlando el ingreso de números.

Modulo 23.

• Reporte dinámico con macros y funciones, parte 1.

• Reporte dinámico con macros y funciones, parte 2.

• Reporte dinámico con macros y funciones, parte 3.

• Reporte dinámico con macros y funciones, parte 4.

***************************************************

Modulo 24. Como crear un calendario para nuestros proyectos.

• Como crear un calendario para nuestros proyectos en una hoja de calculo de Excel.

• Como crear un calendario dentro de un formulario de Excel para una sola fecha.

• Como crear un calendario dentro de un formulario de Excel para dos fechas.

Modulo 25. Ejemplos práctico.

Modulo 26. Ejemplos.

• Obtener el valor mínimo con la función BDMIN.

• Encontrar la ultima fila ocupada.

• Como cambiar de color la fila activa.

************************************************

Modulo 27. DAX.

• Visión general.

• Tabla calculada.

• Fórmulas.

• Funciones.

• Contextos de Evaluación.

• Contexto de fila.

• Contexto de consulta.

• Contexto de filtro.

• Contexto de filtro y fila mediante relaciones.

• Relaciones entre tablas.

• RLS – Seguridad a nivel de fila.

• Procesar y Actualizar (Texto).

Modulo 28. Funciones de filtro.

• ALL.

• Ejemplo con la función ALL.

• Ejemplo. Removiendo filtros.

• Ejemplo. Diferencia entre ALL y DISTINCT.

• CrossFilter.

• HasonFilter.

• HasonValue y Values.

• Keepfilters.

• Ejemplo con ALL, FILTER y KEEPFILTERS.

• CalculeTable, Distinct, IsFiltered.

• Values.

Modulo 29. Funciones Time Inteligence.

• DatesBetween.

• DATESMTD, DATESQTD, DATESYTD.

• DATESYTD.

• DATEADD.

• EDATE. Ejemplo calcular unidades vendidas desde la ultima fecha vendida del año en curso hasta la misma fecha del año anterior.

• DatesInPeriod.

• SamePeriodLastYear.

• Ejemplo SamePeriodLastYear.

• ParalellPeriod.

• PreviousDay, PreviousMonth, PreviousYear.

• NextDay, NextMonth, NextQuarter, NextYear.

• TotalMtd, TotalYtd.

• Secretos ocultos de TOTALYTD.

• FirsDay, LastDay.

• Closingbalancemonth, Endofmonth y Lastday.

• Calcular totales de forma correcta.

Modulo 30. Funciones lógicas.

• Switch.

• If, And y Or.

• Ejemplo de Switch/IF junto con variables.

Modulo 32. Funciones de fecha y hora.

• Calendar y CalendarAuto.

• Crear un calendario con formulas DAX.

• Date, DatesBetween, DateDiff.

• Ejemplo: ¿Cuando es mi cumpleaños?

• Custom Visual: Advance Card.

• Edate.

• Diversas funciones de fecha y hora.

Modulo 33. Funciones Matemáticas.

• ABS, Ceiling, Combin, Combina.

• Currency y Divide.

• Sumarize.

• SumarizeColumns.

• Sum y SumX.

Modulo 34. Funciones Matemáticas.

• Filter, filters.

• Ejemplo. Funciones de tablas mezcladas.

• Related.

• RelatedTable

Modulo 35. Funciones Matemáticas.

• Sumando valores para el total.

• Consultando días laborables.

• Comparación periodos de tiempo.

• Firstnonblank, Lastnonblank, y, TOPN.

• Promedio de 12 meses.

• Efectos secundarios en el ordenamiento por ajuste de columna.

• Referencia a tabla y columna mediante variables.

*******************************************************************************

Modulo 36. Cuadros de mando o dashboards para controlar la información.

• Introducción. Archivo descargable para este curso.

Modulo 37. Cuestiones previas acerca de los dashboards.

• Definición y contexto.

• Concepto de KPI. Key Performance Indicator.

• El mapa o composición de un dashboard.

• Estrategia al crear un dashboard. Audiencia y mensaje.

• KPI y tablas necesarias al crear un dashboard.

• Estrategia al crear un dashboard. Origen de datos.

Modulo 38. Partes mas importantes de un dashboard.

• La hoja opciones o configuración.

• La hoja auxiliar de un dashboard.

• La etapa Datos. El inicio.

• La etapa Análisis. Los datos, una vez procesados.

• La etapa Visualización para presentar la información.

Modulo 39. Construir el dashboard. Pasos previos.

• Un paseo por nuestro futuro dashboard, ya terminado.

• Audiencia y mensaje a transmitir con el dashboard.

• Determinación de los KPI y los tipos de gráficos.

• Boceto de nuestro dashboard.

• Tablas y datos necesarios en el cuadro de mando.

• Origen y forma de obtener los datos.

• Consejos para la configuración de hojas del dashboard.

• Las hojas Portada, Auxiliar y Configuración.

• Controles especiales para el dashboard.

Modulo 40. Construir el dashboard. Etapa 1. Datos.

• Importar datos fusionando tablas. Opción 1.

• Importar datos relacionando tablas. Opción 2.

• Importar datos con conexión a Access. Opción 3.

• Importar datos mediante el portapapeles. Opción 4.

• Importar datos con conexión a Excel. Opción 5.

• Problemas con las conexiones de datos.

• Actualizar los datos conectados.

• Agregar otras columnas necesarias.

Modulo 41. Construir el dashboard. Etapa 2. Análisis.

• Premisas antes de comenzar a construir el dashboard.

• Creación de las primeras tablas dinámicas.

• Réplica de las tablas dinámicas.

• Filtrado de las tablas dinámicas.

• La tabla de promedios. Funciones PROMEDIO y SI.ERROR.

• La función SI en la tabla de promedios.

• La hoja previa al dashboard. Títulos de meses.

• La hoja previa al dashboard. Cifras de evolución mensual.

• Otras cifras necesarias en el dashboard.

• Cifras de los velocímetros en el dashboard.

Modulo 42. Construir el dashboard. Etapa 3. Visualización.

• Zonas básicas de visualización del dashboard.

• Crear cuadros de texto para KPI numéricos.

• Crear el gráfico combinado del dashboard.

• Gráficos de velocímetro.

• Crear los gráficos de velocímetro en el dashboard.

• Crear los gráficos de anillo.

• Crear la cuenta de resultados.

• Crear la segmentación de datos.

• Dar formato al panel de segmentación de datos.

Modulo 43. Construir el dashboard. Automatizacion y proteccion.

• Automatizar el zoom del dashboard.

• Otras automatizaciones. Crear casilla de verificación.

• Proteger nuestro dashboard.

Modulo 44. Otro dashboard alternativo.

• Vista previa de nuestro nuevo dashboard.

• La hoja Datos. La tabla origen del cuadro de mando.

• Tabla de ventas por ruta y agente.

• Crear tabla dinámica para el apartado Ventas por agente.

• Crear el gráfico de anillo en el dashboard.

• Gráfico de barras para el ranking de mejores clientes.

• Gráfico especial de barras para los productos más vendidos.

• Crear un campo calculado para comisiones de venta.

• Un toque de color con formato condicional.

• Crear la agrupación de fechas por meses y años.

• Segmentación de datos para filtrar todo coordinadamente.

********************************************************************

Modulo 45. 100 trucos de productividad en Excel. Configurar y optimizar excel.

• 3 trucos para acelerar el funcionamiento de Excel.

• 5 trucos desde las ‘Opciones de Excel.

• Quitar las líneas de cuadrícula de una hoja.

• Reducir al máximo el tamaño de un archivo Excel.

• Restaurar las configuraciones por defecto de Excel.

Modulo 46. 100 trucos de productividad en Excel. Formato de valores y celdas.

• 2 trucos para resaltar celdas automáticamente.

• Borrar todo en una celda o rango de celdas.

• Cambiar signo negativo de derecha a izquierda.

• Convertir números de texto a números de valor.

• Forzar formato de número telefónico.

Modulo 47. 100 trucos de productividad en Excel. Gráficos en Excel.

• 5 Trucos para mejorar nuestros gráficos.

• Agregar series a un gráfico en tres segundos.

• Evitar que un gráfico de área represente ceros.

• Cómo hacer un gráfico interactivo.

• Gráficos dentro de una celda con Repetir.

• Como hacer un gráfico termómetro.

Modulo 48. 100 trucos. Introducción de datos en excel.

• 3 trucos para la edición de celdas.

• Listas desplegables en Excel.

• 5 trucos para introducir datos rápidamente.

• Comentarios dentro de fórmulas con la función N.

• Insertar iconos y símbolos en Excel.

• Insertar una ecuación en Excel.

• Solucionar un error cometido en múltiples fórmulas.

Modulo 49. 100 trucos. Ordenar y filtrar en excel.

• Dejar valores únicos en una lista.

• Filtrar por más de dos criterios un campo de tabla.

• Ordenar una tabla por más de un campo.

Modulo 50. 100 trucos. Productividad y ahorro de tiempo en excel.

• Trucos para alinear graficos y formas.

• Apertura de libros de Excel.

• Barra de inicio rápido en Excel.

• Trucos para seleccionar rangos rápidamente.

• 3 trucos muy rápidos sobre celdas que ahorran tiempo.

• Depurar tablas importadas de un ERP con una macro.

• Transponer filas a columnas o al revés.

Modulo 51. 100 trucos. Proteccion de datos y detectar errores en excel.

• 5 trucos para proteger nuestra información.

• Compartir un libro y revisar cambios.

• Detectar diferencias y duplicados en listas.

• Mantener actualizada siempre una tabla dinámica.

• Permitir a VBA ejecutar macros en hojas protegidas.

• Usar CodeName en VBA para evitar incoherencias.

• Ver una vista previa de lo que se va a imprimir.

Modulo 52. 100 trucos. Sumas especiales y manejo de texto en excel.

• Dos formas de hacer sumas acumuladas.

• Varios trucos para manejar texto.

• Sumar rangos con filas ocultas o con errores.

Modulo 53. 100 trucos. Varios trucos de Excel.

• Movernos por Excel con más agilidad.

• Ajuste de columnas y filas en Excel.

• Imprimir Excel de forma profesional.

• Trucos geniales para impresionar al jefe.

• Filtrar una tabla por color.

• Combinar celdas horizontalmente.

• Crear un libro nuevo de Excel a partir de una hoja.

• Bloquear objetos ante la eliminación de filas.

• Tabla dinámica con varias tablas de origen.

• Calcular el trimestre correspondiente a una fecha.

Modulo54. Auto-evaluación

• Cuestionario: 29 preguntas a responder.

• Ejercicio hotel FTF.

• Ejercicio Apartamentos Soy y Playa.

• Ejercicio Alquiler de coches.

*** Importante: El 95% de la información sobre Microsoft Power BI esta en ingles, por lo que en el modulo 0, la pagina a la que se se direcciona esta en ingles, por eso, en el curso recomiendo usar la versión de Power BI Desktop en ingles ***