Calendario de tabla dinámica - Tutoriales de Excel TOP
Excel tiene numerosas características y funciones relacionadas con las fechas. En esta publicación, exploraremos algunos de ellos. Necesitamos una ilustración que los una a todos, así que crearemos un calendario gráfico con una tabla dinámica. Incluso si no necesita un calendario gráfico en sus libros de trabajo, la mecánica subyacente que nos permite crearlo se puede utilizar en muchas otras situaciones.
Objetivo
Esto es lo que queremos construir:
Este es un calendario gráfico de tabla dinámica. Podemos elegir el mes deseado en el control de filtro y la tabla dinámica se actualiza en consecuencia.
Para crear esto, necesitaremos explorar algunas funciones, específicamente usaremos: TEXTO, NUMERO DE SEMANA y DÍA.
He creado un video y una narrativa con los detalles a continuación.
Esta publicación fue presentada por Excel FSM. Antes de pasar a los detalles, me gustaría agradecer a nuestro patrocinador Excel FSM. Excel FSM (Módulo de estados financieros) es un complemento patentado de Excel que permite a los contadores crear más fácilmente estados financieros a partir de un balance de comprobación (o de muchos balances de comprobación para preparar estados financieros consolidados).
Le permite trabajar con cualquier sistema que pueda exportar un archivo de balance de comprobación. Y si usted o sus clientes utilizan QuickBooks, Excel FSM transforma Excel en un redactor de estados financieros para QuickBooks.
El complemento de Excel le permite definir los grupos de informes y crear los informes. Actualizar las finanzas en el futuro es solo cuestión de exportar un balance de comprobación actualizado y presionar actualizar.
Si cree que Excel FSM podría ayudarle a ahorrar tiempo, obtenga más información y una prueba gratuita aquí:
¡Gracias nuevamente a Excel FSM por patrocinar esta publicación!
Video
Narrativo
Recorreremos este proceso siguiendo los siguientes pasos:
- Tabla de fechas
- Tabla dinámica básica
- Formato de tabla dinámica
Hagámoslo.
Tabla de fechas
El primer paso es crear una tabla que almacene las fechas de los períodos que desea mostrar en su calendario. Primero mostraré la tabla completa y luego repasaremos la fórmula para cada columna. Al final del día, necesitamos una columna para cada área de diseño de la tabla dinámica (filas, columnas, valores, filtros). Entonces, para ayudar a visualizar dónde terminarán estas columnas en el PT, las anoté en la siguiente captura de pantalla:
Columna WeekNum: Para calcular la columna WeekNum, podemos usar la función WEEKNUM y usar el valor en la columna Fecha como argumento, como se muestra a continuación:
=NÚMERO DE SEMANA([@Fecha])
La función WEEKENUM devuelve el número de semana de la fecha. Usaremos este valor para crear nuestras filas de la tabla dinámica.
Columna de día de la semana: para calcular la columna de día de la semana, usaremos la función TEXTO que devuelve un valor de texto con el formato especificado. El formato que usaremos en este caso es “ddd” que devuelve la abreviatura de tres letras. (También puede mostrar el nombre del día completamente escrito con “dddd”).
=TEXTO([@Fecha],"ddd")
Usaremos esta columna para crear nuestras columnas de tabla dinámica.
Columna de día: para calcular la columna de día, podemos usar la función DÍA que devuelve el número de día de la fecha:
=DÍA([@Fecha])
Usaremos esta columna para crear nuestros valores de tabla dinámica.
Columna Mes: Para calcular la columna Mes, podemos usar la función TEXTO nuevamente, pero con diferentes códigos de formato:
=TEXTO([@Fecha],"mmm aaaa")
El código de formato “mmm yyyy” le dice a Excel que queremos una abreviatura de mes de tres letras y un año de 4 dígitos. Puede utilizar otros códigos según lo desee, por ejemplo, “mmmm” sería el nombre del mes completamente escrito y “m” sería el número del mes.
Usaremos esta columna para crear nuestros filtros de tabla dinámica.
Con nuestra tabla de fechas completa, es hora de crear nuestra tabla dinámica básica.
Tabla dinámica básica
Para crear nuestra tabla dinámica básica, simplemente seleccionamos cualquier celda en la tabla Fecha y seleccionamos Insertar Tabla dinámica . Podemos colocar el PT en una hoja nueva o existente.
Hacemos clic y arrastramos para insertar el campo WeekNum en el área de diseño de Filas , el campo WeekDay en el área de Columnas , el campo Día en el área de Valores y el campo Mes en el área de Filtros . Deberíamos ver algo como esto:
Y lo creas o no, ¡nuestra tabla dinámica básica ya está lista! Ahora sólo es cuestión de formatear.
Formato de tabla dinámica
Primero, le pediremos a Excel que conserve los anchos de columna actuales. Para ello, seleccionamos Herramientas de tabla dinámica Opciones . En el cuadro de diálogo Opciones de tabla dinámica resultante , desmarcamos la casilla de verificación Autoajustar anchos de columna al actualizar .
Eliminar totales generales: a continuación, queremos eliminar los totales generales. Entonces, hacemos clic en Herramientas de tabla dinámica Totales generales Desactivado para filas y columnas .
Filtro: a continuación, en lugar de mostrar todos los meses, podemos elegir un solo mes usando el menú desplegable de filtro Mes. Elegiré enero de 2019, y en este punto nos vemos bastante bien:
Aplicaremos algunas cosas cosméticas más para limpiarlo un poco más.
Encabezados de campo: eliminaremos los encabezados de campo desactivando el comando Herramientas de tabla dinámica Encabezados de campo .
Bandas: marcamos las casillas Herramientas de tabla dinámica Filas con bandas y Columnas con bandas .
Estilo de tabla dinámica: elegiremos el estilo que queramos de la galería Herramientas de tabla dinámica Estilos . Elegí “Azul claro, estilo pivote oscuro 2”.
Ocultar la columna WeekNum: si desea ocultar la columna WeekNum, simplemente haga clic derecho en la etiqueta de la columna de la hoja de trabajo (B, C, D,…) y seleccione Ocultar en el menú contextual.
¡Y creo que lo conseguimos!
No dude en descargar el archivo de muestra funcional: PivotTableCalendar.xlsx
Si tiene otros consejos divertidos sobre fechas, calendarios o tablas dinámicas, compártalos publicando un comentario a continuación… ¡gracias!
Divulgaciones y notas
- Esta es una publicación patrocinada para Excel FSM. Todas las opiniones son mías. Excel FSM no está afiliado ni respalda ningún otro producto o servicio mencionado.
- El 100% de las ganancias de los patrocinadores financian los programas de becas y ayuda financiera de Excel University… ¡gracias!
- Si desea información sobre cómo convertirse en patrocinador, consulte nuestra página de oportunidades de patrocinio .