Este fórum está sendo desativado

[donrafy]

Boa noite pessoal,

estou passando por uma última dificuldade para finalizar meu programa.
Eu tenho um programa que analisa os vãos dentro de propriedades. Se essas propriedades possuem binário 1, significa que posso utiliza-la. Caso contrário preciso esperar até essa propriedade ficar binário 1. A partir disso existem algumas funções que chegam num percentual da situação do meu problema.
Esse resultado está na planilha Resumo.
Esses pontos já estão funcionando muito bem. A análise que gostaria de fazer agora era verificar quais propriedades que ainda não são 1, se tornando binário = 1, aumentam meu percentual mais do que os outros.

Ao verificar minha planilha "proprietários", ao modificar a coluna AB de 0 para 1, automaticamente meu percentual aumenta. Preciso saber quais são os 5 proprietários, que, ao liberar na sequencia, aumentam mais meu percentual do que os outros.

Tentei fazer com SOLVER, mas o número de critérios é muito maior que 100 e de variáveis, maior que 200. Logo não consegui fazer isso.
Então com VBA, tentei fazer com 4 for, mas a planilha sempre travava e não encontrava o resultado.
Minha última tentativa foi usar o open solver, que você pode por mais critérios. Mas está dando problemas também e o algorítimo não espera o novo "resultado" ser calculado, para verificar aquela mudança de proprietários que melhor aumentou o percentual. Sendo assim, usando o opensolver , também não chego a minha solução final.

Link do open solver:

Código: Selecionar todos

https://opensolver.org/

Premissas:
- A coluna que precisa ser modificada de 0 para 1 é a AB da planilha "proprietários"
- Quem é 1 não pode voltar a ser zero.
- Coluna S é apenas uma cópia da coluna AB, para ver quem mudou com um filtro.
- Uma estratégia que usei para saber quantos proprietários foram modificados foi a célula "U1". Esse número deve ser igual a 5.
- Sei que são muitas veiáveis, por isso estou em busca de um resultado que dure em torno de 10 a 20 minutos, será que é possível?

Na planilha vão e resumos contem diversas formulas que calculam o resultado.

[Jimmy]

Olá,

Tenho uma ideia, mas não sei se funciona. Analise e me diga:

A) Percorremos todos os proprietários. Os com 1, nada fazemos. Os com zero, alteramos para 1, anotamos o percentual da situação e o proprietário. Depois disso voltamos ele para zero.
B) Após feito isso em todos os proprietários zero, verificamos de qual proprietário é o maior percentual anotado. Já temos o primeiro dos 5 proprietários a alterar.
C) Colocamos 1 nesse proprietário, e partimos para procurar o segundo.
D) Repetimos o processo desde o passo A), mais 4 vezes, até achar os 5.

Jimmy San Juan

[donrafy]

Olá Jimmy.

pode funcionar para muitos casos, mas não todos.
Qual o problema? Existem vãos que estão relacionados entre si de forma conjunta, ou seja libera-lo sozinho não incrementa de forma significativa o percentual, mas liberar ele e outros vãos em conjunto aumenta muito mais o percentual.
O ideal era tentar o conjunto de 5 de uma vez só, variando todas as possibilidades possíveis e vendo aquele que deu o maior percentual.
Para fazer isso usei 5 for, o que dariam em torno de 380.059.617.807 possibilidades para esse caso.
Infelizmente não consegui rodar o código no meu pc, sempre travava tudo.

é possível fazer esse código (de testar as variáveis) apenas para células filtradas? Filtraria tudo que é zero e Isso iria reduzir minha análise para apenas 120.

[Jimmy]

Bom dia!

Sim, o filtro faz parte da proposta, pois se vamos ativar 5 dos proprietários zerados, iremos atuar exclusivamente em cima dos zerados, ou seja 120.

Se for escolher os 5 que resultariam em maior percentual, deve fazer uma combinação de 120 elementos, 5 a 5, sem repetição.

A fórmula da quantidade de subconjuntos é:

Comb de 120, 5 a 5 = 120! / (5! x (120 – 5)!) = 190 milhões

Em termos de código VBA é fácil, mas o tempo seria elevado, sem condições de estimar quanto, mas poderia chegar a dias.

Acho que você deve fazer uma análise matemática sobre a forma de cálculo do percentual, e tentar eliminar condições, o que permitiria reduzir a quantidade de possibilidades.

Vou chutar algo apenas para ilustrar a ideia, uma vez que desconheço o cálculo: podemos obter inicialmente o percentual de uma por vez, como eu havia proposto antes, escolher os 20% maiores (seriam uns 25 de 120 proprietários zerados), e combinar estes 25, 5 a 5. Esse exemplo daria uns 53 mil, o que poderia demorar algo na casa dos segundos, ou poucos minutos.

Jimmy San Juan

[donrafy]

vamos ver se conseguimos ver outra solução.

Qual o problema de ir de 1 em 1 para tomar como base aquele que libera o maior percentual.

Modificar o proprietário "ao" ou "aq" de forma separada, não incrementa o percentual em nada. (Permanece em 11,78%)
Entretanto modificar os dois de vez, passa de 11,78 % para 13,39 %. (Analisar em conjunto a planilha vão, "vão 57 até vão 64"
Já fiz o teste e verifiquei que existem proprietários singulares que, ao modificar, liberam um percentual maior, mas vão existir situações, no decorrer do tempo que essa situação de exemplo sejam mais representativas.

Talvez o código precise ser feito observando a planilha "vãos".

Como funciona: O percentual apenas sobre se o "gabarito" for 1 entre tipo "X" a "X". Isso acontece se todos os vãos tipo "Y" estejam em 1. Isso faz com que, automaticamente, o gabarito se torne 1 de "X" a "X", aumentando o percentual.
Se entre "X" houver ao menos um "Y" com proprietário 0, não conseguimos pontuar o percentual.

Sera que conseguimos, analisando o vão, fazer uma tabela de percentual na planilha vão, e a partir disso verificar quantos proprietários juntos liberam um percentual, conforme o exemplo acima.
para liberar vão 61, 62, 63 e 64, preciso liberar proprietários ao e aq. Isso me da um percentual de 1,61 %.
O cálculo indireto do percentual pode ser feito somando a dimensão dos vãos (coluna E) entre X. (Nesse exemplo 1.798,93) sobre 87.266,31. Apesar do percentual não ser igual, tem a mesma ordem de crescimento da calculada no resumo.

Fiz duas novas colunas AC e AD mostrando o percentual. Mas seria necessário pensar em uma forma que o percentual so se conte se todos os "Y" sejam "gabarito = 1", ai conta apenas 1 percentual, o do último Y antes do novo "x", e mostrasse quantos proprietários seriam necessários para transformar todos os Y entre X em 1.

Ficou confuso ou deu para entender legal?

[Jimmy]

Não consegui acompanhar.

Acho que só poderei te ajudar se você achar uma saída, e tiver dificuldade em codificar as fórmulas ou VBA.

Desculpe!!

[donrafy]

Fiz uma análise melhor, acredito que de para fazer uma solução via VBA de forma mais fácil.

Explicação:
Planilha "vão": A coluna "AI" representa o percentual que vai ser liberado caso todos os proprietários entre Tipo "X" estejam liberado.
A coluna "AJ" mostra a quantidade de proprietários entre tipo "X".
A coluna "AK" mostra a quantidade de embargos que, se liberados, vão liberar o percentual da coluna "AI"

Idealização da macro:
- Verificar qual tramo que se liberar até 5 proprietários me da o maior percentual. -> Indicar quais proprietários precisam ser liberados.
- Verificar se, liberando 5 pessoas posso liberar mais de 1 tramo e se esse percentual é maior do que o item anterior -> Indicar quais proprietários precisam ser liberados.
- Se não existir conjuntos de proprietários possíveis a serem liberados, fazer a primeira análise proposta:

A) Percorremos todos os proprietários. Os com 1, nada fazemos. Os com zero, alteramos para 1, anotamos o percentual da situação e o proprietário. Depois disso voltamos ele para zero.
B) Após feito isso em todos os proprietários zero, verificamos de qual proprietário é o maior percentual anotado. Já temos o primeiro dos 5 proprietários a alterar.
C) Colocamos 1 nesse proprietário, e partimos para procurar o segundo.
D) Repetimos o processo desde o passo A), mais 4 vezes, até achar os 5.

Comparar os 3 resultados e indicar as 5 melhores indicações de binário na planilha proprietários.

será que é possível fazer isso?

acho que minha maior dúvida é identificar na planilha "vão'' quais proprietários estão com binário zero que podem ser liberados para gerar o maior percentual.

[Jimmy]

Bom dia!

Possível me parece que é. Você só precisa me dizer o que fazer, porque não entendo do assunto, aliás, nem sei que assunto é! Não sei se o proprietário é dono de terrenos, ou de lotes de ações, ou box de carga no porto de Santos, ou se isso faz parte do desenvolvimento de um novo jogo. Nem sei o que é um Tramo kkk.

Vamos tentar ir passo a passo:

- Verificar qual tramo que se liberar até 5 proprietários me da o maior percentual. -> Indicar quais proprietários precisam ser liberados.

Primeiro que diga o que a macro deve varrer, encontrar, registrar, e de que forma. Obtido isso, partimos para o próximo passo da macro, etc.

Procure explicar com detalhes, como:
Varrer a coluna X, dividir em blocos de valores 0 contíguos, para cada bloco combinar 5 a 5, anotar para cada combinação o percentual (só um exemplo).

Mãos a obra!

Jimmy San Juan

[donrafy]

Desculpa a demora, tentei fazer por mim mesmo mas, infelizmente, fracassei.
O que tentei fazer foi:
1. Analisar a coluna "AI" da planilha "vão" e procurar o maior percentual. Verifico na mesma linha a coluna "AK" e vejo se o número de proprietários é <=5.
Se sim, verifico o primeiro "X" (coluna O) e observo o Inicio (coluna F) ( do primeiro "X") e o fim (coluna "M") da linha onde tem o percentual encontrado. (Exemplo, o maior percentual do último anexo foi 6,34 % e a coluna K deu 4, ou seja menor que 5. Logo Busco o inicio (coluna F) do primeiro "x" dessa área (linha 300) e busco o número de fim (coluna M) da linha do percentual (linha 326). A partir desse inicio/fim, busco na planilha proprietários quais proprietários estão contidos nesse inicio/fim que possuem binário = 0 e posso transformar em 1. - Armazeno então o nome dos proprietários e o novo percentual calculado).

Depois procuro o resto dos proprietários que posso liberar. A mesma análise de cima deve ser feita para o número restantes de proprietários (no caso liberou-se 4, então so podemos procurar mais 1 para completar 5.

Se a mesma análise não achar nenhuma possibilidade plausível, fazemos esse passo:

A) Percorremos todos os proprietários. Os com 1, nada fazemos. Os com zero, alteramos para 1, anotamos o percentual da situação e o proprietário. Depois disso voltamos ele para zero.
B) Após feito isso em todos os proprietários zero, verificamos de qual proprietário é o maior percentual anotado. Já temos o primeiro dos 5 proprietários a alterar.
C) Colocamos 1 nesse proprietário, e partimos para procurar o segundo.
D) Repetimos o processo desde o passo A), mais 4 vezes, até achar os 5.

Ficou muito complexo ou é possível?

[donrafy]

Tentei fazer essa parte:

A) Percorremos todos os proprietários. Os com 1, nada fazemos. Os com zero, alteramos para 1, anotamos o percentual da situação e o proprietário. Depois disso voltamos ele para zero.
B) Após feito isso em todos os proprietários zero, verificamos de qual proprietário é o maior percentual anotado. Já temos o primeiro dos 5 proprietários a alterar.
C) Colocamos 1 nesse proprietário, e partimos para procurar o segundo.
D) Repetimos o processo desde o passo A), mais 4 vezes, até achar os 5.

Mas o código finaliza sem executar nada.
Como se não tivesse atualizando os valores nos cálculos. Alguma sugestão de solução?

Segue o códgio:

Código: Selecionar todos

Sub Programa_mundanca()
Application.ScreenUpdating = False
Dim i As Integer
Dim k As Integer
Dim Linha2 As Integer
Dim melhor As Integer
Dim prop As String
Dim poste_m As Integer
Dim vao_m As Integer
Dim FSU_m As Long
Dim FSU_n As Long

Worksheets("proprietários").Activate

k = 2

Linha2 = Sheets("proprietários").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row
        Worksheets("proprietários").Activate
        
FSU_m = Cells(1, 20).Value


Worksheets("proprietários").Activate

    For i = 4 To 6 '(Linha2 - 1)
        
    If (StrComp(Cells(i, 22).Value, "0", vbTextCompare) = 0) Then
    Cells(i, 22).Value = "1"
    End If
    
    FSU_n = Cells(1, 20).Value
    
    Worksheets("proprietários").Activate
    'MsgBox (FSU_m)
    'MsgBox (FSU_n)
    
           If (FSU_m < FSU_n) Then
    
                melhor = i
                poste_m = Cells(1, 21).Value
                vao_m = Cells(1, 22).Value
                prop = Cells(melhor, 26)
                Worksheets("vão").Activate
                Cells(k, 2) = prop
                Cells(k, 3) = FSU_n
                Cells(k, 5) = poste_m
                Cells(k, 6) = vao_m
                k = k + 1
                Worksheets("proprietários").Activate
                
           End If
'Copiar_base
Worksheets("proprietários").Activate
    Next i

'MsgBox (melhor)
'MsgBox ("A liberação de " & Cells(melhor, 26) & " resulta na maior liberação de frente de serviço útil corrosivo.")

'MsgBox (Cells(1, 20).Value)
Worksheets("proprietários").Activate

    Range("X4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("V4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Application.ScreenUpdating = True

End Sub

[Jimmy]

Olá,

É um bom começo. Amanhã (hoje) vou olhar o código e vamos fazer funcionar.

[Jimmy]

Olá donrafy,

Tive uma longa luta com a planilha hoje kkkk. Difícil deixar o processo rápido. A macro demorava uma eternidade pra rodar. Bati muita cabeça até achar o bandido.

Você colocou em várias colunas (abaixo) na planilha de vãos fórmulas matriciais:

P =SE(SOMASES(proprietários!$AB:$AB;proprietários!$T:$T;"<="&vão!F2;proprietários!$U:$U;">="&vão!F2)=1;"1";"0")
S =SE(SOMASES(AA:AA;Y:Y;Y2)/CONT.SES(Y:Y;Y2)<1;0;SOMASES(AA:AA;Y:Y;Y2)/CONT.SES(Y:Y;Y2))
AF =SOMASES($AE:$AE;$O:$O;"Y";Y:Y;Y2)/CONT.SES(Y:Y;Y2)
AG =SOMASES(AA:AA;Y:Y;Y2)/CONT.SES(Y:Y;Y2)
AH =MÁXIMO(SE($Y:$Y=Y2;$AC:$AC))
AI =SE(MÁXIMO(SE($Y:$Y=Y3;$AD:$AD))=MÁXIMO(SE($Y:$Y=Y4;$AD:$AD));0;MÁXIMO(SE($Y:$Y=Y3;$AD:$AD)))
AJ =SOMASES(AA:AA;Y:Y;Y2)/CONT.SES(Y:Y;Y2)
AK =SE(AI2>0;SOMASES(AE:AE;Y:Y;Y2);0)

Quando uma fórmula matricial tem uma faixa que pega toda uma coluna ($Y:$Y ou $AB:$AB, por exemplo), a fórmula percorre todas as 1 milhão de células dessa coluna. A fórmula da coluna S, por exemplo, tem 5 blocos desses, logo, envolve processamento de 5 milhões de linhas. Por isso a planilha estava demorando alguns minutos toda vez que calculava, tornando a execução da macro inviável. O correto é definir a faixa. Eu alterei todos os blocos de coluna por faixa indo da linha 2 até a 388.

Peço que revise as fórmulas que alterei pra ver se está tudo em ordem, se eu deixei algum bloco de coluna toda pra traz, etc. Peço também que verifique porque as alterações de zero para 1 nos proprietários, não está afetando o percentual. Sem isso não dá pra eleger os 5 com maior percentual.

Uma vez resolvido isso, comecei a macro que escolhe os 5 maiores percentuais, mas ainda não terminei, mas em paralelo você pode ir verificando as fórmulas. Tem também que re-ocultar as colunas que desocultei pra verificar as fórmulas, pois não lembro quais eram.

donrafy, peço que nas mensagens acima que julgar que de alguma forma contribuem para a solução da questão, dê seu like clicando na mãozinha. Obrigado.