Puede utilizar Microsoft Query para recuperar datos de orígenes externos. Si utiliza Microsoft Query para recuperar datos de las bases de datos (base de datos: colección de datos relacionados con un fin o tema concreto. Dentro de una base de datos, la información sobre una entidad en particular, como un empleado o un pedido, se clasifica en categorías en tablas, registros y campos.) y de los archivos corporativos, no es necesario que vuelva a escribir los datos que desea analizar en Excel. También puede actualizar los informes y resúmenes de Excel automáticamente de la base de datos de origen inicial siempre que la base de datos se actualice con información nueva.
¿Qué desea hacer?
Obtener más información sobre Microsoft Query
Con Microsoft Query, puede conectar con orígenes de datos externos, seleccionar datos de esos orígenes externos, importar datos a la hoja de cálculo y actualizar los datos según sea necesario para mantener los datos de la hoja de cálculo sincronizados con los datos de los orígenes externos.
Tipos de bases de datos a los que se puede obtener acceso Es posible recuperar datos de varios tipos de bases de datos, incluidos Microsoft Office Access, Microsoft SQL Server y los servicios OLAP de Microsoft SQL Server. También puede recuperar datos de libros de Excel y de archivos de texto.
Microsoft Office facilita controladores que pueden utilizarse para recuperar datos de los siguientes orígenes de datos (origen de datos: conjunto almacenado de información de "origen" utilizado para conectarse a una base de datos. Un origen de datos puede incluir el nombre y la ubicación del servidor de la base de datos, el nombre del controlador de la base de datos e información que necesita la base de datos cuando se inicia una sesión.):
También puede utilizar controladores ODBC (controlador ODBC (Conectividad abierta de bases de datos): archivo de programa utilizado para conectarse a una base de datos concreta. Cada programa de base de datos, como Access o dBASE, o sistema de administración de bases de datos, como SQL Server, requiere un controlador diferente.) o controladores de origen de datos (controlador de origen de datos: archivo de programa utilizado para conectarse a una base de datos específica. Cada programa o sistema de administración de bases de datos requiere un controlador diferente.) de otros fabricantes para recuperar información de orígenes de datos que no figuran en esta lista, incluidos otros tipos de bases de datos OLAP. Para obtener información sobre cómo instalar un controlador ODBC o un controlador de origen de datos que no esté en la lista, vea la documentación de la base de datos o póngase en contacto con el proveedor de la misma.
Seleccionar datos de una base de datos Puede recuperar datos de una base de datos creando una consulta, que es una pregunta que se hace acerca de los datos almacenados en una base de datos externa. Por ejemplo, si los datos están almacenados en una base de datos de Access, puede que desee conocer las cifras de ventas de un producto determinado por regiones. Puede recuperar parte de los datos seleccionando sólo los datos del producto y la región que desee analizar.
Con Microsoft Query, puede seleccionar las columnas de datos que desee e importar sólo esos datos a Excel.
Actualizar la hoja de cálculo en una operación Cuando disponga de datos externos en un libro de Excel, siempre que cambie la base de datos, puede actualizar (actualizar: renovar datos de un origen de datos externo. Cada vez que se actualicen los datos, se verá la versión más reciente de la información en la base de datos, incluidos los cambios que se hayan realizado en los datos.) los datos y, a su vez, el análisis, sin tener que volver a crear los informes y los gráficos de resumen. Por ejemplo, puede crear un resumen de ventas mensual y actualizarlo cada mes cuando disponga de nuevas cifras de ventas.
Forma en la que Microsoft Query utiliza los orígenes de datos Una vez establecido un origen de datos para una base de datos determinada, lo podrá utilizar siempre que desee crear una consulta para seleccionar y recuperar los datos de esa base de datos, sin tener que volver a escribir toda la información de conexión. Microsoft Query utiliza el origen de datos para conectarse con la base de datos externa y mostrar los datos que están disponibles. Después de crear la consulta y devolver los datos a Excel, Microsoft Query proporciona al libro de Excel la información de la consulta y del origen de datos de modo que pueda volverse a conectar con la base de datos cuando desee actualizar los datos.
Utilizar Microsoft Query para importar datos Para importar datos externos en Excel con Microsoft Query, siga estos pasos básicos, cada uno de los cuales se describe con más detalle en las secciones siguientes.
- Conectar con un origen de datos.
- Utilizar el Asistente para consultas para definir una consulta.
- Trabajar con los datos en Excel.
Volver al principio
Conectar con un origen de datos
¿Qué es un origen de datos? Un origen de datos es un conjunto de información que permite que Excel y Microsoft Query se conecten con una base de datos externa. Cuando utilice Microsoft Query para establecer un origen de datos, asigne un nombre al origen de datos y, a continuación, proporcione el nombre y la ubicación de la base de datos, el tipo de base de datos y la información de inicio de sesión y la contraseña. En esta información también se incluye el nombre de un controlador OBDC o un controlador del origen de datos, que es un programa que realiza conexiones con un tipo de base de datos determinado.
Para definir un origen de datos mediante Microsoft Query:
- En la ficha Datos, en el grupo Obtener datos externos, haga clic en De otras fuentes y luego en Desde Microsoft Query.
- Siga uno de los procedimientos siguientes:
- Haga doble clic en <Nuevo origen de datos>.
O bien,
Haga clic en <Nuevo origen de datos> y luego en Aceptar.
Aparecerá el cuadro de diálogo Crear nuevo origen de datos.
- En el paso 1, escriba un nombre para identificar el origen de datos.
- En el paso 2, haga clic en un controlador para el tipo de base de datos que se va a utilizar como origen.
Si la base de datos externa a la que desea obtener acceso no es compatible con los controladores ODBC instalados con Microsoft Query, deberá conseguir e instalar un controlador ODBC que sea compatible con Microsoft Office de otro proveedor, como el fabricante de la base de datos. Póngase en contacto con el proveedor de la base de datos para obtener instrucciones sobre la instalación.
Nota Las bases de datos OLAP no requieren controladores ODBC. Cuando se instala Microsoft Query, se instalan los controladores de las bases de datos que se crearon mediante Microsoft SQL Server Analysis Services. Para conectarse a otras bases de datos OLAP, deberá instalar un controlador de origen de datos y un software cliente.
- Haga clic en Conectar y, a continuación, proporcione la información necesaria para conectarse al origen de datos. Para las bases de datos, los libros de Excel y los archivos de texto, la información proporcionada dependerá del tipo de origen de datos que haya seleccionado. Es posible que se pida un nombre de conexión, una contraseña, la versión de la base de datos que utiliza, la ubicación de la base de datos e información adicional específica para el tipo de base de datos.
Utilice contraseñas seguras que combinen letras en mayúsculas y minúsculas, números y símbolos. Las contraseñas no seguras son aquellas que no combinan estos elementos. Un ejemplo de contraseña segura sería Y6dh!et5 y de contraseña no segura, Casa27. Las contraseñas deben tener 8 o más caracteres. Una frase con 14 o más caracteres es todavía mejor. Para obtener más información, vea Ayudar a proteger la información personal con contraseñas seguras.
Es fundamental que recuerde la contraseña. Si la olvida, Microsoft no puede recuperarla. Guarde las contraseñas que anote en un lugar seguro, lejos de la información que ayudan a proteger.
- Tras especificar la información necesaria, haga clic en Aceptar o Finalizar para volver al cuadro de diálogo Crear nuevo origen de datos.
- Si la base de datos tiene tablas (tabla: conjunto de datos acerca de un tema determinado que se almacena en registros (filas) y campos (columnas).) y desea que aparezca automáticamente una tabla determinada en el Asistente para consultas, haga clic en el cuadro del paso 4 y, a continuación, en la tabla que desee.
- Si no desea especificar el nombre de conexión y la contraseña cuando utilice el origen de datos, active la casilla de verificación Guardar Id. y contraseña en la definición de origen de datos. La contraseña guardada no está cifrada. Si esta casilla de verificación no está disponible, póngase en contacto con el administrador de la base de datos para averiguar si esta opción puede habilitarse.
Seguridad Debe evitarse el guardar la información de inicio de sesión durante la conexión a un origen de datos. Dicha información podría almacenarse como texto sin formato y, con ello, quedar al alcance de usuarios malintencionados capaces de comprometer la seguridad del origen de datos.
Al finalizar estos pasos, aparece el nombre del origen de datos en el cuadro de diálogo Elegir origen de datos.
Volver al principio
Utilizar el Asistente para consultas para definir una consulta
Utilice el Asistente para consultas para la mayoría de las consultas El Asistente para consultas permite seleccionar y reunir fácilmente datos de diferentes tablas y campos en la base de datos. Mediante el Asistente para consultas, pueden seleccionarse las tablas y los campos que se desee incluir. Una combinación interna (una operación de consulta que especifica que se combinen las filas de dos tablas en función de valores de campo idénticos) se crea automáticamente cuando el asistente reconoce un campo de clave principal en una tabla y un campo con el mismo nombre en la otra tabla.
Asimismo, puede utilizar el asistente para ordenar el conjunto de resultados y realizar una operación de filtrado sencillo. En el último paso del asistente, podrá elegir devolver los datos a Excel o depurar la consulta en Microsoft Query. Una vez creada la consulta, podrá ejecutarla en Excel o en Microsoft Query.
Para iniciar el Asistente para consultas, siga estos pasos.
- En la ficha Datos, en el grupo Obtener datos externos, haga clic en De otras fuentes y luego en Desde Microsoft Query.
- En el cuadro de diálogo Elegir origen de datos, asegúrese de que la casilla de verificación Usar el Asistente para consultas para crear o modificar consultas está activada.
- Haga doble clic en el origen de datos que desea utilizar.
O bien,
Haga clic en el origen de datos que desea utilizar y, a continuación, haga clic en Aceptar.
Trabajar directamente en Microsoft Query con otros tipos de consultas Si desea crear una consulta más compleja que las que permite crear el Asistente para consultas, puede trabajar directamente en Microsoft Query. Puede utilizar Microsoft Query para ver y cambiar las consultas que haya comenzado a crear en el Asistente para consultas, o puede crear consultas nuevas sin utilizar el asistente. Trabaje directamente en Microsoft Query cuando desee crear consultas que hagan lo siguiente:
- Seleccionar datos específicos de un campo En una base de datos grande, puede elegir algunos de los datos de un campo y omitir los datos no necesarios. Por ejemplo, si necesita los datos de dos de los productos de un campo que contiene información de muchos productos, puede utilizar criterios (criterios: condiciones que se especifican para limitar los registros que se incluyen en el conjunto de resultados de una consulta o un filtro.) para seleccionar los datos únicamente de los dos productos que desea.
- Recuperar datos basados en criterios diferentes cada vez que se ejecute la consulta Si necesita crear el mismo informe o resumen de Excel para varias áreas de los mismos datos externos, como un informe de ventas distinto para cada región, puede crear una consulta de parámetros (consulta de parámetros: tipo de consulta que, cuando se ejecuta, pide los valores (criterios) que se utilizarán para seleccionar los registros para el conjunto de resultados, de modo que una misma consulta puede utilizarse para recuperar diferentes conjuntos de resultados.). Cuando se ejecuta una consulta de parámetros, se pide un valor que se utiliza como criterio cuando la consulta selecciona los registros. Por ejemplo, una consulta de parámetros puede solicitar que escriba una región concreta, y esta consulta se puede volver a utilizar para crear cada uno de los informes de ventas regionales.
- Combinar datos de formas distintas Las combinaciones internas que crea el Asistente para consultas son el tipo de combinación que se utiliza con más frecuencia a la hora de crear consultas. Sin embargo, en algunas ocasiones, tal vez desee utilizar un tipo de combinación diferente. Por ejemplo, si tiene una tabla de información de ventas de productos y otra tabla de información de clientes, una combinación interna (el tipo de combinación creado por el Asistente para consultas) impedirá que se recuperen los registros de los clientes que no hayan realizado ninguna compra. Con Microsoft Query, puede combinar estas tablas de forma que se recuperen todos los registros de los clientes, junto con los datos de ventas de los clientes que han efectuado alguna compra.
Para iniciar Microsoft Query, siga estos pasos.
- En la ficha Datos, en el grupo Obtener datos externos, haga clic en De otras fuentes y luego en Desde Microsoft Query.
- En el cuadro de diálogo Elegir origen de datos, asegúrese de que la casilla de verificación Usar el Asistente para consultas para crear o modificar consultas está desactivada.
- Haga doble clic en el origen de datos que desea utilizar.
O bien,
Haga clic en el origen de datos que desea utilizar y, a continuación, haga clic en Aceptar.
Volver a utilizar y compartir consultas Tanto en el Asistente para consultas como en Microsoft Query, puede guardar las consultas como un archivo .dqy que podrá modificar, volver a utilizar y compartir. Excel puede abrir archivos .dqy directamente, por lo que se podrán crear rangos de datos adicionales desde la misma consulta.
Para abrir una consulta guardada desde Excel:
- En la ficha Datos, en el grupo Obtener datos externos, haga clic en De otras fuentes y luego en Desde Microsoft Query. Aparecerá el cuadro de diálogo Elegir origen de datos.
- En el cuadro de diálogo Elegir origen de datos, haga clic en la ficha Consultas.
- Haga doble clic en la consulta guardada que desea abrir. La consulta se muestra en Microsoft Query.
Si desea abrir una consulta guardada y Microsoft Query ya está abierto, haga clic en el menú Archivo de Microsoft Query y luego en Abrir.
Si hace doble clic en un archivo .dqy, Excel abre y ejecuta la consulta y, después, inserta los resultados en una nueva hoja de cálculo.
Si desea compartir un resumen o informe de Excel basado en datos externos, puede dar a otros usuarios un libro que contenga un rango de datos externos o puede crear una plantilla (plantilla: libro que se crea y utiliza como base para otros libros similares. Se pueden crear plantillas para libros y hojas de cálculo. La plantilla predeterminada para libros se denomina Libro.xlt. La plantilla predeterminada para hojas de cálculo se denomina Hoja.xlt.). La plantilla permite guardar el resumen o el informe sin guardar los datos externos para que el archivo sea más pequeño. Los datos externos se recuperan cuando el usuario abre la plantilla del informe.
Volver al principio
Trabajar con los datos en Excel
Cuando se crea una consulta en el Asistente para consultas o en Microsoft Query, se pueden devolver los datos a una hoja de cálculo de Excel. Los datos se convierten entonces en un rango de datos externos (rango de datos externos: rango de datos que se incorpora a una hoja de cálculo pero que se origina fuera de Excel, como en una base de datos o un archivo de texto. En Excel, puede dar formato a los datos o utilizarlos en cálculos como haría con otros datos.) o en un informe de tabla dinámica (informe de tabla dinámica: informe de Excel interactivo de tablas cruzadas que resume y analiza datos, como registros de una base de datos, de varios orígenes incluidos los externos a Excel.) al que se puede aplicar formato y actualizar.
Aplicar formato a los datos recuperados En Excel, puede utilizar herramientas, como gráficos o subtotales automáticos, para presentar y resumir los datos recuperados por Microsoft Query. Puede aplicar formato a los datos y dicho formato se conservará cuando actualice los datos externos. Puede utilizar rótulos de columna propios en lugar de los nombres de campo, y agregar números de fila automáticamente.
Excel puede aplicar formato automáticamente a los nuevos datos que escriba al final de un rango para igualar esos datos con los de las filas precedentes. También puede copiar fórmulas que se repitan en las filas precedentes y extender esas fórmulas a las filas adicionales.
Nota Para que el formato y las fórmulas se extiendan a las nuevas filas del rango, éstos deben aparecer en al menos tres de las cinco filas anteriores.
Puede activar (o volver a desactivar) esta opción en cualquier momento:
- Haga clic en el botón de Microsoft Office
, haga clic en Opciones de Excel y, a continuación, haga clic en la categoría Avanzadas.
- En la sección Edición, active la casilla de verificación Extender formatos de rangos de datos y fórmulas para desactivar esta opción. Para volver a desactivar el formato automático de rangos de datos, active esta casilla de verificación.
Actualizar datos externos Cuando se actualizan los datos externos, se ejecuta la consulta para recuperar todos los datos nuevos o modificados que coincidan con las especificaciones. Una consulta se puede actualizar tanto en Microsoft Query como en Excel. Excel proporciona diversas opciones para actualizar consultas, entre las que se incluye la actualización de datos cada vez que se abra el libro y la actualización automática del mismo a intervalos de tiempo determinados. Puede continuar trabajando en Excel mientras los datos se están actualizando, y también puede comprobar el estado mientras se actualizan los datos. Para obtener más información, vea Actualizar datos conectados (importados).
Volver al principio
