Wie lässt sich die niedrigste Note streichen und der Durchschnitt oder die Summe der Werte in Excel berechnen?
Wenn Sie in Excel mit einer Liste von Noten oder Bewertungen arbeiten, müssen Sie möglicherweise die Endnote eines Schülers berechnen, indem Sie seine schlechteste Leistung oder sogar die n schlechtesten Leistungen ausschließen, bevor Sie den Durchschnitt oder die Summe der verbleibenden Werte berechnen. Dies ist eine gängige Anforderung im Bildungsbereich, wo Schülern erlaubt sein könnte, ihre schlechtesten Leistungen zu streichen, um Anomalien auszugleichen oder Fairness zu gewährleisten. Diese Berechnung manuell durchzuführen kann mühsam sein, insbesondere bei großen Datensätzen oder häufigen Berechnungsanpassungen. Glücklicherweise bietet Excel mehrere flexible Möglichkeiten, dies zu erreichen – von einfachen Formeln bis hin zur Automatisierung mit VBA für Stapeloperationen.
Streichen Sie die niedrigste Note und berechnen Sie den Durchschnitt oder die Summe mit Formeln
Streichen Sie die niedrigste Note und berechnen Sie den Durchschnitt oder die Summe mit Formeln
Wenn Sie die niedrigste oder die n niedrigsten Werte aus einer Datenreihe oder Liste ausschließen und anschließend Berechnungen wie das Mitteln oder Aufsummieren der verbleibenden Zahlen durchführen möchten, bieten Excels eingebaute Formeln einen praktischen Ansatz. Diese Formellösungen sind besonders nützlich, wenn Sie eine moderate Anzahl von Zeilen zu verarbeiten haben oder einen formelbasierten Ansatz für Transparenz und leichte Anpassbarkeit vorziehen.
Im Folgenden finden Sie getrennte Erklärungen für Summierungs- und Mittelungsmethoden, einschließlich detaillierter Anweisungen und praktischer Tipps für eine flexible Anwendung.
Summieren Sie die Zahlen, aber streichen Sie die niedrigste oder die n niedrigsten Zahlen:
Um die Summe für jede Zeile oder Liste zu berechnen, während Sie den niedrigsten Wert auslassen, verwenden Sie die folgende Methode:
1. Wählen Sie eine leere Zelle, in der das Summenergebnis für die erste Zeile erscheinen soll (z. B. in Zelle I2, wenn Ihre Daten in B2:H2 stehen), und geben Sie dann die folgende Formel ein:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Drücken Sie die Eingabetaste, um zu bestätigen, und ziehen Sie dann den Ausfüllkästchen nach unten, um diese Formel bei Bedarf auf andere Zeilen anzuwenden. Dadurch werden alle Zahlen in jeder Zeile summiert, wobei der niedrigste Wert ausgeschlossen wird.
Hier ist ein Screenshot zur Veranschaulichung:
Hinweise und Tipps:
- Um die zwei, drei oder mehr niedrigsten Werte auszuschließen, können Sie die Formel erweitern, indem Sie weitere SMALL-Ergebnisse subtrahieren. Zum Beispiel:
=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)- In diesen Formeln ist B2:H2 der Bereich, den Sie summieren möchten, und die Zahlen 1, 2, 3 usw. geben die n kleinsten Zahlen an, die ausgeschlossen werden sollen. Passen Sie n entsprechend der Anzahl der schlechtesten Noten an, die Sie streichen möchten.
- Achten Sie darauf, n nicht größer oder gleich der Gesamtzahl der Werte zu setzen; andernfalls erhalten Sie Fehler oder unerwünschte Ergebnisse.
- Diese Formeln funktionieren unabhängig für jede Zeile. Wenn Ihre Daten über Spalten statt über Zeilen verteilt sind, passen Sie die Bereiche entsprechend an.
- Wenn Ihr Datensatz Duplikate der niedrigsten Zahl enthält, SMALL(B2:H2,1) pro Referenz nur ein Vorkommen streichen. Um mehrere Vorkommen zu streichen, wiederholen Sie den SMALL-Term mit erhöhten k-Werten, wie oben gezeigt.
Bilden Sie den Durchschnitt der Zahlen, aber streichen Sie die niedrigste oder die n niedrigsten Zahlen:
Um den Durchschnitt zu berechnen, während Sie die niedrigsten oder die n niedrigsten Werte ignorieren, können Sie die folgenden Formeln verwenden. Diese Berechnung ist besonders nützlich in Bewertungsschemata, in denen Ausreißer bei schlechten Leistungen nicht in die Mittelwertberechnung einbezogen werden sollen.
1. Wählen Sie eine Zelle für das Durchschnittsergebnis (z. B. J2, wenn Ihre Noten in B2:H2 stehen), und geben Sie die Formel ein:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. Nachdem Sie die Eingabetaste gedrückt haben, ziehen Sie die Formel bei Bedarf nach unten, um weitere Zeilen zu mitteln, wobei jedes Mal die niedrigste Note im Bereich dieser Zeile ausgeschlossen wird.
Hinweise und wichtige Anweisungen:
- Um den Durchschnitt zu berechnen, während Sie mehr als eine der schlechtesten Noten streichen, erweitern Sie die Formel, indem Sie weitere SMALL-Terme subtrahieren und den Divisor entsprechend verringern:
=(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)- Nochmals, B2:H2 ist der zu mittelnde Bereich, und n steht für die Anzahl der niedrigsten Werte, die nicht in die Berechnung einbezogen werden sollen.
- Wenn Sie versuchen, mehr Zahlen zu subtrahieren, als im Bereich vorhanden sind, geben die Formeln einen #ZAHL!-Fehler zurück, was auf zu wenige Werte für die Mittelwertberechnung hinweist. Stellen Sie immer sicher, dass n kleiner als die Anzahl der Zahlen ist.
- Es wird empfohlen, doppelt zu prüfen, ob die niedrigsten Werte nicht kritisch oder für Ihre Berechnung erforderlich sind, bevor Sie sie ausschließen, da dies die Endergebnisse beeinflussen kann.
- Für extrem große Datensätze oder dynamisches Streichen der niedrigsten n Werte sollten Sie eine automatisierte oder Array-Lösung in Betracht ziehen.
Entfesseln Sie die Magie von Excel mit Kutools AI
- Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
- VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
- Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.
VBA-Code – Streichen Sie die niedrigste oder die n niedrigsten Noten und berechnen Sie die Summe oder den Durchschnitt automatisch
Für Situationen mit großen oder sich häufig ändernden Datensätzen oder wenn Sie das automatische Streichen der n niedrigsten Noten und die Berechnung von Summen oder Durchschnitten über viele Zeilen hinweg automatisieren müssen, kann die Verwendung von VBA repetitive Arbeiten stark vereinfachen. Mit einem VBA-Makro können Sie den Datenbereich und die Anzahl der auszuschließenden schlechtesten Noten angeben, und der Code verarbeitet alle ausgewählten Zeilen effizient in einem Schritt.
Dieser automatische Ansatz ist besonders nützlich für Lehrkräfte, die Tabellen für mehrere Klassen verwalten, oder für jeden, der manuelle Formeleingaben und potenzielle Fehler minimieren möchte. Die folgende Lösung ermöglicht es Ihnen, sowohl die Anzahl der auszuschließenden Noten als auch die Ziel-Funktion (Summe oder Durchschnitt) leicht anzupassen.
Bevor Sie beginnen, speichern Sie bitte Ihre Arbeitsmappe, da ausgeführte Makros nicht direkt rückgängig gemacht werden können.
1. Klicken Sie auf Entwickler > Visual Basic. Im Fenster Microsoft Visual Basic for Applications klicken Sie auf Einfügen > Modul, und geben Sie dann den folgenden Code ein:
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. Nachdem Sie den Code hinzugefügt haben, klicken Sie auf die 
Schaltfläche oder drücken Sie F5 zur Ausführung.
3. Folgen Sie den erscheinenden Anweisungen:
- Wählen Sie den zu verarbeitenden Notenbereich aus (stellen Sie sicher, dass die Noten jedes Schülers in einer Zeile stehen).
- Wählen Sie die linke obere Zelle des Ausgabebereichs (die Ausgabe wird basierend auf der Zeilenanzahl nach unten gefüllt).
- Geben Sie die Anzahl der schlechtesten Noten ein, die gestrichen werden sollen (z. B. 1, um nur die schlechteste Note in jeder Zeile auszuschließen).
- Tippen Sie SUMME, um die Gesamtsumme (ohne gestrichene Noten) oder AVG, um den neu berechneten Durchschnitt (ohne gestrichene Noten) zu erhalten.
Das Makro verarbeitet jede Zeile aus dem angegebenen Notenbereich und platziert entweder die Summe oder den Durchschnitt (je nach Wahl) in den Ausgabebereich. Wenn alle Noten in einer Zeile gestrichen werden, wird das Ergebnis als N/A markiert, um Fehler zu vermeiden.
- Stellen Sie sicher, dass der Eingabebereich Ihrer Datenstruktur entspricht (eine Schülerzeile pro Zeile).
- Nicht-numerische Zellen (z. B. Leer- oder Textfelder) werden standardmäßig ignoriert.
- Dieser VBA-Code beschleunigt repetitive Notenberechnungen für ganze Klassen erheblich und unterstützt flexible Anpassungen der Anzahl der zu streichenden Noten.
- Wenn Sie solche Operationen häufig durchführen, könnten Sie dieses Makro einem Button auf Ihrem Arbeitsblatt zuweisen, um noch schnelleren Zugriff zu ermöglichen.
Wenn Sie Probleme wie falsche Ausgaben oder Fehler feststellen, überprüfen Sie, ob die Bereiche korrekt angegeben sind und dass "n" nicht größer oder gleich der Gesamtzahl der verfügbaren Noten pro Zeile ist.
Für ähnliche Automatisierungsanforderungen, wie das Streichen der höchsten und niedrigsten Noten oder das Bearbeiten von Spalten anstelle von Zeilen, können geringfügige Anpassungen an der VBA-Codelogik vorgenommen werden.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins