Transponer rangos que contienen referencias

Excel facilita la tarea de transponer rangos, es decir, invertir el orden filas/columnas. Todo lo que hay que hacer es seleccionar el rango, copiarlo (Ctrl+C) y pegarlo en otra área usando la opción Transponer de Pegado Especial.
El proceso funciona sin problemas si el rango a transponer contiene sólo valores

y por lo general también si el rango contiene fórmulas. En el ejemplo de arriba, la celda C3 (Enero/Norte) contiene la fórmula

=SUMAPRODUCTO((Mes=$B3)*(Sucursal=C$2)*Ventas)

donde "Mes", "Sucursal" y "Ventas" son nombres definidos que se refieren a los rangos que contienen los datos.

En la tabla transpuesta las ventas de Enero de la zona norte se encuentran en la celda C13 que contiene la fórmulas

=SUMAPRODUCTO((Mes=C$12)*(Sucursal=$B13)*Ventas)

Pero hay casos en que Excel puede dar resultados incorrectos como en este ejemplo. Los datos en el rango D2:E6 se refieren a los datos del rango B2:B11

El valor en D2 está ligado a B2; E2 a B7; D3 a B3; etc.

Dado que las referencias son relativas, al transponer el rango Excel "reordena" las referencias

Podemos transponer copiando sólo lo valores (Pegado Especial-Valores-Transponer), pero si queremos conservar las referencias/fórmulas podemos aplicar esta técnica que publicó Chandoo en su blog:

1. Seleccionamos el rango a transponer (D2:E6 en nuestro ejemplo);
2. usamos Buscar y Reemplazar (Ctrl+L) para reemplazar los "=" por "#"
3. copiamos (Ctrl+C) y transponemos el rango modificado;
4. en el rango transpuesto reemplazamos los "#" por "="

Al reemplazar los "=" por "#", las referencias se convierten en constantes; al reemplazar los "#" por "=" en el rango transpuesto, convertirmos las constantes nuevamente en referencias. Este video muestra el proceso

Este método es útil sólo si los símbolos "=" aparecen al principio de la referencia/fórmula.

Usos del Power Query - importar y transformar datos externos

En la nota anterior vimos cómo importar datos externos con el Power Query, más precisamente una listas de carpetas y archivos. De la misma manera podemos importar datos externos diversas fuentes como bases de datos, Web, archivos texto, archivos Excel, etc.
Power Query nos permite también transformar los datos originales antes de transferirlos a una hoja de Excel o al modelo de datos (tema que será tratado en otra nota).

Siguiendo con el ejemplo de la nota anterior la ventana del editor del Query se ve así

La ventana del editor tiene una barra de fórmulas, un área de datos (filas y columnas) y dos barras a los costados ("Navigator" y "Steps").

Como podrán apreciar, en los encabezamientos de las columnas aparece un triángulo similar al que aparece cuando aplicamos Autofiltro a una tabla en la hoja de Excel. Estos triángulos tiene la misma función en la ventana de Query

Esto nos permite filtrar las filas de la misma manera como lo hacemos con las tablas en Excel. Por ejemplo, si queremos que la venta del query muestre sólo las filas con archivos de tipo ".png", filtramos de acuerdo a este criterio

Una vez aplicado el filtro podemos apretar el botón "Done" en la ventana del query y sólo las filas filtradas serán transferidas a la hoja de Excel

Si queremos cambiar el resultado de la consulta, abrimos el editor del query usando el comando Filter & Shape

Supongamos ahora que queremos transferir sólo los archivos de tipo ".png" creados en el 2014; todo los que hacemos es aplicar un nuevo autofiltro a la columna "Date Created"

Haciendo un clic con el botón derecho del mouse podemos ver las distintas posiblidades para cada una de las columnas.  Por ejemplo, un clic en el encabezado de la columna "Content" nos muestra

Como ven podemos eliminar la columna (Remove) o eliminar todas las otras columnas (Remove other columns), duplicarla, cambiar el tipo de datos, buscar y reemplazar valores (Replace values),etc. Especialmente práctica es la función "Unpivot Columns" como ya hemos mostrado en esta nota.

Supongamos que queremos eliminar todas las columnas excepto "Name", "Extension" y "Date Created". Lo que hacemos es seleccionar estas columnas (clic sobre el encabezamiento manteniendo el botón Ctrl apretado) y activar la opción "Remove other columns"

Ahora vamos a explorar las posibilidades de transformación de los datos de la columna "Date Created". Con un clic del botón derecho del mouse abrimos el menú contextual

Debido al tipo de datos de la columna, la función "Transform" nos muestra las distintas posibilidades de transformación: fecha (Date), hora (Time), día (Day), etc. Por ejemplo, si elegimos la opción "Date", las fechas aparecerán sin las horas.

Si expandimos la barra "Steps" a la derecha de la ventana del Query, podemos ver los pasos que hemos realizado

Cuando apuntamos a una de las acciones aparece una X. Un clic sobre la X cancela la operación, como la acción "Deshacer" (undo, Ctrl Z) en Excel.

En la próxima nota veremos más usos de las posibilidades de transformación de datos con Power Query.

Usos del Power Query - importar una lista de archivos a Excel

Las nuevas herramientas que Microsoft ha incorporado a Excel hace que las versiones anteriores a Excel 2010 parezcan prehistóricas. Me refiero a las herramientas de BI (Business Intelligence, Inteligencia de negocios), específicamente Power Pivot y Power Query.
A quien le interese el Power Pivot  (y a todo analista de datos le debe interesar) le recomiendo visitar el sitio de Miguel Escobar, Powered Solutions. Miguel ofrece cursos de Power Pivot y quien esté interesado puede acceder a la información con un clic en el banner que aparece en la columna derecha (aclaración: por cada inscripción al curso recibo una comisión)

En esta y otras futuras notas me ocuparé de usos prácticos del complemento Power Query. Algunos de mis memoriosos lectores recordará la nota sobre cómo importar una lista de archivos usando funciones XLM (las viejas macrofunciones de Excel 4). Una nota posterior trataba sobre como generar la lista usando Vba. Ambas técnicas tienen un cierto grado de complejidad y requieren cierto nivel de conocimientos en Excel.

El complemento Power Query simplifica la tarea, lo que mostraremos con este ejemplo (el menú del Power Query en mi máquina está en inglés por lo que me discuplo de antemano). Supongamos que quiero crear una lista con todos los archivos que aparecen en mi carpeta "Blog".

Como puede apreciarse, bajo la carpeta Blog hay otras y también archivos de diversos tipos.
Después de descaragar e instalar el complemento (Excel 2010 y 2013), veremos una nueva pestaña en la ventana de Excel: Power Query

En el grupo "Get External Data" (datos externos), elegimos la opción "From File - From Folder"; Excel abre una ventanilla donde seleccionamos la ubicación deseada

Al apretar OK dos veces, Excel transfiere los datos a la ventana del editor del Query (más  adelante transferiremos los datos a la hoja de Excel).

En esta ventana del editor podemos ordenar, seleccionar y transformar datos, agregar o eliminar columnas y muchas otras operaciones. Nótese además que el editor cuenta con una barra de fórmulas (pero de todo esto nos ocuparemos en otras notas).

Después de examinar los datos, todo los que nos queda por hacer es apretar "Done". Excel transfiere los datos del editor del Query a una hoja de Excel

En esta nota tampoco nos ocuparemos de la ventanilla de las definiciones del Query (Query Setup) que aparece a la derecha de la hoja. Sencillamente la cerramos apretando el "x" en el icono "Show Query Settings" en la barra de opciones. ¡Listo! En la hoja de Excel tenemos una tabla con todos los datos de los archivos de la carpeta

En la próxima nota veremos, basados en este ejemplo, como podemos transformar los datos antes de transferirlos a Excel.