Extraction des 5 valeurs maximales les plus élevées dans excel
j'ai un fichier excel avec une colonne correspondant au nom du Joueur et l'autre colonne correspondant aux statistiques de base-ball OPS.
OPS Player
1.000 player 1
5.000 player 2
3.000 player 3
1.000 player 4
--- player 5
4.000 player 6
1.000 player 7
--- player 8
1.000 player 9
--- player 10
1.333 player 11
1.000 player 12
2.000 player 13
--- player 14
--- player 15
--- player 16
1.500 player 17
3.500 player 18
1.500 player 19
--- player 20
1.000 player 21
1.000 player 22
0.000 player 23
0.000 player 24
0.500 player 25
0.000 player 26
0.667 player 27
maintenant, dans excel, j'ai besoin de comprendre comment créer une formule qui retourne une colonne des noms des joueurs avec la valeur des 5 meilleurs OPS. Ainsi, je voudrais que la requête renvoie un vecteur de colonne 5 x 1 dans excel. Quelle formule cellulaire puis-je utiliser pour y parvenir?
aussi, étant donné qu'ils vont se répéter valeurs de L'OPS, j'ai besoin de l'expression pour être robuste contre les liens.
5 réponses
compte tenu des données de configuration comme ceci:
la formule dans la cellule D2 et copiée vers le bas est:
=INDEX($B:$B,MATCH(1,INDEX(($A:$A=LARGE($A:$A,ROWS(D:D1)))*(COUNTIF(D:D1,$B:$B)=0),),0))
cette formule fonctionnera même s'il y a égalité des scores OPS parmi les joueurs.
Il y a 3 fonctions que vous voulez regarder ici:
- LARGE-renvoie la k-ème plus grande valeur d'un ensemble de données.
- INDEX-renvoie une valeur ou la référence à une valeur à l'intérieur d'une table ou d'une fourchette.
- MATCH-la fonction MATCH recherche un élément spécifié dans une série de cellules, puis renvoie la position relative de cet élément dans la série.
j'ai fait une recherche dans Excel avec votre OPS valeurs dans la Colonne B et les Joueurs dans la Colonne C, voir ci-dessous:
- dans les cellules A13 à A17, les valeurs 1 à 5 ont été insérées pour spécifier la nième valeur la plus élevée.
- dans la cellule B13, la formule suivante a été ajoutée:
=LARGE($B:$B, A13) - dans la cellule C13, la formule suivante a été ajoutée:
=INDEX($C:$C,MATCH(B13,$B:$B,0)) - ces formules obtiennent le plus haut rang OPS et joueur basé sur la valeur en A13.
- il suffit de sélectionner et faire glisser pour copier ces formules jusqu'aux 4 cases suivantes qui feront référence au classement correspondant dans la colonne A.
mettre les données dans une table de Pivot et faire un filtre Top n sur elle
=VLOOKUP(LARGE(A1:A10,ROW()),A1:B10,2,0)
tapez cette formule dans première ligne de votre drap puis traîner jusqu'à la cinquième rangée...
c'est un simple vlookup, qui trouve le large valeur dans le tableau (A1:A10), le ROW() fonction donne le nombre de lignes (première ligne = 1, deuxième ligne =2 et ainsi de suite) et plus loin est le critère de recherche.
Note: Vous pouvez remplacer le ROW() pour 1,2,3,4,5 comme exigé...si vous avez cette formule dans autre que la première ligne, alors assurez-vous de soustraire certains nombres à partir de la row() pour obtenir des résultats exacts.
EDIT: POUR vérifier l'égalité des résultats
C'est possible, vous devez ajouter un helper colonne de la feuille, voici le lien. Faites-moi savoir au cas où les choses semblent être en désordre....
à mon avis, le cas d'un PT (comme @Nathan Fisher) est un 'no brainer', mais je voudrais ajouter une colonne pour faciliter l'ordre par rang (haut ou bas):
OPS est entré en tant que valeurs (somme de) deux fois donc j'ai renommé les étiquettes de la colonne pour préciser laquelle est laquelle. Le PT est dans une autre feuille de données, mais pourrait être dans la même feuille.
Rang est défini par un clic droit sur un point de données sélectionné dans la colonne et Show Values As... et Rank Largest to Smallest (il y a d'autres options) avec le champ de Base comme Player et le filtre est un Value Filters,Top 10...:
une fois dans un PT, la puissance de cette fonctionnalité peut très facilement être appliquée pour visualiser les données de bien d'autres façons, sans changement de Formule (il n'y en a pas!).
dans le cas d'une égalité pour la dernière position incluse dans le filtre, les deux résultats sont inclus (le Top 5 indiquerait six résultats ou plus). Un égalité pour le premier rang entre seulement deux joueurs se montrerait comme 1 1 3 4 5 pour le Top 5.