Me gustaría un poco de ayuda para un escenario en el que trabajaré con Excel. Cualquier ayuda será muy apreciada.
Tendré un rango de fechas cada vez mayor para elementos específicos y necesito hacer una hoja de Excel para determinar el número promedio de días entre ellos por elemento. Básicamente, este es un ejemplo simplificado de cómo planeo tabular los datos:
Item Code | Date
A.ITEM | January 15, 2017
B.ITEM | January 16, 2017
A.ITEM | January 22, 2017
C.ITEM | January 25, 2017
A.ITEM | January 31, 2017
C.ITEM | February 2, 2017
B.ITEM | February 12, 2017
B.ITEM | February 24, 2017
C.ITEM | March 7, 2017
Luego crearé otra tabla que mostrará la duración promedio entre las fechas por elemento. Me imagino que se verá así:
Item Code | Average Life Span
A.ITEM | 9 days
B.ITEM | 20.5 days
C.ITEM | 21.5 days
¿Qué fórmula necesitaría para hacer posible la segunda mesa? Llevo un tiempo devanándome los sesos y, como no estoy muy familiarizado con las funciones de fecha en Excel, todavía no sé cómo. ¿Es siquiera posible?
¡Gracias!
Respuesta1
Tenga en cuenta que el promedio de las diferencias es solo (max-min)/count: (d1-d2) + (d2-d3) + (d3-d4) +... = d1-dn
Con eso, puedes usar una fórmula como
(MAX(d1:dn)-MIN(d1:dn))/COUNT(d1:dn)
Sin embargo, esto colocaría todas las fechas en un solo recipiente, por lo que deberá filtrar adicionalmente por sus códigos, en lugar de simplemente MAX(d1:dn)utilizarlo MAX(IF(a1:an=code,d1:dn,0)como fórmula matricial. Agregue if similares para MIN y COUNT (o use COUNTIF); tenga en cuenta que para MIN, el valor else no puede ser 0, pero debe ser algo muy grande.
Respuesta2
En este ejemplo, el código del artículo está en la columna A, la fecha es la columna B y luego agrego nuevos datos. La fila 1 son los títulos.
Primero, deberá ordenar sus datos cada vez que agregue una nueva fila, con una clasificación de dos capas:
- primero por código de artículo
- segundo por fecha
Luego, en la columna a la derecha de Fecha, agregue un cálculo de Tiempo entre y arrastre/rellene hacia abajo:
=IF(A2=A1,B2-B1,"")
Luego promediar los tiempos. Enumere los códigos de artículo A, B, C en otro lugar; los puse en la columna F de mi prueba. Junto a A, ingresa y arrastra/completa hacia abajo:
=AVERAGEIF($A$2:$A$6491,F2,$C$2:$C$6491)
Obtuve:
- R: 8
- B: 19,5
- C: 20,5
(para A: del 22 de enero al 15 de enero son 7 días, del 31 de enero al 22 de enero son 9 días, el promedio es de 8 días)