Funciones Base de Datos en Excel

viernes, marzo 16, 2007

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 funciones base de datoslista





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:

41 comments:

Pedro 15 junio, 2007 19:11  

Como se podria definir un criterio que diga >= 60 y <=80, porque cuando uno le define eso el toma los mayores de 60 y los menores de 80 y no el rango
Gracias

Jorge L. Dunkelman 16 junio, 2007 00:17  

Hola Pedro
no estoy seguro de entender tu pregunta, pero si en nuestro ejemplo queremos contar cuantos alumnos recibieron una nota en matemática en el rango 60 - 80 (o sea, igual o mayor que 60 Y menor o igual a 80, haremos lo siguiente:
1 - en la celda C1 repetimos el encabezamiento Matemática
2 - en la celda B2 ponemos ">=60"; en la celda C2 ponemos "<=80"
3 - en la celda E22 ponemos la fórmula =BDCONTARA(alumnos;"nombre";B1:C2)

El resultado será 8.

Anónimo,  12 octubre, 2007 17:17  

Jorge, tengo una base de datos con 3 columnas. Donde la 1era hay nombres (todas las celdas llenas), la 2da fechas de notificacion y la 3era fechas de respuesta. En la 2da y 3er columna, tengo celdas en blanco. Como hago para contar por nombre todas las celdas que estan vacias en la 2da columna? y dps, contar las celdas vacias en la 3er columna por nombre, que no estan vacias en la segunda columna?

Nicolas,  12 octubre, 2007 17:21  

Jorge, tengo una base de datos con 3 columnas. Donde la 1era hay nombres (todas las celdas llenas), la 2da fechas de notificacion y la 3era fechas de respuesta. En la 2da y 3er columna, tengo celdas en blanco. Como hago para contar por nombre todas las celdas que estan vacias en la 2da columna? y dps, contar las celdas vacias en la 3er columna por nombre, que no estan vacias en la segunda columna?

Jorge L. Dunkelman 12 octubre, 2007 19:14  

Supongamos que tu rango va de la fila 1 a la 16. En la columna A aparecen los nombres, entre ellos Juan. Si queremos contar cuantas celdaas vacías hay en la columna B, en la filas donde en la columna A aparece "Juan" usamos esta fórmula

=SUMAPRODUCTO((LARGO(B1:B16)=0)*((A1:A16)="Juan"))

Para contar las celdas no vacías cambiamos LARGO(B1:B16)=0 por LARGO(B1:B16)<>0

Juan 13 octubre, 2007 01:10  

Las celdas con fecha llevan algun formato en especial? cuando intento filtrar los datos de un mes, no me los encuentra. Escribo en las celdas que uso para el filtro > 01/01/2007 y < 01/02/2007. Si no le pongo signos, me lo reconoce, una vez que pongo signos el resultado es 0.

Juan 13 octubre, 2007 01:14  

Hola, te habia hecho una pregunta y como no la vi cuando le di publicar, la repito. Estoy tratando de hacer un bdcuenta, y cuando pongo un rango de fechas no encuentra nada. Si pongo una fechha sola, sin signos de mayor o menor, encuentra lo que busco. En las celdas que uso de filtro escribo lo siguiente > 01/09/2007, y < 01/10/2007. Si pongo una fecha que figure en los datos, por ejemplo 28/09/07 me cuenta sin problemas. Aparentemente hay algun formato que me falta. Sera como el excel que tiene que llevar la fecha entre numerales? Gracias

Jorge L. Dunkelman 13 octubre, 2007 14:46  

Hola Juan,
una posibilidad es que las fechas en la tabla de datos sean fechas pero el dato que pones en el rango de criterios sea interpretado como texto.
Debés tener en cuenta que Excel exhibe las fechas de acuerdo al formato elegido, por ejemplo 15/01/2007, pero lo que Excel "ve" en la celda es el número serial 39097. O lo contrario.
Si quieres puedes mandarme el archivo a jorgedun@gmail.com para que le de un vistazo.

Anónimo,  25 octubre, 2007 20:58  

Como puedo hacer para poder extraer los datos de una tabla a otra hoja solo de las celdas llenas?

Jorge L. Dunkelman 25 octubre, 2007 21:27  

Hay varias formas. Si se trata de una sola columna, aplicando Autofiltro y filtrando con el criterio No Vacías.
Si se trata de una tabla de varias columnas, habría que analizar como quieres organizar los datos en la hoja de destino.

Anónimo,  29 abril, 2008 23:11  

Don Jorge:
Estoy construyendo una tabla diaria para llevar el control prespuestario con las ampliaciones, reducciones y ejecuciones, pero necesito hacer una formula condicional que me permita darme el resultado por actividad(termino numerico), producto y region (terminos de texto).
Gracias

Jorge L. Dunkelman 29 abril, 2008 23:55  

¿Por qué una fórmula condicional? Para sumar resultados con varias condiciones podemos usar funciones base de datos (el tema de esta nota), SUMAPRODUCTO, tablas dinámicas y otras técnicas. Qué técnica usar depende en gran medida del diseño de la hoja o las hojas donde registras los datos.

Anónimo,  08 junio, 2008 09:45  

Hola Jorge, me puedes explicar como trabajar con las funciones BD cuando tengo una base de datos al reves? es decir mi campo es una fila no una columna.

Gracias!

Jorge L. Dunkelman 08 junio, 2008 10:47  

Existe la posibilidad de trasponer la tabla (Copiar-Pegado Especial-Trasponer), y luego usar las funciones normalmente.

Cristian,  09 junio, 2008 02:34  

Mi base de datos es un poco complicada para trasponer...esa seria la unica solucion?

Gracias por tu pronta respuesta!

Jorge L. Dunkelman 09 junio, 2008 10:53  

Hola Cristian

hay otras herramientas fuera de las funciones BD, que pueden hacer la tarea. Por ejemplo, tablas dinámicas.
Puedes mandarme el archivo o un ejemplo para que vea que solución te pueda sugerir.

Anónimo,  11 junio, 2008 08:55  

Jorge, al final pude transponer la tabla sin problemas y obtuve la información que necesitaba.

Nuevamente gracias!!!

Cristian

brayanhabid 01 diciembre, 2008 19:16  

hola. queria saber si era posible mostrar los nombres de los que cumplen la condicion, y no solo la cantidad.

Jorge L. Dunkelman 01 diciembre, 2008 22:57  

Si, pero tendrías que usar Filtro Avanzado con la opción Copiar a otro lugar. Puedes fijarte en las notas del blog sobre Filtro Avanzado.

Álvaro,  31 enero, 2009 18:45  

Hola Jorge, necesito tu ayuda porque estoy haciendo una base de datos en la cual hay una primer columna donde ubico el nombre de autopartes(ej.motor), en la segunda columna la marca del artículo (ej.Ford) y en la tercera el precio. Yo quería utilizar la fórmula BDEXTRAER para poner como condiciones el nombre del artículo y la marca y que la fórmula me devolviera el precio pero no puedo lograrlo. Quisera que me des alguna recomendación. Gracias

Jorge L. Dunkelman 31 enero, 2009 21:24  

Tendría que ver que es lo que no funciona en tu fórmula, pero puede señalarte que hay otras alternativas para tu tarea como usar una fórmula como fórmulas matriciales (o SUMAPRODUCTO) y tablas dinámicas.

Jim McLean 05 octubre, 2009 17:06  

Hola Jorge,
Estoy utilizando BDSuma en una tabla de 45000 filas ya que con fórmulas volátiles tipo sumaproducto o matriciales se me hace eterno el cálculo. Mi pregunta es si BDSUMA puede devolver una dato dándole un rango de fechas desde.... hasta..... En tu ejemplo sería si BDSUMA puede devolver el dato desde el alumno 1 al 8 por ejemplo. ¿Es posible?
Gracias

Jorge L. Dunkelman 05 octubre, 2009 19:16  

Las funciones BD calculan resultados, no "devuelven" datos. No veo ningún inconveniente paa hacer lo que describís, pero me parece que una tabla dinámica sería una solución más eficiente.

Anónimo,  22 enero, 2010 08:44  

Hola, tengo una base de alumnos, y quisiera saber si existe algún modo de que la hoja me muestre el NOMBRE del alumno con el mejor promedio, no solo la mejor calificación. Gracias de antemano

Jorge L. Dunkelman 22 enero, 2010 13:48  

Supongo, a pesar de que no lo describís, que la hoja contiene una columna con los nombres y vrias columnas con las calificaciones de las distintas materias (asignaturas). Naturalmente tiene que existir una columna donde se calculen los promedios de cada alumno. Después se puede aplicar formato condicional, por ejemplo, para poner un fondo de color en la celda con el nombre del alumno con el mayor promedio.
También se puede poner una celda donde se calcule el mayor promedio (con la función MAX) y luego extraer el nombre con INDICE y COINCIDIR.
En fin, tendías que ser más explícito.

Anónimo,  03 febrero, 2010 18:31  

Hola Jorge...
Tengo un inconveniente:
en la primera columna tengo una serie de marcas en la cual existen marcas duplicadas, en la siguiente columna tengo valores correspondientes a las marcas, ¿como puedo formular mi hoja, para que me saque los valores primero mayor, segundo mayor y tercero mayor de alguna marca que yo determine?, es decir de una sola marca, me arroje los valores primero, segundo y tercero mayores...
Gracias

Jorge L. Dunkelman 03 febrero, 2010 19:44  

Una posibilidad es usar Autofiltro con la opción 10 mejores. Esta opción,a pesar del nombre, permite determinar cuantos elementos mostrar.

Anónimo,  08 marzo, 2010 17:36  

como hago para realizar una depuracion de personas que tengo en dos archivos los cuales hay personas con sus respectivos numero de cedula pero necesito datos reales, es decir, las personas que de verdad me hacen falta, las que no aparscan repetidas, por favor ayudame ok. gracias
dailer.

Jorge L. Dunkelman 08 marzo, 2010 18:24  

Ponte en contacto conmigo por mail, pero por favor lee primero lo que escribo en la pestaña Ayuda.

Anónimo,  13 diciembre, 2011 05:28  

Todo lo que pueda aprender sobre Excel, Access, y lo que sea, se agradece, gracias por ponerlo sin animo de lucro.

Anónimo,  13 diciembre, 2011 05:32  

Gracias por poner estos conocimientos al alcance de todos sin ánimo de lucro.

Anónimo,  27 junio, 2012 10:52  

Tengo que depurar una gran base de datos: tengo datos de todos los bancos europeos por filas (un año en cada fila), y en una columna, una de las variables por la que quiero empezar a limpiar la base. ¿cómo puedo hacer para quedarme sólo con aquellos bancos que dispongan de datos para un mínimo de 8 años consecutivos?

Gracias

Jorge L. Dunkelman 27 junio, 2012 14:54  

Podrías, por ejemplo, agregar una columna auxiliar con la función CONTAR.SI para determinar cuántas filas hay para cada banco. Las que tengan un resultado menor a 8 las eliminas.

ErNietoVirtual 18 julio, 2012 12:45  

Cómo puedo dar formato al texto que me dá como resultado de una fórmula en una celda.? Por ejemplo: Si un dato es positivo que saliera en verde o si un resultado es negativo aparezca en rojo:
Si conoce alguien la manera de formularlo de manera automática, agradecería lo comentaran. Mi correo es atoledoc@gmail.com, suelo leerlo a diario mucho más que este foro.
Y muchas gracias de antemano

Jorge L. Dunkelman 18 julio, 2012 18:22  

Usando Formato Condicional!

Te sugiero que no publiques tu correo electrónico en un comentario, excepto que quieras ver tu casilla de correo inundada de spam.

Juancho CABJ 17 agosto, 2012 00:40  

Como hago para poner en el criterio una fórmula? Tengo que contar la cantidad de personas que su apellido empiece con S! Gracias!

Anónimo,  18 diciembre, 2012 02:41  

Hola, necesito ayuda por favor... yo tengo una base de datos similar a lo que escribo abajo... necesito una formula que me filtre el Id. y posteriormente sume los resultados de P1, después de P2 y después de P3 correspondientes a ese Id.

Id P1 P2 P3

AI-014 5 5 5
AI-020 5 3 4
AI-020 4 3 4


Por ejemplo, en base a la tabla que escribí arriba necesito que mi resultado sea:

Id P1 P2 P3

AI-014 5 5 5
AI-020 9 6 8

Habrá alguna fórmula con la que lo pueda hacer?

Espero me puedas ayudar.
GRACIAS! :)

Jorge L. Dunkelman 20 diciembre, 2012 14:20  

El filtrado no se hace con fórmulas sino con Autofiltro o Filtro Avanzado. Ambas funcionalidades permiten filtrar por varios niveles.

Anónimo,  30 junio, 2013 20:59  

Jorge, primero que nada agradezco toda tu disponibilidad para contester todas nuestras dudas, empiezo a usar excel (manejo lotus 123 desde que salio al mercado)asi que tengo base de datos de alumnos donde tienen 15 columnas de datos(apellidos,nombre,semestre,nº contro,etc)como hago para sacar la fila de datos de todos los alumnos de 8º semestre, y que me los enliste?,(en lotus lo hago con data query)Gracias chava

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP