No sé si el título de la entrada es el correcto, pero intenté acercarme a la realidad. El tema viene planteado por un lector del blog que me consulta lo siguiente: "debo medir el grado de avance de un proyecto y que Excel me muestre graficamente cuanto ha logrado cada area de la empresa sobre el total asignado". Lo mas común sería utilizar varios gráficos, pero se me ocurrió ayudarlo con una "tabla gráfica", como la que se observa en la siguiente figura:
omitiendo los datos enviados por mi lector, armé esta tabla, en donde se aprecia visualmente el grado de avance de cada Sucursal en la colocación de productos.
Mas allá del aspecto visual, que se logra con un par de Autoformas (lo explicaré sobre el final del post), esta tabla refleja porcentualmente que grado de cumplimiento tiene cada Sucursal sobre sus metas asignadas. Notarán que, mediante Formato Condicional, los colores varían según la cantidad alcanzada.
Obvimante mostré como primer imagen el trabajo terminado, pero para llegar a él harán falta una serie de tablas "auxiliares", las cuales coloqué en otra hoja. Vamos al paso x paso:
1) Determinar las metas para cada sucursal x producto: para ello, armamos la siguiente tabla, en donde especificaremos cuantitativamente las ventas que deberán realizar, por producto:
2) Discriminar cuanto a logrado cada Sucursal: en una tabla con el mismo formato que el anterior, iremos introduciendo la cantidad alcanzada por cada filial:
aqui se van imputando las ventas alcanzadas por Sucursal, en cada rubro
3) Determinar el porcentaje de cumplimiento: en la primer tabla tenemos las metas y en la segunda los resultados obtenidos... agregaremos una tercer tabla, en donde calcularemos el porcentaje del avance. Esto lo calculamos con un simple anidado de funciones, teniendo en cuenta que necesito valores enteros:
=ENTERO(B20/B4*10)
Sintaxis: divido B20 por B4, o sea lo alcanzado hasta el momento. Esto me dá: 0.8. Lo multiplico x 10 y obtengo 8. Y a todo este resultado lo anido dentro de ENTERO(), para truncar los decimales:
sobre esta ultima tabla haremos los gráficos expuestos en la primer imagen.
4) En la misma hoja en donde colocamos los datos mostrados hasta el momento, dentro de una celda introduciremos una letra (ya verán para qué). En este caso, dentro de B38 introduje la letra "L" (ele minúscula):
luego, desde el cuadro de nombres, nombré a B38 como "letra1" (sin las comillas)
Configurados todos los parámetros, comencemos a trabajar sobre la tabla final:
ya estamos llegando, omitiendo los formatos.
Noten la función introducida en B5: Repetir(), la cual consta de dos argumentos:
a) caracter: el caracter en cuestión. en nuestro caso, la letra l (ele minúscula)
b) cantidad de veces que se deberá repetir el caracter.
Otro ejemplo: =Repetir("X", 6) nos devuelve: XXXXXX. Simple: 6 veces la X.
Explicada la función en sí, veamos como trabaja en nuestro caso:
=REPETIR(letra1;Hoja2!B30)
a) letra1: es la celda B38, a la que llamé "letra1" ¿por qué? sencillo: si en un futuro quiero cambiar la letra, de esta forma omito modificar todas las funciones, simplemente cambio en caracter en B38 y listo, se actualizará a toda la tabla.
b) Hoja2!B30: corresponde a una celda de la tabla "porcentajes". Veremos que en dicha celda encontraremos el valor 8, que me indica el grado de avance de la Sucursal_01 en el rubro Depósitos.
Muy fácil, le digo a Excel: repetime 8 veces la letra que se encuentra en el rango B38 (letra1).
Si luego copiamos y pegamos esta funcion en toda la tabla, obtendremos el siguiente resultado:
bien bien bien, ya vemos como va concluyendo nuestra tarea.
De ahora en más, todo es cuestión de formatos.
Seleccionamos el rango B5:F10 y cambiamos la tipografía por la siguente: Wingdings, lo que nos reemplazará visualmente el "palito" de la ele minúscula por los círculos:
Una vez logrado esto, nos restaría aplicar el formato condicional. Para ello utilizaremos una función, que evaluará el largo de la celda: si tiene 3 o menos caracteres las letras serán de color rojo, si es menor o igual a 7 de color anaranjado y si la cantidad de caracteres es mayor a 8, verde. Esto lo incorporamos para darle mas énfasis visual: los humanos ya hemos preestablecido que el rojo significa "alerta" y el "verde" que todo anda bien. Mas cerca estamos de 10..... mas cerca de lograr los objetivos planteados:
las tres condiciones, con formulas: en cada caso la función me devuelve Verdadero o Falso. Si es verdadero, se aplica la condición.
Concluído el paso anterior, tenemos nuestra tabla con el Formato Condicional lista:
El trabajo está listo: en una sola tabla nos resulta viable observar graficamente el grando de avance... sin utilizar gráficos. Comparen esto con cualquiera de las tablas creadas hasta el momento y obsevarán la ventaja de ofrecer información en este formato, prescindiendo de numeros.
Para llegar a la primer imagen expuesta en la entrada, como mencioné, hice uso de las Autoformas y las distintas opciones de formato que poseen. Les dejo un ejemplo, que luego adaptarán a sus necesidades:
Vamos al menú Insertar / Imagen / Autoforma, y seleccionamos la de nuestro interes:
Hacemos click con el botón derecho sobre ella y elegimos la opción: Formato de autoforma. Luego click sobre "Color" y vamos a "efectos de relleno":
Lo que nos brindará la posibilidad de "jugar" un poco con los tipos, colores y degradados del relleno. No olvidemos modificar la transparencia, para que se vea lo que hay debajo de las figuras:
vean que este formulario tiene otras cuatro pestañas: "textura", "trama" e "imagen", lo que nos permitirá aún lograr efectos mucho mas personalizados. En "imagen" podremos cargar una imagen desde nuestro disco.
Y el resultado:
aquí le apliqué bordes rojos.
Dandole la transparencia adecuada y girando un poco esta autoforma, la coloqué en el encabezado de la tabla, brindando el siguiente aspecto visual:
Como siempre, queda abierto el post a cualquier interrogante, como por ejemplo: ¿además de variar el color... es posible que cambie el tipo de caracter mostrado, es decir, en vez de circulos que sean flechas hacia arriba o abajo, según los resultados?
Les dejo el archivo, por si lo necesitan: Panel de graficos
Les dejo el archivo, por si lo necesitan: Panel de graficos
increíble damián, me ha gustado mucho tu ejemplo y gracias por compartir el archivo, me resultó muy útil. pude darme cuenta que utilizaste autoformas de una manera que desconocía. Necesito lograr que algunas de ellas se muevan por la hoja, pero no se como hacerlo. ¿me ayudarías?
ResponderEliminargracias mauricio por tu comentario.
ResponderEliminarlo que mencionás con las Autoformas es totalmente factible de ser realizado, utilizando macros.
no me especificás bien con que fin necesitás mover estas "Shapes" por la hoja, pero te armo un ejemplo y te aviso. Si luego no entendés algo o en realidad buscas otra cosa, me decís.
el tema es interesante, y haré un post al respecto, respondiendo tu pregunta.
mauricio: la posible solución a tu planteamiento:
ResponderEliminarhttp://damianexcel.blogspot.com/2010/04/simular-power-point-en-excel-crear.html
te dejo el archivo para que lo analices y modifiques a tus necesidades.
avisame que tal te fué.
Excelente,. !! Realmente muy bien explicados todos los posts que publicas men,. Muchas Gracias"!,. por el tiempo que te das para resolver las inquietudes de los demás y si bien no he notado muxos comentarios no creo q sea por q no quieren dejarlo sino más bien xq aún no llegan a tu pag,. pero imagino se irán sumando,. así q no desistas en la labor de seguir actualizando el blog ,. y nuevamente reitero mi gratitud y si gustas puedo aportar con algunos ejemplos q sirvan de apoyo para quienes se inician en este mundo t los hago llegar ,. Estoy en el intermedio pa' avanzado así que tus artículos me caen como anillo al dedo,. Muxas Gracias"!,.
ResponderEliminarUn Cordial Saludo.
Excelente Muxas Gracias!!
ResponderEliminarKarMan: muchas gracias por tus mensajes. Hasta el momento, y con casi un año y medio de vida, el blog recibió poco mas de 42.000 visitas, según el servicio de FlagCounter que puse en el pié de página. No recibo muchos comentarios aquí, mas que nada las consultas me la hacen por mail o desde la página de Facebook (http://www.facebook.com/excel.maniacos). Ojalá con el tiempo aparezcan por aquí, como así también sumen al número de seguidores.
ResponderEliminarUn abrazo y gracias de nuevo