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
archivo 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: MS Excel
como puedo hacer para que me ponga el minimo y luego en otra celda busque el inmediato superior?
ResponderBorrarUsando la función K.ESIMO.MENOR con el segundo argumento igual a 2.
ResponderBorrarHola Jorge
ResponderBorrarPensé 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
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
ResponderBorrarHola,
ResponderBorrarte sugiero que dirijas tu consulta a un foro. El marco de un comentario no es el apropiado.
=K.ESIMO.MENOR(SI(B$2:B$12<>"",FILA(B$2:B$12)),FILAS(D$2:D2))
ResponderBorrarESTA 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.
Fijate en esta nota.
ResponderBorrarComo 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?
ResponderBorrarNo 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+~).
ResponderBorrar