Una frustración de larga data con las tablas dinámicas en Microsoft Excel es que cualquier campo que contenga números siempre debe ser formateado manualmente para añadir comas y/o eliminar los decimales. Además, los cambios de formato deben hacerse en un campo a la vez para asegurar que los cambios posteriores no hagan que el formato de los números desaparezca cuando se actualiza la tabla pivote.
En este artículo mostraré cómo una sola línea de código de programación puede hacer que Excel formatee automáticamente los números a medida que se añaden nuevos campos a una tabla pivote.Primero vamos a crear una tabla dinámica, como se muestra en la figura 1. Los datos subyacentes incluyen tres columnas: Producto , Casos Vendidos , y Ventas Totales . Para crear una tabla pivotante:
- Haga clic en cualquier celda dentro de sus datos de origen. Asegúrate de que cada columna tenga un título único que aparezca dentro de una sola celda.
- Selecciona el menú Insertar de Excel.
- Haz clic en el comando PivotTable .
- Haz clic en OK dentro del cuadro de diálogo Create PivotTable .
- Aparecerá una nueva hoja de trabajo con una tabla pivotante en blanco.
Figura 1: El comando PivotTable aparece en el menú de Excel Insert .
El siguiente paso es añadir campos a la tabla pivotante:
- Seleccione Producto .
- Seleccione Casos Vendidos . Fíjate en que este campo no tiene ningún formato de número.
- Haga clic con el botón derecho del ratón en Suma de los casos vendidos .
- Elija Formato de número . Si eliges Formato de celdas sólo darás formato a una sola celda, en lugar de a toda la columna.
- Elija Número .
- Opcionalmente establezca el número de decimales a 0 .
- Marque la casilla de verificación Use 1000 Separator .
- Haga clic en OK .
- Los números de la columna Suma de los casos vendidos están ahora formateados.
Figura 2: Por defecto, las tablas pivotantes no tienen ningún formato de número.
Llevar a cabo estos pasos cada vez que se añade un nuevo campo a una tabla pivotante puede ser muy entretenido, así que en su lugar automatizaremos esto por medio de una macro en Excel. El primer paso es determinar si tienes un Libro de Trabajo de Macro Personal . Para ello, determinar si el comando Unhide está deshabilitado en el menú View de Excel. Si es así, realiza estos pasos:
- Selecciona el menú de Excel Ver .
- Haz clic en la mitad inferior del botón Macros .
- Clic Macro de grabación .
- Selecciona Cuaderno de trabajo de macros personales en el menú desplegable.
- Haz clic en OK en el cuadro de diálogo Record Macro . Para ello no es necesario realizar ningún cambio.
- Haga clic en el botón Stop Recording , que será un cuadrado junto a la palabra Ready en la parte inferior de la pantalla de Excel.
Figura 3: Enciende y apaga la grabadora de macros para crear un libro de trabajo personal de macros.
Las macros que se colocan dentro del Personal Macro Workbook están disponibles para su uso en cualquier libro de trabajo de Excel, por lo que pueden convertirse en extensiones del propio Excel. El siguiente paso es crear el macro en sí, como se muestra en la Figura 4:
- Elige el menú de Excel Ver .
- Haz clic en el comando Deshacer .
- Haga clic en OK para desocultar el libro de trabajo PERSONAL.XLSB .
- Activa el menú de Excel Ver de nuevo.
- Haz clic en la mitad superior del comando Macros .
- Escriba un nombre para su macro, como PivotTableNumberFormat . El primer carácter debe ser una letra y no puede tener espacios.
- Haz clic en el botón Crear .
Figura 4: Las macros dentro del Cuaderno de trabajo de macros personales pueden ser utilizadas dentro de cualquier otro cuaderno de trabajo de Excel.
En este punto el Editor Visual Basic aparecerá en pantalla. Copia y pega estas líneas de código en el macro, como se muestra en la Figura 5:
En Error Reanudar Siguiente
ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).DataBodyRange.NumberFormat = «#,##0;(#,##0)»
Sobre el error Ir a 0
Figura 5: Una línea de código de programación establecerá una tabla pivotante para formatear automáticamente las nuevas columnas que añadas.
Si eliges escribirlo a mano, el último carácter en el error GoTo 0 es un cero . Ambas líneas On Error están relacionadas con una instrucción a Excel para saltar el código de programación que falla; por ejemplo, si se ejecuta esta macro cuando el cursor no está dentro de una tabla pivote.
En este punto puedes cerrar el Visual Basic Editor , que te devolverá a Excel. El Personal Macro Workbook debería seguir apareciendo en la pantalla. Haz clic en el comando Ocultar del menú Ver de Excel para ocultar este libro de trabajo, que se inicia automáticamente al abrir Excel, pero permanece oculto visualmente para no estorbar.
Los siguientes pasos consisten en añadir un icono a la barra de herramientas de acceso rápido para que puedas ejecutar la macro con un solo clic:
- Haz clic en la flecha que se encuentra al final de la barra de herramientas de acceso rápido .
- Elija Más Comandos .
- Elija Macros de la lista Elija Comandos de .
- Haga doble clic en su macro, que tendrá un nombre como PERSONAL.XLSB!PivotTableNumberFormat .
- Opcional: CIick Modificar para cambiar el icono asociado a este comando, así como la leyenda.
- Haga clic en OK .
Figura 6: Puedes ejecutar un macro con un solo clic del ratón cuando lo asignas a un icono de la barra de herramientas de acceso rápido .
Para probar tu trabajo, primero borra el formato del número de la columna Suma de casos vendidos . Una forma de hacerlo es seleccionar los números y luego elegir General de la lista de la sección Número del menú Inicio de Excel. Como se muestra en la Figura 7:
- Haz clic en cualquier celda de la tabla pivotante.
- Haz clic en el icono que has añadido a la barra de herramientas de acceso rápido .
- Los números de la columna Suma de los casos vendidos deben ser formateados de nuevo.
- Seleccione Ventas Totales del campo Tabla Pivote .
- La nueva columna Suma de las ventas totales debería aparecer en su tabla pivote y ser formateada automáticamente.
Figura 7: El macro pone una tabla pivote en un estado en el que a los nuevos campos se les asigna automáticamente un formato de número.
Debes ejecutar esta macro en cada tabla dinámica que crees, pero una vez que añadas cualquier campo numérico, se formateará automáticamente. Si deseas incluir lugares decimales, cambia el código de formato de la macro a » #,##0.00;(#,##0.00)» o usa » $#,##0;($#,##0)» si deseas tener signos de dólar sin lugares decimales.
Cuando cierres Excel asegúrate de hacer clic en Sí o Guardar cuando se te pregunte si deseas guardar los cambios que has hecho en el Cuaderno de trabajo de macros personales . Si desea eliminar el icono de la Barra de Herramientas de Acceso Rápido que ha creado, haga clic con el botón derecho del ratón en el icono y elija Eliminar de la Barra de Herramientas .