Ir ao conteúdo
  • Cadastre-se
Cleber Teixeira Soares

RESOLVIDO Ajustar valores para chegar a uma determinada soma (Excel)

Recommended Posts

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

Compartilhar este post


Link para o post
Compartilhar em outros sites

Bom dia Cleber

 

Seja bem-vindo ao fórum!

 

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

 

[]s

Compartilhar este post


Link para o post
Compartilhar em outros sites

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?

Compartilhar este post


Link para o post
Compartilhar em outros sites

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

Compartilhar este post


Link para o post
Compartilhar em outros sites

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
  • Curtir 1

Compartilhar este post


Link para o post
Compartilhar em outros sites

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.

Compartilhar este post


Link para o post
Compartilhar em outros sites

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.

Compartilhar este post


Link para o post
Compartilhar em outros sites

Crie uma conta ou entre para comentar

Você precisar ser um membro para fazer um comentário

Criar uma conta

Crie uma nova conta em nossa comunidade. É fácil!

Crie uma nova conta

Entrar

Já tem uma conta? Faça o login.

Entrar agora





Sobre o Clube do Hardware

No ar desde 1996, o Clube do Hardware é uma das maiores, mais antigas e mais respeitadas publicações sobre tecnologia do Brasil. Leia mais

Direitos autorais

Não permitimos a cópia ou reprodução do conteúdo do nosso site, fórum, newsletters e redes sociais, mesmo citando-se a fonte. Leia mais

×