Pasar parámetros a una consulta en MS Query desde celdas de Excel

Esta nota viene a colación de la consulta de un lector que me preguntaba si era posible pasar parámetros a una consulta en MS Query desde una celda de Excel.

La respuesta es afirmativa y en esta entrada mostraré cómo hacerlo.

Empecemos por recordar que una de las herramientas para extraer datos de fuentes externas en Excel es el MS Query

Primer paso: crear la consulta en el MS Query

Antes de empezar el proceso hemos definido que las celdas B1:B3 contendrán los parámetros.

Para nuestro ejemplo usaremos la base de datos Northwind (el archivo no viene con el paquete de Office 2010).

Elegimos la tabla de la base de datos (en nuestro caso: Invoices) y los campos que queremos importar a la hoja de Excel

Apretamos “next” hasta que llegamos a la última etapa (Finalizar) y allí elegimos la opción “ver datos en MS Query”

Segundo paso: agregar parámetros a la consulta

Primero debemos hacer visible el área de criterios

Para ingresar criterios en forma de parámetros debemos usamos los paréntesis “[“ y “]” de esta manera

En la línea de Criterios ingresamos el campo, en nuestro ejemplo usamos Country (país) y Shipped Date (fecha de despacho). En la línea de Valor ponemos

[ingrese Pais] para hacer la consulta según país

Between ]fecha de inicio] and [fecha de cierre] para definir el rango de fechas.

Tercer paso: transferir los resultados a Excel

Al hacerlo se abre un diálogo para ingresar los valores de los parámetros; a esta altura del proceso no es necesario ingresar ningún valor, sencillamente apretamos Aceptar

En la hoja de Excel elegimos la ubicación de la tabla que será importada

Cuarto paso: definir las celdas de los parámetros en Excel

Antes de finalizar el proceso apretamos el botón Propiedades

Luego activamos la pestaña Definición para acceder al botón Parámetros

En el formulario que se abre definimos la opción “Tomar el valor de la siguiente celda” y también marcamos la opción “Actualizar automáticamente…”

Volvemos a este paso para cada uno de los parámetros y apretamos Aceptar para los siguientes tres pasos
Excel importa la tabla filtrada por los criterios que hemos definido en B1:B3

Como hemos elegido la opción de actualizar automáticamente al cambiar los valores en la celdas del rango B1:B3, tenemos una consulta que actúa dinámicamente

Consolidar datos de varios cuadernos Excel con MS Query

Ya hemos tratado anteriormente el tema de consolidación de datos de varios cuadernos u hojas Excel usando MS Query. Sin embargo el uso de esta herramienta presenta ciertos problemas y la ayuda en línea del MS Query es muy pobre.

En esta nota veremos cómo lograr esta consolidación para lo cual tendremos que editar y modificar la consulta SQL que genera el MS Query. Aclaremos que esta tarea es sencilla y no requiere conocimientos del lenguaje SQL.

En nuestro ejemplo suponemos que tenemos los datos de ventas de tres sucursales de una empresa en tres cuadernos distintos: ventasNorte.xls, ventasSur.xls y ventasOeste.xls. Las tablas de datos de cada cuaderno tienen la misma estructura, es decir, los mismos campos (columnas) en las mismas posiciones.

Empezamos por incluir cada tabla en un nombre. Por ejemplo, en ventasNorte.xls incluimos la tabla de datos en el nombre consNorte

También podemos crear el nombre usando el cuadro de nombres

El próximo paso es abrir un cuaderno en blanco donde consolidaremos los datos. En el nuevo cuaderno activamos la pestaña Datos y en Obtener Datos Externos usamos la opción Desde Microsoft Query

En Excel 2003 usamos

En Elegir origen de datos seleccionamos Excel Files

En el paso Select Workbook (seleccione cuaderno), seleccionamos uno de los cuaderno, por ejemplo, ventasNorte.xls

En el próximo paso seleccionamos el nombre del rango y pasamos todas las columnas a la consulta

Seguimos adelante en el proceso hasta llegar al último paso donde seleccionamos la opción Ver datos o modificar en Ms Query

Lo que veremos en la interfaz del MS Query que se abre es esto

Apretamos el botón SQL, lo que nos permite acceder a la sintaxis de la consulta.

En lugar de

SELECT rngNorte.Sucursal, rngNorte.Mes, rngNorte.Ventas, rngNorte.Clientes
FROM `D:\Ventas\ventasNorte`.rngNorte rngNorte

ponemos

SELECT * FROM `D:\Ventas\ventasNorte`.rngNorte
UNION ALL
SELECT * FROM `D:\Ventas\ventasSur`.rngSur
UNION ALL
SELECT * FROM `D:\Ventas\ventasOeste`.rngOeste

Al apretar Aceptar veremos

Apretamos Aceptar y todos los datos serán consolidados en la tabla del MS Query

En el menú del Query elegimos Archivo-Devolver los datos a Excel lo que abre el diálogo de Importar datos en Excel, donde podemos elegir la forma de ver los datos en Excel. Por lo general usaremos la opción Informe de tabla dinámica

En Excel 2003 la interfaz es algo distinta

Consolidar datos de más de dos cuadernos Excel con MS Query

En el pasado hemos mostrado cómo consolidar datos de distintas hojas de un mismo cuaderno empleando el MS Query.

Un lector me comenta que cuando quiere consolidar datos de tres hojas de tres distintos cuadernos, MS Query no permite agregar la tercera tabla (la hoja del tercer cuaderno). Como sucede frecuentemente con esta aplicación, la ayuda no viene a nuestro rescate en este problema.

Veamos un ejemplo (en este caso estaremos usando Excel 2007, pero el mecanismo es el mismo en Excel 97-2003).

Empezamos por establecer el nexo a la fuente de datos, en este caso dos cuadernos Excel

Seleccionamos uno de los cuadernos (MS Query no nos permite hacer selecciones múltiples)



Incluimos todos los campos de la hoja y seguimos adelante con el asistente hasta el último paso, donde seleccionamos la opción “Ver datos o modificar consulta con MS Query”. Al finalizar el proceso se abre la ventana de MS Query con los datos que hemos extraído de la hoja del primer cuaderno.



Ahora agregamos la hoja del segundo cuaderno con el menú Tabla-Agregar Tablas



Aquí nos topamos con el primer problema: no podemos ver el nombre del archivo a elegir dado que no podemos extender el ancho de la ventanilla. Guiándonos por la ubicación relativa de los archivos en la carpeta, elegimos el último



Ahora queremos agregar la tercer tabla, para lo cual volvemos sobre el proceso anterior



MS Query no nos permite agregar la tabla bajo la advertencia “Imposible el acceso..”.
Lo que hacemos en este caso es crear primero una unión entre las dos tablas presentes antes de intentar agregar la tercera. En nuestro caso la unión será entre los campos “factura” de ambas tablas.

Ahora intentamos agregar la tercer tabla, “Clientes”.




Del momento que existe por lo menos una unión entre las dos primeras tablas podemos agregar la tercera.

Si intentamos agregar una cuarta tabla, cuyo origen es una hoja de un cuarto cuaderno, nuevamente tendremos que crear una unión a la tercer tabla antes de poder agregar la cuarta.

Otro rodeo posible es mover las hojas de los distintos cuadernos a un único cuaderno y luego proceder como mostramos en la nota mencionada.

Technorati Tags: MS Excel

Consolidación de datos de distintos cuadernos.

En el pasado hemos mostrado cómo consolidar datos de distintas hojas de un cuaderno usando Datos-Consolidar, Tablas Dinámicas, y MS Query.
Naturalmente se presentan situaciones en las cuales queremos consolidar datos que se encuentran en hojas de distintos cuadernos.
En esta nota veremos algunas de las técnicas posibles. Supongamos estos dos cuadernos con datos de ventas del primer trimestre de una cadena de tiendas, un cuaderno para el año 2007 (2007.xls) y el otro para el 2008 (2008.xls).

Técnica #1 - transferir los datos a un único cuaderno.
La ventaja de esta técnica es que nos permite usar con facilidad Datos-Consolidar o Tablas Dinámicas. Podemos, por ejemplo, crear un nuevo cuaderno y transferir a él los datos en hojas separadas o en una única hoja. Por ejemplo, creamos el cuaderno "Consolidado" y pasamos a él las hojas de los cuadernos 2007 y 2008




Cambiamos el nombre de las hojas transferidas a "2007" y "2008"



Todo lo que nos queda por hacer es crear una hoja consolidada con Datos-Consolidar



Alternativamente podemos usar tablas dinámicas con rangos de consolidación múltiples



Técnica #2 - Datos en cuadernos separados
Usando Datos-Consolidar.
Como en el caso anterior, empezamos creando un cuaderno donde consolidaremos los datos (Consolidado.xls).
Una vez creado el cuaderno abrimos el menú datos consolidar y creamos la referencia al rango relevante en el cuaderno 2007.xls



Apretamos "agregar". Ahora veremos que Excel no nos permite crear una referencia al rango relevante en el cuaderno 2008.xls. Lo que haremos será crear la referencia manualmente



Sencillamente tecleamos la referencia manualmente, cambiando 2007 por 2008 y extendiendo el rango a D8.

Pulsamos "Aceptar" y Excel consolidará los datos.



Usando Tablas dinámicas.
También aquí usamos rangos de consolidación múltiples, creando referencias a las hojas relevantes de los distintos cuadernos. A diferencia de Datos-Consolidar, creamos las referencias directamente apuntando a los rangos en las hojas de los distintos cuadernos



El resultado es

Technorati Tags: MS Excel

Consolidar datos de hojas Excel con MS Query

Supongamos que tenemos una hoja en un cuaderno de Excel con los siguientes datos



En otra hoja tenemos estos datos



Nuestra tarea consiste en consolidar los datos de ambas hojas en una sola hoja, como esta



No podemos hacerlo con Datos—Consolidar o con Tablas Dinámicas. Una posibilidad es agregar el campo Cliente en la segunda hoja usando la función BUSCARV basándonos en el número de factura.
Este método se vuelve muy ineficiente cuando tenemos que manejar una gran cantidad de datos. No sólo que BUSCARV puede ser muy lenta cuando hacemos una búsqueda exacta sino que también tendríamos que estar actualizando las fórmulas cada vez que agregamos datos.

En esta nota mostraremos un método mucho más eficiente usando un programa adjunto de Excel, y poco conocido, el MS Query, sobre el cual ya hemos escrito en el pasado.

Supongamos que las hojas que hemos mostrada más arriba se encuentran en el cuaderno Datos.xls.

Empezamos por abrir un cuaderno en blanco y en este cuaderno abrimos el menú Datos-Obtener Datos externos-Nueva consulta de base de datos



En el diálogo que se abre elegimos la opción Excel Files



Ubicamos la carpeta donde guardamos el archivo (en nuestro caso Datos.xls) y elegimos el archivo



En el diálogo Elegir Columna veremos las dos hojas que contiene nuestro cuaderno



Para ver las tablas, que de hecho son las hojas del cuaderno, debemos asegurarnos que la opción Tablas del sistema esté seleccionada. Para hacer esto apretamos el botón Opciones



Nuestro objetivo es agregar los campos Fecha y Cliente a la hoja Ventas. Apretamos el signo + de Facturas para ver los campos, y elegimos Fecha y Cliente



Ahora señalamos la tabla Ventas y pasamos todos los campos apretando la flecha correspondiente



Al hacer esto veremos este mensaje



MsQuery nos pide que creemos un vínculo entre ambas tablas (unión). Haremos esto más adelante. Por ahora apretamos Aceptar y seguimos



Al apretar Aceptar se abre la interfaz del MS Query. En la parte superior vemos las tablas de datos que hemos importado al MS Query. En la zona de datos, vemos todos los datos de ambas tablas. Como podrán comprobar, en esta tabla hay 90 registros. Esto se debe a que no hemos creado ningún vínculo específico entre las dos tablas y por lo tanto se crean registros redundantes.
Existen distintos tipo de vínculos entre tablas. Quienes tengan conocimientos de SQL, podrán resolver el caso fácilmente. Para quien no conozca SQL, MS Query ofrece un asistente, que es lo que emplearemos en este ejemplo.
En el menú de MS Query elegimos Tablas-Uniones



En la ventana de diálogo del asistente nos aseguramos que en Izquierda y Derecha figure el campo Factura, en una ventanilla el campo de la tabla Facturas y en la otra él de Ventas. Elegimos la opción 3 de las opciones de unión (Left Outer Join, en SQL)



Apretamos Agregar y luego Cerrar. A continuación apretamos el botón de Ejecutar consulta ahora



El resultado es la tabla que estamos buscando. Nótese la forma gráfica en la que MS Query indica el tipo de unión que hemos creado entre las tablas



Todo lo que nos queda por hacer es transferir los datos al cuaderno de Excel que hemos abierto para el caso. Esto lo hacemos con el menú Archivo-Devolver datos a Excel



Excel activa la hoja de la cual hemos partido y abre un diálogo donde podemos elegir desde que celda importar los datos. Existen varias opciones que son visibles al apretar los botones Propiedades y Modificar consulta, y que invito a que investiguen por vuestra cuenta.



Al apretar Aceptar Excel importa los datos a la hoja.



Como ven, Excel también pone la barra de comandos Datos Externos. Esta barra nos permite renovar los datos, con el icono "!" y también editar la consulta. Una de las grandes ventajas de este método es que Excel guarda un vínculo con las tablas de datos lo que nos permite actualizar con comodidad y eficiencia la hoja de Excel cada vez que cambiemos o agreguemos datos en las tablas de origen.

Quien quiera practicar el ejercicio, puede descargar el archivo datos.xls aquí.

Technorati Tags: MS Excel