Calendario en Excel con una tabla dinámica

🔝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

En una hoja llamada 'Fechas' se crea una tabla con 3 columnas: Fecha; Día Semana; Núm. Semana.

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.

  Calendario Dinámico PW0.xlsm 

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.

Calendario Perpetuo desde 1900 con eventos

0) Introducción al Calendario Perpetuo

Esta no es la tercera parte de la trilogía que estoy escribiendo sobre fechas anteriores al año 1900, sino que es un inciso para presentar un nuevo Calendario Perpetuo con fechas desde el año 1900, y que tiene como valor añadido la posibilidad de mostrar los eventos o efemérides de cada día pasando el ratón sobre las celdas. Accede al siguiente enlace en inglés para saber cómo:

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

La tercera entrega de la trilogía se basará en este calendario pero usando fechas en VBA, por lo que abarcará fechas desde el 1 de enero del año 100, con lo que servirá para todo el Calendario Gregoriano.

Este es el aspecto del nuevo calendario perpetuo:

1) Características relevantes

Características relevantes del Calendario Perpetuo en Excel:

1. Calendario Perpetuo Gregoriano con un rango de fechas del 01-03-1900 al 31-12-9999.
2. Vista de 3 meses: el mes seleccionado; el anterior y el posterior.
3. El primer día de la semana puede ser domingo o lunes.
4. Muestra u oculta los días de otros meses.
5. Sábados y domingos marcados en color rojo.
6. Flechas para incrementar o decrementar el mes, rotando como un carrusel.
7. Flechas para cambiar el año.
8. Selección de un mes.
9. Selección de una año mediante 2 cifras para las centenas y 2 cifras para las unidades.
10. Si se decrementa el año 1900 pasa al año 9999.
11. Si se incrementa el año 9999 pasa al año 1900.
12. Selección del día de hoy.
13. Selección del primer día del calendario perpetuo: 01-03-1900.
14. Selección del último día del calendario perpetuo: 31-12-9999.
15. Muestra u oculta un calendario auxiliar y otros datos.
16. Tabla de fechas con las efemérides guardadas, por ejemplo los Días de Independencia de los países obtenidos de Wikipedia (enlace aquí).
17. Hasta 12 efemérides por día en 3 páginas con 4 eventos cada una.
18. BONUS: Al pasar el cursor del ratón sobre una celda muestra los eventos de ese día.
19. Activa o desactiva el evento del ratón sobre una celda.
20. Botones de animación con avance y retroceso de los meses y con 5 velocidades de animación.
21. Botones para ver los primeros o los últimos 3 meses del año.
22. Botones para incrementar o decrementar de 3 en 3 meses.
23. Filtro de un día en la tabla de fechas.

No voy a explicar cómo usar este calendario, lo interesante es probar cada una de las características y experimentar con el diseño de la experiencia del usuario (UXD - User eXperience Design) de este calendario perpetuo.

Si tienes sugerencias para mejorar este calendario, puedes compartirlas escribiendo un comentario al final de este artículo.

2) Plantilla del Calendario Perpetuo desde 1900

Descarga la plantilla totalmente gratuita, con las macros visibles y las hojas protegidas sin contraseña, desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive:

Calendario Perpetuo desde 1900 PW1.xlsm 

3) Cómo he diseñado este Calendario Perpetuo

Este calendario perpetuo se compone de 3 hojas:

• Año>=1900 - Donde se muestra y se controla el calendario.
• Fechas - Donde se guarda la tabla de fechas.
• Idiomas - Donde se guardan las traducciones de los idiomas.

En la hoja 'Idiomas' se selecciona en la celda A1 entre 2 idiomas: Español o English. Los textos de la columna A son las traducciones que aparecen en el calendario. Si quieres, puedes añadir más columnas con más idiomas. Una macro cambia los textos de la cabecera de la tabla de la hoja 'Fechas'.

La hoja 'Fechas' contiene la tabla de fechas en 3 columnas: Fecha; Evento y Núm. Serie VBA. Esta última columna es calculada con una macro y sirve para ordenar las fechas por su número de serie en VBA, o sea, con números positivos y negativos. He incluido algunas fechas significativas del calendario gregoriano desde 1900 y las efemérides de los Días de Independencia de varios países, obtenidos de la Wikipedia - enlace aquí. Desprotegiendo la hoja sin contraseña se puede editar cualquier fecha.

El calendario perpetuo está en la hoja 'Año>=1900'. Para explicar cómo ha sido diseñado nos centraremos en el mes central del calendario, en el rango K5:Q12

En la celda L5 se muestra el nombre del mes pero contiene el día 1 del mes y año seleccionados con alguno de los controles del calendario: con el desplegable de la propia celda; con el cambio de año; con las flechas de incremento o decremento de los meses; con las teclas de animación de la fila 14.

En el rango X34:Z46 está la tabla auxiliar con la lista de meses que se carga con la validación de datos de la celda L5.

El cálculo de un mes del calendario comienza en el calendario auxiliar que se encuentra entre las filas 23 y 30 (se puede ver haciendo clic en la fila 21).

En las celdas B25, K25 y T25 se calcula el primer día de la semana en que comienza un mes, con las fórmulas:

B25: =C5+1-DIASEM(C5;Rango_DíaSemana)

K25: =Rango_MesNom+1-DIASEM(Rango_MesNom;Rango_DíaSemana)

T25: =U5+1-DIASEM(U5;Rango_DíaSemana)

Siendo:

Rango_MesNom: ='Año>=1900'!$L$5

Rango_DíaSemana: ='Año>=1900'!$Z$2 (La semana comienza en: 1-dom; 2-lun)

Esas celdas contienen el número de serie de una fecha, por lo que el resto de los días se calculan a partir de esas celdas sumando un uno al día anterior. Se calculan 6 semanas para cubrir todo el mes. El formato de las celdas de esas fechas es una letra "d", por lo que se muestra el número del día. En los meses de las filas 23 a 30 se ven siempre los días de meses anteriores. Los días con eventos o efemérides en la hoja 'Fechas' se marcan en color naranja gracias al formato condicional. Los sábados y domingos se pintan en color rojo.

¡¡¡ Y ahora el truco fundamental !!!

¿Qué fórmula produce el efecto de pasar el ratón por encima de la celda ejecutando una macro?

Vamos a analizar una fórmula posible para la celda K8:

=HIPERVINCULO(MouseOver(K26);DIA(K26))

Hace referencia a la celda K26 del calendario auxiliar, que contiene una fecha cualquiera y llama a la función HIPERVINCULO que tiene 2 argumentos.

El segundo argumento es un nombre descriptivo que, en este caso, llama a la función DIA para mostrar el número del día en la celda.

El primer argumento es la ubicación del hipervínculo, ejecutando la macro MouseOver(K26), con la celda de la fecha auxiliar como argumento.

Esa era la fórmula original que sirve únicamente para días dentro del mes del calendario. La fórmula definitiva en la celda K8 es un poco más compleja:

=HIPERVINCULO(MouseOver(K26);SI.ERROR(SI(O($Z$3="ü";MES(K26)=$K$5);DIA(K26);"");""))

Esta fórmula sirve para todos los días del mes y tiene en cuenta si se muestran los días de otros meses (controlado por la celda Z3) y si hay errores en las fechas, cosa que sólo ocurre en enero de 1900 y en diciembre de 9999.

La macro MouseOver está en el módulo ModPasarRatónSobreCeldas

La función MouseOver devuelve una String con la ubicación de la propia celda en que se llamó, pero antes modifica la celda B16 ("Rango_Día") con la fecha del día sobre el que ha pasado el ratón por encima. Este efecto lo publicó por primera vez Jordan Goldmeier en su blog OPTION EXPLICIT VBA por lo que le estoy muy agradecido, pues ha contribuido a enriquecer la interactividad y usabilidad de Excel. En el siguiente enlace hay un ejemplo mío de lo que se puede llegar a hacer con este excelente efecto de pasar el ratón sobre las celdas de Excel sin tener que hacer clic en ellas:

pedrowave.blogspot.com - Como pintar con Excel

Las fórmulas que llaman a la función HIPERVINCULO consiguen cambiar el día en la celda B16, denominada "Rango_Día", con la macro MouseOver:

            Range("Rango_Día").Value2 = rCelda.Value2

¡MENUDO TRUCO!

En las filas 17 a 19 se muestran los eventos o las efemérides del día de 4 en 4 y hasta en 3 páginas, obtenidas de la tabla auxiliar en el rango B34:V46

La columna "Fila" contiene una fórmula matricial (introducida con las teclas: Control + Mayúsculas + Intro) para obtener 12 filas con los eventos del día extraidos de la tabla de la hoja 'Fechas':

{=SI.ERROR(K.ESIMO.MENOR(SI($B$16=Rango_Fechas;FILA(Rango_Eventos)-MIN(FILA(Rango_Fechas))+1;"");FILA()-FILA(B34));0)}

Haciendo clic en el día de la celda B16, si hay eventos en ese día, los filtra en la hoja 'Fechas'.

4) Próximos pasos

En una próxima entrega publicaré un calendario perpetuo similar pero usando las fechas de VBA en lugar de las de Excel, con lo que se podrán programar eventos desde el día 1 de enero del año 100.

Si te gustan los calendarios puedes leer todos los calendarios que he publicado hasta la fecha en este enlace:

https://pedrowave.blogspot.com/search/label/calendario