Administrar tablas dinámicas y gráficos de Excel con VBA | de Himalaya Bir Shrestha | junio de 2024

Porautomata

#Administrar, #Bir, #con, #dinámicas, #Excel, #gráficos, #Himalaya, #junio, #Shrestha, #tablas, #VBA

Datos

Tomé los datos de muestra del sitio web de Excel-Fácil. Este es un buen sitio web que contiene tutoriales para principiantes sobre las funcionalidades básicas de Excel y VBA. El conjunto de datos contiene registros de ventas de frutas y verduras en países específicos en 2016 y 2017. El conjunto de datos contiene seis campos: ID de pedido, Producto, Categoría, Importe, Fecha y País. Dividí este conjunto de datos en 2 archivos csv llamados results1.csv y results2.csv para crear tablas dinámicas.

Saqué este conjunto de datos en el RawData hoja de cálculo. Creé un rango dinámico llamado raw_data_source con la siguiente fórmula:

=DESPLAZAMIENTO(¡Datos sin procesar!$A$1, 0, 0, CONTARA(¡Datos sin procesar!$A:$A), CONTARA(¡Datos sin procesar!$1:$1))

El motivo para crear un rango dinámico fue que el tamaño (número de filas) del conjunto de datos era diferente en diferentes archivos CSV y quería usar todo el conjunto de datos como fuente de la tabla dinámica.

Crear un rango dinámico con nombre como fuente para la tabla dinámica. Ilustración del autor.

Tablas dinámicas y gráficos

El conjunto de datos en el raw_data_source rango en el RawData La hoja se utilizó como fuente de datos para crear dos tablas dinámicas y gráficos simples.

Especificación del origen de la tabla dinámica. Ilustración del autor.

El primero mostraba las ventas totales de frutas y verduras por productos individuales filtrables por país con la configuración de campo como se muestra.

Primer gráfico dinámico y su configuración. Ilustración del autor.

El segundo retrataba las ventas totales de frutas y hortalizas por países.

Segundo gráfico dinámico y su configuración. Ilustración del autor.

1. Automatizar la actualización de tablas dinámicas basadas en un nuevo conjunto de datos

En este paso, quería automatizar la actualización de los conjuntos de datos desde el archivo que especifiqué. Creé un marcador de posición en el Admin hoja para colocar la ruta de los datos CSV cuyo conjunto de datos quería extraer y actualizar las tablas dinámicas. Le puse nombre a la celda A2 como filepath Como se muestra abajo:

Marcador de posición para la ruta del archivo con conjunto de datos. Ilustración del autor.

El código para este paso se proporciona a continuación en el UpdateRawData subrutina. Declaré las variables wb, ws_admin, ws_rawdatay filepath para los nombres del libro de trabajo, la hoja de administración, la hoja RawData y la ruta del archivo que contiene el conjunto de datos, respectivamente. Primero limpié el contenido de la hoja RawData. Luego fui al libro de origen según el nombre del archivo, seleccioné la hoja correspondiente, copié su contenido y regresé a la página. ws_rawdata hoja y pegué el contenido como valores. Finalmente, actualicé el libro usando el código wb.RefreshAll que actualizó todo el libro, incluidas las hojas con la tabla dinámica y el gráfico respectivamente.

Sub UpdateRawData()Dim wb As Workbook
Dim ws_admin As Worksheet
Dim ws_rawdata As Worksheet
Dim filepath As String
Set wb = ThisWorkbook
Set ws_admin = wb.Worksheets(“Admin”)
Set ws_rawdata = wb.Worksheets(“RawData”)
'Clear Rawdata sheet
ws_rawdata.Activate
Cells.Clear
'get file path
filepath = ws_admin.Range(“filepath”)
Application.DisplayAlerts = False
'Open source file and select all contents
Dim src_wb As Workbook
Dim src_ws As Worksheet
Set src_wb = Workbooks.Open(filepath)
Set src_ws = src_wb.Sheets(1)
src_ws.UsedRange.Select
'Copy all
Selection.Copy
'Paste all
ws_rawdata.Range(“A1”).PasteSpecial xlPasteValues
'Close source file
src_wb.Close SaveChanges:=False
wb.RefreshAll
Application.DisplayAlerts = True
End Sub

Vinculé esta subrutina a la Actualizar archivos y gráficos botón. Una vez que especifiqué el nombre del archivo en el marcador de posición e hice clic en el botón, el conjunto de datos y las tablas dinámicas se actualizaron automáticamente.

2. Creando una tabla de colores usando VBA

Creé una tabla en el Admin hoja que contiene la lista de frutas y verduras específicas disponibles en el conjunto de datos. En la columna F, especifiqué los códigos de color hexadecimales provisionales para el color de cada fruta o verdura. Quería usar estos colores para actualizar los colores en los gráficos dinámicos. Primero, quería pintar la columna F con el color que especifiqué en cada celda.

Crear una lista de frutas y verduras disponibles en el conjunto de datos junto con sus correspondientes códigos de color hexadecimales. Ilustración del autor.

Código de color hexadecimal

El código hexadecimal para cada color es un número hexadecimal de 6 dígitos (0 a 9 o A a F) con base 16. En un código de color hexadecimal RRGGBB, cada par de dos letras representa los distintos tonos de rojo, verde y azul. En un sistema Rojo Verde Azul (RGB), el valor de cada tono varía de 0 a 255.

Por ejemplo, para un código de color hexadecimal ffab23Calculé el código RGB correspondiente con el siguiente cálculo. ffab23 en el sistema hexadecimal se traduce como (255, 171, 35) en el sistema RGB, refiriéndose a los componentes de color rojo, verde y azul respectivamente.

Calcular el código RGB para un código de color hexadecimal ffab23 manualmente. Ilustración del autor.

Esto también se puede visualizar yendo a la opción Colores personalizados en Excel como se muestra a continuación:

Demostración de los componentes RGB y el color real para el código de color hexadecimal ffab23. Ilustración del autor.

En Excel VBA, usando &H en combinación con un valor implica que es un número hexadecimal y el Val() La función devuelve el número decimal correspondiente. En la ventana Inmediato a continuación, r, gy b representan los valores decimales correspondientes para cada tono de rojo, verde y azul respectivamente.

Ventana inmediata que muestra cómo se derivan los valores decimales correspondientes en VBA a partir del código hexadecimal. Ilustración del autor.

En el siguiente código, creé un rango con nombre color_code_range para la tabla que contiene el código de color hexadecimal para cada fruta o verdura. Recorrí cada celda de la selección, deduje los componentes rojo, verde y azul en números decimales y pinté el interior de la celda con el mismo código de color RGB.

Sub refresh_color_table()Dim wb As Workbook
Dim ws_admin As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets(“Admin”)
ws_admin.Range("color_code_range").Select
Dim c As Range
Dim r, g, b As Long
Dim Hex As String
For Each c In Selection
Hex = c.Value
r = Val("&H" & Mid(Hex, 1, 2))
g = Val("&H" & Mid(Hex, 3, 2))
b = Val("&H" & Mid(Hex, 5, 2))
c.Interior.Color = RGB(r, g, b)
Next c
End Sub

Cuando ejecuté la subrutina anterior, la columna F se pinta con el mismo color que el código de color como se muestra:

Pintar las celdas con el código de color mencionado en ellas. Ilustración del autor.

Si se cambia el código de color y se ejecuta nuevamente, generará nuevos colores en la tabla. ¿No es genial?

3. Trabajar con un diccionario en VBA

En el siguiente paso, quería asignar los colores en los gráficos dinámicos según los colores personalizados que elegí anteriormente. Para ello, creé un diccionario que contiene el nombre del producto como claves y los códigos de color hexadecimales correspondientes como valores.

El requisito previo para crear un objeto de diccionario en VBA es activar previamente Microsoft Scripting Runtime. Para esto, puede ir a Herramientas -> Referencias -> Marque la casilla junto a Microsoft Scripting Runtime y haga clic en Aceptar.

Requisito previo para trabajar con un diccionario en Excel VBA. Ilustración del autor.

En el siguiente código, creé un objeto de diccionario llamado colorMap. Recorrí el rango E2:F10 en el Admin hoja. Agregué el contenido de la columna E como claves y el contenido de la columna F como sus valores correspondientes.

Sub create_dict()Dim wb As Workbook
Dim ws_admin As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets(“Admin”)
Dim colorMap As Dictionary
Set colorMap = New Dictionary
Dim i As Integer
For i = 2 To 10
If Not colorMap.Exists(Range("E" & i).Value) Then
colorMap.Add Range("E" & i).Value, Range("F" & i).Value
End If
Next i
For Each Key In colorMap.Keys()
Debug.Print Key & ": " & colorMap(Key)
Next Key
End Sub

En el segundo bucle for anterior, recorrí cada clave del diccionario colorMap e imprimí las claves y los valores en la ventana inmediata como se muestra a continuación:

Imprimir el contenido del objeto del diccionario colorMap. Ilustración del autor.

4. Administrar elementos del gráfico dinámico usando VBA

Según lo aprendido en los pasos anteriores, quería ir un paso más allá y actualizar los elementos del gráfico dinámico usando VBA. En este caso, quería establecer el título del gráfico automáticamente en función del valor de una celda y aplicar los colores de frutas y verduras específicas definidas en el Admin hoja a los gráficos dinámicos.

En este paso, asigné Plot1 y Plot2 hojas como una matriz llamada sheetNames. yo declaré chartObj como ChartObject. Dentro de cada hoja, recorrí cada ChartObject entre todos los ChartObjects.

Nota: Objeto gráfico actúa como contenedor para un Cuadro Objeto en VBA que controla el tamaño y la apariencia del gráfico incrustado en una hoja de trabajo. Es miembro de la Objetos de gráfico recopilación. Es importante comprender las diferencias en los métodos y propiedades de cada uno de estos objetos en VBA.

Después de revisar cada chartObj, configuro el título de cada gráfico según el valor de la celda E1. A continuación, recorrí cada serie de la colección de series general del objeto Gráfico. Asigné el nombre de la serie (es decir, el nombre de la fruta o verdura) a una variable llamada itemNamey obtuvo el código de color correspondiente del colorMap diccionario. De manera similar al paso 2, obtuve los componentes rojo, verde y azul del código de color en números decimales y llené la barra de serie con los colores RGB.

Sub refresh_plots()Dim wb As Workbook
Dim ws_admin As Worksheet
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets("Admin")
Dim colorMap
Set colorMap = CreateObject("Scripting.Dictionary")
Dim i As Integer
For i = 2 To 10
If Not colorMap.Exists(Range("E" & i).Value) Then
colorMap.Add Range("E" & i).Value, Range("F" & i).Value
End If
Next i
Dim sheetNames As Variant
Dim sheetName As Variant
sheetNames = Array("Plot1", "Plot2")
Dim hex_color_code As String
Dim r, g, b As Integer
Dim chartObj As ChartObject
For Each sheetName In sheetNames
Set ws = wb.Sheets(sheetName)
For Each chartObj In ws.ChartObjects
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = ws.Range("E1").Value
For Each Series In chartObj.Chart.SeriesCollection
itemName = Series.Name
hex_color_code = colorMap(itemName)
r = Val("&H" & Mid(hex_color_code, 1, 2))
g = Val("&H" & Mid(hex_color_code, 3, 2))
b = Val("&H" & Mid(hex_color_code, 5, 2))
Series.Format.Fill.ForeColor.RGB = RGB(r, g, b)
Next Series
Next chartObj
Next sheetName
End Sub

A continuación se muestra una ilustración del uso de este código.

Transformación de elementos del gráfico dinámico utilizando el código VBA. Ilustración del autor.

Conclusión

En esta publicación, ilustré cómo se puede personalizar y automatizar el trabajo con tablas dinámicas y gráficos usando VBA. He demostrado la automatización de cuatro tareas clave: actualizar tablas dinámicas y gráficos con nuevos conjuntos de datos; crear tablas de colores basadas en códigos de colores hexadecimales; cómo trabajar con diccionarios en VBA; y gestionar y actualizar elementos del gráfico dinámico utilizando VBA. En el segundo paso, elaboré la conversión de códigos de color hexadecimales a los códigos de color RGB correspondientes utilizando Excel y VBA, y utilicé esta técnica en los pasos siguientes.

El código y el archivo Excel para esta publicación están presentes en este GitHub. repositorio. ¡Gracias por leer!