Curso de Excel Avanzado
Módulo 3 – Excel Avanzado 1
Técnicas avanzadas para el análisis y la generación de Tablas Dinámicas Comparativas.
Es indispensable que el usuario de Excel domine las funciones lógicas, la formulación avanzada y que manipule adecuadamente los datos que arrojan los programas administrativos, de tal manera que formule en las mismas tablas dinámicas todo tipo de informe comparativo.
Alcance:
Al final de este curso Usted estará en capacidad de resolver múltiples situaciones que requieren de la formulación avanzada en Excel, así como generar múltiples cálculos dentro de las TABLAS DINÁMICAS y resumir las bases de datos en INFORMES COMPARATIVOS que describan el comportamiento de los resultados obtenidos, generando potentes GRÁFICAS ESPECIALIZADAS.
Formulación de grandes modelos en Excel
• La manera óptima de formular grandes modelos administrativos en Excel.
Aplicación sobre un modelo de costos.
– Variables en rangos de una fila o columna.
– Resultados de operar entre rangos.
– Operaciones entre rangos y constantes.
Repaso de los pre-requisitos.
– Referencias relativas, absolutas y mixtas.
– Fórmulas con nombres a celdas y rangos.
– Fórmulas matriciales.
Técnicas de Excel empleadas.
– La ventana PEGAR NOMBRE.
– El comando CREAR DESDE LA SELECCIÓN.
– Uso del comando APLICAR NOMBRES.
fx SI, Y y O y su relación con la fx BUSCARV
• Análisis fx SI, Y y O con Diagramas y Tablas y la función BUSCARV para casos complejos.
Plantear dos o más condiciones.
– Conexión lógica Y y O.
– Análisis de las posibles salidas.
Diagramas de FLUJO DE INFORMACIÓN:
– Función SI anidada a otra SI.
– Funciones Y y O, anidadas a una SI.
Análisis de TABLAS DE LA VERDAD.
– Dos condiciones con dos salidas.
– Dos condiciones con tres salidas.
El cumplimiento de muchas condiciones:
– Análisis del DIAGRAMA DE FLUJO.
– BUSCARV en muchas condiciones.
FX lógicas SI, Y, O y los Filtros Avanzados
• Más sobre condicionales anidados, funciones Y / O y su aplicación en los FILTROS AVANZADOS.
Análisis de pruebas lógicas complejas:
– Comparadores lógicos salidas V/F.
– Análisis de múltiples pruebas lógicas.
– Función SI anidando BUSCARV.
– Análisis de las conexiones Y entre columnas y el O entre filas.
– Diagramas de flujo para conectar datos externos y fx O, en grupos de fx Y.
Filtros avanzados.
– Definición de Rangos de criterios.
– Errores con resultados inesperados.
– Copiar los resultados en otros rangos.
Funciones «.SI» Y «.SI.CONJUNTO».
• Funciones “.SI” y “.SI.CONJUNTO” y otras funciones y herramientas que las complementan.
Planteamiento de las fx “punto SI”:
– Columna de prueba lógica.
– Columna de operación matemática.
Formulando con TABLAS DE EXCEL.
– Referenciación de encabezados.
– Referenciación de columnas.
Funciones “punto SI punto CONJUNTO”.
– El orden de sus argumentos.
– Identificación de condiciones.
– Argumentos en las fx CONTAR, MÁX MIN y SUMA… “.SI.CONJUNTO”
La función SI.CONJUNTO vs BUSCARV.
Temas múltiples que complementan
• Temas múltiples que complementan el dominio avanzado de Excel.
Fórmulas matriciales avanzadas.
– Operaciones matriciales comparativas.
– Varias operaciones en una celda.
Formatos condicionales a partir de fórmulas.
– Para resaltar una fila o una columna.
– Par resaltar una única celda.
Búsqueda matricial con BUSCARV y COINCIDIR.
BUSCARV para buscar por rangos de valores.
VALIDACIÓN de celdas con listas variables.
fx INDIRECTO para referir nombres de rangos.
Tabla de resultados de un modelo complejo.
Solver para maximizar utilidades.
Más sobre las TABLAS DINÁMICAS TDs
• Otros conceptos, Técnicas y Herramientas de las TABLAS DINÁMICAS (TD).
Ordenar los elementos de una TD.
– Orden predefinido por el Tipo de datos.
– Ordenar por LISTAS PERSONALIZADAS.
– Ordenar por columnas de resultados.
Aspectos adicionales del filtrado en TD.
– Comodines ? * en filtros de etiqueta.
– Filtros de valor en total de filas y cols.
Otros conceptos avanzados sobre TD.
– Funcionamiento de la memoria caché.
– Una misma SEGMENTACIÓN DE DATOS y ESCALA DE TIEMPO para varias TD.
Proteger sin bloquear paneles segmentación.
Error común en TDs y Power Query
• El error más común al crear Tablas Dinámicas, sus soluciones y el inicio con Power Query.
Estructuras que conllevan a errores en TD.
– Estructuras de reportes vs planas.
– Detección de estructuras erróneas.
– TD con estructuras inadecuadas.
Errores en TABLAS DINÁMICAS.
– Valores en áreas de FILAS y/o COLS.
– Manejo de los elementos vacíos.
– Operaciones inesperadas.
– Error de ELEMENTOS CALCULADOS al calcular diferencias entre periodos.
Transformar una estructura de reporte con una Macro y con Power Query.
Tablas Dinámicas con cálculos comparativos
• Análisis, construcción y edición de Tablas Dinámicas con cálculos comparativos.
Errores al formular con celdas de TD.
Cálculos comparativos incluidos:
– Participación porcentual.
– Acumulados por periodos.
– Diferencias entre periodos analizadas de forma absoluta y porcentual.
Técnicas aplicadas al trabajo con TD.
– Accesos a los cálculos predefinidos.
– Distribución de una TD con más de un cálculo en el área de VALORES.
– Edición detallada en el área VALORES.
– Errores en CÁLCULOS COMPARATIVOS.
Elementos calculados en Tablas Dinámicas
• Elementos calculados para la comparación entre periodos en TABLAS DINÁMICAS de Excel.
Diferencias entre meses de diferentes años.
– Sin acumular y acumulando.
– Un segundo cálculo comparativo.
Combinación de ELEMENTOS CALCULADOS con CÁLCULOS COMPARATIVOS.
Cálculos especiales dentro de las TD.
– Los TOTALES en ELEM CALCULADOS.
– Ocultar y mostrar elementos.
– Las fechas en los ELEM CALCULADOS.
– Agrupación de ELEMENTOS.
¿Por qué un CÁLCULO COMPARATIVO para los meses y un ELEM CALCULADO para los años?
Estructuras para Tablas Dinámicas Comparativas
• Estructuras ideales para las TABLAS DINÁMICAS comparativas: “un caso de Presupuesto”
Errores al formular datos comparativos:
– Estructura cruzada simple.
– Operaciones entre una estructura cruzada y una TABLA DINÁMICA.
Presupuesto vs datos transaccionales.
– Usando ELEMENTOS CALCULADOS.
– Usando CAMPOS CALCULADOS.
– Separando columnas de valores.
– Agregando una columna descriptiva.
Consolidación de datos a comparar:
– Registros y distribución de los datos.
– Geometría de las TD y de sus gráficas.
Campos y Elementos Calculados en TDs
• Análisis y aplicación de los CAMPOS y ELEMENTOS CALCULADOS en TABLAS DINÁMICAS.
Análisis previo de un caso de presupuesto.
– Resultados sin acumular y acumulados.
– La geometría de las TABLAS DINÁMICAS y GRÁFICAS DINÁMICAS esperadas.
Formas de registrar y formular datos comparativos.
– Con un campo adicional.
– Diferenciando los campos de valor.
– Graficación de los campos de valor.
CAMPOS CALCULADOS vs ELEMENTOS CALCULADOS.
– Resultados únicos y agrupados.
– Formulación dentro de las TABLAS DINÁMICAS vs formular en las TABLAS BASES DE DATOS.
Excel como Sistema de Informacion S.I
• Excel como herramienta para relacionar datos y generar consultas en un sistema de información.
Relación de tablas para el registro de pedidos.
– Tabla principal y Tablas auxiliares.
– Campos que identifican registros.
– Modelo de relación de Tablas ER.
Otras maneras de relacionar tablas de Excel.
– El comando RELACIONES.
– El complemento Microsoft Query.
Ventajas y limitantes de relacionar tablas.
– Omitir el uso de la función BUSCARV.
– Cruzar información de distintas tablas.
– Operaciones entre campos numéricos de distintas tablas con MS Query.
Consolidar DATOS en TABLAS DINÁMICAS TDs
• Introducción al Power Pivot, Power BI y al lenguaje DAX para el manejo de potentes bases de datos.
Modelo relacional en Excel Power Pivot.
– Tablas de Excel en Power Pivot.
– Visualizar y relacionar tablas.
– Relación de uno a muchos.
– La ventana EDITAR RELACIÓN.
– Operaciones entre campos de distintas tablas.
Función RELATED.
– TABLAS DINÁMICAS de Power Pivot.
Introducción de Microsoft Power BI.
– El comando GET DATA.
– El comando RELATIONSHIPS.
– EDIT QUIERY y MERGE QUIERIES.
– Columnas agregadas ADD COLUMN.
Análisis de las TDs de los ESCENARIOS de Excel
• Las funciones de tipo CUBO y sus relación con las TABLAS DINÁMICAS y con POWER PIVOT.
El MODELO DE DATOS de Excel.
– Desde el comando RELACIONES.
– Desde POWER PIVOT.
El concepto de CUBOS OLAP o cubos de datos:
– CUBOS vs TABLAS DINÁMICAS de Excel.
– La DIMENSIÓN tiempo como ejemplo.
– Análisis de una DIMENSIÓN en un CUBO.
– Las fx VALORCUBO y MIEMBROCUBO.
POWER PIVOT y las funciones tipo CUBO:
– DIMENSIONES en POWER PIVOT.
– SEGMENTACIÓN DE DATOS y el TIMELINE para filtrar resultados CUBO.
Gráficas con dos escalas de valores
• Distintas maneras de representar GRÁFICAS CON DOS ESCALAS DE VALORES en dos ejes.
Valores en los ejes primario y secundario.
– Parciales y totales en diferentes ejes.
– Una Serie en el eje secundario.
– Una Categoría en el eje secundario.
Valores en ejes vertical primario y horizontal.
– Formulación gráfica Exponencial.
– Análisis de las gráficas de Dispersión.
– Análisis de las Escalas Logarítmica.
Elementos de una gráfica de cotizaciones.
– Precio Máximo, Mínimo y de Cierre.
– Anteriores más Volumen y Apertura.
– Mensajes de error y edición de estilos.
Más Gráficas para la Gestión Adminsitrativa
• Creación y edición de otros tipos de gráficas para el análisis de información administrativa.
Ejemplo para una GRÁFICAS DE BURBUJAS.
– Una dimensión más una Serie.
– Las GRÁFICAS DE BURBUJAS versus las GRÁFICAS DE DISPERSIÓN.
GRÁFICAS DE CASCADA de un estado financiero.
– Configuración de COLUMNAS FLOTANTES para los valores positivos y negativos.
– Configuración de los TOTALES PARCIALES para los valores formulados.
Las GRÁFICAS DE MAPAS de Excel 2016.
– Representación de valores y propiedades.
– Acerca del complemento POWER MAPS.
4 comentarios