Presentación del Problema
Continuando con los gráficos enfocados al control de medidas de desempeño y cumplimiento de metas, podemos utilizar el Waffle en caso tal que no dispongamos de mucho espacio para una gráfica como el velocímetro o el termómetro, además, este tipo de grafico permite leer específicamente cada uno de los valores que se han obtenido en la meta y también podemos agregarle un diferencial, haciendo que cambie de color según el nivel en el que se encuentre. Así que vamos a ver como se elabora este tipo de gráfica.
Contexto
Para este ejemplo supondremos que somos directores de una revista y tenemos como objetivo obtener mil suscriptores nuevos para el año 2014, al final de cada mes debemos registrar en la tabla de la Imagen 1 la cantidad de suscriptores nuevos.
Imagen 1 – Tabla de Registro de nuevos suscriptores
En la celda B15 se encuentra la meta establecida para este ejemplo.
Pasos para elaborar el gráfico
Primero, debemos calcular el total de nuevos suscriptores hasta el mes indicado, para ello utilizamos la función Suma aplicándola al rango donde se deben ingresar los nuevos suscriptores. Ver Imagen 2.
Imagen 2 – Función Suma
Después, es necesario calcular el porcentaje de cumplimiento de la meta como se muestra en la Imagen 3, no olvidemos poner este resultado en formato de porcentaje.
Imagen 3 – Porcentaje de Cumplimiento
Creación de la gráfica
La particularidad que tiene este tipo de gráfico es que no se requiere insertar una gráfica pre-establecida por Excel, simplemente debemos crear una cuadricula de 10 x 10 de porcentajes, que van de 1% a 100% como se muestra en la Imagen 4, es importante empezar en la parte inferior izquierda de la cuadricula, es decir, seguir el mismo orden de la imagen para colocar cada uno de los valores y además insertar borde a la misma.
Imagen 4 – Cuadricula de Porcentajes
Luego, debemos crear una tabla que nos indique en que porcentaje de la meta deseamos que nuestro Waffle cambie de color, además nuestra tabla debe contener una columna extra con un valor asociado que represente a cada color, más adelante entenderemos la función de dichos números. Ver Imagen 5.
Imagen 5 – Tabla de Colores
Ahora copiamos y pegamos la cuadricula debajo de la misma y borramos los valores y el formato, con el fin de que tengamos solo el borde que nos servirá de guía más adelante. Ver Imagen 6.
Imagen 6 – Cuadricula sin Valores
Ahora debemos insertar la siguiente formula
=SI($B$17>=H2;BUSCARV($B$17;$E$3:$F$7;2;VERDADERO);0) en la celda superior izquierda de la cuadricula sin valores, es decir la celda H13, y luego arrastramos la formula por toda la cuadricula para asignar los valores asociados en cada entrada. Imagen 7.
Explicación de la Formula
El objetivo de la fórmula es asignar el valor asociado en la cuadricula que se encuentra vacía, para que no aparezcan los porcentajes si no el número respectivo al color en el que se encuentra nuestra meta, esto hara que cambie de color según el nivel,
La función Si permite que se identifique si la celda debe llevar un valor de asignación, en caso de que esto sea verdadero, la función Buscar V hace que de acuerdo a la tabla se seleccione el valor de asignación adecuado, en caso contrario asigna cero, Nota: la formula Buscar V debe buscar de manera aproximada.
Imagen 7 – Formula de Asignación de Valores
Formato Condicional
Ahora aplicaremos el formato condicional de acuerdo a la tabla, entonces, seleccionamos la cuadricula con los valores asignados y aplicamos formato condicional para cada uno de los colores ( Formato Condicional -> Nueva Regla -> Utilice una fórmula que determine las celdas para aplicar formato -> en el cuadro de texto escribimos H13=1 > Formato -> seleccionamos relleno rojo -> Fuente del mismo color del relleno -> Borde Blanco ) Imagen 8 a Imagen 11. Debemos repetir el proceso para cada uno de los colores asignados en la tabla teniendo en cuenta el número de asignación es decir para el color Amarillo seria H13=2. Imagen 12.
Imagen 8 – Formato Condicional
Imagen 9 – Relleno Formato Condicional
Imagen 10 – Color de Fuente Formato Condicional
Imagen 11 – Borde Formato Condicional
Imagen 12 – Todas las Reglas de Formato Condicional
Ahora aplicamos un fondo gris a la cuadricula, además, la fuente también debe ser del mismo color de fondo para que no se vean los números y por utlimo usamos un borde blanco para que concuerde con el borde aplicado en las reglas del Formato Condicional. Imagen 13.
Imagen 13 – Ajustes de la Cuadricula
Ahora es necesario copiar la cuadricula, y en una nueva hoja la pegamos como Imagen Vinculada ver Imagen 14, y luego disminuimos su tamaño.
Imagen 14 – Imagen Vinculada
Además, agregamos el valor numérico, nos dirigimos a la pestaña insertar y en el grupo texto añadimos un WordArt con el estilo que deseemos, una vez insertado el WordArt lo seleccionamos y en la barra formulas hacemos referencia a la celda que muestra el porcentaje.
Imagen 15 – Valor Numérico
Y como resultado tenemos nuestra grafica que visualmente es muy llamativa y cambia de color según el nivel.
Hasta la Proxima