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

Activar Macros y controles ActiveX en Excel 2010

Quien haya descargado e instalado Excel 2010 descubrirá, como en Excel 2007, que por defecto la pestaña de Programador no aparece en la cinta. Esto se debe a que por defecto las macros no están habilitadas en Excel 2010. Tampoco tenemos acceso a los controles (formulario y ActiveX).

Si están buscando una solución al problema "No se puede insertar el objeto", debido a la actualización de Microsoft del 9 de diciembre, lean esta nota.

Para poder usar las macros y los controles en Excel 2010 tenemos que seguir los siguientes pasos:
Activamos la pestaña Archivo y apretamos el botón Opciones

Activamos el Centro de Confianza y apretamos el botón Configuración del centro de confianza

En el centro e confianza activamos la opción Configuración de macros y habilitamos la opción Habilitar todas las macros

Apretamos Aceptar con lo que habremos habilitado las macros en nuestra copia de Excel 2010.
Sin embargo la pestaña de Programador no aparecerá. Para hacerla aparecer usamos nuevamente el menú Opciones de Archivo y en Personalizar la cinta de opciones marcamos Programador

A partir de ese momento podemos grabar, editar y crear macros y también usar controles en nuestros cuadernos.

Estilo de referencia F1C1 en Excel

Pregunta: ¿Qué hay de particular en esta imagen?

Efectivamente, las referencias a las columnas son números en lugar de letras. Cuando abrimos una instancia de Excel, la referencia a las columnas son, por defecto, letras. Pero Excel tiene otro sistema de referencia a las celdas conocido como "estilo F1C1" (o estilo R1C1 donde R significa row, fila en inglés, y C obviamente columna).

Cuando usamos el estilo F1C1, las direcciones de las celdas son expresadas sólo con números. A diferencia del estilo "normal", nos referimos primero a la fila y luego a la columna. De esta manera la referencia a la celda superior izquierda de la hoja en lugar de A1 será F1C1.

Para pasar de un estilo de referencia al otro en Excel Clásico usamos el menú Herramientas-Opciones y en la pestaña General marcamos la opción Estilo de referencia F1C1

En Excel 2007 apretamos el botón del Office y en Opciones de Excel pulsamos Fórmulas y marcamos la el Estilo de Referencia F1C1

Vamos a profundizar un poco más en el tema y trataremos de responder a lo que seguramente se pregunta más de un lector: ¿para qué sirve esto?



Al igual que en el estilo normal, existen referencias absolutas, relativas y mixtas. Por ejemplo, la fórmula =F1C1+F2C1 se refiere siempre a la celdas A1 yA2, no importe donde copiemos o pongamos la fórmula. Es decir, esta notación es absoluta, el equivalente a =$A$1+$A$2.

Si queremos usar referencias relativas usamos corchetes o paréntesis, dependiendo de las definiciones regionales, de esta manera

=F[1]C[1]+F[2]C[1]

o =F(1)C(1)+F(2)C(1).

A esta altura de los acontecimientos es importante distinguir qué significa referencia relativa en este caso.
=F(1)C(1)+F(2)C(1) significa: tomemos el valor de la celda que se encuentra una fila hacia debajo de la celda activa y una columna a la derecha y sumemos este valor al de la celda que se encuentra dos filas hacia abajo y una columna a la derecha.

Tomemos como ejemplo esta tabla

Los valores de la columna D son calculados con la fórmula =C2*B2 que hemos copiado al resto de las celdas en la columna. En forma automática Excel cambia la referencia cambiando la letra y el número de referencia

Veamos ahora que pasa si usamos el estilo de referencia F1C1

Todas las fórmulas son idénticas: =FC(-1)*FC(-2)

Como podemos apreciar, se trata de dos métodos distintos de referenciar celdas en la hoja. La referencia nn el método normal, aún cuando nos referimos específicamente a la celda A1, por ejemplo, es relativa. Si la usamos en una fórmula y copiamos esta fórmula a otra ubicación en la hoja, la referencia cambiará de en relación a la nueva ubicación.
En cambio, cuando usamos F1C1, no importa donde copiemos la fórmula siempre nos estaremos refiriendo a la primer celda de la hoja.

¿Por qué existen ambos métodos en Excel? No sé si Microsoft se ha expedido sobre el tema en alguna oportunidad pera parece ser que los motivos son históricos. La primer hoja de cálculo comercial, VisiCalc usaba el sistema de referencia A1. Lotus, la más popular de las hojas en los años 80 y principios del 90 también usaba este sistema. El primer producto de Microsoft en esta área fue el Microsoft Multiplan que usaba el sistema F1C1. Aparentemente, al crear Excel Microsoft decidió dar la batalla por el mercado y adoptó el mismo sistema de Lotus, que en esos tiempos dominaba el mercado.

Y ahora llegamos al núcleo de la cuestión: ¿para qué sirve este método? O la pregunta equivalente, ¿por qué tengo que saber que existe?

Respuestas posibles son:

• Para poder demostrar nuestros profundos conocimientos sobre Excel e impresionar a nuestro jefe (o a su secretaria, o a la nueva empleada del departamento de contaduría).
• Para entender por qué cuando grabamos una macro vemos esto en el módulo del editor
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
• Porque según dicen, en algunos casos es mejor usar este método.
Debo confesar que hasta el día de hoy no me he visto en una situación donde imprescindiblemente tenga que usar este método. Pero nunca se sabe…