Anular dinamización de columnas con VBA

A veces se nos presenta la necesidad de modificar la forma en que vemos los datos para poder trabajar con ellos de una forma diferente. Es muy normal ver informes en los que la información es presentada en una tabla en la que podemos ver la evolución de los datos, por periodos de tiempo, y estos periodos están, cada uno de ellos, en una columna diferente.Si queremos disponer de los datos en una forma adecuada para trabajar con ellos para su análisis, deberemos anular dinamización de columnas de los mismos.

Formato de los datos. Que queremos

A continuación puedes ver una forma típica de estos informes, donde se puede ver la evolución de ventas de determinadas referencias en algunos clientes, mes a mes. Desde luego, es una forma muy clara de ver los datos y poderlos analizar. Sin embargo, si quisiésemos procesarlos de alguna forma para poder hacer otro tipo de análisis, como por ejemplo, crear una Tabla Dinámica, o mejor aún, un PowerPivot, esta no es la forma mas adecuada ya que para ello, los datos deben estar en forma de lista, con campos de agrupación y campos de datos.

En el ejemplo anterior, nuestros campos de agrupación serían Cadena y Código-Nombre, que son los campos sobre los que querremos consultar los datos agrupados (cuanto hemos vendido a una Cadena de clientes o cuanto hemos vendido de una referencia) y los campos de datos serían las unidades vendidas en cada periodo. En concreto, el tipo de dato que tenemos es las ventas de cada mes (dato), que tiene un valor concreto para cada cadena/Articulo (valor).

La forma en que debiéramos tener dispuestos esos datos para poder posteriormente utilizarlos para crear una tabla dinámica o agregarlos al modelo de datos de un PowerPivot sería la siguiente, lo que se conoce como anular dinamización de columnas:

Como ves, están en forma de lista y sin líneas en blanco entre datos o bloques de datos. Este punto es importante: los datos deben ser un todo con continuidad. Dejar líneas en blanco entre bloques, por ejemplo, entre los datos de cada mes, puede que a alguien le resulte muy estético (para gustos los colores), pero desde luego tiene mas de procesador de texto que de Excel y nos va a generar problemas a la hora de trabajar con los datos:

• Los filtros no van a funcionar correctamente ya que normalmente paran de buscar cuando hay líneas vacías al creer que han llegado al final de los datos
• Si quieres definir una tabla, la búsqueda automática del rango de datos no funcionará
• Si empleas determinadas técnicas de VBA para buscar la última fila con datos, como es Rango.end(XlDown) tampoco funcionará correctamente

En resumen, todo ventajas como puedes ver.

Lee la entrada Excel no es un editor de texto al respecto de este punto.

En este artículo os presentaré un método empleando VBA para convertir los datos de la primera tabla a la forma de la segunda y así después poder trabajar con ellos de la forma que queramos.

Objetivo: convertir el formato de presentación

Obviamente, anular la dinamización de columnas se puede hacer de forma manual. Tan solo hay que copiar hacia abajo el bloque de campos de agrupación, tantas veces como columnas de datos haya y después, en cada uno de esos bloques, ir copiando los campos de datos. Claro está, todo esto sin liarse cuando vayamos bajando, y copiar los datos en un sitio equivocado, cosa mas que probable.

La tarea no es complicada. Si intentamos desglosar los pasos que nuestra macro debe hacer, serían los siguientes:

1. Copiar el bloque de campos de agrupación. La copia debe hacerse n-1 veces, donde n es el número de columnas de datos que tengamos. Y es n-1, porque el primer bloque será el original
2. Ir moviendo los campos de datos tantas veces como bloques haya. Los datos del primer bloque ya están «casi» en su sitio, por lo que habrá que hacerlo también n-1 veces
3. A cada uno de esos bloques de datos, además, hay que añadirle la cabecera para cada dato, que es el identificador que necesita para saber dicho dato a que posterior agrupación corresponde

Además de estos pasos, necesitamos saber de cuantos datos a copiar hablamos, es decir, necesitamos saber la primera y última fila en que se encuentra la información.

Una vez tenemos claro que hacer, nos ponemos manos a la obra.

El UserForm

Lo primero que haremos, será abrir el editor de VBA e insertar un UserForm para nuestro propósito. Yo he creado uno al que he dado el nombre de UF_Trasponer y que tiene este aspecto:

Para que puedas indentificarlos luego en el código, he nombrado los diferentes controles de la siguiente forma:

Inicialización del fromulario

Lo primero que haremos será definir la rutina de inicialización del formulario, que se ejecutará cuando se cargue y que lo único que hace es asignar el formulario a la variable Objeto UF y establecer su posición inicial arriba y a la izquierda de la pantalla. Si te sientes mas cómodo, puedes también establecer los valores por defecto de los TextBox de datos de filas y columnas a procesar. Yo no lo he hecho, por que ya los he definido en la configuración inicial de dichos controles.

Private Sub UserForm_Initialize()
Dim UF As Object
Set UF = UF_Trasponer
UF.Top = Application.Top + 10
UF.Left = Application.Left + 10
End Sub

Botón Cancelar

Ahora definimos que hacer si pulsamos Cancelar. En este caso, lo que haremos es ocultar el formulario, que recuerda que lo he llamado UF_Trasponer:

Private Sub CB_Cancelar_Click()
      Me.Hide
End Sub

Rutina Principal

Y ahora, vamos a la rutina principal, que hará las acciones que hemos enumerado antes cuando hagamos click en el botón Dale caña además de alguna cosilla interesante.

Private Sub CB_OK_Click()
     Dim FilaInicio As Long
     Dim FilaFin As Long
     Dim ColFijas As Integer
     Dim NumColumnas As Long

     If IsNumeric(UF_Trasponer.TB_FilaInicio.Value) Then
          FilaInicio = UF_Trasponer.TB_FilaInicio.Value
     Else
          MsgBox "El dato de fila de inicio no es válido. Revísalo, por favor.", vbExclamation + vbOKOnly, "ERROR"
          Exit Sub
     End If

      If IsNumeric(UF_Trasponer.TB_FilaFin.Value) Then
          FilaFin = UF_Trasponer.TB_FilaFin.Value
     Else
          MsgBox "El dato de fila final no es válido. Revísalo, por favor.", vbExclamation + vbOKOnly, "ERROR"
          Exit Sub
     End If

     If IsNumeric(UF_Trasponer.TB_ColumnasFijas.Value) Then
          ColFijas = UF_Trasponer.TB_ColumnasFijas.Value
     Else 
          MsgBox "El dato de columnasfijas no es válido. Revísalo, por favor.", vbExclamation + vbOKOnly, "ERROR"
          Exit Sub
     End If

     If IsNumeric(UF_Trasponer.TB_ColumnasDatos.Value) Then
          NumColumnas = UF_Trasponer.TB_ColumnasDatos.Value
     Else
          MsgBox "El dato de número de columnas no es válido. Revísalo, por favor.", vbExclamation + vbOKOnly, "ERROR"
          Exit Sub
     End If

     Call OrdenarDatosParaTD(FilaInicio, FilaFin, ColFijas, NumColumnas)
     UF_Trasponer.Hide
End Sub

Lo primero que hace esta rutina es definir variables para cargar en ellas los datos del formulario. No sería necesario ya que se podría utilizar directamente el valor de los TextBox, pero clarifica el código a mi entender.

Después va cargando los valores definidos en dichas variables, previa comprobación de que son datos numéricos. Si nos hemos equivocado al teclearlos, nos avisará de que no son números y finalizará la ejecución del código.

Es un chequeo de errores sencillo y no exhaustivo ya que podríamos comprobar mas cosas, como por ejemplo que los números de filas tengan datos, que la última fila sea efectivamente la última ya que si no sobre escribiremos datos, etc. De todas formas como es un código para mi uso personal, tampoco se trata de ponerle música ya que se perfectamente lo que hace. Otra cosa sería si fuese un código para distribuir, en cuyo caso la rutina de chequeo de errores y tratamiento de los mismos debe ser lo mas robusta posible. La experiencia me dice que cuanto menos conoce la gente las herramientas informáticas que usa, mas afina su capacidad de encontrar formas de que algo que vaya mal.

Rutina de ordenación de los datos

Hecha la comprobación de los datos, hacemos una llamada a la rutina principal de ordenación de datos, a la que he llamado OrdenarDatosParaTD.

Finalmente, realizada la ordenación, oculta el formulario.

Recibiendo parámetros

Esta rutina principal comienza por definir los parámetros que espera recibir, que no son mas que los que hemos cargado en las variables del módulo anterior. Además, incluye una descripción de lo que hace la rutina. Esta es una buena práctica que, aunque lleve un poco de tiempo, nos ayudará mucho si en un futuro tenemos que modificar o corregir la rutina. En el momento en que programas algo, sabes perfectamente lo que hace y como lo hace, pero si dentro de unos meses tienes que corregir el código, no tendrás tan frescas esas ideas y puede convertirse en una auténtica tortura entender lo que tu mismo programaste y por qué lo hiciste de esa forma.

Sub OrdenarDatosParaTD(FilaInicio As Long, FilaFin As Long, ColumnasFijas As Integer, ColumnasDatos As Long)
     'Esta rutina procesa los datos de una tabla para convertirlos en lista utilizable por PivotTables
     'Asume que en vertical se encuentran los datos fijos a repetir y en horizontal, en la primera fila de datos -1
     'las cabeceras descriptivas de los datos
     'Se asume que los datos comienzan en la primera columna y no hay columnas vacías o sin titulo

     Dim Datos As Long 'Calculo del numero de datos
     Dim Cabecera As String
     Dim NombreHoja As String
     Dim x As Long

Preservando los datos

Lo siguiente que hacemos para anular la dinamización de columnas, es hacer una copia de la hoja de los datos en otra nueva. Nunca se sabe lo que puede ir mal y si trabajamos directamente sobre los datos originales, una vez ejecutada una macro, no sirve de nada hacer CTRL+z. Lo que se haya hecho, no se puede deshacer. Y si nos hemos cargado los datos, pasaremos un buen sofocón.

'Crear copia de la hoja (para conservar datos originales, por si acaso…)
NombreHoja = ActiveSheet.Name
Sheets(NombreHoja).Select
Sheets(NombreHoja).Copy before:=Sheets(1)
ActiveSheet.Name = NombreHoja & "_Copia"

Preparación previa

Ahora vamos a trasformar los datos mediante un copiado de valores. Si los datos de entrada son valores, no rompemos nada por volverlos a copiar como tales, pero si estamos partiendo de datos calculados con fórmulas, obviando este paso, lo que haremos es imprevisible y podemos montar un buen desaguisado.

'Hacer pegado de valores de la hoja copiada para trasponer datos y no formulas
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Seguimos con el cálculo del número de datos que estamos manejando. Este dato es importante ya que lo usaremos después para saber a partir de que posición ir copiando los datos de los distintos bloques.

Datos = FilaFin - FilaInicio + 1

Copiado de grupo de datos fijos

Y ahora vamos al tema. Comenzaremos por copiar n-1 veces los datos fijos.

'Copiar datos fijos hacia abajo
RangoFijoCopiar = Range(Cells(FilaInicio, 1), Cells(FilaFin, ColumnasFijas)).Select
Selection.Copy

For x = 1 To ColumnasDatos - 1
     Range("A" & (x - 1) * Datos + FilaFin + 1).Select
     ActiveSheet.Paste
Next x

Lo primero que hacemos es copiar el rango de datos del bloque fijo al portapapeles y después, con un bucle For…Next que se ejecuta el número de columnas de datos -1 veces, vamos copiando dichos datos en la celda A? donde ? es la fila que sale del calculo de (x-1)*Datos + FilaFin + 1.

Copiado de datos de información

Ahora vamos a copiar los campos de datos. Para ello, usaremos de nuevo un bucle For…Next que se ejecutará desde la segunda columna de datos (ColumnasFijas+2) hasta la última (ColumnasFijas+ColumnasDatos)

'Copiar datos de columnas
For x = ColumnasFijas + 2 To ColumnasFijas + ColumnasDatos
     'Copiar cabecera
     Cabecera = Cells(FilaInicio - 1, x).Value
     Range(Cells((x - ColumnasFijas - 2) * Datos + FilaFin + 1, ColumnasFijas + 1), _
     Cells((x - ColumnasFijas - 1) * Datos + FilaFin, ColumnasFijas + 1)).Value = Cabecera

     'Copiar datos
     Range(Cells(FilaInicio, x), Cells(FilaFin, x)).Select
     Selection.Copy
     Cells((x - ColumnasFijas - 2) * Datos + FilaFin + 1, ColumnasFijas + 2).Select
     ActiveSheet.Paste
Next x

Como ves, lo primero que hacemos es cargar el encabezado de la columna de datos en la variable Cabecera y copiarlo a su destino y después copiar al portapapeles cada bloque de datos para a continuación pegarlo a partir de la fila correspondiente, en la siguiente columna a la cabecera.

Ya solo nos falta mover los campos de datos de la primera columna, desplazándolos una posición hacia la derecha para hacer sitio para poner su cabecera.

'Copiar primera columna
Range(Cells(FilaInicio, ColumnasFijas + 1), Cells(FilaFin, ColumnasFijas + 1)).Select
Selection.Copy
Cells(FilaInicio, ColumnasFijas + 2).Select
ActiveSheet.Paste
Cabecera = Cells(FilaInicio - 1, ColumnasFijas + 1).Value
Range(Cells(FilaInicio, ColumnasFijas + 1), Cells(FilaFin, ColumnasFijas + 1)).Value = Cabecera

Y terminamos borrando los datos originales (no hemos hecho corta-pega, sino un copiado) y renombrando la cabecera.

'Borrar datos sobrantes
Range(Cells(FilaInicio - 1, ColumnasFijas + 3), Cells(FilaFin, ColumnasFijas + ColumnasDatos)).Select
Selection.Clear
Range("A1").Select

'Modificar cabeceras de Datos
Cells(FilaInicio - 1, ColumnasFijas + 1).Value = "Dato"
Cells(FilaInicio - 1, ColumnasFijas + 2).Value = "Valor"

Rutina completa

Ahora todo junto, nos queda así:

Sub OrdenarDatosParaTD(FilaInicio As Long, FilaFin As Long, ColumnasFijas As Integer, ColumnasDatos As Long)
     'Esta rutina procesa los datos de una tabla para convertirlos en lista utilizable por PivotTables
     'Asume que en vertical se encuentran los datos fijos a repetir y en horizontal, en la primera fila de datos -1
     'las cabeceras descriptivas de los datos
     'Se asume que los datos comienzan en la primera columna y no hay columnas vacias o sin titulo

     Dim Datos As Long 'Calculo del numero de datos
     Dim Cabecera As String
     Dim NombreHoja As String
     Dim x As Long

     'Crear copia de la hoja (para conservar datos originales, por si acaso…)
     NombreHoja = ActiveSheet.Name
     Sheets(NombreHoja).Select
     Sheets(NombreHoja).Copy before:=Sheets(1)
     ActiveSheet.Name = NombreHoja & "_Copia"

     'Hacer pegado de valores de la hoja copiada para trasponer datos y no formulas
     Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     Datos = FilaFin - FilaInicio + 1

     'Copiar datos fijos hacia abajo 
     RangoFijoCopiar = Range(Cells(FilaInicio, 1), Cells(FilaFin, ColumnasFijas)).Select
     Selection.Copy

     For x = 1 To ColumnasDatos - 1
          Range("A" & (x - 1) * Datos + FilaFin + 1).Select
          ActiveSheet.Paste
     Next x

     'Copiar datos de columnas
     For x = ColumnasFijas + 2 To ColumnasFijas + ColumnasDatos
          Cabecera = Cells(FilaInicio - 1, x).Value
          Range(Cells((x - ColumnasFijas - 2) * Datos + FilaFin + 1, ColumnasFijas + 1), _
               Cells((x - ColumnasFijas - 1) * Datos + FilaFin, ColumnasFijas + 1)).Value = Cabecera
          Range(Cells(FilaInicio, x), Cells(FilaFin, x)).Select
          Selection.Copy
          Cells((x - ColumnasFijas - 2) * Datos + FilaFin + 1, ColumnasFijas + 2).Select
          ActiveSheet.Paste
     Next x

     'Copiar primera columna
     Range(Cells(FilaInicio, ColumnasFijas + 1), Cells(FilaFin, ColumnasFijas + 1)).Select
     Selection.Copy
     Cells(FilaInicio, ColumnasFijas + 2).Select
     ActiveSheet.Paste
     Cabecera = Cells(FilaInicio - 1, ColumnasFijas + 1).Value Range(Cells(FilaInicio, ColumnasFijas + 1), Cells(FilaFin, ColumnasFijas + 1)).Value = Cabecera

     'Borrar datos sobrantes
     Range(Cells(FilaInicio - 1, ColumnasFijas + 3), Cells(FilaFin, ColumnasFijas + ColumnasDatos)).Select
     Selection.Clear
     Range("A1").Select

     'Modificar cabeceras de Datos
     Cells(FilaInicio - 1, ColumnasFijas + 1).Value = "Dato"
     Cells(FilaInicio - 1, ColumnasFijas + 2).Value = "Valor"

End Sub

Hacer que sea mas accesible

Ahora que ya sabemos como anular dinamización de columnas y tenemos todo listo, si quisiéramos tener a mano esta macro para poder ejecutarla rápidamente y sobre cualquier documento, no tendríamos mas que añadirla a nuestra plantilla personal de complementos y crearle un acceso directo en nuestra cinta de opciones. Si quieres ver como hacer esto, consulta:

• Personalizando la cinta de opciones
• Personalizar Excel con un fichero de add-ins

Espero que este artículo te haya sido de utilidad. Si es así, puedes dejar tu comentario mas abajo y si algo no te ha quedado claro, puedes también enviar tu consulta o abrir un hilo al respecto en el foro.

En un próximo artículo explicaré otro método para hacer esto mismo con la ayuda de Power Query.