☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Fórmula para calcular a receita fixa ao longo de 12 meses após um atraso de 1 mês

Estou tendo problemas para elaborar uma fórmula que me permita calcular a receita com base no atraso da receita em 1 mês e, em seguida, na estabilização da receita ao longo de 12 meses.

Esta é minha planilha:

Captura de tela da planilha

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

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

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

A fórmula também precisaria levar em consideração os pedidos de F6:Q9(pedidos de 2018) e de S6:AD9(pedidos de 2019).

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

Na célula I2, eu inseri 12(para refletir o número de meses pelos quais a receita precisa ser estabilizada).

O perfil de receita começa na célula AE6e é aqui que estou tendo problemas para determinar qual fórmula usar.

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

Responder1

A fórmula necessária é umaum poucofórmula de matriz complicada.

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

Captura de tela da planilha

Esta fórmula precisa ser inserida na matriz AE6e, em seguida, preenchida/copiada e colada e à direita, conforme necessá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 embelezada 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 para uma duração 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 um array de deslocamentos para acessar os pedidos do I2número de meses anterior, atrasados ​​por I1meses, em relação à célula atual.


Percorrer a fórmula AK6deve 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, expressionjá que o segundo argumento de INDEX()é avaliado como um único valor por padrão. Usar apenas in levaria 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

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

Os +(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))ajustes para a diferença entre as tabelas de pedidos de 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 INDEXe, portanto, a COLUMN()função é avaliada para arrays.

A IFERROR()função é 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 embelezada realmente funciona se inserida.
  • Os colchetes (6:6)na versão embelezada são necessários para forçar o texto 6:6a permanecer em sua própria linha.

Ressalvas:

  • 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 aí, a fórmula, como está, irá incluí-lo no cálculo da receita.
  • Não pode haver lacuna entre as colunas Receita de dezembro de 2018 e Receita de janeiro de 2019.A fórmula pode ser modificada para permitir tal lacuna, se desejado.
  • A lacuna entre as duas tabelas de pedidos ( R:R) deve permanecer exatamente com a largura de uma coluna. Caso contrário, a fórmula, tal como está, irá falhar.
  • A lacuna entre as tabelas de ordens não pode conter nenhum número. Caso contrário, serão tratados como pedidos adicionais.

* A explicação de por que exatamente o hack funciona terá que esperar até que eu descubra por mim mesmo primeiro ;-)

informação relacionada