Formato condicional con iconos en Excel 2007

lunes, febrero 09, 2009

Sin lugar a dudas, las posibilidades del formato condicional en Excel 2007 han sido ampliadas enormemente. Sin embargo, algunas de las posibilidades están lejos de ser intuitivas.

Por ejemplo, el uso de las colecciones de iconos. Supongamos, como me plantea un lector, que tenemos una lista de fechas de vencimientos. Queremos sacar partido de los semáforos del formato condicional de Excel 2007 de manera que si la fecha del vencimiento cae más de 30 días de la fecha corriente aparezca un semáforo verde; si la fecha está dentro de los 30 días un semáforo amarillo y si la fecha ha vencido, un semáforo rojo. Es decir, esto:




Empecemos por agregar una columna auxiliar que muestre la diferencia en días entre la fecha corriente y la fecha de vencimiento



Para poner los semáforos en esta columna abrimos el menú de Formato Condicional –Nueva regla, elegimos el conjunto de iconos (semáforo con recuadro), en Tipo elegimos Número, para el semáforo verde ponemos “>30” y para el amarillo “>=0”. Esto hace que el rojo quede para los valores inferiores a 0. Como dije al principio, no muy intuitivo que digamos



El resultado será



Una posibilidad interesante es editar la regla y marcar la opción “Mostrar icono únicamente”



Pero si queremos prescindir de la columna auxiliar tendremos que definir las reglas del formato condicional de esta manera



EL primer cambio es que definimos el tipo de regla como Fórmula; en Valor creamos una referencia a la celda que contiene la fecha corriente ($B$1 en nuestro caso) + 30 para el semáforo verde y $B$1 para el amarillo (el rojo queda definido por defecto)





Technorati Tags:

50 comments:

Anónimo,  10 febrero, 2009 17:26  

Te felicito por tu blog, realmente es increible y sobre todo las expicaciones paso por paso.

Anónimo,  15 febrero, 2009 11:50  

Muchísimas gracias por tu ayuda, a partir de ahora me convertiré en un asiduo lector de tus comentarios.

Anónimo,  13 abril, 2009 19:20  

Jorge.
Muy buen tip el del formato condicional, en mi caso me he topado con un problema uso la version 2003 y solamente me permite 3 condiciones cuando en lo particular necesito 4, hay manera de agregar esa 4ta condicio??.
Felicidades por tu Blog.
Saludos.

Jorge L. Dunkelman 13 abril, 2009 20:05  

Podés usar el complemento que menciono en esta nota.

Rosales Paz 11 diciembre, 2009 04:21  

HOla, genial tu comentario, pero yo tengo un problema un poco máyor. Uso excel 2007 y necesito un semaforo de 4 colores, se puede? por favor ayudame

Jorge L. Dunkelman 11 diciembre, 2009 08:43  

Hola,
en la ventana Nueva Regla de Formato, en la parte inferior a al derecha en la ventanilla Estilo de Icono podés elegir la opción "4 semáforos".

Anónimo,  31 marzo, 2010 02:58  

Tengo una duda, que pasa si necesito 4 semaforos pero quiero que el 100% sea azul, del 99 al 96 verde, del 95 al 91 amarillo y menor o igual a 90 rojo.

Porque no he podido personalizar los colores

Jorge L. Dunkelman 03 abril, 2010 14:50  

No se pueden personalizar los iconos.

Anónimo,  19 setiembre, 2010 10:20  

Genial explicación, pero y si quieres usar < que en vez de mayor que? de modo que los negativos que exceden se muestren en verde

Lo he intentado invirtiendo criterio y también multiplicando -1.
*no es el mismo caso, pero tiene relación

Jorge L. Dunkelman 19 setiembre, 2010 19:10  

Si ya se ha definido la regla, se entra en Editar regla de formato, en la línea inferior del formulario chequeamos la opción "Invertir criterio de ordenación de icono". Eso es todo.

Anónimo,  19 setiembre, 2010 22:34  

No lo hace correctamente pues los valores son negativos y no puedo indicar el sentido contrario del valor.

Por ejemplo.

de -500 en adelante (-1000 por ejemplo) Verde
de -500 a -100 Naranja
de -100 a -1 Rojo
>= 0 Negro

Jorge L. Dunkelman 20 setiembre, 2010 07:11  

Tienes que hacer lo siguiente:

1 - elegir la opción de 4 semáforos (obviamente)
2 - el primer criterio (para la señal negra) es ">= 0"
3 - el segundo criterio ">=-100" (o >-500)
4 - el tercer criterio ">=-500"
5 - marcar la opción de sentido inverso

Por defecto, todo lo que es menor de -500 recibirá la señal verde.

Anónimo,  20 setiembre, 2010 22:16  

muchas gracias, funciona correctamente, el error lo tenia en el primer criterio 0 pues lo colocaba en el ultimo y aparte entenida como mayor hacia el 0 y no al revés, execel lo interpreta bien yo lo resolvía mal.

Muchas gracias!

Gauss 21 octubre, 2010 16:16  

Disculpame, quisiera usar los íconos de colores pero solamente el verde y el rojo. Es decir, si un número es >0 entonces flecha verde, si es menor o igual entonces roja. Sino podría ser que si es =0 sea amarilla, eso no sería problema.

Los números con los que estoy tratando son % de variaciones.

Muchas gracias por adelantado!!

Jorge L. Dunkelman 21 octubre, 2010 19:46  

En tu ejemplo (sólo dos colores) tendrías que hacer lo siguiente:
en el menú de las reglas eliges el estilo "conjunto de iconos" (obviamente); para el verde usas "cuando el valor es >=", en la ventanilla Valor pones 0 y en tipo "número";
en la segunda línea pones ">" en la ventanilla de los símbolos, 0 en "Valor" y "número" en "Tipo".
Esto hara que en toda celda que contenga una valor igual o mayor que 0 aparezca un icono verde; si el valor es menor que 0, un icono rojo.

Anónimo,  17 noviembre, 2010 00:50  

Hola Jorge una pregunta.. yo estoy tratando de usar el formato condicional con iconos, pero uso el de los circulos que se van llenando en partes de 25% del circulo.
Mi problema es que hago referencia al valor de la celda en porcentaje pero no me da el dibujo adecuado, es decir.. escribo un valor de 26% y me da el dibujo del 25%, hasta el momento todo bien y lo entiendo, pero por ejemplo, escribo 50% y dibuja el 75%, escribo 90% y me da el 100% dibujado. Las reglas son muy claras, ya las especifique... donde crees que esté el error?

Jorge L. Dunkelman 17 noviembre, 2010 18:29  

Enviame el archivo con el que estás trabajando (fijate en la pestaña Ayuda en el parte superior del blog).

Jorge L. Dunkelman 30 diciembre, 2010 20:20  

Vicente,
tendrías que usar una fórmula que combine las dos condiciones con la función O. Supongamos que Si/No aparece en la columna C y los días al vencimiento en la columna D, la fórmula sería:
O(C2="Si",C2>30)

Anónimo,  18 enero, 2011 20:11  

Sos un groso!

Jorge L. Dunkelman 19 enero, 2011 08:40  

Gracias. Sólo para estar seguro, ¿groso es algo bueno? :)

Maritza 15 abril, 2011 23:45  

Hola Jorge, estoy tratando de hacer un formato condicional de 3 colores, pero no me toma los colores con exactitud, tengo datos que van del 100% al -8%, en una tabla de 25x25 app., bueno mis condiciones son que pinte verde si >=6%, amarillo <6%>=0 y lo negativo quedaria en rojo, pero lamentablemente todo lo que es negativo me lo deja en amarillo y lo que debiese ser amarillo me lo deja en verde, cual puede ser mi error???
Espero tu respuesta, desde ya te lo agradezco

Jorge L. Dunkelman 16 abril, 2011 14:22  

Supongamos que estás aplicando el formato según el valor de la celda usando el conjunto de iconos de tres colores, las reglas tendrían que ser:

verde--> cuando el valor es >= 0.06 (tipo: número)

amarillo--> cuando <0.06 y > 0 (tipo: número)

rojo--> cuando <=0

Las reglas "cuando <0.06" y "cuando <=0" aparecen automáticamente según los valores que ponemos en las ventanillas "Valor".

Nuria 05 mayo, 2011 11:32  

Hola, Jorge:
Me parecen muy claras tus explicaciones. Gracias por tu trabajo.

Me gustaría saber si de alguna manera (a través de alguna carpeta de archivos de programa por ejemplo...) se pueden agregar iconos diseñados previamente y luego escogerlos para el formato condicional (hace tiempo decías que no se podían personalizar los iconos, lo que no sé es si se pueden añadir nuevos, como por ejemplo se añaden viñetas).
Muchas gracias:
Nuria

Jorge L. Dunkelman 05 mayo, 2011 19:16  

Nuria,

hasta donde conozco, no se puede agregar iconos personalizados a la colección de iconos del formato condicional.

Anónimo,  25 mayo, 2011 22:26  

Jorge te agradezco de antemano la ayuda estoy intentando usar los semaforos pero nunca me sale el de color amarillo, solo es para cuantificar el gasto de materiales. es decir rojo si >80%, amarillo si 25% a 80% y verde <25, me para los % entr 20 y 80 siempre me sale rojo, mahler3204@hotmail.com

Jorge L. Dunkelman 27 mayo, 2011 11:47  

Te mando la respuesta por mail privado.

Anónimo,  23 junio, 2011 17:40  

HOLA!!
Tengo una pregunta sobre los formatos condicionales:
Quiero utilizar conjuntos de iconos de la siguiente manera:
1.Valores entre 0 y 4: flecha verde.
2.Valores entre 4 y 10: flecha naranja
3. Resto de valores, bien por debajo de 0 o por encima de 10: flecha roja

¿¿Se puede hacer?? Es que no encuentro ninguna fórmula...

Un saludo y gracias

Jorge L. Dunkelman 16 julio, 2011 11:57  

Llevó algo de tiempo, pero acabo de publicar esta nota en respuesta a tu consulta.

Anónimo,  20 agosto, 2011 23:44  

Formidable ayuda, muchas gracias

Anónimo,  12 marzo, 2012 15:25  

DE MUCHA UTILIDAD MUCHAS GRACIAS ...

NELSON RICARDO BELTRAN MOJICA 10 abril, 2012 02:03  

en excell es custion de imaginación, por ejemplo con formato condicional se sombrean celdas con el color que se guste, en la celda se obtiene el indicador, se coloca en formato condicional si es mayor, igual, menor, etc al valor obtenido, se le da un formato condicional, hacerlo de color y letra, etc, una vez se obtienen las celdas puede colocar encima una forma, una imagen... lo que quieran, así se obtiene el semáforo al gusto.

Àmbit de Comunicació - Creu Roja a Girona 24 julio, 2012 00:50  

Saludos,

Tengo que comparar varios datos digamos c1,c2,c3 de los años 2011 y 2012.
Como se deberia hacer para utilitzar los iconos de flechas para indicar que si ha augmentado disminuido o se ha quedado igual las casillas c1,c2,c3 del año 2012 respeto a las 2011

Jorge L. Dunkelman 24 julio, 2012 11:18  

Tendrías que usar la técnica ue muestro en esta nota.

Anónimo,  25 agosto, 2012 19:14  

Excelente Block, te haces entender; te tengo un planteamiento de mi problema,creo que es sencillo pero no lo se hacer, tengo una fecha de emisión de factura y tengo condiciones de pago de acuerdo al cliente que van desde 7, 15 y 30 dias, quisiera que excel me calculara la fecha de vencimiento y con color y palabra vencida y no vencida. Ejemplo fecha emision 16/08/2012 condicion 15 dias

Jorge L. Dunkelman 26 agosto, 2012 12:17  

Estimado, la técnica para calcular los vencimientos está explicada en la nota, lo mismo que la forma de aplicar formato condicional. Para que aparezca "vencida" o "no vancida" hay que usar una fórmula consicional.

Mario Perez 28 agosto, 2012 21:03  

Cuando tengo una duda de Excel, este blog es mi primera opcion. Gracias

Jorge L. Dunkelman 28 agosto, 2012 21:22  

Por nada. Me alegro que el blog te resulte útil.

MALVI MARCANO 07 setiembre, 2012 02:16  

Excelente! Tengo planeado crear un Blog destinado a Excel para principiantes...! Excel es maravilloso! Tan complejo como hermoso... ;)
Bravo! Ojalá nunca falten emprendedores como tú!!

Anónimo,  22 junio, 2013 17:46  

Hola, quiero usar formato condicional (semáforo) para unos indicadores que estoy manejando, el indicador que se debe cumplir es T=13 días

- Si F3<13 verde
- si F3=13 amarillo
- si F3>13 rojo
Por favor ayúdenme es urgente para mi trabajo muchas gracias

Anónimo,  26 noviembre, 2013 19:51  

en la version de excel que tengo no me da la posibilidad de aplicar nuevas reglas cuando selecciono la pestana de formato conditional. Podrias ayudarme?
Gracias
Ricardo

Jorge Dunkelman 26 noviembre, 2013 21:06  

¿Qué versión de Excel estás usando?

Anónimo,  27 noviembre, 2013 23:57  

una consulta mi duda es si puedo usar los iconos en condiciones como esta

=Si(igual (VAL1,VAL2),"ICONO VERDE", "ICONO ROJO")

Jorge Dunkelman 28 noviembre, 2013 16:58  

Bien, Excel no conoce fórmulas de ese tipo. Toda fórmula que uses en formato condicional (o en validación de datos) debe dar como resultado VERDADERO o FALSO.
Si das una descripción de qué es VAL1 y VAL2, podría orientarte.

CORMENES 26 marzo, 2014 19:08  

no logre ver el formato, pero la explicacion esta super bien,,, te felicito

Anónimo,  29 julio, 2014 03:17  

Gracias, fue de mucha utilidad tu ayuda...
hice un semaforo para seguir unas compras y quede como un As de excel... GRAx..

Anónimo,  02 octubre, 2014 01:03  

Estimado,

Yo quiero usa otros condicionales como una flecha roja hacia arriba o una flecha verde hacia abajo.

Estas flechas se pueden importar y colocar como formato condicional?

Muchas gracias.

Saludos

Jorge Dunkelman 02 octubre, 2014 07:39  

Sólo se pueden usar los iconos de Excel, no existe la posibilidad de importar iconos de fuentes externas. Una alternativa a lo que consultas es usar la técnica que muestro en esta nota.

Anónimo,  12 noviembre, 2014 21:08  

Excelente tutorial. Intenté hacer lo mismo pero mi caso es el siguiente:
- Estoy haciendo una hoja de calculo para saber a que clientes debo llamar en X fecha.
- He intentado hacer que, cuando esté llegando la fecha se ponga el semáforo verde, cuando llegue la fecha que se ponga el semáforo amarillo y cuando me pase de la fecha se ponga el semáforo rojo.

Quisiera saber si me podrías ayudar por favor.

Jorge Dunkelman 15 noviembre, 2014 18:08  

¿Intentaste aplicar la técnica que muestro en la nota?

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP