MS Excel: ¿Cómo modelar mis datos para producir un gráfico desglosado en producto y mes (año tras año)?

Question

Tengo una sola columna de fecha en mis datos sin procesar. Las fechas varían entre 2018 y 2018. Tengo varios productos y cada fila representa una transacción de venta. Hay múltiples transacciones por producto.

¿Cómo haría para tener dos columnas en mi tabla dinámica, una para 2018 y otra para las fechas de 2019? es posible?

La razón por la que quiero hacer esto es para poder crear un gráfico de barras dinámicas con valores de fecha de 2018 y 2019 uno al lado del otro. P.ej. cantidades de venta para enero de 2018, enero de 2019, febrero de 2018, febrero de 2019, etc. ¿Cuál sería la mejor manera de hacer esto?

PD. Tengo miles de filas de datos.

Además, me gustaría mostrar las cantidades de venta de varios productos para las fechas anteriores, por ejemplo. Enero de 2018 frente a enero de 2019, febrero de 2018 frente a febrero de 2019, etc. ¿Cómo haría para agregar esto al gráfico de barras dinámicas? Se agradecería mucho su consejo sobre la mejor manera de remodelar los datos que tengo.

Mis datos sin procesar

Cómo me gustaría que se viera mi gráfico. Excepto que me gustaría un desglose de cada uno de los productos, para poder ver una tendencia año tras año:

Logré encontrar un ejemplo en línea sobre cómo me gustaría que se viera mi gráfico y, por lo tanto, supongo que mi pivote también debe verse así:

Answer 1

No tengo acceso a Excel, así que esbocé algo en LibreOffice Calc que cubrirá los conceptos básicos.

Las columnas Fecha y Cantidad son de su ejemplo. Agregué algunas columnas auxiliares como una forma sencilla de organizar los datos como desee.

La columna del mes extrae el número del mes de la fecha. C2:

=MONTH(A2)

La columna de año extrae el año de la fecha. D2:

=YEAR(A2)

Si desea que los meses se registren en la secuencia adecuada, el número del mes lo hace. Los nombres de los meses se registrarán en orden alfabético, por lo que la tabla dinámica se basa en el número de mes. Sin embargo, aún desea que los nombres de los meses etiqueten el gráfico.

Hay algunas formas de obtener los nombres de los meses. La columna E contiene un enfoque (no es necesario si utiliza el otro enfoque). E2:

=TEXT(A2,"mmm")

Esto simplemente formatea la fecha para mostrar solo la abreviatura del mes. Cuando traza los resultados de la tabla dinámica, desea asociar el nombre del mes con el número de mes en la tabla dinámica, que se muestra en la columna G. Para usar la columna E para esto, G3 contendría:

=VLOOKUP(H3,$C$2:$E$7,3)

Esta es sólo una de las formas de buscar el valor. BUSCARV busca el número de mes de la tabla dinámica en la columna Mes de los datos y extrae el nombre del mes de la columna E para la primera coincidencia.

Otro enfoque no requiere la columna E. Simplemente traduce el número del mes:

=CHOOSE(H3,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

ELEGIR utiliza el número del mes como índice para seleccionar el nombre del mes de una lista. Un número de mes de "N" selecciona el enésimo nombre de la lista.

Para la tabla dinámica, seleccione las columnas Cantidad, Mes y Año como datos de origen. En el cuadro de diálogo de la tabla dinámica, arrastre Año a la ventana Columnas, Mes a la ventana Filas y Cantidad a la ventana Valores (verifique que el método de agregación esté configurado en Suma). Eso le brinda los datos organizados para los gráficos.

Ha pasado un tiempo desde que hice gráficos en Excel. Recuerdo que puedes seleccionar los nombres de los meses para las etiquetas de los ejes cuando defines los rangos de datos. LO Calc no hace eso. Para mi gráfico de muestra, dejé las etiquetas de los ejes, agregué un título de eje y puse los nombres de los meses en el título, con espacios para alinear todo.