Wie berechnet man in Excel ausschließlich den Durchschnitt der positiven bzw. negativen Zahlen?
In Excel ist es oft erforderlich, den Durchschnitt nur der positiven oder nur der negativen Zahlen in einem Bereich zu berechnen, der beide Wertetypen enthält – besonders bei der Analyse von Szenarien wie Gewinne versus Verluste, Einnahmen gegenüber Ausgaben oder anderen Fällen, in denen positive und negative Zahlen unterschiedliche Bedeutungen tragen. Die Standardfunktion MITTELWERT berücksichtigt standardmäßig sowohl positive als auch negative Werte, was häufig nicht gewünscht ist. In diesem Tutorial stellen wir praxiserprobte Methoden vor, um gezielt den Durchschnitt ausschließlich positiver oder ausschließlich negativer Zahlen in Excel zu ermitteln. Sie erhalten Schritt-für-Schritt-Anleitungen zu Formeln, integrierten Tools und VBA-Code – passend zu unterschiedlichen Anwenderbedürfnissen.
Berechnen des Durchschnitts nur positiver oder negativer Zahlen mithilfe von Formeln
Anzeigen des Durchschnitts nur positiver oder negativer Zahlen mit Kutools für Excel 
Automatische Berechnung des Durchschnitts nur positiver oder negativer Zahlen mithilfe von VBA-Code
Berechnen des Durchschnitts nur positiver oder negativer Zahlen mithilfe von Formeln
Um ausschließlich den Durchschnitt der positiven Zahlen in einem Bereich zu berechnen, bietet Excel Matrixformeln, die gezielt nur die Werte berücksichtigen, die Ihre festgelegte Bedingung erfüllen – ideal, wenn Sie auf Add-Ins oder zusätzliche Tools verzichten und den Durchschnitt schnell und direkt im Arbeitsblatt per Formel ermitteln möchten.
1. Geben Sie die folgende Formel in eine leere Zelle ein, in der das Ergebnis angezeigt werden soll:
=AVERAGE(IF(A1:D10>0,A1:D10,""))In diesem Beispiel repräsentiert A1:D10 den Datenbereich, der sowohl positive als auch negative Zahlen enthält.
2. Drücken Sie nach der Eingabe der Formel gleichzeitig Strg + Umschalt + Enter, um sie als Matrixformel zu bestätigen. Wenn alles korrekt ausgeführt wurde, erscheinen geschweifte Klammern { } um Ihre Formel in der Formelleiste – wie unten dargestellt:
Erläuterung und Anpassbarkeit der Formel:
- Diese Technik funktioniert sowohl für horizontale als auch für vertikale Bereiche – passen Sie den Bereich einfach an Ihr Arbeitsblatt an.
- Befinden sich keine positiven Zahlen in Ihrem Bereich und verwenden Sie diese Formel für positive Werte, zeigt das Ergebnis einen #DIV/0!-Fehler an, da keine qualifizierenden Zahlen zum Mitteln vorhanden sind. Gleiches gilt für negative Zahlen, wenn Sie die unten gezeigte Formel für negative Werte nutzen. Vermeiden Sie dies ganz einfach, indem Sie die Formel zur Erhöhung der Robustheit in
WENNFEHLEReinbetten:
=IFERROR(AVERAGE(IF(A1:D10>0,A1:D10,"")), "")Verwenden Sie zur Berechnung des Durchschnitts nur der negativen Zahlen die folgende Formel:
=AVERAGE(IF(A1:D10<0,A1:D10,""))- Denken Sie daran, nach der Eingabe der Formel Strg + Umschalt + Enter zu drücken, damit sie ordnungsgemäß funktioniert.
Hinweise:
1. A1:D10 ist der Bereich, für den Sie den bedingten Durchschnitt berechnen möchten – passen Sie ihn entsprechend Ihren Daten an.
2. Wenn der Durchschnitt Nullwerte oder bestimmte Werte ignorieren soll, passen Sie den logischen Test innerhalb der Formel einfach gezielt an.
3. Diese Vorgehensweise können Sie in Excel 365 oder Excel 2019 und neueren Versionen verwenden, ohne Strg + Umschalt + Enter zu drücken, da dynamische Arrays nativ unterstützt werden. In älteren Versionen müssen Sie die Tastenkombination für Matrixformeln verwenden.
4. Enthalten Ihre Daten Fehler (wie z. B.)#DIV/0! oder #N/A), gibt die Formel ebenfalls einen Fehler zurück. Verwenden Sie in solchen Fällen die Funktion WENNFEHLER, um Ausnahmen elegant zu behandeln.
Anzeigen des Durchschnitts nur positiver oder negativer Zahlen mit Kutools für Excel
Wenn Sie Kutools für Excel installiert haben, ermöglicht Ihnen die Funktion Bestimmte Zellen auswählen, schnell nur die positiven oder nur die negativen Zahlen innerhalb eines Bereichs auszuwählen. Der Durchschnitt wird dann direkt in der Excel-Statusleiste bequem angezeigt – ganz ohne zusätzliche Formeln eingeben zu müssen. Diese Methode eignet sich besonders für Benutzer, die eine visuelle und interaktive Möglichkeit bevorzugen, um Daten nach Typ zusammenzufassen, ohne komplexe Formeln verwenden zu müssen.
1. Wählen Sie den Datenbereich aus, der sowohl positive als auch negative Zahlen enthält, die Sie analysieren möchten.
2. Gehen Sie zu Kutools > Auswählen > Bestimmte Zellen auswählen, wie unten dargestellt:
3. Führen Sie im Dialogfeld Bestimmte Zellen auswählen Folgendes aus:
- Wählen Sie die Option Zelle unter Auswahltyp.
- Legen Sie die Bedingung für positive Zahlen fest, indem Sie im Dropdown-Menü unter Typ angeben die Option Größer als auswählen und im Wertefeld 0 eingeben.
- Für negative Zahlen wählen Sie Kleiner als und geben erneut 0 ein.
Klicken Sie auf OK, und Kutools wählt automatisch die Zellen aus, die Ihren Kriterien entsprechen. Anschließend bestätigt ein Dialogfeld die ausgewählten Zellen.
4. Sobald die Zellen ausgewählt sind, sehen Sie den Durchschnittswert der markierten Zellen direkt in der Excel-Statusleiste unten rechts im Fenster. Die Berechnung erfolgt in Echtzeit – ganz ohne Formeleingabe!
 |  |
| Das Ergebnis nur der positiven Zahlen | Das Ergebnis nur der negativen Zahlen |
Vorteile und Hinweise:
- Keine Formeln schreiben oder merken nötig – ideal für schnelle Ad-hoc-Analysen.
- Die Methode eignet sich ideal für kleine bis mittlere Datensätze. Enthält Ihre Auswahl jedoch zahlreiche nicht-numerische Zellen oder Fehler, prüfen Sie die Zusammenfassung in der Statusleiste manuell, um die Genauigkeit sicherzustellen.
- Sie können auch mit der rechten Maustaste auf die Statusleiste klicken, um auszuwählen, welche Berechnungen (z. B. Durchschnitt, Summe oder Anzahl) angezeigt werden sollen.
Demo: Summe/Durchschnitt/Anzahl nur positiver oder negativer Zahlen mit Kutools für Excel Kutools für Excel: Über 300 praktische Werkzeuge griffbereit! Nutzen Sie KI-gestützte Funktionen für intelligenteres und schnelleres Arbeiten!Jetzt herunterladen!
Automatische Berechnung des Durchschnitts nur positiver oder negativer Zahlen mithilfe von VBA-Code
Für Benutzer, die diese Durchschnitte häufig für verschiedene Bereiche berechnen oder den Prozess automatisieren möchten, spart ein einfaches VBA-Makro Zeit und erhöht die Genauigkeit. Dieser Ansatz eignet sich besonders gut bei sich wiederholenden Aufgaben oder komplexen Datenlayouts – vorausgesetzt, Sie sind mit dem Visual Basic for Applications (VBA)-Editor in Excel vertraut.
1. Klicken Sie auf Entwicklertools > Visual Basic, um das Fenster Microsoft Visual Basic for Applications zu öffnen. Klicken Sie im Editor auf Einfügen > Modul, und fügen Sie anschließend einen der folgenden Codes in das neue Modul ein.
Verwenden Sie das folgende Makro, um den Durchschnitt nur der positiven Zahlen in einem Bereich auswählen zu berechnen:
Sub AveragePositiveNumbers()
Dim rng As Range
Dim cell As Range
Dim sum As Double
Dim count As Long
Dim result As Variant
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set rng = Application.Selection
Set rng = Application.InputBox("Please select the range to average positive numbers", xTitleId, rng.Address, Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
sum = 0
count = 0
For Each cell In rng
If IsNumeric(cell.Value) And cell.Value > 0 Then
sum = sum + cell.Value
count = count + 1
End If
Next cell
If count > 0 Then
result = sum / count
MsgBox "The average of only the positive numbers is " & result, vbInformation, xTitleId
Else
MsgBox "No positive numbers found in the selected range.", vbExclamation, xTitleId
End If
End SubUm den Durchschnitt nur der negativen Zahlen zu berechnen, verwenden Sie den folgenden Code:
Sub AverageNegativeNumbers()
Dim rng As Range
Dim cell As Range
Dim sum As Double
Dim count As Long
Dim result As Variant
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set rng = Application.Selection
Set rng = Application.InputBox("Please select the range to average negative numbers", xTitleId, rng.Address, Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
sum = 0
count = 0
For Each cell In rng
If IsNumeric(cell.Value) And cell.Value < 0 Then
sum = sum + cell.Value
count = count + 1
End If
Next cell
If count > 0 Then
result = sum / count
MsgBox "The average of only the negative numbers is " & result, vbInformation, xTitleId
Else
MsgBox "No negative numbers found in the selected range.", vbExclamation, xTitleId
End If
End Sub2. Kehren Sie nach der Eingabe des Makros zu Excel zurück. Drücken Sie F5 oder klicken Sie auf Ausführen. Im erscheinenden Dialogfeld können Sie den Bereich für Ihre Berechnung auswählen – der Durchschnitt (oder eine Warnung, falls keine passenden Zahlen gefunden wurden) wird anschließend in einem Meldungsfeld angezeigt.
Tipps und Fehlerbehebung:
- Speichern Sie Ihre Arbeitsmappe unbedingt als makrofähige Datei ()
.xlsm), wenn Sie Ihre Makros behalten und wiederverwenden möchten. - Dieses Makro berechnet ausschließlich den Durchschnitt numerischer Zellen – Textzellen, leere Zellen sowie Fehler werden automatisch ignoriert.
- Wenn Ihr Datensatz sehr große Datenmengen oder häufige Änderungen umfasst, spart Ihnen die Automatisierung mit VBA Zeit und vermeidet manuelle Fehler.
- Sollte eine Makrosicherheitswarnung erscheinen, passen Sie Ihre Makroeinstellungen unter „Excel-Optionen > Vertrauensstellungscenter“ an, um die Ausführung von Makros zu ermöglichen.
Berücksichtigen Sie bei der Wahl einer Methode Ihren eigenen Arbeitsablauf und Ihre Excel-Kenntnisse:
- Formeln sind schnell und flexibel, erfordern aber die Eingabe als Matrixformel sowie korrekte Bezüge.
- Kutools ist effizient für interaktive Aufgaben und erspart Ihnen die manuelle Eingabe von Formeln.
- VBA-Makros eignen sich ideal für wiederkehrende oder automatisierte Berichtsumgebungen.
Sollten Sie auf Berechnungsfehler oder unerwartete Ergebnisse stoßen, prüfen Sie zunächst, ob Ihre Bereiche unerwünschte Datentypen oder Fehler enthalten. Stellen Sie sicher, dass Sie die richtige Bedingung verwenden – beispielsweise „größer als null“ oder „kleiner als null“ – und passen Sie gegebenenfalls die Bezüge an. Für weitergehende Ausschlüsse, etwa das Ignorieren von Fehlern oder bestimmten Werten, integrieren Sie geeignete Fehlerbehandlungs- oder Filterlogik direkt in Ihre Formeln oder Ihr VBA.
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