Comment ignorer la note la plus basse pour obtenir la moyenne ou le total des valeurs dans Excel ?
Lorsque vous travaillez avec une liste de notes ou de scores dans Excel, il peut être nécessaire de calculer la note finale d’un élève en excluant sa note la plus basse, voire ses n notes les plus basses, avant de faire la moyenne ou la somme des valeurs restantes. Cette exigence est courante dans les contextes éducatifs, où les élèves peuvent avoir le droit d’ignorer leurs performances les plus faibles afin de tenir compte d’anomalies ou de garantir l’équité. Effectuer cette opération manuellement serait fastidieux, surtout avec de grands jeux de données ou des ajustements fréquents. Heureusement, Excel propose plusieurs méthodes flexibles pour y parvenir, allant de formules simples à l’automatisation via VBA pour des traitements par lots.
Ignorer la note la plus basse et obtenir la moyenne ou la somme à l’aide de formules
Ignorer la note la plus basse et obtenir la moyenne ou la somme à l’aide de formules
Si vous souhaitez exclure les *n* valeurs les plus basses d’une ligne ou d’une liste de données, puis effectuer des calculs — comme la moyenne ou la somme des nombres restants —, les formules intégrées d’Excel offrent une solution pratique. Elles sont particulièrement utiles lorsque vous traitez un nombre modéré de lignes ou que vous privilégiez une approche transparente et facilement ajustable.
Vous trouverez ci-dessous des explications distinctes des méthodes de somme et de moyenne, accompagnées d’instructions détaillées et de conseils pratiques pour une application flexible.
Additionner les nombres tout en ignorant la valeur la plus basse ou les N valeurs les plus basses :
Pour calculer la somme de chaque ligne ou liste tout en omettant la valeur la plus basse, utilisez la méthode suivante :
1. Sélectionnez une cellule vide où vous souhaitez afficher le total de la première ligne (par exemple, la cellule I2 si vos données se trouvent en B2:H2), puis saisissez la formule suivante :
=SUM(B2:H2)-SMALL(B2:H2,1)2. Appuyez sur Entrée pour valider, puis faites glisser la poignée de recopie vers le bas afin d’appliquer cette formule aux autres lignes selon vos besoins. Vous additionnerez ainsi tous les nombres de chaque ligne, à l’exception de la valeur la plus basse.
Voici une capture d’écran à titre de référence :
Remarques et conseils :
- Pour exclure les deux, trois ou davantage des valeurs les plus basses, vous pouvez étendre la formule en soustrayant des résultats SMALL supplémentaires. Par exemple :
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n)- Dans ces formules, B2:H2 est la plage que vous souhaitez additionner, et les nombres 1, 2, 3, etc., indiquent les n plus petites valeurs à exclure. Ajustez n en fonction du nombre de notes les plus basses que vous souhaitez ignorer.
- Veillez à ne pas définir n supérieur ou égal au nombre total de valeurs ; sinon, vous obtiendrez des erreurs ou des résultats inattendus.
- Ces formules fonctionnent indépendamment pour chaque ligne. Si vos données s’étendent sur des colonnes au lieu de lignes, adaptez les plages en conséquence.
- Si votre jeu de données contient plusieurs doublons de la valeur la plus basse, SMALL(B2:H2;1) n’exclura qu’une seule occurrence par référence. Pour supprimer plusieurs occurrences, répétez la fonction SMALL avec des valeurs k incrémentées, comme indiqué ci-dessus.
Calculer la moyenne des nombres tout en ignorant la valeur la plus basse ou les N valeurs les plus basses :
Pour calculer la moyenne tout en ignorant la ou les n valeurs les plus basses, vous pouvez utiliser les formules ci-dessous. Ce calcul est particulièrement utile dans les systèmes de notation où les performances exceptionnellement faibles ne doivent pas être prises en compte dans la moyenne.
1. Sélectionnez une cellule pour afficher la moyenne (par exemple, J2 si vos notes se trouvent en B2:H2), puis saisissez la formule suivante :
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. Après avoir appuyé sur Entrée, faites glisser la formule vers le bas selon vos besoins pour calculer la moyenne des lignes supplémentaires, en excluant à chaque fois la note la plus basse de la plage concernée.
Remarques et instructions importantes :
- Pour calculer une moyenne tout en ignorant plus d’une note la plus basse, élargissez la formule en soustrayant des termes SMALLsupplémentaires et en réduisant le diviseur en conséquence :
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2))/(COUNT(B2:H2)-2)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n))/(COUNT(B2:H2)-n)- À nouveau, B2:H2 est la plage à moyenner, et n indique combien de valeurs les plus basses seront exclues du calcul.
- Si vous tentez de soustraire plus de nombres qu’il n’en existe dans la plage, les formules renverront une erreur #NOMBRE!, indiquant qu’il n’y a pas assez de valeurs pour calculer la moyenne. Assurez-vous toujours que n est inférieur au nombre total de valeurs.
- Il est fortement recommandé de vérifier attentivement que les valeurs les plus basses ne sont ni essentielles ni requises pour votre calcul avant de les exclure, car cela pourrait influencer vos résultats finaux.
- Pour des jeux de données très volumineux ou afin d’exclure dynamiquement les n valeurs les plus basses, optez pour une solution automatisée ou matricielle.
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Code VBA – Ignorer la note la plus basse ou les n notes les plus basses et calculer automatiquement la somme ou la moyenne
Dans le cas de jeux de données volumineux ou fréquemment mis à jour, ou lorsque vous devez automatiser l’exclusion des *n* notes les plus basses et le calcul de sommes ou de moyennes sur de nombreuses lignes, VBA devient un atout précieux pour simplifier ces tâches répétitives. Grâce à une macro VBA, il vous suffit de définir la plage de données et le nombre de notes les plus basses à exclure : le code traitera alors efficacement toutes les lignes sélectionnées en une seule opération.
Cette approche automatique est particulièrement utile pour les enseignants qui gèrent des feuilles pour plusieurs classes, ou pour toute personne souhaitant minimiser la saisie manuelle de formules et les erreurs potentielles. La solution ci-dessous vous permet d’ajuster facilement à la fois le nombre d’exclusions et la fonction cible (somme ou moyenne).
Avant de commencer, veuillez enregistrer votre classeur, car l’exécution de macros ne peut pas être annulée directement.
1. Cliquez sur Développeur > Visual Basic. Dans la fenêtre Microsoft Visual Basic pour Applications, cliquez sur Insertion > Module, puis saisissez le code suivant :
Sub DropLowestNandCalculate()
Dim WorkRng As Range
Dim OutputRng As Range
Dim n As Integer
Dim FuncType As String
Dim i As Integer, j As Integer, k As Integer
Dim Arr() As Variant, TempArr() As Double
Dim RowSum As Double
Dim RowCount As Integer
Dim MinIdx() As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the score range (rows to process):", xTitleId, WorkRng.Address, Type:=8)
Set OutputRng = Application.InputBox("Select output cells (top-left for results):", xTitleId, WorkRng.Offset(0, WorkRng.Columns.Count).Cells(1, 1).Address, Type:=8)
n = Application.InputBox("Number of lowest grades to drop (n):", xTitleId, "1", Type:=1)
FuncType = Application.InputBox("Type 'SUM' to calculate total or 'AVG' to calculate average (not case sensitive):", xTitleId, "AVG", Type:=2)
For i = 1 To WorkRng.Rows.Count
Arr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(WorkRng.Rows(i).Value))
RowCount = UBound(Arr)
ReDim TempArr(1 To RowCount)
For j = 1 To RowCount
TempArr(j) = Arr(j)
Next j
' Mark n lowest values as used by setting to very high number
For k = 1 To n
Dim MinVal As Double, MinPos As Integer
MinVal = Application.WorksheetFunction.Min(TempArr)
For j = 1 To RowCount
If TempArr(j) = MinVal Then
TempArr(j) = 1E+308
Exit For
End If
Next j
Next k
RowSum = 0
Dim ValidCount As Integer
ValidCount = 0
For j = 1 To RowCount
If TempArr(j) <> 1E+308 Then
RowSum = RowSum + Arr(j)
ValidCount = ValidCount + 1
End If
Next j
If UCase(FuncType) = "AVG" Then
If ValidCount = 0 Then
OutputRng.Cells(i, 1).Value = "N/A"
Else
OutputRng.Cells(i, 1).Value = RowSum / ValidCount
End If
Else
OutputRng.Cells(i, 1).Value = RowSum
End If
Next i
End Sub2. Une fois le code ajouté, cliquez sur le bouton 
ou appuyez sur F5 pour exécuter.
3. Suivez les invites qui s’affichent :
- Sélectionnez la plage de notes que vous souhaitez traiter (assurez-vous que les notes de chaque élève se trouvent sur une même ligne).
- Choisissez les Zone de placement de la liste premières-La cellule à gauche (le résultat sera rempli vers le bas en fonction du nombre de lignes).
- Indiquez le nombre de notes les plus basses à ignorer (par exemple,)1 pour exclure uniquement la note la plus basse de chaque ligne).
- Saisissez SOMME pour obtenir le total (hors notes exclues) ou MOYENNE pour obtenir la moyenne recalculée (hors notes exclues).
La macro traite chaque ligne de la zone de notes spécifiée et insère soit la somme, soit la moyenne (selon votre choix) dans votre zone de placement de la liste. Si toutes les notes d’une ligne sont exclues, le résultat est marqué N/A afin d’éviter toute erreur.
- Vérifiez que la structure de la plage d’entrée correspond bien à vos données, avec une ligne par élève.
- Les cellules non numériques (par exemple, vides ou contenant du texte) sont ignorées par défaut.
- Ce code VBA accélère considérablement les calculs répétitifs de notation pour des classes entières et permet d’ajuster aisément le nombre de notes à exclure.
- Si vous effectuez fréquemment ce type d’opérations, attribuez cette macro à un bouton sur votre feuille de calcul pour un accès encore plus rapide !
Si vous rencontrez des problèmes tels que des résultats incorrects ou des erreurs, vérifiez attentivement que les plages sont correctement spécifiées et que « n » n’est pas Supérieur ou égal à le nombre total de notes disponibles pour chaque ligne.
Pour des besoins d’automatisation similaires, comme ignorer à la fois les notes les plus hautes et les plus basses ou traiter des colonnes au lieu de lignes, de légers ajustements peuvent être apportés à la logique du code VBA.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels