La Hoja de Cálculo como herramienta para la toma de decisiones: Elaboración de Cuadros de Mando, Dashboard y KPI's

Casos Propuestos (Mis Casos)

Parte I: Introducción General a las Hojas de Cálculo, especial referencia al Excel
 Parte II: Funciones en Excel
 Parte III: Las Hojas de Calculo & Bases de Datos y Tablas Dinamicas
 Parte IV: Modelos y Diseño de Dashboard - Cuadro de Mando con Excel
 Parte V: Excel y VBA. Introducción a la programación de las hojas de calculo Excel
 Parte VI: Casos empresariales - Experiencias Adaptadas
 Parte VII: Otras propuestas de interes


Parte I: Introduccion General a las Hojas de Cálculo, especial referencia al Excel

Ficheros  
mismenusjggomez.xlsm Ejemplo de formulario de bienvenida a nuestra hoja de calculo personalizada

Parte II: Funciones Excel

General  

Casos Básicos Relacionados con el Manejo de las Principales Tipos de funciones en Excel

Descripción y Comentarios

Funciones en Excel (I). Funciones de Búsqueda y Referencias (Fichero Excel) Casos Planteados

  • BUSCARV:
    • Renta y tasa de impuestos
    • ID del producto y precio
    • Operarios Vtas
  • BUSCARH:
    • Fechabusqueda
    • Clasificación de clientes
  • INDICE
    • Distancia entre poblaciones
    • Sucursales y ventas mensuales
  • COINCIDIR
    • Tickets del día
    • Productos con ventas más altas
    • Guaguas, cálculo del tiempo de espera
  • Matriz combinada INDICE COINCIDIR
      • Buscar con doble criterio
      • Obtener el precio según marca y modelo
      • Ventas y comisiones trimestrales según vendedor

Funciones en Excel (II). Funciones de Texto (Fichero Excel) Casos Planteados

  • Aplicando las funciones básicas de texto como Izquierda, Derecha, Extrae, Espacios, Largo, etc.
    • Caso Auditoria interna
  • Combinando funciones de texto
    • Stock Familia Informática. Izquierda, Derecha, Extrae, Valor, Espacios y Largo
    • Direcciones de clientes. Combinando Encontrar, Izquierda y Derecha
    • IDprecios. Combinando Encontrar, Izquierda, Valor, Extraer y Longitud.
    • PNBtrimestral. Remplazando puntos de texto por comas de decimales. Izquierda, Espacio, Extrae y Combinando Valor, Remplazar, y Hallar.
    • Moda. Encontrar, Izquierda, Extrae y combinado Extrae con Largo
    • Nombres invertidos. Encontrar, Izquierda, Extraer, combinado Derecho con Largo y concatenar
  • Caso especial ventas de la provincia. Extraer datos usando el asistente para convertir texto en columnas

Funciones en Excel (III). Funciones Fecha y Hora (Fichero Excel) Casos Planteados

  • Casos planteados generales. Ejercicio Básico
  • Funciones Fecha
    • Antigüedad completa años, meses y días
    • Edad de nuestros empleados
    • Preguntas cortas, buscar 20 días laborables, cuantos días hay entre dos fechas, etc
    • Días de demanda de oro superior a un 15%
    • Tiempos de Amortización Previsto para una flota de vehículos
  • Funciones Hora
    • Horas trabajadas por empleado a la semana
    • Sumar a la hora actual 10 horas
    • Calculo de tiempo promedio de montaje

Funciones en Excel (IV). Funciones Condicionales (Fichero Excel) Casos Planteados

Parte III: Las Hojas de Calculo & Bases de Datos y Tablas Dinámicas

Trabajando como Base de Datos

Ficheros  
BDjggomez1.xlsm Ejemplo Tabla de Gastos Contables
Funcion Indirecto.xls Ejemplo sencillo de uso de la funcion indirecto con ventas por mes
Función DESREF Ejemplos sencillos de uso de la funcion DESREF
Listas desplegables dependientes.xlsm Ejemplo del caso de controles o listas dependientes

Introducción a las Tablas Dinámicas

Documento  

Caso Control y Análsis de los Gastos de Nuestro Móvil

Descripción y Comentarios Convertir Rango en tabla, elaboración de nuestra primera tabla dinámica
Ficheros
Moviles Original.xlsx y Moviles Solución.xlsx Datos y solución propuesta del ejercicio control y análisis de nuestro gasto de móvil

 

Ficheros-Casos  
Ventas Sucursales.xlsm Datos y tabla dinámica básica fundamentada en el análisis de ventas por sucursales
Bodegas Españolas Uso de la Funcion BuscarV para contruir tabla base para la TD y elaboración de la tabla dinámica Datos y Solución
Transporte Sanitario Isla Salud Tablas Dinámicas básicas y uso de la función BUSCARV, BUSCARH y función SI. Datos y Solución
Casas Rurales Tablas Dinámicas básicas y uso de la función BUSCARV y BUSCARH y uso de las funciones de texto IZQUIERDA; ENCONTRAR; VALOR; EXTRAE Datos y Solución

 

Documento  

Recaudación delegaciones

Descripción y Comentarios

Nuestro programa de gestión de TPV nos ofrece la información acumulada de la facturación diaria y por turnos de las distintas cajas registradoras localizadas en las diferentes ciudades en la que contamos con delegaciones de nuestra cadena de cafeterías. Queremos contar con diferentes TD que nos permita evaluar las principales variables estrategicas de la empresa asi como el diseño de un sencillo cuadro de mando intergral.
Uso de las funciones: DESREF, IMPORTARDATOSDINAMICOS, Segmentacion de datos, SUMAR.SI, Aplicacion de fórmulas condicionales

Ficheros
Recaudaciones solucion Datos y solución propuesta del ejercicio recaudacion delegaciones

Parte IV: Modelos y Diseño de Dashboard - Cuadro de Mando con Excel

Ficheros  
Formato condicional en Excel. xlsx Ejemplos simples de formatos condicionales en Excel
Simbolos.xlsx Relación de caracteres a considerar en el diseño de dashboard
   

 

Documento  

Caso Flores Cortadas

Descripción y Comentarios

Creación de gráficos dinámicos basados en rangos dinámicos (flores cortadas 1 y flores cortados 2).

  • Creación de un rango dinámico basado en tablas dinámicas
  • Trabajando con el rango dinámico. Combinando con la función Coincidir
  • Solución, creación del grafico dinámico

Importación de datos dinámicos basado en dos o más rangos dinámicos (flores cortadas 3 y flores cortados 4)

  • Importación de datos dinámicos basado en dos o más rangos dinámicos
  • Creación de un rango dinámico basado en filas o cabeceras de tabla dinámica
  • DESREF y CONTARA. Empleo de la función importardatosdinamicos
Ficheros
Bd Partes1.accdb Base de datos donde se recoge la información e los partes de trabajo del invernadero. Conexión o vinculación a la base de datos.

Flores Cortadas 1.xlsm

Flores Cortadas 2.xlsm

Flores Cortadas 3.xlsm

Flores Cortadas 4.xlsm

Soluciones a los ejercicios propuestos en el caso Flores Cortadas

 

Documento  

Caso CMjggomez I

Descripción y Comentarios

Nuestro primer y segundo Dashboard o Cuadro de Mando.

  • Creación de los Rangos Dinámicos. Definición de las variables determinantes para nuestro Dashborad.
  • Aplicando la función importar datos dinámicos
  • Empleo del formato condicional conjunto de iconos
  • Nuestro segundo Dashboard – Gráficos dinámicos
  • Configuración de los gráficos dinámicos superpuestos
Ficheros

CMjggomez1.xlsm
CMjggomez2.xlsx

Fuente de datos y solución al caso propuesto

 

Documento  

Caso CMjggomez II

Descripción y Comentarios

Gráfico Dinámico con comentarios.

  • Creación de los Rangos Dinámicos. Definición de las variables determinantes para nuestro Dashborad.
  • Diseño y configuración del Control ActiveX –ScrollBar
Ficheros

CMjggomez3.xlsm

Fuente de datos y solución al caso propuesto

 

Documento  

Caso CMjggomez III

Descripción y Comentarios

Gráficos Dinámicos basados en tablas y rangos dinámicos.

  • Creación de los Rangos Dinámicos
  • Problemática. Creación de un rango dinámico con columnas o filas en blanco
  • Creación de macro a través del grabador de macros
  • Ejecución de la macro cada vez que cambie o se actualice la tabla dinámica. Evento WorkSheet Change
Ficheros

CMjggomez4.xlsm

Fuente de datos y solución al caso propuesto

 

Documento  

Caso CMjggomez IV

Descripción y Comentarios

Gráficos Dinámicos intercambiables

  • Definición de los rangos dinámicos para los gráficos
  • Creación de los gráficos dinámicos necesarios
  • Definición del rango que ocupan los gráficos. Referencia a la ubicación de los gráficos
  • Elaboración de la lista de gráficos
  • Definición del área donde mostrar los gráficos dinámicos intercambiables
  • Creación del control Lista Desplegable y uso de la función ELEGIR

Análisis de sensibilidad

  • Creación de una réplica del cuadro de mando
  • Diseño del Área de Simulación. Controles ActiveX
  • Diseño del Área de Análisis Grafico comparativo de la simulación
  • Macro restablecer valores
Ficheros

CMjggomez5.xlsm
CMjggomez6.xlsm

Fuente de datos y solución al caso propuesto

Documento  

Caso CMjggomez V

Descripción y Comentarios

Gráficos con movimiento, aspectos generales

  • Presentación del caso CMjggomez7: Gráficos de Burbujas en Movimiento
    • Preparando los datos.
    • Crear cuadro resume.
    • Elaboración del gráfico de burbujas basado en el cuadro resumen.
    • Insertar barra de desplazamiento y configurándola.
    • Opcional: Insertar botón de reproducción y configuración

Resumen de la técnica y funciones empleadas en el caso CMjggomez7

  • Cuestiones generales
    • Creación de nombre a la celda y a las tablas de datos
    • Crear tabla de valores únicos partiendo de una existente.
  • Uso de funciones
    • CONCATENAR, BUSCARV o CONSULTAV
    • Combinado funciones: BUSCARV y CONCATENAR
  • Otras cuestiones
    • Insertar gráfico de burbujas
    • Opcional Insertar Botón Comando y programarlo. Código VBA
Ficheros

CMjggomez7.xlsm

Fuente de datos y solución al caso propuesto

Parte V: Excel y VBA. Introducción a la programación de las hojas de calculo Excel

Ficheros  
Simulador presupuestario 1.xlsm Ejemplo de formulario y controles programados para una hoja de calculo

 

Parte VI: Casos Empresariales y Experiencias Adaptadas

Casos Empresariales. Experiencias adaptadas

 

Parte VII: Otras propuestas de interes con Excel

 

Documento  

Georeferenciando datos con Excel.

Descripción y Comentarios

Técnicas para la realización de mapas en Excel con regiones. Georeferenciando datos en un mapa . GIS y Excel.

Los Sistemas de Información Geográfica son aplicaciones orientadas a la gestión de la información geográfica, es decir información georreferenciada que permite trabajar con datos georreferenciados mediante coordenadas espacial o geográfica. No obstante, su manejo requiere de ciertos conocimientos técnicos que en muchas ocasiones escapan del nivel requerido en nuestro trabajo y necesitamos de alguna herramienta o técnica más sencilla que facilite esta representación cartográfica. El objetivo del caso propuesto es mostrar la técnica para representar valores en un mapa personalizado haciendo uso de la hoja calculo Excel. El potencial deriva de la facilidad de adaptar esta metodología a cualquier conjunto de valores y mapas personalizados

Ficheros

Tenerife Comarcas.xlsm

Fuente de datos y solución al caso propuesto

 

Documento  

Velocimetros, gauges y similares con Excel

Descripción y Comentarios

Técnicas para implementar velocímetros, gauges, termómetros y similares en los Cuadro de Mandos diseñados en Excel

Los cuadros de mandos empresariales ganan en representación y claridad con el uso de diferentes elementos como son los velocímetros, gaus, termómetros, tacómetros, etc.
El objetivo del caso propuesto es mostrar cómo implementar este conjunto de objetos en nuestros cuadros de mando con lo que mejoraríamos en muchos casos la representación de los datos y su relevancia.
E exponemos las adaptaciones que hemos realizado de los objetos de velocímetros, gaus, etc. y que tenemos disponibles en el fichero Excel vinculado con el nombre de Velocímetros y Gauges.

Ficheros

Velocimetro y gaufes.xlsm

Fuente de datos y solución al caso propuesto