De matriz a columna con Power Query

En el año 2008 publiqué una nota sobre como pasarlos datos de una matriz de varias columnas a una sola columna. La tarea la realizábamos con fórmulas que combinaban las funciones DESREF(), TRUNCAR(), RESIDUO() y COLUMNAS() entre otras. Como se puede intuir, bastante complicado. Agreguemos a esto los problemas de rendimiento que se presentan cuando tratamos de aplicarlas a matrices de cierto tamaño (recordemos que DESREF es volátil).

Un lector me consultaba sobre una macro para convertir una matriz de 24 columnas por 600 filas a una sola columna. La idea de usar una macro era obvia; aplicar fórmulas con DESREF a 14400 celdas de una hoja es una muy mala idea en términos de rendimiento.

Una macro puede dar una buena solución y una búsqueda en la Internet les permitirá encontrar varios códigos para hacerlo. Sin embargo, desde la aparición del Power Query (Excel 2010 en adelante), podemos hacerlo con facilidad y eficiencia sin necesidad de echar mano a las macros.

Suficiente cháchara; vamos a mostrar cómo hacerlo. Supongamos una matriz de 24 columnas por 600 filas que contiene valores numéricos y no tiene encabezamientos

El primer paso es cargar la matriz en la ventana del power query usando la opción “From Table”

asegurándonos de quitar la marca de la opción "My table has headers" (mi tabla tiene encabezados)

Una vez en la ventana del Power Query, agregamos una columna de índices (en el próximo paso se entenderá el objetivo)

Excel crea una columna de números consecutivos a partir del 1. La nueva columna será la última a la derecha (para la presentación en la nota he movido la columna a la izquierda, pero esta acción no es indispensable). Con esta columna seleccionada, usamos la opción "Unpivot other columns" en "Transform"

El reusltado es el siguiente

Ahora removemos las columnas "Index" y "Attribute" 

y cargamos la columna restante a una hoja del cuaderno (Close and Load)

¡Misión cumplida

Este video muestra el proceso, que se realiza en menos de un minuto!

Eliminar estilos en Excel

En el pasado hemos hablado de las bondades de los estilos en Excel. Pero al lado de las bondades convive un problema como el que me consulta un lector:

Pegué en un documento de Excel una celda de otro documento que contenía estilos, ahora han surgido variantes y duplicados de ese estilo y tengo alrededor de 800 estilos parásitos en mi documento, ¿hay alguna forma de eliminarlos todos de un tirón?

Cuando copiamos una celda formada con estilos, tal como describe mi lector, el cuaderno “hereda” los estilos del cuaderno de origen. De manera que si empezamos con los estilos pre-definidos

después de pegar algunas celdas de otros cuadernos podermos encontrarnos con ese cuadro

Excel no tiene un método directo para eliminar todos o parte de los estilos personalizados. Podemos eliminar cada uno por separado apuntando al estilo con el mouse y abriendo el menú contextual

Si queremos eliminar más de tres o cuatro estilos personalizados tendremos que echar mano a las macros.


Sub eliminar_estilos_1()
    Dim Estilo As Style
 
    For Each Estilo In ActiveWorkbook.Styles
        If Not Estilo.BuiltIn Then
        Estilo.Delete
        End If
    Next Estilo
 
End Sub

Esta macro elimina todos los estilos personalizados (aquellos que no sean "BuiltIn").
Si queremos eliminar sólo algunos de los estilos y dejar otros tendremos que modificar la macro de esta manera


Sub eliminar_estilos_2()
    Dim Estilo As Style
    Dim EstElegido As Integer

    For Each Estilo In ActiveWorkbook.Styles
        If Not Estilo.BuiltIn Then
            EstElegido = MsgBox("Eliminar el estilo '" & Estilo.Name & "'?", vbYesNo)
            If EstElegido = vbYes Then Estilo.Delete
        End If
    Next Estilo
  
End Sub

Office 365 y PowerPivot

En mi post sobre Excel 2016 no mencioné que la nueva versión de Office, con Excel incluido, viene en dos variantes fundamentales: la clásica “Desktop” y Office 365 que funciona por suscripción.

No me voy extender en consideraciones sobre las distintas versiones, pero si señalar ciertas diferencias que, en un primer momento, pueden confundir a los usuarios.

Mi primer contacto con Excel 2016 fue a través de la versión de escritorio y la primer sorpresa fue que no tiene actualizaciones automáticas (y parece ser que tampoco manuales). Como señalaba en el post, las nuevas funciones incorporadas a Excel Online y a la versión por suscripción, no aparecen en la versión de escritorio. Lo cual no deja de ser curioso ya que la versión online es gratuita y la de escritorio, obviamente, no.

Hace unos días recibí un mail de Microsoft, al igual que todo quien tenga una cuenta de Onedrive, sobre el cambio de política en lo que respecta al espacio gratuito en la nube. El mail traía una “propuesta que no se puede rechazar”: adquirir una suscripción de Office 365 y recibir un terabyte de espacio en Onedrive, o verse reducido a 5 gigabytes. 

Dadas las circunstancias y ya que el precio de la suscripción no es excesivo (100 dólares al año), compre la suscripción a la versión Home del Office 365 que permite implementarla en cinco máquinas.

Una vez instalado el Office 365, Excel se ve tal como Excel 2016 y muy similar a Excel 2013. La primer sorpresa fue cuando noté que no incluía PowerPivot,

a diferencia de Excel 2013

y de la versión 2016 de escritorio

La situación es que PowerPivot ha desaparecido de casi todas las versiones de Office 365. PP está incluido en Office 365 ProPlus, Office 365 E3 y Office 365 E4. En todas las otras versiones (Home, Personal, Business, Business Premium, etc.) no está. Y esto no deja de ser sorprendente ya que las versiones “básicas” (Home, Personal, etc.) incluyen Access y algunas versiones “business” no.

Mi primera conclusión: quien necesite el almacenamiento en Onedrive, el "cebo" de un terabyte puede justificar la compra de la suscripción, en particular si no se hace uso de PowerPivot (Power Query está incorporado plenamente).

Para quien trabaje con PowerPivot la mejor opción parece ser quedarse en Excel 2013 o Excel 2010.