Wie streicht man die niedrigste Note und berechnet anschließend den Durchschnitt oder die Summe der verbleibenden Werte in Excel?
Wenn Sie mit einer Liste von Noten oder Punktzahlen in Excel arbeiten, müssen Sie möglicherweise die Endnote eines Schülers berechnen, indem Sie seine niedrigste Note – oder sogar die n niedrigsten Noten – vor der Mittelwert- oder Summenbildung ausschließen. Dies ist eine häufige Anforderung im Bildungsbereich, wo Schülern erlaubt wird, ihre schlechtesten Leistungen zu streichen, um Ausreißer zu berücksichtigen oder Fairness sicherzustellen. Eine manuelle Durchführung dieses Vorgangs wäre mühsam, insbesondere bei großen Datensätzen oder häufigen Anpassungen der Berechnung. Glücklicherweise bietet Excel mehrere flexible Möglichkeiten, dies zu erreichen – von einfachen Formeln bis hin zur Automatisierung mit VBA für Batch-Vorgänge.
Streichen der niedrigsten Note und Berechnung des Durchschnitts oder der Summe mithilfe von Formeln
Streichen der niedrigsten Note und Berechnung des Durchschnitts oder der Summe mithilfe von Formeln
Wenn Sie die niedrigsten oder die n niedrigsten Werte aus einer Datenzeile oder -liste ausschließen und anschließend Berechnungen wie die Mittelwertbildung oder Summierung der verbleibenden Zahlen durchführen möchten, bieten die integrierten Excel-Formeln eine praktikable Lösung. Diese Formelansätze eignen sich besonders gut, wenn Sie eine überschaubare Anzahl von Zeilen verarbeiten müssen oder einen formelbasierten Ansatz aufgrund seiner Transparenz und einfachen Anpassbarkeit bevorzugen.
Im Folgenden erhalten Sie klare, getrennte Erläuterungen zu Summierungs- und Mittelwertmethoden – inklusive detaillierter Anleitungen und praktischer Tipps für eine flexible Anwendung.
Summieren der Zahlen unter Ausschluss der niedrigsten oder der n niedrigsten Zahlen:
Verwenden Sie zur Berechnung der Summe pro Zeile oder Liste unter Ausschluss des niedrigsten Werts die folgende Methode:
1. Wählen Sie eine leere Zelle aus, in der das Summenergebnis für die erste Zeile erscheinen soll (z. B. Zelle I2, wenn sich Ihre Daten in B2:H2 befinden), und geben Sie anschließend die folgende Formel ein:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Drücken Sie Enter, um die Eingabe zu bestätigen, und ziehen Sie anschließend den Ausfüllknauf nach unten, um die Formel bei Bedarf auf weitere Zeilen anzuwenden. So berechnen Sie automatisch die Summe aller Zahlen in jeder Zeile – ohne den niedrigsten Wert.
Hier sehen Sie eine Referenzabbildung:
Hinweise und Tipps:
- Um die niedrigsten zwei, drei oder mehr Werte auszuschließen, erweitern Sie die Formel einfach, indem Sie zusätzliche SMALL-Ergebnisse abziehen. 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. legen fest, welche der kleinsten Werte jeweils ausgeschlossen werden sollen. Passen Sie n entsprechend der Anzahl der niedrigsten Noten an, die Sie streichen möchten.
- Achten Sie darauf, n nicht größer als oder gleich der Gesamtanzahl der Werte zu setzen – andernfalls treten Fehler oder unerwünschte Ergebnisse auf.
- Diese Formeln arbeiten zeilenweise unabhängig voneinander. Sollten Ihre Daten stattdessen spaltenweise organisiert sein, passen Sie die Bereiche entsprechend an.
- Falls Ihr Datensatz Duplikate des niedrigsten Werts enthält, streicht SMALL(B2:H2;1) pro Verweis nur ein Vorkommen. Um mehrere Vorkommen zu streichen, wiederholen Sie den SMALL-Term mit schrittweise erhöhten k-Werten, wie oben gezeigt.
Mittelwert der Zahlen unter Ausschluss der niedrigsten oder der n niedrigsten Zahlen:
Zur Berechnung des Durchschnitts unter Ignorierung der niedrigsten oder n niedrigsten Werte können Sie die folgenden Formeln verwenden. Diese Berechnung ist besonders nützlich in Benotungssystemen, bei denen außergewöhnlich schlechte Leistungen bei der Mittelwertbildung nicht berücksichtigt werden sollen.
1. Wählen Sie eine Zelle für das Durchschnittsergebnis aus (z. B. J2, wenn sich Ihre Noten in B2:H2 befinden), und geben Sie die folgende Formel ein:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. Ziehen Sie die Formel nach dem Drücken der Enter-Taste bei Bedarf nach unten, um weitere Zeilen zu mitteln – wobei jeweils die niedrigste Note im jeweiligen Bereich dieser Zeile ausgeschlossen wird.
Hinweise und wichtige Anleitungen:
- Um den Durchschnitt zu berechnen, während mehr als eine niedrigste Note gestrichen wird, erweitern Sie die Formel, indem Sie zusätzliche SMALL-Terme subtrahieren und den Divisor entsprechend reduzieren:
=(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)- Auch hier ist B2:H2 der zu mittelnde Bereich, und n gibt an, wie viele der niedrigsten Werte bei der Berechnung nicht berücksichtigt werden.
- Wenn Sie versuchen, mehr Zahlen zu subtrahieren, als im Bereich vorhanden sind, geben die Formeln einen #NUM!-Fehler zurück, der darauf hinweist, dass nicht genügend Werte für die Mittelwertbildung vorliegen. Stellen Sie stets sicher, dass n kleiner ist als die Anzahl der Zahlen.
- Prüfen Sie sorgfältig, ob die niedrigsten Werte für Ihre Berechnung entscheidend oder erforderlich sind, bevor Sie sie ausschließen – denn dies kann Ihre Endergebnisse beeinflussen.
- Für sehr große Datensätze oder das dynamische Streichen der niedrigsten n Werte empfiehlt sich eine automatisierte oder arraybasierte Lösung.
Entfesseln Sie die Magie von Excel mit KUTOOLS AI
- Intelligente Ausführung: Führen Sie Zelloperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles ganz einfach per Sprachbefehl.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe optimal zu optimieren.
- VBA-Programmierung: Schreiben und implementieren Sie VBA-Code ganz mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln spielend leicht.
- Textübersetzung: Überwinden Sie Sprachbarrieren direkt in Ihren Tabellenkalkulationen.
VBA-Code – Streichen der niedrigsten oder der n niedrigsten Noten und automatische Berechnung von Summe oder Durchschnitt
Für Szenarien mit großen oder häufig wechselnden Datensätzen – oder wenn Sie das Streichen der niedrigsten *n* Noten sowie die Berechnung von Summen oder Durchschnitten über viele Zeilen hinweg automatisieren möchten – vereinfacht VBA Ihre wiederkehrenden Aufgaben erheblich. Mit einem VBA-Makro legen Sie einfach den Datenbereich und die Anzahl der auszuschließenden niedrigsten Noten fest, und der Code verarbeitet alle ausgewählten Zeilen effizient in einem einzigen Schritt.
Dieser automatisierte Ansatz ist besonders wertvoll für Lehrkräfte, die Tabellen für mehrere Klassen verwalten, oder für alle, die manuelle Formeleingaben und potenzielle Fehler reduzieren möchten. Mit der folgenden Lösung passen Sie mühelos sowohl die Anzahl der auszuschließenden Noten als auch die gewünschte Ziel-Funktion – Summe oder Durchschnitt – an.
Speichern Sie Ihre Arbeitsmappe vor Beginn, denn Makros lassen sich nicht direkt rückgängig machen.
1. Klicken Sie auf Entwicklertools > Visual Basic. Klicken Sie im Fenster Microsoft Visual Basic für Applikationen 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. Klicken Sie nach dem Einfügen des Codes auf die Schaltfläche 
oder drücken Sie F5, um das Makro auszuführen.
3. Folgen Sie einfach den angezeigten Aufforderungen:
- Wählen Sie den Notenbereich aus, den Sie verarbeiten möchten – achten Sie dabei darauf, dass die Noten jedes Schülers in einer einzigen Zeile stehen.
- Wählen Sie die Zelle links oberhalb des Platzierungsbereichs der Liste aus (die Ausgabe erfolgt zeilenweise nach unten).
- Geben Sie die Anzahl der niedrigsten Noten ein, die gestrichen werden sollen (z. B.)1, um jeweils nur die niedrigste Note in jeder Zeile auszuschließen).
- Geben Sie SUM ein, um die Summe (ohne gestrichene Noten) zu erhalten, oder AVG, um den neu berechneten Durchschnitt (ohne gestrichene Noten) zu erhalten.
Das Makro verarbeitet jede Zeile des angegebenen Notenbereichs und platziert – je nach Auswahl – entweder die Summe oder den Durchschnitt in Ihren Platzierungsbereich der Liste. Falls in einer Zeile alle Noten gestrichen werden, wird das Ergebnis als N/A markiert, um Fehler zu vermeiden.
- Stellen Sie sicher, dass Ihr Eingabebereich der Datenstruktur entspricht – eine Zeile pro Schüler.
- Nicht-numerische Zellen – wie leere Zellen oder Text – werden standardmäßig ignoriert.
- Dieser VBA-Code beschleunigt wiederkehrende Notenberechnungen für ganze Klassen erheblich und ermöglicht eine flexible Anpassung der Anzahl der zu streichenden Noten.
- Wenn Sie solche Vorgänge häufig durchführen, weisen Sie dieses Makro einem Schaltflächen-Steuerelement in Ihrem Arbeitsblatt zu – für noch schnelleren Zugriff.
Sollten Sie auf Probleme wie falsche Ausgaben oder Fehler stoßen, überprüfen Sie sorgfältig, ob die Bereiche korrekt angegeben wurden und ob „n“ nicht größer als oder gleich der Gesamtanzahl der verfügbaren Noten pro Zeile ist.
Für ähnliche Automatisierungsanforderungen – etwa das gleichzeitige Streichen der höchsten und niedrigsten Noten oder die Verarbeitung von Spalten anstelle von Zeilen – lassen sich mit minimalen Anpassungen an der VBA-Code-Logik umsetzen.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins