Van gemiddeld zonder Verborgen Cellen

Susie heeft een werkblad dat nogal wat gegevens in het heeft. Het is niet ongebruikelijk voor haar om kolommen in het werkblad te verbergen. Ze heeft een formule die het gemiddelde van een reeks cellen zal terugkeren, allemaal in dezelfde rij, terwijl het negeren van verborgen cellen. Bijvoorbeeld, als Susie selecteert B7: G7 wil zij gemiddeld slechts de cellen in dat gebied die zichtbaar is (kolommen D en E, in casu verborgen).

Er is geen intrinsieke functie in Excel om dit gemiddelde te berekenen. Als je probeert om het gemiddelde van een reeks cellen te vinden in een kolom, dan kun je de functie SUBTOTAAL gebruiken, op deze manier:

= SUBTOTAAL (101, A7: A12)

De functie SUBTOTAAL zal een nauwkeurige waarde niet terug als het bereik voorwaarde is binnen een rij, echter. Je kan, indien gewenst, gebruik wat helpercellen om de berekening te doen. Gewoon de volgende in cel B8:

= (CEL ("breedte", B7)> 0) 0

Kopieer cel B8 om het bereik C8: G8. Het resultaat is dat elke cel in het bereik B8: G8 bevat ofwel een 0 of 1 afhankelijk van of de kolom is verborgen of niet. U kunt dan gebruik maken van de volgende formule om het gemiddelde te bepalen:

= SOMMEN.ALS (B7: G7, B8: G8, "> 0") / SOM (B8: G8)

Het berekent het gemiddelde alleen voor kolommen waarbij de cellen B8: G8 een waarde van 1 (ze zijn niet verborgen) bevatten.

Natuurlijk kunt u niet in staat zijn om de helper cellen gebruiken en vind je het misschien voordeliger om een ​​door de gebruiker gedefinieerde functie te doen berekenen van de gemiddelde creëren. De volgende werken zeer snel:

Functie AverageVisible (RNG als Waaier)
Dim rCell als Waaier
Dim iCount As Integer
Dim DTTL As Double

iCount = 0
DTTL = 0
Voor Elke rCell In RNG
Als rCell.ColumnWidth> 0 _
En rCell.RowHeight> 0 _
En Niet IsEmpty (rCell) _
En IsNumeric (rCell.Value) Dan
DTTL = DTTL + rCell
iCount = iCount + 1
End If
Volgende
Als iCount> 0 Then
AverageVisible = DTTL / iCount
Anders
AverageVisible = 0
End If
End Function

Om de functie te gebruiken, eenvoudig gezegd deze formule in de cel die u wilt de gemiddelde bevatten:

= AverageVisible (B7: G7)

De functie controleert elke cel in het bereik (wat betekent dat je zou kunnen gebruiken op rijen, kolommen, of zelfs elk bereik helemaal niet) om ervoor te zorgen dat het niet verborgen is en niet leeg. Als de cel een numerieke waarde, dan wordt het gebruikt om het gemiddelde te berekenen. Als het bereik dat u opgeeft geen waarden bevat die kunnen worden gemiddeld, dan is de functie een waarde van 0.

De functie wordt automatisch elke keer het werkblad wordt herberekend. Als je instellen welke kolommen worden verborgen, doet Excel niet automatisch herberekenen. Zo moet u herberekening dwingen na te verbergen of zichtbaar maken kolommen.

ExcelTips is uw bron voor kosteneffectieve Microsoft Excel training. Deze tip (13.262) is van toepassing op Microsoft Excel 2007, 2010 en 2013.


© 2020 Quilcedacarvers.com | Contact us: webmaster# quilcedacarvers.com