Cómo estamos todos.
Hoy quiero mostrar un código de VBA que te pueda ayudar a manipular una tabla dinámica de una manera rápida e inofensiva :)
Empezemos!!!
Imaginemos que tenemos una tabla como ésta!
Y creamos una tabla dinámica simple a partir de este conjunto de datos. El producto en Filas y la Ventas en Valores. Nada sofisticado hasta ahora.
1 - Creamos una tabla de cálculos Dummy
Al lado de la tabla dinámica se crea una pequeña tabla con los siguientes datos
La columna Cálculo representa el tipo de cálculo y hay un número de serie para cada uno.
2 - Creamos una tabla dinámica con nuestra tabla Dummy
Pulsamos el botón derecho en Cálculo y optamos por Agregar como segmentación de datos.
En caso que aún no se comprenda el propósito de crear la tabla de Calc Dummy y el Slicer sigue leyendo:
La tabla de Cálculo Dummy -
- Es un tabla dummy que apoyará algunos cálculos adicionales y
- También se puede ampliar para que contenga más cálculos (como desviación estándar, varianza, recuento diferenciado, etc.) otros que los mencionados
- Slicer: es sólo una herramienta de selección dinámica para el cálculo que queremos realizar
Nuestra siguiente tarea es conectar el Slicer con los cálculos de la tabla dinámica.
3 - 2 cálculos más simulados ...
La primera lo hacemos mediante la funciones IZQUIERDA y HALLAR
La fórmula empleada es : =IZQUIERDA(B3,HALLAR(" ",B3,1)-1)
La funciones IZQUIERDA y HALLAR se utilizan para extraer la primera palabra de nuestra primera tabla dinámica. Esto nos dirá qué cálculo se realiza actualmente en la tabla dinámica.
Y para la segunda necesitamos usar la función BUSCARV para obtener el número de serie correspondiente al cálculo realizado.
Se ha de tener en cuenta que nuestro slicer está conectado a la tabla dinámica, por lo que cuando cambiemos la selección del Slicer, las etiquetas de fila se actualizarán y el valor del BUSCARV (número de serie) también se actualizará.
Y por último se nombra las celdas ..
La denominación de las celdas es importante porque nos referiremos a estas celdas en el código de VBA no con sus direcciones de celda, sino por sus nombres. Esto hace que el código sea un poco más robusto.
4 - Tiempo para el código VBA
Aquí está el pequeño código que lo une todo. Se puede personalizar cambiando el texto en azul.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Sel")) Is Nothing Then
Dim Pvt As PivotTable
Dim Pf As PivotField
Dim Sh As Worksheet
Set Sh = ActiveSheet
Set Pvt = Sh.PivotTables("TablaDin1")
If Range("Sel") = Range("Tipo.calc") Then
Exit Sub
Else
For Each Pf In Pvt.DataFields
Select Case Range("Num.serie")
Case 1
Pf.Function = xlSum
Case 2
Pf.Function = xlCount
Case 3
Pf.Function = xlAverage
Case 4
Pf.Function = xlMax
Case 5
Pf.Function = xlMin
End Select
Next Pf
End If
End If
End Sub
If Not Intersect(Target, Range("Sel")) Is Nothing Then
Dim Pvt As PivotTable
Dim Pf As PivotField
Dim Sh As Worksheet
Set Sh = ActiveSheet
Set Pvt = Sh.PivotTables("TablaDin1")
If Range("Sel") = Range("Tipo.calc") Then
Exit Sub
Else
For Each Pf In Pvt.DataFields
Select Case Range("Num.serie")
Case 1
Pf.Function = xlSum
Case 2
Pf.Function = xlCount
Case 3
Pf.Function = xlAverage
Case 4
Pf.Function = xlMax
Case 5
Pf.Function = xlMin
End Select
Next Pf
End If
End If
End Sub
Para usar este código ...
- Se copia el código entero
- Use ALT+F11 para abrir la ventana de VBA
- En el libro de trabajo
- Se hace doble clic en la Hoja 1 (o en cualquier hoja en la que esté trabajando)
- Y se pega el código allí!
- Ahora se cierra la ventana de VBA
No hay que olvidarse de guardar el archivo en formato xlsm (habilitado para macros)
Ahora cuando se juega con el Slicer, la tabla dinámica se actualiza automáticamente!
Te ha gustado el artículo?
No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:
1. Puedes dejar una donación pinchando en la siguiente imagen.
2. Puedes hacer clic en los anuncios mostrados en la página
3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.
Quieres aprender más en Excel o tienes algún problema que necesita solución?
Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.
El Sabio de Excel busca colaborades del blog
Si te interesa compartir tu conocimiento sobre Excel de tal manera que puedas ayudar a otras personas, por favor mándame un email a elsabiodeexcel@gmail.com
Subscríbete y recibe todos las entradas por email
Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.
Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.
No hay comentarios:
Publicar un comentario