#ExcelPedroWave

🔝To translate this blog post to your language, select it in the top left Google box.

Cómo crear un calendario con una tabla dinámica

En este blog he publicado bastantes calendarios que se pueden ver en el siguiente enlace:

• calendario | #ExcelPedroWave

He buscado en la Web algún calendario basado en tablas dinámicas y no he encontrado casi nada. El calendario más parecido que he visto ha sido el del siguiente enlace, pero necesita demasiados campos en la tabla de origen de datos para generar la tabla dinámica.

• EXCEL UNIVERSITY - PivotTable Calendar 

Me he propuesto hacerlo con sólo 3 campos como origen de datos y que sea la tabla dinámica la que elabore el calendario, siendo éste el aspecto del nuevo calendario dinámico:

Con dos segmentaciones de datos, una para los años y otra para los meses, se puede configurar dinámicamente el calendario perpetuo, abarcando los siglos XX y XXI del calendario gregoriano.

En este vídeo puedes seguir paso a paso las explicaciones de cómo crear el calendario dinámico:

Aumenta el volumen para escuchar las explicaciones, pues la grabación de audio está a un nivel muy bajo. Disculpa las molestias. Para ayudar a seguir la explicación paso a paso he usado un cuadro de texto que muestra cada uno de los pasos, lo que espero sirva de ayuda para que puedas reproducir las explicaciones y consigas hacer tú mismo el calendario con una tabla dinámica, que es lo que pretendo con este artículo y el vídeo que le acompaña.

Paso a paso de cómo crear el calendario

Hay que rellenar la columna de Fecha con una secuencia desde el 01-01-1901, día de comienzo del siglo XX. El año 1900 es bisiesto erróneamente en Excel, por lo que no será incluido en el calendario.

Una forma elegante de rellenar las fechas es usando la función:

=SECUENCIA("01-01-2101"-"01-01-1901";1;"01-01-1901")

ATENCIÓN: Esta función sólo sirve en las últimas versiones de Excel 365; Web; iPad; iPhone y Android.

Por compatibilidad con versiones desde Excel 2010, es mejor introducir el primer día en la celda A2 y los sucesivos calcularlos con la fórmula: =A2+1

Se trata de rellenar todas las fechas de los siglos XX y XXI.

Son un total de 73.049 días obtenidos con la fórmula: ="01-01-2101"-"01-01-1901"

ATENCIÓN: En una hoja la máxima fecha sería el 24-11-4770: =FILA(A1048576)-1

Esta tabla de fechas será el origen de datos de la tabla dinámica que se creará en la hoja 'Calendario'.

Descarga esta plantilla vacía con los pasos a seguir desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive.

Activa las macros y haz clic en la caja de texto.

Pasos a seguir ya explicados en el vídeo

1. Comienza en una nueva hoja, cambiando su nombre a: Fechas
2. Ponla en color amarillo.
3. Edita la tabla de fechas:
4. En A1 escribe la cabecera: Fecha; Día Semana; Núm. Semana
5. En A2 pon el día 01-01-1901
6. En A3 escribe la fórmula: =A2+1
7. En el menú Vista: Desmarca la Línea de cuadrícula
8. Inmoviliza la fila superior
9. Inserta la tabla con cabecera: Edita su nombre: TablaFechas
10. Cambia su tamaño a: 73050 filas
11. Cambia su formato a: Fecha corta
12. ATENCIÓN: Comprueba que se ha rellenado hasta el último día: 31-12-2100
13. Calcula el día de la semana en B2: =DIASEM([@Fecha];2)
14. NOTA: El segundo argumento indica con un 2 que la semana comienza en lunes (1) y acaba en domingo (7).
15. Calcula el número de semana en C2: =NUM.DE.SEMANA([@Fecha];2)
16. NOTA: El segundo argumento indica con un 2 que la semana comienza en lunes.
17. Copia las celdas B2 y C2 hacia abajo con autorrelleno.
18. Centra y ajusta las columnas.
19. Copia las fórmulas dinámicas de la tabla como valores estáticos.
20. NOTA: Este paso es recomendable si no va a crecer esta tabla, ya que mejora su rendimiento al no tener que recalcularla.
21. Inserta la tabla dinámica en una nueva hoja a partir de esta tabla.
22. Cambia el nombre de la hoja a: Calendario
23. Ponla en color azul.
24. Selecciona todas las celdas (arriba a la izquierda de las filas y columnas)
25. Selecciona menú de Inicio: Alineación
26. Centrar y Alinear en el medio
27. Selecciona "Fecha" como: Etiqueta de fila
28. Agrupa las fechas por: Meses y Años.
29. Selecciona "Núm. Semana" como: Etiqueta de fila
30. Selecciona "Día Semana" como: Etiqueta de columna
31. Selecciona "Fecha" como: Resumen de Valores
32. En Valores: Cuenta de Fecha Cambia la Configuración de campo de valor…
33. Resume el campo de valor por: Máx.
34. ATENCIÓN: Este es el truco principal de este calendario.
35. Valores como: Máx. de Fecha
36. ¡¡¡ Y se ven los números de serie de las fechas en Excel !!!
37. En el menú Vista: Desmarca la Línea de cuadrícula
38. Selecciona la fila 4: Inmoviliza paneles
39. En Herramientas de tabla dinámica: Diseño
40. Totales generales: Desactivar para filas y columnas
41. ¡Ya tenemos el esqueleto del calendario montado con una tabla dinámica!
42. Cambia los nombres de las etiquetas:
43. B2: Máx. de Fecha por: Calendario
44. B3: Etiquetas de fila por: Año / Mes / Semana
45. C2: Etiquetas de columna por: Día
46. C3 a I3: Cambia 1 por lun; … ; 7 por dom
47. Selecciona la celda B4 (1901):
48. Haz clic con el botón derecho del ratón y aparece el menú contextual.
49. Desmarca: Subtotal "Años"
50. Selecciona la celda B5 (ene):
51. Haz clic con el botón derecho del ratón y aparece el menú contextual.
52. Desmarca: Subtotal "Fecha"
53. Selecciona la celda D6 con el primer día del calendario:
54. Haz clic con el botón derecho del ratón y aparece el menú contextual.
55. Selecciona: Configuración de campo…
56. Haz clic en el botón de abajo a la izquierda: Formato de número
57. Selecciona Categoría: Personalizada
58. Cambia el Tipo: Estándar por la letra: d
59. ¡Perfecto! ¡Has conseguido que los valores de fechas se muestren como los días del mes!
60. Vamos a conseguir que el calendario sea dinámico.
61. En Herramientas de tabla dinámica: Opciones
62. Inserta Segmentación de datos
63. Marca: Años y Fecha
64. Selecciona la segmentación de datos: Fecha
65. En Herramientas de Segmentación de datos: Opciones
66. Cambia el Título de Fecha por: Meses
67. Cambia el número de columnas a 12
68. Sitúa esta segmentación arriba
69. Amplia su anchura hasta que se vean los nombres de los meses.
70. En Herramientas de tabla dinámica: Opciones
71. Desmarca: Botones +/-
72. Selecciona una celda de la tabla dinámica:
73. Haz clic con el botón derecho del ratón y aparece el menú contextual.
74. Selecciona: Opciones de tabla dinámica
75. Desmarca: Autoajustar anchos de columna al actualizar
76. Selecciona el rango de columnas C:I
77. Haz clic con el botón derecho del ratón para mostrar el menú contextual
78. Selecciona: Ancho de columna…
79. Pon un valor de: 8
80. En Herramientas de tabla dinámica: Diseño
81. Selecciona un estilo a tu gusto…
82. BONUS: Aplicar formato condicional a los días de entresemana:
83. Selecciona el rango de celdas: =$C$4:$I$15094
84. En el menú Inicio: Formato condicional
85. Haz clic en: Nueva regla
86. Utiliza la fórmula: =DIASEM(C4;2) 6
87. Formato: Color azul claro
88. Haz clic en el botón: Aceptar
89. BONUS: Aplicar formato condicional a los sábados y domingos (findes):
90. Selecciona el rango de celdas: =$C$4:$I$15094
91. En el menú Inicio: Formato condicional
92. Haz clic en: Nueva regla
93. Utiliza la fórmula: =Y(DIASEM(C4;2)>5;C4<>"")
94. Formato: Color rojo claro
95. Haz clic en el botón: Aceptar"
96. EXTRAS: Aplicar formato condicional al día de hoy:
97. Selecciona el rango de celdas: =$C$4:$I$15094
98. En el menú Inicio: Formato condicional
99. Haz clic en: Nueva regla
100. Utiliza la fórmula: =C4=HOY()
101. Formato: Color amarillo
102. Haz clic en el botón: Aceptar
103. Selecciona la celda B5 (ene):
104. Haz clic con el botón derecho del ratón y aparece el menú contextual.
105. Selecciona: Configuración de campo...
106. Selecciona la pestaña: Diseño e impresión
107. Marca: Insertar línea en blanco después de cada etiqueta de elemento
108. Con las segmentaciones de datos:
109. Selecciona el año: 2020
110. Selecciona 3 meses: de oct a dic
111. ATENCIÓN: Cómo reducir el tamaño del libro:
112. Selecciona la fila 27.
113. Mantén pulsada la tecla: Mayúsculas
114. Presiona una vez la tecla: Fin
115. Presiona una vez la tecla de cursor: Flecha hacia abajo
116. Haz clic en cualquier fila con el botón derecho del ratón
117. Haz clic en: Eliminar
118. IMPORTANTE: Guarda el libro inmediatamente.
119. IMPORTANTE: Elimina la hoja Fechas si no vas a ampliar el calendario a más siglos que el XX y el XXI. La tabla dinámica seguirá igual de dinámica y se reducirá el tamaño del libro y, por lo tanto, los tiempos para abrirlo y recalcularlo…
120. ¡¡¡ OBJETIVO CONSEGUIDO !!!

Si has seguido todos los pasos anteriores o has seguido las explicaciones del vídeo, ¡¡¡ ya tendrás un calendario dentro de una tabla dinámica !!!

Si no has seguido los pasos, o no lo has conseguido, puedes descargar esta plantilla desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive, para jugar con este calendario:

  Calendario Dinámico PW1.xlsx 

Como aplicación de las tablas dinámicas he diseñado 3 calendarios más que te pueden interesar:

• Calendario Laboral Dinámico en Excel | #ExcelPedroWave
• Calendario Laboral con Festivos Dinámicos | #ExcelPedroWave
• Calendario de Eventos en Excel | #ExcelPedroWave

Comenta si ha sido una pérdida de tiempo o si has aprendido alguna técnica nueva que te haya servido para mejorar en tu dominio de Excel.