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

Importar datos WEB a Excel – otras alternativas

En una nota anterior mostramos como importar datos de tablas que se encuentran en la Internet a Excel.
Las ventajas y los beneficios de usar este método son obvios, en especial si tomamos en cuenta que cada día más y más información se encuentra en tablas de la Internet.
En la nota mencionada vimos como crear una consulta Internet en Excel con el menú Datos-Obtener datos externos-Nueva consulta WEB.
Si trabajan con Explorer (versión 5.0 en adelante), existen otros métodos de crear estas consultas:

Usar Copiar y Pegar.
Abrimos una página de Internet con la tabla que queremos importar. Por ejemplo, esta tabla de posiciones de la liga española




Seleccionamos la tabla con el Mouse, tal como seleccionaríamos un rango en una hoja de Excel, la copiamos (Ctrl+C) y la pegamos a la hoja de destino.
En el ángulo inferior derecho de la tabla que acabamos de pegar veremos el icono de Opciones de pegado



Abrimos el menú y elegimos la opción Crear consulta WEB actualizable



Excel abre el diálogo de Nueva consulta WEB, donde elegimos la tabla que queremos importar (caso contrario, importará elementos que tal vez no queremos)



También podemos abrir el menú Opciones y elegir el tipo de formato que queremos obtener



Finalmente apretamos Importar

Como ven tendremos que aplicar algunos formatos, ya que el resultado no es del todo estético (la falta de "ñ" se debe a que he cambiado el computador y todavía no he redefinido los idiomas)



Después de aplicar los formatos (los fondos grises los hacemos con la técnica de formato condicional que ya hemos mostrado),



cambiamos algunas definiciones en el cuadro de Propiedades de la barra de Datos Externos para evitar que sean modificados al actualizar la consulta



Editar desde el Explorer
Abrimos la página y en el menú Archivo del Explorer, elegimos la opción Editar con Excel



Esto abrirá el diálogo de Nueva consulta WEB. A partir de aquí todo el proceso es como en el caso anterior.
Si en el Explorer aparece Word como opción de edición, pueden cambiar a Excel usando el menú de Opciones del Explorer y cambiando al opción de editor de HTML a Excel

Technorati Tags: MS Excel

Exportar datos de Excel a archivo texto.

Una tarea corriente es importar archivos de texto a Excel, para lo cual existen varias posibilidades.
Otra tarea similar es generar un archivo texto para exportar datos de una hoja Excel a una aplicación externa. Algunas de estas aplicaciones aceptan archivos texto con formato .csv (comma separated values); otras aplicaciones exigen archivos texto delimitado con tabulaciones.
En ambos casos usamos el menú Guardar como y elegimos alguna de las opciones de texto




Si elegimos la opción .csv Excel genera un archivo de texto cuyos campos están delimitados por comas. Supongamos esta hoja Excel



Si la exportamos usando la opción .csv obtenemos este resultado



Como pueden ver, un archivo texto cuyos campos están separados por ";".

En los casos de exportar a una aplicación que no acepta este formato, usamos la opción texto delimitado con tabulaciones. El resultado es



El problema surge cuando la aplicación a la cual queremos exportar los datos exige que los campos tengan una longitud determinada. En esta nota mostraremos como crear un archivo texto con campos de longitud determinada por el usuario.

A la hoja Excel que contiene los datos a exportar la llamaremos "BD" (base de datos). Agregamos una fila por encima de la fila 1, de manera que los encabezamientos queden en la fila 2. Ahora ponemos en las celdas correspondientes de la fila 1 el "ancho" deseado para cada campo



El próximo paso consiste en agregar una hoja, que llamaremos "ascii". En celda A1 de esta hoja ponemos esta fórmula

=BD!A3&REPETIR(" ";BD!A$1-LARGO(BD!A3))

Esta fórmula crea un valor compuesto por el contenido de la celda A3 (siendo 3 la primer fila de la hoja BD con datos a exportar) y una cantidad de espacios en blanco hasta completar el largo deseado para el campo. En nuestro caso "Jorge" tiene un largo de cinco, pero el campo "Nombre" tiene que tener 15. Los 10 espacios necesarios son calculados por la expresión BD!A$1-LARGO(BD!A3)
Copiamos esta fórmula a lo largo del rango necesario



Ahora tenemos que guardar esta hoja como archivo texto delimitado con tabulaciones, tal como mostramos más arriba. Es recomendable crear una copia de la hoja usando la opción Mover o copiar (clic con el botón derecho en la pestaña de la hoja) a un libro nuevo y asegurándonos de haber señalado la opción "crear una copia"



Al guardar este nuevo libro, que contiene sólo la hoja con los datos, recibimos este mensaje



Apretamos "Si" y guardamos el libro. El resultado es



Como ven, el ancho de los campos está de acuerdo a nuestras definiciones.

Un problema potencial se presenta cuando el largo del valor de la celda en BD es mayor que el ancho requerido para el campo.
Por ejemplo, si el ancho para el campo "Ciudad" es sólo de 15 caracteres, cuando ponemos Santiago de Chile (17 caracteres) en la hoja BD, obtensmo un resultado de error en la hoja "ascii"



Esto se debe a que la función REPETIR no puede aceptar un número negativo como segundo argumento.
En esto casos tenemos dos opciones:
1 – cambiar el parámetro del ancho del campo (lel valor en la celda C1 en nuestro ejemplo);
2 – cambiar la fórmula para que "recorte" el texto al ancho requerido, desechando los caracteres supernumerarios. Para eso usamos esta fórmula

=SI(LARGO(BD!C5)>BD!C$1;IZQUIERDA(BD!C5;BD!C$1);BD!C5&REPETIR(" ";BD!C$1-LARGO(BD!C5)))

que nos dará como resultado



es decir, recortando Santiago de Chile (17 caracteres) a Santiago de Chi (15 caracteres).

El archivo con el ejemplo esta a vuestra disposición aquí.

Technorati Tags: MS Excel

Importar datos de la Web a Excel

Estoy preparando una nota para mi blog sobre gráficos y presentación de datos. La idea es mostrar un gráfico que refleje el ranking de los jugadores de tenis, tal como lo muestran en las transmisiones de torneo de Masters de Shanghai (espantosa derrota de Nalbandian a manos de Blake ).

Estaba por empezar a tipear manualmente los nombres de los primeros diez clasificados del ranking, cuando me acordé que Excel permite importar datos de tablas de la Web fácilmente.

Supongo que muchos de mis lectores conocen esta funcionalidad, pero para aquellos que no aquí un pequeño ejemplo.

Encontré el ranking de la ATP en la página del torneo de Masters.




Para importar los datos de esta tabla (o cualquier otra que Excel reconozca en el sitio), hacemos lo siguiente:

1 – Abrimos la página donde se encuentra la tabla y copiamos el URL al clipboard.

2 – En una hoja de Excel abrimos el menú Datos—Obtener Datos Externos—Nueva Consulta Web



3 – En la ventanilla dirección pegar el URL de la página que contiene la tabla de datos



4 – Navegar en la ventana hasta encontrar la tabla. Excel marca las tablas que reconoce con una flecha sobre un fondo amarillo



5 – Hacer clic sobre la flecha para marcar la tabla. Es posible marcar más de una tabla. Después de marcar la tabla, apretar Importar



Señalamos dónde queremos ubicar los datos y apretamos Aceptar

Excel crea un vínculo con la tabla de origen, de manera que podemos renovar los datos sin necesidad de abrir la página.




A veces conviene crear referencias a los datos en otra hoja, para poder controlar mejor el formato. En nuestro ejemplo, Excel ha dejado dos columnas vacías entre cada columna importada. Si eliminamos las columnas vacías, al renovar los datos volverán a aparecer.



Categorías: Manejo de Datos_

Technorati Tags: Excel

Importar lista de archivos a Excel

Ya vimos que las funciones XLM (macrofunciones Excel 4) nos permiten realizar tareas con fórmulas que de otra manera solo serían posibles con macros.
Una de estas funciones es ARCHIVOS. De acuerdo al archivo de ayuda:

Devuelve una matriz de texto horizontal con los nombres de todos los archivos que se encuentran en el directorio o en la carpeta especificados. Use ARCHIVOS para crear una lista de nombres de archivo sobre los que desea que actúe su macro.

La sintaxis es: ARCHIVOS(directorio)

donde "directorio" especifica los directorios o carpetas que contienen los archivos cuyos nombres se van a devolver.

Un lector del blog me consulta como se puede importar a una hoja de Excel una lista de los archivos de una carpeta.
Supongamos que quiero importar a una hoja de Excel los archivos

Esto la hacemos usando la macrofunción ARCHIVOS (FILES en la versión inglesa).
Los pasos son:
1 - definimos el nombre "Archivos" (Insertar--Nombres--Definir), que contiene esta formula: =ARCHIVOS($A$1).





Prestar atención a la referencia absoluta en la fórmula.

2 - En al celda A1 escribimos:
D:\My Music\Mercedes Sosa\Cantata Sudamericana \*.*

3 - En la celda B1 escribimos la formula: =INDICE(Archivos,FILA())

4 - Copiamos la formula hacia abajo (celdas B2, B3, etc) hasta que recibimos como resultado #REF.

El resultado es el siguiente:




Ahora, seleccionamos todo el rango de la columna B y hacemos Copiar--Pegado especial--Valores, para anular las formulas.

Si queremos usar la fórmula en otras hojas del cuaderno, en el diálogo de definición de nombres, borramos el prefijo Hoja1 en la ventanilla "se refiere a", cuidándonos de dejar el signo "!". De esta manera el nombre Archivos se referirá a la celda A1 de la hoja donde se encuentre, y a la hoja donde fue definido.




En esta entrada hay un enlace para descargar el archivo de ayuda en español.

Categorías: Funciones&Formulas_,

Technorati Tags: Excel

Excel - Como importar archivos texto a con más de 65536 filas.

Ya hemos visto algunas técnicas para importar archivos texto a Excel.
Si usamos alguna de las versiones a partir de Excel 97 y hasta Excel 2003, el máximo de filas que podemos importar es 65536.
En versiones anteriores a Excel 97 el máximo era 16384. A partir de Excel 2007 dispondremos de más de 1 millón de filas.
Pero como la mayoría de los usuarios seguimos trabajando con alguna de las versiones entre Excel 97 y Excel 2003, veremos como solucionar el problema de importar archivos de mas de 65536 filas.
La base de datos de conocimientos de Microsoft propone una macro para solucionar el problema. La solución consiste en dividir automáticamente el archivo a importar, si excede el máximos de filas permitido, en varias hojas.
En esta entrada reproduzco la macro propuesta por Microsoft, con los mensajes traducidos al español.
Además he reemplazado el método Inputbox del original por GetOpenFilename, que permite elegir el archivo desde un diálogo, en lugar de tener que escribir el nombre y la ubicación del archivo que queremos importar.

El archivo con la macro se puede descargar aquí



Esta macro no divide las líneas del archivo importado en columnas. Esto debe hacerse usando el comando Datos---Texto en Columnas.


Categorías: Varios_





Technorati Tags: Vba, Importar archivos a Excel

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: MS Query, Importar Datos, Bases de Datos

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