In diesem Artikel erfahren Sie, wie Sie die durchschnittlichen Zahlen erhalten, wenn sie in Excel nicht leer sind.
Problem?
Zum Beispiel haben wir eine große Liste von Daten und wir müssen den Durchschnitt des Preises oder Betrags anhand einiger Kriterien ermitteln. Die Kriterien werden nur dann durchschnittlich, wenn die Zellen nicht leer sind. Kriterien können auf jede Spalte der Daten angewendet werden.
So lösen Sie das Problem.
Jetzt machen wir eine Formel aus der Funktion. Hier werden uns die Daten gegeben und wir mussten den DURCHSCHNITT der Zahlen finden, die leere Zellen in Excel ignorieren
Generische Formel:
= AVERAGEIFS ( Average_range , range, "")
- Average_range : Wertebereich, in dem der Durchschnitt ausgewertet werden muss
- Bereich : Bereich, in dem das Kriterium angewendet wird.
- "" : Kriterien, ignoriert leere Zellen.
Beispiel:
All dies kann verwirrend sein zu verstehen. Lassen Sie uns diese Formel testen, indem wir sie im unten gezeigten Beispiel ausführen.
Hier haben wir Daten (A1: D15) der eingegangenen Bestellungen aus verschiedenen Regionen mit gegebener Menge und Preiswert angegeben. Wir müssen den DURCHSCHNITT des Preises ermitteln, wenn die Menge nicht leer ist.
Die obige Bedingung oder Kriterien müssen erfüllt sein. Die Funktion AVERAGEIFS kann uns helfen, den Durchschnitt ohne leere Zellen zu extrahieren.
Verwenden Sie die Formel:
= MITTELWERTWENN ( Preis , Menge , " " )
Menge: benannter Bereich, der für das C2: C15-Array verwendet wird
Preis: benannter Bereich, der für das D2:D15-Array verwendet wird
"" : Kriterien
Erläuterung:
- MITTELWERTIFS Funktion erhält den Durchschnitt des Bereichs, in dem alle Bedingungen erfüllt sind.
- Hier gilt die Bedingung für den Mengenbereich, d. h. den Durchschnittspreis, wenn die Menge nicht leer ist.
- "" stimmt mit Werten überein, bei denen Zellen nicht leer sind.
Hier benannte Bereiche angegeben als (C2:C15) Menge und (D2:D15) Preis.
Die in der Zelle verwendete Formel wird wie im obigen Schnappschuss angezeigt. Drücken Sie die Eingabetaste, um den Preisdurchschnitt zu erhalten, wenn die Menge nicht leer ist.
Wie Sie in der obigen Momentaufnahme sehen können, ergibt sich der DURCHSCHNITT der Preise, bei denen die Menge nicht leer ist 158.26. Sie können das Ergebnis überprüfen, indem Sie einen Filter auf die Daten anwenden und nicht leere Mengenwerte filtern, wie unten gezeigt.
Die durchschnittlichen Ergebnisse werden unten rechts im Excel-Arbeitsblatt zusammen mit der Anzahl und der Summe angezeigt.
Beispiel 2:
Hier haben wir Daten (A1: D15) der eingegangenen Bestellungen aus verschiedenen Regionen mit gegebener Menge und Preiswert angegeben. Wir müssen den DURCHSCHNITT des Preises ermitteln, wenn die Menge nicht leer ist und die Region Ost ist.
Dies schließt mehrere Kriterien in die Formel ein. Die AVERAGEIFS-Funktion kann uns helfen, den Durchschnitt mit verschiedenen Kriterien zu extrahieren.
Verwenden Sie die Formel:
= MITTELWERTWENN ( Preis , Menge , " " , Region , "Ost" ) )
Region: benannter Bereich, der für das B2:B15-Array verwendet wird
Menge: benannter Bereich, der für das C2: C15-Array verwendet wird
Preis: benannter Bereich, der für das D2:D15-Array verwendet wird
"" : Kriterien
Erläuterung:
- MITTELWERTIFS Funktion erhält den Durchschnitt des Bereichs, in dem alle Bedingungen erfüllt sind.
- Hier gilt die Bedingung für den Mengenbereich, d. h. den Durchschnittspreis, wenn die Menge nicht leer ist.
- "" stimmt mit Werten überein, bei denen Zellen nicht leer sind.
- Region ist der zweite Kriterienbereich, in dem die Bedingung ist Region Array muss mit dem Wert "Ost" übereinstimmen
Hier benannte Bereiche angegeben als (C2:C15) Menge , (D2:D15) Preis & (B2:B15) Region.
Die in der Zelle verwendete Formel wird wie im obigen Schnappschuss angezeigt. Drücken Sie die Eingabetaste, um den Preisdurchschnitt zu erhalten, wenn die Menge nicht leer ist und die Region Ost ist.
Wie Sie in der obigen Momentaufnahme sehen können, ergibt sich der DURCHSCHNITT der Preise, bei denen die Menge nicht leer ist 171.35. Sie können das Ergebnis überprüfen, indem Sie einen Filter auf die Daten anwenden und nicht leere Mengenwerte filtern, wie unten gezeigt.
Die durchschnittlichen Ergebnisse werden unten rechts im Excel-Arbeitsblatt zusammen mit der Anzahl und der Summe angezeigt. Dies beweist, dass die Formel gut funktioniert.
Hier sind einige Beobachtungen zur Formelverwendung unten.
Anmerkungen:
- Die Funktion gibt #WERT zurück! Fehler, wenn das Argument der Funktion nicht numerisch ist.
- Die Funktion gibt #WERT zurück! Fehler, wenn kein Wert den angegebenen Kriterien entspricht.
- KriterienoperatorDie Formel akzeptiert beispielsweise gleich (=), kleiner als (<) , kleiner oder gleich (=) , größer als (>) & ungleich ().
Auf diese Weise haben wir gelernt, wie man die AVERAGEIFS-Funktion in Excel verwendet, um eine Rückgabe aus den nicht leeren Zellen zu erhalten. Sie können diese Funktionen in Excel 2016, 2013 und 2010 verwenden. Weitere Artikel zur mathematischen Formulierung mit Bedingung finden Sie hier. Wenn Sie ein Problem mit diesem Artikel oder eine ungelöste Frage haben, kommentieren Sie bitte im Kommentarfeld unten. Wir werden Ihnen helfen.
So verwenden Sie die AVERAGEIFS-Funktion in Excel
So verwenden Sie die DAVERAGE-Funktion in Excel
So markieren Sie Zellen über und unter dem Durchschnittswert
Ignoriere Null im Durchschnitt der Zahlen
Gewichteten Durchschnitt berechnen
Durchschnittliche Differenz zwischen Listen
Validierung von Texteingaben
Dropdown-Liste in Excel mit Farbe erstellen
populäre Artikel
Bearbeiten einer Dropdown-Liste
Absolute Referenz in Excel
Bei bedingter Formatierung
Wenn mit Wildcards
Vlookup nach Datum
Vor- und Nachname in Excel eintragen
Zählen Sie Zellen, die entweder mit A oder B übereinstimmen
Konvertieren Sie Zoll in Fuß und Zoll in Excel 2016
50 Excel-Shortcut zur Steigerung Ihrer Produktivität