☰

Divida o conteúdo da célula em 'n' valores iguais repetidamente e redistribua em células consecutivas

tag-icon • tag-icon microsoft-excel worksheet-function
Divida o conteúdo da célula em 'n' valores iguais repetidamente e redistribua em células consecutivas

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...

Então, quando você vir um valor na linha superior (1000), a linha inferior seráuniformementedividir esse valornvezes até que a soma de todas as divisões seja igual ao valor original.

Eu prefiro evitar usar VB, se possível.

EDITAR1:

Você tem garantia de que qualquer valor encontrado será divisível por n?

Não. Porém, quando a diferença entre a soma e o valor original for < 1, continue.

Você tem garantia de que haverá células adequadas para n divisões?

Não.

Você tem garantia de que haverá apenas um valor diferente de zero?

Não, haverá vários valores. Se outro valor for visto na linha superior e a linha inferior não tiver concluído a divisão, a linha inferior adicionará as divisões antigas e novas.

O valor diferente de zero estará sempre na mesma posição?

Não. Pode aparecer em qualquer posição diferente da primeira.

EDITAR2:

Para ser transparente, estou tentando distribuir uniformemente os grandes custos em dólaresnanos.

Talvez este seja um exemplo melhor: Cronograma de entrada esperada e saída esperada

Observe que:

  • O processo de divisão do Valor de Entrada deverá ocorrer até que a soma das respectivas divisões seja igual ao Valor de Entrada.

  • Se outro valor de entrada for visto quando ainda não terminamos de dividir um valor de entrada anterior, a linha de saída continuará somando as respectivas divisões até que a condição no primeiro ponto seja atendida para cada valor de entrada.

Responder1

EDITAR: Embora a fórmula anterior, desnecessariamente complicada (vejaesta revisão) ainda será útil, por exemplo, se for necessário aplicar um fator de escala móvel às entradas, substituí-o nesta resposta pela versão nova, mais simples e muito mais fácil de entender.

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

Captura de tela da planilha

Insira a seguinte fórmula na matriz B3e 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 embelezada 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}


Basicamente funciona gerando uma A5matriz de deslocamentos de comprimento variável (com base em n, armazenado no meu exemplo) para acessar o n-1 anterior, mais os valores de entrada atuais.

Percorrer a fórmula I3deve tornar o acima mais claro:

  • (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, expressionjá que o segundo argumento de INDEX()é avaliado como um único valor por padrão. Usar apenas in levaria aINDEX($2:$2,expression)I3

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

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)

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 cabeçalho da linha ou na tentativa de acessar uma célula à esquerda da coluna A.

Notas:

  • A fórmula embelezada realmente funciona se inserida.
  • Os colchetes ($2:$2)na versão embelezada são necessários para forçar o texto $2:$2a permanecer em sua própria linha.

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

Responder2

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 nvalor como parâmetro.

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

  • Sub não verifique se os valores nas células podem ser convertidos em números para comparação 0e divisão por nou não.

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

  • Os valores deslocados serão perdidos ao serem movidos além do limite das colunas.

  • E nenhuma otimização.

Use este código como uma ideia.

Responder3

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

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

Recomendo fortemente que você faça isso, pois simplifica muito os cálculos, e também acrescenta muita clareza financeiramente falando: você pode facilmente adicionar colunas para coisas como referência de custo, descrição, tipo de servidor, etc. colunas de preço, 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.

insira a descrição da imagem aqui

  1. Em C2, nomeie a célula _Npara ter uma maneira fácil de acessar a variável 5 yearsem outro lugar
  2. Para seus custos (intervalo A5:D10), insira uma tabela que você possa nomear Costspara facilitar as referências

Aqui estão as fórmulas para usar e, se necessário, arrastar/copiar para a direita (em azul na imagem):

=IFERROR([@[Real Cost]]/_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]))

informação relacionada