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

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

La Biblia en Excel

John Walkenbach publicó en su blog un cuaderno Excel que contiene toda la Biblia. Cada libro aparece en una hoja y cada versículo en una celda. Lo interesante en este uso de Excel es no sólo mostrar sus posibilidades casi ilimitadas, sino también el uso de funciones de texto y funciones matriciales.
Partiendo de la base que una buena parte de mis lectores no dominan el inglés, decidí hacer una adaptación al español.

La Biblia Excel en Español que publico aquí, comprende solamente los capítulos 1 al 9 del libro del Génesis.

Al igual que Walkenbach, empecé por buscar un archivo texto de la Biblia para importarlo a Excel. Después de descargar un archivo un tanto problemático (cada porción de texto tiene un formato distinto), logré importarlo a un cuaderno Excel usando algunas macros ad-hoc.

A diferencia de la versión en inglés, mi versión sólo contiene los primeros 9 capítulos del Génesis, cada capítulo en una hoja y cada versículo en una celda.

La primer hoja es el índice de dónde se puede acceder a las distintas hojas. Además contiene una serie de estadísticas por capítulo: número de versículos, número de letras, letras por versículo, número de palabras, palabras por versículo y concordancia (número de veces que una determinada palabra aparece en un capítulo).




Para elaborar las estadísticas usamos las siguientes fórmulas:

Versículos: =CONTARA(INDIRECTO("'"&B10&"'!A:A")). Usamos indirecto para crear una referencia a la hoja indicada usando el valor que aparece en la celda de la columna B de la fila correspondiente. En lugar de INIDRECTO podríamos usar una referencia explícita

Letras (Caracteres): ={SUMA(LARGO(INDIRECTO("'"&B10&"'!B1:B"&C10)))}. Esta es una fórmula matricial. LARGO suma el total de caracteres en cada celda del rango señalado por INDIRECTO, y SUMA en su forma matricial suma todos los valores obtenidos por LARGO.

Palabras: esta es la fórmula más elaborada
=CONTARA(INDIRECTO("'"&B10&"'!B1:B"&C10))+SUMAPRODUCTO(LARGO(ESPACIOS(INDIRECTO("'"&B10&"'!B1:B"&C10)))-LARGO(SUSTITUIR(ESPACIOS(INDIRECTO("'"&B10&"'!B1:B"&C10));" ";""))).

La primer función LARGO calcula la cantidad de caracteres que hay en la celda. La segunda función LARGO calcula la cantidad de caracteres después de haber sustraído los espacios entre las palabras.
La diferencia entre ambos resultados es la cantidad de palabras menos 1 (ya que no hay espacio antes de la primer palabra). Así que tenemos que agregar una unidad por cada versículo en el capítulo, lo que hacemos con la función CONTARA.

Finalmente tenemos la Concordancia, que lo que hace es contar cuantas veces aparece una palabra determinada en cada versículo. La fórmula es matricial:

=SUMA(LARGO(MAYUSC(INDIRECTO("'"&B10&"'!B1:B"&C10)))-LARGO(SUSTITUIR(MAYUSC(INDIRECTO("'"&B10&"'!B1:B"&C10));MAYUSC($H$7);"")))/LARGO($H$7)

La técnica es similar a la usada en la fórmula anterior. La primer función LARGO da el número de caracteres que hay en la celda. La segunda función LARGO calcula el número de caracteres después de haber extraído con SUSTITUIR la palabra que buscamos (celda $H$7). El resultado lo dividimos por la cantidad de caracteres de la palabra buscada, que calculamos con la última función LARGO en la fórmula.

Como se puede apreciar, Excel es muy flexible y ofrece muchas posibilidades en todo lo que respecta a cálculos vinculados con textos.



Technorati Tags: MS Excel

Números a letras en Excel – versión para México

En la nota Función números a letras en Excel en español - versión corregida había publicado la versión corregida de la función, publicada originariamente en la nota Como convertir números en palabras en Excel en Español.
Esta función está orientada a usos financieros. Es decir, no sólo traduce los números a su expresión en palabras, sino que también agrega el nombre de la moneda.
Uno de mis lectores, Javier Reséndiz, ha adaptado la función a las necesidades de México. Según sus palabras:

Entre otras cosas necesitaba convertir las cifras a texto y esa una de las
razones por la que te escribo. En las facturas se acostumbra escribir,
entre paréntesis, la cantidad con letra (para evitar remiendos o
añadiduras fraudulentas en la cifra), los decimales se escriben con número
y seguidos por "/100", y al final todo se remata con "M.N." (moneda
nacional), eso cuando menos en México, no lo sé de cierto en otras partes.

Así las cosas, necesitaba que una cifra dada, $125.11, p. eje, quedara
escrita así:
(CIENTO VEINTICINCO PESOS 11/100 M.N.)

Aquí pueden descargar el código de la función adaptada para México por gentileza de Javier.


Technorati Tags: MS Excel