Comience con varios años de datos.
Supongamos que tiene registros detallados de dos años. Cada registro tiene una fecha diaria. Cuando cree una tabla dinámica a partir de este informe, tendrá cientos de filas de fechas diarias en la tabla dinámica. Esto no es un gran resumen.
Elija una de esas celdas de fecha en la tabla dinámica. En la pestaña Analizar de la cinta, elija Agrupar campo.
Como se encuentra en un campo de fecha, obtiene esta versión del cuadro de diálogo Agrupación. En él, deseleccione Meses y seleccione Años.
Las fechas diarias se acumulan en años. Mueva el campo Fecha de FILAS a COLUMNAS.
El resultado es casi perfecto. Pero en lugar de un gran total en la columna D, probablemente desee una variación porcentual.
Para deshacerse de la columna Gran total, en la pestaña Diseño, elija Grandes totales, Activado solo para columnas. (Estoy de acuerdo, esta es una de las redacciones más incómodas en Excel).
Para construir la columna de varianza, debe escribir una fórmula fuera de la tabla dinámica que apunte dentro de la tabla dinámica. No toque el mouse o las teclas de flecha mientras crea la fórmula, o aparecerá la desagradable función GETPIVOTDATA. En su lugar, simplemente escriba =C5/B5-1y presione Entrar.
Gracias a Александр Воробьев por sugerir este consejo.
Ver video
- Comience con varios años de datos
- Insertar, mesa pivotante
- Arrastre el campo de fecha al área de la fila
- Excel 2016: presione Ctrl + Z para desagrupar fechas
- Arrastre los ingresos al área de valores
- Seleccione cualquier fecha en el área de la fila
- Usar campo de grupo
- Elija meses y años
- Cómo agregar subtotales al campo años después de agrupar una tabla dinámica
- Use el formulario tabular en una tabla dinámica para dar a cada campo de fila su propia columna
- Repita todas las etiquetas de fila para completar los espacios en blanco en una tabla dinámica
- Arrastre años al área de la columna
- Haga clic con el botón derecho en el encabezado de la columna Gran total y elimine
- Cómo evitar la función GetPivotData cuando la fórmula apunta a una tabla dinámica
- Para generar una variación, escriba la fórmula sin el mouse ni las flechas
- Gracias a Александр Воробьев por sugerir este consejo
Transcripción del video
Aprenda Excel del podcast MrExcel, episodio 1998. Tabla dinámica año tras año.
Estaré publicando un podcast de este libro completo, haz clic en esa "i" en la esquina superior derecha, para la lista de reproducción de MrExcel Excel.
Oye, bienvenido de nuevo al netcast, soy Bill Jelen. Hoy, el primer tema del libro sobre las tablas dinámicas y cómo crear un informe año tras año. Entonces, estamos comenzando con el conjunto de datos, que abarca dos años, ya ve, vamos desde 2018 hasta 2019. Voy a crear una tabla dinámica, insertar, tabla dinámica, está bien, vamos a un nueva hoja de trabajo.
En la nueva hoja de trabajo, tomaremos el campo Fecha y lo arrastraremos al área Filas. Y luego, verá, en Excel 2016 agrupa automáticamente las cosas. Ahora sé que la mayoría de la gente todavía está en Excel 2013, tal vez Excel 2010, así que presionaré Ctrl + Z para deshacer. Esa es la experiencia que obtendrá en 2010 y 2013, vamos a tomar el campo Ingresos y arrastrarlo al área de Valores. Así que comenzamos con un informe feo, habrá cientos y cientos de filas, porque todos los días tenemos ventas. Pero voy a ir al primer campo de fecha, al primer campo de fecha, ir a Herramientas de tabla dinámica, Analizar, en 2013 o 2016 y 2010 se llamaba Opciones, y elegir Campo de grupo. Asegúrese de elegir una fecha antes de elegir Campo de grupo y acumular las cosas en Meses y Años. O cuartos y años,o Meses y Trimestres y Años, haga clic en Aceptar. Y lo que obtenemos es un nuevo campo virtual llamado Años aquí en el área de Filas.
Ahora algunas molestias. Es molesto que no nos den los totales del año, es bastante fácil arreglar eso, aunque en mi caso no necesito arreglarlo. Hacemos clic en Configuración de campo y Automático, porque no planeo dejar los Años aquí. Por supuesto, cada vez que creo una tabla dinámica, voy a Diseño y digo Mostrar en forma tabular para poner todo en su propia columna. Y luego Repita todas las etiquetas de elementos para completar esos espacios en blanco a lo largo del lado izquierdo. Nuevamente, eso no sería necesario aquí, porque no estoy planeando dejar los Años donde están, voy a tomar los Años y arrastrarlos a las Columnas, y ese es el informe que estaba buscando.
Muy bien, tenemos Meses en el lado izquierdo, de 2018 a 2019. En la columna D, ¿qué es lo que realmente quiero? No quiero un Gran Total. Voy a hacer clic derecho y decir Eliminar el Gran Total y ahora estamos fuera de una tabla dinámica. Entonces,% Change, es muy tentador construir esta fórmula usando el mouse o las teclas de flecha, pero no puede, porque obtendrá la función … get Pivot Data, que no se copia fácilmente. Entonces = C5 / B5-1, lo formatearemos como un porcentaje y haremos doble clic para copiarlo, a la derecha. Hermoso informe que muestra dónde estábamos el año pasado, dónde estábamos este año y luego el cambio porcentual de un año a otro.
Bueno, este consejo y otros 40 están todos en este libro. Adelante, haga clic en esa "i" en la esquina superior derecha, junto al libro. Muy bien, entonces comienza con el conjunto de datos con varios años, inserta, tabla dinámica, arrastra el campo de fecha al área de la fila. Y en Excel 2016 presione Ctrl + Z para deshacer sus actualizaciones automáticas de agrupación. ¿Por qué no dejamos esto aquí? Bueno, en realidad depende. Si tuviera datos de, digamos, del 2 de enero al 30 de diciembre, no van a agrupar esos datos correctamente. Tiene que pasar un año entero para obtener el campo de año. Puede ser que te funcione, soy un poco fanático del control, prefiero tener el control, por eso deshago. Hay: arrastre los ingresos al área de valores, seleccione cualquier celda, cualquier fecha en el área de la fila y luego Campo de grupo, elija Meses y años,arrastre años sobre el área de la columna, haga clic con el botón derecho en el Gran Total para eliminar y luego cree una variación, escriba la fórmula, no use el mouse o las teclas de flecha.
No tengo idea de cómo pronunciarlo, pero gracias a este lector (Александр Воробьев) por sugerir este consejo y gracias a ti por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast1998.xlsx
