capítulo5 - query
TRANSCRIPT
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 1/21
ELEMENTOS DE CONTEXTUALIZACIÓN
Introducción al tema
En el presente capítulo aprenderemos a utilizar la herramienta de Excel MS QUERY. Esta
herramienta es supremamente útil porque nos permite tener acceso a bases de datos deotras fuentes, como el caso de una base de datos en Access, en SQL server, entre otros
programas que manejan grandes bases de datos.
Podremos tener acceso a tablas de datos completas o tablas que generemos con la
utilización de campos de varias fuentes.
A partir de tener acceso a los datos en Excel podremos utilizar herramientas de datos para
el procesamiento, tales como tablas dinámicas.
Objetivo
Aprender el funcionamiento de la herramienta de MS QUERY para obtener datos
externos.
Duración en horas
El desarrollo de esta unidad tomará aproximadamente cuatro horas.
TABLA DE CONTENIDO
1. ¿Qué es Microsoft QUERY?.......................... .......................... .......................... ....................... 1
2. ¿Cómo obtener datos de una fuente externa a través de MS QUERY? .......................... .......... 3
3. ¿Cómo obtener datos de campos de diferentes tablas a través de MS QUERY? .................... 11
DESARROLLO TEMÁTICO
1. ¿Qué es Microsoft QUERY?
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 2/21
Es un programa que facilita obtener datos de fuentes externas de otros programas.
Dentro de los programas a los que puede tener acceso tenemos Access, SQL server,
entre otros.
Cuando hablemos de QUERY, pensemos siempre en una consulta que podemos hacer
a una base de datos, esa consulta nos permitirá obtener datos de acuerdo a la
necesidad de información que tengamos en el momento. Los datos que genere la
consulta nos servirán de insumo para entregar el resultado final a través de una
herramienta de datos de Excel, como podría ser una tabla dinámica.
Dentro de la ficha Datos de la cinta de opciones de Excel, encontramos un grupo de
opciones llamado "Obtener datos externos", es allí donde podremos encontrar la
opción para utilizar MS QUERY.
Vamos a la opción "De otras fuentes" y desplegamos la lista que posee y aparecerá la
siguiente pantalla...
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 3/21
A través de esta opción podremos utilizar todo el potencial que tiene esta herramienta
de Excel.
2. ¿Cómo obtener datos de una fuente externa a través de MS QUERY?
Para que conozcamos el procedimiento de cómo obtener datos por medio de MS
QUERY, utilizaremos una fuente de datos de Access, la base de datos con el nombre de
"BD_VENTAS", será nuestra fuente de datos externa. Esta base de datos contiene tres
tablas:
La tabla clientes contiene datos de 60 clientes. En la tabla pedidos existen 2.927
registros de las ventas que se han realizado, y en la tabla productos está toda la
información de 32 productos.
Anexo base de datos tomada como referencia.
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 4/21
Para inicial el procedimiento de obtener datos por medio de MS QUERY, debemos
abrir un libro nuevo de Excel, hacemos clic en la ficha Datos, luego clic en la opción De
otras fuentes del grupo de opciones Obtener datos externos y por último clic en Desde
Microsoft QUERY...
Inmediatamente aparece el siguiente cuadro...
Debemos elegir el origen de los datos, recordemos que para nuestro ejemplo la base
está almacenada en Access, por lo tanto la tercera opción es la adecuada... MS Access
Database*. Hacemos clic en esta opción y presionamos el botón de Aceptar...
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 5/21
Debemos continuar con la selección de la base de datos, en las unidades, desplegamos
la lista y hacemos clic en la unidad donde se encuentra la base de datos, y en la parte
superior observamos las carpetas para que ubiquemos el lugar donde está la base de
datos...
Una vez seleccionado el nombre de la base de datos, procedemos a hacer clic en el
botón de Aceptar...
Entonces deberá aparecer un cuadro con el asistente para consultas, y debemos
seleccionar las columnas o los campos que requerimos para la consulta. En el ladoizquierdo vamos a encontrar todas las tablas disponibles de la base de datos (para
nuestro ejemplo son tres tablas), cada una tiene un signo + al lado izquierdo del
nombre de la tabla, que se utiliza para mostrar todos los campos que tiene la tabla...
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 6/21
Como podemos observar, podríamos utilizar los campos necesarios no importa de la tabla que
sea.
Para nuestro primer ejemplo... necesitamos conocer las cantidades vendidas por año y mes,
esto significa que debemos revisar que campos tiene la tabla de pedidos... hacemos clic en el
signo + y mostrará los nombres de los campos disponibles:
Podemos utilizar los campos Fecha_Venta y Cantidad, con estos dos es suficiente para
responder a nuestra necesidad de información...
Seleccionamos el campo Fecha_Venta y presionamos el botón que tiene el símbolo de mayor
(>), este traslada el campo del lado izquierdo al derecho indicando que ese campo está
incluido en la consulta, hacemos lo mismo con el campo Cantidad...
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 7/21
Debe quedar así:
Luego presionamos el botón siguiente para continuar con el Asistente para consultas...
El paso siguiente del Asistente para consultas hace referencia a que podemos filtrar datos de
los campos seleccionados.
En caso de que se requieran todos los registros de los campos seleccionados, no realizamosningún filtro, esto significa que presionamos el botón siguiente y continuamos con el
asistente...
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 8/21
En el siguiente paso del Asistente para consultas nos da la posibilidad de ordenar los datos...
para ello nos muestra la siguiente pantalla...
En nuestro caso no necesitamos ordenar, por lo tanto simplemente hacemos clic en el botón
de Siguiente...
Por último el Asistente para consultas nos presenta su paso final; en este debemos seleccionar
como queremos ver el resultado de la consulta... por defecto trae seleccionada la opciónDevolver datos a Microsoft Office Excel, esto significa que el resultado de los campos
seleccionado quedará en una hoja de Excel...
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 9/21
Procedemos a hacer clic en el botón Finalizar, y deberá aparecer un cuadro como el siguiente:
En este cuadro nosotros debemos definir como deseamos ver los datos en el libro, las
opciones son:
Tabla (viene seleccionada por defecto)
Informe de tabla dinámicaInforme de gráfico y tabla dinámicos
Para nuestro caso como necesitamos hacer una tabla dinámica que muestre la cantidad
vendida por año y mes, seleccionamos la segunda opción y damos clic en el botón de
Aceptar...
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 10/21
Entonces en la hoja activa del libro donde estábamos ubicados aparece la lista de campos
para realizar la tabla dinámica...
Podemos ver que los dos únicos campos que tenemos son los dos que seleccionamos en el
Asistente para consultas... el resultado final, después de hacer una tabla dinámica por
agrupaciones (mes y año) es el siguiente:
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 11/21
RECUERDE QUE:
Para elaborar la tabla dinámica por agrupaciones de mes y año es necesario que
ubiquemos en Etiqueta de fila el campo Fecha_Venta y luego nos ubicamos en
cualquier fecha y nos vamos por la ficha Opciones de la tabla dinámica y luego
hacemos clic en Agrupar campos, allí seleccionamos mes y año. De esta manera
generamos los meses y los años. Luego completamos la tabla ubicando el campoCantidad en el área de valores. Para que los años queden en las columnas,
simplemente pasamos el campo Año a Etiquetas de columna y listo.
Hasta el momento hemos aprendido el procedimiento para realizar un informe de
tabla dinámica con datos externos y utilizando campos de una sola tabla de la fuente
de datos. Ahora conoceremos como se hace cuando se requiere más campos que
están en diferentes tablas.
3.
¿Cómo obtener datos de campos de diferentes tablas a través de MS QUERY?
No todas las veces que necesitamos obtener datos de bases de datos de fuentes
externas los campos que necesitamos se encuentran en una sola tabla. Lo normal es
que debamos utilizar campos de varias tablas, lo que implica que nosotros debemos
conocer como se relacionan esas tablas, para que podamos obtener los datos
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 12/21
correctamente... vamos a ilustrar esta situación con la siguiente situación: nos han
solicitado que presentemos un informe a través de un gráfico y tabla dinámicos que
muestre el resultado de las cantidades vendidas por cada categoría.
Si recordamos en la tabla Tabla_Pedidos no aparece ningún campo de categoría. El
nombre de la categoría está en la tabla Tabla_Productos, significa entonces que
nosotros debemos relacionar estas dos tablas para que podamos obtener los datos
correctos... veamos cómo se hace...
Nos ubicamos en otra hoja disponible del libro en el que estamos trabajando, nos
vamos por la Ficha Datos y hacemos clic en la opción De otras fuentes y luego clic en la
opción Desde Microsoft Query...
Seleccionamos el origen de datos MS Access Database*, y hacemos clic en Aceptar
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 13/21
Luego ubicamos la carpeta donde se encuentra la base de datos, seleccionamos el
nombre de la base de datos y clic en Aceptar...
De la tabla Tabla_Productos seleccionamos el campo Categoría y lo ubicamos en el
área de Columnas en la consulta:
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 14/21
Luego seleccionamos de la tabla Tabla_Pedidos el campo Cantidad y lo ubicamos en el
área de Columnas en la consulta...
Con los dos campos seleccionados debería quedar así:
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 15/21
Procedemos a hacer clic en el botón Siguiente... Inmediatamente sale un mensaje,
como se muestra a continuación:
No nos debemos asustar... simplemente el Asistente de la consulta nos está
advirtiendo de que ha encontrado campos de diferentes tablas y que él no es capaz de
relacionar las tablas, solicita que nosotros manualmente definamos la relación entre
las dos tablas de donde seleccionamos los campos, es decir, la tabla Tabla_Productos y
la tabla Tabla_Pedidos las debemos relacionar. Veamos como...
Procedemos a hacer clic en el botón Aceptar del mensaje y deberá aparecer una
pantalla tal como la siguiente:
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 16/21
Estamos en el editor de consultas de Microsoft Query. En el recuadro rojo se
encuentran los menús que están disponibles y algunos botones que nos permiten, por
ejemplo, aplicar criterios a los campos seleccionados, entre otros.
En el recuadro verde está el área donde aparecen las tablas de donde seleccionamos
los campos para la consulta, para nuestro caso tenemos las tablas Tabla_Pedidos y
Tabla_Productos.
En el recuadro azul aparecen los campos que previamente seleccionamos para nuestra
consulta.
Recordemos que es necesario relacionar las dos tablas, para ello, vamos a tomar elcampo Referencia_Producto de la tabla Tabla_Pedidos y lo vamos a unir con el campo
Referencia de la tabla Tabla_Pedidos. Es muy sencillo... con clic sostenido en el campo
Referencia_Producto arrastramos el mouse hasta el campo Referencia de la otra tabla
y soltamos el clic... y listo quedan las dos tablas relacionadas por este campo, lo que
nos permitirá obtener los datos correctos de ambas tablas.
Quedaría de la siguiente manera:
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 17/21
¿Qué pasa si hacemos mal la relación?
La respuesta es muy sencilla, no tendríamos datos para nuestra consulta, esto se vería
reflejado en el recuadro azul pues allí aparecía vacío... un ejemplo a continuación:
No aparecen datos porque el campo Referencia_Producto de la tabla Tabla_Pedidos
no es común con el campo Descripción de la tabla Tabla_Pedidos.
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 18/21
Continuando con nuestro ejemplo... una vez realizada la relación entre las dos tablas,
hacemos clic en el menú Archivo...
Luego clic en la opción Devolver datos a Microsoft Excel... Inmediatamente aparece elsiguiente cuadro:
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 19/21
Como nosotros necesitamos hacer un informe de gráfico y tabla dinámicos, debemos
seleccionar la tercera opción y damos clic en el botón de Aceptar:
Después procedemos a elaborar la tabla dinámica... el resultado final sería así:
Podemos apreciar que tenemos el informe del gráfico dinámico y la tabla dinámica
para cada una de las categorías con sus respectivas cantidades vendidas.
Hemos aprendido los procedimientos para elaborar informes por medio de Microsoft
Query, obteniendo datos de fuentes externas y utilizando campos de diferentes tablas.
Para complementar los conceptos de Microsoft Query, revisemos detenidamente el
contenido de los dos vídeos de este capítulo, pues nos enseñarán a realizar filtros y
criterios de ordenación a los campos seleccionados para la consulta, como también
crear campos calculados dentro de la consulta.
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 20/21
Enlace de ampliación: Microsoft Query en Excel
Enlace de ampliación: http://exceltotal.com/microsoft-query-en-excel/
ACTIVIDAD DE APRENDIZAJE
A continuación encontraremos algunas afirmaciones, de acuerdo a lo estudiado en este
capítulo, debemos responder si cada afirmación es verdadera o falsa.
1) Por medio de MS QUERY puedo acceder a bases de datos de otros programas como
Access. (V) ó (F). Respuesta: V
2) Las relaciones entre las tablas de los campos seleccionados dentro de la consulta las
realiza automáticamente Excel. (V) ó (F). Respuesta: F
3) Podemos seleccionar para una consulta por medio de MS QUERY campos de
diferentes tablas que estén en la base de datos que se esté accediendo. (V) ó (F).
Respuesta: V
4) El resultado de la consulta puede ser devuelto a Excel por medio de una tabla
dinámica. (V) ó (F). Respuesta: V
5) La opción de MS QUERY se encuentra dentro de la Ficha Revisar. (V) ó (F). Respuesta: F
BIBLIOGRAFIA
BESKEEN, DAVID. DUFFY, JENNIFER A. FRIEDRICHSEN, LISA. REDING, ELIZABETH E.
MICROSOFT OFFICE XP. MEXICO: THOMSON, 2004.
BERK, KENNETH N. CAREY, PATRICK. ANALISIS DE DATOS CON MICROSOFT EXCEL:
ACTUALIZADO PARA OFFICE 2000. MEXICO: THOMSON, 2001.
DAVILA LADRON DE GUEVARA, FERNANDO. HACIA LA INTELIGENCIA DEL NEGOCIO
CON EXCEL 2003. BOGOTA: POLITECNICO GRANCOLOMBIANO, 2005.
FRYE, CURTIS D. EXCEL 2010: PASO A PASO. MADRID: ANAYA, 2011.
GOMEZ G., JUAN ANTONIO. EXCEL 2010 AVANZADO. MEXICO: ALFAOMEGA, 2011.
ENLACES DE INTERÉS
Obtener datos externos a través de MS QUERY
Esta página describe el procedimiento para obtener datos que se encuentran en una
fuente de datos externa por medio de Microsoft Query.
7/23/2019 Capítulo5 - Query
http://slidepdf.com/reader/full/capitulo5-query 21/21
http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-
HA010099664.aspx
PREGUNTAS FRECUENTES
1) ¿Es posible acceder a un computador que esté conectado a una red para acceder a
una base de datos a través de MS QUERY?
Si es posible siempre y cuando se tengan los permisos necesarios otorgados por el
administrador de la red para acceder al computador y a la base de datos. Con estos
permisos podría acceder a la base de datos sin inconvenientes.
2) ¿Puedo hacer informes de tablas dinámicas sin necesidad de tener físicamente la base
de datos, por medio de MS QUERY?
Definitivamente esta es una de las ventajas de utilizar Microsoft Query, pues nos
permite acceder a datos externos y realizar diferentes informes sin necesidad de tener
la base de datos en el libro de Excel.
3) ¿Qué pasa si los datos de la base de datos se actualizan y tenemos informes realizados
por medio de MS QUERY con esa base de datos?
Simplemente con actualizar los informes que tenemos, estos validarán si los datos han
cambiado y se actualizan todas las tablas y datos.
4) ¿Qué debemos hacer?, si necesitamos varios campos de diferentes tablas en una
consulta por medio de MS QUERY y no conocemos como se relacionan las tablas.
Debemos preguntar a la persona que conozca muy bien la base de datos para que nos
indique con qué campos se relacionan las tablas, pues de ello depende el éxito de la
consulta.
5) ¿Es posible seleccionar parte de los registros de una tabla por medio de MS QUERY?
Por medio de los filtros, se pueden seleccionar los registros que cumplan alguna
condición determinada.