MP Load – Estudo rápido de carga

Introdução

O MP-Load faz um estudo rápido de carga. O “MP” é em homenagem a Max Planck, um físico do século XX.

O estudo de carga simplificado é baseado em duas hipóteses:

• A bobina vai de pé
• Há somente um tipo de bobina

E testa dois padrões:

1. Retangular

• Zig zag

Há dois elementos, a bobina de papel e o contêiner

O mais legal é que usa apenas geometria de segundo grau, baseado em contas espertas.

Padrão Retangular

O primeiro padrão é o retangular:

Este tem uma solução muito simples.

Número de bobinas na linha e colunas:

• N_comp  = arrend.baixo (ComprimentoC / Dext)
• N_larg= arrend.baixo (LarguraC / Dext)
• Ntotal = N_comp * N_larg

Padrão Zig Zag

O segundo padrão é o zig zag, que pode ou não ser melhor que a configuração retangular.

Dois parâmetros cruciais a serem calculados são o d e o c, que comandarão a disposição das bobinas.

No extremo, c será igual ao Raio (metade do Dext)

Note também que temos um triângulo retângulo, d^2 + c^2 = Dext^2

Outra definição é o das linhas pares e ímpares:

Por convenção, a linha par começa com a bobina no canto superior esquerdo, e a linha ímpar é a que vai no meio das bobinas pares.

Primeiro cálculo: estimar quantas linhas pares e ímpares cabem no contêiner

Arrendondamos para cima o valor (Largura contêiner / dextB), para fazer caber uma fileira a mais, em relação à configuração retangular:

     nl_larg = WF.RoundUp(LarguraC / dextB, 0)  ‘Número de linhas total

Número de linhas par e ímpar

     nl_largOdd = WF.RoundUp(nl_larg / 2, 0) ‘Number odd rows

     nl_largEven = WF.RoundDown(nl_larg / 2, 0) ‘Number even rows

Note que o parâmetro c pode ser calculado a partir da Largura do contêiner.

Tiro um diâmetro externo e uma fileira, e o que restar é múltiplo do valor de c:

 dextB + (nl_larg – 1) *c = LargC

Portanto:

  c = (LarguraC – dextB) / (nl_larg – 1)

  d = Math.Sqr(dextB * dextB – c * c) ‘Por Pitágoras

A observação anterior é válida também para casos em que c é maior que o raio, como o abaixo

A partir dos valores calculados c e d, é possível calcular quantas bobinas entram no comprimento do contêiner

Sub-caso: d> Dext / 2:

            nl_compOdd = WF.RoundDown((ComprimentoC – dextB) / (2 * d), 0) + 1

            nl_compEven = WF.RoundDown((ComprimentoC – dextB – d) / (2 * d), 0) + 1

Sub-caso: d <= Dext / 2:

                nl_compOdd = WF.RoundDown((ComprimentoC – dextB) / (dextB), 0) + 1

                nl_compEven = WF.RoundDown((ComprimentoC – dextB – dextB / 2) / (dextB), 0) + 1

Altura. Para calcular o número de camadas de bobinas que cabem no contêiner, consideramos que uma bobina será empilhada sobre outra, e a conta é arrendondar para baixo AlturaC / LargB

nl_alt = WF.RoundDown(AlturaC / largB)

N. Bobinas Total

Será calculado como número de camadas da altura * (núm linhas pares *núm bobinas nas linhas pares + núm linhas ímpares *núm bobinas nas linhas ímpares)

         n_reels = nl_alt * (nl_largOdd * nl_compOdd + nl_largEven * nl_compEven)

Limite por peso

Há também a possibilidade do limite ser o peso, e não o número de bobinas, mesmo sendo geometricamente possível caber mais carga no contêiner. O algoritmo verifica se o número máximo de bobinas para restringir pelo peso.

Algoritmo completo

O algoritmo completo (escrito em VBA) tem uma série de checagens e outros detalhes de implementação,

e pode ser encontrado na função MaxPlanck_loader do arquivo Excel abaixo.

MaxPlanck_loader(AlturaC As Long, LarguraC As Long, ComprimentoC As Long, MaxCargaC As Double, dextB As Long, largB As Long, pesounitB As Double, resultados As Variant) As Boolean

Versão para download pode ser encontrada em: https://1drv.ms/x/s!Aumr1P3FaK7joGEmWAJLQiqcslZz?e=agdxJQ

Vale notar também que este não é um estudo de carga completo, que considera diferentes tamanhos de carga e outras restrições.

Veja também:

Laboratório de Matemática

Gráfico dinâmico usando Excel

É possível criar gráficos dinâmicos usando o Excel, porém isso não é nativo. É necessário usar um pouco de VBA.

A lógica é usar o comando sleep e atualizar o gráfico, dentro de um loop for. Vide arquivo anexo.

Também é possível usar linguagens como o Python, mas isso envolve programação.

Outras alternativas: há sites online que criam gifs animados, dadas as imagens.

Download no Google Drive

MotionChart.xlsm

Arte com Retângulos em Excel

Descubra como fazer figuras agradáveis com retângulos dispostos aleatoriamente e algumas linhas de código, em Excel – VBA.

Para traçar um único retângulo no VBA, utilizar o comando a seguir:

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 50, 30, 50, 30).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 200)
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)

Este vai traçar um retângulo nas coordenadas (x,y) = (50,30), com largura 50 e altura 30. Cor azul escuro e contorno preto.

Mais uma linha, para dizer a transparência de 70% – isso para destacar a intersecção com cores diferentes.

Selection.ShapeRange.Fill.Transparency = 0.7

Se colocarmos tudo dentro de um loop for, sem aleatoriedade alguma, teremos um grid de retângulos.

For i = 1 To 12
 For j = 1 To 12
  ActiveSheet.Shapes.AddShape(msoShapeRectangle, 50 * j, 30 * i, 50, 30).Select
  Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 200)
  Selection.ShapeRange.Fill.Transparency = 0.7
  Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
 Next j
Next i

Já o resultado fica um pouco mais interessante se acrescentarmos alguma aleatoriedade na disposição e tamanho dos retângulos.

Isso pode ser feito utilizando a função Math.Rnd, que dá um número aleatório entre 0 e 1. No caso, a função mexerá um pouquinho na posição (x,y) e um pouquinho no tamanho (width, height).

For i = 1 To 12
 For j = 1 To 12
  ActiveSheet.Shapes.AddShape(msoShapeRectangle, 50 * j + 4 * Math.Rnd - 2, 30 * i + 4 * Math.Rnd - 2, 50 + 10 * Math.Rnd, 30 + 10 * Math.Rnd).Select
  Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 200)
  Selection.ShapeRange.Fill.Transparency = 0.7
  Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
 Next j
Next i

Algo curioso é que uma disposição totalmente aleatória ou totalmente ordeira fica menos bonita do que uma figura parcialmente ordenada, porém, com um grau de incerteza.

Podemos acrescentar uns blocos de cor amarela, sobrepostos aleatoriamente.

Finalmente, acrestamos mais alguns blocos azuis.

Não chega a ser um quadro de Mondrian, mas dá para brincar um pouco.

O mesmo pode ser feito com outras cores e outras doses de caos / ordem.

Para baixar a planilha, link: ArteRetangulos.xlsm

Confira a versão D3 – Javascript on-line em:

https://asgunzi.github.io/Arte-Retangulos—D3-JS/ArteRetangulos.html

Veja também:

https://ideiasesquecidas.com/

Utilização do OpenSolver com VBA

Introdução

O OpenSolver é um suplemento poderoso do Excel, para problemas de otimização combinatória.

Foi desenvolvido por Andrew Mason e equipe, da Universidade de Auckland (Nova Zelândia) e pode ser encontrado em: https://opensolver.org.

A ideia básica é que o Solver comum, do Excel, tem um limite de utilização (apenas 200 variáveis). Para problemas maiores do que isso, é necessário adquirir uma licença junto à Frontline, empresa desenvolvedora do Solver.

Já o OpenSolver pode utilizar solver open-source, como o CBC. Ou utilizar ferramentas (pagas) ainda mais poderosas, como o Gurobi ou o CPLEX. Dessa forma, abrimos uma gama de aplicações muito maior do que apenas o Solver da Frontline.

O OpenSolver tem um menu de utilização bastante similar ao Solver comum.

Este tutorial mostra como utilizar VBA para acessar e manipular o OpenSolver.

Qual a vantagem de utilizar o VBA? É ter uma flexibilidade maior na formulação, e com isso poder transformar a planilha numa ferramenta – para o usuário leigo, é só clicar num botão e resolver.

O Problema da Mochila

O Problema da Mochila é um clássico da otimização combinatória. Vamos utilizar como exemplo.

Vou fazer uma viagem, e tenho uma mochila. Há diversos itens que posso escolher para levar. Cada item tem um peso e um valor.

Quais itens devo levar?

Quero maximizar o valor que estou levando na mochila, restrito ao peso máximo que consigo carregar.

No Excel, o peso máximo da mochila e os valores e pesos por item devem ser dados de entrada do problema.

O campo “Solução” deve conter 0 ou 1 (indicando não levar ou levar o item). O Peso Total é a soma do peso dos itens escolhidos, e a Função Objetivo é a soma dos valores dos itens escolhidos.

Baixe o arquivo Excel em Knapsack_OpenSolver.xlsm

Primeiro passo: adicionar o OpenSolver nas referências do VBA

Como adicionar o OpenSolver nas referências do VBA?

Ir em Ferramentas -> Referências

Adicionar o OpenSolver e clicar em OK.

Como modelar um problema no OpenSolver com VBA

Vamos utilizar o OpenSolver via VBA, para resolver o problema da mochila.

É importante notar que todo comando que fazemos manualmente tem o equivalente via VBA.

A referência para os comandos pode ser consultada em https://opensolver.org/opensolver-api-reference. Outra forma é consultando direto o código VBA do OpenSolver, ele detalha bem a forma de utilização.

Função Objetivo

No painel do OpenSolver, tem um campo para definir a célula com a função objetivo, e a direção: maximizar, minimizar, ou valor alvo.

O equivalente no VBA é a função SetObjectiveFunctionCell:

SetObjectiveFunctionCell Range(“i5”)

Para minimizar ou maximizar, SetObjectiveSense:

SetObjectiveSense MaximiseObjective

O próprio código, via autocompletar, vai dar as dicas das opções de preenchimento.

Definição de Variáveis

A seguir, podemos definir as variáveis, utilizando o painel do OpenSolver.

O equivalente, via código, é:

SetDecisionVariables Range(“i8:i470”)

E é nesse ponto que o código começa a ficar poderoso. Ao invés do range ser fixo e ser mudado a cada novo cenário, como no exemplo, podemos estabelecer o range de acordo com o tamanho do problema modelado via código.

Definição de Restrições

Para o caso em questão, o primeiro ponto a notar é que as variáveis de decisão devem ser binárias.

E a segunda restrição é a de que o valor carregado deve ser menor do que a capacidade máxima da mochila.

O equivalente VBA é bastante similar, com a utilização do comando AddConstraint:

AddConstraint Range(“i8:i470”), RelationBIN 

AddConstraint Range(“i4”), RelationLE, Range(“c5”)

A notar o parâmetro “RelationLE”, que significa “menor igual”. Há parâmetros para “maior igual”, “igual”, etc, basta consultar as opções no próprio código ou no link de referência dado acima.

Rodar o modelo

Com o modelo formulado, o próximo passo é rodar. Manualmente, isso equivale a clicar no ícone correspondente.

Em código, é utilizar o comando RunOpenSolver:

Dim result As OpenSolverresult

result = RunOpenSolver

O valor retornado será 0, no caso de rodar corretamente, e códigos outros conforme instruções abaixo.

Enum OpenSolverResult
Pending = -4 ‘ Used for solvers that asynchronously and are yet to run
AbortedThruUserAction = -3 ‘ Used to indicate that a non-linearity check was made (losing the solution)
ErrorOccurred = -2 ‘ Indicate an error occured and has been reported to the user
Unsolved = -1 ‘ Indicates a model not yet solved
Optimal = 0
Unbounded = 4 ‘ objective can be made infinitely good
Infeasible = 5 ‘ There is no solution that satisifies all the constraints
LimitedSubOptimal = 10 ‘ CBC stopped before finding an optimal/feasible/integer solution because of CBC errors or time/iteration limits
NotLinear = 7 ‘ Report non-linearity so that it can be picked up in silent mode
End Enum

Uma última dica é resetar o modelo antes do início da macro:

ResetModel

Senão for feito isso, as restrições antigas vão continuar existindo sobrepondo com as novas, o que pode fazer o modelo ficar incorreto.

Conclusão

O OpenSolver é uma ferramenta poderosa. Aliada ao VBA, pode permitir um grau de automação enorme, e resolver uma gama de problemas de Pesquisa Operacional nas empresas e na vida real.

Há diversas outras opções mais avançadas, a fim de permitir modelos complexos. A ideia do tutorial foi fazer o exemplo mais simples e didático possível.

O OpenSolver, em geral, é recomendado para problemas de tamanho médio. Para problemas muito grandes, começa a ter lentidão demasiada na leitura de dados e formulação do problema, pela forma com que este foi construído (fortemente baseada em Excel).

Exemplo prático: um trabalho de otimização de silvicultura, com cerca de 100 mil variáveis, demorava 6 horas para resolver com OpenSolver puro. Migrando para Python (Pyomo) + CBC, o tempo caiu para 40 min, para resposta de mesma qualidade. Com Python  (Pyomo) + Gurobi, o tempo caiu para 4 minutos, para a mesma resposta.

De qualquer forma, cabe ao projetista escolher a melhor ferramenta para o processo em questão, dadas as restrições (custo, tempo de processamento, maturidade do processo). E o OpenSolver figura como um excelente candidato a resolver problemas difíceis, de forma rápida e flexível.

Referências:

About OpenSolver

OpenSolver API Reference

https://en.wikipedia.org/wiki/Knapsack_problem

Como gerar um UUID no Excel?

Num banco de dados, é importante ter um registro único como chave, para evitar que dois registros conflitem.

Como eu posso fazer para ter esse identificador (ID) único para cada registro?

Uma forma é ter um controle de IDs: verificar qual o último número já gravado e gerar um novo número. Talvez sequencial, como no exemplo abaixo.

Porém, para casos onde for difícil ou custoso fazer essa verificação, ou a comunicação central online for complicada, é possível utilizar uma técnica de números aleatórios.

É aí que entra o conceito de UUID, identificador universal exclusivo. É um número de 128 bits representado por 32 dígitos hexadecimais, cada um gerado aleatoriamente.

Algo como:

c3d5d666-4050-4bad-8258-6a0c4f8eb701

Há uma série de protocolos para gerar esse número. Apesar de aleatório, é aleatório dentro desses protocolos.

O site a seguir fornece o serviço que gera um UUID, de forma gratuita.

https://www.uuidgenerator.net/

A tabela ficaria assim, para efeito de comparação:

Há chance de duplicação? Sim, há uma probabilidade maior que zero, porém muito pequena: 1 em 2,7 quintilhões. É mais fácil ganhar na mega sena duas vezes seguidas do que dar um conflito de UUIDs.

Em anexo, uma rotina em Excel – VBA que consulta o site citado acima, gera um UUID aleatório e puxa para uma célula. Foi desenvolvida pelo amigo Bruno Cambria, como um componente de um projeto que tocamos.

Um uso possível pode ser gerar N bancos separados, assíncronos, e juntar depois, com chance quase nula de duplicação. Ciência da computação pura.

Link para saber mais.

https://en.wikipedia.org/wiki/Universally_unique_identifier

Bônus: Review do livro Hacking Digital, com boas dicas e cuidados a serem tomados na transformação digital da companhia.

Acesse em https://www.linkedin.com/posts/arnaldogunzi_hacking-digital-best-practices-to-implement-activity-6924321829675241472-73gh

https://ideiasesquecidas.com/

Respostas dos desafios

1 – Preencher números de 1 a 9 nos espaços vazios, e não repetidos.

Uma solução possível: escrever as equações, testar números de 1 a 9 e ficar com aquelas que atendem as equações (vide macro no anexo).

2 – Preencher números primos de modo a atender as multiplicações.

Método: uma forma é decompor o número nos fatores primos.

4147 = 29*11*13.

Daí, continuar a testar: 29 divide 33611, então 29 deve estar na intersecção entre esta linha e coluna.

Dá para escrever uma rotina que decompõe o número, mas é mais fácil usar o Wolfram Alpha. Exemplo:

prime factors of 4147 – Wolfram|Alpha (wolframalpha.com)

Artigo Bônus:

Why Do Chief Data Officers Have Such Short Tenures? – Tom Davenport

Artigo de Tom Daveport, cita alguns desafios do profissional que lida com dados:

– O escopo do trabalho e prioridades são mal definidos desde o início;

– Há uma alta expectativa para o cargo, porém além de implementar Analytics, são necessárias mudanças transformacionais sobre organizações com grande legado. Há enorme necessidade de change management;

Dicas:

– Começar com uma conexão clara com a estratégia de negócios e casos tangíveis;

– Após os projetos piloto, os CDOs devem implementar produtos escaláveis e sustentáveis para agregar valor às unidades de negócio,

– Balancear quick-wins com iniciativas estruturantes;

– Liderar as transformações organizacionais, tomando o cuidado de não ficar relegado a ações de menor relevância;

Veja também:

https://ideiasesquecidas.com/2022/04/14/por-que-o-chief-data-officer-dura-tao-pouco-nas-empresas

Dois desafioszinhos de preencher números

Um mais fácil, outro mais complicado. É possível usar o Excel, resolver no braço ou utilizar algum outro meio para resolver.

Na sexta feira, compilo e envio as respostas.

1)

Preencher nas casas vazias números de 0 a 9, de modo que o mesmo dígito não se repita, e as fórmulas sejam  verdadeiras

Lembrando que multiplicação tem precedência sobre soma e divisão

2) Esse é um pouquinho mais difícil, mas nem tanto. Preencher as casas vazias com números primos, de modo que as multiplicações deem os resultados indicados nas linhas e colunas.

Como plotar o “Gráfico ternário” em Excel?

Primeiro, o que é isso?

É um gráfico na forma de triângulo, a fim de representar visualmente três dimensões  (eu também não conhecia o nome, o amigo Beto Bisogni me explicou).

Digamos que o ponto seja formado pelo trio (20%, 30%, 50%)

Nota: a soma dos três componentes deve dar 100%.

O gráfico a seguir vai mostrar o ponto, segundo as três dimensões de coordenadas.

Basta preencher os pontos a plotar, e clicar no botão. Macros deve estar ativadas.

O gráfico é simplesmente um gráfico de dispersão, com uma transformação dos pontos para ficar nesse formato triangular.

Vide planilha em

https://1drv.ms/x/s!Aumr1P3FaK7joA7TmAktJgKv14mN

Adaptado de: https://github.com/FrancoSivila/TernaryPlot

Ideias técnicas com uma pitada de filosofia
https://ideiasesquecidas.com/

Dicas de Copiar e colar VBA

Em geral, é melhor carregar tudo na memória, trabalhar e voltar tudo de uma vez só.

A cada manipulação de planilha (como colar dados), perdemos eficiência.

Exemplo simples.

Digamos que eu queira copiar valores de A para coluna B.

Um jeito simples é copiar linha a linha.

    n = Application.WorksheetFunction.CountA(Range(“a2:a100000”))

    For i = 2 To n

        Range(“b” & i) = Range(“a” & i)

    Next i

Uma forma extremamente mais eficiente é copiar tudo numa variant, e posteriormente colar ela.

n = Application.WorksheetFunction.CountA(Range(“a2:a100000”))         

‘Copia a tabela inteira para uma variável variant    

tab1 = Range(“a2:a” & 1 + n)         

‘Cola a tabela duma vez só, começando na célula c2    

Range(“c2”).Resize(UBound(tab1, 1), UBound(tab1, 2)) = tab1      

Neste caso específico, a diferença é de 9s, pouco, porém para manipulações mais complexas, a gente pode ganhar muito tempo, da ordem de dezenas de minutos a cada vez que rodar.    

Baixar a planilha aqui, a fim de comparação.

Ideias técnicas com uma pitada de filosofia
https://ideiasesquecidas.com/

Loja e recomendações
https://ideiasesquecidas.com/loja/

Como deixar a execução do seu script VBA em espera…?

(Enviado pelo amigo Wagner Gurgel do Amaral)

Nos nossos projetos em VBA, de vez em quando precisamos fazer uma chamada a aplicações externas para executar algum outro processo.

Porém, o processo externo pode acabar antes ou depois do resto da aplicação VBA. Como garantir a sincronização?

Pensando nisso, a dica de hoje é como fazer o VBA aguardar esse processo externo ser finalizado.

O código abaixo é bem simples e caso necessite também segue aqui (https://1drv.ms/x/s!Aumr1P3FaK7jn39ra16Dtq8ssIQw). O segredo é utilizar a opção (waitOnReturn) no código.

Link para maiores informações: https://docs.microsoft.com/pt-br/office/vba/api/excel.range.run#syntax

Ideias técnicas com uma pitada de filosofia
https://ideiasesquecidas.com/