Fórmula para calcular a receita fixa durante 12 meses após um atraso de 1 mês

Question

Estou com problemas para criar uma fórmula que me permita calcular a receita com base no atraso da receita em um mês e, em seguida, em diminuir a receita ao longo de 12 meses.

Esta é minha planilha:

Os dados base para pedidos 2018 estão nas células F6:Q9 .

Os dados base para pedidos de 2019 estão nas células S6:AD9 .

Atualmente, há um pedido para março de 2018 (célula H6 ) e o perfil de receita deve apresentar um atraso de 1 mês para que não haja receita para abril, mas a partir de maio de 2018, a ordem de 20.000 deve começar a refletir £ 1,667 (até abril de 2019).

A fórmula também precisaria levar em conta as ordens de F6:Q9 (2018 ordens) e de S6:AD9 (2019 ordens).

Na célula I1 , digitei 1 (para refletir o atraso de um mês).

Na célula I2 , digitei 12 (para refletir o número de meses para os quais a receita precisa ser linearizada).

O perfil de receita começa em cell AE6 e é aí que estou tendo problemas para determinar qual fórmula usar.

Eu tentei usar uma fórmula de deslocamento, mas não consegui fazê-lo funcionar. Eu não sei como abordar a solução.

    

Answer 1

A fórmula necessária é uma fórmula de matriz complicada ligeiramente .

Aqui está sua planilha com a fórmula que mostra os resultados esperados:

EssafórmulaprecisaserinseridaemmatrizemAE6e,emseguida,preenchida/copiadaecoladaparabaixoeparaadireita,conformenecessário):

{=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))}

Explicação:

A versão prettificada da fórmula é a seguinte:

{=
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
  )
)}

A fórmula é muito mais fácil de entender se você considerar que, por um período de 12 meses e um atraso de 1 mês, o segundo argumento do primeiro INDEX() é aproximadamente equivalente a:

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

A fórmula funciona basicamente gerando uma matriz de deslocamentos para acessar as ordens do número de meses anterior I2 , atrasado por I1 meses, em relação à célula atual.

Percorrer a fórmula em AK6 deve tornar o acima mais claro:

• 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))) é um hack ^* necessário para forçar o Excel a retornar uma matriz para expression , já que o segundo argumento de INDEX() é avaliado como um único valor por padrão. Usando apenas INDEX(6:6,expression) em AK6 levaria a

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

desde que dentro de uma expressão retornando um único valor, COLUMN(multi-cell-range) retorna a coluna da primeira célula do intervalo.

O +(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)) ajusta o intervalo entre as tabelas de pedidos para 2018 e 2019 (coluna R ). Observe que o N(IF( hack não é necessário neste caso, pois o hack usado anteriormente já forçou uma avaliação de array do segundo argumento de INDEX e, portanto, a função COLUMN() foi avaliada para matrizes.

A função IFERROR() é necessária caso a fórmula exista em uma célula próxima ao lado esquerdo da planilha, resultando no acesso ao texto ou na tentativa de acessar uma célula à esquerda da coluna A .

Notas:

• A fórmula prettificada realmente funciona se inserida.
• Os parênteses em torno de (6:6) na versão prettified são necessários para forçar o 6:6 a permanecer em sua própria linha.

Advertências:

• Não pode haver números nas células das n colunas à esquerda das ordens (onde n é especificado pelo valor em I2 ). Se houver algum número lá, a fórmula, como está, os incluirá no cálculo da receita.
• Não pode haver diferença entre as colunas Receita de dezembro de 2018 e Receita de janeiro de 2019. A fórmula pode ser modificada para permitir essa lacuna, se desejado.
• O intervalo entre as duas tabelas de pedidos ( R:R ) deve permanecer exatamente com uma coluna de largura. Caso contrário, a fórmula, como é, irá quebrar.
• O intervalo das tabelas de intercoisas não pode conter números. Caso contrário, eles serão tratados como pedidos adicionais.

_{^* A explicação exata do motivo pelo qual o hack funciona terá que esperar até eu descobrir por mim primeiro; -)}