Susie hat ein Arbeitsblatt, das eine Menge Daten enthält. Es ist nicht ungewöhnlich, dass sie Spalten im Arbeitsblatt ausblendet. Sie benötigt eine Formel, die den Durchschnitt eines Zellbereichs in derselben Zeile zurückgibt und alle versteckten Zellen ignoriert. Wenn Susie beispielsweise B7: G7 auswählt, möchte sie einen Durchschnitt nur der Zellen in diesem Bereich, die sichtbar sind (die Spalten D und E sind in diesem Fall ausgeblendet).

In Excel gibt es keine intrinsische Funktion zur Berechnung eines solchen Durchschnitts. Wenn Sie versuchen, den Durchschnitt eines Zellbereichs in einer Spalte zu ermitteln, können Sie die Funktion SUBTOTAL folgendermaßen verwenden:

=SUBTOTAL(101,A7:A12)

Die SUBTOTAL-Funktion gibt jedoch keinen genauen Wert zurück, wenn der angegebene Bereich innerhalb einer Zeile liegt. Falls gewünscht, können Sie einige Hilfszellen verwenden, um die Berechnung durchzuführen. Fügen Sie einfach Folgendes in Zelle B8 ein:

=(CELL("width",B7)>0)+0

Kopieren Sie Zelle B8 in den Bereich C8: G8. Das Ergebnis ist, dass jede Zelle im Bereich B8: G8 entweder eine 0 oder eine 1 enthält, je nachdem, ob die Spalte ausgeblendet ist oder nicht. Sie können dann die folgende Formel verwenden, um den Durchschnitt zu bestimmen:

=SUMIFS(B7:G7,B8:G8,">0")/SUM(B8:G8)

Der Durchschnitt wird nur für die Spalten berechnet, in denen die Zellen in B8: G8 den Wert 1 enthalten (sie sind nicht ausgeblendet).

Natürlich können Sie die Hilfszellen möglicherweise nicht verwenden, und es ist möglicherweise vorteilhafter, eine benutzerdefinierte Funktion zur Berechnung des Durchschnitts zu erstellen. Folgendes funktioniert sehr schnell:

Function AverageVisible(rng As Range)

Dim rCell As Range     Dim iCount As Integer     Dim dTtl As Double

iCount = 0     dTtl = 0     For Each rCell In rng         If rCell.ColumnWidth > 0 _           And rCell.RowHeight > 0 _           And Not IsEmpty(rCell) _           And IsNumeric(rCell.Value) Then               dTtl = dTtl + rCell               iCount = iCount + 1         End If     Next     If iCount > 0 Then         AverageVisible = dTtl / iCount     Else         AverageVisible = 0     End If End Function

Um die Funktion zu verwenden, fügen Sie diese Formel einfach in die Zelle ein, in der Sie den Durchschnitt enthalten möchten:

=AverageVisible(B7:G7)

Die Funktion überprüft jede Zelle im Bereich (dh Sie können sie für Zeilen, Spalten oder sogar für jeden Bereich verwenden), um sicherzustellen, dass sie nicht ausgeblendet und nicht leer ist. Wenn die Zelle einen numerischen Wert enthält, wird dieser zur Berechnung des Durchschnitts verwendet. Wenn der von Ihnen angegebene Bereich keine Werte enthält, die gemittelt werden können, gibt die Funktion den Wert 0 zurück.

Die Funktion wird bei jeder Neuberechnung des Arbeitsblatts automatisch ausgeführt. Wenn Sie ändern, welche Spalten ausgeblendet sind, berechnet Excel nicht automatisch neu. Daher müssen Sie eine Neuberechnung erzwingen, nachdem Sie Spalten ausgeblendet oder ausgeblendet haben.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13262) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.