Fórmula para calcular los ingresos fijos durante 12 meses después de un retraso de 1 mes

Question

La fórmula requerida es unalevementefórmula matricial complicada.

Aquí está su hoja de cálculo con la fórmula que muestra los resultados esperados:

Esta fórmula debe ingresarse en una matriz AE6y luego completarse/copiarse y pegarse hacia abajo y hacia la derecha tanto como sea necesario):

{=SUM(IFERROR(INDEX(6:6,N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))/$I$2,0))}

Explicación:

La versión embellecida de la fórmula es la siguiente:

{=
SUM(
  IFERROR(
    INDEX(
      (6:6),
      N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))
      +(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))
    )/$I$2,
    0
  )
)}

La fórmula es mucho más fácil de entender si se considera que para una duración de 12 meses y un retraso de 1 mes, el segundo argumento del primero INDEX()equivale aproximadamente a:

COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-1

La fórmula básicamente funciona generando una serie de compensaciones para acceder a los pedidos del I2número de meses anterior, retrasados por I1meses, en relación con la celda actual.

Repasar la fórmula AK6debería aclarar lo anterior:

COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))
→{1,2,3,4,5,6,7,8,9,10,11,12}
COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-$I$1
→ {37}-({31}-{6})-{1,2,3,4,5,6,7,8,9,10,11,12}-1
→{10,9,8,7,6,5,4,3,2,1,0,-1}
N(IF(1,{10,9,8,7,6,5,4,3,2,1,0,-1}))
→ N({10,9,8,7,6,5,4,3,2,1,0,-1})
→{10,9,8,7,6,5,4,3,2,1,0,-1}
{10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>=COLUMN($R:$R))
→ {10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>={18})
→ {10,9,8,7,6,5,4,3,2,1,0,-1}+{0,0,0,0,0,0,0,0,0,0,0,0}
→{10,9,8,7,6,5,4,3,2,1,0,-1}
INDEX((6:6),{10,9,8,7,6,5,4,3,2,1,0,-1})/$I$2
→ INDEX(6:6,{10,9,8,7,6,5,4,3,2,1,0,-1})/12→ {24000,0,20000,0,0,"Opportunity Name1","bWmd1","Col C val","Col B val","Col A val","Col A val",#VALUE!}/12
→{2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
SUM(IFERROR({2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},0))
→2666.67

INDEX(6:6,N(IF(1,expression)))es un truco requerido ^* para forzar a Excel a devolver una matriz for expressionya que el segundo argumento de INDEX()se evalúa como un valor único de forma predeterminada. Usar just in conduciría aINDEX(6:6,expression)AK6

INDEX((6:6),COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))
→ INDEX(6:6,37-(31-6)-COLUMN($A$6:$L$6)-1+(37-(31-6)-COLUMN($A$6:$L$6)-1>=18))
→ → →INDEX(6:6,12-1-1+(12-1-1>=18))
INDEX(6:6,10)
24000

ya que dentro de una expresión que devuelve un solo valor, devuelve la columna de la primera celda del rango.COLUMN(multi-cell-range)

Se +(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))ajusta a la brecha entre las tablas de pedidos para 2018 y 2019 (columna R). Tenga en cuenta que el N(IF(truco no es necesario en este caso ya que el truco utilizado anteriormente ya ha forzado una evaluación de matriz del segundo argumento de INDEXy, por lo tanto, la COLUMN()función evalúa matrices.

La IFERROR()función es necesaria en caso de que la fórmula exista en una celda cercana al lado izquierdo de la hoja, lo que resulta en acceder al texto o intentar acceder a una celda a la izquierda de la columna A.

Notas:

La fórmula embellecida realmente funciona si se ingresa.
Los corchetes (6:6)en la versión embellecida son necesarios para obligar a 6:6permanecer en su propia línea.

Advertencias:

No puede haber números en las celdas de las n columnas a la izquierda de los pedidos.(donde n está especificado por el valor en I2). Si hay números allí, la fórmula, tal como está, los incluirá en el cálculo de ingresos.
No puede haber ninguna brecha entre las columnas de Ingresos de diciembre de 2018 y de Ingresos de enero de 2019.La fórmula se puede modificar para permitir dicha brecha si se desea.
El espacio entre las dos tablas de pedidos ( R:R) debe permanecer exactamente en una columna de ancho. De lo contrario, la fórmula tal como está se romperá.
El espacio entre las tablas de pedidos no puede contener ningún número. En caso contrario, serán tratados como pedidos adicionales.

_{^* La explicación de exactamente por qué funciona el truco tendrá que esperar hasta que lo descubra por mí mismo primero ;-)}

Answer 1