jueves, 19 de abril de 2012

Una matricial para un Formato condicional de Excel.

Me planteaba un lector, a través de un correo, la posibilidad de remarcar, mediante el Formato condicional, para un listado de valores, los cuatro mayores importes correspondientes a un elemento particular:
...Quisiera saber si se puede en formato condicional que me marque de color las celdas C2:C31, con respecto a la celda $F$2, ahora tiene el numero cuatro, entonces que me marque de color la suma de los tres elementos como se muestra ahora de color amarillo...

Para entenderlo mejor veamos el listado de nuestra hoja de cálculo. La idea es clara, para la persona llamada 'Andrés' remarcar los cuatro mayores valores.:

Una matricial para un Formato condicional de Excel


El asunto es complicado ya que para configurar nuestro Formato condicional deberíamos incluir una fórmula matricial que defina el condicional, esto es, la fórmula que diga para cada registro evaluado del rango C2:C31 si correponde a la persona 'Andrés' y que además su valor está entre los cuatro más altos para dicha persona.
Al no admitir directamente una función matricial la herramienta Formato condicional, tendremos que recurrir a otra de las funcionalidades potentes de Excel, los Nombres definidos.

Para comenzar a trabajar, y para facilitar el trabajo posterior, Asignaremos nombres a nuestros campos del listado, de la hoja 'FormCond':
personas =FormCond!$A$2:$A$31
valores =FormCond!$C$2:$C$31
y acabaremos por construir una función matricial como un Nombre definido, lo que nos permitirá posteriormente incluirla dentro de nuestro Formato condicional.
En este caso la hemos llamado identidad:
identidad =(personas=$G$3)*valores

Una matricial para un Formato condicional de Excel


Una vez generado esta identidad ya podemos dar nuestro Formato condicional al rango C2:C31; asi que seleccionando dicho rango, desde la Ficha Inicio > Estilos > Formato condicional activamos la ventana diálogo, dentro de la cual elegiremos la opción de 'Fórmula':
=Y($A2=$G$3;$C2>=K.ESIMO.MAYOR(identidad;$F$2))

Una matricial para un Formato condicional de Excel


Obteniendo el resultado esperado:

Una matricial para un Formato condicional de Excel

19 comentarios:

  1. Hola,
    Mira tengo una lista casi igual que la del blog. la diferencia es que tengo varios valores repetidos le pongo el número 4 y me colorea los cuatros más 3 valores repetidos osea 7 celdas coloreadas en total.
    Qué puedo hacer, alguna solución.

    Gracias y felicidades por el blog

    Saludos
    Ana

    ResponderEliminar
    Respuestas
    1. Hola Ana,
      la cosa puede complicarse un poco, ya que deberíamos incorporar a la matricial las condiciones que nos digan en caso de repetición qué celdas debemos remarcar...
      y eso es lo primero que debemos conocer, si se repiten valores (en tu caso tres repetidos) con cuáles nos quedamos...
      Puedes ver un ejemplo de ordenación en
      http://excelforo.blogspot.com.es/2009/10/la-funcion-jerarquia-la-ordenacion-de.html
      La clave para contestar tu pregunta pasa por una correcta ordenación de los valores (repetidos o no), para luego indicarle con una matricial al formato condicional el número de valores ordenados que queremos remarcar.

      Si tienes claro esto y dudas en algo coméntamelo a
      excelforo@gmail.com

      Un saludo y gracias a ti por 'leerme'.

      Eliminar
  2. Hola ExcelForo
    En tu nota me nuestra los 4 valores máximo, y si yo quisiera los 4 valores mínimo cual seria la fórmula, porque he intentado con
    cambiar (K.ESIMO.MAYOR) POR (K.ESIMO.MENOR) y me colorea muchas celdas.
    Un cordial saludo
    Germán.

    ResponderEliminar
    Respuestas
    1. Hola,
      el problema es que el rango identidad devuelve cero para aquellas personas diferentes de 'Andrés', por lo que al evaluar el menor valor del rango identidad se toma cero como menor valor, asi que da formato a muchos valores, como tu indicas.

      La solución pasaría por modificar el Nombre definido identidad por la siguiente:
      =SI((FormCond!personas=FormCond!$G$3)*FormCond!valores=0;"";(FormCond!personas=FormCond!$G$3)*FormCond!valores)
      y la fórmula para el Formato condicional ahora sería:
      =Y($A2=$G$3;$C2<=K.ESIMO.MENOR(identidad;$F$2))
      Slds

      Eliminar
  3. Hola
    Una pregunta, Cuál sería la fórmula normal para que me sume solo las celdas amarillas de andrés y en otra celda el promedio.
    gracias excelforo por tú atención
    Félix

    ResponderEliminar
    Respuestas
    1. Hola Felíx,
      lamentablemente sólo es posible (en una sóla celda) mediante fórmulas matriciales. Para la suma:
      {=SUMA(K.ESIMO.MAYOR(((personas=$I9)*valores);FILA(INDIRECTO("1:"&$F$2))))}
      para el promedio:
      {=PROMEDIO(K.ESIMO.MAYOR(((personas=$I9)*valores);FILA(INDIRECTO("1:"&$F$2))))}
      Suponiendo en I9 el nombre de 'Andrés' y en $F$2 el número de valores con el que operar(como en el ejemplo de esta entrada).
      Slds

      Eliminar
  4. Hola excelforo
    Quisiera hacerte una consulta,y disculpa la ignorancia que tengo con excel más concretamente con matriz; yo se que es una matriz porque tiene corchete al principio y al final de una función, también sé que presionando las teclas Ctrl+Mayusc+Enter), se activa la función matricial.
    Quiero que me explique de como saber cuándo estoy realizando una fórmula que si es o no matricial, a qué me vaso a qué criterio porque no lo sé, te hago esta consulta porque veo mucho tú blog donde hay muchas fórmulas matriciales, es obvio que tú dominas las matriciales, y así sacarme esta espina de la ignorancia de las matrices.
    Muchas gracias
    felicidades por tu blog
    Saludos
    Ángel.

    ResponderEliminar
    Respuestas
    1. Hola Angel...
      bueno, la cuestión que planteas no es fácil de responder, pero intentaré ser lo más claro posible.
      Lo primero decirte que es cierto lo que dices, efectivamente se ejecutan presionando Ctrl+Mayus+Enter y sabemos que es una matricial cuando al principio y al final aparecen unos corchetes; pero lo importante es cómo trabajan estas fórmulas matriciales.
      Podríamos decir que es matricial por que evalua o trabaja sobre rangos o matrices, cuando la mayoría de las funciones estándar de Excel sólo trabajan sobre una celda. Esto es, una fórmula matricial es una fórmula que puede realizar varios cálculos en uno o varios de los elementos de una matriz. Las fórmulas matriciales pueden devolver varios resultados o un único resultado.
      Esto podría generar confusión ya que por ejemplo, la función SUMAPRODUCTO es en esencia una función matricial 'estándar', que se puede replicar mediante un producto de rangos, con lo que tendríamos una fórmula matricial.
      En resumen, desde mi punto de vista existen dos tipos de matriciales: unas funciones estándar, y otras personales (que serían a las que te referías en la consulta) que construimos nosotros; pero en ambos casos el efecto es el mismo se trabaja sobre rangos de celdas de diferentes formas.
      Espero haber arrojado algo de luz (y no de oscuridad) sobre el tema.
      Un cordial saludo

      Eliminar
  5. como hago para sacar la formula de un porsentge menor de un rango y muliplicarlo por un % y el mayor por otro

    ResponderEliminar
    Respuestas
    1. Hola Pedro, ¿cómo estás?.
      No está muy claro cuáles son esos rangos, pero tendrías que emplear un condicional de este estilo:
      =SI(valorestudiomáximo;porcentaje2*valor))
      Espero te sirva.
      Cordiales saludos

      Eliminar
  6. hola excelforo
    tengo una duda acerca de si la funcion k.esimo.menor puede usar como argumento el nombre de una matriz pero que esta escrita dentro de otra celda. es decir, yo tengo una matriz a la cual le asigne un nombre, luego ese nombre lo puse en una celda supongamos la celda C3. para obtener el segundo numero menor utilizo como argumentos: k.esimo.menor(C3; 2)pero me sale error. que se puede hacer??
    saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      en este caso tendrías que anidar la función INDIRECTO:
      =k.esimo.menor(INDIRECTO(C3); 2)
      pero asegúraté que lo que haya en C3 sea una referencia (bien un nombre definido, una celda o un rango de celdas) existente.
      Slds

      Eliminar
    2. funcionó perfecto
      muchas gracias

      Eliminar
  7. hola excelforo
    tengo una consulta acerca de como puedo obtener el valor de una celda en una fila teniendo el numero de columna en que se encuentra.
    es decir, tengo una fila con datos y tengo el numero de la columna donde se encuentra el dato que necesito de esa fila.
    existe alguna funcion que pueda cumplir mi requerimiento??
    saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      si conocer el número de columna y de fila, puedes aplicar la siguiente función anidada:
      =INDIRECTO(DIRECCION(fila;columna))
      esto te devolverá el valor de esa celda.
      Slds

      Eliminar
  8. Hola puedo consultar ejemplos sobre el condicional si gracias

    ResponderEliminar
    Respuestas
    1. Hola Juan Carlos,
      puedes encontrar algunos ejemplos de la función SI condicional en
      http://excelforo.blogspot.com.es/search/label/Funciones
      son algo antiüas, por lo que estarán al inicio.
      Usa el buscador del blog (en la parte derecha del blog), busca 'SI condicional'.

      Si tienes alguna duda en concreto, también puedes plantearla.
      Saludos cordiales

      Eliminar
  9. Hola tengo una consulta:
    Como puedo agregar formato condicional a una lista basándome en otra lista; me explico:
    En una lista tengo a todos mis clientes unos 3,000 y en otra tengo a mis clientes morosos unos 150; y quiero pintar de rojo a los clientes morosos con formato condicional; como podría hacerlo?

    ResponderEliminar
    Respuestas
    1. Hola!
      echa un vistazo a este post:
      http://excelforo.blogspot.com.es/2016/11/encontrar-desparejados-formato-condicional.html
      Te dará la pista de cómo hacerlo...

      Es más simple, ya que la fórmula bastaría que fuera CONTAR.SI(Rng_morosos;cod_cliente)>=1

      Saludos

      Eliminar