Usando o Excel para resolver qualquer modelo complexo
Lev é comissário de uma liga de natação competitiva. Ele escreve: "Sou o comissário de uma liga de natação. Há oito times este ano. Cada time hospeda um encontro e é o time da casa. Um encontro terá 4 ou 5 times. Como organizar o cronograma para que todos os times nadem contra todas as outras equipes duas vezes? No passado, quando tínhamos 5, 6 ou 7 equipes, eu poderia resolver pressionando F9 até o final. Mas este ano, com 8 equipes, não está saindo. "
Uma das restrições é que alguns pools oferecem apenas 4 pistas, então você só pode ter 4 equipes quando esse pool hospeda a gala. Para outras piscinas, eles podem ter 5, 6 ou mais raias, mas o encontro ideal terá o time da casa mais quatro outras.
Minha sugestão: pressione F9 mais rápido! Para ajudar com isso: desenvolva uma "medida de proximidade" em seu modelo. Dessa forma, ao pressionar F9, você pode ficar de olho em um número. Quando você encontrar uma solução "melhor" do que a melhor que você encontrou, salve-a como a melhor solução intermediária.
Etapas específicas para o problema de natação
- Liste os 8 times da casa no topo.
- Quantas maneiras de preencher as outras 4 pistas?
- Liste todas as formas.
- Quantas maneiras de preencher as outras 3 pistas (para locais pequenos?). Liste todas as formas.
- Use
RANDBETWEEN(1,35)para escolher times para cada partida.
Observe que existem 35 8 maneiras possíveis de organizar a temporada (2,2 trilhões). Seria "impossível" fazer tudo isso com um PC doméstico. Se houvesse apenas 4000 possibilidades, você poderia fazer todas elas, e esse é um vídeo para outro dia. Mas com 2,2 trilhões de possibilidades, adivinhar aleatoriamente tem mais probabilidade de encontrar soluções.
Desenvolva uma medida de proximidade
No cenário de natação, o mais importante é: Cada equipe nada contra todas as outras duas vezes?
Pegue os 8 números aleatórios atuais e use fórmulas para marcar todas as correspondências. Liste as 28 combinações possíveis. Use COUNTIFpara ver quantas vezes cada partida está acontecendo com os números aleatórios atuais. Conte quantos são 2 ou mais. O objetivo é aumentar esse número para 28.
Objetivo secundário: há 28 confrontos. Cada um precisa acontecer duas vezes. São 56 matchups que precisam acontecer. Com 8 pools e 6 com cinco raias, você terá 68 matchups ocorrendo. Isso significa que algumas equipes nadarão contra outras equipes 3 vezes e possivelmente 4 vezes. Objetivo secundário: Certifique-se de que o menor número possível de equipes tenha 4 partidas. Objetivo terciário: Minimize o Max.
Maneira lenta de resolver isso
Pressione F9. Veja o resultado. Pressione F9 algumas vezes para ver quais resultados você está obtendo. Quando você obtiver um resultado alto, salve as 8 entradas e as três variáveis de saída. Continue pressionando F9 até obter um resultado melhor. Salve este registrando as 8 células de entrada e as 3 células de resultado.
Macro para salvar o resultado atual
Esta macro salva os resultados na próxima linha.
Sub SaveThis() NR = Range("Z1048576").End(xlUp).Row + 1 Cells(NR, 26).Resize(1, 11).Value = Array(Range("c8").Value, _ Range("D8").Value, Range("E8").Value, Range("F8").Value, _ Range("G8").Value, Range("H8").Value, Range("I8").Value, _ Range("J8").Value, Range("O1").Value, Range("P1").Value, _ Range("Q1").Value) End SubMacro para pressionar F9 repetidamente e verificar os resultados
Escreva uma macro para pressionar F9 repetidamente, registrando apenas soluções "melhores". Faça a macro parar quando você chegar aos resultados desejados de 28 e 0.
Sub TrySome() NR = Range("Z1048576").End(xlUp).Row + 1 Ctr = Range("T1").Value Application.ScreenUpdating = Range("AH2").Value SolutionFound = False GoAgain: ActiveSheet.Calculate Ctr = Ctr + 1 UseIt = 0 If Range("O1").Value> Range("AK1").Value Then UseIt = 1 ElseIf Range("O1").Value = Range("AK1").Value Then If Range("P1").Value 300 Then Application.ScreenUpdating = True Exit Sub End If If SolutionFound = True Then Application.ScreenUpdating = True Exit Sub End If If Ctr Mod 1000 = 0 Then Range("T1").Value = Ctr Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").Value End If GoTo GoAgain End SubBarra lateral sobre o ScreenUpdating
Barra lateral: a princípio, é "divertido" observar o desenrolar das iterações. Mas você finalmente percebe que pode ter que testar milhões de possibilidades. Ter o Excel redesenhado na tela retarda a macro. Use Application.ScreenUpdating = False para não redesenhar a tela.
Cada vez que você obtiver uma nova resposta ou a cada 1000, deixe o Excel redesenhar a tela. Problema: o Excel não está redesenhando a tela, a menos que o ponteiro da célula se mova. Descobri que, ao selecionar uma nova célula enquanto ScreenUpdating for True, o Excel pintaria novamente a tela. Decidi alternar entre a célula do contador e os melhores resultados até agora.
Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").ValueSoluções alternativas de solução
Considerei muitos títulos para este vídeo: pressione F9 até fechar, adivinhe até correto, resolução de força bruta, medida de proximidade
Observe que tentei usar o Solver para resolver o problema. Mas o Solver não conseguiu chegar perto. Nunca melhorou do que 26 equipes quando o objetivo era 28.
Observe também que qualquer solução que eu obtiver neste vídeo é "sorte estúpida". Não há nada inteligente sobre o método de solução. Por exemplo, a macro não diz: "Devemos começar pela melhor solução até agora e fazer alguns micro-ajustes." Mesmo que você obtenha uma solução que esteja a apenas um número de distância, ele pressiona F9 novamente às cegas. Provavelmente existe uma maneira mais inteligente de atacar o problema. Mas … agora … para nosso comissário de natação, essa abordagem funcionou.
Baixe a apostila
Assistir vídeo
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2180.zip
