Utilisez l’outil de classement et de centile du ToolPak d’analyse dans Excel 2007 et 2010 pour créer des tableaux de classement

Robbie écrit principalement sur Skyrim, mais met également occasionnellement en lumière les chances des applications Microsoft telles qu’Excel et Outlook.

Dans cet article, je vais étudier la Rang et centile outil.

Classement vous permet de classer toutes vos données dans l’ordre du plus élevé au plus bas et Centile vous permet de catégoriser facilement vos données en centiles tels que les 25 % supérieurs, les 50 % supérieurs, etc.

Pour illustrer comment utiliser cet outil, je vais travailler sur un exemple. Dans mon exemple, j’ai une classe de 30 élèves. Je viens d’obtenir leurs derniers résultats et je souhaite les classer et leur attribuer des notes en fonction de leur classement dans la classe. Les 10 % supérieurs obtiennent un A, les 20 % suivants un B, les 20 % suivants un C et les autres un D.

Mon but ultime est de me retrouver avec une table telle que celle illustrée ci-dessous avec le moins de travail manuel possible.

En conjonction avec le Rang et centile outil, je vais utiliser le MATCH et INDICE des fonctions pour attribuer les noms des étudiants aux données brutes créées par l’outil. J’utiliserai alors le SI fonction pour leur attribuer automatiquement une note en fonction de leurs résultats.

Avant d’utiliser l’outil, nous devons nous assurer que le ToolPak d’analyse est présent dans Excel. Le ToolPak se trouve sur le Rendez-vous onglet, dans l’onglet Une analyse groupe et est accessible via le L’analyse des données bouton.

Maintenant que nous avons ajouté le ToolPak d’analyse, sélectionnez le L’analyse des données bouton et sélectionnez Rang et centile comme indiqué ci-dessous.

L’outil lui-même est assez simple et facile à utiliser.

• Sélectionnez d’abord le Plage d’entrée
• Si vos données sont en colonnes, laissez Groupé par cendre Colonnes (sinon sélectionner Lignes)
• Si vous avez des étiquettes pour vos colonnes, sélectionnez Libellés sur la première ligne
• Enfin, sélectionnez votre options de sortiesélectionner Plage de sortie pour le placer à un endroit spécifique de votre feuille de calcul ou sélectionnez Nouvelle feuille de travail ou Nouveau classeur selon votre préférence (nous avons placé les résultats de l’outil à côté des données d’origine dans l’exemple sur lequel nous travaillons aujourd’hui)
• Cliquez sur D’ACCORD et Excel créera votre tableau de classement et de centile

Vous pouvez voir la sortie ci-dessus de l’outil. Excel a classé chaque étudiant et leur a donné leur centile exact (la colonne Pourcentage). Cependant, plutôt que d’utiliser les noms des étudiants, il y a une colonne appelée indiquer, qui est le numéro de ligne de chaque étudiant. Pour tourner le indiquer colonne dans une colonne contenant le nom de l’étudiant, je dois me tourner vers le MATCH et INDICE fonction.

Utilisation de la fonction MATCH et INDEX pour convertir les résultats dans la colonne de points

Ces deux fonctions fonctionnent en tandem pour convertir les résultats de la colonne Point en nom de l’élève.

première:

Faites défiler pour continuer

• la MATCH recherche le nombre dans la colonne Point, puis trouve sa correspondance exacte dans la colonne intitulée Student No.
• INDICE recherche ensuite la cellule adjacente au résultat qui MATCH trouvé et renvoie le nom de l’élève.

Dans la figure ci-dessous, vous pouvez voir la formule qui transforme 12 dans la colonne Point à notre meilleur étudiant (Ed Bradley).

La formule entière est

=INDEX($B$3:$B$32,EQUIV(F5,$A$3:$A$32))

Il est plus facile de comprendre qu’il est brisé lorsqu’il est réduit à ses composants, MATCH et INDICE

• la MATCH la fonction recherche le contenu de F5 (12) dans la plage A3 à A32

CORRESPONDANCE(F5,$A$3:$A$32))

• la INDICE la fonction recherche alors dans la plage B3 à B32 la cellule adjacente à la cellule trouvée par MATCH

alors, MATCH trouve 12 dans la cellule A14 et INDICE trouve Ed Bradley dans la cellule adjacente à A14

Noter: les $ ajoutés aux références de cellule indiquent à Excel 2007 et Excel 2010 que ces références de cellule ne changent pas même lorsque la formule est copiée dans d’autres cellules (appelées références de cellule absolues). Cela empêche Excel d’en ajouter un à chaque référence de cellule chaque fois que vous le copiez (la formule deviendrait =INDEX(B4:B33,MATCH(F4,A4:A33)) plutôt que =INDEX($B$3:$B$32,MATCH (F4,$A$3:$A$32)) qui pourrait autrement fournir un résultat potentiellement incorrect).

J’ai placé cette formule dans la colonne à droite de la colonne Point pour faciliter le remplissage de notre tableau final avec l’en-tête de colonne Étudiant.

Remplir notre tableau de classement de nos étudiants

Maintenant que nous avons une colonne contenant les noms de nos étudiants, pour compléter le tableau :

• Cache le indiquer et rang colonnes (ne les supprimez pas ou les formules seront cassées)
• Copiez le Pour cent colonne et placez-la à droite de la colonne d’origine
• Changer le nom de l’original Pour cent colonne à Noter

N’oubliez pas que nous voulions le faire aussi automatiquement que possible. Pour attribuer une note à nos élèves, nous utiliserons un SI déclaration comme indiqué ci-dessous.

=SI(K3<50%, "D", IF(K3>90%, « A », SI(K3>70%, »B », SI(K3>50%, « C »))))

Cette formule examine la colonne Pourcentage et examine le pourcentage, puis attribue une lettre en fonction du résultat trouvé.

Noter: L’ordre est d’une importance vitale, si nous le faisions dans l’ordre C, B, A, nous nous retrouverions avec seulement les notes D et C car Excel examinerait tous les nombres supérieurs à 50% et leur donnerait un C et la formule se terminerait.

• Copiez cette formule dans le reste du Noter colonne.

Nous avons maintenant notre tableau complété, qui ressemble à la figure ci-dessous :

Conclusion

la Rang et centile L’outil de l’Analysis ToolPak disponible dans Excel 2007 et 2010 nous permet de travailler avec des données et de les classer, puis de demander à Excel de calculer son centile.

Dans mon exemple d’aujourd’hui, j’ai pris ma classe (fictive) d’étudiants et je les ai classés en fonction de leurs résultats aux tests. Je leur ai ensuite donné une note en fonction de l’endroit où ils se sont assis par rapport à leurs pairs.

Pour ce faire, j’ai non seulement utilisé l’outil Rang et centile, mais j’ai également utilisé l’outil MATCH et INDICE fonctions pour convertir les résultats afin que le nom de l’élève apparaisse à côté de sa note, puis a également utilisé le SI commande pour leur attribuer automatiquement leur note.

Et enfin…

Cet article est exact et fidèle au meilleur de la connaissance de l’auteur. Le contenu est uniquement à des fins d’information ou de divertissement et ne remplace pas un conseil personnel ou un conseil professionnel en matière commerciale, financière, juridique ou technique.

© 2013 Robbie C Wilson