Microsoft Query en Excel

Descargar como pdf o txt
Descargar como pdf o txt
Está en la página 1de 15

Curso de Excel – Nivel Avanzado

Utilizar Microsoft Query


Puede utilizar Microsoft Query para recuperar datos de orígenes externos. Si
usa Microsoft Query para recuperar datos de las bases de datos y 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 desde la base de datos de origen inicial siempre que la base
de datos se actualice con información nueva.
Con Microsoft Query, puede conectarse a orígenes de datos externos,
seleccionar datos de esos orígenes externos, importar los datos a la hoja de
cálculo y actualizar los datos según sea necesario para mantener sincronizados
los datos de la hoja de cálculo con los datos de los orígenes externos.
Tipos de bases de datos a los que se puede tener acceso Puede recuperar
datos de varios tipos de bases de datos, entre los que se incluyen 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 ofrece drivers que puede usar para recuperar datos de los
siguientes orígenes de datos:
• Microsoft SQL Server Analysis Services (proveedor OLAP)
• Microsoft Office Access
• dBASE
• Microsoft FoxPro
• Microsoft Office Excel
• Oracle
• Paradox
• Bases de datos de archivos de texto
También puede usar controladores ODBC o controladores de origen de datos
de otros fabricantes para recuperar información de orígenes de datos que no se
enumeran aquí, entre los que se incluyen 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, consulte la documentación
de la base de datos o póngase en contacto con el proveedor de la misma.
Selección de datos de una base de datos Para recuperar datos de una base
de datos, puede crear una consulta, que es una pregunta que se hace sobre los
datos almacenados en una base de datos externa. Por ejemplo, si los datos se
almacenan en una base de datos de Access, es posible que desee conocer las
cifras de ventas de un producto específico por región. Puede recuperar una parte
de los datos seleccionando solo los datos del producto y la región que desea
analizar.
Con Microsoft Query, puede seleccionar las columnas de datos que desee e
importar solo esos datos a Excel.

Prof. Ing. Miguel Angel Chalco Flores 1


Curso de Excel – Nivel Avanzado

Actualizar la hoja de cálculo en una sola operación Una vez que tenga
datos externos en un libro de Excel, siempre que cambie la base de datos podrá
actualizar los datos para actualizar el análisis, sin tener que volver a crear los
informes y los gráficos de resumen. Por ejemplo, puede crear un resumen
mensual de ventas y actualizarlo todos los meses cuando se incorporan las
nuevas cifras de ventas.
Cómo usa Microsoft Query los orígenes de datos Después de configurar
un origen de datos para una base de datos determinada, puede usarlo siempre
que desee crear una consulta para seleccionar y recuperar datos de esa base
de datos, sin tener que volver a escribir toda la información de conexión.
Microsoft Query usa el origen de datos para conectarse a 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 el libro de Excel con
la información de la consulta y del origen de datos para que pueda volver a
conectarse a la base de datos cuando desee actualizar los datos.

Usar 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 de forma más detallada en las secciones siguientes.

Conectarse a un origen de datos


¿Qué es un origen de datos? Un origen de datos es un conjunto de
información almacenado que permite a Excel y Microsoft Query conectarse a
una base de datos externa. Al utilizar Microsoft Query para configurar un origen

Prof. Ing. Miguel Angel Chalco Flores 2


Curso de Excel – Nivel Avanzado

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 o el servidor, el tipo de base de datos
y la información de inicio de sesión y la contraseña. La información también
incluye el nombre de un controlador OBDC o un controlador de origen de datos,
que es un programa que realiza conexiones a un tipo específico de base de
datos.
Para configurar un origen de datos con Microsoft Query:
1. En la pestaña datos, en el grupo obtener datos externos, haga clic en
desde otros orígenes y, a continuación, haga clic en desde Microsoft
Query.
2. Siga uno de los siguientes procedimientos:
 Para especificar un origen de datos para una base de datos, un archivo
de texto o un libro de Excel, haga clic en la pestaña bases de datos.
 Para especificar un origen de datos de cubo OLAP, haga clic en la
pestaña cubos OLAP . Esta pestaña solo está disponible si ejecutó
Microsoft Query desde Excel.
3. Haga doble clic en <nueva>de origen de datos .
O bien:
Haga clic en <nuevo origen de datos>y, a continuación, haga clic en Aceptar.
Se muestra el cuadro de diálogo crear nuevo origen de datos .
4. En el paso 1, escriba un nombre para identificar el origen de datos.
5. En el paso 2, haga clic en un controlador para el tipo de base de datos
que está usando como origen de datos.
6. 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 que proporcione
dependerá del tipo de origen de datos que haya seleccionado. Es posible
que se le pida que proporcione un nombre de inicio de sesión, una
contraseña, la versión de la base de datos que está usando, la ubicación
de la base de datos u otra información específica para el tipo de base de
datos.
7. Después de escribir la información necesaria, haga clic en Aceptar o
Finalizar para volver al cuadro de diálogo crear nuevo origen de datos
.
8. Si la base de datos tiene tablas y desea que una tabla determinada se
muestre automáticamente en el Asistente para consultas, haga clic en el
cuadro del paso 4 y, a continuación, haga clic en la tabla que desee.
9. Si no desea escribir el nombre y la contraseña de inicio de sesión al usar
el origen de datos, active la casilla guardar mi ID. de usuario y
contraseña en la definición de origen de datos . La contraseña
guardada no está cifrada. Si la casilla no está disponible, consulte al

Prof. Ing. Miguel Angel Chalco Flores 3


Curso de Excel – Nivel Avanzado

administrador de la base de datos para determinar si esta opción puede


estar disponible.

Usar el Asistente para consultas para definir una consulta


Usar el Asistente para consultas para la mayoría de las consultas
El Asistente para consultas facilita la selección y la agrupación de datos de
diferentes tablas y campos en la base de datos. Mediante el Asistente para
consultas, puede seleccionar las tablas y los campos que desea incluir. Una
combinación interna (una operación de consulta que especifica que las filas de
dos tablas se combinan basándose en 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 una segunda tabla.
También puede usar el Asistente para ordenar el conjunto de resultados y
realizar un filtrado simple. En el último paso del asistente, puede elegir devolver
los datos a Excel, o bien refinar la consulta en Microsoft Query. Después de
crear la consulta, puede ejecutarla en Excel o en Microsoft Query.
Para iniciar el Asistente para consultas, siga estos pasos.
1. En la pestaña datos, en el grupo obtener datos externos, haga clic en
desde otros orígenes y, a continuación, haga clic en desde Microsoft
Query.
2. En el cuadro de diálogo elegir origen de datos , asegúrese de que la
casilla usar el Asistente para consultas para crear o modificar
consultas está activada.
3. Haga doble clic en el origen de datos que desea usar.
O bien:
Haga clic en el origen de datos que desea usar y, a continuación, haga clic en
Aceptar.
Trabajar directamente en Microsoft Query para otros tipos de consultas
Si desea crear una consulta más compleja que la que permite el Asistente para
consultas, puede trabajar directamente en Microsoft Query. Puede usar
Microsoft Query para ver y cambiar las consultas que empiece a crear en el
Asistente para consultas, o bien puede crear consultas nuevas sin usar 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, es posible que desee elegir algunos de los datos de un campo y omitir
los datos que no necesite. Por ejemplo, si necesita datos para dos de los
productos en un campo que contiene información para muchos productos,
puede usar criterio para seleccionar datos solo para los dos productos que
desee.  Recuperar datos basándose en criterios diferentes cada vez que
se ejecuta 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 por separado para cada región, puede crear una consulta de parámetros.
Cuando se ejecuta una consulta de parámetros, se le pide un valor para usarlo

Prof. Ing. Miguel Angel Chalco Flores 4


Curso de Excel – Nivel Avanzado

como criterio cuando la consulta selecciona registros. Por ejemplo, una consulta
de parámetros puede pedirle que especifique una región determinada y puede
volver a usarla para crear cada uno de sus informes de ventas regionales.
 Combinar datos de diferentes maneras Las combinaciones internas
que crea el Asistente para consultas son el tipo más común de combinación que
se usa en la creación de consultas. Sin embargo, a veces desea usar un tipo
diferente de combinación. Por ejemplo, si tiene una tabla de información de
ventas de productos y una tabla de información de clientes, una combinación
interna (el tipo creado por el Asistente para consultas) evitará la recuperación
de registros de clientes para los clientes que no han realizado una compra. Con
Microsoft Query, puede unir estas tablas de modo que se recuperen todos los
registros de los clientes, junto con los datos de ventas de los clientes que han
realizado compras.
Para iniciar Microsoft Query, siga estos pasos.
1. En la pestaña datos, en el grupo obtener datos externos, haga clic en
desde otros orígenes y, a continuación, haga clic en desde Microsoft
Query.
2. En el cuadro de diálogo elegir origen de datos , asegúrese de que la
casilla usar el Asistente para consultas para crear o modificar
consultas está desactivada.
3. Haga doble clic en el origen de datos que desea usar.
O bien:
Haga clic en el origen de datos que desea usar y, a continuación, haga clic en
Aceptar.
Reutilizar y compartir consultas Tanto en el Asistente para consultas como
en Microsoft Query, puede guardar las consultas como un archivo. dqy que
puede modificar, volver a usar y compartir. Excel puede abrir archivos. dqy
directamente, lo que permite a usted u otros usuarios crear rangos de datos
externos adicionales desde la misma consulta.
Para abrir una consulta guardada desde Excel:
1. En la pestaña datos , en el grupo obtener datos externos , haga clic en
desde otros orígenes y, a continuación, haga clic en desde Microsoft Query.
Se muestra el cuadro de diálogo elegir origen de datos .
2. En el cuadro de diálogo elegir origen de datos , haga clic en la pestaña
consultas .
3. 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, a continuación, haga clic en abrir.
Si hace doble clic en un archivo. dqy, se abrirá Excel, se ejecutará la consulta y,
a continuación, se insertarán los resultados en una hoja de cálculo nueva.

Prof. Ing. Miguel Angel Chalco Flores 5


Curso de Excel – Nivel Avanzado

Si desea compartir un resumen o un informe basado en datos externos, puede


dar a otros usuarios un libro que contenga un rango de datos externos o puede
crear una plantilla. Una plantilla le permite guardar el resumen o informe sin
guardar los datos externos para que el archivo sea más pequeño. Los datos
externos se recuperan cuando un usuario abre la plantilla de informe.

Prof. Ing. Miguel Angel Chalco Flores 6


Curso de Excel – Nivel Avanzado

Ejemplo
Para comprender la utilidad de esta herramienta realizaremos el siguiente
ejemplo:
Usaremos el archivo: Ventas Empresa ABC – 2021.accdb
Que tiene las siguientes tablas relacionadas.

De los datos de la Base de datos vamos a obtener cierta información para ello:
Paso 1:
En la hoja de Excel

Prof. Ing. Miguel Angel Chalco Flores 7


Curso de Excel – Nivel Avanzado

Paso 2
Se muestra la siguiente pantalla

Se elige Ms Access Database*


Y luego Aceptar
Paso 3
Buscamos en el Escritorio (desktop) la carpeta que contiene le archivo y le
damos clic sobre el archivo

Prof. Ing. Miguel Angel Chalco Flores 8


Curso de Excel – Nivel Avanzado

Luego Aceptar
Paso 4
Tenemos que elegir los campos a usar de cada tabla
Tb_Cliente: Cod_cli,
Tb_Detalle_Factura: Can_ven, Val_ven
Tb_Producto: Des_pro, Pre_pro
Tb_Cliente: Ruc_Cliente,
Tb_Distrito: Nom_Distrito

Luego siguiente

Prof. Ing. Miguel Angel Chalco Flores 9


Curso de Excel – Nivel Avanzado

Siguiente

Siguiente

Prof. Ing. Miguel Angel Chalco Flores 10


Curso de Excel – Nivel Avanzado

Elegimos Ver datos o modificar consulta en Microsoft Query Luego


Finalizar

Para ver las sintaxis SQL damos clic en el botón SQL y se muestra

Prof. Ing. Miguel Angel Chalco Flores 11


Curso de Excel – Nivel Avanzado

Damos clic en Aceptar


Para llevar la data a la hoja de Excel Damos Clic en el botón
Se muestra el siguiente cuadro y se elije la celda a llenar los datos

Damos clic en Aceptar y se muestra la información requerida.

Ahora realizaremos lo mismo pero haremos una consulta


Las cantidades vendidas mayores a 40
Se repiten los pasos hasta llegar al siguiente cuadro

Prof. Ing. Miguel Angel Chalco Flores 12


Curso de Excel – Nivel Avanzado

Siguiente

Finalizar
Clic en

Se muestra el cuadro así

Prof. Ing. Miguel Angel Chalco Flores 13


Curso de Excel – Nivel Avanzado

Y elegimos el campo de criterio y el valor, luego ejecutar

Se ejecuta la consulta y lo llevamos a Excel

Prof. Ing. Miguel Angel Chalco Flores 14


Curso de Excel – Nivel Avanzado

Y la información requerida es:

Prof. Ing. Miguel Angel Chalco Flores 15

También podría gustarte