Biblioteca Online : ¡La Suscripción ENI por 9,90 € el primer mes!, con el código PRIMER9. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Cuadros resumen y cuadros de mando
  3. Configuración de los resultados
Extrait - Cuadros resumen y cuadros de mando Tratamiento y análisis de grandes volúmenes de datos con Excel 2016
Extractos del libro
Cuadros resumen y cuadros de mando Tratamiento y análisis de grandes volúmenes de datos con Excel 2016
1 opinión
Volver a la página de compra del libro

Configuración de los resultados

Introducción

Acabamos de ver cómo llegar a resultados estadísticos fiables mediante subtotales o fórmulas variadas. La presentación de estos datos es esencial para destacar los puntos importantes. En el capítulo Cuadros de mando e indicadores veremos cómo aplicar indicadores gráficos en los cuadros de mando, asimismo, este capítulo le permitirá aplicar las distintas técnicas de formato de resultados.

 Abra el libro BaseCap.xlsx.

Este libro contiene un conjunto de hojas con los datos y las tablas de estadística asociadas. Los cálculos ya se han realizado para que pueda dedicarse únicamente al formato de los resultados.

Se abordarán las siguientes funciones:

  • Configuraciones condicionales

  • Minigráficos

  • Gráficos

  • Fórmula que hace aparecer un indicador.

Estadísticas de gastos de secciones de club

1. Configuración de los valores

La hoja EstadDeportes del libro consta de dos cuadros resumen relativos a los gastos de las distintas secciones de un club de artes marciales.

La tabla de las columnas A y B calcula los gastos acumulados y la de las columnas D a O los gastos mensuales a partir de los datos introducidos en la hoja GASTOS1.

images/SOB05_01.png

La fórmula en B5 es: =SUMA.SI(GASTOS1!$B$2:$B$1000;EstadDeportes!A5;GASTOS1!$C$2:$C$1000)

Esta fórmula se ha vuelto a copiar hacia abajo y hacia la derecha hasta la celda O10.

La fórmula que aparece en D5 es una fórmula matricial: {=SUMA((GASTOS1!$B$2:$B$1000=EstadDeportes!$A5)*(MES(GASTOS1!$A$2:$A$1000)=EstadDeportes!D$4)*(GASTOS1!$C$2:$C$1000))}

Su primera tarea consistirá en poner en caracteres de color rojo los gastos mensuales cuyo importe sea superior a 150 euros.

Excel nos da la posibilidad de aplicar un formato condicional a los datos de nuestras tablas. Esta primera opción permite configurar un formato que se aplicará a los valores que se muestran en función de uno o varios criterios.

 Antes de empezar, seleccione el rango D5 a O10.

 Pestaña Inicio - grupo Estilos, haga clic en el botón Formato condicional.

images/SOB05_02.png

 A pesar del número de reglas disponibles, haga clic en Nueva regla. En el cuadro de diálogo Nueva regla de formato, seleccione Aplicar formato únicamente...

Gastos personales

1. Configuración de los valores

La hoja ESTADGAS del libro consta de un cuadro resumen relativo a los gastos por concepto en un hogar.

La fórmula en C4 es: =SUMA.SI.CONJUNTO(GASTOS2!$D$2:$D$500;GASTOS2!$B$2:$B$500;ESTADGAS!$A4;GASTOS2!$C$2:$C$500;ESTADGAS!C$3)

Esta fórmula se ha vuelto a copiar hacia abajo.

Se ha establecido un presupuesto de previsión de gastos a principio de año y nuestro trabajo consiste en poner en fondo rojo los nombres de los conceptos cuyos gastos son superiores al presupuesto previsto.

images/SOB05_11.png

La acción que se va a llevar a cabo ahora es ligeramente diferente de la técnica aplicada anteriormente. En efecto, en el ejercicio anterior, habíamos aplicado formatos condicionales directamente a las celdas en cuestión en función de sus valores. Aquí el color rojo debe aplicarse a una celda de la columna A en función de la comparación de los datos de las columnas B y E. Por lo tanto, el formato no dependerá de la celda en sí, sino de las demás celdas.

Por consiguiente, será necesaria la utilización de una forma de cálculo.

 Seleccione el rango de celdas A4 a A14.

 Pestaña Inicio - grupo Estilos, haga clic en el botón Formato condicional.

 Haga clic en Nueva regla.

 En el cuadro de diálogo Nueva regla de formato, escoja la última opción Utilice una fórmula...

Seguimiento de comerciales

1. Configuración de los valores

La hoja ESTADSEGUR del libro consta de un resumen de los contratos vendidos por comerciales de seguros. Estas estadísticas se obtienen a partir de los datos de la hoja SEGUROS.

images/SOB05_21.png

La fórmula en B14 es:

=SUMA.SI.CONJUNTO(SEGUROS!$E$2:$E$2000;SEGUROS!$C$2:$C$2000;ESTADSEGUR!$A14;SEGUROS!$B$2:$B$2000;ESTADSEGUR!B$13)

Esta fórmula se ha vuelto a copiar hacia abajo y hacia la derecha para cada comercial y cada producto.

Para hacer un seguimiento de los comerciales, vamos a colocar en fondo rojo en la última tabla las ventas inferiores al objetivo correspondiente.

 Seleccione el rango de volumen de ventas realizado: B14 a D19.

 Pestaña Inicio - grupo Estilos, haga clic en el botón Formato condicional.

 Haga clic en Nueva regla.

 En el cuadro de diálogo Nueva regla de formato, escoja la última opción Utilice una fórmula que determine las celdas para aplicar formato.

 Introduzca la fórmula =B14<B4.

images/SOB05_22.png

 Haga clic en el botón Formato.

 Pestaña Relleno, seleccione el color Rojo y finalice pulsando dos veces la tecla Aceptar.

Las ventas inferiores al objetivo aparecen ahora en fondo rojo.

images/SOB05_23.png

Este formato condicional se ha aplicado en el detalle de las ventas. Ahora vamos a aplicar algunos indicadores visuales en los porcentajes de nuestra tercera tabla.

En función del porcentaje del volumen de ventas realizado con respecto al objetivo, hemos definido tres indicadores:

  • Porcentaje superior o igual a 90%: icono verde

  • Porcentaje superior a 80% e inferior a 90%: icono naranja

  • Porcentaje inferior o igual a 80%: icono rojo

images/SOB05_24.png

 Seleccione el rango de porcentajes B25 a D25.

 Pestaña Inicio - grupo Estilos, haga clic en el botón Formato condicional.

 Haga clic en Nueva...

Gestión de horas

1. Presentación

La hoja GESTTIEMPO contiene una lista de horarios. Cada día, cada colaborador introduce la distribución de su tiempo de trabajo entre la realización de presupuestos, la facturación, la asistencia a reuniones y las visitas a clientes.

En la hoja ESTADGESTTIEMPO se ha diseñado un cuadro resumen que aúna los tiempos de trabajo por colaborador y por tipo de actividad.

Las primeras filas de datos de la hoja GESTTIEMPO:

images/SOB05_38.png

La tabla de la hoja ESTADGESTTIEMPO:

images/SOB05_39.png

La fórmula en B4 es: =SUMA.SI.CONJUNTO(GESTTIEMPO!$D$2:$D$1000;GESTTIEMPO!$C$2:$C$1000;ESTADGESTTIEMPO!$A4;GESTTIEMPO!$B$2:$B$1000;ESTADGESTTIEMPO!B$3)

Esta fórmula se ha vuelto a copiar hacia abajo y hacia la derecha para cada colaborador y cada tipo de actividad.

No se ha aplicado ningún formato a los resultados. La primera acción que realizaremos será dar formato a los resultados en formato horario. Los números tal como se muestran en la tabla corresponden a un número de horas. Durante el año, nuestros colaboradores superarán con creces las 24 horas por actividad. Por lo tanto, habrá que configurar un formato que permita superar las 24 horas.

2. Configuración de un formato horario

 Seleccione todas las celdas que contengan fórmulas (B4 a D7).

 Pestaña Inicio - grupo Número, haga clic en el icono del cuadro de diálogo....

Seguimiento de una obra

1. Presentación

Dirige una empresa del sector de la construcción. La hoja SeguimientoObra contiene una lista de los gastos relativos a una obra. Los gastos se reparten en tres categorías: MO (mano de obra), materiales y honorarios. Cuando realizó el presupuesto, hizo una previsión de los gastos por conceptos.

Las primeras filas de la hoja SeguimientoObra:

images/SOB05_44.png

Las tablas de la hoja EstadObra:

images/SOB05_45.png

La fórmula en D5 es: =SUMA.SI(SeguimientoObra!$B$2:$B$1000;EstadObra!A5;SeguimientoObra!$C$2:$C$1000)

Esta fórmula se ha vuelto a copiar hacia abajo.

Para ver cómo se sitúan los totales de los gastos de cada categoría con respecto al presupuesto estimado, vamos a integrar en las celdas de la columna D barras de progreso.

Además, si para un concepto se ha superado el coste estimado, haremos aparecer en la columna E indicadores gráficos rojos. El número de puntos rojos va en función al valor de sobrecoste: si los costes acumulados superan el presupuesto del 50% al 59%, se mostrarán cinco puntos, si los costes acumulados superan el presupuesto del 20% al 29%, se mostrarán dos puntos.

images/SOB05_46.png

2. Insertar una barra de datos en las celdas

El formato condicional que vamos a aplicar debe tener en cuenta para cada fila el presupuesto estimado correspondiente.

Desgraciadamente, Excel no admite el uso de coordenadas relativas en los criterios de formato condicional para los matices de color, las barras de datos y los conjuntos...