Configuración de los resultados

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.

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.

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.

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.

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.

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.

Las ventas inferiores al objetivo aparecen ahora en fondo rojo.

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:

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:

La tabla de la hoja ESTADGESTTIEMPO:

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

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:

Las tablas de la hoja EstadObra:

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.

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...