Usar Nombres en Excel para Formato Condicional

lunes, febrero 20, 2006

Ayer hablamos sobre el uso de "nombre" en Excel. En esa nota decía que el uso de nombres nos permite realizar operaciones que de otra manera no serían posibles.
Veamos un ejemplo. Una de las funcionalidades que empleo a menudo en mis modelos es el "Formato Condicional".





Un ejemplo puede verse en esta nota sobre comparación entre dos listas. En ese ejemplo usamos una fórmula para condicionar el formato.
Ahora supongamos que queremos condicionar el formato de una celda de acuerdo al resultado de una celda que se encuentra en otra hoja. En este ejemplo tenemos en la Hoja1 los totales de ventas para el primer trimestre del año de nuestros agentes de ventas.

En la Hoja2 tenemos las ventas por mes y por agente


Para verificar que no falten datos mensuales queremos hacer un formato condicional en la Hoja1 que de un fondo rojo cuando falte algún dato de ventas de algún agente. Para hacer esto tenemos que detectar si hay alguna celda vacía en el rango B2:D6. La forma de verificar esto es con la función CONTAR. Por ejemplo, si CONTAR(B2:D2) da un resultado menor de 3, señal que falta algún dato. Ahora lo que tenemos que hacer es aplicar un formato condicional en la celdas con los nombre de los agentes, de manera que si falta algún dato reciban un fondo rojo.

Como veremos Excel no nos permitirá hacer esto:
Seleccionamos la celda A2 en la Hoja1, entramos en el menú Formato--->Formato Condicional



En al ventanilla de la condición escribimos la formula =CONTAR(Hoja2!B2:D2)<3>





Elegimos la trama roja, pero cuando apretemos "aceptar" recibiremos esta nota

Excel no nos permite usar referencias a otras hojas en el formato condicional.

La forma de superar este problema es usando nombres. Veamos primero la variante sencilla. Seleccionamos la Hoja2, seleccionamos el rango B2:D2 y en el cuadro de nombres escribimmos el nombre "agente1".

Ahora volvemos a la Hoja1, abrimos el menú Formato--->Formato Condicional, pero usaremos la fórmula =CONTAR(agente1)<3>

Como pueden ver, Excel acepta esta notación y podemos ver que para el Agente 1 nos faltan datos de ventas en la Hoja2.

Una variante un poco más compleja es incluir toda la fórmula en el nombre:

Esta misma técnica la podemos utilizar para superar un problema similar que existe en la Validación de Datos. Tampoco aquí Excel permite referencias a otras hojas, excepto que estas referencias estén incluidas en nombres.


Categorías: Funciones&Formulas_, Manejo de Datos_



Technorati Tags:,

38 comments:

Luis Zavaleta,  14 mayo, 2007 18:03  

¿Como puedo hacer para asignar formato condicional a celdas vacias dentro de una misma hoja?
Tengo una planilla donde algunas celdas deben llenarse obligatoriamente y me gustaria resaltar las que estan vacias. El problema es que el numero cero en un dato valido, pero cuando le doy formato condicional me toma el cero como vacio...

Jorge L. Dunkelman 14 mayo, 2007 21:34  

Hola Luís, no estoy seguro sientiendo tu pregunta. Si quieres que las celdas vacías reciban un formato condicional debes usar la opción Fórmula y poner ésta:
=ESBLANCO(A1)
Fijate que la referencia a A1 es relativa, es decir, sin los símbolos "$", caso contrario siempre se referirá a A1.
Para Excel una celda con valor 0 no es una celda vacía.

HECTOR 18 mayo, 2007 04:53  

Hola, es posible utilizar NOMBRES con CONDICIONES ?? por ejemplo:
=SI(A3=datos;"ok";"control")
el nombre Datos contiene numeros y la celda A3 puede o no tener esos numeros.

Jorge L. Dunkelman 18 mayo, 2007 07:00  

Hola Hector
si, pero no de la forma que pones en tu pregunta.
Supongamos que el nombre Datos define el rango $A$1:$A$4 donde tienes los números 100, 200, 300 y 400 y en la celda B2 tienes el número que quieres comparar. Tu fórmula tendía que ser
=SI(ESERROR(COINCIDIR(B3,Datos,0));"control";"OK")
Es decir, primero buscamos si el número en la celda a controlar existe en la matriz de números; esto es lo que hace COINCICDIR. Si COINCIDIR da error (ESERROR) quiere decir que el número no está en la matriz.

Anónimo,  20 julio, 2007 20:31  

Hola.
Me pregunto si me pueden ayudar a dejar definitivos los formatos condicionales de una hoja de cálculo.
De antemano gracias.

Jorge L. Dunkelman 21 julio, 2007 00:49  

Al remover la condición, también el formato desaparece.
Lo único que se me ocurre, es darle el mismo formato manualmente y luego eliminar el formato condicional.

Anónimo,  11 agosto, 2007 21:39  

HOLA MI NOMBRE ES SELENE Y ME DA GUSTO EXISTAN ESPACIOS PARA LA PROGRMACION EN EXCEL, ME GUSTARIA SABER SI ME PUEDEN AYUDAR.
YA HE UTILIZADO EL FORMATO CONDICIONAL CON LOS VALORES DE LA CELDA Y ME HA FUNCIONADO BIEN.

PERO AHORA ME ENFRENTO CON UN PROBLEMA MAYOR NO PUEDO DETERMINAR COMO PONERLE FORMATO CONDICIONAL A NA CELDA.

TENGO UNA TABLA Q VA DEL NIVEL 0 AL 6 Y DEL A AL E. Y ARROJAN LOS VALORES SEGUN DONDE SE ENCUENTREN A1 A2,D4,D5, E5, ETC.

EL CHISTE Q EN LA CELDA DONDE DA ESTE VALOR TENGO Q INDICARLE Q SE PONGA SEGUN COLOR DE SEMAFORO PERO COMO FORMATO CONDICIONAL SOLO PERMITE 3, Y CON LA FORMULA ME MANDA ERROR DE REFERENCIA NO SE Q HACER.

QUEDARIA ASI
VERDES
A0-A4
B0-B3
C0-C2
D0-D1
E0-E1

AMARILLOS
A5
B4-B5
C3-C4
D2-D3
E2

ROJOS
C5
D4-D5
E3-E5

COMO PUEDO PLAICARLE A LA CELDA DONDE DA ESTE VALOR CON FORMATO CONDICIONAL MEDIANTE FORMULA.

MUCHAS GRACIAS SI ME PUEDEN AYUDAR

Jorge L. Dunkelman 11 agosto, 2007 23:54  

Hola Selene
para usar Formato COndicional con más de 3 condiciones puedes usar el complemento desarrollado por Frank Kabel y Bob Phillips que explico en esta nota.

Anónimo,  17 septiembre, 2007 05:53  

como puedo hacer para que una celda cambie a color rojo cuando no tiene texto, siendo que en esta celda existe una formula

Jorge L. Dunkelman 12 octubre, 2007 22:43  

Usando como condición la fórmula
=LARGO(A1)=0
Usar LARGO es por lo general más conveniente que usar ESBLANCO, justamente por los casos como en tu ejemplo, donde la celda no ehibe contenido pero de hecho contiene una fórmula (que da un resultado en blanco).

Cristian,  21 noviembre, 2007 10:58  

Hola,

¿se puede usar el Formato Condicional para un valor de otro archivo .xls?

Muchas gracias!!

Jorge L. Dunkelman 21 noviembre, 2007 20:03  

Hola Cristian

no, no se puede. Tal vez haya algún rodeo, pero no lo conozco. Será investigado.

Anónimo,  05 febrero, 2008 13:01  

Estoy tratando de automatizar una hoja de cálculo cuyo objetivo final es el de generar un informe. En este informe se incluye una gráfica en función del tiempo (semanas) para el eje "X", y para el eje "y" los valores que la gráfica debe tomar dependen de una fórmula. El problema es que cuando en las celdas se coloca la fórmula la gráfica lo toma como valor cero y aparecen ceros en la gráfica donde no se espera nada. Si se elimina o borra la fórmula la gráfica refleja las líneas correctamente.
La pregunta es: ¿cómo hago para que la gráfica tome las celdas con fórmula como si estuvieran vacías, en lugar de interpretarlas

Anónimo,  02 julio, 2008 19:05  

Hola,
cuando copio una hoja me aparece una advertencia de duplicacion de nombre. He eliminado todos los nombres de la hoja original, pero aun asi siguen apareciendo al copiar. ¿como puedo saber donde estan almacenados esos nombres? ya revise en "Insertar > Nombre > Definir" y ahí no estan.

Jorge L. Dunkelman 02 julio, 2008 19:23  

Hola

Excel tiene nombres "reservados" o ocultos que no son vistos en el diálogo de definir nombres. Puede darse que por casualidad le has dado a uno de tus nombre uno de esos nombres reservados.

Andrea 09 julio, 2008 19:40  

Hola, tengo un problemita y me gustaría que me ayudaras a solucionarlo, quiero que en toda una fila se pongan las letras rojas si en las celdas de la fila anterior los valores son mayores, es decir que cada celda de una fila se compare con su respectiva celda de arriba y si el valor es menor entonces que los valores aparezcan en color rojo, por favor ayudame con este inconveniente, gracias.

Octavio Hernández 08 marzo, 2011 20:07  

Hola Jorge,
Buen dia, muchisimas gracias por todas tus aportaciones, quiero hacerte una consulta sobre formato condicional, tengo que hacer una tabla en donde vienen varios tipos de aceites distintos, tengo el valor minimo y maximo de inventario que deben de tener y semanalmente se hacen revisiones de cada nivel de aceite, para detectar niveles muy bajos o muy altos en el inventario de cada aceite, quise poner un formato condicional para que si el valor del inventario es igual o menor al minimo la celda se ponga de color rojo, si esta entre los niveles minimo y maximo sea verde y si esta por arriba del nivel maximo se ponga azul,mi problema es que como excel toma las celdas vacias como cero, me pinta todas las celdas vacias en rojo, hice una prueba para que si el valor de la celda es (="")pinte la celda de blanco, pero entonces cuando el inventario es 0 tambien lo pone en blanco, cuando lo necesito en rojo, estoy usando excel 2003, hay alguna manera de resolver esto?

Gracias de antemano por tus atenciones, saludos!

Jorge L. Dunkelman 09 marzo, 2011 20:04  

Para establecer si la celda no contiene ningún valor (vacía) tienes que usar la condición

=LARGO(A1)=0

Anónimo,  11 octubre, 2011 02:23  

HOLA HABRA UNA FORMULA PARA DESCONTAR LOS DOMINGOS,
EJEMPLO
FECHA DE ENVIO 01/10/2011
DIAS DE TRANSLADO
*MICH=2
*BCN=3
*SIN=4
EL RESULTADO SERIA:
01/10/2011+2=04/10/2011
01/10/2011+3=05/10/2011
01/10/2011+4=06/10/2011

SALUDOS CORDIALES,

Jorge L. Dunkelman 11 octubre, 2011 10:16  

Tu comentario no está relacionado con el tema de la nota.
Si tienes Excel 2010 puedes usar DIAS.LAB.INTL
Para versiones anteriores fijate en esta nota

Anónimo,  26 octubre, 2011 16:55  

Buenas tardes, antes de nada gracias por su ayuda, tengo otra duda, tengo una hoja donde muchas de las formulas son inputs de otra pestaña llamada "assumptions" de tal forma que muchas celdas se encuentran formuladas =Assumptions!G738....

¿Se puede poner un formato condicional para que cuando una celda venga de la pestaña Assumptions se vea de otro color? Estoy intentando la opcion "aplicar un formato a las celdas que contengan- Texto especifico-Assumptions pero no me hace nada....

Gracias!

Jorge L. Dunkelman 28 octubre, 2011 11:05  

Tendrías que crear una fórmula que extraiga la referencia de la celda, no el valor. Se puede hacer con una UDF (función definida por el usuario, macro).

Anónimo,  20 diciembre, 2011 18:51  

Hola, me gustaría saber como se puede aplicar un formato condicional a una celda que no contiene nada, a ver, me explico, dispongo de una tabla, en la que la condición que tengo que aplicar es que si tiene texto se ponga de color verde, y si está en blanco, de color rojo. Muchas gracias.

Jorge L. Dunkelman 21 diciembre, 2011 07:15  

Suponiendo que queremos aplicar el formato a A1, con la opción Fórmula, usar

=ESBLANCO(A1)

o

=LARGO(A1)=0

Anónimo,  19 enero, 2012 19:35  

Hola: tengo una duda, como aplico un formato condicional asociando a un color un texto y que cuando ordeno por alguna columna, se quede fijado el color al valor y se mueva con el y no se quede fijo a la celda. De antemano muchas gracias.

Jorge L. Dunkelman 20 enero, 2012 13:36  

Estimado,
el formato condicional está siempre ligado al valor de la celda, no a la celda en si misma. Por lo tanto, siempre se mueve con el valor.

Anónimo,  13 marzo, 2012 21:19  

hola quiero usar formato condicional para porner aletar cuando ya se pasan de la fecha establecida ejempo 120 días

Jorge L. Dunkelman 14 marzo, 2012 07:02  

En la opción Fórmula usar:

=SIFECHA([celda con la fecha a comparar];HOY();"d")>120

Anónimo,  01 junio, 2012 21:08  

Hola, aplique formato condicional en una columna de saldos. Si es potivo aparece en rojo, si es negativo o cero aparece en verde. El tema es que las celdas vacias tambien aparecen en verde, como puedo hacer para que queden blancas?

Jorge L. Dunkelman 02 junio, 2012 10:26  

Tienes que combinar la condición que la celda contenga un número. Sería

=Y(A1>=0;ESNUMERO(A1)) para los positivos y

=Y(A1<0;ESNUMERO(A1)) para los negativos

Joan 26 julio, 2012 18:34  

Buen día tengo un problema, tengo una lista de 10 datos lo que necesito es colocar en rojo las celdas que contengan numeros mayores que 100 y menores que 100 sin incluir el cero. Me podrían ayudar de antemano muchas gracias.

Jorge L. Dunkelman 26 julio, 2012 22:01  

O sea, todo número que sea distinto de 100 y distinto de 0.

Formato Condicional-Fórmula: Y(A1<>100,A1<>0)

Anónimo,  27 noviembre, 2012 15:30  

Hola..necesito ayuda..
Tengo una tabla excel con muchos datos, y una de sus columnas son vto de fechas.
Necesito que las fechas su vto sea 180dias me aparezcan en rojo, las ya caducadas por que son antiguas en naranja y las celdas vacias xq no tienen fecha vto se queden en blanco.
Llevo tres dias con lo mismo y no logro acerlo bien..

Jorge L. Dunkelman 28 noviembre, 2012 07:15  

El formato condicional, usando la opción fórmula, tendría que ser, suponiendo que el plazo de pagos esta en las celdas de la columna B y la fecha de vencimiento en las celdas de la columna C

para las celdas que con vencimiento 180 días:
=B2=180

para las caducadas
=HOY()-C2>180

Diego 12 febrero, 2014 20:54  

Hola, una consulta: si;
en la hoja1, celda A1 tengo el titulo Jugadores de la fecha desde / / hasta / / .
en la hoja2, celda A1 tengo el titulo Entrenadores de la fecha desde / / hasta / / .
en la hoja3, celda A1 tengo el titulo Cuidadores de la fecha desde / / hasta / / .
en la hoja4, celda A1 tengo el titulo Utileros hasta la fecha / / .

Como puedo hacer para poner la fecha en la Hoja1, celda A1 y que se me repita en todas dado que hay un texto diferente en cada hoja pero la fecha es la misma. Y, en la ultima hoja va una sola fecha. Todo esto sin utilizar una celda auxiliar sino solo en A1.

Dede ya muchas gracias.

Jorge Dunkelman 13 febrero, 2014 08:36  

Digamos que en la celda Z1 ponés la priner fecha y en Z2 la segunda. Luego, en las celda A1 de la hoja 1 ponés

="Jugadores de la fecha desde "&TEXTO(Z1,"dd/mm/aa")&" hasta "&TEXTO(Z2,"dd/mm/aa")

Lo mismo para el resto de las hojas.

Diego 13 febrero, 2014 14:04  

Muchas, pero muchas gracias. Saludos.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP