¡Hola gente mejor en Excel que yo! Agradezco cualquier ayuda que puedas ofrecer. Intentaré ser breve:
Tengo una tabla de 3 columnas. Mes (como 1,2,3,etc…), Nombre del vendedor y Ventas. Cada fila muestra cuántos dólares en ventas obtuvo un vendedor durante ese mes.
Está organizado de la siguiente manera: las primeras 1000 filas son todas del 1 de enero, las siguientes 1000 son todas del 2 de febrero, etc.
Necesito obtener la cantidad promedio en dólares vendida por cada vendedor durante los últimos 3 meses. Pero... si uno de esos últimos 3 meses está en blanco, todavía necesito un promedio de 3 meses.
Entonces, necesito la fórmula para reconocer, por ejemplo, un resultado de septiembre en blanco y obtener el siguiente resultado más reciente (agosto, julio, cualquiera que sea), de modo que siempre promedie los últimos 3 meses de los resultados de ese vendedor, y no sólo 1 o 2 resultados.
Ahora mismo tengo una tabla dinámica muy simple para esto. Estaba funcionando muy bien para mis propósitos hasta que me di cuenta de este problema :(
¿Hay alguna forma (tabla dinámica o no) de poder hacer esto? Hay más de 1000 vendedores, por lo que no es factible hacerlo manualmente. Tengo muchas personas sin números durante un mes por cualquier motivo, así que necesito hacer que el cálculo solucione eso.
¡Gracias por cualquier ayuda! Avíseme si puedo incluir algo más o si no está claro.
Respuesta1
No conozco una buena manera de promediar los tres meses más recientes que no estén en blanco, pero aquí hay unamuymanera hacky de hacerlo. Esto puede generar mejores formas de lograrlo.
En sus datos sin procesar, cree una tabla y luego ordénela por mes (descendente) y persona.
A continuación, filtre por la columna Ventas y anule la selección de los espacios en blanco. Luego copie esos contenidos a otra área y péguelos.
Luego cree una columna de "Clasificación". Fórmula para la celda D2, que clasifica cada mes (el más reciente es 1, etc.) para cada persona.
=IF(B2=B1,D1+1,1)
Luego, tenga otra área con sus distintas personas (columna F) y columnas de marcador de posición para los 3 meses más recientes (columnas denominadas 1, 2 y 3).
Fórmula de la celda G2:
=SUMIFS($C:$C,$B:$B,$F2,$D:$D,G$1)
Arrástrelo hasta el título de la columna 3 y hacia abajo. Por último, cree su fórmula promedio.
Respuesta2
Adoptando un enfoque diferente que deja los datos originales intactos, sin reordenarlos, filtrarlos o copiarlos, se agregan 3 columnas auxiliares a la lista de datos como se muestra a continuación.
ColumnaFilaes una serie de índice simple que comienza en 1, columnaPersona2es lo mismo quePersonapero usa una función IF para insertar una cadena nula en filas con ceroVentas.MCountproporciona un recuento, porPersona, de meses con distinto de ceroVentas.
MCountinteractúa con una lista de resultados que contiene una fila para cada vendedor, como se muestra a continuación.
En la lista de resultados, columnaMesessimplemente cuenta la cantidad de meses con ventas distintas de cero en la lista de datos para cada vendedor. La fórmula en la celda I2 es
=COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")
FilaM1muestra elFilavalor en la lista de datos correspondiente al primer mes con ventas distintas de cero para cada vendedor. La fórmula en la celda J2 es
=MATCH(H2,$E$2:$E$21,0)
columnasMes1,Mes2yMes3son los números de mes dentro de la columnaMCount(de la lista de datos) correspondiente al primer, segundo y tercer mes utilizados para calcular el promedio de tres meses, mientras queVentas1,Ventas2yVentas3son los valores de ventas de estos 3 meses.Promedioes el promedio calculado de 3 meses.
Las fórmulas para las celdas K2, L2 y M2 son respectivamente
=I2-1
=K2+1
=L2+1
La fórmula en la celda N2 es
=SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)
y se basa en los valores delMCountcolumna de la lista de datos (rango $F$2:$F$21, ver a continuación). La celda N2 se copia en las celdas O2 y P2.
El rango I2:Q2 se puede copiar a todas las filas posteriores de la lista de resultados.
Lista de datos - columnaMCount
La fórmula en la celda F2 es
=IF(C2>0,1,0)
Esto pone 0 o 1 en la celda. 0 indica que la persona en la celda E2 no tuvo ventas en el mes que se muestra en la celda B2, mientras que 1 indica que fue el primer mes de ventas para esta persona.
La fórmula de la celda F3 es considerablemente más compleja y es
=IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))
La parte VERDADERA del primer IF simplemente entrega 0 si la fila corresponde aVentasde cero.
De lo contrario,Ventasson distintos de cero y se activa el segundo IF. En este segundo SI elPersonaen la celda E3 se busca en la tabla de resultados y, si el FilaM1El valor (número de fila en la lista de datos correspondiente al primer mes de ventas) coincide con elFilavalor en la celda D3, esto significa que la fila 3 corresponde a la primera fila con ventas distintas de cero para la persona identificada en la celda E3.
Si elFilaM1yFilaLos valores difieren, la fila 3 en la tabla de datos corresponde a un mes de ventas posterior (después del primero) para la persona en la celda E3. En este caso, el número del mes se obtiene sumando 1 al valor máximo en la columna F para el vendedor en la celda E3 en las filas anteriores de la lista de datos. Se utiliza una función MAXIFS para determinar este máximo.
La fórmula de la celda F3 se copia a las filas siguientes de la lista de datos. El uso apropiado de direccionamiento relativo y absoluto en la fórmula garantiza que la función MAXIFS utilice los rangos apropiados de celdas en las copias.
Notas
- El uso de MAXIFS requiere Excel 2019 o posterior
- Se supone que (i) los datos están en orden cronológico y (ii) hay, como máximo, una fila distinta de cero de datos de ventas para cada combinación de vendedor y mes.
- En las imágenes, una fuente azul indica una fórmula y una fuente negra un valor estático.