EXPORTAR INFORME DE TABLA DINÁMICA EN LA MISMA HOJA

Hola todos 😉

Hoy me gustaría ampliar parte de un post anterior: EXPORTAR INFORME DE TABLA DINAMICA A UN LIBRO NUEVO en el que mediante una macro generábamos automáticamente informes de tabla dinámica y exportábamos cada uno de ellos a un nuevo libro.

Hace unos días un lector quería saber qué modificaciones tenía que hacer en la macro para que esos informes no se generasen en un libro nuevo, sino que se generasen en la misma hoja, uno por pestaña.

Lo cierto, es que la modificación no es compleja, solo hemos de eliminar de la macro anterior toda referencia a “nuevo libro” y dejar que los informes se generen automáticamente en la hoja.

Voy a utilizar un ejemplo nuevo para ilustrar el ejercicio, en este caso, volvemos a los datos por provincias y por número de personas en determinados años. Esta sería la información principal, que la tenemos en una hoja denominada “DATOS”:

EXPORTAR INFORME DE TABLA DINAMICA EN LA MISMA HOJA.jpg

El siguiente paso es insertar una tabla dinámica y en la pestaña Diseño aplicar “mostrar en forma de esquema”, de esta forma aparecerá el título del primer campo seleccionado, en nuestro caso: “Provincia”

EXPORTAR INFORME DE TABLA DINAMICA EN LA MISMA HOJA1

Una vez que tenemos la tabla creada, procedemos escribir nuestro código:

Sub GENERAR_INFORMES_TABLA()
Dim i As Integer
Dim Ini As Integer
Dim Fin As Integer
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
Sheets("TABLA").Select
With Sheets("TABLA")
'indicamos la fila siguiente a la primera fila con datos: 01 Araba/Álava
Ini = Columns(1).Range("A1").End(xlDown).Row
'Contamos los todas las provincias de la tabla dinámica1, es decir las 52
Fin = .PivotTables(1).PivotFields("PROVINCIA").VisibleItems.Count
'Inciamos un bucle donde por cada provincia generamos informe (ShowDetail)
For i = 1 To Fin
'para mostrar el informe debemos hacer referencia al campo de valor de la tabla
.Cells(i + Ini, 2).ShowDetail = True
'Nombramos la pestaña con el nombre de la provincia.
ActiveSheet.Name = Application.WorksheetFunction.Substitute((.Cells(i + Ini, 1).Value), "/", "_")
Next i
End With
Application.ScreenUpdating = True
End Sub

Lo que vamos a conseguir con esta macro es generar tantos informes como ítems tengamos en la tabla dinámica y los colocaremos en una pestaña cada uno. Dado que estamos nombrando las pestañas de las hojas, debemos tener en cuenta:

– El nombre de la hoja no puede superar los 31 caracteres.
– No ha de contener los siguientes caracteres: \  / ¿ ? * [  ]
– No puede quedar el nombre en blanco.

Seguro que os habéis fijado que en nuestros datos tenemos uno de esos símbolos “/”: por ejemplo en 01 Araba/Álava. Para solucionar este problema sin tener que modificar los datos, realizaremos el cambio en el mismo momento que nombramos la hoja, simplemente sustituyendo “/” por “_” (por ejemplo). Esta es la línea del código en la que hacemos este reemplazo:

ActiveSheet.Name = Application.WorksheetFunction.Substitute((.Cells(i + Ini, 1).Value), "/", "_")

Por supuesto, también podríamos utilizar la función Mid () si necesitásemos extraer los 31 caracteres de la hoja y la función Trim() para eliminar los espacios en blanco.

Una vez aplicada la macro, el resultado sería el siguiente:

EXPORTAR INFORME DE TABLA DINAMICA EN LA MISMA HOJA2.jpg

Y ya tenemos una pestaña por provincia con el informe correspondiente.

Y estos ha sido todo, espero que os sea de utilidad:

Descarga el archivo de ejemplo pulsando en: EXPORTAR INFORME DE TABLA DINÁMICA EN LA MISMA HOJA

 

 

Anuncios

EXPORTAR INFORME DE TABLA DINAMICA A UN LIBRO NUEVO

Una característica de las tablas dinámicas es que una vez confeccionadas, podemos extraer un informe con el detalle de los datos de cada uno de los campos que hemos seleccionado. Cuando lo hacemos, se crea una pestaña nueva que contiene la información requerida.

Pero esto no siempre resulta funcional, cuando hablamos de poca información (pocos campos), no hay problema, generamos el informe y desde la misma pestaña lo pasamos a un libro nuevo. Pero, ¿Qué sucede si hablamos de cientos de campos?, ¿Tendríamos que ir generando y pasando a un libro nuevo cada uno de los datos?, esto sería muy costoso tanto moral como físicamente 😉

Voy a escenificar un ejemplo de lo que pretendo hacer, primero lo expondré cómo se suele hacer manualmente y luego una alternativa mucho más eficaz.

Imaginad que tenemos los datos de población por sexo de todos los municipios de España ordenados por provincia, así:

EXPORTAR INFORME DE TABLA DINAMICA A LIBRO NUEVO

Ahora realizamos una tabla dinámica, que agrupe la información por cada provincia:

EXPORTAR INFORME DE TABLA DINAMICA A LIBRO NUEVO_1

Y ahora si queremos obtener los datos de, por ejemplo, la provincia de Ávila, solo tenemos que hacer doble clic en uno de los campos de totales y obtendremos en una nueva pestaña la información de ÁVILA:

EXPORTAR INFORME DE TABLA DINAMICA A LIBRO NUEVO_2

Y si queremos pasar la información de Ávila a un libro nuevo, o bien seleccionamos y pegamos o bien vamos al el menú de la pestaña a “copiar o mover” a un libro nuevo.

Pero, ¿y si resulta que los presidentes de cada diputación provincial nos solicitan el detalle la información de su provincia y tenemos que enviarle la información individual?, pues ya podemos ponernos a generar los 52 ficheros, renombrarlos (por defecto en la tabla dinámica sale el número del libro) e ir pasándolos a un nuevo libro. Esto llevaría unos cuantos minutos ¡u horas!, pero ¿y si mañana lo quieren otra vez? ¿y pasado también?.

Como siempre hay una alternativa con VBA, y es mediante este código que acabo de componer:

Sub Generar_informes()
Dim i As Double
Dim Ini As Double
Dim Fin As Double
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
Sheets("TABLA").Select
With Sheets("TABLA")
'indicamos la fila siguiente a la primera fila con datos, (Albacete)
Ini = Columns(1).Range("A1").End(xlDown).Row
'Contamos los todas las provincias de la tabla dinámica1, es decir las 52
Fin = .PivotTables(1).PivotFields("PROVINCIA").VisibleItems.Count
'Inciamos un bucle donde por cada provincia generamos informe (ShowDetail)
For i = 1 To Fin
'para mostrar el informe debemos hacer referencia a los campos de valores (nº Hombres o Mujeres),
.Cells(i+Ini, 2).ShowDetail = True
'Nombramos la pestaña con el nombre de la provincia.
ActiveSheet.Name = .Cells(i+Ini, 1).Value
'Seleccionamos la hoja con el detalle de la provincia
ActiveSheet.Select
'Movemos la hoja a un libro nuevo
ActiveSheet.Move
'Guardamos la hoja con el nombre de la provincia correspondiente
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ActiveSheet.Name
'Cerramos archivo que hemos creado
ActiveWorkbook.Close False
Next i
End With
Application.ScreenUpdating = True
End Sub

Importante en este código:

– Debemos tener en cuenta que el bucle que iniciamos haga referencia a los campos de la tabla dinámica y nunca a los totales. En este ejemplo, hacemos referencia a la provincia (que está en la columna 1).

– Para mostrar los informes de cada uno de los campos, debemos hacer referencia a los totales de la tabla dinámica, ejemplo, el total de Hombres (Suma de Hombres) que está en la columna 2: .Cells(i+Ini, 2).ShowDetail = True.

– Otro tema importante es donde guardamos los informes que vamos a generar, (los libros nuevos). Para este ejemplo, los informes se guardan en la misma carpeta donde tengamos este archivo:

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ActiveSheet.Name

Pero se podría indicar cualquier otra ubicación:

ActiveWorkbook.SaveAs Filename:="E:\" & "\" & ActiveSheet.Name

Para darle nombre al nuevo archivo, debemos hacer referencia a la hoja activa que acabamos de crear: ThisWorkbook.Path & "\" & ActiveSheet.Name

Pues bien, una vez ejecutada la macro, debería generar 52 ficheros en la misma carpeta en la que se encuentra este archivo, y este es el resultado:

EXPORTAR INFORME DE TABLA DINAMICA A LIBRO NUEVO_3

* Esta macro ha sido probada en las versiones 2010, 2013 y  2016. Su funcionamiento es correcto, si bien, en algunos equipos (dependiendo de los requisitos del sistema), puede experimentar problemas relacionados con el rendimiento del equipo.

Descarga el archivo de ejemplo pulsando en: EXPORTAR INFORME DE TABLA DINAMICA A UN LIBRO NUEVO

AGRUPAR EN TRAMOS CON VBA

Llevo una temporada trabajando con el denominado concepto “Big Data” y algunos programas específicos,  y lo cierto es que sí, que es una tendencia consolidada y con un gran potencial. Lo verdaderamente importante, como siempre, son las herramientas con las que vamos a analizar esa enorme cantidad de datos y la figura del analista para trasladar e interpretar los resultados.

En este sentido, Excel se enfrenta a un importante desafío, la capacidad que pueda ofrecer para asumir la gestión de grandes cantidades de datos y las herramientas con las que pueda analizar esas informaciones.

Teniendo en cuenta esto último, he decidido realizar un pequeño ejemplo donde voy a utilizar la máxima cantidad de datos en una hoja Excel y realizar varias macros para comprobar la usabilidad de la herramienta.

El ejemplo que voy a utilizar es de la agrupación de datos en tabla dinámica utilizando VBA. Para ello cuento con tres columnas de datos (Población, Edad y Provincia) y con 1.048.576 registros, que es el número máximo que admiten las versiones superiores a Excel 2003:

AGRUPAR EN TRAMOS CON VBA_1

Con estos datos, lo que realizaremos es una agrupación por Provincia y por Edades y que cuente el total de personas en dichos tramos. Es decir, por ejemplo para Albacete, que los datos que se muestren sean estos: (Nota: los datos son ficticios, son solo válidos para un ejemplo, no es la población real de cada provincia).

AGRUPAR EN TRAMOS CON VBA_0

Este trabajo lo vamos a realizar con la siguiente macro:

Sub AGRUPAR()
Application.ScreenUpdating = False
Sheets("AGRUPADO").Select
Sheets("AGRUPADO").Cells.Select
Selection.Delete Shift:=xlUp
'INSERTAMOS LA TABLA DINÁMICA EN LA HOJA "AGRUPADO", RANGO A1:C1048576
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATOS!R1C1:R1048576" & "C3", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="AGRUPADO!R1C1", TableName:="Tabla dinámica1", _
DefaultVersion:=xlPivotTableVersion14
'INCLUIMOS EDAD Y POR PROVINCIA EN LA TABLA DINÁMICA
Sheets("AGRUPADO").Select
Cells(1, 1).Select
With Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields("EDAD")
.Orientation = xlRowField
.Position = 1
End With
With Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields("PROVINCIA")
.Orientation = xlRowField
.Position = 1
End With
'AÑADIMOS EL CAMPO CUENTA DE POBLACIÓN AL INFORME AGRUPADO
Sheets("AGRUPADO").PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica1").PivotFields("POBLACION"), "Cuenta de POBLACION", xlCount
'DEFINIMOS LA EDAD MÍNIMO COMO EL ITEM MÁS BAJO DESDE EL QUE AGRUPAMOS
Minimo = Application.WorksheetFunction.Min(Worksheets("DATOS").Range("B:B"))
'AGRUPAMOS LOS DATOS EN TRAMOS DE 10 AÑOS EMPEZANDO POR EL MÍNIMO (18 AÑOS EN ESTE CASO)
Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotSelect Minimo, xlDataAndLabel + xlFirstRow, True
Selection.Group Start:=True, End:=True, By:=10
Application.ScreenUpdating = True
End Sub

Con este código vamos lograr agrupar los datos de la forma que necesitamos, sin embargo me gustaría comentar algunos detalles que me parecen importantes:

  • El origen de los datos, SourceData:= “DATOS!R1C1:R1048576” & “C3“, que escribo de esta forma cuando lo normal es que utilizase una variable para hacer referencia a los 1.048.576 registros. El motivo de no hacerlo es que en el momento en el que la variable hace referencia a más de 99.998 registros arroja un error de referencia en los datos, e impide continuar con el proceso. Por eso debemos detallar todo el rango, lo que implica que el código recorra todas las filas, y aunque en este ejemplo está correcto porque usamos toda la hoja, en caso de que tuviésemos 200.000 registros, estaríamos obligados, o bien a escribir los 200.000 registros en rango, algo que no es viable si nuestros datos varían o indicar el final de la hoja como el fin del rango, incrementando recursos y tiempo de ejecución en la macro. Con una variable detectaría los 200.000 y se ejecutaría hasta ese número.
  • En una agrupación siempre debemos definir el rango con el que vamos a agrupar, en este caso, desde los 18 años a los 95 años. Pues bien, en el código quedaría así:

   Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotSelect ("'18'")

  • De forma que estaríamos obligados a cada vez que cambien las edades en nuestra base de datos y por ejemplo no existiese personas de 18 años, la macro arrojaría un error. Por ello, debemos establecer que la edad inicial es la menor de las edades:

    Minimo = Application.WorksheetFunction.Min(Worksheets("DATOS").Range("B:B"))

  • Ahora sabemos que la menor de las edades es 18 años y ya podemos indicarlo así en la macro:

    Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotSelect Minimo

  • Los tramos de edad que vamos agrupar para este ejemplo es de 10 en 10 años, para establecerlo, lo podremos hacer en la siguiente línea de código:
    Group Start:=True, End:=True, By:=10
    Se puede indicar el tramo que mejor se ajuste a nuestras necesidades.

Una vez ejecutada la macro, el resultado sería el descrito en la primera imagen para todas las provincias:

AGRUPAR EN TRAMOS CON VBA_2

Aprovechando que tenemos ese informe, se me ocurre que quizás alguien nos pueda pedir que marquemos en cada provincia el tramo de edad con más población. Y además nos sugieran que sería ideal marcar los datos en rojo.

Podemos hacerlo de diferentes formas, pero teniendo en cuenta que estamos trabajando con tablas dinámicas, creo que debemos utilizar todos los recursos que tenemos a nuestro alcance con esta herramienta.
Os dejo el código utilizado y luego lo explico:

Sub MARCAR()
'DECLARAMOS VARIABLES QUE VAMOS A UTILIZAR
Dim i As Double
final = Application.CountA(Worksheets("AGRUPADO").Range("a:a"))
Application.ScreenUpdating = False
'INCLUIMOS UN CONDICIONAL PARA BORRAR LOS DATOS MARCADOS CUANDO ACTUALICEMOS
Sheets("AGRUPADO").Select
If Sheets("AGRUPADO").Range("C1") <> Empty Then
Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields("Cuenta de POBLACION2"). _
Orientation = xlHidden
End If
If Sheets("AGRUPADO").Range("a1") = Empty Then
MsgBox ("DEBES GENERAR LOS DATOS AGRUPADOS ANTES DE GENERAR EL RANKING")
Exit Sub
End If

'DUPLICAMOS EL CAMPO POBLACIÓN Y LO DENOMINAMOS POBLACIÓN2
Sheets("AGRUPADO").PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables  ("Tabla dinámica1").PivotFields("POBLACION"), "Cuenta de POBLACION2", xlCount
'SOBRE EL CAMPO POBLACIÓN2 MOSTRAMOS LOS VALORES COMO RANKING
With Worksheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields( "Cuenta de POBLACION2")
.Calculation = xlRankDecending
.BaseField = "EDAD"
End With
'SI EL VALOR ES IGUAL A 1 (MAYOR) LO MARCAMOS EN ROJO
For i = 2 To final
If Sheets("AGRUPADO").Cells(i, 3) = 1 Then
With Sheets("AGRUPADO")
.Cells(i, 3).Interior.Color = vbRed
.Cells(i, 2).Interior.Color = vbRed
.Cells(i, 1).Interior.Color = vbRed
End With
End If
Next
Application.ScreenUpdating = True
End Sub

Para empezar, lo que debemos hacer es incluir de nuevo el mismo campo “POBLACIONES en la tabla dinámica, pero que vamos a llamar POBLACIONES2 (no pueden existir dos campos con el mismo nombre):

Sheets("AGRUPADO").PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables  ("Tabla dinámica1").PivotFields("POBLACION"), "Cuenta de POBLACION2", xlCount

AGRUPAR EN TRAMOS CON VBA_3

El siguiente paso sería, sobre ese nuevo campo “Poblaciones2” mostrar los valores que cuentan a las personas, como un ranking, donde el 1 sería el valor más alto, es decir, el tramo donde la población es más elevada:

With Worksheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields( "Cuenta de POBLACION2")
.Calculation = xlRankDecending
.BaseField = "EDAD"
End With

AGRUPAR EN TRAMOS CON VBA_4

Una vez que contamos con este dato, solo tendríamos que marcar todos los valores “1” en rojo y destacar así la información que nos están solicitando:

For i = 2 To final
If Sheets("AGRUPADO").Cells(i, 3) = 1 Then
With Sheets("AGRUPADO")
.Cells(i, 3).Interior.Color = vbRed
.Cells(i, 2).Interior.Color = vbRed
.Cells(i, 1).Interior.Color = vbRed
End With
End If

AGRUPAR EN TRAMOS CON VBA_5

 

Y este sería nuestro informe final, datos agrupados por edad y provincia y destacando el tramo de edad en cada provincia con más población.

Reflexionando, a pesar de la restricción de los registros es de 1.048.576 en Excel, es una excelente herramienta para procesar grandes cantidades de datos. Sin embargo, cuando hablamos de Big Data, hacemos referencia a millones de datos y actualmente Excel no resulta viable como alternativa para tratar ese tipo de informaciones, teniendo que acudir a otros lenguajes de programación y plataformas para realizar el trabajo.

No obstante, como complemento de análisis, ya sea de resultados resumidos o porciones asumibles, es un excelente complemento para el Gran Dato 🙂

Como siempre os dejo el archivo para facilitar su consulta y las pruebas.

Importante: Solo válido para versiones superiores a Excel 2003

Descarga el archivo de ejemplo pulsando en: AGRUPAR EN TRAMOS CON VBA

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.

TRANSPONER DATOS PERSONALIZADOS_1

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:

TRANSPONER DATOS PERSONALIZADOS_2

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:

TRANSPONER DATOS PERSONALIZADOS_3

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:R1″ & 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:R1" & 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")
.PivotItems("(blank)").Visible = False
.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

 

INSERTAR CAMPO CALCULADO EN UNA TABLA DINAMICA

Como sabéis, el uso de las tablas dinámicas nos ofrece la solución a muchos problemas relacionados con la exposición y la agrupación de datos. Sin embargo, en muchas ocasiones los datos que ofrecen no son suficientes, o simplemente, nuestro jefe ha decidido que necesita obtener un dato adicional al que le hemos mostrado.

Para ese tipo de tareas existe un funcionalidad en la cinta de opciones que se despliega cuando insertamos una tabla dinámica denominada “Campos, elementos y conjuntos” y en concreto la opción “Insertar campo calculado”.

Para poner un ejemplo utilizaré los datos poblacionales de las provincias españolas que todos podéis descargar a través del INE. Imaginad que a partir de los datos por municipios del total de hombres y mujeres, hemos construido una tabla dinámica agrupando totales por provincia, así:

INSERTAR CAMPO CALCULADO EN TABLA DINAMICA_1

Pero una vez finalizado el trabajo nos indican que es necesario agregar tres campos más, suma de hombres y mujeres, % de hombres y % de mujeres.

Para hacerlo tenemos dos opciones, o bien nos vamos a los datos iniciales y creamos una columna con el sumatorio y otras dos con los porcentajes o bien lo hacemos directamente a partir de la tabla dinámica. Vamos a probar con la segunda opción.

En herramientas de tabla dinámica, nos vamos a la pestaña de Opciones y pulsamos en “Campos, elementos y conjuntos” y seguidamente en “Insertar campo calculado“. A continuación se nos va a abrir una ventana en la que debemos especificar el nombre del nuevo campo y la fórmula a aplicar, es decir “Población Total” como nuevo campo y en fórmula seleccionamos el campo “Varones” insertamos, luego ponemos un + para sumar y seleccionamos el campo “Mujeres” e insertamos:

INSERTAR CAMPO CALCULADO EN TABLA DINAMICA_2

Una vez que tenemos todo correctamente seleccionado, pulsamos en aceptar y ya tenemos un nuevo campo en la tabla dinámica:

INSERTAR CAMPO CALCULADO EN TABLA DINAMICA_3

Ahora nos queda hacer lo mismo pero para obtener el porcentaje de hombres y mujeres, esto lo vamos a conseguir dividiendo el campo “Varones” o “Mujeres” entre el “Población Total”:

INSERTAR CAMPO CALCULADO EN TABLA DINAMICA_4

Y para finalizar, aplicamos formato de % a los nuevos datos obtenidos. El resultado será este:

INSERTAR CAMPO CALCULADO EN TABLA DINAMICA_5

Y esa ha sido la entrada de hoy, algo sencillo de hacer, pero que tiene la ventaja de que permite modificar la tabla dinámica y que los datos siempre se actualizarán a pesar de que los datos de la tabla base no tengan esos campos calculados.

Descarga el archivo pulsando enINSERTAR CAMPO CALCULADO EN TABLA DINÁMICA

 

AGRUPAR EN TRAMOS CON TABLA DINAMICA

Dado el poco tiempo que tengo últimamente, hoy voy a subir una entrada un poco más corta y sencilla (pero no deja de ser importante). Se trata del uso del comando Agrupar que encontramos en la cinta de opciones de cuando usamos una tabla dinámica.

Esta herramienta nos puede resultar muy útil cuando necesitamos generar información por tramos, ya sea cifras de negocios, ventas, edad … etc.

Para el ejemplo de hoy voy a usar una lista de edad que pretendo agrupar por tramos de quince en quince años. Para poder hacerlo es necesario tener un listado con edades, en el ejemplo he generado aleatoriamente en 1000 celdas números comprendidos entre 25 y 85 años. Ya sabéis la fórmula es la siguiente y está disponible en Excel desde la versión 2007 en versiones anteriores no funciona:

=ALEATORIO.ENTRE(valor inferior; valor superior)

Este dato yo lo estoy generando porque no tengo una base de datos con información para hacerlo, este paso os lo saltaréis dado que ya contaréis vosotros mismos con la información que os interese agrupar por tramos.

Una vez tenemos el listado, insertamos la tabla dinámica:

TRAMOS CON TABLA DINAMICA
Con la tabla dinámica generada, vamos a mostrar en etiqueta de fila  las edades y también en valores (resumiendo el campo de valores contando los elementos):

TRAMOS CON TABLA DINAMICA_1

Una vez que ya tenemos la tabla con los elemento correctamente introducidos, tan solo tenemos que ir al comando “Agrupar”, situado en la pestaña y se desplegará un cuadro de diálogo para que podamos agrupar la información según nos convenga. En este caso quiero mostrar las edades entre 0 y 85 años y que los tramos sean de 15 en 15 años.

TRAMOS CON TABLA DINAMICA_2

El resultado es este 🙂

TRAMOS CON TABLA DINAMICA_3
Una sencilla forma de resumir datos en vuestros registro de objetivos y en los informes que tengáis que presentar.

Descarga el archivo pulsando enAGRUPAR EN TRAMOS CON TABLA DINÁMICA