jueves, 30 de julio de 2009

Ejemplo de Funciones para Bases de datos.

Ocurre en ocasiones que al trabajar con nuestro listados, o con nuestras bases de datos en Excel, no son suficientemente potentes la 'funciones normales' de Excel; por ejemplo por que necesitamos una suma condicionada a varios de los campos de nuestro origen de datos. Normalmente con la función SI, o con la suma condicionada SUMAR.SI (ambas ya vistas en diferentes post de ese blog) podíamos concluir nuestros análisis, de una forma elaborada.
Pero existen algunas funciones de bases de datos (así categorizadas por Excel en el listado de funciones), que aunque pocas, reproducen especialmente diseñadas para Bases de datos de Excel las funciones más básicas (SUMA, CONTAR, PROMEDIO, MAX, MIN, PRODUCTO, etc.); las diferenciamos por que estas funciones de bases de datos comienzan con 'BD' (BDCONTAR, BDSUMA, etc).
Lógicamente estas funciones necesitan una manera particular de ser definidas, es decir, a la hora de incorporarle sus argumentos habrá que pensar de diferente forma -condiciones o criterios aplicados sobre distintos campos-.
Los argumentos mencionados son siempre tres, para todas estas funciones:
Base_de_datos es el rango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada columna.
Nombre_de_campo indica el campo que se utiliza en la función. Nombre_de_campo puede ser texto con el rótulo encerrado entre dobles comillas, o como un número que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente.
Criterios es el rango de celdas que contiene las condiciones especificadas. Puede utilizar cualquier rango en el argumento Criterios mientras éste incluya por lo menos un rótulo de columna y por lo menos una celda debajo del rótulo de columna que especifique una condición de columna.
Es importante remarcar este argumento de Criterios, ya que para poder ejecutar estas funciones de Bases de datos, necesitaremos un rango independiente de nuestro listado de datos a analizar.
Propondremos un ejemplo sencillo donde poder practicar algunas de estas funciones. Tenemos un listado sacado de nuestro diario contable, en el que tenemos información de la Fecha del registro contable, de la Cuenta de gasto empleada, si proede de una factura o de un abono y del importe (nos aparecen todos en positivo, ya que el signo no lo indica ese campo factura/abono):


Plantearemos varias preguntas sobre ese listado e iremos viendo la forma de responderlas con estas fuciones de bases de datos.
Si necesitamos conocer cuántos registros he tenido en mi contabilidad con la cuenta 622, entonces aplico sobre el listado la función =BDCONTAR($A$1:$D$24;"Cuenta";F4:F5)
Nos fijaremos que el tercer argumento de criterios(F4:F5) nos dirije a unas celdas definidas por mi.
Si pretendemos saber cuál ha sido el acumulado en euros de todas nuestras facturas, aplico la función =BDSUMA(A1:D24;"Importe";$F$9:$F$10)
Si quisiera discriminar todas aquellas facturas de mi listado cuyos importes estuviera comprendido entre 0 eur y 5.000 eur resumiría con la siguiente función
=BDSUMA($A$1:$D$24;"Importe";F12:H13)


Una regla general a todas estas funciones, para saber cómo aplicarlas es: dentro del origen de datos seleccionado, ejecútame la operación dada por la funcion BD sobre el campo del segundo argumento, pero condicionada por el rango de condiciones sobre distintos campos.

P.D.: La forma de entender estas funciones de base de datos es muy similar a la empleada con los Filtros avanzados -dedicaremos una entrada en el futuro a este tema-.

27 comentarios:

  1. Enhorabuena por el blog. Una duda con la función BDSUMA. SI tuvieras que darle un rango de fechas en tu ejemplo, del 30/07/09 al 7/08/09 ¿Cómo lo harías?

    ResponderEliminar
  2. Muchas gracias.
    He contestado a tu cuestión en la siguiente entrada
    http://www.excelforo.blogspot.com/2009/10/otro-ejemplo-de-bdsuma.html
    También puedes verla en la etiqueta de 'Funciones de base de datos'.
    Un saludo

    ResponderEliminar
  3. Muy buena tu informacion, me ha sido de mucha ayuda. Gracias.

    ResponderEliminar
  4. Tengo una columna C de 13f y los cada fila tiene la CI de Alumno,cada vez que se Retira(R)una CI se coloca la siguiente CI.
    Ejemp. A B C
    R 1 V193205
    R 2 V194566
    3 V205325
    4 V206543
    R 5 V217765
    6 V218875
    RESULTADO
    A B
    1 V205325
    2 V206543
    3 V218875

    DE ANTE MANO GRACIAS.

    ResponderEliminar
  5. CHEVERE POR TU APORTE me ayudoen especial la funcion BDSUMA con criterios de rango de fecha. Sige adelante. Gracias

    ResponderEliminar
  6. Hola! Me estoy liando con la función BDSuma: Tengo una tabla con años, trimestres, precios, índices y aumentos. En la columna años tengo varios y todos se repiten 3 0 4 veces (2002, 2002....2003,2003,....). Ahora bien, debo introducir en unas celdas la función de base de datos BDSUMA, que calcule solo la suma de los aumentos correspondientes a los años 2002, 2003, 2004, 2005, 2006 (Cada año con sus aumentos) y que son algunas de las fechas que se me muestran en la tabla, pues hay fechas desde el año 1995 hasta 2007.

    ResponderEliminar
    Respuestas
    1. Hola,
      bueno, sería la función BDSUMA, efectivamente sobre el Rango de datos, y a aplicar el rango de criterios.
      Por ejemplo supongamos tu rango de datos A1:E10 (con los campos en el orden que indicas: años, trimestres, precios, índices y aumentos), por otro lado el rango de criterios estará en el rango G1:H2 (en G1 y H1: "años" y en G2:>=2002 y eh H2: <=2006.
      Tu fórmula quedará:
      =BDSUMA(A1:E10;"aumentos";G1:H2)

      Slds

      Eliminar
  7. Que tal.. el ejercicio q no logro resolver consiste en una pregunta: ¿Determine la cantidad de clientes ingresados en el mes de "Marzo" sin importar el año. (los años de mis datos son diversos, 2000, 2001, etc,). se que se resuelve con BDCONTAR o BDCONTARA pero desconosco como poner el criterio o condición. :(

    ResponderEliminar
    Respuestas
    1. Hola,
      supongo que tendrás un Campo de MES sobre el que aplicar esa condición... si no fuera así, siempre podrías anexar una columna axuliar en el origen de datos que esa información. Luego efctivamente la función podría ser BDCONTAR
      =BDCONTAR(origendatos; "MES";rngcriterios)
      siendo rngcriterios un par de celdas, compuesta de una cabecera : "MES" y debajo: 3 (del mes de marzo).
      Espero lo veas más claro, al no conocer la estructura de tu origen es difícil ser más preciso.
      Slds

      Eliminar
  8. Hola, fuiste muy preciso amigo te lo agradesco, hice todo lo que dijiste, adicioné una columna "MES" Extraendo sus meses de las fechas (12/01/2000, 15/03/2010, etc) con =MES(A2) y asigné 2 celdas con la cabecera "MES" y abajo 3 como rngdcreiterios, otra vez gracias ahora si aprobare mi Practica:D (Y).

    ResponderEliminar
    Respuestas
    1. Gracias a tí, Jhonatan...
      espero mi propuesta te sirva para aprobar esa práctica.
      Sdls cordiales

      Eliminar
  9. Hola ¿sabes porque cuando uso las funciones BD todo me da cero? ya cheque y si hay valores no tendría porque ser cero.
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Jenai,
      tendría que ver cómo construyes la función...
      en principio necesitas una base de datos (un rango con cabecera) y un rango de criterios a aplicar sobre esta, luego basta indicar a la función BD sobre qué campo ejercer la acción (suma, contar, promedio, etc).
      Si cumples con los parámetros tendría que devolverte algo.

      Dime como construyes la función y te comentaré.
      Slds

      Eliminar
  10. BUENAS NOCHES, SU APOYO, DESEO CREAR UNA TABLA DINAMICA CONLOS SIG. DATOS.
    NUM DE CLIENTE, CLIENTE, FECHA_INICIO,FECHA_PAGO1,PAGO1,FECHA_PAGO2,PAGO2,TOTAL. Y O SE COMO HACERLA, GRACIAS POR SU APOYO

    ResponderEliminar
    Respuestas
    1. Hola,
      lo primero es suponer que tienes una Tabla con al menos esos campos (un origen de datos en una hoja o quizá una base de datos externa).
      Para construir una TD necesitas seleccionar el origen de datos y desde la Ficha Insertar > grupo Tablas > botón Tablas dinámicas.
      Te aparecerá la TD con una Lista de campos, sólo arrastra cada campo que necesites a las diferentes áreas (filas, columnas, valores) hasta que la TD tenga la forma que requieras...

      Sin más datos poco más te puedo decir.
      Slds cordiales

      P.D.: por favor, evita escribir en mayúsculas.

      Eliminar
  11. Hola, muy buenas.
    Bueno he leído este post (y muchos otros de este "foro"); y quería comentarle que tengo que hacer lo siguiente:
    Contar cuantas personas han nacido en Madrid, en una tabla específica.

    Pues bien, la función sería esta:

    =BDCONTAR(B3:H13;"Provincia de Nacimiento";G3:G13)

    El "titulo" por así decirlo, de la columna donde están las provincias de nacimiento, se llama:

    Provincia de nacimiento

    Según el post anterior de ustedes, la fórmula me debería de contar y no lo hace. ¿Hay que hacer algo antes?; ¿Cómo hay que hacerlo?... Creo que no logro entenderlo.

    Ante todo, gracias por su ayuda.

    ResponderEliminar
    Respuestas
    1. Hola,
      estas funciones de bases de datos, como quedan explicadas en este y otros del blog, funcionan siempre igual.
      El primer argumento corresponde al origen de datos (incluida la cabecera), en tu caso B3:H13.
      El segundo argumento el nombre (o número de columna) del campo sobre el que queremos realizar la acción de la función, en tu caso CONTAR sobre la 'Provincia de Nacimiento'.
      El tercer y último argumento es el de criterios, debe estar compuesto de una primera fila con los títulos de los campos sobre los que aplicar las condiciones, que aparecen en las filas de abajo. En tu caso, debería ser en una celda por ejemplo J3 con el nombre del campo 'Provincia de Nacimiento' y debajo en la J4 la provincia 'Madrid'.
      Tu función debería quedar así:
      =BDCONTAR(B3:H13;"Provincia de Nacimiento";J3:J4)

      Aunque para ese conteo tan sencillo como el que planteas sería mejor aplicar la función
      =CONTAR.SI(G3:G13;"Madrid")
      suponiendo que G3:G13 es donde está el campo 'Provincia de nacimiento'

      Slds

      Eliminar
    2. Sigue dando error, ya que la fórmula que empleo es la misma que dejé en el post, me da el error típico de "Valor". Exacto, con la función CONTAR.SI, sería más sencillo pero el ejercicio está hecho sobre una base de datos. Entonces la cosa cambia. D:
      ¿No hay que formatear nada antes de poner la fórmula?, no sale. xD

      Gracias.

      Eliminar
    3. Hola,
      no hay que formatear nada, todo depende exclusivamente de los argumentos de la función (y seguramente del rango de criterios).
      Te has asegurado de incluir en los rangos (del origen y de los criterios) las cabeceras, y que el segundo argumento 'Provincia de Nacimiento' es exacto al de la cabecera ???

      No entiendo que tiene que ver que sea una 'base de datos' para que no puedas aplicar el CONTAR.SI.

      Si sigue dándote error, puedes enviármelo a
      excelforo@gmail.com
      Slds

      Eliminar
  12. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  13. necesito saber como hago para que en una sol celda bajo un criterio determinado me quepan segun el criterio, el contenido de varias celdas en 1 sola celda, una formula no se, lo hice con tabla dinamica pero necesito que toda esa info entre en solo 1 celda, se podra? ejm: tengo 6 celdas

    a1=E105201
    a2=E105202
    a3=E105203
    a4=P100541
    a5=P100542
    a6=P100543

    y el resultado debe ser: en b1=E105201 E105202 E105203
    y en b4=P100541 P100542 P100543 pero automatico osea formula o no se, ese resultado en 1 sola celda.

    ResponderEliminar
    Respuestas
    1. Hola Fanny,
      dependerá de los criterios o reglas que tengas que emplear una función SI condicional, para luego emplear la función CONCATENAR.
      O bien directamente en B1:
      =CONCATENAR(A1;" ";A2;" ";A3)
      luego selecciona B1:B3 y arrastra hacia abajo

      Espero haberte comprendio
      Saludos

      Eliminar
  14. Hola
    Voy a intentar dibujar el escenario de mi problema:
    Tengo un libro en una carpeta de un servidor de dominio en el cual hay varias tablas "proveedores", "Ventas", etc..
    Por otro lado tengo los libros de trabajo de cada usuario los cuales tienen una hoja ("Proveedores") con una tabla que se debe actualizar desde el libro anterior.
    A través de una conexión , al abrir el libro de trabajo, la tabla "Proveedores" se actualiza correctamente.
    Lo que no se hacer es que si un usuario, modifica, borra o añade un registro cuando esta en el libro de trabajo, automáticamente, se actualice la tabla de "Proveedores" en el Servidor para que esa actualización sea común a los demás usuarios.
    Muchas gracias por este Blog y Un saludo

    ResponderEliminar
    Respuestas
    1. Hola Francis,
      'cada vez haya una modificación' sería bastante complejo.. pero la conexión se puede configurar para actualizar automáticamente cada minuto si es necesario...

      Creo sería una posible solución

      Saludos cordiales

      Eliminar
    2. Hola
      La actualización automática cada n minutos, estaría bien en el caso de que la actualización fuese en ambas direcciones, pero solo lo hace desde el origen (libro común en el Servidor) hacia el libro de trabajo, por lo que se pierden las modificaciones que se hayan realizado en los libros de trabajo.
      Ahora el procedimiento que hago es:
      Al salir de la hoja "Proveedores" del libro de trabajo, compruebo si ha habido modificaciones (variable booleana "Datos_Nuevos") en caso afirmativo, copio la tabla "Tb_Proveedores"
      Abro el libro del Servidor
      Selecciono la Tabla "Proveedores"
      Pego la copia
      Guardo el libro, Volviendo a estar activo el libro de trabajo
      Refresco la consulta
      Para todo esto necesito ir activando y desactivando events para que no entre en un bucle infinito, usando la variable "Datos_nuevos"
      Este procedimiento funciona más o menos bien, pero lo veo lioso y seguro que hay otro método más eficaz, aunque sea más complejo, no me asusta.
      Gracias por contestar
      Un Saludo

      Eliminar
    3. Bien,
      siempre se podría (perdiendo funcionalidad en el libro origen) convertir en Libro compartido, que admite varios usuarios simultáneos y gestionar el control de cambios..
      No creo la programación aporte nada al modelo... ya que el problema, a mi modo de ver, es que el fichero origen podría estar abierto y en uso por otra persona en el mismo momento del proceso, lo cual generaría inconsistencias.
      Diría que quizá Excel no es la mejor herramienta para esto, y puesto que el tratamiento de datos simula un Gestor de base de datos, la herramienta recomendada sería Access (o similar)
      Saludos

      Eliminar
  15. Hola
    Si, tienes razon, creó que optaré por q cada usuario gestione su propia tabla de Proveedores dentro del libro de trabajo
    Gracias
    Un saludo

    ResponderEliminar