lunes, febrero 20, 2006

Usar Nombres en Excel para Formato Condicional

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 comentarios:

  1. ¿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...

    ResponderBorrar
  2. 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.

    ResponderBorrar
  3. 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.

    ResponderBorrar
  4. 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.

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

    ResponderBorrar
  6. 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.

    ResponderBorrar
  7. 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

    ResponderBorrar
  8. 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.

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

    ResponderBorrar
  10. 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).

    ResponderBorrar
  11. Hola,

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

    Muchas gracias!!

    ResponderBorrar
  12. Hola Cristian

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

    ResponderBorrar
  13. 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

    ResponderBorrar
  14. 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.

    ResponderBorrar
  15. 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.

    ResponderBorrar
  16. 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.

    ResponderBorrar
  17. 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!

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

    =LARGO(A1)=0

    ResponderBorrar
  19. 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,

    ResponderBorrar
  20. 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

    ResponderBorrar
  21. 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!

    ResponderBorrar
  22. 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).

    ResponderBorrar
  23. 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.

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

    =ESBLANCO(A1)

    o

    =LARGO(A1)=0

    ResponderBorrar
  25. 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.

    ResponderBorrar
  26. 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.

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

    ResponderBorrar
  28. En la opción Fórmula usar:

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

    ResponderBorrar
  29. 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?

    ResponderBorrar
  30. 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

    ResponderBorrar
  31. 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.

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

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

    ResponderBorrar
  33. 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..

    ResponderBorrar
  34. 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

    ResponderBorrar
  35. 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.

    ResponderBorrar
  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.

    ResponderBorrar
  37. Muchas, pero muchas gracias. Saludos.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.