TRANSPONER DATOS PERSONALIZADOS EN EXCEL

Hace unos días recibí una consulta en la que se me planteaba la problemática de realizar una transposición de datos, es decir, colocar de forma horizontal una serie de datos que previamente estaban ordenados de forma vertical.

Pues bien, existen múltiples forma de hacerlo, se puede hacer con fórmulas, se puede hacer con código y se puede hacer con tablas dinámicas … o utilizando código en la propia tabla dinámica. De esta última forma estamos aprovechando toda la potencia de la tabla dinámica y la versatilidad del código para realizar trabajos personalizados.

Para ilustrar este caso práctico voy a poner el ejemplo de una comunidad de propietarios, donde en una hoja denominada (DATOS) tenemos la siguiente información: ID, NOMBRE, FECHA e IMPORTE distribuida en cuatro columnas. En la columna «IMPORTE» está la cuota que han pagado cada mes y en la FECHA el histórico desde enero de 2014 hasta junio 2015.

Lo que queremos conseguir es mostrar la información en registros únicos en cada fila (ID y NOMBRE) y una columna por cada mes mostrando el importe de cada una de las mensualidades por mes. Exactamente esto:

Pues bien, esta información la podemos obtener directamente realizando una tabla dinámica, indicando para las columnas los campos ID y NOMBRE y para la información en horizontal la FECHA. Con la vista de tabla dinámica los datos son estos, idénticos a la imagen anterior, solo que los de arriba son valores y los de abajo son una tabla dinámica:

Por lo tanto, sabemos que podemos realizar la tarea de transponer con una simple tabla dinámica, indicando la información correcta y ocultado o eliminando totales y subtotales de fila y columna. Genial 🙂

Pero … (siempre hay un pero) resulta que lo que en realidad queremos es que se genere esa información desde una tabla con unos datos que van cambiando periódicamente y no queremos estar actualizando y cambiando continuamente, por ejemplo el rango de los datos de origen para generar la taba dinámica.

Para ese cometido vamos a necesitar echar mano de VBA y confeccionar un código a medida, para ello he optado por realizar el siguiente ejercicio:
– Usar la grabadora de macros para grabar creación de la tabla dinámica.
– «Tunear» la macro para que realice lo que necesito.

La macro grabada directamente sería esta:

Sub Macro1()
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATOS!R1C1:R371C4", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="TRANSPONER!R1C1", TableName:="Tabla dinámica1", _
DefaultVersion:=xlPivotTableVersion10
Sheets("TRANSPONER").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("ID")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("FECHA")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica1").PivotFields("IMPORTE"), "Suma de IMPORTE", xlSum
Range("A6").Select
ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("ID").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
Range("A33").Select
ActiveSheet.PivotTables("Tabla dinámica1").ColumnGrand = False
Range("U2").Select
ActiveSheet.PivotTables("Tabla dinámica1").RowGrand = False
End Sub


Pero este código solo nos va a servir para reproducir la tabla con los datos que tenemos en este momento, si aumentamos el rango o queremos cambiarla de ubicación tendremos que modificar la tabla. Para automatizar el proceso debemos por lo tanto, solucionar el primer lugar el problema del los rangos variables, (que se decida incluir a más personas en el rango o eliminarlas). Esto lo podemos hacer agregando una variable a la fuente de los datos que indique el final de los datos, es decir, la última celda que tiene datos.

En la macro original la fuente de datos es SourceData:= «DATOS!R1C1:R371C4», es decir, va desde la fila 1 de la columna 1 hasta la fila 371 de la columna 4.

Si definimos una variable (a la que llamaremos «final») podremos solucionar el problema:

Dim final As Double
final = Application.CountA(Worksheets(«DATOS»).Range(«a:a»))

Donde final es el total del contador de ContarA. y quedaría así la fuente de datos:
SourceData:=»DATOS!R1C1:R» & final & «C4»

Ahora que ya tenemos esta parte, solo tenemos que incluir en la primera parte del código, que cada vez que se ejecute la macro se limpie o elimine cualquier dato en la hoja «TRANSPONER», dado que no se puede generar una tabla dinámica encima de otra. También, al final de la macro debemos colocar una parte del código que copie y pegue valores, de forma que desaparezca la tabla dinámica y se queden solo datos para mejor manipulación.

Os dejo aquí la macro totalmente modificada o «tuneada» jeje. Os dejo en verde las instrucciones comentadas para que sea más sencillo de comprender. En algunos casos he eliminado parte de código que no era necesario y en otros he añadido algunas instrucciones, como por ejemplo, evitar el refresco de pantalla u ocultar los registros que aparezcan en blanco.

Sub Transponer()
'CREAMOS LA VARIABLE PARA DETERMINAR LA LONGITUD DE LOS DATOS
Dim final As Double
final = Application.CountA(Worksheets("DATOS").Range("a:a"))
Application.ScreenUpdating = False
'SELECCIONAMOS LA HOJA TRANSPONER Y ELIMINAMOS TODOS LOS DATOS
Sheets("TRANSPONER").Select
Sheets("TRANSPONER").Cells.Select
Selection.Delete Shift:=xlUp
'SELECCIONAMOS LA HOJA DATOS
Sheets("DATOS").Select
'CREAMOS LA TABLA DINÁMICA, LOS DATOS SE ACTUALIZAN AUTOMÁTICAMENTE CON LA VARIABLE "FINAL"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATOS!R1C1:R" & final & "C4", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="TRANSPONER!R1C1", TableName:="Tabla dinámica1", _
DefaultVersion:=xlPivotTableVersion10
'LLEVAMOS LA TABLA A LA HOJA TRANSPONER
Sheets("TRANSPONER").Select
'INDICAMOS QUE SE GENEREN LOS DATOS DESDE LA FILA 1 Y LA COLUMNA 1
Cells(1, 1).Select
'AGREGAMOS LA PRIMERA COLUMNA "ID" Y OCULTAMOS DATOS EN BLANCO
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("ID")
.Orientation = xlRowField
.Position = 1
End With
'AGREGAMOS LA SEGUNDA COLUMNA CON EL CAMPO "NOMBRE"
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE")
.Orientation = xlRowField
.Position = 2
End With
'AGREGAMOS LA FILA DE DATOS HORIZONTALES "FECHA"
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("FECHA")
.Orientation = xlColumnField
.Position = 1
End With
'AGREGAMOS EL IMPORTE AL CONTENIDO DE LA TABLA
ActiveSheet.PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica1").PivotFields("IMPORTE"), "Suma de IMPORTE", xlSum
'OCULTAMOS SUBTOTALES DEL CAMPO "ID"
ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("ID").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
'OCULTAMOS EL TOTAL GENERAL DE COLUMNA
ActiveSheet.PivotTables("Tabla dinámica1").ColumnGrand = False
'OCULTAMOS EL TOTAL GENERAL DE FILA
ActiveSheet.PivotTables("Tabla dinámica1").RowGrand = False
'*COPIAMOS Y PEGAMOS VALORES. ELIMINAMOS LA PRIMERA FILA DE LA TABLA DINAMICA
With Sheets("TRANSPONER")
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
End With
Application.ScreenUpdating = True
'*PARA VER LA TABLA DINÁMICA SOLO HAY COMENTAR LA PARTE DEL CODIGO DE COPIAR Y PEGAR VALORES
'PARA QUE NO REALICE ESTA ULTIMA INSTRUCCION
End Sub

Como habéis podido comprobar, el resultado son los datos con formato valores y no como tabla dinámica, la idea es que sirvan para trabajarlos como si se tratase de un tabla «normal», pero si queréis que se quede como una tabla dinámica, tan solo tenéis que comentar* (o eliminar) la última parte de la macro (os lo he indicado en el código mostrado) y no convertirá en valores el formato.

* Doy por supuesto que lo sabéis, pero «comentar» es marcar al principio de cada línea de código con comillas simples.

Pues este es el ejemplo de hoy, espero que os sirva y sobre todo os invito a que grabéis vuestras propias macros y luego las personalicéis según vuestras necesidades.

Para probar el archivo, seleccionar la hoja «DATOS» y pulsar el botón «TRANSPONER DATOS».

No lo he podido probar en excel 2003 ni en 2007, pero supongo que funcionará, si genera algún error, agradezco que me lo comentéis.  🙂

Descarga el archivo de ejemplo pulsando en: TRANSPONER DATOS PERSONALIZADOS