Curso de Excel Intermedio
Módulo 2 – Excel Intermedio
Se hace énfasis en la combinación de técnicas y el uso de herramientas que optimizan el desarrollo de potentes modelos administrativos. se continúa con el tratamiento detallado de las tablas dinámicas y al final, se va un poco mas allá de las gráficas simples.
Alcance:
Al final de este curso Usted estará en capacidad de crear modelos óptimos en Excel, relacionando datos para la generación de TABLAS DINÁMICAS que agrupen elementos, editando su presentación para generar potentes GRÁFICOS DINÁMICOS, utilizando de manera eficiente muchas herramientas y técnicas que agilizarán su trabajo rutinario.
Formulación tradicional y formulación matricial
• Formulación tradicional con referencias Relativas, Absolutas y Mixtas, versus la Formulación matricial.
Formulación entre rangos de valores.
– Análisis de las referencias relativas.
– Acciones para la extensión de fórmulas.
– Construcción de fórmulas matriciales.
– Rangos como operadores matemáticos.
Fórmulas con rangos y celdas.
– Referencias absolutas y mixtas.
– Errores al fijar celdas en fórmulas.
– Celdas como operadores matriciales.
Más sobre fórmulas matriciales:
– Cambios en las fórmulas matriciales.
– Referencias absolutas, relativas y mixtas.
Formulación tradicional y matricial con nombres
• Formulación con nombres a celdas y rangos, aplicados de manera tradicional y de la forma matricial.
Nombres a celdas y rangos.
– Creación y los caracteres prohibidos.
– El ADMINISTRADOR DE NOMBRES.
– Extender el rango de un nombre.
Formulación con nombres.
– Usando la lista desplegable.
– Desde la ventana PEGAR NOMBRES.
– La Intersección implícita.
Formulación Matricial con nombres.
– Modificar y borrar fórmulas matriciales.
– Edición de celdas y rangos nombrados.
– Cambios en la formulación matricial.
Funciones SI, BUSCARV, Estadísticas y de Fecha
• Aplicación de las funciones SI, BUSCARV y las principales funciones Estadísticas y de Fecha y Hora.
Función lógica SI condicional.
– Planteamiento del Diagrama de Flujo.
– Comparadores lógicos en fórmulas.
– Análisis de los datos de salida.
Funciones fx estudiadas.
– BUSCARV para la búsqueda en tablas.
– Funciones HOY, AÑO, MES y DIA.
– Funciones PROMEDIO y MAX.
Otros comandos y técnicas estudiadas.
– Validación de datos en celdas.
– Borrar formato.
– Formulación de rangos que se amplían.
fx de TEXTO vs. comando TEXTO EN COLUMNAS
• Profundización en funciones de texto EXTRAE, ENCONTRAR y LARGO, versus el comando TEXTO EN COLUMNAS.
Funciones de texto estudiadas.
– IZQUIERDA, DERECHA, CONCAT, EXTRAE, ENCONTRAR y LARGO.
Técnicas con funciones de texto.
– Extraer y unir fracciones de texto.
– Limitantes de las funciones solas.
– Anidación para ubicar distintas posiciones de caracteres especiales.
– Eliminar caracteres en blanco al final.
TEXTO EN COLUMNAS vs fx de TEXTO.
– Definidos por un “ancho fijo”.
– Definidos por “caracteres separadores”.
Tipos de error, sus funcones y su auditaría
• Tipos de error en celdas formuladas, las funciones de ERROR y la manera de auditarlos.
Los Errores identificados por Excel.
– Longitud de caracteres (####).
– División por cero (#¡DIV/0!).
– Valor (#¡VALOR!).
– No Aplica (#¡N/A!).
– Referencia (#¡REF!).
Acciones sobre los errores.
– Evaluar Paso a Paso un error.
– Auditar precedencia y dependencia.
– Rastrear el origen del error.
– Errores que se pueden omitir.
Funciones ESERROR, ESERR y SI.ERROR.
TABLAS para ORDENAR, FILTRAR Y SubTOTALES
• Manipulación de listas versus las tablas de Excel y los comandos ORDENAR, FILTRAR y SUBTOTALES.
Aspectos de las TABLAS de Excel.
– Nombres en rangos vs en TABLAS.
– Ventajas al formular en TABLAS.
– Referenciación de TABLAS en fórmulas.
Ordenar TABLAS por múltiples criterios.
– Criterio alfabético y numérico.
– Crear LISTAS de ordenación.
Filtrar por rangos de valores.
– Múltiples criterios y los conectores Y y O.
– Termina con, Contiene, Mayor a, Entre…
– Análisis visual al filtrar rangos de valores.
SUBTOTALES vs TABLAS DINÁMICAS.
Crear y editar FORMATOS CONDICIONALES
• Creación y edición de Formatos condicionales en Excel.
Iniciando con Formatos Condicionales.
– Aplicación para una celda o un rango.
– Fórmulas lógicas que definen los FC.
– Formatos predefinidos y de salida.
Formatos Condicionales predefinidos.
– DUPLICADOS, ÚNICOS, PROMEDIO, …
– Diseños: BARRAS, ESCALAS e ÍCONOS.
– Análisis con SEMÁFOROS y la Simetría.
Ventana ADMINISTRADOR DE REGLAS.
– Edición de celdas y rangos con FC.
– Varios FC a un mismo rango.
– EDITAR REGLA, cambiar condiciones.
Varios temas que complementan esta módulo
• Temas múltiples que complementan el dominio intermedio de Excel.
Aplicación de los ESTILOS DE TEXTO.
– Preestablecidos y personalizados.
Las VISTAS PERSONALIZADAS.
– Características que se almacenan.
Los COMENTARIOS EN CELDAS.
BLOQUEAR versus PROTEGER.
– Características que se bloquean al proteger una hoja.
– Contraseñas.
Comando BUSCAR OBJETIVO.
Más sobre VALIDACIÓN DE CELDAS.
– Validaciones y mensajes de error.
– Permitir o no registrar otros valores.
Análisis y edición de TABLAS DINÁMICAS simples
• Análisis durante la creación de Tablas Dinámicas de Excel y los primeros pasos para editar sus resultados.
Estructuras para el análisis de datos.
– Tablas de datos y sus ventajas.
– Prefijos en TABLAS y CAMPOS.
– Cuidados al registrar información.
– Campos INDETIFICADORES de filas.
El área VALORES en TABLA DINÁMICA.
– CAMPOS, ELEMENTOS y su impacto.
– Agrupación acorde a los ELEMENTOS.
– Cuidados con la función CONTAR.
Análisis de áreas FILAS y COLUMNAS.
– Los Subtotales en campos agrupados.
– Diseños para la visualización de TD.
Estructuras, Filgros, Agrupación y Fechas en TDs
• Estructuras de datos en Tablas Dinámicas, Filtros, agrupación de elementos y el manejo de fechas.
Estructura óptima en modelos de datos.
– La TABLA PRINCIPAL y AUXILIARES.
– Tipo de relaciones entre TABLAS.
– fx BUSCARV para relacionar tablas.
– Columnas externas y principales.
– Análisis con el campo FECHA.
Más sobre TABLAS DINÁMICAS.
– Filtros sobre elementos y campos.
– La SEGMENTACIÓN DE DATOS.
– Segmentación de ESCALA DE TIEMPO.
– Agrupación de ELEMENTOS en TD.
– Tips sobre otros aspectos en TD.
Análisis de encuetnas con fx «.SI», TDs y Gráficas
• Funciones CONTAR.SI y SUMAR.SI versus las Tablas y Gráficas para el análisis de encuestas.
Funciones “punto SI” en TABLAS.
– fx CONTAR.SI, SUMAR.SI, y PROM.SI. Cols de criterios y cálculos.
– Referenciación a cols de Tablas.
Tips y trucos adicionales:
– Comando Ir a Celdas en Blanco.
– Comando Buscar y Reemplazar.
– Pegado especial de Validación.
– Las GRÁFICAS DINÁMICAS.
– Botones y controles sobre la hoja.
– Las propiedades de los controles.
– Análisis de tabulación de encuestas.
fx financieras para analizar opciones e crédito
• Técnicas para la CONSOLIDACIÓN de datos en Tablas Dinámicas a partir de diferentes rangos.
La Consolidación de datos en Excel.
– Estructuras de tablas y cruzadas.
– Plantillas para la recolección de datos.
– Encabezados en Rangos de consolidación.
– Optimización de los RC.
– El comando CONSOLIDAR vs las Tablas Dinámicas para consolidación.
– Agrupación de múltiples resultados.
Otras técnicas estudiadas.
– La formulación 3D vs la formulación tradicional entre hojas.
– Los nombres en las TABLAS.
Plantillas de gráficas que facilitan su edición
• Creación de Plantillas para agrupar características de diseño de gráficas que faciliten su edición.
Diseños de Plantillas de gráficas.
– Diseños de gráfico y Estilos.
– Etiquetas y Tablas con Claves.
– Líneas principales y secundarias.
El trabajo con Plantillas de gráficas.
– Graficar a partir de Plantillas.
– Aplicar plantilla a una gráfica.
– Visualización plantillas disponibles.
La ventana Administrador de Plantillas.
– Copia de respaldo de una plantilla.
– Compartir plantillas.
– Plantillas como predeterminadas.
Los Mini-gráficos o gráficos en celdas de Excel
• Los mini-gráficos o gráficos en celdas y la manera de editarlos para facilitar su interpretación.
Creación y edición de Minigráficos.
– Rango de datos y la celda de Ubicación.
– Una celda o rango como Ubicación.
– Gráficos alineados o no a sus datos.
Cuidados al crear y editar Minigráficos.
– Seleccionando la celda Ubicación.
– Con Rango de datos varias filas y cols.
– Al copiar una celda con un Minigráfico.
Otros aspectos de las Minigráficas:
– Puntos altos, bajos, negativos…
– Errores de proporcionalidad.
– Escalas de fechas y su visualización.
Gráficas para la proyección de tendencias
• Gráficas para la proyección de pronósticos a partir de datos históricos.
Graficación de datos históricos
– Ajuste de sus escalas.
– Definición de límites máx y mín.
– Espaciamiento ejes principales.
– Definición de ejes secundarios para facilitar su análisis.
Métodos de proyección de las Líneas de tendencia:
– Tendencias LINEALES, EXPONENCIALES, POLINÓMICAS, …
– Asignación de periodos a proyectar.
– Ajustes del valor de la variable R2.
Más sobre Gráficas Circulares y Sub gráficas
• Aspectos adicionales de las GRÁFICAS CIRCULARES que las hacen más funcionales.
Configuración de Gráficas Circulares.
– Ventana Formato de etiqueta de datos.
– Ventana Asignar formato a la selección.
– Menú Elemento de gráficos / cinta DISEÑO.
Aspectos de presentación simple.
– Distintos valores en el área de la serie.
– Valores entre líneas o separados por coma.
– Control del formato numérico.
Aspectos para análisis más detallados.
– Agrupación en una Sub-gráfica.
– Criterio para agrupar elementos.
– Tipos de Sub-gráficas.
5 comentarios