MÓDULO III

EXCEL AVANZADO I

16 lecciones en 16 horas…

Técnicas Avanzadas para generación de informes

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.
Temas relevantes: – Formulación avanzada, – Las fx simples y anidadas: SI, YO y BUSCARV, INDICECOINCIDIRINDIRECTO.SI.CONJUNTO, etc… – Filtros avanzados, – Formatos condicionales avanzados, – Tablas dinámicas (Cálculos comparativosCampos y Elementos calculadosConsolidar, de Escenarios, de Relaciones), – MS-QUERY,  – Gráficas (con Dos  ejes, de Cascada, de Burbujas, inicios de Power Maps,…).

Temario detallado por lección

Contenido en plataforma

Lección 1

Formulación de grandes modelos en Excel.
———-

Aplicación sobre un modelo administrativo.
  • Variables de una fila o columna.
  • Resultados de operar entre rangos.
  • Operaciones entre rangos y constantes.

Más sobre la formulación con nombres.

  • La ventana PEGAR NOMBRE.
  • El comando CREAR DESDE LA SELECCIÓN.
  • Uso del comando APLICAR NOMBRES.

Lección 2

fx SIY y O y su relación con la fx BUSCARV.
———-

Diagramas de FLUJO DE INFORMACIÓN:
  • Anidación de funciones fx SI.
  • Anidación de pruebas lógicas Y y O.

Análisis con TABLAS DE LA VERDAD.

  •  Dos condiciones con dos salidas.
  • Dos condiciones con tres salidas.
  •  Diagrama para muchas condiciones.
  •  fx BUSCARV para muchas condiciones.

Lección 3

fx lógicas SI, Y, O y los Filtros Avanzados.
———-

Análisis de pruebas lógicas complejas:
  • Estructura de múltiples pruebas lógicas.
  • El SI anidando vs BUSCARV.
  • Y entre columnas y O entre filas.
  • Conectar fx O, en grupos de fx Y.

Filtros avanzados:

  • Rangos de criterios y Errores.
  • Copiar resultados en otros rangos.

Lección 4

Funciones «.SI» y «.SI.CONJUNTO«.

———-

Planteamiento de las fx .SI.CONJUNTO.

  • El orden de sus argumentos.
  • Identificación de condiciones.
  • Argumentos de las .SI.CONJUNTO en las fx CONTAR, MAX, MIN y SUMA.
  • Funciones .SI.CONJUNTO vs .BUSCARV, vs las TABLAS DINÁMICAS.
  • Formulando en TABLAS DE EXCEL.

Lección 5

Temas múltiples que complementan.
———-

Fórmulas matriciales avanzadas.
  • Formatos condicionales complejos.
  • Búsqueda 2D con BUSCARV COINCIDIR.
  • Buscar por rangos de valores.
  • VALIDACIÓN de celdas con listas variables.
  • Referenciación indirecta con fx INDIRECTO.
  • Análisis avanzado con Tabla de resultados.
  • Solución de Máximos y Mínimos con Solver.

Lección 6

Más sobre las TABLAS DINÁMICAS TDs.
———-

  • Ordenar Filtrar informes de TDs.
  • Ordenar porPredefinidoColumna de resultados LISTAS PERSONALIZADAS.
  • Filtrar: filtros de etiquetaComodines ? *, por Totales de filas y columnas.
  • Otros temas: Memoria caché, SEGMENTACIÓN y ESCALAS comunes para varias TDSproteger sin bloquear Paneles de segmentación.

Lección 7

Error común en TDs y el Power Query.
———-

Estructura de datos que conlleva a errores en TABLAS DINÁMICAS (TDs).
  • TDs con estructuras inadecuadas.
  • Valores en áreas de Filas y/o Cols.
  • Manejo de los elementos vacíos.
  • Operaciones inesperadas.
  • Erros de ELEMENTOS CALCULADOS.
  • Transformar estructuras con Power Query.

Lección 8

Tablas Dinámicas con cálculos comparativos.
———-

Cálculos comparativosParticipacionesAcumuladosDiferencias periódicas, …
  • Formulas comparativas externas a las DTs.
  • Análisis con valores y porcentajes.
  • Técnicas aplicadas: Cálculos predefinidos,
  • Distribución y Edición detallada en el área de VALORES. Campos y Elementos base.
  • Errores en los Cálculos comparativos.

Lección 9

Elementos calculados en Tablas Dinámicas.
———-

Análisis de las diferencias entre periodos.
  • Sin acumular y acumulado.
  • Un segundo cálculo comparativo.
  • ¿Por qué un CÁLCULO COMPARATIVO para los meses y un ELEM CALC para los años?
 Cálculos especiales dentro de las TDs.
  • Los TOTALES en los ELEMENTOS CALCULADOS.
  • Ocultarmostrar y agrupar ELEMENTOS.

Lección 10

Estructuras para Tablas Dinámicas Comparativas.
———-

  • Errores al formular datos comparativos.
  • Operaciones entre una estructura cruzada y una TABLA DINÁMICA.
 Estructuras que comparan Presupuestos vs. Reales.
  • Separando Columnas de valores.
  • Agregando una Columna descriptiva.
  • Consolidación de datos a comparar.
  • Geometría de las TDs y de sus Gráficas.

Lección 11

Campos y Elementos Calculados en TDs.
———-

Formular en las TABLAS DINÁMICAS TDs vs. formular en las TABLAS de datos.
  • Análisis previo de un caso de presupuesto.
  • Resultados sin acumular y acumulados.
  • Distribución de datos en las GRÁFICAS DINÁMICAS y en las TABLAS DINÁMICAS.
  • Estructuras para el análisis comparativo.
  • Resultados únicos y agrupados en las TDs.

Lección 12

Excel como Sistema de Información S.I.
———-

Aplicación práctica al manejo de Inventarios.
  • Tabla Principal y tablas Auxiliares.
  • Campos identificadores ID e id.
  • Modelo Entidad Relación ER.
 Otras maneras de relacionar tablas de Excel.
  • El comando RELACIONES.
  • El complemento Microsoft Query.
  • Ventajas y limitantes de relacionar tablas.

Lección 13

Consolidar DATOS en TABLAS DINÁMICAS TDs.
———-

Técnicas para la Consolidación de datos:
  • Plantillas para la recolección de datos.
  • Encabezados en Rangos de Consolidación.
  • El comando CONSOLIDAR vs. las TDs.
  • Agrupación de múltiples resultados.
Otras técnicas estudiadas:
  • La formulación 3D vs fórmulas entre hojas.
  • El manejo de nombres en las TABLAS.

Lección 14

Análisis de las TDs. de los ESCENARIOS de Excel.
———-

Creación de ESCENARIOS en Excel.
  • Visualización datos de entrada y salida.
  • Optimizacióncuidados y otros aspectos.
  • Infome de RESUMEN y sus limitantes.
 Informe de TABLA DINÁMICA.
  • IMPORTAR los escenarios de otro archivo.
  • Un solo escenario con muchas variables vs. una variable que se agrupa y combina

Lección 15

Gráficas con dos escalas de valores.
———-

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.
  • Formulación gráfica Exponencial.
  • Análisis de las gráficas de Dispersión.
  •  Análisis de las Escalas Logarítmicas.
  • Partes de una gráficas de cotizaciones.

Lección 16

Mas gráficas para la Gestión Administrativa.
———-

Gráficas de BURBUJAS y de DISPERSIÓN.
  • Una dimensión más una Serie.
  • Gráfica de CASCADA aplicada a un Balance.
  • Configuración de columnas FLOTANTES y de PARCIALES de valores formulados.
  • Las GRÁFICAS DE MAPAS y su configuración.
  • Representación de Valores y Propiedades.
  • Acerca del complemento POWER MAP.