Seguimos hablando de diseño de tabla dinámica. En Excel 2007 se agregaron nuevos tipos de formato condicional: barras de datos, escalas de colores, conjuntos de iconos, etc, además se agregaron nuevas reglas (top 10) y una nueva interfaz de usuario para trabajar con tablas dinámicas.

Estamos hablando de dar formato a los valores de la tabla, a aquellos elementos que han sido definidos en el cubo OLAP desde SSAS y que son medidas o campos calculados. Sobre cómo se crean estos valores en el cubo y cómo se consumen por la tabla dinámica hemos hablado antes.   Sobre KPIs hablaremos en la siguiente entrega de esta serie. Hoy nos centramos en el formato dinámico desde Excel.

Las propiedades dinámicas están relacionadas a los elementos de la tabla, no a la celda en particular. Me explico, digamos que tenemos una columna que muestra un determinado valor como puede ser ventas por países. Pues bien, una vez configurada esta columna para que, por ejemplo, dibuje una barra de color, que será más o menos larga o corta en dependencia del dato en cuestión, no pierda su configuración. En la medida que aparezcan o se eliminen datos, en este caso países, y en la medida en que los datos resultantes aumenten o disminuyan, o que nuestra tabla se expanda o se contraiga, así será la barra que se obtendrá, porque la propiedad que se ha configurado dinámicamente se ha configurado contra el elemento que muestra el nivel de la jerarquía correspondiente a Países, no contra el grupo de celdas específicas seleccionadas en el momento de configurar. Así que se reevalúan las condiciones definidas en la regla de formato condicional y se obtiene el formato acorde con estas condiciones.

Para trabajar con formato condicional nos vamos al menú Inicio de Excel 2007, grupo Estilos.

Veamos un ejemplo, mostramos las ventas por países y las ordenamos. Seleccionamos las celdas que abarcan estas ventas y luego, seleccionamos un color para la opción Barra de datos del desplegable en Formato Condicional.

Ahora vemos claramente la relación que existe entre los valores obtenidos, nos da idea de la magnitud y proporcionalidad de los datos. A estas alturas ya hemos ganado un terreno importante en cuanto al análisis de los datos se refiere. Pero aún hay más.

¿Qué pasa si deseo obtener esta misma barra para el resto de los datos? Yo podría comparar los datos entre las diferentes ciudades; pero, sin tener en cuenta los subtotales; porque estos valores, al ser agregados distorsionarían el dato. En lugar de ir seleccionando cada grupo de ciudades, dejando fuera el subtotal, para cada país, lo cual es un dolor de cabeza, lo que podemos hacer es definir el alcance de la regla de formato condicional en la tabla dinámica con Office 2007. Veamos, como siempre, una imagen vale más que mil palabras.

Cuando se define un formato condicional, de forma predeterminada se configura para las celdas seleccionadas. Esa es la regla. Para establecer algo diferente, seleccionamos Nueva regla del menú desplegable del comando Formato condicional. Se muestra la ventana que vemos a continuación:

Vamos a detenernos un poco en esta ventana.

Aplicar regla se refiere al alcance en cuanto a celdas y niveles de las jerarquías definidas, donde Celdas seleccionadas es el valor predeterminado y los otros casos serán según se muestra a continuación.

Se trata de todas las celdas que muestren el dato, por lo que si bajamos del nivel Calendar Year al nivel Calendar Semester de la jerarquía, los datos van a seguir mostrando el formato condicional, lo mismo que si expandimos el nivel Country y mostramos City.

Este caso es más restrictivo, como vemos el formato condicional se mantiene solamente para el nivel en el que fue definido. No incluye totales.

Seleccionar un tipo de reglas

Ahora no se trata del nivel de jerarquía que se está mostrando sino aplicar el formato según el valor que se está mostrando.

1.- Aplicar formato a todas las celdas según sus valores es la configuración predeterminada, en este caso, no se tiene en cuenta el valor a mostrar para definir si se aplica el formato o no, la decisión queda de manos de Aplicar regla.

2.- Aplicar formato únicamente a las celdas que contengan da la posibilidad de aplicar el formato filtrando aquellas celdas que nos interesa, en el ejemplo siguiente hemos definido que aplique a las celdas seleccionadas que tengan valores comprendidos entre 30000 y 40000, fuente itálica o cursiva, formato moneda y fondo azul. Veamos cómo estaban antes, como queda la configuración y el resultado

3.- Aplicar formato solamente a los valores con rango superior o inferior – Aquí se trata de dividir los datos, ya sean los seleccionados o todas las celdas que estén mostrando, digamos las ventas, y tomar un tanto porciento de los valores superiores o inferiores de los datos. Hacemos un ejemplo y vemos el resultado:

4.- Aplicar formato únicamente a los valores que están por encima o por debajo del promedio – se trata en este caso de que Excel calcule el valor medio del rango seleccionado, y luego, en dependencia de lo que se le indique, resalte las que corresponda.

5.- Aplicar formato únicamente a los valores únicos y duplicados se trata de resaltar si hay valores únicos o duplicados y puede ser muy útil para detección de anomalías. Tiene una peculiaridad y yo desconozco la causa, se activa para una celda o selección que no esté únicamente en el área de valores, es decir, hay que incluir en la selección algún campo de filas de no valores. Veamos:

Tenemos esta selección que combina valores y no valores

Formato condicional, nueva regla nos deja ver la opción Valores únicos o duplicados

Seleccionamos solamente las celdas de tipo valor, las mismas de antes, ya no aparece esta variante para el formato.

Basta con seleccionar entre únicos y duplicados y definir el formato deseado.

6.- Utilice una fórmula que determine las celdas para aplicar formato – este lo dejamos para cuando veamos algo de fórmulas en Excel con MDX

Editar una descripción de la regla se dedica a configurar la regla en cuestión cuando se ha seleccionado la opción Aplicar formato a todas las celdas según sus valores. Veamos un ejemplo de posible configuración  y resultado.

La segunda parte de este tema la voy a dedicar al Administrador de reglas de formatos condicionales.

Hasta entonces,

Saludos,

Ana