Partage pratique de compétences Excel : Apprenez à réaliser des tableaux statistiques de classement multifactoriel ?

Dans l'article précédent « Partage pratique de compétences Excel : deux méthodes pour ajouter des images d'arrière-plan aux tableaux », nous avons appris à utiliser les images d'arrière-plan des tableaux. Aujourd'hui, nous allons parler du tableau de statistiques et apprendre à faire un tableau de statistiques de classement multifactoriel en regardant la Coupe du Monde. Venez jeter un oeil !

La compétition féroce de la Coupe du monde organisée tous les quatre ans bat son plein. Tous les fans applaudissent leur équipe préférée et sont plus préoccupés par la performance du groupe de cette équipe. Avec la fin du premier tour, la phase de groupes atteint son paroxysme et chaque but marqué par une équipe du même groupe peut affecter le résultat global. Bien que l'information médiatique soit désormais très développée, les actualités et les prévisions de développement peuvent être trouvées partout, mais il est sans aucun doute très pratique si vous pouvez disposer d'une feuille de calcul capable de calculer et de prédire les résultats du classement du groupe après avoir saisi les données. Je la partagerai avec vous. aujourd'hui. Un tel tableau de statistiques de performances de jeu.

L'effet final du tableau est le suivant :

Fonction : Tant que vous saisissez les résultats du concours dans la zone jaune, divers indicateurs et le classement final seront automatiquement calculés.

Valeur :

①Statistiques en temps réel des données de bataille

②Classements prévus

Bien sûr, la signification la plus importante est : apprenez de nombreux points de connaissances détaillés grâce aux idées de conception de ce tableau si vous rencontrez le problème du calcul. le classement complet de votre travail Toutes les questions peuvent être utilisées à titre de référence. Jetons un coup d’œil au processus de production spécifique.

Parce que la structure de chaque groupe est complètement la même, nous prenons uniquement les données du groupe A comme exemple d'explication. La production de l'ensemble du tableau est divisée en trois étapes : détermination de l'idée générale, conception de la mise en page et conception de la formule.

1. Déterminer l'idée globale

Avant de concevoir le formulaire, il est nécessaire de trier les règles et exigences pertinentes, de clarifier quelles données de base peuvent être utilisées et quels résultats sont nécessaires à la fin, puis concevoir les maillons intermédiaires.

Dans ce tableau de statistiques de performance, nos données de base sont les deux chiffres après chaque match (le nombre de buts marqués par les deux équipes) ; le résultat final requis est le classement du groupe ;

Comment obtenir le classement en fonction des résultats de chaque jeu, vous devez également comprendre les règles de classement :

L'image ci-dessus est l'explication officielle, qui est une règle de calcul relativement compliquée. Nous pouvons résumer cette règle. dans les points suivants :

①Jugez le résultat de chaque équipe en fonction des besoins de chaque match. Une victoire sera marquée avec 3 points, une égalité sera marquée avec 1 point et une défaite sera marquée avec 0 point ;

②Lorsque les points sont identiques, le classement sera basé sur la différence de buts (la différence de buts est Nombre de buts marqués - nombre de buts encaissés)

③Si la différence de buts est la même, les équipes seront classées par le ; nombre de buts marqués.

④ Pour les situations où le nombre de buts est toujours le même, le jugement doit être basé sur la situation de faute dans le jeu.

Parce que nos données de base sont uniquement le nombre de buts marqués par les deux équipes dans chaque match, les points 1 à 3 peuvent être atteints, mais le point 4 ne peut pas être atteint.

L'étape suivante consiste à concevoir le formulaire sur la base de ces informations.

2. Conception de composition

1. Stocker les numéros séparément

Le premier principe à maîtriser dans la conception de composition est qu'une cellule ne stocke qu'un seul type de données. Par exemple, si le score de 5:2 est placé dans une cellule, les deux nombres doivent être extraits séparément puis calculés lors d'une analyse statistique ultérieure, ce qui est très gênant. Par conséquent, cette question doit être prise en compte lors de la conception des tableaux. Voyons comment cela se fait :

Afin de faciliter la compréhension de tous, nous affichons les bordures des cellules. On voit clairement qu'en fait, trois cellules sont utilisées pour chaque score, divisant le. deux équipes. Les résultats sont stockés séparément des deux points. Les quatre cellules avec la barre oblique au milieu et le nom de l'équipe dans la quatrième ligne sont traitées à l'aide de cellules fusionnées.

En utilisant rationnellement les cellules fusionnées, cela répond non seulement à l'exigence de stocker les nombres séparément, mais répond également aux besoins visuels, et vous pouvez voir les résultats du jeu de manière très intuitive.

2. Utiliser raisonnablement les colonnes auxiliaires

Le deuxième principe de la conception de composition est de simplifier les étapes de saisie et de simplifier le processus de calcul. Ceci peut être réalisé à l'aide de colonnes et de formules auxiliaires. Par exemple, dans ce tableau, vous devez remplir le score dans la zone jaune sous la ligne diagonale. Le score au-dessus de la ligne diagonale est référencé par la formule :

.

Afin de comptabiliser les victoires de chaque équipe Nombre de champs, champs plats et champs négatifs, quelques colonnes auxiliaires sont utilisées :

Concernant les colonnes auxiliaires, certains amis peuvent trouver cela trop gênant et espèrent toujours obtenir les résultats directement à l'aide de formules. En fait, c'est un malentendu. Dans certains tableaux modèles comportant des relations logiques complexes ou de nombreuses étapes de calcul, une bonne utilisation des colonnes auxiliaires peut simplifier la difficulté des formules, réduire la quantité de calculs et faciliter la maintenance du tableau pendant son utilisation, car le processus de calcul est entièrement mis en œuvre via colonnes auxiliaires, si vous devez ajuster la méthode de calcul quelque part, il vous suffit de changer l'emplacement correspondant.

Ce qui précède est une explication de la conception de ce tableau de statistiques de performances. Cela semble beaucoup, mais ce n'est en fait pas difficile. Voyons ensuite quelles formules et fonctions sont utilisées dans ce tableau.

3. Conception de la formule

1 Jugement de victoire : =IF(F5>H5,1,0)=IF(F5>H5,1,0)

利用IF函数直接比较F5和H5，当F5大于H5为胜，得到1，反之得到0。

胜场次数计算：=SUM(O5:Q5)

直接对前面的数据求和。

2、平场的判断：=IF(AND(F5=H5,F5" "),1,0)

与胜场有点区别，平场判断时还需要加一个条件，也就是进球数不为空的时候才去比较，因此加了AND函数去做判断。同时满足进球数相等并且不为空这两个条件时才算打平，记为1，反之为0。

平场次数计算：

同样是直接求和。

3、负场的判断：=IF(F5

与胜场的判断方法一样，只是公式中把大于符号改成小于符号。

负场次数计算：

直接求和即可。

以上这部分内容是将每场比赛结果变成具体的数字，看起来有点麻烦，但是没什么难度。接下来将辅助列隐藏，仅显示胜场、平场和负场的统计次数即可。

4、进球数、失球数和净胜球数的统计：

进球数：=SUM(F5,I5,L5)

失球数：=SUM(H5,K5,N5)

净胜球数：=AA5-AB5

这部分公式非常简单，都是加减运算。

5、积分的计算

按照规则，胜场记3分，平场记1分，负场记0分，积分的公式就是：=R5*3+V5*1

至此，计算排名的各项指标我们都算出来了，分别是：积分、净胜球和进球数。

在根据这三项指标计算排名之前，还需要做一个过渡，将三项指标根据各自的优先级（权重）进行量化，变成可以比较的数字，可以在AF列使用这样的一个公式来实现：=AD5+AC5%+AA5%%

Utilisez la fonction IF pour comparer directement F5 et H5. Lorsque F5 est supérieur à H5 gagne et obtient 1, sinon il obtient 0.

Calcul des gains : =SUM(O5:Q5)

🎜Sommez directement les données précédentes. 🎜🎜🎜2. Jugement de champ plat : 🎜=IF(AND(F5=H5,F5" "),1,0)🎜🎜🎜🎜C'est un peu différent du jeu gagnant, et ça est également nécessaire pour juger le jeu plat. Ajoutez une condition, c'est-à-dire que la comparaison ne sera effectuée que lorsque le nombre de buts n'est pas vide, donc la fonction ET est ajoutée pour porter le jugement. Il n'y a égalité que si les deux conditions d'égalité de buts et de non-vide sont remplies en même temps, et est enregistré comme 1, sinon il est 0. 🎜🎜Calcul des temps plats : 🎜🎜🎜🎜 est aussi une somme directe. 🎜🎜🎜3. Jugement de champ négatif : 🎜=IF(F5🎜🎜🎜🎜La méthode de jugement des gains est la même, sauf que le symbole supérieur à dans la formule est remplacé par un symbole inférieur à. 🎜🎜Calcul du nombre de jeux négatifs : 🎜🎜🎜🎜Sumez directement. Oui. 🎜🎜La partie ci-dessus consiste à transformer les résultats de chaque jeu en nombres spécifiques, ce qui semble un peu fastidieux, mais ce n'est pas difficile. Ensuite, masquez la colonne auxiliaire et affichez uniquement le statistiques de victoires, nuls et défaites 🎜🎜🎜 🎜🎜4. Statistiques de buts marqués, buts encaissés et différence de buts : 🎜🎜🎜Nombre de buts marqués : =SUM(F5,I5,L5)🎜🎜Nombre de buts encaissés : =SUM(H5,K5,N5)🎜🎜Différence de buts : =AA5-AB5🎜🎜Cette partie de la formule est très simple, c'est tout opérations d'addition et de soustraction 🎜🎜🎜🎜🎜5. Calcul des points🎜🎜🎜Selon les règles, une victoire vaut 3 points, un match nul vaut 1 point et une défaite vaut 0 point. La formule pour les points est : =R5. *3+V5*1🎜🎜🎜🎜A ce stade, nous avons calculé les différents indicateurs de classement. Ils sont tous calculés, à savoir : les points, la différence de buts et le nombre de buts 🎜🎜Avant de calculer le classement en fonction de ces trois indicateurs, un. une transition doit être effectuée pour quantifier les trois indicateurs en fonction de leurs priorités respectives (pondérations), devient un nombre comparable, qui peut être obtenu en utilisant une telle formule dans la colonne AF : =AD5+AC5%+AA5% %, les résultats sont les suivants. 🎜🎜Pensez-vous que cette formule est étrange ? Que signifie le signe pourcentage ? 🎜🎜Concentrons-nous sur l'explication de cette formule. Trois cellules sont utilisées ici : AD5 (points), AC5 (différence de buts) et AA5 (nombre de buts)🎜🎜🎜🎜.

Il est clairement indiqué dans les règles de classement que les points sont utilisés en premier si les points sont identiques, la différence de buts sera jugée, puis le nombre de buts sera jugé si les points sont identiques. Alors comment transformer cette comparaison séquentielle de plusieurs données en une comparaison d’une seule donnée ? Vous pouvez vous référer à la méthode d'utilisation des pondérations pour calculer les classements multifactoriels. Par exemple, le poids de l'indicateur A est de 50 %, le poids de l'indicateur B est de 30 % et le poids de l'indicateur C est de 20 %, alors le score global est A*0,5+B*0,3+C*0,2. , donc bien qu'il y ait trois facteurs de classement A, B, C, seul le score global doit être comparé pour obtenir le classement. A*0.5+B*0.3+C*0.2，如此虽然有A、B、C三个排名因素，但只需要比较综合得分即可获得排名。

这时这就需要人为的指定权重，将需要先后比较的数据转化为一个数据。

公式=AD5+AC5%+AA5%%原本的写法应该是：=AD5*100+AC5*10+AA5，在AG列输入公式，结果如下。

也就是将每个指标按照优先级分别扩大100倍、10倍、1倍后相加，使每个指标不会在同一个数位上，而是分别位于百位、十位和个位，如此既保证了各指标的优先等级，又将多个指标化成了一个数据。（注：当前倍数设置只适合积分、净胜球、进球数据都是小于10的情况。）

在足球比赛中，数字都比较小（一般都不满10），如果要计算的数据量级比较大的话，仅仅差10倍是不够的，经常会遇到相差100倍或者10000倍的情况。在这种这种情形下，公式里就会有很多个0，此时可以变个思路，将扩大改为缩小，%的作用就是将数据缩小100倍，%%的作用就是将数据缩小10000倍，这就是=AD5+AC5%+AA5%%的由来。

经过了这么多步骤，终于到了计算最终排名的时候，一起来看看排名公式吧。

6、排名的计算

公式非常简单，就是rank函数的标准用法：=RANK(AF5,$AF:$AF)

再来复习一下rank函数的含义：=rank（要计算名次的数据，参与排名的区域，升序或降序）

À ce stade, vous devez spécifier manuellement le poids pour convertir les données qui doivent être comparées successivement en une seule donnée.

L'écriture originale de la formule =AD5+AC5%+AA5%% doit être : =AD5*100+AC5*10+AA5, entrez la formule dans la colonne AG, et le résultat est le suivant.

Aussi Il s'agit d'élargir chaque indicateur respectivement 100 fois, 10 fois et 1 fois selon la priorité, puis de les additionner ensemble, de sorte que chaque indicateur ne soit pas sur le même chiffre, mais soit situé dans les centaines, dizaines et un chiffre respectivement. Cela garantit que la priorité de chaque indicateur est déterminée et que plusieurs indicateurs sont transformés en une seule donnée. (Remarque : le paramètre de multiplicateur actuel ne convient que dans les cas où les points, la différence de buts et les données de buts sont tous inférieurs à 10.) Dans les matchs de football, les chiffres sont relativement petits (généralement inférieurs à 10). être calculé est d'une ampleur Si elle est relativement grande, une différence de seulement 10 fois n'est pas suffisante. Une différence de 100 fois ou 10 000 fois est souvent rencontrée. Dans ce cas, il y aura de nombreux zéros dans la formule. À ce stade, vous pouvez changer votre façon de penser et changer l'expansion en réduction. La fonction de % est de réduire les données de 100 fois, et la fonction de %% est de. réduisez les données de 10 000 fois, c'est l'origine de =AD5+AC5%+AA5%%. Après tant d’étapes, il est enfin temps de calculer le classement final. Jetons un coup d’œil à la formule de classement.

6. La formule de calcul du classement

est très simple, qui est l'utilisation standard de la fonction de classement : =RANK(AF5,$AF$5:$AF$8)

Revoyons la signification de la fonction classement :=rang (donnée pour calculer le classement, zone participant au classement, ordre croissant ou décroissant), lorsque le troisième paramètre est omis, le classement sera par ordre décroissant, et celui avec le le score le plus élevé sera classé en premier.

🎜Enfin, masquez toutes les colonnes auxiliaires, et un tableau de statistiques de performances est complété : 🎜🎜🎜🎜🎜🎜🎜 IV Résumé 🎜🎜🎜🎜Vous avez peut-être le sentiment qu'il est vraiment difficile de concevoir un tel modèle. colonnes auxiliaires, et les formules à certains endroits ne peuvent pas être extraites. Elles doivent être remplies une par une. Existe-t-il un moyen d'extraire directement la formule sans utiliser de colonnes auxiliaires ? 🎜🎜Il y en a certainement un, mais il n'est peut-être pas pratique à utiliser. Il utilisera beaucoup de formules matricielles et de fonctions complexes. Bien entendu, en ce qui concerne ce formulaire, il existe certainement une possibilité d’optimisation continue. Le vétéran a passé une semaine à améliorer un modèle de rapport pour calculer le classement des ventes, le tester et le modifier à plusieurs reprises au cours du processus. Mais l'expérience utilisateur est très bonne, car il vous suffit de remplir les données de base et vous pouvez voir les résultats les plus souhaités. Le processus de calcul intermédiaire comprend toutes les colonnes auxiliaires (stockées séparément dans une feuille) et utilise même des fonctions relativement simples. lorsque des modifications sont nécessaires, les utilisateurs peuvent les résoudre eux-mêmes. 🎜🎜Pour les modèles de rapports, la durée du temps de conception n'est pas une mesure de la qualité, mais de la facilité d'utilisation et de maintenance. J'espère que vous pourrez apprendre quelques idées pour concevoir des modèles à partir de l'exemple d'aujourd'hui, ce qui pourra rendre votre travail plus efficace et vos formulaires plus intelligents. 🎜🎜Recommandations d'apprentissage associées : 🎜Tutoriel Excel🎜🎜

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!