Este fórum está sendo desativado

[Martins]

Olá, estou com um problema de combinação de valores e não estou conseguindo resolver.

Em meu exemplo na coluna "B" tenho vários valores analíticos e na coluna "C" valores sintéticos em que os valores da coluna "C" representam a soma dos valores analíticos em "B", assim o objetivo é que a macro faça a combinação de dados para identificar quais valores em A representam o valor total em "C",

Essa identificação poderia ser, como por exemplo cor de célula, cor interior da célula ou outra.

[Jimmy]

Olá Martins,

Esclareça alguns pontos. Você falou que há valores nas colunas "B", "C", e "A", mas na planilha não há valores na coluna "A".

Falou também que os valores sintéticos estão em "C", mas depois falou que em "C" está a soma dos valores sintéticos. Não seriam dos analíticos?

Poderia revisar a descrição do problema?

Jimmy San Juan

[Martins]

Oi Jimmy.

Foi um erro de edição. Já ajustei a mensagem. Então tenho duas colunas com valores, em "B" e "C", onde "C" representa valores analíticos inseridos em "B". Eu tentei fazer isso com solver, mas não consegui, pois não é uma questão de células variáveis, mas fazer uma combinação dos valores em "B" para chegar ao valor de "C". Mas a macro deve mostrar quais valores de "B" para encontrar valores de "C".

Espero ter conseguido transmitir a ideia.

[Jimmy]

Martins,

Estou tentando fazer uma macro para isso. Hoje ainda te passo.

Dúvida: os Valores Analíticos que geraram um determinado valor sintético, estão sempre em linhas acima da linha do sintético? Há alguma ordem, ou é possível, por exemplo, que um dos analíticos do primeiro sintético esteja lá pelas linhas finais?

Jimmy San Juan

[Jimmy]

Martins,

Segue uma planilha preliminar, que atende ao seu exemplo, mas não atende a todos os casos.

Na Planilha2 eu coloquei um exemplo que a macro não resolve, mas que tem solução (está nas colunsa E e F). O problema todo é a disposição dos valores analíticos, por isso te perguntei sobre isso.

Também gostaria de saber a quantidade máxima de valores analíticos que vão existir, porque o tempo de procura cresce exponencialmente com a quantidade de VAs.

Teste, e vamos tentando melhorar.

Jimmy San Juan

[Martins]

Olá Jimmy.

Respondendo sua pergunta. Sim os valores analíticos de "B" estarão sempre acima de "C".

Analisarei sua macro, mas a princípio parece que atende minha necessidade.

Então o meu muito obrigado pela ajuda. Incrível, pois pensei que não seria possível fazer isso.

[Jimmy]

Eu avancei naquela macro, sem ter lido esta sua última mensagem, e consegui contornar o problema que eu mesmo te mostrei na Planilha2.

A macro 2 acha a solução, mas os valores ficam um pouco bagunçados, ou seja, um VS pega valores da região de outro VB.
O raciocínio é o seguinte: para formar a soma do primeiro VS, a macro procurará usar os maires valores de VA, deixando os menores para os VS seguintes.

A lógica é mais ou menos essa: "Não pague a primeira compra com o dinheiro trocado, porque pode ser que, mesmo tendo o dinheiro, não tenha notas miúdas para as demais compras, Pague a primeira compra com as maiores cédulas possível, deixando o trocado para as compras posteriores."

Se notar no exemplo que dei, verá que é possível atender a todos os VS, mas o primeiro usou o 20 e o 30, que fizeram falta para os demais VS. Se o primeiro tivesse usado o 50 ao invés de 20+30, todos os VS estariam atendidos.

Se te interessar testar isso, posso te passar.

Se não interessar, encerramos.

Jimmy San Juan

[Martins]

Oi Jimmy

Para ter certeza que me atenderá colei uma pequena parte do meu relatório na planilha.

A macro encontrou apenas dois dos 4 valores em "C". Assim pergunto se sabes o motivo da macro não ter identificado 13.197,78 e 26.312,26.


Acho que entendi o que colocaste. Em tese, os valores de "C" devem estar nas linhas superiores em "B". Por exemplo, se tenho 1.000 em "C" na linha 10, os valores de 1.000 estão nas linhas 1 a 9. Não poderiam estar na linha 10 em diante.



Se possível, poderias fazer com que a busca inicie a partir da linha 2, pois na 01 será inserido textos.

Muito obrigado.

[Jimmy]

Bom dia,

A macro encontrou apenas dois dos 4 valores em "C". Assim pergunto se sabes o motivo da macro não ter identificado 13.197,78 e 26.312,26.

Sei o motivo sim. As vezes o excel, ao fazer uma soma, comete um erro na última casa decimal, ou seja, algo como 0,000000000001 de diferença. Isso faz que com a comparação se a soma é igual ao valor procurado, resulte em NÃO É IGUAL. Já alterei a macro para considerar uma diferença de 0,00000001 como sendo números igual. Agora ela encontra o valor.

Quanto às cores, uma das planilhas é uma tabela de cores. Cada cor têm um código, e a lista de códigos de cores a serem utilizados, e sua respectiva ordem, está dentro da macro. Isso você pode alterar quando quiser, para obter outras cores e/ou outra ordem. Se não souber como fazer isso, me avise que te explico.

Notei que a macro demorou muito para chegar à conclusão de que não encontrou a soma daqueles 2 números, isso antes de eu corrigir a macro. Essa demora é porque ao encontrar uma soma, a macro aborta a procura pois já encontrol, não segue até a última combinação de valores, e exclui os valores analíticos somados das possibilidades para a soma do valor sintético seguinte. Diminuindo a quantidade de valores, a combinação de valores é menor, e a macro roda mais rápido. Ao não encontrar um valor (por erro de digitação, por exemplo) a macro testa até a última combinação pra ver se encontra a tal da soma, e isso leva tempo, pois a quantidade de combinações do exemplo que você deu (25 valores analíticos) é de 33 milhões de combinações.

Esse último exemplo seu, quando ainda não estava achando a soma, demorou mais de 1 hora!!!!!

Estou tentando fazer alterações na macro (já tenho a ideia do que fazer) para melhorar essa performance.

Jimmy San Juan

P.S. Esqueci de alterar a linha inicial de 1 para 2. Você tem noções básicas de como alterar o conteúdo de uma macro?

[Martins]

Olá Jimmy. Muito obrigado pelo retorno.

Eu tenho noção sim, faz alguns meses que estou estudando bastante, mas vejo que há muita coisa pela frente.

Eu ia comentar com você justamente pela demora em obter um resultado. Sei que as combinações são muitas, mas seria muito bom que isso fosse mais rápido.

Para ter uma ideia, em meu caso, utilizarei esta macro para conciliar valores em que seja faturado várias notas a um cliente e o recebimento se faz em lote, assim essa macro ajudaria bastante.

Diante do exposto farei a análise da macro. Se você conseguir retornar sobre os pontos de melhoria seria ótimo, mas sei também que isso te toma tempo e já sou muito grato pelo que fizeste.

[Jimmy]

Ok Martins,

Assim que tiver algo pronto na questão da performance, te aviso.
Quanto aos valores a serem somados estarem sempre em linha superior à linha do valor sintético, creio que a principio você disse que sim, mas depois passei a ter dúvida sobre se isso é fato ou não. Se for, a rapidez no resultado aumenta, porque isso faz diminuir a quantidade de combinações de valores.
Me diga se posso contar com isso, ou se não posso.

Eu te perguntei do seu conhecimento com macros apenas para saber se é tranquilo pra você alterar o nome da coluna ou linha inicial dentro da macro.

Não precisa agradecer, porque faço porque que gosto... aliás, você já tem agradecido clicando na mãozinha, e isso é muito legal!

[Martins]

Olá Jimmy.

Pode considerar que os valores analíticos virão antes dos sintéticos. É possível que ocorra exceções, mas isso, se ocorrer será irrelevante.

[Jimmy]

Respondendo sua pergunta. Sim os valores analíticos de "B" estarão sempre acima de "C".

Pode considerar que os valores analíticos virão depois dos sintéticos. É possível que ocorra exceções, mas isso, se ocorrer será irrelevante.

Martins,

Fiquei confuso com os parágrafos acima.

Aproveito pra perguntar, quantos valores analíticos terá no máximo?

[Martins]

Oi Jimmy.

Manifeste-me mal. A mensagem acima já foi editada:

Pode considerar que os valores analíticos virão antes dos sintéticos. É possível que ocorra exceções, mas isso, se ocorrer será irrelevante.

Com relação ao número de registros analíticos, estes poderão ser muitos, como por exemplo 30.000, mas como isso deve pesar muito no que tange a validação você pode considerar um número reduzido tam como 5.000 ou menos.

Uma pergunta; É possível fazeres para validar em 5.000 ou 1.000, mas se eu precisar aumentar esta busca para 30.000, por exemplo, eu consiga fazer isso editando a macro? Se isso for possível acredito que será a melhor alternativa.

[Jimmy]

Olá Martins,

30.000 linhas, ou mesmo 1.000 é muita coisa pra um software que procura a soma porque a quantidade de combinações sobe exponencialmente.

Se você tiver um arquivo com quantidade e valores reais de VAs e VSs pra me mandar, ajudaria bastante.

O fato dos VA estarem em linha igual ou acima do VS, ajuda muito pois reduz drasticamente a quantidade de VA a serem pesquisados.
Ok, mas como fica para os VS finais da lista? Para eles serão pesquisados todos os VAs anteriores? Não. A medida que os VSs vão sendo encontrados, os VAs que gereram sua soma, vão sendo eliminados da procura. Isso faz com que cada VS tenha poucos VAs a serem pesquisados? Não, porque nos teus exemplos, há VA soltos, que não pertencem a nenhum VS, e nessas 5000 linhas devem haver algumas dezenas desses, e esse é o problema. No final desses 5000 terão sobrados muitos VAs que não tem VS correspondente (vamos chama-los de VAs inúteis), mas a macro não sabe que ele não tem, e vai tentar usa-los na soma até o final. Esse é o problema. Se a quantidade de VAs passar de 25 ou 30, a macro se torna inviável pela demora no processamento. Ela até aguenta listas de 100mil linhas ou mais se quiser, mas a demora será de séculos.

A situação ainda piora se você digitar um valor de VS errado, um que não haja soma possível, porque quando uma soma é encontrada, o processo de busca daquela soma é encerrado sem se ter explorado todas as possibilidades. Se a procura for por uma soma que nunca será encontrada, a macro vai tentar cada possibilidade, e para 1000 linhas vai travar.

Estou terminando as alterações da macro, e vou te mandar para testes, mas já adianto que essa nova informação da quantidade de linhas, inviabiliza a macro.

Jimmy San Juan

[Martins]

Olá Jimmy.

Entendi sua explicação. Realmente aprendi bastante sobre esta lógica.

Se necessário ainda depois do que colocaste poderei enviar um arquivo teste, sim.

Acredito que depois da tua explicação eu devo gerar arquivos em períodos menores, mas eu corro o risco de um valor sintético não ser conciliado devido um analítico estar em período não gerado no arquivo.

Imagina que gerarei um arquivo mensal ou quinzenal, mas para ter certeza da conciliação, devido ainda ter valores não conciliados eu deveria gerar trimestral, por exemplo.

Então farei isso, gerarei arquivos menores sabendo que alguns poderão não ser conciliados, mas fazendo desta forma esta macro já ajudará bastante na otimização do processo.

[Jimmy]

Mande o arquivo sim, mas não com dados fictícios. Apague qualquer tipo de referência a clientes, mas mande os valores reais, se possível, já conciliado. Quero ver como se distribuem os valores, suas quantidades, o quanto os VAs estão distantes dos VSs, a quantidade média de VAs por VSs tem, a quantidade de VAs sem um VSs correspondente, etc.

[Martins]

Olá Jimmy.

Anexo o arquivo original. Apenas excluir uma coluna com informações do cliente.

Observei por cima que alguns valores sintéticos não fecham com o analítico. Acredito que seja pelo fato que há recebimentos com juros e eventual multas, mas isso acredito que a lógica de programação não conseguirá ajudar.

Obrigado.

[Jimmy]

Martins,

Desculpe, mas não vejo como a macro pode resolver isso, não pela lógica, mas pelo tempo que iria tomar.

Espero poder te ajudar mais numa próxima oportunidade.

Jimmy San Juan

[Martins]

Entendo perfeitamente Jimmy.

Me ajudasse demais.

Muito obrigado mesmo.