martes, marzo 14, 2006

Manejo de pequeñas bases de datos en Excel – Acceso a base de datos externos con MS Query

Esta es otra nota de la serie sobre manejo de pequeñas bases de datos en Excel. Las entradas anteriores fueron

1 -
Manejo de pequeñas bases de datos en Excel con tablas dinámicas (pivot tables), donde nos ocupamos de cómo construir una lista o tabla que sirva de base para generar una tabla dinámica (pivot table).


2 -
Manejo de pequeñas bases de datos en Excel con tablas dinámicas (pivot tables) - Actualización de Datos, donde vimos como actualizar la tabla dinámica cada vez que los datos de la base de datos cambian.

3-
Manejo de pequeñas bases de datos en Excel – Referencias dinámicas con nombres (NAMES), donde expliqué como generar una referencia al rango de la base de datos en la tabla dinámica, de manera que no haga falta actualizarlo manualmente con cada cambio.

En todas estas entradas la base de datos de la tabla dinámica era una lista que se encontraba en una hoja de Excel. A esta tabla le agregamos datos (o borramos datos de ella) manualmente.
En esta cuarta entrada nos ocuparemos de cómo acceder bases de datos externas con Excel. Para esta tarea Excel cuenta con un programa auxiliar, el MS Query.
Para los ejemplos de esta entrada he utilizado la base de datos Northwind, que forma parte del paquete Office. Estos archivos se encuentran (Office XP) en la carpeta C:\Program Files\Microsoft Office\Office10\Samples .

Para importar los datos externos a una hoja Excel usamos los comandos Datos--->Obtener datos externos--->Nueva consulta de base de datos.





Si se fijan en la barra inferior de la pantalla, verán que Excel a abierto un nuevo programa, el MS Query




En el diálogo que se abre, debemos señalar cual es nuestra fuente de datos



En nuestro ejemplo basta con señalar el tipo de base datos, MS Access, que ya figura en la lista de MS Query. A veces el tipo de base de datos no aparece en al lista y el acceso debe ser creado. En este ejemplo nos limitaremos al primer caso.
Luego de elegir la fuente, elegimos la tabla y/o los campos de la tabla que estamos interesados en importar



Luego de elegir la fuente, veremos la lista tablas disponibles. Para no complicar nuestro ejemplo elegiremos sólo una tabla. Al lado del nombre de cada tabla hay un símbolo "+". Al pulsarlo veremos la lista de campos de la tabla- Esto nos permite elegir cuales estamos interesados en importar. Cada campo ocupará una columna en la hoja de Excel.


En nuestro ejemplo elegimos la tabla "Alphabetical List of Products", y de ella los campos que aparecen en la imagen (eso se hace señalando los campos en la ventanilla izquierda y pulsando la flecha en dirección a la ventanilla derecha)



Luego podemos establecer criterios para la importación, por ejemplo productos cuyos precios sean mayores de 15



En el próximo diálogo podemos ordenar los datos de acuerdo a los campos



Finalmente podemos elegir si importar los datos directamente a la hoja de Excel, si ver los datos en el MS Query (luego se pueden importar desde allí) o crear un cubo OLAP (tema sobre el cual escribiré en el futuro)



Si elegimos importar los datos a una hoja Excel, debemos indicar a partir de qué celda comenzar (por lo general será A1)



Como pueden ver, Excel nos propone en este mismo diálogo crear una tabla dinámica.

Apretamos "Aceptar" y Excel importará los datos a la hoja



A partir de este momento podemos trabajar como con toda lista de datos en Excel, ordenar los datos, generar subtotales, usar filtros y generar tablas dinámicas.


Si en lugar de importar los datos a una hoja, elegimos la opción "Crear un informe de tabla dinámica", Excel abrira una plantilla de tabla dinámica vacía. En lugar de importar los datos a una hoja, Excel establece un vínculo con los datos en la tbla de la base de datos.

En la próxima entrada daré una breve explicación de cómo proceder cuando la fuente de datos no aparece en la lista de MS Query.


Categorías: Manejo de Datos_




Technorati Tags: , ,




Si te gustó esta entrada anotala en del.icio.us

19 comentarios:

  1. Hola Manuel Alejandro, te estoy mandando un mail pidiendo aclaraciones sobre tus preguntas. De todas maneras, las hojas de Excel tienen un máximo de 65536 filas, así que no tengo claro cómo es el problema. Como supongo que se trata de todas maneras de hojas con muchas filas habría que buscar cuál es la manera más efeciente de calcular. Sobre ese tema te escribo en el mail.
    Sobre tu segunda pregunta, no hay forma que puedas abrir archivos con fórmulas, solo con Vba (macros). Si se puede establecer una referencia dinámica a un cuaderno. Sobre esto tambien en mi mail.

    ResponderBorrar
  2. Estimado Jorge: tengo separado por cada mes archivos llamados por la fecha del día que se crearon (ej. 25-06-07; 26-06-07, etc) de los cuales necesito sacar información en una planilla de todo lo registrado en la misma celda en todo el mes. Es posible lograr ésto? gracias- torresguille@gmail.com

    ResponderBorrar
  3. Hola, en principio se puede. Tienes que crear una referencia al archivo. Si la referencia tiene que ser dinámica, es decir en una celda pones la fecha y en la celda que contiene la fórmula aparece el valor en el archivo remoto, tendrás que usar la función INDIRECTO. Esta función tiene la limitación de exigir que los archivos de referencia están abiertos.
    Puedes leer mi nota sobre la función INDIRECTO y sobre cómo usarla con cuadernos cerrados

    ResponderBorrar
  4. Estimado Jorge, estoy generando consultas en excel desde archivos acces, mi problema es que me salé el error de "La consulta devolvio mas datos de los que se admiten en una hoja de calculo", lo raro es que solo tengo 2450 filas y como 10 columnas.

    Ojalá me puedas orientar.

    atte

    Manuel
    limaribraniff@gmail.com

    ResponderBorrar
  5. Hola Manuel, sin ver los archivos es difícil darse una idea de donde pueda estar el problema.
    Si todo lo que tienes en el archivo Access, que es la base de la tabla dinámica, son 2450 filas y 10 columnas, puede ser que estés poniendo los filas el Access en la zona de campos de columnas. Eso resultaría en un intento de poner 2450 columnas en la hoja de Excel, lo que no es posible.

    ResponderBorrar
  6. Hola!

    Tengo una duda respecto a este tema.

    Puedo hacer una consulta y poner que, por ejemplo, la columna "Productos" sea igual a "C4". Es decir hacer referencia a una celda para efectual el filtro??

    Espero tu respuesta.

    ResponderBorrar
  7. Si te refieres a criterios en el MS Query, la respuesto es no.

    ResponderBorrar
  8. Hola!

    Hace dos meses que descubrí su blog, y no puedo más que felicitar y agradecer el esfuerzo que le dedica.

    Por otro lado tengo una consulta que agradecería que pudiese atender, porque por más que busco en la red no he encontrado una solución que no implique acces o formulas pesadas:

    Tengo varios libros, uno por cada compañía que gestiono. Todos tienen una hoja resumen con una tabla de todos los movimientos anuales de dicha compañía. Todas las tablas tienen exactamente la misma estructura.

    Lo que pretendo es que en otro libro, aparezca en una tabla todos esos datos y que se puedan actualizar sin tener los libros abiertos (puesto que son unos cuantos).

    En principio creí que lo más fácil sería seguir el ejemplo que ha puesto con MS Query, ya que puedo actualizar con un botón todos los datos con los archivos cerrados. Pero por más que lo intento no puedo vincular más de 2 cuadernos a la vez para realizar la consulta, y la opción "ayuda" no me ha servido de mucho.

    ¿Quizás no es éste el mejor camino?¿Alguna sugerencia?

    ResponderBorrar
  9. También yo le he intentado y veo que existe algún problema que impide incluir más de dos tablas cuando estas provienen de distintos cuadernos. Espero publicar una nota explicando como solucionar este problema.

    ResponderBorrar
  10. Bueno, el rodeo era más fácil de los que pensaba. Se puede leer en esta nota.

    ResponderBorrar
  11. Buenos dias Jorge, saludos desde Panamá, espero que este muy bien.
    No se si sera tarde esta consulta pero ahi va. Trabajo siempre importando datos de Access a Excel usando MSQuery de excel y los datos los uso en otras hojas de exce, ya sea con formulas, calculos etc.
    Esl problema esta que cuando en Datos/Refresch Data y me actualiza la importacion en las formulas o calculos de las otras hojas, se distorciona las referencias de la importacion, ya sea que salta filas o aparece el error REF. Como se resuelve eso.....GRACIAS.

    ResponderBorrar
  12. El problema que describes puede deberse a varias causas, pero me parece que tienes un problema con la estructura de las hojas. Los datos son importados a una hoja determinada. Si de esa hoja estás pasando los datos a otras hojas/cuadernos, tienes que mantener la estructura original.

    ResponderBorrar
  13. tu blog es excelente!!!!

    ResponderBorrar
  14. Muy buena la informacion, la pregunta q tengo es la siguiente: Cree una consulta a una base de datos con Microsoft Query y q me pida los parametros desde una celda, la inquietud es como hago si el campo es tipo fecha y necesito utilizar en Between???? e probado asi:
    Entre #2011/12/01 12:00:00 a.m.# Y #2011/12/01 11:59:59 p.m.#
    Entre #2011-12-01 12:00:00 a.m.# Y #2011-12-01 11:59:59 p.m.#
    Entre #01/12/2011 12:00:00 a.m.# Y #01/12/2011 11:59:59 p.m.#
    Entre #01-12-2011 10:44:10 a.m.# Y #01-12-2011 11:59:59 p.m.#

    ResponderBorrar
  15. Supongo que estás introduciendo los parámetros de la consulta directamente en MS Query. Bien, MS Query, no "habla castellano". En la fila de los criterios tienes que poner, en el campo de las fechas

    >=2011/12/01 12:00:00 a.m. and <=2011/12/01 11:59:59 p.m

    Creo que los símbolos # los agrega automáticamemnte (así es en Access).

    ResponderBorrar
  16. Estimado Jorge, muy buen tu página.
    Tengo una consulta que hacer.
    Tengo un archivo excel que es el resumen de una serie de actividades, por lo que cada celda tendria vinculada una query distinta, de acuerdo a diversos parametros.
    Como puedo vincular una celda en particular a una query en SQL. Dicha query es un select count(*), por lo que me entrega un solo número.

    ResponderBorrar
  17. Podrías usar ADO, siguiendo este ejemplo de la base de conocimientos de Microsoft.
    También podrías usar el MS Query, ya que el resultado se pegaría a una única celda.
    Pero tengo la sospecha que tendrías que organizar tus datos de otra manera. Por ejemplo, usando una única query para extraer los datos y usando una tabla dinámica para resumir por actividad. O mejor aún, crear una query que traiga el count para todas las actividades en una única tabla y pasar estos datos a la hoja de Excel.

    ResponderBorrar
  18. Hola Jorge, aprecio lo que haces con este blog...
    Mi pregunta es sobre la ubicación de Northwind.mdb en los archivos de ejemplo de office 2010, supuse que era C:\Program Files\Microsoft Office\Office 14\Samples\, pero no existe esa ruta
    Solo esta en C:\Program Files (x86)\Microsoft Office\Office 14\Samples\, pero tampoco hay nada
    Sospecho que esa base de datos de ejemplo ya no esta instalada en mi equipo
    Gracias de antemano por tu comentario

    ResponderBorrar
  19. La base de datos Northwind no viene con el paquete Office 2010.
    En esta página hay un enlace para descargar la nueva versión de la base de datos.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.