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:
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.:
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 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))
Obteniendo el resultado esperado:
| ...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.:
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 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))
Obteniendo el resultado esperado:
Hola,
ResponderEliminarMira 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
Hola Ana,
Eliminarla 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'.
Hola ExcelForo
ResponderEliminarEn 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.
Hola,
Eliminarel 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
Hola
ResponderEliminarUna 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
Hola Felíx,
Eliminarlamentablemente 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
Hola excelforo
ResponderEliminarQuisiera 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.
Hola Angel...
Eliminarbueno, 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
como hago para sacar la formula de un porsentge menor de un rango y muliplicarlo por un % y el mayor por otro
ResponderEliminarHola Pedro, ¿cómo estás?.
EliminarNo 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
hola excelforo
ResponderEliminartengo 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
Hola,
Eliminaren 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
funcionó perfecto
Eliminarmuchas gracias
hola excelforo
ResponderEliminartengo 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
Hola,
Eliminarsi 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
Hola puedo consultar ejemplos sobre el condicional si gracias
ResponderEliminarHola Juan Carlos,
Eliminarpuedes 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
Hola tengo una consulta:
ResponderEliminarComo 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?
Hola!
Eliminarecha 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