Divida uma quantidade de células 'n' de tempos até 0

Question

O que eu tenho:

n = 5
0, 0, 0, 0, 1000, 0, 0, 0, 0

O que eu quero:

0, 0, 0, 0, 200, 200, 200, 200, 200, 0, 0...

Assim, quando você vê um valor na linha superior (1000), a linha inferior irá uniformemente dividir esse valor n vezes até que a soma de todas as divisões seja igual à valor original.

Eu preferiria evitar usar o VB, se possível.

EDIT1:

Are you guaranteed that any value you encounter will be evenly divisible by n? No, however assume once the sum is < 1, continue

Are you guaranteed that there will be adequate cells for n divisions? No

Are you guaranteed that there will be just one non-zero value? No there will be multiple, if another value is seen in the top row and bottom row has not completed splitting, the bottom row will add old + new splits

Will the non-zero value always be in the same position? No can appear in any position other than the first

EDIT2:

Para ser transparente, estou tentando distribuir uniformemente grandes custos em n anos, talvez este seja um exemplo melhor: Cronograma da entrada esperada e do resultado esperado

Observe que:

• O processo de dividir o Valor de Entrada deve ocorrer até que a soma das divisões respeitadas seja igual ao Valor de Entrada respeitado.

• Se outro valor de entrada for visto quando ainda não tivermos concluído a divisão, a linha Saída continuará a somar os grupos até que a condição no primeiro ponto seja atendida para cada valor de entrada.

Answer 1

EDIT: Embora a fórmula anterior desnecessariamente complicada (veja esta revisão ) ainda seja útil, por exemplo, se um o fator de escala deslizante era necessário para ser aplicado às entradas, eu substituí-lo nesta resposta com a versão nova, mais simples, muito mais fácil de entender.

Aqui está uma solução não VBA que não requer linhas / colunas auxiliares ou tabelas extras:

Array:introduzaaseguintefórmulaemB3e,emseguida,preencha-aàdireita:

{=SUM(IFERROR(INDEX($2:$2,N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1))))/$A$5,0))}

Explicação:

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

{=
SUM(
  IFERROR(
    INDEX(
      ($2:$2),
      N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)))
    )/$A$5,
    0
  )
)}

A fórmula é muito mais fácil de entender se você considerar que para n = 5, o segundo argumento do primeiro INDEX() é efetivamente equivalente a:

COLUMN()-{0,1,2,3,4}

Ele basicamente funciona gerando um array de comprimento variável (baseado em n, armazenado em A5 no meu exemplo) para acessar o n-1 anterior, mais os valores de entrada atuais.

Percorrendo a fórmula em I3 deve-se esclarecer:

• (COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1) → {1,2,3,4,5}-1 → {0,1,2,3,4}
• COLUMN()-{0,1,2,3,4} → {9}-{0,1,2,3,4} → {9,8,7,6,5}
• N(IF(1,{9,8,7,6,5})) → N({9,8,7,6,5}) → {9,8,7,6,5}
• (INDEX($2:$2,{9,8,7,6,5})/$A$5 → {100000,0,0,0,7}/5 → {20000,0,0,0,1.4}
• SUM(IFERROR({20000,0,0,0,1.4},0)) → 20001.40

INDEX($2:$2,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($2:$2,expression) em I3 levaria a

INDEX($2:$2,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)) → INDEX($2:$2,9-(COLUMN($A$3:$E$3)-1)) → INDEX($2:$2,9-(1-1)) → INDEX($2:$2,9) → 100000

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

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 cabeçalho da linha 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 ($2:$2) na versão prettified são necessários para forçar o $2:$2 a permanecer em sua própria linha.

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

    

Answer 2

Código:

Sub Divide(n As Integer)
Dim temp(), i As Integer, j As Integer, cnt As Integer, tmp

' Copy values from row to array
temp = Rows(Selection.Row).Value

' Search for the last cell. Use SpecialCells instead is the better variant.
For i = UBound(temp, 2) To LBound(temp, 2) Step -1
    If Not IsEmpty(temp(1, i)) Then
        cnt = i
        Exit For
    End If
Next i

' Perform main operation
For i = cnt To 1 Step -1
    If Rows(Selection.Row).Cells(1, i).Value <> 0 Then
        tmp = Rows(Selection.Row).Cells(1, i).Value
        Rows(Selection.Row).Cells(1, i).Delete Shift:=xlShiftToLeft
        For j = 1 To n
            Rows(Selection.Row).Cells(1, i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Rows(Selection.Row).Cells(1, i).Value = tmp / n
        Next
    End If
Next

End Sub

• Sub aceita o valor n como um parâmetro.

• Sub processa a linha com a seleção (canto superior esquerdo da) em.

• Sub não verifica se os valores nas células podem ser convertidos em números para comparar com 0 e dividir por n ou não.

• Outros erros não são verificados também.

• Os valores deslocados serão perdidos quando forem movidos sobre o limite de colunas.

• E sem otimização alguma.

Use esse código como uma ideia.

    

Answer 3

Aqui está uma solução de trabalho sem a necessidade de usar o VBA.

A única diferença é que você precisaria adicionar uma tabela em algum lugar para inserir cada custo individualmente.

Recomendo enfaticamente que você faça isso, pois ele simplifica muito os cálculos e também acrescenta muita clareza financeira: você pode adicionar colunas facilmente para itens como referência de custo, descrição, tipo de servidor etc. quantidade e colunas de preço base, se você tiver vários itens. Você pode estender isso e produzir relatórios interessantes, e é muito mais fácil do que escrever VBA, IMO.

As células amarelas representam a entrada.

1. EmC2,nomeieacélula_Nparaterumamaneirafácildeacessaravariável5yearsemoutrolugar
2. Paraseuscustos(intervaloA5:D10),insiraumatabelaquevocêpodenomearCostsparareferênciasmaisfáceis

Aquiestãoasfórmulasparausare,senecessário,arrastar/copiarparaadireita(emazulnacapturadetela):

=IFERROR([@[RealCost]]/_N;0)=IF([@From]>0,[@From]+_N-1,"")

=SUMPRODUCT((B$13=Costs[From])*(Costs[Real Cost]))

=SUMPRODUCT((B$13>=Costs[From])*(B$13<=Costs[Until])*(Costs[Yearly Cost]))