Comment supprimer la note la plus basse et obtenir la moyenne ou le total des valeurs dans Excel ?
Lorsque vous travaillez avec une liste de notes ou de scores dans Excel, vous pourriez avoir besoin de calculer la note finale d'un étudiant 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. C'est une exigence courante dans les milieux éducatifs, où il est possible que les étudiants puissent ignorer leurs pires performances pour tenir compte des anomalies ou assurer l'équité. Effectuer cette opération manuellement pourrait s'avérer fastidieux, surtout avec de grands ensembles de données ou des ajustements fréquents de calculs. Heureusement, Excel propose plusieurs façons flexibles d'atteindre cet objectif, allant de formules simples à l'automatisation avec VBA pour des opérations par lots.
Supprimer la note la plus basse et obtenir la moyenne ou la sommation avec des formules
Supprimer la note la plus basse et obtenir la moyenne ou la sommation avec des formules
Si vous souhaitez exclure la note la plus basse ou les n notes les plus basses d'une ligne ou d'une liste de données, puis effectuer des calculs tels que la moyenne ou la somme des nombres restants, les formules intégrées d'Excel offrent une approche pratique. Ces solutions basées sur des formules sont particulièrement utiles si vous avez un nombre modéré de lignes à traiter ou si vous préférez une méthode basée sur des formules pour plus de transparence et de facilité d'ajustement.
Ci-dessous, vous trouverez des explications distinctes pour les méthodes de sommation et de moyenne, y compris des instructions détaillées et des conseils pratiques pour une application flexible.
Faire la somme des nombres mais supprimer la note la plus basse ou les N notes 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 que le résultat de la somme de la première ligne apparaisse (par exemple, dans la cellule I2 si vos données sont dans B2:H2), puis entrez la formule suivante :
=SUM(B2:H2)-SMALL(B2:H2,1)2. Appuyez sur Entrée pour confirmer, puis faites glisser la poignée de recopie vers le bas pour appliquer cette formule aux autres lignes si nécessaire. Cela fera la somme de tous les nombres dans chaque ligne, en excluant la valeur la plus basse.
Voici une capture d'écran à titre de référence :
Remarques et conseils :
- Pour exclure les deux, trois, ou plus petites valeurs, 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., spécifient les n plus petits nombres à exclure. Ajustez n en fonction du nombre de notes les plus basses que vous souhaitez supprimer.
- Veillez à ne pas définir n supérieur ou égal au nombre total de valeurs ; sinon, vous rencontrerez des erreurs ou des résultats indésirables.
- Ces formules fonctionnent indépendamment pour les lignes. Si vos données couvrent des colonnes plutôt que des lignes, ajustez les plages en conséquence.
- Si votre ensemble de données contient des doublons du plus petit nombre, SMALL(B2:H2,1) ne supprimera qu'une seule occurrence par référence. Pour supprimer plusieurs occurrences, répétez le terme SMALL avec des nombres k incrémentés comme indiqué ci-dessus.
Moyenne des nombres mais supprimer la note la plus basse ou les N notes les plus basses :
Pour calculer la moyenne tout en ignorant la note la plus basse ou les n notes les plus basses, vous pouvez utiliser les formules ci-dessous. Ce calcul est particulièrement utile dans les systèmes de notation où les faibles performances aberrantes ne doivent pas être prises en compte dans la moyenne.
1. Sélectionnez une cellule pour le résultat moyen (par exemple, J2 si vos scores sont dans B2:H2), puis entrez la formule :
=(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 si nécessaire pour calculer la moyenne des lignes supplémentaires, en omettant à chaque fois la note la plus basse dans la plage correspondante.
Remarques et conseils importants :
- Pour faire la moyenne tout en supprimant plus d'une note basse, étendez la formule en soustrayant des termes SMALL supplé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)- Encore une fois, B2:H2 est la plage à moyenner, et n représente combien de valeurs les plus basses ne seront pas incluses dans le calcul.
- Si vous essayez de soustraire plus de nombres qu'il n'y en a dans la plage, les formules renverront une erreur #NOMBRE!, indiquant qu'il n'y a pas assez de valeurs pour faire la moyenne. Assurez-vous toujours que n est inférieur au nombre de chiffres.
- Il est recommandé de vérifier deux fois que les valeurs les plus basses ne sont pas critiques ou nécessaires pour votre calcul avant de les exclure, car cela peut impacter les résultats finaux.
- Pour les très grands ensembles de données ou la suppression dynamique des n valeurs les plus basses, envisagez une solution automatisée ou en tableau.
Découvrez la magie d'Excel avec Kutools AI
- Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
- Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
- Codage VBA : Écrivez et implémentez du code VBA sans effort.
- Interprétation des formules : Comprenez facilement des formules complexes.
- Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.
Code VBA - Supprimer la note la plus basse ou les n notes les plus basses et calculer la somme ou la moyenne automatiquement
Pour les situations impliquant de grands ensembles de données ou des données changeant fréquemment, ou lorsque vous devez automatiser la suppression des n notes les plus basses et le calcul des sommes ou moyennes sur de nombreuses lignes, utiliser VBA peut grandement simplifier les travaux répétitifs. Avec une macro VBA, vous pouvez spécifier la plage de données et combien de notes les plus basses exclure, et le code traitera toutes les lignes sélectionnées efficacement en une seule étape.
Cette approche automatique est particulièrement utile pour les enseignants qui gèrent des feuilles pour plusieurs classes, ou pour toute personne qui souhaite 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 Insérer > 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. Après avoir ajouté le code, cliquez sur le 
bouton ou appuyez sur F5 pour exécuter.
3. Suivez les invites qui apparaissent :
- Sélectionnez la plage de scores que vous souhaitez traiter (assurez-vous que les scores de chaque étudiant sont sur une ligne).
- Choisissez la cellule en haut à gauche de la plage de sortie (la sortie se remplira vers le bas en fonction du nombre de lignes).
- Entrez le nombre de notes les plus basses à supprimer (par exemple, 1 pour exclure uniquement la note la plus basse dans chaque ligne).
- Tapez SOMME pour obtenir le total (en excluant les notes supprimées) ou MOYENNE pour obtenir la moyenne recalculée (en excluant les notes supprimées).
La macro traite chaque ligne de la zone de score spécifiée et place soit la somme, soit la moyenne (selon le choix) dans votre plage de sortie. Si toutes les notes sont supprimées dans une ligne, le résultat est marqué comme N/A pour éviter les erreurs.
- Assurez-vous que la plage d'entrée correspond à la structure de vos données (les scores d'un étudiant par ligne).
- Les cellules non numériques (par exemple, vides ou texte) seront 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 prend en charge l'ajustement flexible du nombre de notes à supprimer.
- Si vous effectuez fréquemment de telles opérations, vous pouvez attribuer 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 sorties incorrectes ou des erreurs, revérifiez que les plages sont précisément spécifiées et que « n » n'est pas supérieur ou égal au nombre total de notes disponibles pour chaque ligne.
Pour des besoins d'automatisation similaires, tels que la suppression des notes les plus élevées et les plus basses ou la gestion des colonnes au lieu des lignes, de légères modifications peuvent être apportées à la logique du code VBA.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels