Mittelwert von gefilterter Spalte

Bild

Betrifft: Mittelwert von gefilterter Spalte
von: Alex
Geschrieben am: 18.07.2015 12:32:12

Hallo,
da ich in Sachen VBA noch Anfänger bin, habe ich gerade folgendes Problem:
Ich habe eine Tabelle mit verschiedenen Typen in Spalte G und dazugehörigen Messwerten in Spalte H. Die Tabelle ist gefiltert, d.h. alle Werte < 2 und alle Werte > 9 wurden herausgefiltert. Nun möchte ich den Mittelwert der Messwerte, getrennt nach Typen, berechnen.
Dazu habe ich folgendes Makro geschrieben:

Sub Mittelwert()
Dim Typ1 As String
Dim Typ2 As String
Typ1 = Workbooks("Auswertung.xlsm").Worksheets(1).Cells(10, 1).Value
Typ2 = Workbooks("Auswertung.xlsm").Worksheets(1).Cells(11, 1).Value
Dim Ende As Long
Dim mw1 As Double
Dim mw2 As Double
Dim wks As Worksheet
Dim zaehler As Integer
Dim n As Integer
zaehler = 1
For Each wks In Workbooks("Messwerte.xls").Worksheets
    wks.Activate
    
    Workbooks("Auswertung.xlsm").Worksheets(1).Cells(9, 1 + zaehler) = wks.Name
    
    Ende = wks.Cells(Rows.Count, 2).End(xlUp).Row
    
    For n = 3 To Ende
        If wks.Cells(n, 7) = Typ1 Then
            mw1 = wks.Application.WorksheetFunction.AverageIf(Range("G4:G" & Ende), Typ1, Range( _
 _
"H4:H" & Ende))
            Workbooks("Auswertung.xlsm").Worksheets(1).Cells(10, 1 + zaehler) = mw1
        End If
        
        If wks.Cells(n, 7) = Typ2 Then
            mw2 = wks.Application.WorksheetFunction.AverageIf(Range("G4:G" & Ende), Typ2, Range( _
 _
"H4:H" & Ende))
            Workbooks("Auswertung.xlsm").Worksheets(1).Cells(11, 1 + zaehler) = mw2
        End If
    Next n
    
zaehler = zaehler + 1
    
Next wks
    
        
End Sub

mein Problem ist jetzt, dass beim Mittelwert auch die eigentlich herausgefilterten Werte mit einbezogen werden. Ich habe gelesen, dass man das mit .SpecialCells(xlCellTypeVisible) lösen kann. Aber
mw1 = wks.Application.WorksheetFunction.AverageIf(Range("G4:G" & 
Ende).SpecialCells(xlCellTypeVisible), Typ1, 
Range("H4:H" & Ende).SpecialCells(xlCellTypeVisible))

funktioniert leider nicht.
Schleifen möchte ich nach Möglichkeit vermeiden, da ich 30 Tabellenblätter mit jeweils 9000 Messwerte auswerten muss. Eine Abfrage in einer if-Schleife, ob die Zelle ausgeblendet ist, möchte ich vermeiden, weil das Programm sonst zu lange dauert. Es wäre also gut, wenn man die Lösung irgendwie in die AverageIf-Funktion integrieren könnte.
Es wäre sehr nett, wenn mir da jemand helfen könnte.
Schon einmal vielen Dank!

Bild

Betrifft: AW: Mittelwert von gefilterter Spalte
von: Tino
Geschrieben am: 18.07.2015 15:32:08
Hallo,
filtere die Tabelle einfach noch nach den Typ und arbeite mit Teilergebnis.
Nicht getestet!

mw1 = Application.WorksheetFunction.Subtotal(101,wks.Range("H4:H" & Ende))
Gruß Tino

Bild

Betrifft: AW: Mittelwert von gefilterter Spalte
von: Alex
Geschrieben am: 18.07.2015 17:38:29
Vielen Dank! Hat super geklappt!

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Mittelwert von gefilterter Spalte"