Funciones Base de Datos en Excel

Escribiendo las notas sobre Filtro Avanzado me acordé de un tema que parece haber caído en desuso en Excel: las funciones Base de Datos (Database Functions, DBase Functions).

Estas funciones permiten analizar y realizar cálculos en base a listas. En Excel "lista" es una tabla de datos que en su primera fila tiene encabezamientos.

Tal vez uno de los motivos de la pérdida de popularidad de estas funciones sea la alternativa de usar tablas dinámicas (pivot tables) y fórmulas matriciales (array formulas).

Sin embargo en la mayoría de los casos, las funciones base de datos son preferibles a las fórmulas matriciales.
Las funciones matriciales son muy poderosas pero tienen un gran inconveniente: producen problemas de recálculo. El uso intensivo de fórmulas matriciales hace que los archivos tarden en recalcularse. En este terreno, las funciones base de datos son la mejor alternativa.

Las funciones base de datos comienzan todas con BD y tienen una sintaxis en común:
Función BD(base_de_datos, campo ,criterios).

El argumento base_de_datos es la tabla/lista que contiene los datos; campo es la columna sobre cuyos datos queremos realizar el cálculo y criterios es el rango que contiene los criterios para filtrar la base de datos.

Existen 13 funciones base de datos (XL2003), entre ellas BSUMA, BCONTAR y BPROMEDIO.

Consideremos esta lista





El rango A5:E20 está asociado al nombre "alumnos"

Si queremos calcular la cantidad de alumnos que cumplen dos condiciones simultáneamente: han recibido por lo menos 80 puntos en matemática y por lo menos 75 puntos en historia, hacemos lo siguiente



Nótese que en la primer fila de la hoja hemos replicado los encabezamientos de las columnas de la tabla de datos.

En la celda E22 hemos puesto la fórmula =BDCONTARA(alumnos;1;B1:D2)

donde:

alumnos =Hoja1!$A$5:$E$20 es el rango de la tabla de datos

1 indica que queremos contar las miembros de la primer columna de la tabla

B1:D2 es el rango de criterios con los cuales queremos "filtrar" la tabla

En lugar de usar el número de columna, podemos usar el encabezamiento, con lo cual la fórmula se vuelve totalmente legible

=BDCONTARA(alumnos;"nombre";B1:D2)

Al poner las definiciones de los criterios en la misma fila, estamos indicando el uso del operador Y, es decir, todas las condiciones deben cumplirse simultáneamente.

Para usar el operador O, ponemos las condiciones en filas distintas. Por ejemplo, si queremos contar cuantos alumnos hay en la lista que recibieron por lo menos 80 puntos en matemática o 75 en historia, arreglamos la hoja de la siguiente manera



Por supuesto, también hemos modificado el rango de los criterios en la fórmula para que incluya todas las filas

=BDCONTARA(alumnos;"nombre";B1:C3)

También podemos combinar los operadores Y y O en el rango de criterios. Por ejemplo, si queremos saber cuantos alumnos hay que recibieron por lo menos 80 puntos en matemática Y 75 en historia O más de 85 de promedio, usamos el modelo



También aquí hemos modificado la fórmula para que incluya todos los criterios

=BDCONTARA(alumnos;"nombre";B1:E3)

Las funciones base de datos son fáciles de usar, una vez que hemos entendido la sintaxis básica. Son muy flexibles y permiten hacer cálculos que con más facilidad y velocidad que las funciones matriciales

La lista completa de funciones base de datos es:

BDPROMEDIO Devuelve el promedio de las entradas seleccionadas de la base de datos

BDCONTAR Cuenta las celdas que contienen números en una base de datos

BDCONTARA Cuenta las celdas que no están en blanco en una base de datos

BDEXTRAER Extrae de la base de datos un único registro que coincida con los criterios especificados

BDMAX Devuelve el valor máximo de las entradas seleccionadas de la base de datos

BDMIN Devuelve el valor mínimo de las entradas seleccionadas de la base de datos

BDPRODUCTO Multiplica los valores de un campo determinado de registros de la base de datos que coinciden con los criterios especificados

BDDESVEST Calcula la desviación estándar basándose en una muestra de entradas seleccionadas de la base de datos

BDDESVESTP Calcula la desviación estándar basándose en la población total de las entradas seleccionadas de una base de datos.

BDSUMA Suma los números de la columna del campo de los registros de la base de datos que coincidan con los criterios especificados

BDVAR Calcula la varianza basándose en una muestra de las entradas seleccionadas de una base de datos

BDVARP Calcula la varianza basándose en la población total de las entradas seleccionadas de una base de datos

IMPORTARDATOSDINAMICOS Devuelve los datos almacenados en una tabla dinámica

Technorati Tags: MS Excel

Filtro Avanzado – otros usos

Continuando al nota anterior sobre Filtro Avanzado, en esta veremos otro uso interesante de esta herramienta.
Supongamos esta lista que muestra la fecha de recepción de las órdenes de nuestros clientes y la fecha en que se efectuó el despacho




Si queremos obtener una lista de los despachos que han tardado más de 7 días en salir, aplicamos Filtro Avanzado usando esta fórmula como criterio

=(C5-B5)>7



Como hemos explicado en la nota anterior, el encabezamiento distinto del rango de los criterios debe ser distinto al de las columnas de la tabla. También podemos prescindir de encabezamiento para el criterio.



El resultado es



También podemos usar funciones más avanzadas. Por ejemplo, si queremos ver todas las órdenes que se despacharon por lo menos 5 días hábiles después de recibidas, usamos la función NETWORKDAYS

Technorati Tags: MS Excel

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