Formule Excel pour trouver des valeurs top 3, 5, 10 dans la colonne ou la ligne

Ce didacticiel montre comment localiser efficacement les n valeurs supérieures dans un ensemble de données et récupérer les données associées à l'aide de formules Excel. Que vous ayez besoin du plus haut, le plus bas ou ceux qui répondent aux critères spécifiques, ce guide fournit des solutions.

• Trouver des valeurs supérieures / inférieures
• Localisation des valeurs supérieures en lignes
• Récupération des matchs pour des valeurs les plus importantes / plus petites
• Gestion des doublons dans les recherches de valeur supérieure
• Intégration de critères pour la sélection de valeur maximale
• Utilisation de la fonction de filtre pour les valeurs N supérieures

Excel Formules pour les valeurs top 3, 5, 10 ou n

Pour identifier les valeurs N les plus élevées, combinez les LARGE fonctions et ROWS :

=LARGE(values, ROWS(A$2:A2))

Par exemple, pour trouver les n tops n valeurs dans les cellules B2: B12, entrez cette formule dans la cellule de sortie souhaitée (par exemple, D2), puis faites glisser vers le bas pour couvrir le nombre de cellules requis (3 pour le top 3, 5 pour le top 5, etc.).

La LARGE fonction identifie la plus grande valeur K-tth, tandis que ROWS(A$2:A2) génèrent dynamiquement la valeur «K» (1, 2, 3 ...). La référence absolue A$2 maintient la ligne de départ constante, tandis que la référence relative A2 incréments avec chaque copie cellulaire.

Excel Formules pour les valeurs N inférieures

Pour trouver les valeurs N les plus basses, utilisez la SMALL fonction au lieu de LARGE :

=SMALL(values, ROWS(A$2:A2))

Trouver des valeurs N supérieures dans une rangée

Pour les données organisées horizontalement, remplacez ROWS par COLUMNS :

=LARGE(values, COLUMNS(A$2:A2)) (pour les valeurs supérieures) =SMALL(values, COLUMNS(A$2:A2)) (pour les valeurs inférieures)

Récupération des données associées aux valeurs N supérieures

Utilisez INDEX et MATCH à LARGE (ou SMALL ) pour récupérer des données correspondant aux n valeurs supérieures:

=INDEX(return_array, MATCH(LARGE(lookup_array, k), lookup_array, 0))

Alternativement, les utilisateurs d'Excel 365 peuvent simplifier cela avec XLOOKUP :

=XLOOKUP(LARGE(lookup_array, k), lookup_array, return_array)

Gestion des valeurs supérieures en double

Pour les ensembles de données avec des doublons, une fonction MATCH plus robuste est nécessaire (formule du tableau, Shift Ctrl Entrez dans les versions pré-365):

=INDEX(return_array, MATCH(1, (lookup_array=LARGE(lookup_array, k))*(COUNTIF(output_range, return_array)=0), 0))

Excel 365 propose une solution plus simple à l'aide FILTER .

Trouver les principales valeurs avec des critères

Utilisez des instructions IF dans les formules LARGE et MATCH à l'intégration des critères:

=LARGE(IF(criteria, values), k)

Encore une fois, Excel 365 simplifie cela avec FILTER .

Utilisation de la fonction de filtre (Excel 365)

Les fonctions FILTER et SORT d'Excel 365 fournissent une approche rationalisée:

=SORT(FILTER(data, numbers>=LARGE(numbers, n)), sort_index, -1) =SORT(FILTER(data, numbers (top n) = srie

Ce tutoriel fournit des méthodes complètes pour extraire les valeurs N Top N dans Excel, s'adressant à divers scénarios et versions Excel. Un classeur de pratique est disponible en téléchargement.

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!