Création des 10 meilleures listes et tableaux de classement dans Excel 2007

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

Aujourd’hui, je vais regarder le MIN et MAX fonctionne aux côtés de PETIT et GRAND fonctions et comment elles peuvent être utilisées sur vos données pour vous fournir les nombres les plus grands et les plus petits dans vos données. L’utilisation de ces fonctions avec INDICE et MATCH vous permet de créer les 10 meilleures listes et tableaux de classement.

De plus, je vais regarder le INDICE et MATCH fonctions et comment ils peuvent extraire une étiquette pour accompagner les données recueillies à l’aide MIN, MAX, PETIT, et GRAND pour créer un tableau dynamique avec l’étiquette (dans mon cas, ce sera le nom de l’article) et la valeur (dans ce cas, le nombre total de visites).

• MIN et MAX vous fournir le plus petit et le plus grand nombre dans votre plage de données.
• PETIT et GRAND remplir une fonction similaire ; cependant, ils autorisent un argument qui peut par exemple vous permettre de déterminer les trois plus grands nombres de vos données, ou le deuxième plus petit.

Utilisation des fonctions MIN et MAX

J’ai un certain nombre d’articles que j’ai créés au fil du temps et j’aimerais connaître les valeurs minimales et maximales de mon Hub Score, mes vues sur différentes périodes de temps, etc. Je peux facilement utiliser MIN et MAX pour les afficher.

MIN et MAX sont assez simples à utiliser, commencez simplement comme pour toutes les formules ou fonctions avec un = suivi de MAX et de la plage de cellules que vous souhaitez vérifier.

=MAX()

MIN fonctionne exactement de la même manière.

=MIN()

En créant une formule pour chaque colonne pour les deux MIN et MAX fonction, je me retrouve avec ce qui suit.

Travailler avec les fonctions SMALL et LARGE

PETIT et GRAND sont très utiles si vous souhaitez créer un tableau de classement ou rechercher une valeur spécifique, par exemple, la quatrième plus petite valeur de votre plage de données.

Pour mon exemple ci-dessus, je veux connaître les 10 valeurs les plus élevées pour le nombre total de visites globales pour mes articles publiés. Pour ce faire, j’utilise le GRAND fonction dans 10 formules, commençant par 1, 2, 3 et se terminant par 10 comme ci-dessous.

=GRAND(1)

Faites défiler pour continuer

Et se terminant par :

=GRAND(,dix)

Cela me donne une liste comme indiqué ci-dessous.

De même, travailler avec PETIT pourrait me donner une liste des 10 articles avec les totaux les plus bas. Si je voulais connaître la troisième plus petite valeur pour le nombre total de vues d’articles de tous mes articles, j’utiliserais une formule comme ci-dessous, la plage étant la colonne contenant le nombre total de vues.

=PETIT(,3)

Limitations des fonctions MIN, MAX, SMALL et LARGE

Si vous regardez à nouveau mon classement des 10 articles avec le plus grand nombre de visites globales ci-dessus, vous remarquerez les limites de ces fonctions. Bien qu’ils soient fantastiques pour renvoyer les valeurs les plus petites ou les plus grandes d’une plage, ou pour vous indiquer la troisième plus petite valeur d’une plage, c’est tout ce qu’ils vous disent.

Pour mon tableau de classement, il serait très utile de savoir de quels articles il s’agissait ainsi que leurs totaux.

Ensuite, nous allons explorer un moyen d’obtenir qu’Excel 2007 nous fournisse automatiquement les noms de mes articles les plus performants pour accompagner leur nombre total de visites. Pour ce faire, nous allons utiliser trois fonctions dont une que nous avons déjà utilisée INDEX, CORRESPONDANCE et GRAND.

Utilisation des fonctions INDEX et MATCH avec LARGE

Pour créer un tableau de classement qui contient non seulement le nombre total de visites pour mes 10 articles les plus populaires mais aussi les noms de ces articles, nous devons utiliser deux nouvelles fonctions à côté GRAND. Voici les INDICE et MATCH les fonctions.

Ce que nous faisons essentiellement, c’est :

• Nous demandons d’abord à Excel de trouver la plus grande valeur dans la colonne pour le nombre total de visites en utilisant GRAND.
• On demande alors à Excel d’utiliser MATCH pour trouver la position de cette cellule dans la même colonne.
• INDICE recherche ensuite la cellule adjacente à cette cellule dans la colonne contenant les noms d’articles.

Voila, ça va construire notre classement.

OK, maintenant sur la formule elle-même montrée dans la figure ci-dessous.

OK, donc encore une fois en commençant par GRANDil recherche simplement la première valeur la plus élevée dans la colonne qui contient le nombre total de vues d’articles, exactement comme nous l’avons fait dans notre exemple précédent.

pour MATCHla syntaxe est la suivante :

=MATCH(lookup_value,lookup_array,match_type)

• Le lookup_value a été fourni par le résultat qui nous a été donné par GRAND.
• Le lookup_array est à nouveau la colonne qui contient le nombre total de vues d’articles.
• Le type de correspondance est 0, ce qui indique MATCH chercher le nombre qui est exactement égal au résultat que nous donne GRAND.

pour INDICEla syntaxe est la suivante :

=INDICE(déployer,num_ligne,num_colonne)

• Le tableau est la colonne contenant les noms d’articles.
• Les row_num et column_num sont fournis par MATCH.

Donc, nos deux formules ensemble,

=INDEX(‘hubs(1)’!A3:A47,MATCH(LARGE((‘hubs(1)’!F3:F47),1),’hubs(1)’!F3:F47)) et =LARGE( ‘hubs(1)’!F3:F47,1) nous fournit le tableau suivant.

C’est exactement ce que nous voulions réaliser, ce qui est génial. Pour créer chaque ligne, nous avons juste augmenté la valeur dans le GRAND partie de la formule de 1 à 2 et ainsi de suite.

=INDEX(‘hubs(1)’!A3:A47,MATCH(LARGE((‘hubs(1)’!F3:F47),2),’hubs(1)’!F3:F47)) et =LARGE( ‘hubs(1)’!F3:F47,2)

Alternatives à INDEX, MATCH et LARGE

Avant d’étudier la possibilité d’utiliser INDEX, CORRESPONDANCE, et GRAND en combinaison, j’ai étudié la possibilité d’utiliser RECHERCHE.

RECHERCHE est une fonction extrêmement puissante. Cependant, il est incapable de rechercher une colonne à gauche de la colonne qui vous intéresse, ce qui est le cas dans cet exemple. Plutôt que de déplacer les données à chaque fois que je les extrais dans cette feuille de calcul, j’ai jeté RECHERCHE dans ce cas.

J’ai un autre article qui examine RECHERCHE plus en détail. Je l’ai utilisé pour remplir automatiquement les prix de divers supports musicaux et également pour remplir automatiquement l’adresse d’un client en fonction de son numéro de client. Mon article pour RECHERCHE peut être trouvé ici.

Un tableau croisé dynamique aurait parfaitement fonctionné dans ce cas, mais pas avec le MIN et MAX, GRAND et PETIT fonctions que j’avais déjà commencé à utiliser. En fait, j’ai déjà une telle table mais je voulais aller jusqu’au bout de ces fonctions.

Les tableaux croisés dynamiques sont également très puissants, vous permettant de créer un tableau à partir de vos données, puis de le modifier à volonté. Si vous décidez que vous souhaitez que votre tableau croisé dynamique vous fournisse les articles avec le score Hub le plus bas, puis changez d’avis pour les articles avec les dix meilleurs résultats des sept derniers jours, cela peut être fait facilement et rapidement. De plus, les tableaux croisés dynamiques sont dynamiques, lorsque les données changent, elles peuvent être instantanément actualisées pour refléter cela.

Conclusion

J’espère que cet article sur le MIN, MAX, GRAND, PETIT, INDEX, et MATCH les fonctions. nous avons exploré MIN, MAX, GRAND, et PETIT en détail avant d’explorer comment INDICE et MATCH pourrait rendre ces fonctions initiales encore plus utiles.

Nous avons commencé avec les quatre premières fonctions retournant un nombre (par exemple le plus grand nombre total de hits) et en utilisant INDICE et MATCH nous avons pu renvoyer le nom réel de cet article pour créer un tableau de classement dynamique.

Comme c’est souvent le cas dans Excel 2007, et pourquoi c’est une application si intéressante à apprendre et pourquoi elle est si puissante, c’est que vous pouvez prendre des fonctions apparemment sans rapport et créer quelque chose en les utilisant qui est à la fois puissant et très utile. Merci beaucoup d’avoir lu et si vous avez des commentaires, n’hésitez pas à les laisser ci-dessous.

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

Robbie C. Wilson (auteur) le 22 janvier 2013 :

Merci pour votre gentil commentaire Paul. Je suis content que vous ayez aimé l’article. C’est bon d’entendre que les captures d’écran ont été utiles et les exemples utiles. J’adore étudier les fonctions disponibles dans Excel et trouver de nouvelles façons intéressantes de les utiliser, puis de les partager via HubPages.

Paul Maplesden d’Asheville, Caroline du Nord, le 22 janvier 2013 :

En tant qu’utilisateur passionné d’Excel, je pense que c’est un très bon article. J’aime la façon dont vous avez séparé les différentes fonctions, en fournissant de bons exemples avec chacune. L’utilisation de captures d’écran a également été très utile pour résumer les étapes clés que les gens doivent suivre. Un excellent tutoriel pour certaines fonctions souvent négligées, un excellent travail.