Funciones Texto Excel en megafórmulas

Uno de mis lectores me preguntaba si existe alguna función en Excel que, dado un nombre propio, de cómo resultado el sexo de la persona.
Semejante función no existe, pero basándonos en una lista de nombres y sexos, podemos construir una fórmula que lo haga.
El objetivo de esta nota es doble. Demostrar el uso de algunas funciones de texto y como podemos combinarlas y, una vez más, cavilar sobre el tema de "megafórmulas" versus "tablas auxiliares".

Comencemos por considerar esta tabla.




Los nombres son de alumnos de mi promoción del Colegio Nacional de Buenos Aires (y espero que nadie se moleste por publicarlos sin su consentimiento).
La idea es crear una fórmula en la columna B que de cómo resultado el sexo de la persona, basándose en el nombre propio.

El primer lugar creamos una lista que contiene nombres propios y el sexo apropiado (por suerte en castellano no hay nombres "duales").



A la tabla le asociamos el nombre "sexo"

Volviendo a la tabla de los alumnos, vemos que el apellido está separado de los nombres propios por una coma. Esto nos servirá para extraer el primer nombre propio del alumno, que a su vez nos servirá de argumento de una función BUSCARV para obtener el sexo del nombre en la tabla "sexo".

Primero mostraremos como resolvemos el problema usando tablas auxiliares. La solución es la siguiente



En la columna D ponemos esta fórmula: =ENCONTRAR(",";A2)+2
El resultado de ENCONTRAR es la posición de la coma, que separa el apellidos de los nombres propios, en la cadena de texto (apellido + nombres propios). Al sumarle 2 obtenemos la posición de la primer letra del nombre propio.

En la columna E ponemos la fórmula:

=SI(ESERROR(ENCONTRAR(" ";A2;D2+2));LARGO(A2)+D2;ENCONTRAR(" ";A2;D2+2))

Esta fórmula calcula la posición de la última letra del primer nombre propio. La función SI es necesaria para evitar que la fórmula de error si hay un solo nombre propio.
De haber más de un nombre propio basta con la fórmula ENCONTRAR(" ";A2;D2+2), que busca la posición del primer espacio después de la coma. Pero de haber un solo nombre, no hay espacio y la función da resultado ERROR. En ese caso usamos la función LARGO.

En la columna F ponemos la fórmula =EXTRAE(A2;D2;E2-D2), que se basa en los resultados de las columnas auxiliares. Como ven, aquí obtenemos el nombre propio.

Todo lo que nos queda por hacer es buscar el sexo del nombre en la tabla "sexo". Esto lo hacemos con la función BUSCARV, que ponemos en la columna B

=BUSCARV(F2;sexo;2;0)

Otra alternativa es construir una "megafórmula". Un megafórmula combina en una sola fórmula todas las funciones que hemos usado en las columnas auxiliares. La megafórmula que usaríamos es

=BUSCARV(EXTRAE(A2;ENCONTRAR(",";A2)+2;SI(ESERROR(ENCONTRAR(" ";A2;(ENCONTRAR(",";A2)+2)+2));LARGO(A2)+(ENCONTRAR(",";A2)+2);ENCONTRAR(" ";A2;(ENCONTRAR(",";A2)+2)+2))-(ENCONTRAR(",";A2)+2));sexo;2;0)

La ventaja de usar "megafórmulas" es que eliminamos las columnas auxiliares, resultando en archivos más pequeños y con menor tiempo de recálculo.
La desventaja consiste en que estas "megafórmulas" son muy difíciles, o imposibles, de interpretar.

Technorati Tags: MS Excel

Usando Filtro Avanzado en Excel

Autofiltro permite filtrar una lista de acuerdo a una serie de criterios. Por cada columna podemos elegir hasta dos criterios, combinándolos con los operadores Y (AND) u O (OR).
Autofiltro es una gran herramienta para filtrar datos, pero en ciertas ocasiones resulta limitada. En muchos de esos casos podemos utilizar Filtro Avanzado.




En una nota anterior en este blog he mostrado uno de los usos de Filtro Avanzado: extraer una lista de valores únicos.

En esta nota veremos otros usos de Filtro Avanzado. Empecemos por considerar esta lista de nombres con sus respectivas edades



Como pueden ver hemos dejado algunas líneas en blanco por encima de la lista. Esto es necesario para permitir introducir los criterios con los cuales queremos filtrar la lista.

Supongamos que queremos filtrar la lista de manera que vemos todas las personas mayores de 40 años o menores de 30 años. El primer paso consiste en copiar los encabezamientos de las columnas en la fila 1; luego escribimos los criterios en las celdas B2 y B3



Ahora abrimos el menú Datos—Filtro--Filtro Avanzado y señalamos la tabla de datos y el rango que contiene los criterios



Apretamos aceptar y Excel filtrará la lista



Para volver a mostrar todas las líneas usamos el menú Datos—Filtro—Mostrar todo



Al escribir los criterios en distintas líneas estamos indicando a Excel que queremos usar el operador O (OR). Si queremos usar el operador Y (AND) tenemos que poner los criterios en la misma línea. Por ejemplo, si queremos mostrar solo aquellos cuyo nombre empieza con la letra R y son mayores de 30 años






Nótese que usamos R* como criterio.

También podemos combinar operadores Y (AND) y O (OR)






También podemos usar fórmulas como criterio. Para esto deben cumplirse dos condiciones:
1 - que den como resultado un valor VERDADERO o FALSO.
2 – el encabezamiento del rango de criterios debe ser distinto del encabezamiento de la columna de datos

Por ejemplo, queremos mostrar sólo aquellos nombres cuya edad sea menor que la edad promedio de la lista. En el rango de criterios ponemos esta fórmula

=B6<PROMEDIO($B$6:$B$15)






Otro ejemplo sería mostrar aquellos nombres que aparecen más de una vez. Para eso usaremos la fórmula

=CONTAR.SI($A$6:$A$15,A6)>1






Otra gran ventaja de Filtro Avanzado es que nos permite copiar la lista filtrada a otra ubicación en la hoja. Volviendo sobre el ejemplo anterior, marcamos la posibilidad "Copiar a otro lugar", por ejemplo a D1






Como podemos ver, Filtro Avanzado permite filtrar listas de maneras que no serían posibles con Autofiltro.



Technorati Tags: MS Excel

Excluyendo ceros en cálculos con Excel

De acuerdo a Wikipedia, el cero pertenece al conjunto de los números enteros. Sea lo que fuere, en ciertos cálculos, el cero puede ser bastante molesto, en especial si lo hacemos con Excel.

Por ejemplo, supongamos esta lista de números

Si queremos contar cuántos elementos hay en la lista, sin incluir los ceros, podemos hacerlo fácilmente usando la función CONTAR.SI. Definamos un nombre que contenga el rango de números

lista_num: =Hoja1!$A$1:$A$20

la siguiente fórmula nos da como resultado 20: =CONTAR(lista_num)

Si queremos contar la cantidad de números en la lista, sin considerar los ceros, usaremos la fórmula

=CONTAR.SI(lista_num;"<>0")

Esta fórmula da como resultado 17.

Los problemas empiezan cuando queremos encontrar el mínimo de la lista, lo que hacemos usando la función MIN. Esta fórmula da como resultado 0

=MIN(lista_num)

Si queremos encontrar el mínimo de la lista, sin incluir los ceros, usamos esta fórmula

=SI(MIN(lista_num)<>0;MIN(lista_num);K.ESIMO.MENOR(lista_num;CONTAR.SI(lista_num;"=0")+1))

Esta fórmula realiza el cálculo en dos etapas. Primero comprueba si el mínimo es cero. Si no hay ningún cero en la lista, usa la fórmula MIN(lista_num).
Si el mínimo es cero, pasa a usar la segunda parte de la función SI, la función K.ESIMO.MENOR combinado con CONTAR.SI.

La sintaxis de la función K.ESIMO.MENOR es: K.ESIMO.MENOR(matriz;k)
Donde matriz es el conjunto de datos y k determina a partir de qué valor se calculará el mínimo. En nuestra fórmula k es determinado por la fórmula CONTAR.SI(lista_num;"=0")+1 que da como resultado, en nuestro ejemplo, 4.

Un problema similar, simétrico diría, tenemos si nuestra lista está formada sólo por números negativos.
En ese caso, calcular el máximo de la lista (supongamos la misma lista, pero con valores negativos), daría 0. Para calcular el máximo, sin tomar en cuenta los ceros usamos una fórmula similar (al rango le hemos dado el nombre lista_neg)

=SI(MAX(lista_neg)<>0;MAX(lista_neg);K.ESIMO.MAYOR(lista_neg;CONTAR.SI(lista_neg;"=0")+1))

Otro problema con ceros es calcular promedios. Excel ignora las celdas vacías al calcular promedios con la función PROMEDIO. Pero los ceros son tenidos en cuenta. Por ejemplo, la fórmula =PROMEDIO(lista_num) da como resultado 39,25. Pero si calculamos el promedio sin incluir los ceros, obtenemos 46,176.

Para calcular promedios sin tomar en cuenta los ceros tenemos varias posibilidades:

=SUMA(lista_num)/CONTAR.SI(lista_num;"<>0")

El problema con esta fórmula es que toma en cuenta las celdas vacías. Para evitar que esto suceda podemos usar la fórmula

=SUMA(lista_num)/(CONTAR(lista_num)-CONTAR.SI(lista_num;0))

Otra alternativa es usar la función SUMAPRODUCTO de esta manera

=SUMA(lista_num)/SUMAPRODUCTO(--(lista_num<>0))

EL doble signo "--" tiene como función forzar el cálculo de la expresión lista_num<> y en su lugar se puede, sencillamente, multiplicar la expresión por 1

=SUMA(lista_num)/SUMAPRODUCTO((lista_num<>0)*1)

Technorati Tags: MS Excel