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

Explotación de las fuentes de información para la toma de decisiones. Desarrollo y técnicas aplicadas con Excel Avanzado

En un entorno tan complejo como el actual en términos económicos, es necesario que las empresas cuenten con una información adecuadamente filtrada y depurada para que sea útil en el proceso de toma de decisiones.
Pretendemos dar a conocer el potencial que presenta las hojas de calculo como instrumento para la explotación de la información disponible en las empresas orientada a la toma de decisiones y análisis de su situación que permita afrontar los difíciles retos que el marco de coyuntura actual nos presenta.
En este sentido, las hojas de cálculo es un instrumento que permite un análisis avanzado de los datos, no solo en términos de poder realizar complicados cálculos, análisis estadístico sino especialmente generar tablas de resultados como base para la toma de decisiones. Para alcanzar el objetivo planteado exponemos una serie de apartados que sintetizamos a continuación:

Fundamentos

 Parte I: Introducción General a las Hojas de Cálculo, especial referencia al Excel. - Casos propuestos relacionados
   

Generalidades - Lectura Relacionada Control de Gestión vs Sistemas de Gestión ¿Por qué y para qué?

  • Los costes ocultos en la utilización de las hojas de calculo. (Presentación)
  • Cuatro reglas básicas para trabajar eficientemente con las hojas de calculo: 80/20
  • Lo más consultado sobre las hojas de cáculo, sobre Excel

Técnicas Básicas 0: Bases del Excel - Ejemplo para practicar

  • Tipos de referencias en Excel (Relativas, absolutas y Mixtas)
  • Fijar filas y columnas
  • Operadores básicos en Excel: Aritméticos, de comparacion, texto y de referencia.
  • Foramtos de celdas: numeros y ajustes y combinacion
  • Diferencia entre rango y tabla., convertir un rango en una tabla.

Técnicas Básicas I: Trucos, opciones y personalizacion con Excel (Presentación)

  • Administración de nombres en Excel Simple, introduccion a los nombres dinámicos
  • Trabajando con formatos. Numero en miles y millones. Formato Mascara
  • Formatos personalizados, directrices generales para números,, días, meses, años, monedas, porcentajes, horas, minutos, etc..
  • Aplicando símbolos a nuestras celdas
  • Atajos de teclados útiles en Excel. Aumentando la productividad.
  • Cambiar el desplazamiento del cursor en Excel al pulsar enter. Desplazamiento Horizontal o Vertical
  • Operar con la misma celda de varias hojas de cálculo en el mismo libro
  • Otras utilidades: Cámara de Excel, Personalizando la barra de accesos directos, vista personalizada en Excel, etc..
  • Proteger un archivo Excel con Contraseña
  • ¿Cómo ver todas las fórmulas de la hoja de cálculo? CTRL+` 
  • Ocultar las formulas
  • Esconder datos de una celda completamente

Técnicas Básicas II: Trucos, opciones y personalizacion con Excel (Presentación)

  • Preparando la apertura de nuestro libro de trabajo personalizado
  • Configuración de una hoja de incio o presentación
  • Ocultar hojas para que no puedan ser mostradas
  • Proteger y desproteger varias hojas a la vez (todo el libro) con una sola macro para evitar cambios no deseados
  • Introducción a los formularios, conceptos y tipos de userforms
  • Creando un menu avanzado para nuestros desarrollos Excel
  • Proteger nuestro codigo VBA

Técnicas Básicas III: Trucos, opciones y personalizacion con Excel (Presentación)

  • Conectando datos y gráficos de Word con Excel, vinculos absolutos y relativos.
  • Vínculos absolutos y relativos, conectando datos de Excel con Word (gráficos y tablas)
  • Rutas relativas, tipo por defecto en los documentos Office
  • Vinculo a una celda de otra hoja de cálculo
  • Marcas, marcadores e hipervínculos en Word y cuestiones relacionadas
  • Creando marcas de índices en un documento Word para palabras clave., etc.
  • Creación de marcadores en Word y Excel. Concepto de marcador Hipervínculos en celdas.
  • Convertir de Word a PDF y conservar los marcadores e hipervínculos
  • Navegación a través de los marcadores de un PDF en Web…

Técnicas Básicas IV: Trucos, opciones y personalizacion con Excel (Presentación)

  • Las fórmulas matriciales en Excel
  • Ejemplos de empleo de formulas matriciales..
 Parte II: Funciones Excel. - Casos propuestos relacionados
   

Funciones en Excel (I). Funciones de Búsqueda y Referencias - (Presentación)

  • ¿Para qué las funciones de búsqueda o referencias?
  • Sintaxis básicas
  • BUSCARV-BUSCARH / CONSULTAV-CONSULTAH
  • INDICE - COINCIDIR
  • Buscar por dos criterios en Excel: Fórmula matricial combinando índice y coincidir. Obtener el precio según marca y modelo
  • Buscar a la izquierda con la combinación indice y coincidir

Funciones en Excel (II). Funciones de Texto (Presentación)

  • ¿Para qué las funciones de texto?
  • Sintaxis básicas. Izquierda, Derecha, Extrae, Valor, Espacios y Largo.
  • Combinando funciones de texto Función Remplazar y combinado con Hallar.
  • Función Valor combinado con Remplazar y con Hallar Preparando la apertura de nuestro libro de trabajo personalizado

Funciones en Excel (III). Funciones Fecha y Hora (Presentación)

  • ¿Para qué las funciones fecha y hora?
  • El especial tratamiento de las fechas en Excel, formato número de serie
  • El especial tratamiento de los tiempos (horas) en Excel
  • Principales funciones fechas horas.Función SIFECHA ( ) - NSHORA ( ) -HORANUMERO ( )
  • Formato fecha y hora
  • Extraer la hora, minuto y segundo de un tiempo dado
  • Operaciones básicas con horas

Funciones en Excel (IV). Funciones Matemáticas y Trigonométricas

  • Función Subtotal

Funciones en Excel (V). Funciones Estadísticas

  • Función k.esimo.mayor y k.esimo.menor

 

 Parte III: Las Hojas de Calculo & Bases de Datos y Tablas Dinámicass - Casos propuestos relacionados
   

Excel Base de Datos (I). Consideraciones básicas relacionadas con con el uso de Excel como base de datos. Funciones relacionadas

  • Manipulación de datos con Excel. Las diez formas de limpiar los datos. Convertir rango en tabla.Filtros simples y avanzados
  • Errores y depuración relacionados con la importación de datos. Quitar espacios en blanco en celdas, eliminar saltos de linea, forzar formato númerico multiplicando por uno, etc..
  • Repasando las principales funciones relacionadas con la BD:
    • Funciones texto:Encontrar, extraer, izquierda, derecha, concatenar, esblanco, etc.
    • Funciones Fecha: DIA.SEM, Ahora, Año, Redonderar.MAS, etc.
    • Funciones de buscar: CONSULTAH, CONSULTAV, INDICE, COINCIDIR, etc.
  • Ejemplos tipicos de uso de BD en Excel. Combinar correspondencia, fichero de facturación.

Excel Base de Datos (II). Consideraciones básicas relacionadas con con el uso de Excel como base de datos. Funciones relacionadas

  • Definición de rangos dinámicos en Excel (I). La función DESREF ( )
  • La función DESREF anidada con otras funciones
  • Rangos dinámicos con Excel (II). Desref anidada con Función Contara.
  • La Función CONTARA. CONTARA. RANGO-CONTARA.BLANCO-CONTARA.SI
  • Rangos dinámicos con Excel (III). DESREF anidada con INDIRECTO y CONTARA. Listas desplegables dependientes.
  • Importación de datos y conexiones externas (I). Importación
  • Importación de datos y conexiones externas (II). Vinculación

Las Tablas Dinámicas (I).Fundamentos, funciones esenciales y otros aspectos relacionados con las tablas dinámicas.

  • Aspectos generales de las Tablas Dinámicas
  • Opciones de configuración de una TD."Herramientas de TD": Ficha Opciones y Diseño
  • Funciones especiales asociadas a las TD. Importardatosdinamico
  • Problemas con las tablas dinámicas. Limpiar histórico de la lista desplegable.

Las Tablas Dinámicas (II).Fundamentos, funciones esenciales y otros aspectos relacionados con las tablas dinámicas. Caso propuesto

  • Tablas Dinámicas y segmentación de datos
  • Eliminar en "en blanco" en TD
  • Convertir Tabla de Referencias Cruzadas en Tabla Vertical
  • Campos calculados en Tablas Dinámicas
  • Elementos calculados en Tablas Dinámicas
  • Contar valores únicos
  • Agrupar o desagrupar valores en informe de TD
  • Distintos valores en la TD (% del total de fila y/o columnas, etc..)
  • Mostrar valores como: Sin cálculo, % del total de filas principales, clasificación de menor a mayor y de mayor a menor, etc...

Las Tablas Dinámicas (III).La Ley de Pareto 80/20 y su aplicación con tablas dinámicas. Estudios de Intervalos. Caso propuesto

  • Concepto de ley de Pareto 80/20 UN POCO REPRESENTA MUCHO
  • Uso de K.ESIMO.MAYOR, COINCIDIR, INDICE en una misma fórmula de Excel
  • Alternativa a la función BuscarV: INDICE+COINCIDIR
  • Construccion de un diagrama de Pareto con Tablas Dinámicas
  • Fórmula de Sturges para establecer intervalos de valores. Distribución de frecuencias mediante la Regla de Sturges

Las Tablas Dinámicas (IV).Notas sobre Gráficos Dinámicos

  • Seleccionando las series de datos a representar en el gráfico dinámico
  • Paneles de segmentación vinculadas con varias tablas y gráficos dinámicos

 

 

 Parte IV: Modelos y Diseño de Dashboard - Cuadro de Mandos con Excel - Casos propuestos relacionados
   

Formatos Tablas y Gráficos (I)

  • Aplicando formato simple a las tablas, estilos de tablas
  • Aplicando formato condicional a las tablas
    • Reglas de celda
    • Reglas superiores e inferiores
    • Formatos condicionales aplicados a barra de datos, escala de colores y conjunto de iconos.
  • Ejemplos.

Formatos Tablas y Gráficos (II)

  • Las 12 partes mas importantes de un gráfico
  • ¿Qué tipo de gráfico utilizar en nuestros informes?
  • Consejos sobre el diseño de gráficos
  • Los minigráficos.
  • Los gráficos de jerarquía en Excel 2016

Gráficos Personalizados (I)

  • Coordinar el valor máximo del eje vertical en varios gráficos de Excel sin macros y con macros

Modelos y ejemplos de Dashboard - CMI con Excel (I)

  • Introducción a Excel como herramienta para la elaboración de Cuadros de Mando
  • Un caso y consejo sobre los gráficos en Excel
  • Recomendaciones generales en el diseño de un Dashboard
    • Como empezar, planificando
    • Los cuatro principios: Simplicidad, Uso adecuado del espacion en la pagina, intuitivo, interactivo y navegable
  • Ejemplos de técnicas Dashboard en Excel
    • Modelos de Informes y Gráficos Simples
    • Modelos de Informes y Gráficos Dinámicos

Modelos y ejemplos de Dashboard - CMI con Excel (II) 2016

  • Dashboard combinando Tablas Dinámicas con formatos condicionales
  • Ejemplos según áreas o departamentos
    • Informe Reporting de Ventas
    • Informe del Área Comercial, control de vendedores clientes
    • Indicadores Comerciales y Financieros
  • Sistemas de alertas o alarmas.
  • Mapas espaciales....

Power BI: Excel como instrumento de Business Intelligence. Transformar datos en conocimientos (Borrador)

  • Concepto de Business Intelligence
  • Power Query, conexión a fuentes de datos. Buscando y organizando los datos
    • Esquema general de trabajo
    • Lenguaje M
    • Principales razones para usar Power Query
  • Power Pivot, creando nuestro modelo de datos
    • Diferencias entre Power Pivot vs Access / Excel / Tablas Dinámicas
    • Esquema general de funcionamiento
    • Cálculos en Power Pivot (DAX)
  • Power View, creando informes
  • Power Map

Ayuda a la selección de un esquema de color para nuestros diseños de hoja de calculo

  • Excel y Cuadro de Mando (Dashboard): Ayuda a la selección y combinación de colores, paleta de colores para nuestros cuadros de mando. Fuente original: Herramienta y artículo en Bacon Bits
    A través de Bacon Bits disponemos de una hoja de cálculo  que nos permite visualizar diversos esquemas de color para seleccionar y aplicar a nuestros diseños de cuadro de mando y otros trabajos con Excel.
    Una vez elegido la combinación deseada solo tenemos que prestar atención a los valores RGB de las celdas M2:M6 para ser aplicados.
  • Descarga Hoja de Calculo
 Parte V: Excel y VBA. Introducción a la programación de las hojas de calculo Excel - Casos propuestos relacionados
   

Formularios y controles (I)

  • Introducción a la programación de controles
  • Simulador presupuestario.xlsm: trabajando con controles ActiveX, Microsoft Office Web Components, ChartSpace, Spreadsheet, Spin, Conectar y bloquear texbox, enlazar textbox, etc..
  • Controles ActiveX, Spreadsheet y ChartSpace

Excel VBA I

  • Activar la ficha del programador
  • Aspectos conceptuales previos
  • Entender los principales objetos y miembros. Métodos y propiedades
  • Uso de la ayda
  • El objeto WorkBook (Libro de Excel). Caracteristicas y principales miembros (métodos y propiedades)
  • El objeto WorkSheet (Hoja o pestaña). Caracteristicas y principales miembros (métodos y propiedades)
  • El objeto Range (Rango). Caracteristicas y principales miembros (métodos y propiedades)
  • Resumen. Puntos claves

Excel VBA II

  • Fundamentos VBA
  • Introducción a las Macros y Eventos
  • Ejecutar una macro a traves de un botón o vinculada a un evento (recoibir el enfoque, hacer doble click, etc) sobre una celda o rango de celdas de una hoja
  • Lo Módulos

Excel VBA III

  • Protección y desprotección de libros. Propuesta mas reciente para desproteger un Excel
  • Bloquear la ejecución de una macro con clave o acceso a un apartado con clave.
  • Diseño básico de la estructura de una aplicación Excel.
  • Vincular tablas y gráficos de Excel en Word.
  • Diseñar un sistema de ayuda para nuestra aplicación.
  • Una cinta de opciones personalizada. Menú Ribbonx.
  • Trabajando con formularios y componentes Web.
  • Otras cuestiones.

Excel VBA IV

  • Excel como herramienta de base de datos
  • Conceptos y tipos de Userform (Formularios)
  • Código, modulos y procedimientos
  • Formularios básicos para interactuar con bases de datos en Excel
  • Trabajando con formularios y componentes Web
  • Controles ActiveX, Spreadsheet y Chartspace

Excel VBA V Apuntes Programación Excel VBA. PARTE VI: Código de interés (I). Generalidades

  • Código VBA, casos y ejemplos simples. Condiciones con IF...THEN...ELSE
  • Acciones con rangos de Excel, código VBA. Seleccionar rangos en Excel con VBA. Una celda, una columna o fila, rangos distintos, etc. Borrar e introducir contenido, etc
  • Acciones con una hoja de Excel, código VBA. Ocultar/Mostrar hojas. Protección y desprotección de hojas. Varias acciones con las hojas, Seleccionar una hoja, cambiar el zoom. Imprimir la hoja seleccionada, ocultar la cuadricula, ocultar la cuadrícula de la hoja activa, ocultar encabezamiento de filas y columnas, etc
  • Acciones con un libro, código VBA. Cierra el libro que está activo en ese momento. Abrir libro, Guardar libro activo. Acciones con la aplicación Excel, código VBA. Modificar la apariencia de Excel y salir de la aplicación. Evitar que la pantalla parpadee. Ocultar la barra de estado y la de formulas. Pasar a pantalla completa. Maximizar Excel. Ocultar pestañas de las hojas. Salir de Excel con VBA
  • Cómo automatizar una acción al abrir Excel: ir a una hoja concreta, introducir fecha actual en una celda y pasar a modo pantalla completa
  • Automatizar una tarea cuando una celda cambia

Excel VBA VI Apuntes Programación Excel VBA . PARTE VI: Código de interés (II). Trabajando con controles especiales Hoja de calculo relacionada

  • El control “barra de desplazamiento”, ScrollBar, SpinButton. Presentacion y caracteristicas generales. Propiedades principales y configuración, establecer el valor máximo y mínimo vinculado a una celda,etc
  • El control "barra de desplazamiento" Listas desplegables en Excel. Concepto y tipos. Listas desplegables generadas mediante el menú contextual. Listas desplegables personalizadas mediante "Validación de datos"
  • Listas desplegables, control "Cuadro Combinado"
  • Creación de listas desplegables múltiples

Excel VBA VII Apuntes Programación Excel VBA . PARTE VII: Código de interés (II). Trabajando con controles especiales

  • Método SendKeys

Excel VBA VIII Apuntes Programación Excel VBA . PARTE VIII: La función MsgBox. Trabajando con Userform en Excel Conceptos generales, una introducción a la programación

  • La función MsgBox y principales argumentos
  • Conceptos y tipos de Userforms
  • Controles y Propiedades en los Formularios
  • Ejemplo de Programación de Userforms

 Parte VI: Power BI Power Pivot. -
   

Videos tutoriales relacionados, recomendado su consulta: Nociones de Power Pivot - Autor: Excel Free Blog 18 videos explicativos

Power BI: Excel como instrumento de Business Intelligence. Transformar datos en conocimientos (Borrador)

  • Concepto de Business Intelligence
  • Power Query, conexión a fuentes de datos. Buscando y organizando los datos
    • Esquema general de trabajo
    • Lenguaje M
    • Principales razones para usar Power Query
  • Power Pivot, creando nuestro modelo de datos
    • Diferencias entre Power Pivot vs Access / Excel / Tablas Dinámicas
    • Esquema general de funcionamiento
    • Cálculos en Power Pivot (DAX)
  • Power View, creando informes
  • Power Map
 Parte VII: Mis referentes, mis maestros
   

Quiero mostrar a continuación mi más sincero reconocimiento a los diferentes autores de los que he aprendido y sigo aprendiendo, gracias por compartir su conocimiento, esfuerzo y trabajo.