Inteligencia de negocios e

Indicadores de gestión

Excel Avanzado como herramienta para el

desarrollo de dashboard

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Contenido
Módulo 1 : Los indicadores de Gestión
 Introducción a la Inteligencia de Negocio. Módulo 3: Excel. Transformación de datos en

 Fases del modelo de Inteligencia de Negocio (B.I). información.

 Conceptos básicos de indicadores de gestión o  Extracción datos externos.

KPI’s.  Formatos condicionales

 Clasificación de los indicadores.  Tablas y gráficos dinámicos.

 Características de los indicadores.  Segmentación.

 Importancia de los KPI’s en la toma de decisiones.

 Definición de objetivos, indicadores y metas. Módulo 4: Indicadores y Dashboard.

 Ejemplos prácticos.  Definición de objetivos, KPI’s, Metas.

Módulo 2: Herramientas de Excel  Balanced scorecard

 Creación de tableros.
 Seguridad  Función Cámara.
 Análisis de indicadores.
 Etiquetas de rangos  Gráficos velocímetro.
Lic. Rafael Mosquera
 Fórmulas y funciones.  Construcción banco de datos. Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Módulo 2: Inteligencia de negocios

Concepto de Inteligencia de Negocios

o Business Intelligence (B.I)
Datos
Es una disciplina que integra datos proveniente de diversas
Extracción
fuentes u orígenes, facilitando su almacenamiento, abordaje,
Información
selección y tratamiento de datos históricos, para ser
transformados en información que permitirá apoyar al Transformación

proceso de toma de decisiones. Conocimiento

Análisis
Es un modelo mediante el cual se transforman datos crudos
Experiencias
almacenados diferentes fuentes, en información relevante
para la empresa, para apoyar el proceso de toma decisiones. Resultados

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Inteligencia de negocios

Características de Inteligencia de negocios

 Extrae los datos crudos de diferentes fuentes, independientemente del ambiente de base de datos, para procesarlos.

 Permite generar información a través del procesamiento de los datos, que apoyan al proceso de toma de decisiones

 Consta de una serie de operaciones y tareas especializadas en cada etapa

 Involucra la manipulación de la data a través de consultas OLAP, herramientas de análisis, aplicación de fórmulas,

reportes. Todas estas herramientas aplicando programación por parte de profesionales especializados en diferentes

lenguajes.

 Facilita el control de la gestión operacional de la empresa en cualquier momento

 Permite generar consultas en cualquier área de la empresa, a través de un manejo sencillo por parte del usuario

 B.I requiere fuertes inversiones en hardware, software y personal especializado

 Tiene una fuerte dependencia del personal de T.I para realizar cambios, adaptaciones o inclusiones
Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Indicadores de gestión e Inteligencia de negocios

Esquema general de inteligencia de negocios

CONSULTAS

ALMACENAMIENTO / RECUPERACIÓN TRANSFORMACION

.
B.D B.D
Externas Externas
Extracción . OLAP Dashboard
Data
ETL
.
Data
Reporting
Warehouse Análisis

Data
Marts Convertir datos provenientes de
Toma de
diferentes fuentes de operación, en decisiones
información precisa para la toma de Lic. Rafael Mosquera
decisiones. Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Inteligencia de Negocios

Niveles organizacionales

Nivel Estratégico

Toma de decisiones

Nivel Táctico

Análisis de resultados
Elaboración de escenarios

Nivel Operacional

Operación continua
Lic. Rafael Mosquera
Objetivos operacionales Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Excel en inteligencia de negocios

TRANSFORMACIÓN
ALMACENAMIENTO

EXTRACCIÓN
B.D .
Externas Extracción Datos desde Excel

Fórmulas, funciones, filtros, escenarios,

alarmas, tablas y gráficos dinámicos,
Macros

B.D
Externas .
.
CONSULTAS
Nube Dashboard

Banco de datos.
Hoja de cálculo
.

Función cámara, cortar y copiar, formatos

celdas, segmentación de datos

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Indicadores de gestión y KPI’s

Los indicadores de gestión son métricas que nos permiten evaluar el grado de efectividad de los procesos que
se realizan en cualquier área de la organización.

Permiten medir la eficiencia y eficacia de las operaciones que son realizadas en cualquier departamento de la
empresa. Independientemente del tamaño o actividad económica de ésta.

Pueden haber tantos indicadores como se necesiten para para cada actividad. Lo importante es tener claro lo
que se va a medir.

Los KPI’s (Key Performance Indicator o indicadores claves de desempeño) son indicadores que apuntan a
objetivos estratégicos que tienen impacto en el crecimiento y desarrollo de la organización.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Clasificación de los indicadores

Resultados
Cuantitativos
Impacto
Cualitativos
Proyección
según nivel
según su de
medición intervención

Eficacia según según

Eficiencia dimensión jerarquía
Estratégico
Efectividad
Operacional
Calidad
Satisfacción

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Características

Simplicidad

Adecuación
Validez en el
tiempo

Indicadores
De
Gestión

Medible
Utilidad

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Objetivos

Método SMART

 Específico. (Specific)
 Medible.
 Alcanzable.
 Realista.
QUE QUIERO LOGRAR ?
 Basado en tiempos.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Pasos y para la elaboración de indicadores

Establecer Definir lo que Redactar los Desarrollar

Objetivos a se requiere indicadores la fórmula de
alcanzar del medir cálculo
área a evaluar (KPI’s)

Identificar la
fuente de los
datos
Diseñar los
medios de
salida y
consultas

Asignar una
Establecer los Establecer la
meta al
referentes periodicidad de la
indicador
comparativos medición

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Ejemplo de indicadores de gestión

Objetivo
Mantener los niveles de ingresos por venta total de productos, por encima de un 95% del presupuesto, durante los meses de
Julio a Diciembre 2017.

Indicadores
Frecuencia de
Nombre Descripción Unidad Meta Verde Amarillo Rojo Cálculo Fuente Responsable
medición
Ingresos por venta de Medir el comportamiento de Cantidad de productos Mòdulo de ventas
Entre 80% y
productos en el primer las ventas de productos por Bs. 95% >=95% <80% Trimestral vendidos* Precio unitario - sistema Gerente de ventas
94%
semestre categoría descuentos administrativo
Módulo
Medir el porcentaje de Monto de las facturas
Entre 75% y facturación Analista de
Efectividad en la cobranza facturas cobradas con % 95% >=90% <75% Mensual cobradas / Monto facturas
89% sistema administración
respecto a las emitidas. emitidas
administrativo
Módulo
Evaluar la labor de venta de Monto de las facturas
Cumplimiento de metas por Entre 75% y facturación Gerente de
productos por cada % 90% >=90% <75% Mensual cobradas / Meta de ventas
vendedor 89% sistema administración
vendedor por vendedor
administrativo

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Interpretación de indicadores de gestión

OBJETIVO
Mantener los niveles de ingresos por venta total de productos, por encima de un 95% del presupuesto, durante los meses de Julio
a Diciembre 2016.

Indicador
Ingresos en bolívares por ventas de productos por categoría

VENTAS POR CATEGORIA Ventas por categoría

Bs. Bolivares
140.000,00
Nombre de categoría Ventas por categoría Meta Venta % Variación
120.000,00
Bebidas 102.074,29 120.000,00 85% 100.000,00
Carnes 81.338,06 100.000,00 81% 80.000,00

Condimentos 55.277,56 50.000,00 111% 60.000,00

Jane
Mar
Frutas/Verduras 53.019,98 90.000,00 59% 40.000,00 Mar
Mar
Granos/Cereales 55.948,82 51.000,00 110% 20.000,00 Nan
Lácteos 100.000,00 110.000,00 91% 0,00
Nan
Nan
Pescado/Marisco 65.544,19 60.000,00 109% Mar
Mar
Repostería 72.000,00 100.000,00 72% Mar
Mar
Totales 585.202,90 681.000,00 86% Ventas por categoría Meta Venta
Mar

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Interpretación de indicadores de gestión

OBJETIVO:

Mantener los niveles de ingresos por venta total de productos, por encima de un 95% del presupuesto, durante los meses de Julio
a Diciembre 2015.

Indicador
Cumplimiento de la cuota de ventas por vendedor
VENTAS POR VENDEDOR Monto facturado Vs. Cobrado
250.000,00 Bolívares
Etiquetas de fila Monto Facturación Monto cobrado Meta de venta % Variación 200.000,00
Andrew Fuller 166.537,75 145.000,00 160.000,00 91% 150.000,00
Anne Dodsworth 77.308,07 76.000,00 75.000,00 101% 100.000,00
Janet Leverling 202.812,84 202.000,00 250.000,00 81%
50.000,00
Laura Callahan 126.862,28 125.000,00 125.000,00 100%
0,00
Margaret Peacock 232.890,85 175.000,00 369.200,00 47%
Michael Suyama 73.913,13 69.000,00 85.000,00 81%
Nancy Davolio 192.107,60 150.000,00 265.000,00 57%
Robert King 124.568,23 120.000,00 130.000,00 92%
Steven Buchanan 68.792,28 73.000,00 75.000,00 97% Monto cobrado Monto Facturación
Total general 1.265.793,04 1.135.000,00 1.534.200,00 83%

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Módulo 2

Herramientas Inter-Avanzadas
de Excel

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
21
Fórmulas y Funciones

Fórmulas: son expresiones matemáticas o lógicas creadas por el usuario para realizar cálculos,
entre datos de la misma hoja de cálculo, hojas diferentes e incluso archivos diferentes.

Funciones: Son fórmulas prediseñadas en Excel, que sirven para ser invocada por los usuarios
con la finalidad de realizar cálculos matemáticos, financieros, lógicos, trigonométricos, entre otros

Cuando se van a utilizar funciones, se debe dar los parámetros o argumentos, después de la
palabra clave. Ejemplo: =SUMAR(A4:A20)

Se pueden utilizar funciones dentro de una fórmula o fórmulas dentro de los argumentos de una
función.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Fórmulas y Funciones

Barra de fórmulas

Muestra la fórmula
contenida en la
celda seleccionada.
Es editable.

Lista de funciones

Permite seleccionar
el tipo de función
que se quiere crear
mediante cuadros
de diálogo paso a
paso. Aparece
cuando de escribe
el signo =

Ventana de argumentos. Todas las funciones

generarán esta ventana, solicitando los datos
que necesite la función para ejecutarse.
Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Fórmulas y Funciones

Las fórmulas comienzan con el símbolo “=“,

colocando las celdas a las cuales se va a
hacer referencia, con los respectivos
símbolos aritméticos.

En el caso de las funciones, se comienza igualmente con el símbolo

“=“, y se coloca el nombre de la función con sus respectivos
parámetros. En la mayoría de los casos, los parámetros son
referencias a celdas.
Existen cuadros de diálogos (Wizzards) que van llevando de la mano
para la creación de las funciones y fórmulas.
Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Fórmulas y Funciones

Cuando se hace referencia a una celda desde otra, la sintaxis es como se muestra en el siguiente cuadro:

En la celda D14, se hace referencia al valor Nótese que cuando se copia la fórmula hacia
contenido en la celda B4. abajo, la referencia de las filas se incrementan.

Si se necesita anclar el valor de una celda determinada, se

colocará un símbolo de dólar ($) antes de la fila o columna
que se desee inmovilizar.
En este caso, se ha hecho
una copia, anclando la fila en
4, por medio del símbolo $.
Nótese que tanto la copia por
fila como por columna, la
Si se copia la fórmula hacia los lados, se fila permanece anclada.
incrementa / decrementa el valor de las Lic. Rafael Mosquera
columnas. Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Creación de formulas con etiquetas

La manera más eficiente de manejar fórmulas que hacen referencia a datos ubicados en celdas diferentes, es asignarle una
etiqueta a dicha celda, de tal manera que en la fórmula aparezca el nombre de ésta y no la ubicación en el formato fila-columna.

Por ejemplo:
En la tabla de centros de costos, a cada columna se les va a crear una etiqueta con el nombre de cada mes.

Etiqueta del
rango

Rango de
datos del mes
de enero

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Creación de fórmulas con etiquetas

Etiquetamos también la columna de “CANTIDAD”, y elaboramos la fórmula para calcular el ingreso por ventas (Precio por Cantidad)

Al copiar la fórmula hacia abajo, los nombres de las celdas permanecerán sin cambios.
Estas etiquetas solo se podrán usar en la fórmula haciendo referencia a las celdas que se encuentran dentro del rango que
tiene creado el nombre.
De esta manera se puede eliminar el anclaje de las celdas a través del signo $.
Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Funciones

Hay dos formas de crear funciones en Excel:

 A través del asistente de funciones.

 Manualmente, conociendo la sintaxis de la función.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Ejemplo de Funciones

Función Buscar
Es una de las funciones más utilizadas en Excel ya que permite la búsqueda de valores dentro de una estructura de datos, en una hoja
de cálculo. Su sintaxis es la siguiente:

=Buscar(Valor buscado; Rango de búsqueda; Rango de recuperación)

Donde:
Valor buscado: Es el valor que se va a buscar.
Rango de búsqueda: Es el rango en el cual se va a buscar el valor buscado
Rango de recuperación: Es el rango donde se encuentra el valor que se va a traer.

Existen funciones derivadas de esta, y que son frecuentemente utilizadas:

 BUSCARH( Valor buscado; Rango horizontal de búsqueda; número de filas a desplazarse)
 BUSCARV(Valor buscado; Rango vertical de búsqueda; número de columnas a desplazarse)

La limitación que tiene esta función es que solo permite buscar un valor dentro de una
estructura de datos unidimensional, es decir, buscar un valor en un rango y traer lo que se
encuentre en otro rango (en la misma fila donde se encontró el valor). Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Ejemplo de Funciones

Funciones Índice y Coincidir

La combinación de estas dos funciones se utiliza para buscar un valor dentro de una matriz (estructura de dos dimensiones), cuya
ubicación estará en la intercepción de las coordenadas de fila y columna.
Por ejemplo, encontrar la cantidad de productos vendidos en un mes determinado, donde en las filas estarán los nombres de los
productos y en las columnas el nombre de los meses.
La sintaxis de estas funciones son las siguientes:

=Indice(Matriz; número de fila; número de columna)

=Coincidir(Valor buscado; Rango de búsqueda; 0)

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Ejemplo de Funciones

Funciones Índice y Coincidir

La combinación de estas dos funciones se utiliza para buscar un valor dentro de una matriz (estructura de dos dimensiones), cuya
ubicación estará en la intercepción de las coordenadas de fila y columna.
Por ejemplo, encontrar la cantidad de productos vendidos en un mes determinado, donde en las filas estarán los nombres de los
productos y en las columnas el nombre de los meses.
La sintaxis de estas funciones son las siguientes:

=Indice(Matriz; número de fila; número de columna)

Donde
Matriz: es el rango del banco de datos, sin incluir los encabezados ni los totales.
Número de fila y de columna: son número que indican las coordenadas de fila y de columna

=Coincidir(Valor buscado; Rango de búsqueda; 0)

Donde
Valor buscado: es el valor que se va a buscar en un rango especificado.
Rango de búsqueda: Es el rango donde se buscará el valor.
Lic. Rafael Mosquera
0: Significa que la búsqueda sea exacta Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Funciones Lógicas en Excel

Las funciones lógicas son las que nos permiten tomar decisiones entre dos o más alternativas.

Estas alternativas son tomadas en base a los resultados obtenidos de expresiones, las cuales pueden tener
operaciones matemáticas, lógicas, o ambas.

Ejemplo:

Se quiere promediar tres notas parciales de los alumnos de un curso para colocar en una celda si el
estudiante aprobó o aplazó.

Teóricamente la expresión se construiría asi:

Si el promedio de las tres notas es mayor o igual a 10, entonces escriba la palabra “Aprobado”, si no,
escriba la palabra “Aplazado”.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Funciones Lógicas en Excel
Sintácticamente, la expresión se definiría asi:

Si (A+B+C)/3 > = 10 ENTONCES “APROBADO”

SI NO “APLAZADO”

En Excel, cada una de las notas estará colocada en una celda particular, por lo cual, la expresión en Excel se realizaría de la
siguiente manera:

La Función Comienza Separador entre Expresión si no se

con el signo = las expresiones cumple la condición

=SI((B3+B4+B5)/3 >= 10,”APROBADO”,”APLAZADO”)

Celdas a Operador de Expresión si se

Función SI
evaluar comparación cumple la condición

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Función “SI” combinada con “Y” y “O”

Para simplificar expresiones que combina varios “y” anidados, Excel permite simplificarla en una sola
expresión, dentro de la misma función “SI”, de la siguiente manera:

= SI ( y (E5 > = 10 ; B3 <= 20); “Aprobado”; “Aplazado”)

Esta expresión significa que si la celda E5 es mayor o igual a 10, y la celda B3 es menor que 20,
entonces colocar la palabra “Aprobado”, si no, colocar la palabra “Aplazado”.

Nota: Basta con que una de las dos condiciones no se cumpla, para que no se cumpla la expresión
completa. En el caso que fuera “O” en vez de “Y”, bastaría con que se cumpliera una de las condiciones
para que se cumpla toda la expresión.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Seguridad

Excel permite bloquear hojas de cálculo o celdas, de manera de proteger la información contenida en éstas.
Se puede proteger con o sin password .

Todas las celdas están bloqueadas por default. Cuando se va a proteger una hoja, se deben desbloquear aquellas en las cuales
se va a permitir modificar datos.

Para proteger las celdas se debe seguir los siguientes pasos:

Celdas
1. Dar click al botón derecho en la celda o rango bloqueadas
a desbloquear
2. Seleccionar la opción “formato de celdas…”
3. En la ventana siguiente, seleccionar la opción
“Proteger”
4. Seleccionar la opción “Bloqueada”

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Seguridad

Una vez desbloqueadas las celdas, se debe proteger la hoja, desde el menú “Revisar”, ícono “Desproteger Hoja”

Password.

Tareas que pueden

ser permitidas o
prohibidas a los
usuarios una vez
protegida la hoja

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Seguridad

Excel permite crear adicionalmente dos claves para restringir los permisos a los usuarios para accesar o modificar el archivo

a) Clave de acceso al archivo: Solicita una clave antes de abrir el archivo.

Al momento de grabar el archivo,

seleccionar la opción Grabar como… y
especificar la ubicación y el nombre del
archivo.
Guardar como

Ubicación
Nombre del archivo

Abrir las opciones de “Herramientas”

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Seguridad

Excel permite crear adicionalmente dos claves para restringir los permisos a los usuarios para accesar o modificar el archivo

a) Clave de acceso al archivo: Solicita una clave antes de abrir el archivo.

Seleccionar Opciones Generales

Contraseña de apertura : es la clave que solicitará Excel para permitir abrir el archivo

Contraseña de escritura : es la clave que permite grabar el archivo una vez hecha alguna modificación. Si el usuario no
tiene la clave de escritura, solo podrá grabarlo cambiándole el nombre del archivo, y el original permanecerá sin los
cambios.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Automatización de Tareas

Una macro es una herramienta que permite automatizar procesos que se hacen cotidianamente en forma
manual.
Para el usuario, una macro es una o varias tareas que se ejecutan en forma automática, sin embargo, para Excel
no es más que un conjunto de instrucciones que se ejecutan detrás de una hoja de cálculo para realizar tareas
específicas.
Para crear una macro, se realizan los siguientes pasos:

1. En el menú “Vista”, seleccionar la opción “Macros”

2. Elegir “Grabar” Macro

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Automatización de Tareas

Aparece el siguiente cuadro de diálogo

El usuario debe colocar el nombre de la macro que

desee

Se elije donde se quiere guardar la macro

Se puede asignar una tecla que permita la ejecución

de la macro anteponiendo la tecla CTRL

Al dar click en el botón “Aceptar”, Excel comenzará a grabar cada una de las acciones realizadas
por el usuario, hasta que éste de click en el cuadro “Detener Grabación”.

Cuando se está ejecutando una macro, en el menú “Macro”, aparece “Detener Grabación” en
lugar de “Grabar Macro”, tal como se muestra en el siguiente cuadro.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Automatización de Tareas

Para ver las macros creadas, ejecutarlas manualmente, eliminarlas o modificar el código de programación,
se elige la opción “Ver Macros” del menú Macros.

Se puede asignar la macro a un objeto, imágen o formas, de manera que ésta se ejecute cada
vez que se le haga click en el.

Para esto se siguen los siguientes pasos:

• En el menú “Insertar” elegir el objeto que se quiera incluir para ejecutar la macro.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Automatización de Tareas

• Una vez insertada la imagen, Click en el botón derecho. En el menú contextual, elegir la opción “Asignar Macro”.

•Seleccionar la Macro requerida.

• Dar Aceptar.

Luego de estos pasos, al colocar el cursor encima del objeto, se cambiará el cursor hacia una mano, al dar
click se ejecutará la macro.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Función Cámara

 Es una funcionalidad que tiene Excel con la finalidad de tomar una foto a un rango y convertirlo en una imagen vinculada al
origen.
 Esta función está disponible desde la versión 2007 de Excel.
 Los pasos para su uso son:

 Añadir la función cámara a la cinta de accesos directos.

 Seleccionar el rango que se desee transformar en imagen.
 Seleccionar el botón cámara de la cinta de opciones. El cursor Imagen
cambia su forma a una cruz.
 Se dibuja un cuadro del tamaño que se desea mostrar la
imagen.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Módulo 3

Transformación de datos
en información con Excel

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Acceso a Datos Externos

Excel permite extraer datos almacenados en archivos de base de datos, tales como Access, SQL, WEB,
Dbase, etc.

Es importante destacar que una tabla de base de datos tiene una estructura similar a una hoja de cálculo,
estructurada a través de filas y columnas, llamadas registros y campos respectivamente.

Para extraer datos de una base de datos remota, de otro origen que no sea Access o web, se debe crear
una conexión con la base de datos, especificando el nombre del servidor, usuario y password.

Para efectos del curso, vamos a trabajar con una base de datos de Access.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Acceso a Datos Externos

Comparación entre una tabla de base de Datos y una hoja de Cálculo

Formato del archivo en Dbase Hoja de cálculo Excel.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Acceso a Datos Externos

Las opciones de extracción de datos externos, se encuentran en la barra del menú “Datos”.

Existen dos formas de traer valores de una base datos:

1. Traer todos los campos de una tabla
2. Seleccionar solo los campos necesarios de una o varias tablas.

1. Obtener todos los datos de una tabla.

Seleccionar el icono de Access de la barra de menú.

Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Acceso a Datos Externos

Abierta la base de datos, se selecciona la

tabla que se desea extraer hacia Excel.
El usuario debe seleccionar el
archivo de origen de datos en Se seleccionará una base de
la parte del disco duro local o datos, en este caso se
del servidor. selecciona una bajo Access.

Se especifica la
celda a partir de la
cual se mostrarán
los valores.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Acceso a Datos Externos

En este caso se muestran todos los datos contenido en la tabla

CLIENTES de la base de Datos Neptuno.

La barra de herramientas cambia y aparece el

menú de “Herramientas de tabla”

2. Seleccionar solo los campos necesarios de una o varias tablas.

Si se desea obtener data de diferentes tablas, dentro de la

misma base de datos Access, se debe seleccionar la opción
“De Otras Fuentes”, del menú “Datos”.

Seleccionar la opción “Desde Microsoft Query”

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Acceso a Datos Externos

Este origen se debe configurar a

través de un ODBC creado por
windows

En este caso, el origen de datos a elegir

será MS Access Database. Se selecciona la base de datos.

Tablas y campos

Valores que se van a

mostrar en la consulta

Las tablas contenidas en la base de

datos se muestran con un signo +, que De cada tabla se puede seleccionar solo los campos
significa que se pueden abrir para necesarios. Se pueden elegir campos de diferentes tablas.
seleccionar los campos de dicha tabla Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Acceso a Datos Externos

Condiciones
para los filtros

La información puede ser extraída En este paso, Excel permite traer la Se especifica que la data será extraída
filtrada desde la base de datos. información ordenada, en forma en formato Excel.
ascendente o descendente

El último paso será especificar la celda

desde donde se desplegará la data
extraída.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Formatos condicionales

Permite cambiar fuentes, colores, tamaño de letra, etc. dependiendo del valor que aparezca en la celda.
Existen varias alternativas para aplicar formatos condicionales.
Al seleccionar la opción “Formato Condicional en el menú ”inicio”, aparece el siguiente cuadro de diálogo.

Se establecen reglas que relacionan los contenidos con las

otras celdas, o comparaciones entre valores superiores o
inferiores.

Configurar de formatos que permiten la visualización de las

características de una celda dependiendo de sus valores.

Se pueden crear, modificar o eliminar las reglas.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Formatos condicionales
Los pasos para configurar un formato condicional son los siguientes:
3. Introducir los parámetros o
argumentos que se deben cumplir
2. Seleccionar la
para activar el formato.
condición principal

1. En el menú “Formatos
condicionales”, seleccionar
“Resaltar reglas de celdas”
para configurar las
condiciones que activarán el
formato en las celdas.
4. Diseñar el formato que
Otros casos contendrá la celda si se
cumple las condiciones
Es mayor que…

Validación de fechas

Texto que contiene…

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Formatos condicionales

Más reglas…

Formatos pre establecidos Condición configurable

por el usuario

Se pueden establecer valores pivotes desde los cuales

evaluar las condiciones.
Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Formatos condicionales

Formatos gráficos para las

celdas que cumplan las
condiciones configuradas. Se
utilizan para la evaluación de
rangos de celdas y diseño de
reportes semáforos.
Si no se han establecido
Visualiza las celdas con barras condiciones, Excel determina
en escala de colores, según el las escalas en base al promedio
valor de ésta. de los valores seleccionados.

Se puede reforzar el formato

de la celda, añadiéndole color
al fondo de la celda,
Lic. Rafael Mosquera
dependiendo del valor de la
Asesoría externa de sistemas y procesos
celda.
Mosquerar10@[Link]
Formatos condicionales

Administración de reglas

Permite añadir una nueva condición o regla a las ya existentes

Se utiliza para modificar reglas existentes.

Permite eliminar una o más reglas configuradas.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Tablas Dinámicas

 Es una de las herramientas más poderosas de Excel en cuanto a manejo de escenarios.

 Permite la aplicación de filtros, por distintos campos, ofreciendo una visión multidimensional de la

información.

 Integra las funcionalidades de filtros, funciones lógicas, subtotales, graficación, fórmulas.

 Se aplica a la metodología de consultas OLAP de la fase de Transformación de Inteligencia de negocio.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Tablas Dinámicas

Para explicar cómo se construye una tabla dinámica, utilizaremos una tabla que contiene los gastos ocasionados
por centro de costos en el semestre enero - Junio

CENTRO DE COSTO DESCRIPCION Enero Febrero Marzo Abril Mayo Junio Semestre
Administración Caja Chica 262.50 275.00 287.50 300.00 312.50 325.00 1,762.50
Administración Gasto Mínimo en Publicidad Local (Anual) 577.50 605.00 632.50 660.00 687.50 715.00 3,877.50
Administración Ingresos por Ventas 105,000.00 110,000.00 115,000.00 120,000.00 125,000.00 130,000.00 705,000.00
Administración Papelería 420.00 440.00 460.00 480.00 500.00 520.00 2,820.00
Administración Patente de Industria y comercio, (sobre ventas) 1,050.00 1,100.00 1,150.00 1,200.00 1,250.00 1,300.00 7,050.00
Administración Regalías Comerciales (Sobre Ventas SIN IVA) 6,300.00 6,600.00 6,900.00 7,200.00 7,500.00 7,800.00 42,300.00
Administración Servicios Contables 210.00 220.00 230.00 240.00 250.00 260.00 1,410.00
Administración Caja chica 300.60 315.63 331.41 347.98 365.38 383.65 2,044.65
Administración Compras 650,000.00 682,500.00 716,625.00 752,456.25 790,079.06 829,583.02 4,421,243.33
Administración Sueldos 5,200,000.00 5,460,000.00 5,733,000.00 6,019,650.00 6,320,632.50 6,636,664.13 35,369,946.63
Administración Vacaciones 756,500.00 794,325.00 834,041.25 875,743.31 919,530.48 965,507.00 5,145,647.04
Local Alquiler Mensual 8,400.00 8,800.00 9,200.00 9,600.00 10,000.00 10,400.00 56,400.00
Local Condominio 105.00 110.00 115.00 120.00 125.00 130.00 705.00
Local Depreciación mensual Mobiliarios y Equipos 2,916.67 2,916.67 2,916.67 2,916.67 2,916.67 2,916.67 17,500.00
Local Limpieza 210.00 220.00 230.00 240.00 250.00 260.00 1,410.00
Local Mantenimiento 840.00 880.00 920.00 960.00 1,000.00 1,040.00 5,640.00
Local Reserva Reparaciones 840.00 880.00 920.00 960.00 1,000.00 1,040.00 5,640.00
Local Seguro(anual) 262.50 275.00 287.50 300.00 312.50 325.00 1,762.50
Local Mobiliario 1,250,000.00 1,312,500.00 1,378,125.00 1,447,031.25 1,519,382.81 1,595,351.95 8,502,391.02
Local Remodelación 1,203,000.00 1,263,150.00 1,326,307.50 1,392,622.88 1,462,254.02 1,535,366.72 8,182,701.11
Personal Gasto en Uniformes por empleado 94.50 99.00 103.50 108.00 112.50 117.00 634.50
Personal Pasivos Laborales 6,376.40 6,376.40 6,376.40 6,376.40 6,376.40 6,376.40 38,258.40
Personal Sueldo Personal 16,780.00 16,780.00 16,780.00 16,780.00 16,780.00 16,780.00 100,680.00
Servicios Electricidad 1,050.00 1,100.00 1,150.00 1,200.00 1,250.00 1,300.00 7,050.00
Servicios Internet 52.50 55.00 57.50 60.00 62.50 65.00 352.50
Servicios Servicio (Agua+Aseo) 157.50 165.00 172.50 180.00 187.50 195.00 1,057.50
Servicios Teléfono 525.00 550.00 575.00 600.00 625.00 650.00 3,525.00
Servicios Luz 350,000.00 367,500.00 385,875.00 405,168.75 425,427.19 446,698.55 2,380,669.48
Servicios Aseo 25,003.00 26,253.15 27,565.81 28,944.10 30,391.30 31,910.87 170,068.23 Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Tablas Dinámicas

Los pasos son:

1. Ubicar el cursor en cualquier parte del banco de datos al cual se le va a generar la tabla dinámica.
2. Seleccionar la opción “Tablas dinámica” dentro del menú “Insertar.

Excel selecciona automática el rango

correspondiente al banco de datos.
El usuario puede modificar este rango.

Se puede crear la tabla en una hoja

aparte o en un área dentro de la
misma hoja.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Tablas Dinámicas
Aparece la siguiente pantalla que le permite al usuario armar la tabla dinámica.

Campo ancla que

muestra la información
según éste.

Campos del rango

seleccionado

A medida que se va
construyendo la tabla se puede
configurar la presentación de
cada campo

Área de
presentación de los Lic. Rafael Mosquera
datos Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Tablas Dinámicas

A medida que se van marcando los campos a agregar a la tabla, van apareciendo en el área de datos, así como en los cuadros
de configuración.

La tabla dinámica va organizando la información según la clasificación que tenga en el banco de datos que la originó

Excel permite, a través del mouse, cambiar los campos de posición para adaptar el cuadro a las necesidades del usuario.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Tablas Dinámicas

Configuración Al seleccionar uno de los campos incluidos en la tabla dinámica,

aparece un cuadro de diálogo que permite cambiar algunas
características de ese campo.

Nombre del Campo que aparecerá

en la tabla dinámica

Función a aplicar para el cálculo de

los subtotales de cada campo

Configuración para la visualización

de los valores
Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Gráficos Dinámicos

Los gráficos dinámicos tienen la misma filosofía de trabajo que las tablas, sin embargo permite visualizar los
datos a través de cualquier tipo de gráfico permitido por Excel.

Cada gráfico dinámico está asociado a una tabla dinámica. Cuando se cambian los valores en dicha tabla,
automáticamente se actualiza el gráfico y viceversa.

La diferencia con respecto a los gráficos sencillos de Excel, es que a través de esta herramienta, se pueden
elaborar escenarios directamente filtrando la información que se desee analizar.

Cualquier cambio que se desee realizar en las etiquetas del gráfico, se deben hacer en la tabla dinámica.

Los gráficos dinámicos se pueden generar desde el banco de datos origen o desde una tabla dinámica
creada previamente.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Gráficos Dinámicos

Para crear un gráfico dinámico sin que haya una tabla dinámica creada, se selecciona la opción “Gráfico dinámico” que se
encuentra dentro de la opción “Tabla Dinámica del menú “Insertar”, tal como lo muestra el siguiente cuadro.

Al igual que las tablas dinámicas, aparece el cuadro “Crear tabla dinámica con el gráfico dinámico”, donde el usuario elegirá el
rango que quiere graficar, y si lo quiere en la misma hoja o en una nueva.

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Gráficos Dinámicos

Aparece la pantalla para armar el gráfico, colocando los datos en el área respectiva.

Barra de
herramientas de
gráficos.

Campos del
rango
seleccionado

Configuración
de los campos
en las áreas del
gráfico
Área de gráfico.
Área donde se va Se va armando el gráfico
construyendo la a medida que se van
tabla dinámica seleccionando los Lic. Rafael Mosquera
asociada al gráfico campos. Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Gráficos Dinámicos

Una vez elaborado el gráfico, se pueden cambiar las características y la configuración de sus componentes.

Tabla dinámica
generada con el
gráfico

Cuadro para filtrar la Gráfico generado

información a mostrar Área de organización
con la data
en el gráfico de los datos en cada
seleccionada
parte del gráfico
Lic. Rafael Mosquera
Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Gráficos Dinámicos

Click botón derecho en el borde del gráfico, aparece el cuadro de

diálogo que configurará las características del gráfico general.

Click botón derecho el área del gráfico para cambiar las características
del área donde se muestra éste. Es un extracto del menú general.

Click botón derecho en cualquiera de las columnas o líneas para cambiar las
características de la forma. Incluso de pueden añadir imágenes dentro de
las columnas, señalando la opción “Dar formato a la serie de datos…” –
Relleno – Insertar de …

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Segmentación de datos

Pasos para la construcción de la segmentación de datos.

Vendedor (Todas)

NombreCategoría NombreProducto Cantidad PrecioUnidad

Bebidas Café de Malasia 580 1205,2
La siguiente tabla dinámica esta conformada Cerveza Klosterbier Rhönbräu 1155 339,45
Cerveza Laughing Lumberjack 184 137,2
por los siguientes elementos: Cerveza negra Steeleye 883 612
Cerveza Outback 817 552
 Nombre del vendedor (Titulo de la tabla) Cerveza Sasquatch
Cerveza tibetana Barley
506
1057
246,4
786,6
 Categoría Licor Cloudberry 981 662,4
 Nombre del producto Licor verde Chartreuse 793 500,4
Refresco Guaraná Fantástica 1125 216
 Cantidad Té Dharamsala 828 651,6
 Precio unitario Vino Côte de Blaye 623 5902,4
Total Bebidas 9532 11811,65
Carnes Buey Mishi Kobe 95 465,6
Cordero Alice Springs 978 1349,4
Empanada de carne 722 904,8
Empanada de cerdo 755 244,95
Paté chino 903 739,2
Salchicha Thüringer 746 3713,38
Total Carnes 4199 7417,33

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Segmentación de datos

Pasos para la construcción de la segmentación de datos.

Vendedor (Todas)

NombreCategoría NombreProducto Cantidad PrecioUnidad

Bebidas Café de Malasia 580 1205,2
Cerveza Klosterbier Rhönbräu 1155 339,45
Cerveza Laughing Lumberjack 184 137,2
 Seleccionar la tabla dinámica en cualquier Cerveza negra Steeleye 883 612
Cerveza Outback 817 552
parte de ésta. Cerveza Sasquatch 506 246,4
Cerveza tibetana Barley 1057 786,6
Licor Cloudberry 981 662,4
Licor verde Chartreuse 793 500,4
 Aparece en la cinta de menú, las opciones Refresco Guaraná Fantástica 1125 216
Té Dharamsala 828 651,6
de herramientas de tabla dinámica. Vino Côte de Blaye 623 5902,4
Total Bebidas 9532 11811,65
Carnes Buey Mishi Kobe 95 465,6
Cordero Alice Springs 978 1349,4
Empanada de carne 722 904,8
Empanada de cerdo 755 244,95
Paté chino 903 739,2
Salchicha Thüringer 746 3713,38
Total Carnes 4199 7417,33

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Segmentación de datos

Pasos para la construcción de la segmentación de datos.

 En la herramienta “Analizar”, área filtrar, elegir la opción “segmentación de datos” .

Segmentación de datos Opción Analizar Menú de herramientas

Área filtrar

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Segmentación de datos

Pasos para la construcción de la segmentación de datos.

 Aparece un cuadro con los campos que

 Al hacer clic en Aceptar,
contiene el banco de datos desde donde
aparecerán tantos cuadros
se origina la tabla dinámica.
como campos se hayan
 Se seleccionan los campos a través de
seleccionado
los cuales se realizará la consulta.

Al seleccionar uno o varios campos de los cuadros de segmentación, se cambia automáticamente

Lic. Rafael Mosquera
los escenarios correspondientes. Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
 Módulo 4

Creación de tableros de control

(Dashboard)

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
72
BALANCED SCORE CARD

Sostenibilidad
Financieros Económica
Misión, visión
Estrategias

Propuesta de
Clientes valor
Objetivos
por área
Dashboard

Cuadro de Procesos Eficiencia

Mando Integral internos Interna

Aprendizaje Actualización
Indicadores
de gestión
Innovación
Metas

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
Creación de Dashboard

Conocidas las herramientas que brinda Excel para la transformación de los datos en información y elaboración de consultas,
lo que queda es la integración de todas éstas para la visualización de toda la información necesaria en una pantalla.

TRANSFORMACIÓN
ALMACENAMIENTO EXTRACCIÓN

Extracción Datos .
desde Excel
B.D Fórmulas, funciones, filtros, escenarios,
Externas . alarmas, tablas y gráficos dinámicos, Macros

Banco de datos.
B.D Hoja de cálculo
Externas CONSULTAS
Menú Datos – área “Obtener datos
externos” – B.D
Repositorio de datos de los .
sistemas de apoyo a la gestión

Dashboard

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
EJEMPLO DE UN DASHBOARD

Formatos condicionales
Gráficos dinámicos
Tablas dinámicas

Segmentación de datos

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]
RECUERDA :

LO QUE NO SE PUEDE MEDIR NO SE PUEDE CONTROLAR

Y LO QUE NO SE CONTROLA, NO SE PUEDE MEJORAR.

Peter Drucker

Lic. Rafael Mosquera

Asesoría externa de sistemas y procesos
Mosquerar10@[Link]