Appuyez sur F9 jusqu'à la fermeture - Conseils Excel

Table des matières

Utilisation d'Excel pour résoudre n'importe quel modèle complexe
Étapes spécifiques au problème de natation
Développer une mesure de proximité
Façon lente de résoudre ce problème
Macro pour enregistrer le résultat actuel
Macro pour appuyer plusieurs fois sur F9 et vérifier les résultats
Barre latérale sur ScreenUpdating
Solutions de résolution alternatives
Téléchargez le classeur
Regarder la vidéo

Utilisation d'Excel pour résoudre n'importe quel modèle complexe

Lev est commissaire d'une ligue de natation compétitive. Il écrit: "Je suis le commissaire d'une ligue de natation. Il y a huit équipes cette année. Chaque équipe accueille une rencontre et est l'équipe locale. Une compétition aura 4 ou 5 équipes. Comment organiser le programme pour que chaque équipe nage contre Dans le passé, quand nous avions 5, 6 ou 7 équipes, je pouvais le résoudre en appuyant sur F9 jusqu'à la fermeture. Mais cette année, avec 8 équipes, ça ne sort pas. "

L'une des contraintes est que certaines piscines n'offrent que 4 couloirs, vous ne pouvez donc avoir que 4 équipes lorsque cette piscine accueille le gala. Pour les autres poules, ils peuvent avoir 5, 6 couloirs ou plus, mais la compétition idéale aura l'équipe locale plus quatre autres.

Ma suggestion: appuyez sur F9 plus vite! Pour vous aider: développez une «mesure de proximité» dans votre modèle. De cette façon, lorsque vous appuyez sur F9, vous pouvez garder un œil sur un numéro. Lorsque vous trouvez une «meilleure» solution que la meilleure que vous avez trouvée, enregistrez-la comme meilleure solution intermédiaire.

Étapes spécifiques au problème de natation

Listez les 8 équipes à domicile dans le haut.
Combien de façons de remplir les 4 autres voies?
Énumérez tous les moyens.
Combien de façons de remplir les 3 autres voies (pour les petites salles?). Énumérez tous les moyens.
Utilisez RANDBETWEEN(1,35)pour choisir les équipes pour chaque match.

Notez qu'il existe 35 8 façons d'organiser la saison (2,2 billions). Il serait "impossible" de tout faire avec un ordinateur personnel. S'il n'y avait que 4000 possibilités, vous pourriez toutes les faire, et c'est une vidéo pour un autre jour. Mais avec 2,2 billions de possibilités, il est plus probable que les suppositions aléatoires trouvent des solutions.

Développer une mesure de proximité

Dans le scénario de natation, la chose la plus importante est Est-ce que chaque équipe nage deux fois contre une autre équipe?

Prenez les 8 nombres aléatoires actuels et utilisez des formules pour tracer toutes les correspondances. Dressez la liste des 28 matchs possibles. Utilisez COUNTIFpour voir combien de fois chaque correspondance se produit avec les nombres aléatoires actuels. Comptez combien sont 2 ou plus. Le but est de porter ce nombre à 28.

Objectif secondaire: il y a 28 matchs. Chacun doit se produire deux fois. C'est 56 confrontations qui doivent avoir lieu. Avec 8 piscines et 6 avec cinq voies, vous aurez 68 affrontements. Cela signifie que certaines équipes nageront contre d'autres équipes 3 fois et éventuellement 4 fois. Objectif secondaire: assurez-vous que le moins d'équipes possible ont 4 matchs. Objectif tertiaire: minimiser le Max.

Façon lente de résoudre ce problème

Appuyez sur F9. Regardez le résultat. Appuyez plusieurs fois sur F9 pour voir les résultats que vous obtenez. Lorsque vous obtenez un résultat élevé, enregistrez les 8 entrées et les trois variables de sortie. Continuez à appuyer sur F9 jusqu'à ce que vous obteniez un meilleur résultat. Enregistrez celui-ci en enregistrant les 8 cellules d'entrée et les 3 cellules de résultat.

Macro pour enregistrer le résultat actuel

Cette macro enregistre les résultats dans la ligne suivante.

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 Sub

Macro pour appuyer plusieurs fois sur F9 et vérifier les résultats

Écrivez une macro pour appuyer sur F9 à plusieurs reprises, en enregistrant uniquement les «meilleures» solutions. Arrêtez la macro lorsque vous obtenez les résultats souhaités de 28 & 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 Sub

Barre latérale sur ScreenUpdating

Encadré: Au début, il est "amusant" de regarder les itérations défiler. Mais vous réalisez finalement que vous devrez peut-être tester des millions de possibilités. Faire redessiner l'écran par Excel ralentit la macro. Utilisez Application.ScreenUpdating = False pour ne pas repeindre l'écran.

Chaque fois que vous obtenez une nouvelle réponse ou tous les 1000, laissez Excel redessiner l'écran. Problème: Excel ne redessine pas l'écran à moins que le pointeur de cellule ne bouge. J'ai trouvé qu'en sélectionnant une nouvelle cellule alors que ScreenUpdating est True, Excel repeindrait l'écran. J'ai décidé de l'alterner entre la cellule Counter et les meilleurs résultats à ce jour.

Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").Value

Solutions de résolution alternatives

J'ai considéré de nombreux titres pour cette vidéo: Appuyez sur F9 jusqu'à la fermeture, devinez jusqu'à ce que correct, résolution de force brute, mesure de proximité

Notez que j'ai essayé d'utiliser Solver pour résoudre le problème. Mais Solver n'a pas pu se rapprocher. Il n'a jamais été meilleur que 26 équipes lorsque l'objectif était de 28.

Notez également que toute solution que j'obtiens dans cette vidéo est "de la chance". Il n'y a rien d'intelligent dans la méthode de résolution. Par exemple, la macro ne dit pas: «Nous devrions partir de la meilleure solution à ce jour et faire quelques micro-ajustements». Même si vous obtenez une solution qui n'est qu'à un chiffre, elle appuie à nouveau aveuglément sur F9. Il existe probablement une manière plus intelligente d'attaquer le problème. Mais… en ce moment… pour notre commissaire à la natation, cette approche a fonctionné.