viernes, 23 de marzo de 2012

Condiciones o Pruebas lógicas en funciones de Excel.

Estoy firmemente convencido de que nunca nos acostaremos sin aprender algo nuevo; y que por mucho que pensemos que sabemos sobre algo, siempre descubriremos lo equivacados que estamos... Y eso precisamente me pasó hace algunos días.
Siempre había pensado, y por tanto trabajado sobre esa hipótesis, que las pruebas lógicas o condiciones que aplicaba en las fórmulas o funciones, debían responder a un 'VERDADERO' o 'FALSO', a un 'SI' o un 'NO', a un 'CUMPLE' o 'NO CUMPLE', a un '0' ó a un '1'; sin embargo, con gran sorpresa por mi parte, me demostraron cómo Excel reconoce como CIERTO cualquier valor mayor a cero, y como FALSO cuando es cero... Situación muy útil para encontrar valores o texto dentro de celdas, por ejemplo.

Plantearemos la siguiente situación. Queremos que una fila de una base de datos aparezca en verde cuando una celda concreta contenga un texto específico.
Por poner un ejemplo concreto, queremos que si en la celda B2 aparece el texto 'excelforo' toda la fila 2, cambie su fondo por el color verde.
Para ello, a la hora de aplicar el Formato condicional tipo Fórmula, sólo deberemos insertar la condición:
=HALLAR("ExcelForo";$B2)

Condiciones o Pruebas lógicas en funciones de Excel.


Tras aplicar este Formato condicional a la fila 2: $2:$2:

Condiciones o Pruebas lógicas en funciones de Excel.


Comprobamos como efectivamente, Excel entiende que ha encontrado el 'texto buscado', y que al ser CIERTO cambia el formato de las celdas de la fila en cuestión:

Condiciones o Pruebas lógicas en funciones de Excel.

Vemos en la imagen como en la celda B2 aparece el texto buscado, y por tanto, cambia el color de la fila; y como en B3, no se produce tal cambio, al ser el texto diferente.


Sobre las mismas celdas, también podemos ver la manera de trabajar, en este sentido, de Excel; en concreto aplicando una condicional SI.
Al construir, en la celda B5, la función: =SI(HALLAR("excelforo";$B2);"contiene";"no contiene"), e incluir como Prueba lógica la función HALLAR("excelforo";$B2), responde como esperábamos, es decir, al devolver la función HALLAR un valor superior a cero (en el ejemplo 12), entiende como VERDADERO el resultado de la condición, por lo que actúa en consecuencia, devolviendo el argumento VERDADERO de la función SI:

Condiciones o Pruebas lógicas en funciones de Excel.


Para salvar el ERROR que devuelve HALLAR cuando no encuentra el 'texto buscado', anidaremos nuestra anterior función en un SI.ERROR:
=SI.ERROR(SI(HALLAR("excelforo";$B3);"contiene";"no contiene");"no contiene")

Condiciones o Pruebas lógicas en funciones de Excel.


Pues esto han sido dos ejemplos de cómo actua Excel frente a condiciones o pruebas lógicas con valores diferentes de VERDADERO o FALSO. También he probado con éxito esta ventaja en los Filtros Avanzados...

9 comentarios:

  1. A raiz de este post, tal vez me puedas ayudar con esta duda:
    ¿Podemos contar el número de celdas que toman un color determinado según el formato condicional?
    Es decir ¿Cuántas celdas en un rango hay con color amarillo o verde??

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Ludasi,
      seguramente la opción más sencilla sería emplear una macro, programando con un IF...THEN o algo similar; aunque también se podría sin emplear macros, utilizando la función =INDICAR.CELDA (63;ref) como un nombre definido, para luego contar cuantos registros tienes con un determinado valor, con un =CONTAR.SI(rango a evaluar; núm_color).
      Slds

      Eliminar
  2. Hola Ismael:
    El día 8 me contestabas a una consulta sobre como contar celdas con un color determinado en función de un formato condicional.
    La cuestión es que se me olvidó decirte que debe ser para Excel 2000, no me dejan trabajar con versiones superiores.
    No puedo ver la función =INDICAR.CELDA (63;ref) y supuse que sería cuestión de versión de Excel.
    Gracias.

    Luis G. A.

    ResponderEliminar
    Respuestas
    1. Hola Luis,
      no, no tiene que ver la versión. Lo que ocurre es que esa función INDICAR.CELDA no es una función estándar, y no la vas a encontrar en la biblioteca de funciones.
      Para poder usarla tienes que crear un Nombre definido e incluier dicha función en la referencia del Nombre; sólo así funcionará...
      Este tipo de función, precisamente, tienen origen en las primeras versiones de Excel (muy anteriores a la 2000 con la que trabajas).
      Slds

      Eliminar
  3. Hola, buenas

    tengo varias columans con mas de 10000 registros o lineas y quisiera poner un color diferente para cada dato que se repita 30 veces.
    como lo puedo hacer .. alguna pista ???

    muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      con formato condicional puedes identificarlos, pero con un mismo color, no sería posible que el Formato condicional comprobara que el elemento repetido es diferente a otros. Parece claro que necesitarías una macro que recorriera los 10.000 registros y evaluara esas repeticiones.
      Sin macros, podrías optar por un Formato condicional sencillo con fórmula: =CONTAR.SI($A:$A;A1)>30 aplicado a la base de datos (suponiendo empieza en A1).
      Otra opción, sería con una tabla dinámica aplicada sobre el origen de datos (la base de datos de 10.000 líneas), previ filtro de aquellos elementos con repetción de más de 30 veces, y luego aplicarles a esos exclusivamente un Formato...
      Intentaré subir una entrada completando alguna de las opciones.
      Slds

      Eliminar
  4. muchas gracias

    yo he intentado lo que tu dices ... pero solo 1 color .. jijij

    lo que hago primero aislo los unicos ... que me quita unos 1000 .. pero me queda el respto que puede repetirese, 2,3,4,5 .... asi ... sucesivamente.

    salu2.

    ResponderEliminar
  5. UMuy buenas de nuevo.

    Una idea que tengo seria:

    1.- En la hoja 2 por ejemplo poner cada dato que se repite y al lado el numero de veces ...

    2.- ir consultando esa base y como no es superior a 30, ir eliminando

    Asi no seria incoveniente ... a ver si lo consigo programar.

    salu2.

    ResponderEliminar
    Respuestas
    1. Hola,
      he subido una macro que quizá te pueda servir, la puedes ver en:
      http://excelforo.blogspot.com.es/2012/07/vba-formato-condicional-en-excel-segun.html
      Sólo vale para elementos numéricos!!
      Slds

      Eliminar