Ajustar valores para chegar a uma determinada soma (Excel)

[Cleber Teixeira Soares]

Olá a todos,

 

Faz muito tempo que faço um trabalho manualmente e gostaria de saber se há uma forma de automatizar o processo que vou descrever.

 

Imagine a situação hipotética no Excel (uso o Excel 2003):

 

A1:A5 com valor 1 em todas as células

 

B1:B5 com os respectivos valores: 10, 15, 20, 25, 30

 

C1:C5 a multiplicação dos respectivos resultados ( coluna A x coluna B ): 1x10, 1x20, etc.

 

e finalmente em C6 a soma das multiplicações: no nosso caso 100

 

Eu preciso incrementar (ou decrementar) todos os valores na coluna A o mais uniforme possível para que atinja (ou se aproxime o máximo possível, SEM ULTRAPASSAR) o valor que eu queira na célula C6 (como exemplo quero 150).

 

Há mais um problema adicional que o número de linhas com essas operações de multiplicação nunca é fixo (no exemplo citei 5 linhas somente).

 

Desde já, agradeço a todos que se empenharem nesse problema.

exemplo.xls

[Patropi]

Bom dia Cleber

 

Seja bem-vindo ao fórum!

 

Para facilitar anexe uma planilha de exemplo, demonstrando o resultado esperado.

 

[]s

[Cleber Teixeira Soares]

Feito. Alterei o post principal com um termo que esqueci de citar (que nunca ultrapasse o valor desejado) e anexei um exemplo.

[Visitante]

Olá, Cleber.

 

Servem somente valores inteiros na coluna 'A', ou pode ser com valores fracionários?

 

Por exemplo, substituindo todos os valores na coluna 'A' por "1,5" a soma será 150.

 

E na coluna 'C', inteiros ou fracionários?

 

Outras situações alterando somente um valor da coluna 'A':

1. colocando 6 em 'A1' a soma será 150 ou

2. colocando 3 em 'A4' a soma também será 150

 

E aí, essas soluções servem?

 

Ainda, no post #6 você escreveu "Eu preciso incrementar (ou decrementar) todos os valores na coluna A", no entanto, na planilha de exemplo você alterou somente 3 dos 5 valores. E aí?

 

Até este ponto me parece que você impôs somente 1 critério: aproximar o valor da soma do valor de referência sem ultrapassá-lo. Há mais critérios? Quais?

[Cleber Teixeira Soares]

Olá Osvaldo,

 

Primeiramente, desculpe na demora de responder. Andei viajando e estava impossibilitado de acessar a internet.

 

 

Sobre seu tópico:

 

 

"Servem somente valores inteiros na coluna 'A', ou pode ser com valores fracionários?"

"Por exemplo, substituindo todos os valores na coluna 'A' por "1,5" a soma será 150."

 

RESPOSTA: Na coluna 'A' será somente Valores inteiros, pois estou trabalhando com quantidades.

 

 

"E na coluna 'C', inteiros ou fracionários?"

 

RESPOSTA: Todas as  outras colunas são valores em real que podem ser fracionários, mas não devem ser manualmente alterados.

 

 

"Outras situações alterando somente um valor da coluna 'A':"

"1. colocando 6 em 'A1' a soma será 150 ou"

"2. colocando 3 em 'A4' a soma também será 150"

"E aí, essas soluções servem?"

 

RESPOSTA: Não. Eu preciso distribuir o mais uniforme possível e não posso aumentar somente um valor e deixar discrepante dos outros. Como trabalhei com valores pequenos realmente fica ruim tirar conclusões. Vou anexar nesse post o exemplo real do que eu preciso. Lá você não terá nenhuma dúvida.

 

"Ainda, no post #6 você escreveu "Eu preciso incrementar (ou decrementar) todos os valores na coluna A", no entanto, na planilha de exemplo você alterou somente 3 dos 5 valores. E aí?"

RESPOSTA: Realmente a palavra correta seria 'ALGUNS'.

 

"Até este ponto me parece que você impôs somente 1 critério: aproximar o valor da soma do valor de referência sem ultrapassá-lo. Há mais critérios? Quais?"

RESPOSTA: Para o total não, é só isso. O outro critério seria que nas primeiras parcelas o incremento tem que ser distribuído o mais uniforme possível (deixarei um exemplo real em anexo e acredito que lá já será sanado quaisquer dúvidas).

exemplo 2.xls

[Visitante]

Olá, Cleber.
Se quiser experimentar esta solução que utiliza macro instale o código abaixo em um módulo comum, assim:

1. copie o código daqui
2. a partir da planilha em que estão os dados tecle 'Alt+F11' para acessar o editor de VBA
3. no menu do editor >> Inserir >> Módulo
4. cole o código na janela em branco que vai se abrir
5. feito! 'Alt+Q' para retornar para a planilha e testar

para rodar o código:
6. tecle 'Alt+F8' >> selecione a macro correspondente >> Executar, ou insira um botão na planilha e vincule-o à macro ou vincule-a a um atalho de teclado (Alt+F8 > Opções).

Antes de rodar o código coloque o valor meta em 'C40', ou seja, no exemplo que você disponibilizou no post #5 coloque o valor 896,14 em 'C40'.

funcionamento - o código irá alterar os valores da coluna 'A' em busca do valor meta conforme o critério.

obs. o código é válido para o exemplo do post #5
 

Sub AjustaVariáveis() Dim vm As Double, cv As Range, k As Long, p As Boolean  vm = [C40]  With Range("A5:A34")   .Value = Evaluate(.Address & "*" & Replace(([C40] / [C35]), ",", "."))  End With  For Each cv In Range("A5:A34")   cv.Value = Application.RoundDown(cv.Value, 0)  Next cv   Do Until k = 4    For Each cv In Range("A5:A34")     cv.Value = cv.Value + 1      If [C35] > vm Then       cv.Value = cv.Value - 1      ElseIf [C35] >= vm * 0.9999 Then Exit Sub      Else: p = True      End If    Next cv      If Not p Then       Exit Sub      Else: k = k + 1      End If     p = False   LoopEnd Sub

[Cleber Teixeira Soares]

Caro Osvaldo

 

UAU!!!

 

Você resolveu meu problema de uma forma melhor ainda.

A minha planilha é exatamente como eu enviei (uma de cada lado) e eu usava a segunda.

Com sua macro, eu só preciso da primeira agora.

 

Muitíssimo obrigado por essa solução. Resolvido meu problema.

 

 

Cleber.

[Visitante]

Olá, Cleber.

 

Que bom que ajudou. Grato pelo retorno.

 

Gostaria de saber o desempenho do código se forem colocados outros números na tabela.

 

Se você puder fazer o teste e nos informar, seria legal. Assim, após alterar os números da tabela faça uma solução manual, conforme você vinha fazendo, e uma solução rodando o código e compare os resultados.