Excel grande fonction pour obtenir la n-tth la plus élevée

Cet article vous guidera sur la façon d'utiliser une grande fonction dans Excel avec de nombreux exemples de formules.

Lors de l'analyse d'un ensemble de nombres, il est souvent logique de trouver les plus grands. Obtenir la valeur la plus élevée est super facile avec la fonction maximale. Lorsqu'il s'agit de cibler une plus grande valeur spécifique, disons que le 2 ^nd ou le 3 ^e plus grand nombre dans un ensemble de données, la grande fonction est utile.

• Excel grande fonction
• Grande formule de base
• Utilisation de grandes formules dans Excel - Exemples
  • Obtenez les valeurs N Top N
  • Somme ou des valeurs N plus importantes moyennes
  • Obtenez le N-Thth Most Matches
  • Trier les nombres du plus élevé au plus bas
  • Grande formule pour les dates et les heures
  • Obtenez une date future la plus proche de aujourd'hui ou de la date spécifiée
• Une grande fonction ne fonctionne pas

Excel grande fonction

La grande fonction dans Excel est utilisée pour renvoyer la n-plus grande valeur à partir d'un ensemble de données numériques. Par exemple, il peut calculer le score le plus élevé, le 2 ^e plus grand ordre, le résultat de la ^3e place, etc.

La syntaxe se compose de deux arguments, tous deux requis:

Grand (tableau, k)

Où:

• Array - Une plage ou un tableau où rechercher la valeur la plus importante.
• K - La position du plus haut à retourner, c'est-à-dire la plus grande valeur K-tth dans un ensemble de données.

Large est classé dans les fonctions statistiques. Il est disponible dans toutes les versions d'Excel pour Office 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010 et plus tôt.

3 choses à savoir sur la grande fonction

Avant de passer à des choses plus pratiques et de commencer à construire nos propres formules, veuillez faire attention à 3 faits simples qui expliquent les éléments essentiels:

1. La grande fonction ne traite que les valeurs numériques. Les cellules vierges , le texte et les valeurs logiques sont ignorées.
2. Si le tableau contient des erreurs , une erreur est renvoyée.
3. Dans le cas où le tableau contient n valeurs, grand (tableau, 1) renverra la valeur maximin, et grand (tableau, n) renvoie la valeur minimale.

Grande formule de base

Dans sa forme de base, une grande formule dans Excel est très facile à construire. Pour le 1er argument, vous fournissez une gamme de valeurs numériques. Dans le 2 ^e argument, vous définissez la position du plus grand à retour.

Dans le tableau des échantillons ci-dessous, en supposant que vous souhaitez connaître le 2 ^E plus grand score. Cela peut être fait avec la formule suivante:

=LARGE(B2:B10, 2)

Comment utiliser de grandes formules dans Excel - Exemples

Et maintenant, regardons les cas d'utilisation plus spécifiques et voyons comment la grande fonction pourrait être utile.

Comment obtenir les valeurs N supérieures dans Excel

Pour obtenir les valeurs les plus importantes de 3, 5, 10, etc. avec une seule formule, effectuez ces étapes:

1. Tapez les positions d'intérêt dans les cellules séparées. Ces nombres seront utilisés comme valeurs k .
2. Faites une grande formule en appliquant une référence de plage absolue pour le tableau ($ B 2: $ B 10 $ dans notre cas) et une référence cellulaire relative pour K (D3).
3. Entrez la formule dans la cellule la plus supérieure, puis faites-la glisser vers les cellules ci-dessous. Fait!

Par exemple, nous allons trouver les 3 meilleurs scores du tableau ci-dessous. Pour cela, nous tapons les nombres 1, 2 et 3 en D3, D4 et D5, respectivement, et entrons dans la formule suivante dans E3:

=LARGE($B$2:$B$10, D3)

Faites-le glisser via E4, et vous obtiendrez ce résultat:

Au lieu de taper les positions sur la feuille, vous pouvez utiliser la fonction des lignes avec une référence de plage en expansion pour générer automatiquement les valeurs k comme expliqué dans la formule Excel pour trouver les valeurs N supérieures dans une liste.

=LARGE($B$2:$B$10, ROWS(B$2:B2))

Conseil. Vous pouvez également utiliser la grande fonction pour extraire les N Top N enregistrements avec un filtre avancé.

Comment résumer ou en moyenne les plus grandes valeurs N plus importantes

Pour résumer les valeurs N Top dans un ensemble de données, vous pouvez utiliser large avec le sumproduct ou la somme de cette manière:

Sumproduct (grand ( tableau , {1,…, n })))

Ou

Sum (grand ( tableau , {1,…, n }))

Pour en moyenne les valeurs N les plus élevées, combinez les fonctions moyennes et grandes:

Moyen (grand ( tableau , {1,…, n }))

Pour voir comment cela fonctionne dans la pratique, trouvons une moyenne des 3 premiers scores de notre tableau d'échantillons. Pour cela, nous utilisons cette formule:

=AVERAGE(LARGE(B2:B10, {1,2,3}))

Pour additionner les 3 scores les plus élevés, la formule est:

=SUM(LARGE(B2:B10, {1,2,3}))

Note. Dans le cas où vous utilisez une plage plutôt qu'une constante de tableau pour K , vous devez appuyer sur Ctrl Shift Entrée pour en faire une formule de tableau. Dans Excel 365 qui prend en charge les tableaux dynamiques, toutes les formules peuvent être terminées comme d'habitude en appuyant sur la touche Entrée .

Comment ces formules fonctionnent:

Normalement, la grande fonction renvoie une seule valeur basée sur le nombre k . Dans ces formules, nous fournissons une constante de tableau comme {1,2,3} pour l'argument k le forçant à renvoyer un tableau de valeurs. Ce tableau va à la fonction extérieure pour être additionné ou moyen.

Obtenez des données associées à la n-plus la plus grande valeur

Pour récupérer des informations relatives aux valeurs les plus importantes, nidifiez simplement la grande fonction dans la formule de correspondance d'index canonique:

Index ( return_array , match (grand ( lookup_array , n ), lookup_array , 0))

Où:

• Return_array est une gamme à partir de laquelle extraire les correspondances.
• Lookup_Array est une gamme de nombres à se classer du plus haut.
• N est la position de la plus grande valeur à rechercher.

Par exemple, pour obtenir le nom d'un étudiant qui a fait le mieux aux examens, nter cette formule en F3:

=INDEX($A$2:$A$10, MATCH(LARGE($B$2:$B$10, $D3), $B$2:$B$10, 0))

Lorsque A2: A10 est le tableau de retour (noms), B2: B10 est le tableau de recherche (scores) et D3 est la position de la plus grande.

Pour découvrir qui a les ² et ³ points les plus élevés, copiez le forma en F4 et F5:

Notes:

• Cette solution fonctionne bien pour des valeurs uniques. Si votre ensemble de données contient des nombres en double, des "liens" dans le classement peuvent se produire, ce qui produira des résultats incorrects. Pour éviter que cela ne se produise, utilisez une formule plus complexe pour gérer les liens.
• Dans Excel 365, vous pouvez utiliser de nouvelles fonctions de tableau dynamique pour élaborer une solution beaucoup plus simple qui résout automatiquement un problème de liens. Pour plus de détails, veuillez consulter comment filtrer les valeurs N Top N dans Excel.

Grande formule pour trier les nombres descendant

Pour trier rapidement une liste de nombres dans Excel 365, vous pouvez utiliser la nouvelle fonction de tri. Dans Excel 2019, 2016 et antérieures qui ne prennent pas en charge les tableaux dynamiques, nous devons compter sur la bonne vieille fonction pour trier descendant et petit à trier ascendant.

Pour cet exemple, nous allons trier les nombres dans A2: A10 du plus élevé au plus bas. Pour le faire, nous aurons à nouveau besoin de la fonction des lignes avec une référence de plage en expansion pour incrémenter l'argument k de 1 avec chaque ligne:

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

La formule ci-dessus va à la cellule la plus supérieure (C2). Et puis, vous le faites glisser à travers autant de cellules qu'il y a de nombres dans la liste originale (C2: C10 dans notre cas):

Grande formule pour les dates et les heures

Comme vous le savez, les dates et les temps dans Excel sont des valeurs numériques: les dates sont stockées comme des entiers et des heures que des décimales. Qu'est-ce que cela signifie pour nous? La grande fonction calcule les valeurs de date et d'heure exactement de la même manière que les nombres. En d'autres termes, la formule que vous avez utilisée pour les nombres fonctionnera également pour les dates et les heures!

En supposant que vous ayez une liste de dates dans B2: B10, la formule suivante renverra les dates N les plus récentes , selon le nombre de cellules auxquelles vous le copiez:

=LARGE($B$2:$B$10, ROWS(B$2:B2))

La même formule peut également trouver les 3 fois les plus longues :

Obtenez une date future la plus proche de aujourd'hui ou de la date spécifiée

Cet exemple montre une utilisation plus spécifique de la fonction Excel grande avec les dates.

À partir d'une liste de dates dans B2: B10, supposons que vous souhaitiez retourner une date future la plus proche de aujourd'hui . Pour accomplir la tâche, nous utiliserons le Countif et les fonctions aujourd'hui pour calculer une valeur pour l'argument k de grand:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY()))

Pour trouver la prochaine mais une date, la formule est:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY())-1)

Pour trouver une date qui vient juste après une date donnée , saisissez la date cible dans une cellule (E3 dans cet exemple) et concaténez cette référence cellulaire dans les critères de Counif:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1))

Dans la situation où une date correspondant à vos critères n'est pas trouvée, vous pouvez utiliser la fonction IIRERROR comme un "wrapper" pour assister à une erreur et la remplacer par tout texte que vous voyez:

=IFERROR(LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1)), "Not found")

Comment fonctionne cette formule:

Essentiellement, vous utilisez la fonction COUNFIF pour compter le nombre de dates dans la liste plus importantes qu'aujourd'hui ou une date spécifiée. Ce décompte est la valeur k dont la grande fonction a besoin.

Au moment de la rédaction, la date d'aujourd'hui était le 7 octobre 2020. La fonction Countif avec ">" & Today () pour les critères a déterminé qu'en B2: B10, il y a 4 dates supérieures au 7-oct-2020. Ce qui signifie que la 4e date la plus importante de la liste est la date future la plus proche que nous recherchons. Ainsi, nous branchons compter dans le 2 ^ème argument de grand et obtenons le résultat souhaité:

=LARGE($B$2:$B$10, 4)

La prochaine mais une date est la date de 3 ^e plus importante de notre cas. Pour l'obtenir, nous soustrayons 1 du résultat de Counif.

Excel grande fonction ne fonctionne pas

Une grande formule peut lancer un #num! Erreur pour les raisons suivantes:

• Le tableau fourni est vide ou ne contient pas une seule valeur numérique.
• La valeur k est un nombre négatif.
• La valeur k est supérieure au nombre de valeurs dans le tableau .

C'est comment utiliser la grande fonction dans Excel pour trouver des valeurs les plus élevées dans un ensemble de données. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Excel Exemples de formule (fichier .xlsx)

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!