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
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
archivo del ejemplo
