Encontrar el mínimo entre valores repetidos en un rango en Excel.

viernes, julio 13, 2007

Este blog trata sobre temas de interés general para los usuarios de Excel. Cuando recibo preguntas o consultas sobre problemas específicos, suelo dirigir a mis lectores a los foros de ayuda, como Exceluciones por ejemplo, o trato el problema por línea privada (a través del mail), si es que dispongo del tiempo.
A veces sucede que un problema específico deriva a otro de interés más general. Así han nacido no pocas de las entradas en este blog.

Supongamos esta situación(como siempre, el Minimo entre valores repetidosarchivo del ejemplo esta a vuestra disposición aquí):

- en el rango A2:A16 tengo 15 valores únicos
- en el rango B2:B16 tengo 15 valores, algunos repetidos

Quiero encontrar el mínimo entre los números repetidos en B2:B16 y encontrar su ubicación en el rango de los valores únicos (A2:A16):





En esta entrada veremos dos soluciones:
- usando columnas auxiliares
- usando fórmulas matriciales

La solución, en ambos casos, consiste en generar una matriz que contenga sólo los valores repetidos en la columna B, y luego buscar el mínimo entre los miembros de esta matriz. Una vez hallado el valor mínimo en B, podemos encontrar su "dirección" (la celda donde se encuentra) usando las funciones DIRECCION y COINCIDIR.

La solución con columnas auxiliares comienza por generar una columna auxiliar en el rango C2:C15 con esta fórmula =CONTAR.SI($B$2:$B$16;B2)>1 que copiamos a todo el rango. Esta fórmula da FALSO si un número no esta repetido en el rango, y VERDADERO si lo está



Luego multiplicamos los valores del rango C2:C16 por los del rango B2:B16. Al multiplicar por FALSO el resultado será 0. VERDADERO es interpretado por Excel como 1 y por lo tanto el resultado al multiplicar por VERDADERO será idéntico al valor en la celda correspondiente de la columna B



Podemos ver que en la columna D los números que no se repiten en B han sido transformados en 0.

Para encontrar el menor de ellos, sin tomar en cuenta los ceros, usamos esta fórmula:

=K.ESIMO.MENOR(D2:D16;CONTAR.SI(D2:D16;0)+1)

La función K.ESIMO.MENOR (nombre espeluznante! en inglés se llama SMALL) tiene dos argumentos. EL primero es el rango dónde buscamos el menor de los miembros; el segundo representa la posición a partir de la cual buscar el menor. En nuestra matriz hay 6 ceros, por lo tanto queremos empezar a buscar de la séptima posición. Para determinar este número en forma dinámica usamos CONTAR.SI para determinar cuantos ceros hay y al resultado le agregamos 1



La solución con fórmulas matriciales es conceptualmente igual, pero las "columnas auxiliares" pasan a ser matrices generadas dentro de la fórmula. Con fórmulas matriciales resolvemos el problema con una única fórmula

=K.ESIMO.MENOR((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1));CONTAR(SI((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1))=0;1))+1)

Como toda fórmula matriciales la anotamos apretando simultáneamente Ctrl+Mayúsculas+Enter.


Si comparamos esta fórmula con la de la solución con columnas auxiliares, veremos que de hecho son similares. La expresión D2:D16 es reemplazada en la fórmula matricial por la expresión B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1).


La expresión CONTAR.SI(D2:D16;0)+1 es reemplazada por CONTAR(SI((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1))=0;1))+1

Aquí notarán seguramente que estamos usando una combinación de CONTAR y SI y no la función CONTAR.SI, como sería de esperar. Si intentáramos usar CONTAR.SI recibiríamos un aviso de error. De acuerdo a la ayuda on-line de Microsoft esto se debe a que las funciones SUMIF(), COUNTIF() y COUNTBLANK() utilizan el mismo algoritmo criteria-matching como funciones de base de datos tal como DSUM(). Este algoritmo no admite matrices.
Debido a esto usamos el rodeo =CONTAR(SI(...

Finalmente, para ubicar la posición del resultado en el rango de la columna A usamos esta fórmula:

=DIRECCION(COINCIDIR(C19;A1:A16;0);1)



donde C19 es la celda que contiene la fórmula matricial.

La pregunta de mi lector era en realidad a la inversa. Encontrar en el rango de los números repetidos, el mínimo (y el máximo) del rango de los valores únicos. Para esto nos basta con encontrar el mínimo con =MIN(A2:A16) y usar este resultado como argumento en la función DIRECCION

=DIRECCION(COINCIDIR(MIN(A2:A16);B2:B16;0)+1;2)

Si el número está repetido en el rango B, siempre recibiremos la dirección de la primera ocurrencia del número.

Otra alternativa interesante es usar Formato Condicional, para marcar todas las celda en el rango B que contienen el mínimo del rango A:



En esta formula hay que prestar atención a las direcciones absolutas y relativas. Por defecto, Formato Condicional usa direcciones absolutas.




Technorati Tags:

9 comments:

Anónimo,  24 octubre, 2007 19:38  

como puedo hacer para que me ponga el minimo y luego en otra celda busque el inmediato superior?

Jorge L. Dunkelman 25 octubre, 2007 21:09  

Usando la función K.ESIMO.MENOR con el segundo argumento igual a 2.

Anónimo,  22 octubre, 2009 00:52  

Hola Jorge
Pensé que en lugar de K.ESIMO.MENOR podíamos usar MIN para resolver el problema y aquí lo encontré, tanto para columnas auxiliares como para fórmula matricial. Aquí va:
=K.ESIMO.MENOR(D2:D16;CONTAR.SI(D2:D16;0)+1)
la podemos reemplazar por
{=MIN(SI(D2:D16>0;D2:D16))}
Como puedes ver, esta fórmula es matricial.

Por otra parte, la fórmula matricial:
{=K.ESIMO.MENOR((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1));CONTAR(SI((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1))=0;1))+1)}
la podemos reemplazar por
{=MIN(SI(CONTAR.SI($B$2:$B$16;B2:B16 >1;B2:B16))}
Como ves, sobre todo esta última sale bastante más corta.
Un abrazo

Anónimo,  07 abril, 2010 06:30  

Hola maestros del Excel: Tengo un problemita por resolver y necesito de su ayuda, es el siguiente: Tengo un rango formado de la siguiente forma: En la primera fila, los 12 meses del año, en la primera columna años (por decir de 1901 a 2010) y las celdas restantes son valores de temperaturas. Se necesita encontrar los años y meses en que ocurrieron las temperaturas más altas. El caso a resolver es cuando se presenta en varios meses o años un mismo valor máximo. Ejemplo: Sea la temp máx.de 20, y se presentó en marzo de 1998; también en abril de 1998 y también en marzo de 2001. Necesito que Excel me indique el máximo (20) y TODAS las fechas de ocurrencia. Será posible obtener todas las fechas?. Ya probé con K.mayor (1,2,3) y podría obtener los valores pero no logro obtener las fechas correspondientes. Agradezco su atención calean99@hotmail.com

Jorge L. Dunkelman 07 abril, 2010 12:24  

Hola,
te sugiero que dirijas tu consulta a un foro. El marco de un comentario no es el apropiado.

Anónimo,  01 agosto, 2016 20:59  

=K.ESIMO.MENOR(SI(B$2:B$12<>"",FILA(B$2:B$12)),FILAS(D$2:D2))
ESTA FORMULA SOLO FUNCIONA EN LA PRIMERA FILA DE CALCULO. CUANDO SE COPIA A LAS OTRAS DE ABAJO MARCA !$NUM! ES UN EJEMPLO QUE COPIE DE ALGUIEN QUE LE FUNCIONA BIEN. AYUDENME A ENCONTRAR LA CAUSA.

csolmar 28 febrero, 2017 03:36  

Como hago para imprimir el valor de la celda hallada? me explico tengo la funciòn =DIRECCION(COINCIDIR(C19;A1:A16;0);1) en donde me arroja la direcciòn de la celda pero lo que necesito no es que me muestre la direccion de la celda si no su contenido, como puedo lograr eso?

Jorge Dunkelman 01 marzo, 2017 07:21  

No me queda claro. El valor de la celda es lo que se ve en pantalla y lo que se imprimirá, exceptoo que decidas mostrar las fórmulas en lugar del contenido (lo que se hace con Ctrl+~).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP