En la nota anterior sobre uso de archivos de texto como fuente para una tabla dinámica, vimos cómo agregar un origen de datos a la lista de las posibilidades del MS Query. Los archivos de textos usan distintos elementos para separar los campos como comas (archivos .cvs), espacios o tabs. Pero también puede darse el caso que se use un separador no convencional como el pipe (|).
En esos casos tendremos que agregar algunos pasos a nuestra solución, lo que mostraremos en esta nota.
Supongamos que queremos analizar los datos de este archivo de texto con una tabla dinámica
Como puede observarse los campos están separados por el carácter "|" (pipe).
Empezamos el proceso tal como lo mostramos en la nota anterior hasta llegar a la etapa "Crear nuevo origen de datos"
Como en el caso anterior elegimos un controlador adecuado para el tipo de datos (texto)
Apretamos el botón Conectar lo que abre el diálogo "ODBC Text Setup"
En esta etapa apretamos el botón Options lo que abre una nueva zona en el formulario
Apretamos el botón Define Format para definir las definiciones del nuevo tipo de archivo. En la parte izquierda del formulario señalamos el archivo que queremos usar (el que sirve de modelo para el nuevo origen), si tiene encabezados y los más importante, cuál es el separador (delimiter)
Luego apretamos el botón Guess lo que nos permite ver las columnas, cambiar el tipo de datos y el nombre del campo
Apretamos OK y en el formulario de Crear nuevo origen de datos apretamos Aceptar.
Esto nos lleva nuevamente al formulario Elegir origen de datos
Al aceptar el nuevo origen volvemos al diálogo del asistente para consultas. También podemos interrumpir el proceso y usar el nuevo origen más adelante.
En las primeras etapas de este blog escribí una serie de notas sobre tablas dinámicas en Excel. Uno de los temas que pasé por alto es el del uso de fuentes de datos externas para construir tablas dinámicas.
La importancia del tema es evidente. Una hoja en Excel Clásico puede contener hasta 65536 filas, lo cual puede ser una limitación crítica si tenemos que analizar grandes cantidades de datos. Si bien Excel 2007 y 2010 han extendido este límite más allá del millón de filas, no creo que un cuaderno con semejante cantidad de datos sea una alternativa razonable.
Excel viene provisto con un mecanismo que le permite conectarse con fuentes de datos externas. Hemos visto este mecanismo en acción en algunas de las notas sobre el uso de MS Query y la importación de datos externos a hojas de Excel.
En esta nota mostraremos como crear una tabla dinámica a partir de datos remotos, es decir, que no se encuentran en el cuaderno que contiene la tabla dinámica.
Estos datos remotos pueden estar en archivos de distinto tipo como Access (.mdb), Texto (.csv, .txt), Excel y otros. Si la fuente de datos aparece en la lista de Excel, el proceso es sencillo. Pero en ciertos casos la fuente no existe y debemos crearla.
En nuestro caso vamos suponer que tenemos los datos en un archivo de texto tipo .csv (comma separated values). En Excel Clásico empezamos el proceso abriendo un cuaderno Excel y usando el menú Datos-Informe de Tablas y Gráficos Dinámicos elegimos la opción Fuente de datos externa. Al apretar el botón Siguiente se abre el diálogo para ubicar la fuente de los datos
En nuestro caso podemos ver que el tipo de datos que queremos usar no figura en la lista. En este caso usamos la opción "Nuevo origen de datos"
En la ventanilla superior ponemos el nombre que queremos dar a la nueva fuente (archivoTXT o cualquier otro que crean conveniente), en la ventanilla 2 elegimos el controlador (driver) indicado para el tipo de datos y finalmente apretamos el botón Conectar.
En el nuevo formulario que se abre ubicamos el archivo que queremos que sirva de base a nuestra tabla dinámica
Si el directorio no coincide con el que aparece en el formulario, quitamos la señal de la casilla "Use current directory" para poder elegir la ubicación indicada.
Finalmente apretamos OK y Aceptar. Con este hemos creado una nueva fuente de datos
El próximo paso es elegir el archivo que contiene los datos
Y seguimos apretando Aceptar hasta llegar a la etapa final
Aquí señalamos la opción "Devolver datos a Microsoft Excel" y Aceptar, lo que no lleva de regreso al asistente de tablas dinámicas (por si no se dieron cuenta, hasta ahora hemos trabajado en el MS Query)
El próximo paso nos lleva al conocido formulario de ubicación de la tabla dinámica en la hoja
Al apretar Aceptar se creará la tabla dinámica
En Excel 2007 hay algunas diferencias, por lo que mostraremos dos caminos. Excel 2007 nos permite usar la interfaz de tablas dinámicas de Excel Clásico usando el atajo de teclado Alt+T+B
A partir de aquí procedemos como mostramos más arriba.
Otra alternativa es comenzar el proceso en la pestaña Datos – Obtener datos externos-de otras fuentes-MS Query
Esto abre el diálogo del MS Query para elegir el origen de datos, tal como sucede con Excel Clásico.
Alguna vez he mencionado que estaba preparando una nota sobre el uso de estilos en Excel. Esta es una funcionalidad tan útil como ignorada, o por lo menos subestimada, por gran parte de los usuarios de Excel.
El tema volvió a mi conciencia a partir de la consulta de un lector a quien “lo mata” (sic) que no pueda controlar el formato de la fuente de los hipervínculos. Cada vez que crea un hipervínculo, Excel le pone la fuente Arial con el tamaño 10, cosa que él nunca usa ni considera usar en algún futuro cercano o lejano.
Primero veamos cómo podemos solucionar el problema de mi sufrido lector y luego nos referiremos al tema de la nota.
Como ya sabemos, podemos agregar un hipervínculo a una celda. El hipervínculo puede dirigirse a una página Web, a un archivo o a una ubicación en una hoja de Excel (y también a un párrafo específico de un documento Word, como ya hemos visto).
En Excel Clásico la tarea de cambiar el estilo de los hipervínculos es sencilla. Seleccionamos una de las celdas con hipervínculo y abrimos el menú Formato-Estilos
En la lista desplegable de nombres de Estilo, aparece Hipervínculo. Apretando el botón Modificar podemos cambiar los formatos a nuestro parecer
Al apretar Aceptar, el formato de todas las celdas que contienen hipervínculos cambiará
Al agregar un nuevo hipervínculo éste recibirá los formatos del nuevo estilo
El nuevo estilo es guardado con el cuaderno donde lo hemos creado y no se aplica a los otros cuadernos.
En Excel 2007 usamos
El menú de las opciones lo abrimos apuntando al estilo (Hipervínculo) y apretando el botón derecho del mouse. Elegimos Modificar y cambiamos los formatos a nuestro gusto.
Pero Estilos, como ya habrán entendido, no se limita a Hipervínculos. Cada Estilo es una colección de formatos que incluye los formatos de números, fuente, bordes, tramas y protección de la celda. Una vez creado el estilo lo podemos aplicar a una celda o a un rango de celdas con el menú Formato-Estilo en Excel Clásico o con Inicio-Estilo de Celdas en Excel 2007.
Podemos crear un estilo con el menú, como indicamos más arriba, o aplicando todos los formatos a una celda y luego usando la opción “según el ejemplo”
Aquí, por ejemplo, hemos creado el estilo Título 1 basándonos en las definiciones de la celda B2.
Entre Excel clásico y Excel 2007 hay algunas diferencias en los métodos de guardar los nuevos estilos que hemos definido.
En Excel Clásico el formulario tiene dos botones para guardar el estilo: Agregar y Aceptar
Si apretamos Aceptar, el todas las celdas de la selección reciben el nuevo estilo (suponiendo que hemos seleccionado más de una celda). Si usamos Agregar y luego Aceptar, se crea el nuevo estilo, pero las celdas de la selección no cambian.
En Excel 2003 podemos agregar la lista desplegable de estilos a una barra
lo que es una forma muy eficiente de usarlos.
En Excel 2007 lo podemos agregar a la barra de acceso rápido
Aplicar estilos es muy sencillo. Vamos a mostrar esto creando un nuevo estilo, EstiloNuevo, con fuente Arial 20, color de fuente azul subrayado y relleno amarillo
Para aplicar el nuevo estilo a este rango
en Excel 2007 después de seleccionar el rango abrimos el menú Estilo de Celdas; al apuntar al nuevo estilo el rango es mostrado como se verá si aceptamos la opción
En Excel Clásico seleccionamos el rango y abrimos la lista desplegable de estilo
¿Cuáles son las ventajas de aplicar estilos? Puede ser a que a primera no sean evidens. Usar estilos nos permite
realizar cambios a grandes cantidades de celdas con un solo clic. Todo cambio en la definición del estilo se refleja inmediatamente en todas las celdas definidas con él;
mantener la uniformidad en los cuadernos que creamos;
ayudan a considerar más a fondo el diseño de las hojas y a conservar su consistencia.
