Ermittlung gefilterter Daten in vers. Abschnitten

Bild

Betrifft: Ermittlung gefilterter Daten in vers. Abschnitten
von: JB
Geschrieben am: 24.11.2015 11:01:41

Hallo,
ich habe eine große Anzahl Daten in Tabellen (Tabelle 1-4) auf mehreren Arbeitsblättern zusammengestellt. Für die Auswertung habe ich neue Arbeitsblätter erstellt (Analysis 72-..).
Spalte 1 und 4 in den Tabellen müssen so gefiltert sein, dass nur Werte >0 angezeigt werden. Anschließend ermittle ich den größten Wert in Spalte 4 und unterteile dieses Maximum in Bereiche der Größe 500. Für jeden Abschnitt (0...499, 500...999, ...) filtere ich in einer Schleife Spalte 4, sodass sie nur die Zahlen aus dem Bereich des jeweiligen Abschnitts enthält. Zusätzlich setze ich einen Filter in Spalte 6, um die Level zu erhalten, für die ich mich interessiere. Nun möchte ich die Zeilen der gefilterten Tabelle zählen, um die Anazahl der Events mit diesem bestimmten Level in diesem Abschnitt zu erhalten.
Das Problem: Der Befehl Subtotal funktioniert nicht, ich erhalte als Ergebnis immer 0. Außerdem bekomme ich bei i=66 die Fehlermeldung "Überlauf".
Ich hoffe ihr könnt mir helfen und mir sagen, wo im Code mir ein Fehler unterlaufen ist!
Vielen Dank im Voraus!

Sub workscope_data_analysis()
    Dim Maximum As Long
    Dim Minimum As Long
    Dim t As Integer
    Dim tables As Variant
    Dim analysis As Variant
    Dim TSO_sections_float As Single
    Dim TSO_sections As Integer
    Dim i As Integer
    Dim lower_limit As Long
    Dim upper_limit As Long
    Dim TSO_step_size As Integer
    Dim number_SC_levels As Integer
    Dim w As Worksheet
    
    tables = Array("Tabelle1", "Tabelle2", "Tabelle3", "Tabelle4")
    analysis = Array("Analysis 72-21", "Analysis 72-22", "Analysis 72-23", "Analysis 72-61")
    t = 0
    
    For Each w In Worksheets
        If w.Name Like "72*" Then
            
            
            ' Filter data
            w.Activate
            If w.FilterMode Then w.ShowAllData
            w.ListObjects(tables(t)).Range.AutoFilter Field:=1, Criteria1:=">0",Operator:= _
xlFilterValues
            w.ListObjects(tables(t)).Range.AutoFilter Field:=4, Criteria1:=">0",Operator:= _
xlFilterValues
            
            ' Get minimum and maximum TSO
            Maximum = w.Application.WorksheetFunction.MAX(Range("D3:D600"))
            Minimum = w.Application.WorksheetFunction.MIN(Range("D3:D600"))
            
            ' Compute number of TSO sections
            TSO_step_size = 500 ' Set TSO step size
            TSO_sections_float = Maximum / TSO_step_size
            ' Round to next full number
            TSO_sections = Application.WorksheetFunction.RoundUp(TSO_sections_float, 0)
            
            Worksheets(analysis(t)).Cells(2, 2).Value = Minimum
            Worksheets(analysis(t)).Cells(3, 2).Value = Maximum
            Worksheets(analysis(t)).Cells(2, 1).Value = "Minimum TSO: "
            Worksheets(analysis(t)).Cells(3, 1).Value = "Maximum TSO: "
            Worksheets(analysis(t)).Cells(4, 2).Value = TSO_sections
            Worksheets(analysis(t)).Cells(4, 1).Value = "Number of TSO sections: "
            
            ' Count number of ws events (S/C) per TSO_section
            
            upper_limit = 0 'Initialize upper limit
            For i = 1 To TSO_sections ' Loop over each TSO_section
                lower_limit = upper_limit
                upper_limit = i * TSO_step_size
                w.Activate
                ' Set filter for workscope level and TSO_section
                w.ListObjects(tables(t)).Range.AutoFilter Field:=6,Criteria1:="S/C", Operator:  _
_
=xlFilterValues
                w.ListObjects(tables(t)).Range.AutoFilter Field:=4,Criteria1:=">=lower_limit", _
Operator:=xlAnd, Criteria2:="0 in column 4
                w.ListObjects(tables(t)).Range.AutoFilter Field:=4
                w.ListObjects(tables(t)).Range.AutoFilter Field:=4,Criteria1:=">0", Operator:= _
xlFilterValues
                w.ListObjects(tables(t)).Range.AutoFilter Field:=6
                
            Next
            
            
            ' Create graph
            '...
            
            ' Last step: Show all data (S/N and TSO >0) and sort by S/N and date
            w.Activate
            If w.FilterMode Then w.ShowAllData
            w.ListObjects(tables(t)).Range.AutoFilter Field:=1, Criteria1:=">0",Operator:= _
xlFilterValues
            w.ListObjects(tables(t)).Range.AutoFilter Field:=4, Criteria1:=">0",Operator:=  _
xlFilterValues
            w.ListObjects(tables(t)).Sort.SortFields.Clear
            w.ListObjects(tables(t)).Sort. _
            SortFields.Add Key:=Range(tables(t) & "[[#All],[Date]]"), SortOn:= _
            xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With w.ListObjects(tables(t)).Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            ' Then sort S/N number
            w.ListObjects(tables(t)).Sort.SortFields.Clear
            w.ListObjects(tables(t)).Sort. _
            SortFields.Add Key:=Range(tables(t) & "[[#All],[S/N]]"),SortOn:=xlSortOnValues _
            , Order:=xlAscending, DataOption:=xlSortNormal
            With w.ListObjects(tables(t)).Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            
            ' Initialize next table
            t = t + 1
            
        End If
    
    Next w
    
    
   
End Sub

Bild

Betrifft: Beispieldatei?
von: Michael
Geschrieben am: 24.11.2015 16:20:04
Hi JB,
ich sehe keinen "subtotal" in Deinem Code.
Das ist zu komplex, als daß ich das ohne Beispieldatei bearbeiten möchte.
Schöne Grüße,
Michael

Bild

Betrifft: AW: Beispieldatei?
von: JB
Geschrieben am: 25.11.2015 14:33:37
Hey,
ja du hast Recht, der Code, den ich gepostet habe, ist zu komplex.
Mein Problem ist, dass ich in einer gefilterten Tabelle die Anzahl der angezeigten Zeilen zählen möchte. Es gibt keine Fehlermeldung, aber komischerweise bekomme ich als Ergebnis immer 0 oder 1, was nicht stimmen kann. Ich habe auf zwei Arten versucht, die gefilterten Zeilen zu zählen (ein Versuch ist auskommentiert):

' Set filter
w.ListObjects(tables(t)).Range.AutoFilter Field:=6, Criteria1:="S/C", Operator:=xlFilterValues
' Count number of filtered data rows
'number_SC_levels = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible). _
Count
number_SC_levels = Application.WorksheetFunction.Subtotal(103, Range("A3:A600"))
'Remove filter
w.ListObjects(tables(t)).Range.AutoFilter Field:=6


Bild

Betrifft: immer noch: Beispieldatei?
von: Michael
Geschrieben am: 25.11.2015 18:34:17
Hi JB,
es liegt nicht am "komplex", sondern daran, daß ich das ohne Datei nicht testen kann.
Die zu anonymisieren, kostet Dich natürlich Zeit, aber mich kostet es Zeit, Deine Daten nachzubasteln: danke, nein.
Schöne Grüße,
Michael

Bild

Betrifft: AW: immer noch: Beispieldatei?
von: JB
Geschrieben am: 26.11.2015 11:32:24
Hallo,
sorry das war wohl ein Missverständnis. Ich kenne mich noch nicht so gut aus, wie das hier im Forum läuft. Hier ist die Datei: https://www.herber.de/bbs/user/101821.xlsm
Danke&Grüße

Bild

Betrifft: aha, aber jetzt
von: Michael
Geschrieben am: 26.11.2015 16:47:34
Hi JB,
den genannten Fehler mit i=66 kann ich nicht nachvollziehen; ich habe (in 72-22) testweise einen Wert 33001 eingegeben, der bis 67 rechnet, das geht.
Der Knackpunkt sind die Filterkriterien: das ist ein typischer Anfängerfehler.
Du codierst (das Forum zeigt die Größer-/Kleinerzeichen nicht richtig an, deshalb in []):
Criteria1:="[größer]=lower_limit", Operator:=xlAnd, Criteria2:="[kleiner]upper_limit"
aber richtig ist es so:
Criteria1:="[größer]=" & lower_limit, Operator:=xlAnd, Criteria2:="[kleiner]" & upper_limit
weil lower_limit und upper_limit *Variablen* sind; in der oberen Schreibweise werden sie als *Text* gewertet.
Bei den weiteren Filtern hast Du ein ähnliches Problem:

Criteria1:="MIN"
Criteria1:="=MIN"

oben fehlt das "=", mit dem die Formel beginnt.
Damit kommst Du auf die gewünschten Treffer; allerdings gibt es noch zwei Fallstricke:
Sieh Dir mal die "Analysis 72-21" an: hier werden nur 6 "Treffer" erzeugt, obwohl Deine Daten offensichtlich 8 Zeilen haben. Das Eine ist ein Tippfehler (unter WS steht einmal S/N statt S/C), das andere ein Fehler in der Logik: der Wert 25000 S/C fehlt.
Du hast ja bereits nach größer Null gefiltert, aber im oben Filter von-bis filterst von größergleich 0 bis kleiner 500; 25000 ist eben nicht kleiner 25000, sonder gleich, also würde ich den Filter anders gestalten: von größer min bis kleinergleich max:
Criteria1:="[größer]" & lower_limit, Operator:=xlAnd, Criteria2:="[kleiner]=" & upper_limit
dann sind die 25000 auch dabei.
(das habe ich noch nicht in der Datei geändert)
Zu guter Letzt kannst Du bei einer Sortierung nach einem Kriterium nur genau danach sortieren: wenn Du zuerst nach Datum und dann nach S/N sortierst, wird die erste Sortierung von der zweiten gleich wieder "umgeräumt". Im Beispiel hast Du kein Datum eingetragen, also habe ich mich nicht vertieft.
Sortieren kann man mit mehreren Keys gleichzeitig - recherchiere das mal. Halt so, wie im Telefonbuch: Key1=Name; Key2=Vorname oder so.
Die Datei: https://www.herber.de/bbs/user/101829.xlsm
Schöne Grüße,
Michael
P.S.: Ich mag diese Tabellen in den Tabellen nicht besonders; wenn Du Dich davon trennen könntest, würde ich Dir evtl. eine deutlich optimierte Lösung anbieten: wieviele Daten sind es denn in der Praxis?

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Ermittlung gefilterter Daten in vers. Abschnitten"