■■ Puis-je utiliser Excel pour définir des écarts de points NFL?
|
Beaucoup d’entre nous suivent le basketball, le football, le hockey ou le baseball. Les cotes établissent des écarts de points sur les jeux dans tous ces sports et dans d’autres. Par exemple, la meilleure estimation des bookmakers était que les Colts d’Indianapolis gagneraient le Super Bowl 2010 de 7 points. Au lieu de cela, les New Orleans Saints ont gagné le match. Dans ce chapitre, vous voyez que Solver a prédit que les Saints étaient la meilleure équipe et auraient dû être favorisés. Maintenant, voyez comment Solver peut estimer avec précision la capacité relative des équipes de la NFL.
En utilisant un modèle de solveur simple, vous pouvez générer des écarts de points raisonnables pour les jeux en fonction des scores de la saison 2009. Le travail se trouve dans le fichier, illustré à la figure 1. Vous utilisez le score de chaque match de la saison NFL 2009 comme données d’entrée. La cellule changeante pour le modèle Solver est une note pour chaque équipe et la taille de l’avantage sur le terrain. Par exemple, si les Colts d’Indianapolis ont une note de +5 et que les Jets de New York ont une note de +7, les Jets sont considérés comme deux points mieux que les Colts.
En ce qui concerne l’avantage du terrain, la plupart des années, les équipes de football universitaires et professionnelles, ainsi que les équipes de basket-ball professionnelles, ont tendance à gagner en moyenne de trois points (alors que les équipes de basket-ball à domicile ont tendance à gagner en moyenne de cinq points). ). Dans votre modèle, cependant, vous définirez le bord d’origine comme une cellule changeante et le solveur estimera le bord d’origine. Vous pouvez définir le résultat d’un match de la NFL comme étant le nombre de points par lequel l’équipe à domicile surclasse les visiteurs et prédire le résultat de chaque match en utilisant l’équation suivante (équation 1):
(Points prévus par lesquels l’équipe à domicile surclasse les visiteurs) = (Bord à domicile) + (Note de l’équipe à domicile) – (Extérieur
Note d’équipe) Par exemple, si le bord du terrain est égal à trois points, lorsque les Colts accueillent les Jets, les Colts seront un favori d’un point (3 + 5 – 7). Si les Jets accueillent les Colts, les Jets seront un favori à cinq points (3 + 7 – 5). (Le match Tampa Bay – Nouvelle-Angleterre a été joué à Londres, il n’y a donc pas d’avantage à domicile pour ce match.)
Quelle cellule cible produira des évaluations fiables? L’objectif est de trouver l’ensemble de valeurs pour les cotes d’équipe et l’avantage sur le terrain qui prédit le mieux le résultat de tous les matchs. En bref, vous voulez que la prédiction de chaque match soit aussi proche que possible du résultat de chaque match. Cela suggère que vous voulez pour minimiser la somme sur tous les jeux de (Résultat réel) – (Résultat prévu). Cependant, le problème avec l’utilisation de cet objectif est que les erreurs de prédiction positives et négatives s’annulent. Par exemple, si vous surestimez la marge de l’équipe à domicile de 50 points en un match et sous-estimez marge de l’équipe à domicile de 50 points dans un autre match, la cellule cible donnerait une valeur de 0, indiquant une précision parfaite, alors qu’en fait vous étiez en retard de 50 points par match. Vous pouvez remédier à ce problème en minimisant la somme de tous les jeux en utilisant la formule [(Résultat réel) – (Résultat prévu)] 2. Désormais, les erreurs positives et négatives ne s’annuleront pas. |
|
|
Puis-je utiliser Excel pour définir des écarts de points NFL?
Voyez maintenant comment déterminer des cotes précises pour les équipes de la NFL en utilisant les scores de la saison régulière 2009. Vous pouvez trouver les données pour ce problème dans le fichier , qui est illustré à la figure ci-dessus.
FIGURE 1: Il s’agit des équipes d’évaluation de données de la NFL que vous utiliserez avec Solver.
Pour commencer, placez une valeur d’avantage sur le terrain d’essai dans la cellule B8.
À partir de la ligne 5, les colonnes E et F contiennent les équipes à domicile et à l’extérieur pour chaque match. Par exemple, le premier match (indiqué à la ligne 5) est le Tennessee qui joue à Pittsburgh. La colonne G contient le score de l’équipe à domicile et la colonne H contient le score de l’équipe visiteuse. Comme vous pouvez le voir, les Steelers ont battu les Titans 13-10. Vous pouvez maintenant calculer le résultat de chaque match (le nombre de points par lesquels l’équipe à domicile bat l’équipe visiteuse) en entrant la formule = G5 – H5 dans la cellule I5. En pointant vers la partie inférieure droite de cette cellule et en double-cliquant, vous pouvez copier cette formule vers le dernier jeu de la saison régulière, qui apparaît à la ligne 260. (Soit dit en passant, un moyen facile de sélectionner toutes les données consiste à appuyer sur Ctrl + Maj + flèche vers le bas. Cette combinaison de touches vous amène à la dernière ligne remplie de données – la ligne 260 dans ce cas.) Dans la colonne J, utilisez l’équation 1 pour générer la prédiction pour chaque partie. La prédiction pour le premier le jeu est calculé dans la cellule J5 comme suite:
= $ B $ 8 + RECHERCHEV (E5, $ B $ 12: $ C $ 43,2, FAUX) – RECHERCHEV (F5, $ B $ 12: $ C $ 43,2, FAUX)
Cette formule crée une prédiction pour le premier match en ajoutant l’avantage à domicile au classement de l’équipe locale, puis en soustrayant le classement de l’équipe visiteuse. (Notez que dans la ligne 103, le terme $ B $ 8 est supprimé de la formule car il n’y avait aucun avantage à domicile dans la Nouvelle-Angleterre – Tampa Bay game.) Le terme RECHERCHEV (E5, $ B $ 12: $ C $ 43,2, FAUX) localise le domicile- classement par équipe, et RECHERCHEV (F5, $ B $ 12: $ C $ 43,2, FAUX) recherche la note de l’équipe visiteuse. (Pour plus d’informations sur l’utilisation des fonctions de recherche, consultez l’article 2, «Fonctions de recherche».) Dans la colonne K, calculez l’erreur quadratique (score réel – score prévu) 2 pour chaque partie. Votre erreur quadratique pour le premier jeu est calculée dans la cellule K5 avec la formule = (I5 – J5) 2. Après avoir sélectionné la plage de cellules I5: K5, vous pouvez double-cliquer et copier les formules sur la ligne 260.
Ensuite, calculez la cellule cible dans la cellule K3 en additionnant toutes les erreurs au carré avec le SOMME (J5: J260) formule.
astuce Vous pouvez saisir une formule pour une grande colonne de chiffres comme celle-ci en tapant = SOMME (puis en sélectionnant la première cellule de la plage que vous souhaitez ajouter ensemble. Appuyez sur Ctrl + Maj + flèche vers le bas pour entrer la plage de la cellule que vous avez sélectionnée à la ligne inférieure de la colonne, puis ajoutez la parenthèse fermante.
Il est pratique de rendre la note moyenne de l’équipe égale à 0. Une équipe avec une note positive est meilleure que la moyenne et une équipe avec une note négative est pire que la moyenne. Calculez la note moyenne de l’équipe dans la cellule C10 avec la formule MOYENNE (C12: C43).
Vous pouvez maintenant remplir la boîte de dialogue Paramètres du solveur, comme illustré à la figure ci-dessous.
FIGURE 2 : La boîte de dialogue Paramètres du solveur est configurée pour les classements NFL.
Minimisez la somme des erreurs de prédiction au carré pour tous les jeux (calculées dans la cellule K3) en modifiant le classement de chaque équipe (répertorié dans les cellules C12: C43) et l’avantage à domicile (cellule B8). La contrainte C10 = 0 garantit que la note moyenne de l’équipe est 0. Sur la figure 1, vous pouvez voir que l’équipe à domicile a un avantage de 2,26 points sur l’équipe visiteuse. Les 15 équipes les mieux notées sont présentées dans la figure ci-dessous. N’oubliez pas que les valeurs répertoriées dans la plage de cellules E3: E34 sont calculées par Solver. Dans le fichier modèle, vous pouvez commencer par n’importe quel nombre dans ces cellules, et le solveur trouvera toujours les meilleures notes.
FIGURE 3 : Ce sont les 15 meilleures équipes pour la saison NFL 2009.
Ces notes ont les Saints environ 5 points de mieux que les Colts, donc ce modèle aurait prédit (avant les éliminatoires) que les Saints battraient les Colts de 5 points.
Pourquoi votre modèle n’est-il pas un modèle de solveur linéaire?
Ce modèle n’est pas linéaire car la cellule cible additionne les termes de la (Note d’équipe locale
+ Home-Field Edge – Visiting Team Rating) 2 formulaire. Rappelez-vous que pour qu’un modèle de solveur soit linéaire, la cellule cible doit être créée en ajoutant des termes avec la forme (cellule changeante) * (constante). Cette relation n’existe pas dans ce cas, donc le modèle n’est pas linéaire. Le solveur obtient cependant la bonne réponse pour tout modèle de notation sportive dans lequel la cellule cible minimise la somme des erreurs au carré. Notez que le moteur non linéaire GRG a été choisi car ce modèle n’est pas linéaire et n’implique pas de fonctions non mathématiques telles que les instructions SI. Je n’ai pas sélectionné Rendre les variables non contraintes non négatives car pour que la note de l’équipe soit en moyenne de 0, vous devez autoriser certaines notes de l’équipe à être négatives.
Remarque Récemment, j’ai constaté que le moteur du solveur GRG fonctionne mal lorsque la mise à l’échelle automatique est vérifiée. Je recommande d’ouvrir la boîte de dialogue Options et de désactiver l’option Utiliser la mise à l’échelle automatique.
Comment utiliser Solveur de Microsoft Excel pour évaluer les équipes sportives
Comment utiliser Solveur de Microsoft Excel pour évaluer les équipes sportives
■■ Puis-je utiliser Excel pour définir des écarts de points NFL?
En utilisant un modèle de solveur simple, vous pouvez générer des écarts de points raisonnables pour les jeux en fonction des scores de la saison 2009. Le travail se trouve dans le fichier, illustré à la figure 1. Vous utilisez le score de chaque match de la saison NFL 2009 comme données d’entrée. La cellule changeante pour le modèle Solver est une note pour chaque équipe et la taille de l’avantage sur le terrain. Par exemple, si les Colts d’Indianapolis ont une note de +5 et que les Jets de New York ont une note de +7, les Jets sont considérés comme deux points mieux que les Colts.
En ce qui concerne l’avantage du terrain, la plupart des années, les équipes de football universitaires et professionnelles, ainsi que les équipes de basket-ball professionnelles, ont tendance à gagner en moyenne de trois points (alors que les équipes de basket-ball à domicile ont tendance à gagner en moyenne de cinq points). ). Dans votre modèle, cependant, vous définirez le bord d’origine comme une cellule changeante et le solveur estimera le bord d’origine. Vous pouvez définir le résultat d’un match de la NFL comme étant le nombre de points par lequel l’équipe à domicile surclasse les visiteurs et prédire le résultat de chaque match en utilisant l’équation suivante (équation 1):
(Points prévus par lesquels l’équipe à domicile surclasse les visiteurs) = (Bord à domicile) + (Note de l’équipe à domicile) – (Extérieur
Note d’équipe) Par exemple, si le bord du terrain est égal à trois points, lorsque les Colts accueillent les Jets, les Colts seront un favori d’un point (3 + 5 – 7). Si les Jets accueillent les Colts, les Jets seront un favori à cinq points (3 + 7 – 5). (Le match Tampa Bay – Nouvelle-Angleterre a été joué à Londres, il n’y a donc pas d’avantage à domicile pour ce match.)
Quelle cellule cible produira des évaluations fiables? L’objectif est de trouver l’ensemble de valeurs pour les cotes d’équipe et l’avantage sur le terrain qui prédit le mieux le résultat de tous les matchs. En bref, vous voulez que la prédiction de chaque match soit aussi proche que possible du résultat de chaque match. Cela suggère que vous voulez pour minimiser la somme sur tous les jeux de (Résultat réel) – (Résultat prévu). Cependant, le problème avec l’utilisation de cet objectif est que les erreurs de prédiction positives et négatives s’annulent. Par exemple, si vous surestimez la marge de l’équipe à domicile de 50 points en un match et sous-estimez marge de l’équipe à domicile de 50 points dans un autre match, la cellule cible donnerait une valeur de 0, indiquant une précision parfaite, alors qu’en fait vous étiez en retard de 50 points par match. Vous pouvez remédier à ce problème en minimisant la somme de tous les jeux en utilisant la formule [(Résultat réel) – (Résultat prévu)] 2. Désormais, les erreurs positives et négatives ne s’annuleront pas.
Puis-je utiliser Excel pour définir des écarts de points NFL?
Voyez maintenant comment déterminer des cotes précises pour les équipes de la NFL en utilisant les scores de la saison régulière 2009. Vous pouvez trouver les données pour ce problème dans le fichier , qui est illustré à la figure ci-dessus.
FIGURE 1: Il s’agit des équipes d’évaluation de données de la NFL que vous utiliserez avec Solver.
Pour commencer, placez une valeur d’avantage sur le terrain d’essai dans la cellule B8.
À partir de la ligne 5, les colonnes E et F contiennent les équipes à domicile et à l’extérieur pour chaque match. Par exemple, le premier match (indiqué à la ligne 5) est le Tennessee qui joue à Pittsburgh. La colonne G contient le score de l’équipe à domicile et la colonne H contient le score de l’équipe visiteuse. Comme vous pouvez le voir, les Steelers ont battu les Titans 13-10. Vous pouvez maintenant calculer le résultat de chaque match (le nombre de points par lesquels l’équipe à domicile bat l’équipe visiteuse) en entrant la formule = G5 – H5 dans la cellule I5. En pointant vers la partie inférieure droite de cette cellule et en double-cliquant, vous pouvez copier cette formule vers le dernier jeu de la saison régulière, qui apparaît à la ligne 260. (Soit dit en passant, un moyen facile de sélectionner toutes les données consiste à appuyer sur Ctrl + Maj + flèche vers le bas. Cette combinaison de touches vous amène à la dernière ligne remplie de données – la ligne 260 dans ce cas.) Dans la colonne J, utilisez l’équation 1 pour générer la prédiction pour chaque partie. La prédiction pour le premier le jeu est calculé dans la cellule J5 comme suite:
= $ B $ 8 + RECHERCHEV (E5, $ B $ 12: $ C $ 43,2, FAUX) – RECHERCHEV (F5, $ B $ 12: $ C $ 43,2, FAUX)
Cette formule crée une prédiction pour le premier match en ajoutant l’avantage à domicile au classement de l’équipe locale, puis en soustrayant le classement de l’équipe visiteuse. (Notez que dans la ligne 103, le terme $ B $ 8 est supprimé de la formule car il n’y avait aucun avantage à domicile dans la Nouvelle-Angleterre – Tampa Bay game.) Le terme RECHERCHEV (E5, $ B $ 12: $ C $ 43,2, FAUX) localise le domicile- classement par équipe, et RECHERCHEV (F5, $ B $ 12: $ C $ 43,2, FAUX) recherche la note de l’équipe visiteuse. (Pour plus d’informations sur l’utilisation des fonctions de recherche, consultez l’article 2, «Fonctions de recherche».) Dans la colonne K, calculez l’erreur quadratique (score réel – score prévu) 2 pour chaque partie. Votre erreur quadratique pour le premier jeu est calculée dans la cellule K5 avec la formule = (I5 – J5) 2. Après avoir sélectionné la plage de cellules I5: K5, vous pouvez double-cliquer et copier les formules sur la ligne 260.
Ensuite, calculez la cellule cible dans la cellule K3 en additionnant toutes les erreurs au carré avec le SOMME (J5: J260) formule.
astuce Vous pouvez saisir une formule pour une grande colonne de chiffres comme celle-ci en tapant = SOMME (puis en sélectionnant la première cellule de la plage que vous souhaitez ajouter ensemble. Appuyez sur Ctrl + Maj + flèche vers le bas pour entrer la plage de la cellule que vous avez sélectionnée à la ligne inférieure de la colonne, puis ajoutez la parenthèse fermante.
Il est pratique de rendre la note moyenne de l’équipe égale à 0. Une équipe avec une note positive est meilleure que la moyenne et une équipe avec une note négative est pire que la moyenne. Calculez la note moyenne de l’équipe dans la cellule C10 avec la formule MOYENNE (C12: C43).
Vous pouvez maintenant remplir la boîte de dialogue Paramètres du solveur, comme illustré à la figure ci-dessous.
FIGURE 2 : La boîte de dialogue Paramètres du solveur est configurée pour les classements NFL.
Minimisez la somme des erreurs de prédiction au carré pour tous les jeux (calculées dans la cellule K3) en modifiant le classement de chaque équipe (répertorié dans les cellules C12: C43) et l’avantage à domicile (cellule B8). La contrainte C10 = 0 garantit que la note moyenne de l’équipe est 0. Sur la figure 1, vous pouvez voir que l’équipe à domicile a un avantage de 2,26 points sur l’équipe visiteuse. Les 15 équipes les mieux notées sont présentées dans la figure ci-dessous. N’oubliez pas que les valeurs répertoriées dans la plage de cellules E3: E34 sont calculées par Solver. Dans le fichier modèle, vous pouvez commencer par n’importe quel nombre dans ces cellules, et le solveur trouvera toujours les meilleures notes.
FIGURE 3 : Ce sont les 15 meilleures équipes pour la saison NFL 2009.
Ces notes ont les Saints environ 5 points de mieux que les Colts, donc ce modèle aurait prédit (avant les éliminatoires) que les Saints battraient les Colts de 5 points.
Pourquoi votre modèle n’est-il pas un modèle de solveur linéaire?
Ce modèle n’est pas linéaire car la cellule cible additionne les termes de la (Note d’équipe locale
+ Home-Field Edge – Visiting Team Rating) 2 formulaire. Rappelez-vous que pour qu’un modèle de solveur soit linéaire, la cellule cible doit être créée en ajoutant des termes avec la forme (cellule changeante) * (constante). Cette relation n’existe pas dans ce cas, donc le modèle n’est pas linéaire. Le solveur obtient cependant la bonne réponse pour tout modèle de notation sportive dans lequel la cellule cible minimise la somme des erreurs au carré. Notez que le moteur non linéaire GRG a été choisi car ce modèle n’est pas linéaire et n’implique pas de fonctions non mathématiques telles que les instructions SI. Je n’ai pas sélectionné Rendre les variables non contraintes non négatives car pour que la note de l’équipe soit en moyenne de 0, vous devez autoriser certaines notes de l’équipe à être négatives.
Remarque Récemment, j’ai constaté que le moteur du solveur GRG fonctionne mal lorsque la mise à l’échelle automatique est vérifiée. Je recommande d’ouvrir la boîte de dialogue Options et de désactiver l’option Utiliser la mise à l’échelle automatique.
Initiation à Excel
Fonctions Excel
Excel VBA
Macros VBA Utiles
Plus d'outils
Sur Facebook
Sur YouTube