martes, 1 de marzo de 2011

Formato condicional con fórmula en Excel 2007.

Para contestar la cuestión planteada en un comentario por un usuario deberemos recordar el uso del Formato condicional con fórmula. La pregunta es:

...Utilizo una sencilla tabla para llevar mis cuentas de casa. En la columna de la izquierda, una vez la oreración está realizada, le coloco un número que siempre va incrementándose de 1 en 1. Pues bien lo que me gustaría hacer es que aunque la tabla sea mayor (por debajo de la última fila numerada por mí hay otras filas rellenas pero sin numerar) el valor de la celda de la columna 'saldo' que corresponde a la misma fila que la última numerada por mí aparezca con un formato distinto pues se trata del saldo disponible....



Reproduciré en una tabla el ejemplo que plantea nuestro amigo:


La labor consistirá en encontrar con una fórmula la manera de identificar el registro del campo 'Saldo' correspondiente al último numerado del campo 'Num Oper'.
Para ello, antes de aplicar la fórmula conseguida al Formato condicional con fórmula, añadiremos una serie de columnas auxiliares que permitirán explicar el funcionamiento de nuestra fórmula.
Con la primera de nuestra columnas Auxiliares obtendremos una pista importante, a partir de qué valor del rango a estudio no hay numeración:
=SUMAPRODUCTO($A$2:A2)
fijémonos como la celda que cierra el rango $A$2:A2 es relativa y se mueve de acuerdo a nuestro copiado hacia abajo:


La siguiente columna auxiliar nos muestra el valor máximo del producto de los elementos del rango evaluado, o lo que es lo mismo, el valor numérico a partir del cual nuestro producto empieza a repetirse, esto es, nos dirá desde que punto no existen valores numerados:
=MAX(SUMAPRODUCTO($A$2:$A$8))


Si combinamos ambas auxiliares para ver en qué registros coinciden dichos valores obtendremos:
=E2=F2


Una última columna auxiliar para explicar el funcionamiento de nuestra fórmula:
=A2<>""
para cada registro, lo que nos dice si para ese elemento, del campo 'Num Oper' tenemos numeración.


Como podemos ver en nuestras columnas G y H, si cruzamos los resultados obtenidos sólo obtenemos una coincidencia, precisamente en el registro buscado.
Ya podemos, entonces, construir todo en una única fórmula:
=(SUMAPRODUCTO($A$2:A2)=MAX(SUMAPRODUCTO($A$2:$A$8)))*(A2<>"")


Damos el Formato condicional al rango C2:C8, desde Inicio > Estilos > Formato condicional:


Resultando lo esperado, sólo el último de los registros con numeración en el campo 'Num Oper' queda resaltado:

30 comentarios:

  1. hola tengo una duda en una tabla tengo distintos valores que son

    do pincht arreglo 2 pasos

    3/4 1 cuadrado 32
    3/4 1 cuadrado 52
    1 1 1/4 cuadrado 21
    1 1 1/4 cuadrado 32

    como puedo hacer para que al elejir do,pinch, arreglo y nt(calculados) me de una valor aproximado de nt parecido o igual al teorico que esta en tablas (2 pasos)

    ResponderEliminar
  2. Gracias por la ayuda, aunque nunca imaginé que iba a ser tan compleja.
    Ahora me pasaré un buen rato para entender qué has hecho pero ya te confirmo que me sirve.

    ResponderEliminar
  3. Respecto a la complejidad de la solución dad en esta entrada, seguro existen otras más sencillas, por ejemplo, empleando macros.
    Si te queda alguna duda de cómo o por qué he hecho algo coméntamelo a:
    excelforo@gmail.com

    Slds

    ResponderEliminar
  4. Hola,
    respecto a la forma de elegir do, pinch, arreglo y nt te contestaré a través del email que me enviaste.
    Slds

    ResponderEliminar
  5. Hola, tengo una pregunta necesito obtener valores secuenciales duplicados y que pueda buscarlo en esa secuencia en las demás filas, me explico con un ejemplo: en la fila 1 2 tenemos el 32 duplicado, pero quiero seguir obteniendo duplicado en secuencia es decir la fila 2y3 y 3y 4..... pero no se que formula emplear para que se respete esa secuencia y pueda obtener los duplicados, espero que me puedas ayudar y gracias de antemano


    1 1 12 15 26 32 40 18
    2 16 29 32 36 41
    3 2 10 19 26 32 38 17
    4 7 13 39 47 50
    5 8 17 34 39 44 47 13
    6 14 18 19 31 37
    7 6 7 8 19 22 35 17
    8 4 7 33 37 39


    Saludos
    Cecy

    ResponderEliminar
  6. Hola Cecilia,
    para comprobar qué elementos están duplicados o repetidos en filas continuas (1 y 2, 2 y 3, etc) deberás aplicar lo explicado en esta entrada
    http://excelforo.blogspot.com/2010/03/elementos-duplicados-en-campos.html
    Repitiendo el trabajo de dos en dos...
    Slds

    ResponderEliminar
  7. hola tengo una consulta como hago en este caso:
    País A favor En contra
    Paraguay 8 6
    Brasil 9 3
    Argentina 7 8
    Uruguay 11 0

    Aplique el formato condicional para asignarle color a los nombres de los
    paises que tienen menos goles en contra que a favor.

    ResponderEliminar
  8. Hola,
    primero seleccionas el rango de los paises y desde formato condicional tipo fórmula escribes (suponiendo que los paises estén en A2:A5):
    =$B2>$C2
    le das el formato personalizado de color que quieras y Aceptas.
    Espero te sirva.
    Slds

    ResponderEliminar
  9. hola, queria saber sobre formatos condicional pero relacionando dos hojas, es como un calendario de alertas. estoy media confundida, de antemano gracias :), xcelnte Blog eh.
    te lo mando via mail, las imagenes de el calendario (plantilla)
    saludos ,
    Vivian

    ResponderEliminar
  10. Como ejemplo de lo que se puede hacer en formato condicional está bien, pero se podía haber hecho mucho más sencillo con:

    =(A2<>"")*(A3="")

    o si queremos resaltar toda la línea podemos poner:
    =($A2<>"")*($A3="")

    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola!
      muchas gracias por el aporte... ciertamente más sencillo.
      Como bien dices se trata de aportar algo más en cada entrada, e intentar aprender aspectos nuevos.
      Sin duda tu manera es mucho más óptima para el trabajo.
      Un cordial saludo

      Eliminar
  11. Buen día, como puedo hacer que una celda me avise cuando le han quitado la formula?

    ResponderEliminar
  12. Buenos días,
    Mi problema es que al unir estas 2 fórmulas, me da error. Mi pregunta es.. ¿cómo lo hago?
    Gracias.

    =SI(Y(C6="T");SI(O(E6="ENERO";E6="ABRIL";E6="JULIO";E6="OCTUBRE");D6;0))

    =SI(Y(C7="C");SI(O(E7="ENERO";E7="MAYO";E7="SEPTIEMBRE");D7;0))

    ResponderEliminar
  13. Buenos dias de nuevo.
    Aclaro.. lo que quiero es unirlas para que se cumpla una u otra, no las dos a la vez.
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola!
      no sé si acabo de entender bien lo que quieres,
      pero prueba con
      =SI(Y(C6="C");SI(O(E6="ENERO";E6="MAYO";E6="SEPTIEMBRE");D6;0);SI(Y(C6="T");SI(O(E6="ENERO";E6="ABRIL";E6="JULIO";E6="OCTUBRE");D6;0)))

      así, creo se verifican las condiciones que quieres ¿¿¿???
      Slds

      Eliminar
  14. Hola,
    Muchas gracias, exactamente eso quería. Pero.. quiero poner más condiciones y me dice que son demasiadas. ¿hay alguna forma de hacerlo?
    De nuevo muchas gracias

    ResponderEliminar
    Respuestas
    1. ummm
      demasiadas?.Si trabajas con Excel 2003, puedes anidar hasta 7 condicionales, si trabajas con Excel 2007 o superior, hasta 64.

      En ambos casos, cuando la cosa se complica, lo mejor es crear tu propia función personalizada VBA.
      Echa un vistazo a
      http://excelforo.blogspot.com.es/2010/04/select-case-anidado.html

      Espero te oriente.
      Slds

      Eliminar
  15. Muchas gracias, una última duda. en esta función:
    =SI(Y(C6="C");SI(O(E6="FEBRERO";E6="JUNIO";E6="OCTUBRE");D6);SI(Y(C6="T");SI(O(E6="FEBRERO";E6="MAYO";E6="AGOSTO";E6="NOVIEMBRE");D6);SI(Y(C6="B");SI(O(E6="FEBRERO";E6="ABRIL";E6="JUNIO";E6="AGOSTO";E6="OCTUBRE";E6="DICIEMBRE");D6);SI(Y(C6="S");SI(O(E6="FEBRERO";E6="AGOSTO");D6);SI(Y(C6="A");SI(O(E6="FEBRERO");D6);SI(C6="m";D6))))))
    Si el resultado es FALSO, quiero que me aparezca el valor "0". Será una tontería pero.. ¿cómo lo hago?.
    De nuevo muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      así, a simple vista, al final de la fórmula:
      ....;SI(C6="m";D6;0))))))

      Slds

      Eliminar
  16. Hola,
    Necesito buscar en una lista de hipervínculos, los que contengan un determinado texto. Puede ser uno o varios, y que me los copie en otra columna. ¿Es posible?
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      lo más sencillo sería aplicar sobre ese listado de 'hipervínculos' (o de lo que sea), un filtro avanzado, aplicando en el rango de criterio esa condición, por ejemplo, el campo se llama 'Hiper', y quieres obtener los que contengan el texto 'excel', en este caso en rango de criterios sería:
      celda B1: Hiper
      celda B2: *excel*

      el la ventana de Filtro avanzado indica dónde quieres pegar (a partir de qué celda) el resultado filtrado.
      Slds

      Eliminar
  17. Hola,
    A través de una macro, genero una serie de hojas a partir de una plantilla.
    Lo que necesito es rellenar después cada hoja con unas descripciones que variarán en función de tres condiciones que son: tipo de máquina, mes y periodicidad (mensual, bimestral..).
    La cantidad de filas necesarias para cada caso es variable dependiendo de estas 3 condiciones.

    ¿Cómo lo hago?
    Muchas gracias por anticipado

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrías que aplicar un condicional IF ... THEN a esos tres criterios, para que en cada hoja , copia de esa plantilla original, complete en la hoja las descrpciones.
      Por ejemplo,
      IF condicion1 AND condicion2 AND condicion3 THEN
      Range(celda_descripcion1).value = "Descripción1"
      Range(celda_descripcion2).value = "Descripción2"
      Range(celda_descripcion3).value = "Descripción3"

      Con los datos tan generales que aportas poco más te puede indicar...
      Slds

      Eliminar
  18. Lo que tengo es varios listados de operaciones. Dependiendo del tipo de máquina, tengo que elegir la lista de operaciones adecuada, y dependiendo del mes y periodicidad, las operaciones dentro de esa lista. (gracias por la rápida respuesta)

    ResponderEliminar
    Respuestas
    1. En ese caso, parece más un tema de filtro avanzado (o incluso autofiltro).
      Si me apuras no sería necesario ni aplicar macros.
      Slds

      Eliminar
  19. Hola,
    Tengo una duda, seguramente básica, pero que no consigo solucionar.
    Tengo utilizar el formato condicional para que los datos de una columna resalten cuando les correspondan el máximo y el mínimo de la columna de al lado.
    No se si me he explicado bien.
    Un saludo y gracias

    ResponderEliminar
    Respuestas
    1. Hola!
      supongamos tienes los datos en las columnas A y B, y quiers dar formato a la columna B, según el valor máximo correspondiente de la columna A.
      Entonces, seleccionas el rango en la columna B, por ejemplo, B1:B100, y con la celda activa B1, accedes al Formato condicional tipo fórmula e introduces:
      =$A1=MAX($A$1:$A$100)

      E igual para el mínimo, en otra regla de formato condicional
      =$A1=MIN($A$1:$A$100)

      Prueba y comentas
      Slds

      Eliminar
  20. tengo la fila b3:b102 con datos alumno00 hasta alumno99, en columna z3:z102 tengo valores de ok para aprobado y no para reprobado- necesito que segun los valores de z en b aparezca color verde para aprobados y color rojo reprobados ...no lo se hacer. gracias

    ResponderEliminar
    Respuestas
    1. Hola, que tal estás?.. espero te encuentres bien.

      Para dar formato condicional basado en valores de otras celdas, selecciona primeroel rango que quieras formatear, esto es, poner en rojo o verde. En tu caso rango B3:B102
      A continuación entra en Formato condicional tipo fórmula y asegurándote que tienes seleccionado todo el rango, tu celda activa es B3, y en la venta diálogo del FC escribe:
      =$z3="ok"
      luego le das el formato que quieras ( fondo celda verde).
      para el verde repite la operación, en la fórmula escribe:
      =$z3="no"
      y le das el formato( fondo celda rojo).
      Espero lo veas claro
      Slds cordiales

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.